MySQL 索引种类及优化

前言

在 MySQL 中，索引是在存储引擎层而不是服务器层中实现的，所以选择不同的存储引擎，索引的类型和实现也不同。同时按照不同的需求，索引的类别也不相同。

功能逻辑：普通索引、唯一索引、主键索引和全文索引；
物理结构：索引分为聚簇索引和非聚簇索引；
字段个数：索引分为单一索引和联合索引；
数据类型：Hash 索引、B+树索引；

索引分类

单一索引/联合索引

索引按照字段个数划分为单一索引和联合索引。索引为一列时为单一索引；多个列组合在一起时为联合索引。

联合索引存在最左匹配原则，按照最左优先的方式进行索引的匹配。比如(x, y, z)，如果查询条件为WHERE x = 1 AND y = 2 AND z = 3是可以匹配联合索引，但是如果使用WHERE y=2，就无法匹配联合索引。

B-树索引 / Hash 索引

B-树索引

大多数存储引擎都是用了 B-树作为默认的索引类型，但是实际上在技术里使用的是 B+树，例如 InnoDB。B-树索引之所以能加快查询数据的速度，是因为存储引擎不再需要扫描整表来查询需要的数据，取而代之的是从索引的根结点开始进行搜索。具体的工作原理：B-树/B+树

B-树索引适用于全键值、键值范围或者键前缀（最左前缀）查找。因为索引树中的结点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。

B-树索引的限制在于：

如果不是从索引的最左列开始查找，无法使用；
不能跳过索引中的列，否则只会使用跳过之前的索引列；
如果查询中有某个列的范围查询（中序遍历），则右边所有的列都无法使用索引优化查找；

Hash 索引

哈希索引基于哈希表实现，对于每一行数据，存储引擎都会对所有索引列计算一个哈希码并存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引有如下限制：

哈希索引只包含了哈希值和行记录指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
哈希索引不能进行范围查找，因为哈希索引指向的数据是无序的；
哈希索引同样不支持排序ORDER BY；
哈希索引中联合索引的部分索引无法使用，因为对于联合索引来说，哈希索引是将索引键合并在一起再计算哈希值，不会对每个索引键计算哈希值；
哈希冲突的量很大时，会影响读取的性能；

自适应 Hash 索引

MySQL 的 InnoDB 存储引擎还有个“自适应 Hash 索引”的功能，当某个索引值使用非常频繁的时候（即热数据），它会在 B+ 树索引的基础上再创建一个 Hash 索引。自适应 Hash 索引存储的是 B+树索引中的页面地址，目的在于快速定位到 B+树的叶子结点。当 B+树的深度越深，自适应 Hash 索引提高数据检索效率越明显。这中结构让 B+ 树也具备了 Hash 索引的优点，比如快速的哈希查找。

对比

哈希索引不能进行范围查询，而 B+树可以；
哈希索引不支持联合索引的最左侧原则，而 B+树可以；
哈希索引不支持ORDER BY，B+树的索引数据是有序的，所以可以排序；

聚簇索引 / 非聚簇索引（二级索引）

B-树索引类型都可以用在 MyISAM 和 InnoDB 上，但 InnoDB 有聚簇索引的特性而 MyISAM 没有。

聚簇索引

聚簇表示数据行和键值紧凑地存储在一起，因为无法同时将数据行存放在两个不同的地方，所以每一张 InnoDB 引擎表都只有一个聚簇索引。一般情况，聚簇索引就是主键索引（因为聚簇索引在有主键的情况下，默认指定主键为聚簇索引），在 B+树中，索引键值为主键，聚簇索引的叶子结点存储了整条记录的数据。

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

非聚簇索引（二级索引）

以主键以外的列值作为键值构建的 B+树索引为非聚簇索引（二级索引）。二级索引的叶子结点中保存的并不是指向行的物理地址指针，而是行的主键值。

区别

