高性能索引

绝地灬酷狼 2023-01-08 03:25 124阅读 0赞

> 时光，总是恬淡的，君不见，那翻阅过的书，充满了淡淡的清香，即便有褶皱，有划痕，也无形中沉淀了书的厚重

### 前言 ###

索引是存储引擎用于快速查询记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。一个好的索引能够轻易的将查询性能提升几个数量级，“ 最优 ” 的索引有时比一个 “ 较好 ” 的索引性能还要好两个数量级。所以，如何创建一个真正 “ 最优 ” 的索引就成了提升性能必须要面对的一个问题

### 正文 ###

索引可以让服务器快速的定位到表的指定位置，但这并不是索引的唯一作用。根据创建索引的数据结构不同，索引也有一些其他的附加作用。

以最常见的 B-Tree 索引为例。

*  按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作
 *  因为数据有序，所以 B-Tree 也就会将相关的列值都存储在一起。
 *  因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

可以根据 “三星系统” 来作为一个索引是否适合某个查询的评判

“ 三星系统 ”：索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果覆盖索引则获得三星 ——《Relational Database Index Design and the Optimizers（Wiley 出版社）》

##### 索引的优点 #####

索引之于数据库系统，就如同风之于鸟，水之于鱼，阳光之于树木，星辰之于夜空。  
对于数据库系统来讲，索引的重要性怎么吹都不过分。但如果你想概括地讲索引的优点，大约可以从以下几点切入

*  索引大大减少了服务器需要扫描的数据量
 *  索引可以帮助服务器避免排序和临时表
 *  索引可以将随机 I/O 变为顺序 I/O

##### 高性能索引策略 #####

但是索引并不是在任何时候都是一个好东西，有时候索引并不能提升多少查询效率，反而给存储引擎带来很多额外的麻烦。所以，如何创建出高性能的索引就成为了我们不得不面对的问题

切记：只有能帮助存储引擎快速查找记录带来的好处大于其带来的额外工作时，索引才是好东西

> 独立的列

并不是创建了索引，MySQL 在查询时就一定能够使用。我们经常会看到一些查询不当的使用索引，或者使得 MySQL 无法使用已有的索引。如果查询中的列不是独立的，则该次查询就无法使用索引

如：`select id from test_table where id + 1 = 5;` 就不能使用索引，因为 MySQL 无法自动解析这个方程式。如果我们将这条语句改写成 `select id from test_table where id = 4;` 就可以使用索引（假设在 id 列创建了索引）。像这种情况在编写 SQL 的时候需要注意

独立的列：指索引列不能是表达式的一部分，也不能是函数的参数

> 前缀索引和索引选择性

有时候需要建索引的列值是很长的字符串，这会让索引变得大且慢。

有两种策略可以优化此类问题：

*  模拟哈希索引
 *  建立前缀索引

前缀索引：指的是只索引开始的部分字符。这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性。这需要平衡索引选择性和索引空间【一个好的前缀索引应该在节约索引空间的同时，保证较高的索引选择性】。

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

索引的选择性是指：不重复的索引值 和 数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行（从索引选择性的角度来讲，唯一索引的性能最好）

计算合适的前缀长度有两种办法可供参考。一个办法是将常见的值列出来，然后找到那个最合适的前缀长度。另一个办法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

计算完整列的选择性：select count(distinct <field\_name>)/count( \* ) from <table\_name>  
计算前缀长度为3的选择性：select count(distinct left(<field\_name>,3))/count(\*) from <table\_name>

> 联合索引和最左原则

每次查询最多只能使用一个索引。对于查询条件有多个的时候，联合索引往往会有出人意料的表现。不过，并不是每一个联合索引都比单列索引优秀，这里需要注意一个原则——最左原则。

最左原则：最左优先，以最左边的列为起点，任何连续的列都能走索引。例：如果存在联合索引 union\_index(a, b)，则 select a,b from test\_table where a = 12; 或者 select a,b from test\_table where b = 12 and a = 23; 都能够走该索引，且不需要回表

