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」