使用聚簇索引查找，记录和索引一起保存在了叶子节点中，所以数据的访问速度更快，同时节省了磁盘 I/O 资源。

通过二级索引查找到对应的记录，存储引擎需要得到二级索引的叶子结点中的主键值，然后根据该主键值到聚集索引中查到相对应的行。根据主键值回到聚集索引查找数据的过程称为回表。回表虽然会让二级索引占用更多的空间，但是优点在于记录移动时引擎可以减少二级索引的维护工作。

使用 InnoDB 存储引擎时应该尽可能地按主键顺序插入数据（可以使用AUTO_INCREMENT自增），最好避免随机的插入（例如使用 UUID 作为主键）。因为当主键的值是顺序的时，InnoDB 会把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子时（默认为 15/16），下一条记录就会写入新的页中。而每次插入主键的值近似于随机时，新纪录根据值的大小要被插入到现有索引页的中间某个合适位置，此时页分裂会导致大量的数据移动并产生碎片，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引使用倒排索引实现，它记录了关键词到其所在文档的映射。在相同的列上同时创建全文索引和基于值的 B-树索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

空间数据索引

MylSAM 存储引擎支持空间数据索引（R-Tree），可以用作地理数据存储。空间索引会从所有维度来索引数据，查询时可以有效利用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数来维护数据。

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则 MySQL 无法使用索引。

例如：SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引

在需要使用多个列作为条件查询时，使用多列索引比使用多个单列索引性能更好。

例如在一下语句中，最好将actor_id和film_id设置为多列索引：

1 2	SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

过滤因子描述了WHERE条件语句中每个条件的选择性，即满足这个条件列的记录和总记录数之间的比例。过滤因子的条件过滤能力越强，满足条件的记录数就越少，查询所需要扫描的的索引片就越小。

我们将选择性最强的过滤因子放在索引片的最前面，这样 MySQL 在查找时会过滤掉更多的行。

前缀索引

对于很长的字符串可以索引开始的部分字符，使得前缀的选择性接近于完整列的选择性。

覆盖索引

如果索引包含了所有需要查询的字段值，这个索引被称为“覆盖索引”。

不是所有类型的索引都可以作为覆盖作引，因为覆盖索引必须存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 中只能使用 B-Tree 索引做覆盖索引。当发起一个被索引覆盖查询时，在EXPLAIN的 Extra 列可以看到“Using index”的信息。

覆盖索引的优点在于：

对于 InnoDB 引擎，如果二级索引能够覆盖查询，就可以避免对主键索引的二次查询，即不需要回表操作；
索引条目远小于数据行大小，如果只需要读取索引，那 MySQL 会极大减少数据的访问量；
索引是按照列值顺序存储的，对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 少的多；
一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，比较费时。

使用索引排序

MySQL 中排序有两种方式：排序操作或按照索引顺序扫描。如果EXPLAIN出来的type为index，则说明 MySQL 使用了索引扫描来排序。

只有当索引列的顺序和ORDER BY子句的顺序完全一致时，并且所有列的排序顺序（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则当只有ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY的查询限制也需要满足索引的最左前缀，否则 MySQL 需要执行排序操作，而无法使用索引排序。

三星索引

一般来说，建立索引表会遵守一个三星索引的标准：

在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列。这样做的好处在于索引的过滤能力越强，需要扫描的行记录数量越少。
将 GROUP BY 和 ORDER BY 中的列加入到索引中。如果使用分组或者排序操作，都需要重新扫描数据记录。如果将分组或者排序涉及的列加入到索引中，就会按照索引的顺序来存储数据。
将 SELECT 字段中剩余的列加入到索引片中，避免出现回表的情况，提高查询效率。

三星索引可以作为理想化的索引，因为在实际设计时往往会有一下的限制：

三星索引的标准会让索引片变宽，在页大小相同的情况下，存储的索引数据会减少而页数量会增多。
索引维护的成本增大。

前言