MySQL索引类型及索引优化

索引(在MySQL中也叫做“键”)是存储引擎用于快速找到记录的一种数据结构。

索引的优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序、分组和临时表(临时表主要是在排序和分组过程中创建,因为不需要排序和分组,也就不需要创建临时表)
  • 索引可以将随机I/O变为顺序I/O(B-Tree索引是有序的,会将相邻的数据都存储在一起)

索引的类型

索引有很多种类型,可以为不同的场景提供更好的性能。在MySQL中,索引实在存储引擎层而不是在服务器层实现的。不同的存储引擎的索引工作方式也不一样。

B-Tree索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度快很多。

可以指定多个列作为索引列,多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找,其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找,则无法使用索引。

因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于索引中的ORDER BY操作。

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。根节点的槽存放了指向子节点的指针,存储引擎根据这些指针向下层查找。通过比较节点的页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值得上限和下限。最终存储引擎要么是找到对应的值,要么该记录不存在

B-Tree同样也有一些限制:

  • 如果不是按照索引的最左列开始查找,则无法使用索引
  • 不能跳过索引的列
  • 如果查询中有某个列的范围查找,则其右边所有列都无法使用索引优化查找

哈希索引

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

因为索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快,但是也有它的限制:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。
  • 无法用于排序。
  • 不支持部分索引列匹配查找。
  • 不支持范围查询。
  • 哈希冲突。
  • 如果哈希冲突很多的话,一些索引维护操作的代价也会很高

InnoDB索引有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时,它就会在内存中基于B-Tree索引值上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。这是一个完全自动的、内部的行为,用户无法控制或者配置,不过如果有必要,完全可以关闭该功能。

全文索引

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

空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引优化

独立的列

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

例如下面的查询不能使用 actor_id 列的索引:

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

应该为

1
SELECT actor_id FROM sakila.actor WHERE actor_id = 4;

多列索引

在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。例如下面的语句中,最好把 actor_id 和 film_id 设置为多列索引。

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

索引列的顺序

让选择性最强的索引列放在前面。索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。

前缀索引

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

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”,此时不需要回表操作。其具有以下优点:

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

聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据的存储方式。具有的细节依赖于其实现方式,但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

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

聚簇表示数据行和相邻的键值紧凑地存储在一起,因为无法同时把数据行存放在两个不同的地方,所以每张Innodb引擎表都只有一个聚簇索引。一般情况,聚簇索引就是主键索引(因为聚簇索引在有主键的情况下,默认指定主键为聚簇索引),而非聚簇索引都是二级索引。

如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。采用聚簇索引,索引和其他列值存储在一起,因此数据访问比采用非聚簇索引(如MyISAM引擎)更快,节省了磁盘I/O资源。

二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行。这样虽然会让二级索引占用更多的空间,但换来的好处是InnoDB在移动行时减少了二级索引的维护工作。

MyISAM没有聚簇索引的特性,主键索引和其它索引在结构上没有什么不同。

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

使用索引扫描来排序

MySQL有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index,则说明MySQL使用了索引扫描来做排序。

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

参考资料

  • 高性能 MySQL[M]. 电子工业出版社, 2013.