MySQL的索引

索引的类型

不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。——《高性能MySQL》

MySQL支持的主要索引类型

B-Tree索引

• InnoDB使用的是B+Tree，如下图示例。
• B+Tree索引可以加快访问数据的速度，引擎只需要从索引的根节点开始，向下搜索，不需要扫描全表数据。
• 索引只在叶子节点存储数据，并且叶子节点有指向下一个相邻叶子节点的指针。
• B+Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
• 索引树中节点是有序的，不仅支持按值查找，还可以用于查找中的order by操作。
• 如果索引多个值，依据CREATE TABLE语句中定义索引时列的顺序进行排序。
• 如果不是按照索引最左列开始查找无法使用索引；或者跳过索引中的列，或者查找中有某个列的范围查询，那么之后的列无法索引查找。
• 所以在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

InnoDB的B+Tree索引——摘自《高性能MySQL》

B-Tree索引有效

1. 全值匹配：和索引中所有列进行匹配。
2. 匹配最左前缀：只使用索引所有列中左边的列。
3. 匹配列前缀：使用索引左边的列，只匹配列的值的开头部分。
4. 匹配范围值：使用索引左边的列，索引用于查找某个范围的值。
5. 精确匹配某一列并范围匹配另外一列：精确匹配左边的列，下一列范围匹配。
6. 只访问索引的查询：查询只需要访问索引，不需要访问数据行。

B-Tree索引无效

1. 如果不是按照索引最左边的列开始查询，无法使用索引。
2. 不可以跳过索引中的列，否则跳过列的右边的列无法使用索引。
3. 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引查询。

哈希索引

• 基于哈希表实现，只有精确匹配索引所有列的查询才有效。
• 存储引擎对所有索引列计算一个哈希码，哈希索引将所有哈希码存储在索引中，同时在哈希表中保存指向数据行的指针。
• 哈希索引不是按索引值顺序存储的，无法用于排序，也不支持部分索引列匹配查找。
• 当哈希冲突很多的时候，索引的维护也会很高。

哈希索引的限制

1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值避免读取行。
2. 哈希索引数据不是按照索引值顺序存储的，无法用于排序。
3. 哈希索引使用所有索引列计算哈希值，所以不支持部分索引列的查询。
4. 哈希索引只支持等值比较查询，不支持范围查询。
5. 哈希索引访问数据很快，但是当有大量哈希冲突时，将不得不遍历链表中所有的行指针，逐行比较。
6. 哈希冲突很多的话，索引的维护成本也很高。

MySQL InnoDB为什么使用B+Tree？

1. 实际上很多存储引擎使用的是B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
2. 相比较平衡二叉树和红黑树等，B+树节点的度更高，这样大大降低了树的高度，减少磁盘访问量。
3. B树中间的节点会存放数据，相同存储空间，B+树中间节点具有更大的度，树高更低。因为B+树的所有数据都放在叶子节点上。