那些年我们一起优化的SQL

随着业务不断迭代，系统中出现了较多的SQL慢查。慢查虽不致命，但会让商家感知到系统较慢，影响使用体验。在进行慢查优化过程中，我们积累了一些经验。本文将基于我们的实战经历，讲解工作中比较常见的慢查原因，以及如何去优化。

本文讲解基于MySQL 5.7。

本节主要针对常见的慢查进行分类，讲解怎么去优化。

2.1 建立索引的正确姿势

数据量较大的时候，如果没有索引，那么数据库只能全表一行一行的遍历判断数据，因此优化SQL的时候，第一步要做的就是确定有没有合适的可用的索引。在业务本身比较复杂的情况下，一个表会涉及各种各样的查询方式，因此我们需要建立各种各样的索引去提高查询。然而索引数量过多又会影响增删改的效率，并且也会占用更多额外的空间去存储索引，因此我们要懂得怎么去正确的建立索引，避免索引滥用。

2.1.1 索引不要包含选择性过低字段

选择性过低，即通过该字段只能过滤掉少部分的数据，是没必要建立索引的，因为如果该数据只是占小部分，即使没有索引直接查询数据表也不用过多的遍历即可找到目标数据，没有必要基于索引查询。

SQL：

select * from my_table where col_a=1 and col_b=1

索引：
index (col_a,col_b)

col_b为逻辑删除字段，包含0未删除和1已删除，数据库中的值只有很少量部分是逻辑删除的。但是在业务中我们一般都只查未删除的，那么这种情况col_b是完全不必要在索引中的，可以把col_b从组合索引中去掉。

2.1.2 选择性高的字段前置或者单独建立索引

SQL：

索引：
index(col_a,col_b,col_c)

假设选择性col_c>col_b>col_a，抛开业务本身需要，组合索引建立的顺序尽可能建为index(col_c,col_b,col_a)。

原因是组合索引底层的存储先按照第一个进行排序，第一个字段相同再按照第二字段排序，如果选择性低的字段放在前面，因此选择性高的字段放前面相对而言IO的次数可能会减少一些。比如基于第一个字段过滤完会有10000条数据，基于第二个字段过滤完只有100条数据，如果先查第一个字段则需要在10000条数据的基础上再进行过滤查询，而基于第二字段过滤完只需要基于100条数据进行过滤查询即可。

而如果col_c选择性特别高，经过col_c过滤后只剩下极少的数据甚至一条数据，单独建立col_c索引就可以。

需要注意的是这个规则特别提到了抛开业务本身需要，比如如果col_a选择性比col_b高一点相差不大，但是col_b在业务场景中通用性更强，那么col_b放在前面更合适,可以减少创建的索引。

2.1.3 尽量使用覆盖索引

SQL：

索引：
index(col_a,col_b)

如果col_a和col_b过滤完后还有大量数据，那么建议建一个index(col_a,col_b,col_c)索引，否则MySQL需要通过大量回表去查询col_c的数据再去求和。

2.1.4 小结

1、选择性低的字段不用建立索引。

2、具有唯一性或者高选择性的字段无需与其他字段建立组合索引。

3、除了业务需求上的考虑，尽量将选择性高的索引字段前置。

4、在经过索引过滤后数据量依旧很大的情况下可以考虑通过覆盖索引优化。

2.2 使用索引的正确姿势

除了SQL本身没有适用的索引，有了相关的索引但是对应的索引没有生效是比较常见的情况，以下列举一些常见的失效场景，在日常的开发中，我们要尽量避免。

需要注意的是，索引失效这里指的是没有利用到索引的二分查询进行数据过滤。因为存在ICP，所以会存在触发了失效场景执行计划还是显示使用了索引的情况。

2.2.1 最左匹配截断

SQL：

索引:
index(col_a,col_b)

组合索引的匹配规则是从左往右匹配，无论是作为过滤条件还是排序条件都要遵循这个原则。如果要使用col_b字段走索引，查询条件则必须要携带col_a字段。

补充说明:
1、col_b作为排序字段如果要走索引，只要保证组合索引中col_b前面的字段都可以包含在过滤条件或者排序条件中即可，也不需要保证col_b作为组合索引中的最后一个字段。

比如：

col_a和col_b都走不了索引，因为col_a在组合索引左边，但是col_a不在查询条件中。

2.2.2 隐式转换

字段类型：
col_a(VARCHAR)
col_b(DATETIME)

索引：
index1(col_a)
index2(col_b)

SQL：

失效原因
字段类型和查询数据的值类型不一致，会导致字段上的索引失效。

• col_a是字符类型，使用了数字类型进行查询。

• col_b是datetime类型，针对datetime/date/time类型，MySQL增删查改都要基于字符串形式日期去处理，否则MySQL就需要额外进行转换。（虽然底层储存的是数字类型，但是并不是存储时间戳，底层是处理是统一将外部传入的字符串进行转换，比如是date类型通过将 “2021-12-01” 字符串转数字 20211201 这种形式去存储）。

2.2.3 in + order by 导致排序失效

索引：
index(col_a,col_b)

SQL：

解决方式：

• 如果col_a的过滤性不高，在组合索引中可以通过将col_b字段前置，将col_a移动到组合索引后面，只用于避免或减少回表。