另外，这里还需要注意的是，当联合索引左边的列被用作范围查询时，右边的列则不能够走索引，例如 `select a, b from test_table where a < 5 and b = 56;` 就只有列 a 能够走索引

一个经验法则是：在设计联合索引时，将选择性最高的列放到索引最左边（最左原则）。当不需要考虑排序和分组时，这个方法通常是很好的。

切勿将最左原则和代码顺序混为一谈。假如有索引 union\_index(a, b)，那么不管你是 where a= 12 and b = 33; 还是 where b= 33 and a = 12; 都能匹配到该索引

> 覆盖索引（USing index）

通常大家都会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。优秀的索引设计应该考虑到整个查询，而不仅仅是 WHERE 条件部分。如果索引的叶子节点中已经包含要查询的数据，那么此次查询将不需要回表

覆盖索引的好处：

*  索引条目通常远小于数据行大小，所以如果只需要读取索引，那么 MySQL 就会极大的减少数据访问量
 *  索引是按照列值顺序存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多
 *  一些存储引擎（如：MyISAM）在内存中只缓存索引，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题，如果覆盖索引，可避免此类问题
 *  对于 InnoDB 来说，还可以避免对主键索引的二次查询（回表）

> 索引条件下推（ index condition pushdown ）【NDB存储引擎】

如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行（这基本都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在 I/O 密集型的工作负载时）。

但是启用索引条件下推之后，就可以把查询发送到数据上（存储引擎层），大大改善查询所需时间。不过索引条件下推只有 NDB存储引擎支持，且还有诸多限制

> 使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果 EXPLAIN 出来的 type 列的值为 index ，则说明使用了索引扫描来做排序（ MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能的话，设计索引时应该尽可能同时满足这两种任务）

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。

另外，ORDER BY 子句也需要满足最左前缀原则。但是有一种情况 ORDER BY 子句可以不满足索引的最左前缀要求，就是前导列为常量的时候

如果需要按不同方向做排序，一个技巧是存储该列值的反转串或者相反数；另外一个技巧是，使用降序索引【仅 InnoDB引擎支持】（MySQL 8.0 ↑）

注：如果同时存在 ORDER BY 子句 和 WHERE 子句，则需要满足两个子句中的列都存在同一个索引，且满足最左前缀才能使用索引做排序（ WHERE 列中不能存在范围查询，IN 条件也不行 ）

> 压缩（前缀压缩）索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能。（默认只压缩字符串，但通过参数设置也可以对整数做压缩）

> 冗余和重复索引

重复索引是指在**相同的列上**按照**相同的顺序**创建的**相同类型**的索引，应该极力避免（优化器在优化查询时需要逐个进行考虑，INSERT、UPDATE、DELETE等操作时也需要同步维护索引。这会影响性能）

冗余索引：如果创建了索引（A, B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引

注：大多数时候都不需要冗余索引，但也有例外。例如：在一个整数列上有一个索引，现在需要额外增加一个很长的 VARCHAR 列来拓展该索引，那性能可能会急剧下降

由于 InnoDB 的二级索引叶子结点保存的是主键值（假设为 ID），则二级索引（A）就相当于（A, ID）。像 WHERE A = 2 ORDER BY ID 这样的查询，就能覆盖索引。如果存在索引（A, B），就相当于（A, B, ID）。那么上面的 ORDER BY 子句就无法使用该索引做排序，而只能用文件排序了）

> 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，这样的索引完全是累赘。建议考虑删除

注：有些索引的功能相当于唯一约束，虽然该索引一直没有被查询使用，却可能是用于防止产生重复数据的

> 索引和锁

索引可以让查询锁定更少的行，这对性能有好处。（虽然 InnoDB 的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；另外，锁定超过需要的行会增加锁争用，减少并发性）

