MySQL 索引优化

索引优化是对查询性能优化最有效的手段,这篇文章就来研究下 MySQL 的索引。在介绍优化索引的方法之前,会先介绍索引是如何工作的,我们应当根据这些理解来创建最合适的索引,而不是一些经验法则。

索引类型

MySQL 中不同存储引擎使用的索引不同,支持的索引的类型也不同。

B-Tree 索引

InnoDB 使用的是 B+Tree,它的优点是 IO 读取次数少,适用于硬盘这种瓶颈在于 IO 的设备。B+Tree 的非叶子节点存的是 key(不包含指向数据的指针)。我们学数据结构的时候,这个 key 一般是个整数,这样就能自然地进行排序;但实际应用中,key 不一定非得是整数,我们也还可以组合多个 key 成为一个组合的 key,这就是所谓的索引,例如某个索引可以由 last_name, first_name, date_of_birth 三个键组成。

这里需要关注的一点就是索引列的顺序,因为查询只能使用索引的最左前缀,直到遇到第一个范围条件列,不能跳过某个索引列。以索引 ABC 为例,我们可以查询:

  1. 严格匹配 ABC 三个列
  2. 严格匹配 A
  3. 范围匹配 A(可以是前缀,也可以是某个取值范围)
  4. 严格匹配 A,范围匹配 B

所以对于同样的三个索引列,有时我们可能需要 ABC, BAC, CAB 等多个不同组合顺序的索引来满足我们的查询优化。

除此之外,B+Tree 有个特殊的地方是索引和实际的数据是分开存储的,所以我们可以只访问索引,不访问数据,这样可以实现后面要讲的索引覆盖查询,可以大大提高查询效率。

哈希索引

学数据结构的时候,我们知道实现 Map 有哈希表和搜索树两种方式(前面 B-Tree 也是一种搜索树),并且根据经验,哈希表一般要比搜索树更高效,但这些比较都是脱离了 IO 等因素来说的,也就是说,更准确的说法是只在内存中操作的话,哈希表比搜索树更高效(如果是基于硬盘,前面的 B-Tree 更高效)。由于这些原因,MySQL 中只有 Memory 引擎显示支持哈希索引。

哈希表中存的键是索引列的哈希值(并不是索引列本身的内容),表也是按照哈希值的顺序存储的,所以哈希索引只适用于快速的随机读取,不能按索引列的值的顺序进行排序、匹配、范围查询等顺序操作。

除此之外,我们可以按照这个思路在 InnoDB 等引擎中自定义哈希索引列来加快某些列的查询速度。例如 url,对 url 这种长的字符串进行匹配比较耗时,我们可以增加一个 url_crc=CRC32(url) 的列存放 url 的哈希值,查询的时候使用以下语句:

1
******** WHERE url_crc=CRC32("XXX") AND url="XXX";

url_crc 和 url 组成一个多列的哈希索引,url 主要是用来防止哈希碰撞的,进行索引选择的主要是 url_crc。

这种方式的实现可以使用触发器,让 url 更新或插入操作来触发哈希值的计算和设置。

其他索引

主要有空间数据索引和全文索引。空间索引做的比较好的是 PostgreSQL 的 PostGIS。全文索引让搜索引擎来做更合适,例如 ElasticSearch。

索引的优缺点

索引主要有三个优点:

  1. 减少需要遍历的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随机 I/O 变为顺序 I/O

索引的缺点:

  1. 索引占据额外的空间
  2. 对于非常小的表,全表遍历一般更高效,这时候就也没必要有索引。

评价索引好坏的「三星系统」:

  1. 索引将相关的记录放在一起
  2. 索引中的数据顺序和查询中的排列顺序一致
  3. 索引中的列包含了查询中需要的全部列

高性能的索引策略

前缀索引和索引选择性

1
索引选择性 = 不重复的索引值数目(Cardinality) / 记录总数

例如,我们有 10 组有关年龄的数据:

1
2
3
4
5
6
7
8
9
10
11
年龄, 其他列
12, ***
23, ***
23, ***
40, ***
51, ***
54, ***
62, ***
62, ***
71, ***
93, ***

如果用年龄作为索引,那么以上不重复的索引的个数是 8,索引选择性就等于 0.8。

如果记录总数记为 T,那么索引选择性的取值范围就是 1/T 到 1 之间。1 代表没有重复的索引,这种索引选择性最高,性能也就最好。

对于字符串,我们经常要用前缀索引,这个时候就需要截断。如果截得太短,例如只截一个字母,那么对于英文来说不重复的索引值数目就只有 26 个,这时候索引选择性就不好(相当于很多哈希碰撞),所以我们要尝试各种截取的长度,来测试索引选择性如何,最后得到一个既保证较高选择性,又不太长的前缀索引。

多列索引

多列索引常见的误区是为每个列单独设立索引,例如有 A, B, C 三个列,索引 A、索引 B、索引 C 这三个不是多列索引,而是三个单独的索引;索引 AC 或索引 BAC 这种才是多列索引。

在当前版本的 MySQL 中,类似 WHERE A=** OR B=*** 的查询,如果没有多列索引,只有单列索引 A 和单列索引 B,那么我们 EXPLAIN 这个查询语句之后,会发现有 union 等字样,这表明它会用这个两个单列索引,并且把它们查询结果合并。这时候我们就应该考虑用多列索引 AB 来优化这个查询。

多列索引中的索引列的顺序

对于 WHERE A=*** AND B=*** 这样的 SQL 语句,SQL 语句中的 A 和 B 的顺序是无所谓的,最主要的是在建立 AB 多列索引的时候,A 和 B 的先后顺序是有讲究的。

