MySQL怎样创建高性能的索引

本文为《高性能MySQL（第三版）》一书的摘要总结

索引基础

索引优化是对查询性能优化最有效的手段。

索引可以包含多个列的值，如果索引包含多个列，列的顺序非常重要，因为MySQL只能高效使用索引的 最左前缀列。

索引类型

B-Tree索引

使用B-Tree（实际上是B+Tree）数据结构来存储数据。

• 可以使用B-Tree索引的查询类型：

1. 全值匹配

   和索引中的所有列进行匹配。

2. 匹配最左列

3. 匹配列前缀

   只匹配某一列的值的前缀部分，后续查询条件将不会使用索引。

4. 匹配范围值

   匹配某一列的值的某个范围，后续查询条件将不会使用索引。

5. 精确匹配某一列，并范围匹配另外一列

6. 值访问索引的查询

   覆盖索引。

• B-Tree索引的一些限制

1. 如果不按索引的最左列开始，则无法使用索引。

2. 不能跳过索引中得列

3. 如果查询中有某个列的范围查询，那么其右边的所有列都无法使用索引优化查询。

哈希索引

哈希索引基于 哈希表 实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个 哈希码。MySQL中只有Memory引擎支持哈希索引。

• 哈希索引的限制

1. 哈希索引只包含哈希码和行指针，所以不能使用索引中的值来避免读取行。
2. 哈希索引数据并不是按照索引只顺序存储的，所以无法用于排序
3. 哈希索引不支持部分索引列匹配查询。
4. 哈希索引只支持等值比较查询，包括=,IN(),<=>(安全比较运算符，a <=> NULL:当a为NUll时返回1，否则返回0)。
5. 需要考虑哈希冲突。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”，当InnoDB注意到一些索引只被使用的非常频繁时，它会在内存中基于B-Tree索引在创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点，如快速哈希查找。

• 创建自定义哈希索引：

如果存储引擎不支持哈希索引，那么可以模拟像InnoDB一样创建哈希索引：在B-Tree索引上创建一个 伪哈希索引。

• 处理哈希冲突：

我们在使用哈希索引进行查询时，必须在Where子句中包hash值和对应列值。

空间数据索引：

MyISAM表支持空间索引，可以用作地理数据存储。

全文索引

全文索引查找的是文本中的关键词，而不是直接比较索引中的值。

高性能索引策略

1. 独立的列

   索引列不能是表达式的一部分，也不能是函数的参数。

2. 前缀索引和索引选择性

   索引选择性 = 不重复的索引值 ÷ 数据表的记录总数

   当我们需要索引很长的字符列时，会导致索引变得很大且慢，一个策略是使用哈希索引，但这回导致引起哈希索引的一些限制，这时候我们可以使用前缀索引：索引列的前缀。

   前缀索引会导致索引选着性降低，所以我们需要经过统计选择合适的前缀长度。

   MySQL无法使用前缀索引做 ORDER BY 和GROUP BY ，也无法使用前缀索引做覆盖扫描。

3. 多列索引

   在多个列上创建 独立 的所有大部分情况下并不能提高MySQL的查询性能，MySQL5.0之后引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个 单列索引 来定位指定的行。

   当使用OR，AND或者两者结合使用时，可能会使用索引合并，但是索引合并通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并上。这部分时间优化器不会被计算到“查询成本”中。所以当EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构。

4. 选择合适的索引顺序

   将选择性高的列放在前面

5. 聚簇索引

   聚簇索引并不是一种单独的索引类型，而是一种 数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。

   InnoDB通过主键聚簇数据，如果没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

   聚簇索引优点：

   • 可以把相关数据保存在一起。

   • 数据访问更快

   • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值（因为此时索引的数据行指针是数据行的主键值）

   聚簇索引的缺点：

   • 极大限度提高了I/O密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没有那么重要了。

   • 插入速度严重依赖插入顺序。

   • 更新聚簇索引列的代价很高，会导致每一个被更新的行移动到新的位置

   • 插入新行或者主键被更新导致需要移动行的时候，可能会面临页分裂的问题。

   • 聚簇索引可能导致全表扫描变慢

   • 二级索引可能比想象中的要大

   • 二级索引访问需要两次索引查找

6. 覆盖索引

   如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”。

   优点：

   • 只需要读取索引，MySQL会极大的减少数据访问量

   • 对于I/O密集型应用范围查询会比随机从磁盘读取每一行数据的I/O要少的多。

   • 一些存储引擎，如MyISAM在内存中中缓存索引，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，覆盖索引可以避免系统调用

   • 覆盖索引对聚簇索引十分有用，因为二级索引中保存了行的主键值，所以如果二级索引如果能够覆盖查询，就可以避免对主键的二次索引。

7. 使用索引扫描排序

   MySQL有两种方式可以生成有序的结果：通过排序操作；或者按照索引顺序扫描。如果EXPLAIN出来的type列值为“index”，那么说明MySQL使用索引排序。

   只有当索引列的顺序与ORDER BY子句的顺序相同，且所有列的排序方向都相同时，MySQL才能够使用所迎来对结果做排序。如果查询需要关联多张表时，只有当ORDER BY子句的列全为第一张表是，才能使用索引来做排序。