InnoDB 只有在访问行的时候才会对其加锁，而索引在绝大多数时候能够减少 InnoDB 访问的行数，从而减少锁的数量

关于 InnoDB、索引和锁有一些很少有人知道的细节：InnoDB在二级索引上使用 共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多

> 尝试在选择性不高的列上建立索引

一般来讲，在有更多不同值的列上创建索引的选择性会更好（大多数情况下这样做都是对的，因为这可以让 MySQL 更有效的过滤掉不需要的行）。但是列的选择性并不是唯一考虑的因素，还应该考虑诸如 列使用的频率（例如：sex 列的选择性肯定很低，但是如果 sex 列会在很多查询中用到的话，还是建议在创建不容组合索引的时候将 sex 列作为前缀）

一个小诀窍：如果某个查询查询没有筛选性别，可以通过在查询条件中新增 AND SEX IN( ‘m’, ‘f’) ，使得此条查询走该索引。（这样写并不会过滤任何行，但是可以让查询满足最左前缀原则）。但是这种技巧也不能滥用，因为每额外增加一个 IN 条件，优化器需要做的组合都将以指数形式增加。需要更多的内存，消耗更多的时间，最终可能会极大地降低查询性能

> 考虑将经常用于范围查询的列放到最后

经常用于范围查询的列（如：age）应该考虑放到联合索引的最后。因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。另外，也可以使用 IN 来代替范围查询，例如要查询年龄在15 ~ 20 之间的数据，可以使用 IN（15,16,17,18,19,20）代替 BETWEEN AND 语句，使得查询满足最左前缀。

但不是所有的范围查询都可以转换，这里描述的原则是，尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列

> 避免多个范围条件

从 EXPLAIN 的输出很难区分 MySQL 是要查询范围值，还是查询列表值。EXPLAIN 使用同样的词 “ range ” 来描述这两种情况（例如：`id < 5` 和 `id in(1,2,3,4)` 。EXPLAIN 的 type 列都是 “ range ”），但是这两种访问效率是不同的。对于范围条件查询，MySQL 无法再使用范围列后面的其他索引列了，但是对于 “ 多个等值条件查询 ”则没有这个限制。

注：查询列表值 = 多个等值条件查询

> 优化排序

使用文件排序对小数据集是很快的，但如果一个查询匹配的结果很大（如：上百万行）的话，将会很慢。

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建（sex, score）索引用于下面的查询 `select cols from test_table where sex = 1 order by score limit 10;` 。这个查询同时使用了 ORDER BY 和 LIMIT，如果没有索引的话会很慢

> 延迟关联

通常我们习惯于使用 LIMIT 对结果集进行分页，这在请求靠前的页码时是个不错的主意。

但是，如果数据量非常大，并且请求靠后的页码时，即使有索引，也可能出现严重的性能问题（因为 LIMIT 分页实际上是把前面所有页的数据也请求了一次，只是在返回结果之前丢掉了）。随着偏移量的增加，MySQL 需要花费大量的时间来扫描需要丢弃的数据。这里有两个方法可以避免此类问题：

一个方法是限制用户能够翻页的数量，因为用户很少会真正在乎搜索结果的第 10000 页。  
另一个比较好的方法是使用延迟关联。通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行（这可以减少 MySQL 扫描那些需要丢弃的行数）。假设 test\_table 表有联合索引 union\_index(a, b)，id 为主键列，则查询语句 `select <cols> from test_table where a = 12 order by b limit 100000, 10;` 改写成 `select <cols> from test_table inner join ( select id from test_table where x.a = 12 order by b limit 100000, 10) as x using( id );` ，将极大的提高查询速度

##### 写在最后 #####

本文摘自《高性能MySQL（第3版）》，这是一本非常好的 MySQL 相关读物，建议感兴趣的同学可以看看

#### 参考文献 ####

《高性能MySQL（第3版）》

[MySQL官方文档][MySQL]

[MySQL]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html