MySQL高性能索引

一、索引类型

1. B-Tree索引
   索引默认指的就是这种索引，大多数MySQL引擎都支持这种索引。B-Tree通常意味着所有的值都是按照顺序存储的，并且每个叶子页到根的距离相同。
   B-Tree索引适用有下：
   1）全值匹配：全值匹配指的是和索引中的所有列进行匹配。
   2）匹配最左前缀：匹配索引左边起部分的列（遵循左前缀匹配规则）。
   3）匹配列前缀：匹配索引值列值的前缀。
   4）匹配范围值：匹配索引值列的范围（遵循左前缀匹配规则）。
   5）精确匹配某一列并范围匹配另外一列：可以先匹配索引左前列值，并范围匹配紧跟后边索引列值（遵循左前缀匹配规则）。
   6）只访问索引的查询：也称覆盖索引查询，查询数据只需要访问索引，无须访问数据行。
   索引还可以用于查询中ORDER BY操作，如果ORDER BY子句中满足前边的集中查询类型，则这个索引可以满足对应的排序需求。
   注意：不能跳过索引中的列，如果跳过，索引只会生效跳过列前边的列值；如果查询中有某个列的查询范围，则其右边所有列都无法使用索引（左前缀匹配规则）。
2. 哈希索引
   哈希索引只存储哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
   哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
   哈希索引也不支持部分索引匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
   哈希索引只支持等值比较查询，不支持任何范围查询。
   访问哈希索引的数据非常快，当数据量大，有很多哈希冲突的时候，必须遍历链表进行比较，或者维护链表值。
   创建自定义哈希索引：B-Tree索引情况下，可以利用哈希优点，为某些列多增加一个哈希值列，来加快查询。
3. 空间数据索引R-Tree（很少用）
   MyISAM表支持空间数据索引，它将会从所有维度来索引数据。
4. 全文索引
   全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。在相同的列上同时创建全文索引和基于值的B-Tree索引不会冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

二、高性能索引策略

有时需要索引很长的字符列，这会让索引变得大而且慢，一个策略就是使用前边说的自定义哈希索引。
索引选择性是指，不重复的索引值（也称基数cardinality）和数据表的记录总数（#T）的比值，范围从1/#T~1之间。索引选择性越高则查询效率越高。

1. 前缀索引，前缀索引就是索引很长值的列时，取这个列的值左边部分数据，取的长度按照索引选择性原则，维护一个新列，然后进行索引匹配，有缺点就是无法做覆盖查询，以及ORDER BY和GROUP BY。
2. 选择合适的索引列顺序，当不考虑排序和分组的时，将选择性最高的列放在最前面通常是最好的。但也要根据情况，选择那些运行频率最高的条件来调整索引列的顺序。
3. 聚簇索引，是一种数据存储方式。术语”聚簇索引“表示数据行和相邻的键值紧凑地存储在一起。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。MyISAM按照插入的顺序存储在磁盘上。使用InnoDB时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。因为如果不按照这样，写入时乱序的，需要寻找目的页，导致大量的随机I/O，而且InnoDB不得不频繁做页分裂操作，最终导致数据碎片。
   InnoDB二级索引的叶子节点都包含了主键的值。
4. 顺序主键坏处：高并发工作负载，主键的上界可能成为“热点”，间隙锁竞争。另外一个可能是AUTO_INCREMENT锁机制。这时可以考虑重新设计表或应用，或者更改innodb_autoinc_lock_mode配置。
5. 覆盖索引，如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就是覆盖索引。
   好处：索引条目通常远小于数据行大小、所以只需要读取索引，极大减少数据访问量。索引是按照列值顺序存储的，范围查询会比随机从磁盘读取每一行数据的I/O要快得多。InnoDB数据存储方式是聚簇索引，它二级索引在叶子节点保存行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
   使用EXPLAIN中，当Extra列有“using index”就是判定是覆盖索引查询（和type列没有关系，type显示查找行的方式）。

三、其他的一些建议

1. 使用索引排序，MySQL有两种生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，说明MySQL使用了索引扫描来做排序。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正/倒序）都一样，才能使用索引排序。
2. 避免冗余和重复索引，如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。
3. 尽量使用等值查询代替范围查询，对于范围条件查询，MySQL无法再使用范围列后边的其他索引列了，但是对于“多个等值条件查询”则没有这个限制（使用in代替>、<等范围查询）。
4. 延迟关联，通过使用覆盖索引查询返回需要的主键，然后通过这些主键关联原表获取需要的行，延迟对列的访问（这是应对一些查询条件苛刻，通过使用覆盖索引，快速获取数据其他列的方式）。
5. 减少索引和数据的碎片，表的数据存储有三种类型数据碎片，行碎片、行间碎片、剩余空间碎片。可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。先删除索引，然后再重新创建索引的方式也是一种消除索引碎片化的一种方式。