每天一点MySQL-索引

索引（Index）是帮助MySQL高效获取数据的数据结构。它对于高性能非常关键，建立索引是现实中性能问题的首要原因。

理解索引如何工作的最简单的方式就是把索引看成一本书，为了找到书中一个特定的话题，需要查看目录，它会告诉你页码。

Ex：

SELECT first_name FROM sakila.actor WHERE actor_id=5;

索引列位于actor_id，因此MySQL会使用索引找到actor_id为5的行。

索引包含了来自表中某一列或多个列的值。如果索引了多列数据，那么列的顺序非常重要，因为MySQL只能高效地搜索索引的最左前缀（Leftmost Prefix）。

索引类型

索引是在存储引擎层实现的，每个引擎的索引工作方式略有不同，实现方式也可能不同。

B+Tree索引

B+Tree通常意味着数组是有序的，并且每个叶子页（Leaf Page）到根的距离是一样的。下图显示了InnoDB的索引工作过程（InnoDB使用了B+树结构），MyISAM使用了不同的结构，但是原则是类似的。

叶子节点是很特殊的，因为它们含有指向被索引的数据的指针，而没有指向其他页面的指针。在根节点和叶子之间可能有很多层节点页面。树的深度取决于表的大小。

因为B+Tree按顺序保存了索引的列，对于搜索范围数据很有用。例如，一个文本字段的索引会按照字母顺序在树上依次排列，因此查找”名字开头字母从I到K的人”的效率会很高。

适用范围：
B+Tree索引能很好地用于全键值、键值范围或键前缀查找。它们只有在查找使用了索引的最左前缀的时候才有用。

• 匹配全名
  全键值匹配指和索引中的所有列匹配。

• 匹配最左前缀
  仅仅适用于索引中的第一列

• 匹配列前缀
  可以匹配某列的值的开头部分

• 匹配范围值
  只会索引第一列

• 精确匹配一部分并匹配某个范围中的另一部分

• 只访问索引的查询

局限：

• 如果查找没有从索引列的最左边开始，它就没有什么用处。
• 不能跳过索引中的列
• 存储引擎不能优化访问任何在第一个范围条件右边的列

所以列顺序非常重要，这些局限都和列顺序有关

哈希索引

建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中每一行的指针。

在MySQL中，只有Memory存储引擎支持显式的哈希索引。如果多个值有相同的哈希码，索引就会把行指针以链表（Linked List）的方式保存在哈希表的同一条记录中。

哈希表的查找速度是很快的，但是也有一些局限：

• 因为索引只包含了哈希码和行指针，而不是值自身，MySQL不能使用索引中的值来避免读取行。幸运的是，访问内存中的行很快，因此这通常不会降低性能。
• MySQL不能使用哈希索引进行排序，因为它们不会按序保存行
• 不支持部分键匹配，因为它们是由被索引的全部值计算出来的
• 哈希索引只支持使用了=、IN和<=>的相等比较。
• 访问哈希索引中的数据非常快，除非碰撞率很高（很多值有相同的哈希码）。当发生碰撞的时候，存储引擎必须访问链表中的每一行指针，然后逐行进行数据比较，以确定正确的数据
• 如果有很多碰撞，一些索引维护操作就很有可能会变慢。如果在一个选择性很低（很多碰撞值）的列上创建哈希索引，然后从表中删除一行，那么送索引中找到行的代价会很高。存储引擎将不得不检查哈希键链表中的每一行，以找到和移除被删除行的索引

InnoDB存储引擎有一个特别的功能，叫自适应哈希索引。当InnoDB注意到一些索引值被很频繁地访问的时候，它就会在B-Tree的顶端为这些值建立起内存中的索引。

参考资料

• 《高性能MySQL》
• 《MySQL技术内幕》