• 如果col_a的过滤性高，过滤后的数据相对较少，则维持当前的索引即可，剩余不多的数据通过filesort进行排序。

• 如果存在大量数据，并且经过col_b过滤后还是存在大量数据，建议基于别的数据存储实现，比如Elasticsearch。

另外SQL建议调整为只查询id（或者其他已经在索引中的字段），再根据id去查对应的数据。可以促使SQL走覆盖索引进一步优化、也可以促使MySQL底层在进行filesort使用更优的排序算法。

2.2.4 范围查询阻断组合索引

索引：
index(col_a,col_b)

SQL：

解决方式：
可以调整下索引顺序，col_a放在最后面。index(col_b,col_a)

2.2.5 后缀匹配不能走索引

索引:
index(col_a,col_b)

SQL：

以上SQL会导致索引失效。前缀匹配比如name%是可以走索引的，但是后缀匹配比如%name会导致没办法基于索引树进行二分查找。如果需要进行后缀匹配，数据量较大建议基于Elasticsearch实现。

2.2.6 or查询导致失效

索引：
index(col_a,col_b)

SQL：

or查询会导致索引失效，可以将col_a和col_b分别建立索引，利用Mysql的index merge(索引合并)进行优化。本质上是分别两个字段分别走各自索引查出对应的数据，再将数据进行合并。

2.2.7 使用函数查询或运算

索引：
index(col_a,col_b)

SQL：

2.2.8 不等于、不包含(只用到ICP)

索引：
index(col_a,col_b,col_c)

SQL：

2.2.9 选择性过低，直接走全表

选择性过低会导致索引失效。由于通过二级索引查询后还有回表查询的开销，如果通过该字段只能过滤少量的数据，整体上还不如直接查询数据表的性能，则MySQL会放弃这个索引，直接使用全表扫描。底层会根据表大小、IO块大小、行数等信息进行评估决定。

索引：

index(col_a)

SQL：

2.2.10 asc和desc混用

索引：
index(col_a,col_b,col_c)

SQL：

desc 和asc混用时会导致索引失效，不建议混用。

2.2.11 小结

根据以上例子，总结几个索引失效的场景：

1. 组合索引左匹配原则

2. 发生隐式转换

3. 组合索引，in + order by in会阻断排序用索引

4. 范围查询会阻断组合索引,索引涉及到范围查询的索引字段要放在组合索引的最后面。

5. 前模糊匹配导致索引失效

6. or查询，查询条件部分有索引，部分无索引，导致索引失效。

7. 查询条件使用了函数运算、四则运算等。

8. 使用了!=、not in

9. 选择性过低

10. asc和desc混用

2.3 编写SQL的正确姿势

懂得怎么建立索引，也懂得了怎么避免索引失效，但是有些场景即使索引走对了，也会慢查，这时我们要考虑优化我们SQL写法。

2.3.1 深分页

索引：

index(col_c)

SQL：

覆盖索引： 如果业务需要的字段比较少，可以通过保证SQL查询的字段和查询条件都在索引上，避免回表。

延迟关联： 通过延迟关联，通过编写完全基于索引查询数据的SQL，再根据id查询详细的字段数据。

2.3.2 order by id

索引：
index(col_a)

SQL：

MySQL INNODB二级索引最终叶子结点引用的都是主键id，因此我们可以利用这个点去使用id排序。

但是在本场景中，col_b截断了索引，导致SQL没法利用id进行索引排序。而主键索引的权重会比较高，可能会导致MySQL没有正确选择索引，从而选择了可能性能更差的主键索引去做排序，查询条件通过遍历扫描数据。

因此在不能保证id排序可以走索引的情况下，建议改用其他字段排序。如果查询结果集确定会比较少排序字段可以不在索引上，如果结果集较大还是要把排序字段加到索引中。

在掌握了SQL优化的理论知识之后，我们怎么验证编写的SQL是否有按照预期使用了比较合适的索引？这就需要学会分析SQL的执行情况。

执行计划：我们可以通过explain关键字查看SQL的执行计划，通过执行计划分析SQL的执行情况。

执行计划字段描述：

extra字段常见值：

using index condition补充说明：
using index condition表示使用了ICP（Index Condition Pushdown索引下推），ICP是MySQL 5.6之后新增的特性，部分字段因为某些情况无法走索引查询，则会展示using where（在server层进行数据过滤），ICP是在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据过滤调一部分数据。

using where 和 using index condition的区别：

为什么需要ICP机制：

索引:

index(col_a,col_b)

SQL：

如果没有using index condtion，col_a会走索引查询，匹配到对应的数据后，回表查出剩余字段信息，再去匹配col_b。假设col_a过滤后还有10000条数据，而通过col_b过滤后只会剩余1000条数据，则有9000条的数据是没必要的回表。

本质上索引树上是包含col_b字段的，只是col_b不能利用索引树二分查找特性（因为使用了前模糊匹配），但是可以利用索引上现有的数据进行遍历，减少无效回表。有了ICP后，基于索引就可以过滤col_a和col_b字段，过滤后只会剩下1000条数据，减少了大量的回表操作。

小结：
通过执行计划我们可以分析出SQL最终使用了什么索引，对索引的使用是处于什么情况，进而可以得出还有没有优化空间。