MySQL索引

索引类型

索引是在存储引擎层实现的，而不是服务器层。因此，它们并不是标准化的，每个引擎的索引工作方式略有不同。即使多个引擎支持同样的索引，它们的实现方式也可能有所不同。

 

B-Tree索引

B-Tree通常意味着数据存储是有序的，并且每个叶子页到根的距离是一样的。它加速了数据访问，因为存储引擎不会扫描整个表得到需要的数据，相反，它从根节点开始。根节点保存了指向子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针。因为B-Tree按顺序保存了索引的列，它们对于搜索范围数据很有用。

 

能使用B-Tree索引的查询类型

1. 匹配全名

2. 匹配最左前缀

3. 匹配范围值

4. 精确匹配一部分并且匹配某个范围中的另一部分

5. 只访问索引的查询

 

局限性

1. 如果查找没有从索引列的最左边开始，它就没什么用处。

2. 不能跳过索引中的列。

3. 存储引擎不能优化访问任何在第一个范围条件右边的列。

 

Hash索引

哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，并且保存了一个指向哈希表中每一行的指针。

 

因为索引本身只保存简短的哈希值，哈希索引显得非常紧凑。哈希值的长度不会依赖于索引的列，TINYINT列的哈希索引和大型字符列的哈希索引大小是一样的。

 

局限性

1. 因为索引只包含了哈希码和行指针，而不是值自身，所以不能使用索引中的值来避免读取行。

2. 不能排序，因为它们不会按序保存行。

3. 不支持部分匹配，因为被索引的值是计算出来的。

4. 只支持使用了=，IN()和<=>的相等比较，不能加快范围查询。

5. 发生碰撞（哈希码相同）的时候，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较。

 

R-Tree

空间索引（Spatial Index），它可以使用诸如GEOMETRY这样的地理空间数据类型（Geospatial Type）。和B-Tree索引不同，空间索引不会要求WHERE子句使用索引的最左前缀。它同时全方位的索引了数据。这样就可以高效的使用任何数据组合进行查找。然而必须使用MySQL GIS函数，例如MBRCONTAINS()，才能得到这个好处。

 

全文索引

不讨论，MySQL中文分词没研究过。

 

为排序使用索引扫描

MySQL有两种产生排序结果的方式：使用文件排序（Filesort）和扫描有序的索引。EXPLAIN的输出中type列的值为“Index”，这说明了MySQL会扫描索引。扫描索引本身是很快的，因为它只需要从一条索引记录移到另外一条记录。然而，如果没有使用索引覆盖查询，就不得不查找在索引中发现的每一行。这基本是随机的IO操作，因此以索引顺序读取数据通常比顺序扫描表慢的多，尤其对于IO密集的工作负载。

 

按照索引对结果进行排序，只有当索引的顺序和ORDER BY子句中的顺序完全一致，并且所有列排序的方向一样才可以。如果查询链接了多个表，只有在ORDER BY子句的所有列引用的是第一个表才可以。

 

ORDER BY无需定义索引的最左前缀的一种情况是前导列为常量。例如定义了索引：

KEY idx_name (created_date, first_name, last_name)

 

下面的查询中不会看到filesort：

EXPLAIN SELECT last_name FROM user WHERE created_date = '2010-01-01' ORDER BY first_name, last_name

 

以上情况中，即使ORDER BY子句自身不是索引的最左前缀，它也能工作，因为它为索引的第一列定义了等于条件。下面是一些不能使用索引进行排序的查询。

 

使用不同的排序方向：

WHERE created_date = '2010-01-01' ORDER BY first_name DESC, last_name ASC

 

引用了一个不在索引中的列：

WHERE created_date = '2010-01-01' ORDER BY first_name, email

 

WHERE和ORDER BY不能形成最左前缀：

WHERE created_date = '2010-01-01' ORDER BY email

 

第一列上有范围条件，因此MySQL不会使用余下的索引：

WHERE created_date > '2010-01-01' ORDER BY first_name, last_name

 

有多个等于条件。对于排序来说，这也是范围查询：

WHERE created_date = '2010-01-01' AND first_name = 'clay' ORDER BY last_name

 

压缩索引

MyISAM使用前缀压缩以减少索引大小。它在默认情况下会压缩字符串，但是可以让他压缩整数。MyISAM在对索引块排序的时候，首先对第一个值进行全排序，然后记录下有相同前缀的字节数，加上不同的值作为后缀。例如，如果第一个值是“perform”并且第二个值是“performance”，第二个值就会被近似的存储为“7，ance”。

 

压缩后的块占用的空间较小，但是使得某些操作变慢了。因为每个值的压缩前缀依赖于前面的值，MyISAM不能在索引中使用二进制搜索找到想要的值，而必须从头开始。顺序向前的操作性能尚可，但是反正扫描，例如ORDER BY DESC不会很好的工作。任何需查找数据块中部的行的操作要对块进行扫描，平均来说，要扫描半个块。

 

多余和重复索引

重复索引是类型相同，以同样的顺序在同样的列上创建的索引。应该避免创建重复索引，并且在发现它的时候把它移除掉。例如下面的代码在同一列上创建了三个相同的索引：

 

CREATE TABLE test (

    ID INT NOT NULL PRIMARY KEY,

    UNIQUE(ID),

    INDEX(ID)

)

 

通常没有理由这么做，除非想在同一列上有不同的索引以满足不同类型的查询。

 

多余索引和重复索引又一些不同。如果列（A，B）上有索引，那么另外一个列（A）上的索引就是多余的。这就是说（A，B）上的索引能被当成（A）上的索引。这种多余只适合于B-Tree索引。然而（B，A）上的索引不会是多余的，（B）上的索引也不是，因为列B不是列（A，B）的最左前缀。

 

在大部分情况下，多余索引都是不好的，为了避免它，应该扩展已有索引，而不是添加新索引。但是，还是有一些情况处于性能考虑需要多余索引。使用多余索引的主要原因是扩展已有索引的时候，它会变得很大。多余索引还存在维护开销的缺点。向有更多索引的表中插入新行是会慢的多，这通常会对INSERT，UPDATE，DELETE有较大的性能影响，尤其在新索引遇到内存限制的时候。