一、背景
随着业务不断迭代,系统中出现了较多的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:
select * from my_table where col_a=1 and col_b=1 and col_c=1
索引:
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:
SELECT sum(col_c) FROM my_table where col_a=1 and col_b=1
索引:
index(col_a,col_b)
如果col_a和col_b过滤完后还有大量数据,那么建议建一个 index(col_a,col_b , col_c) 索引,否则MySQL需要通过大量回表去查询col_c的数据再去求和。
2.1.4 小结
1、选择性低的字段不用建立索引。
2