如果只用于优化上面的 WHERE 语句,那么应当把索引选择性高的那个列放在前面。除此之外,为满足 ORDER BY, GROUP BY, DISTINCT 等子句的查询需求,我们应当根据这些查询语句,来确定正确的索引列的顺序,来满足排序和分组的需要。

聚簇索引(Clustered Index)

聚簇索引是一种数据存储方式,这里只讨论 InnoDb 的实现,它的存储类似于 B-Tree。当有聚簇索引的时候,数据在物理上是按照该索引的顺序连续存储的。因此,当按照聚簇索引进行遍历的时候,系统可以一次 I/O 读取一整页的数据(包括很多行数据),这样就大大节省了 I/O 的次数。

用户是无法指定哪个列作为聚簇索引的。InnoDB 会根据以下优先级选择聚簇索引:

  1. PRIMARY KEY
  2. 被 UNIQUE 和 NOT NULL 修饰的索引
  3. InnoDB 自己隐式定义的主键

因此,对于大多数有主键的表,聚簇索引和主键两者是相同的。

聚簇索引的主要优点有:

  1. 相关数据可以在物理上保存在一起
  2. 数据访问速度更快
  3. 覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点:

  1. 聚簇索引主要是减少 I/O 的次数,这对于内存型的引擎并不重要
  2. 插入速度依赖于插入顺序,如果不是按照聚簇索引的顺序进行插入的,速度就会较慢。
  3. 更新聚簇索引列的代价很高,需要在物理上移动数据行

二级索引(Secondary Index)

所有除了聚簇索引之外的索引都是二级索引,例如一个用户表,id 是主键,那么 id 就是聚簇索引。如果还用 name 列建了索引,那么这个就是二级索引。

为什么叫「二级索引」呢?因为所有的二级索引都必须包含聚簇索引,InnoDB 是通过聚簇索引来搜索指定行的。从数据结构上看,二级索引叶子节点保存的是行的主键值,而不是指向行的物理位置的指针。

例如索引(name),它实际包含了两列:id 和 name,我们先通过 name 找到相应的 id,再通过 id 获得相应的数据行。

1
name="Yinyin Qian" --> id=12 --> physical_address = 0x23957204

InnoDB 和 MyISAM 的数据分布对比

这部分需要图片作为辅助理解,这里只是简单说下结论。

MyISAM

这个引擎中数据在物理上是按照插入顺序进行排列的。以下以「行指针」来指代行的物理位置的指针。

对于索引,主键索引和其他索引没有差别(只多了 UNIQUE 和 NOT NULL 的要求),索引的叶子节点包含的都是「行指针」,没有二级索引那样的两次查询,但索引的顺序和物理上存储的顺序是不同的,所以也就意味着没有聚簇索引。

InnoDB

InnoDB 的前面实际已经阐述了,聚簇索引(主键索引)的叶子节点存储的是「行指针」,而二级索引的叶子节点存储的是主键值。二级索引查询在内部需要先查询主键值,再查询得到「行指针」

InnoDB 表中按主键顺序插入行

我们一般用自增的 int/long 型的列作为主键,有时也用 UUID,但从性能的角度来看,UUID 作为主键、继而成为聚簇索引并不合适,因为 UUID 是无序的,这导致每次插入的位置都是无序的。在用 UUID 作为主键的时候,在插入大量数据之后,也许需要进行 OPTIMIZE TABLE。

因此,使用 InnoDB 时应该尽可能地按主键顺序插入数据,并且尽可能地使用单调增加的聚簇键来插入新行。

覆盖索引

覆盖索引指的是我们通过索引来直接获取列的数据,这样就不用找到数据行并提取其中数据了,节省了 I/O 操作的次数,例如:

1
SELECT store_id, film_id FROM sakila.inventory;

如果 store_id 和 film_id 都建了索引,并且我们也只需要查询这两项,那么索引就覆盖了查询,这种情况下可以极大地减少数据访问量。

如果存在索引覆盖,我们通过 EXPLAIN 可以看到 Using index 的字眼。

使用索引扫描来做排序

一个索引可以同时满足排序和查找行两个要求。例如 ORDER BY inventory_id DESC,如果其中 inventory_id 建了索引,就能用扫描索引来进行排序。

冗余和重复的索引

在一些情况下我们会不经意地建立冗余或重复的索引:

  1. 主键不需要被 UNIQUE 标注,也不需要被 INDEX 标注;UNIQUE 的列也不需要被 INDEX 标注。因为主键和 UNIQUE 都是通过索引来实现的,如果给主键又标 UNIQUE,又标 INDEX,就建了三个索引了。

  2. 创建索引(A, B),再创建索引(A)就是冗余的,但是索引(B, A)和(B)都不是冗余的

  3. 索引(A, ID)中的 ID 是冗余的,因为 ID 是主键,二级索引本身就隐含了主键了。

重复索引一般没什么用处,并且索引越多插入速度就越慢。

索引和锁

InnoDB 使用的是行级锁,如果我们能通过索引告诉哪些行是需要遍历的,那么就可以只加锁这些行。具体使用方式就不细说了。

维护索引和表

维护主要有三个目的:

  1. 找到并修复损坏的表:使用 CHECK TABLE 和 REPAIR TABLE 来实现
  2. 维护准确的索引统计信息:使用 ANALYZE TABLE
  3. 减少碎片:使用 OPTIMIZE TABLE

参考资料:「高性能 MySQL」