关于sql:一文带你搞懂如何优化慢SQL

作者：京东科技 宋慧超

一、前言

最近通过 SGM 监控发现有两个 SQL 的执行工夫占该工作总执行工夫的 90%，通过对该 SQL 进行剖析和优化的过程中，又从新对 SQL 语句的执行程序和 SQL 语句的执行打算进行了系统性的学习，整顿的相干学习和总结如下；

二、SQL 语句执行程序

要想优化慢 SQL 语句首先须要理解 SQL 语句的执行程序，SQL 语句中的各关键词执行程序如下：

◦首先执行from、join 来确定表之间的连贯关系，失去初步的数据。

◦而后利用 where 关键字前面的条件对符合条件的语句进行筛选。

from&join&where：用于确定要查问的表的范畴，波及到哪些表。

抉择 一张表，而后用 join 连贯：

from table1 join table2 on table1.id=table2.id

抉择 多张表，用 where 做关联条件：

   from table1,table2 where table1.id=table2.id

最终会失去满足关联条件的两张表的数据，不加关联条件会呈现笛卡尔积。

◦而后利用 group by 对数据进行分组。

依照 SQL 语句中的分组条件对数据进行分组，然而不会筛选数据。

上面用依照 id 的 奇偶 进行分组：

◦而后分组后的数据别离执行 having 中的一般筛选或者聚合函数筛选。

having&where

having中能够是一般条件的筛选，也能是聚合函数，而 where 中只能是一般函数；个别状况下，有 having 能够不写 where，把where 的筛选放在 having 里，SQL 语句看上去更丝滑。

应用 where 再group by：先把不满足 where 条件的数据删除，再去分组。

应用 group by 在having：先分组再删除不满足having 条件的数据。（该两种简直没有区别）

比方举例如下：100/2=50，此时咱们把 100 拆分 (10+10+10+10+10…)/2=5+5+5+…+5=50, 只有筛选条件没变，即使是分组了也得满足筛选条件，所以where 后group by 和 group by 再having是不影响后果的！

不同的是，having语法反对聚合函数, 其实 having 的意思就是针对每组的条件进行筛选。咱们之前看到了一般的筛选条件是不影响的，然而 having 还反对聚合函数，这是 where 无奈实现的。

以后的数据分组状况

执行 having 的筛选条件，能够应用聚合函数。筛选掉工资小于各组平均工资的having salary<avg(salary)：

而后再依据咱们要的数据进行 select，一般字段查问或者聚合函数查问，如果是聚合函数，select 的查问后果会减少一条字段。

分组完结之后，咱们再执行 select 语句，因为聚合函数是依赖于分组的，聚合函数会独自新增一个查问进去的字段，这里咱们两个 id 反复了，咱们就保留一个 id，反复字段名须要指向来自哪张表，否则会呈现唯一性问题。最初依照用户名去重。

select employee.id,distinct name,salary, avg(salary)

将各组 having 之后的数据再合并数据。

◦而后将查问到的数据后果利用 distinct 关键字去重。

◦而后合并各个分组的查问后果，依照 order by 的条件进行排序。

比方这里依照 id 排序。如果此时有 limit 那么查问到相应的咱们须要的记录数时，就不持续往下查了。

◦最初应用 limit 做分页。

记住 limit 是最初查问的，为什么呢？如果咱们要查问薪资最低的三个数据，如果在排序之前就截取到 3 个数据。实际上查问进去的不是最低的三个数据而是前三个数据了，记住这一点。

如果 SQL 语句执行程序是先做 limit 再执行 order by，执行后果为 3500,5500,7000 了（ 正确 SQL 执行的最低工资的是 3500,5500,5500）。

SQL 查问时须要遵循的两个程序：

1、关键字的程序是不能颠倒的。

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

2、select 语句的执行程序（在 MySQL 和 Oracle 中，select 执行程序基本相同）。

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

以 SQL 语句举例，那么该语句的关键字程序和执行程序如下：

SELECT DISTINCT player_id, player_name, count(*) as num #程序 5
FROM player JOIN team ON player.team_id = team.team_id #程序 1
WHERE height > 1.80 #程序 2
GROUP BY player.team_id #程序 3
HAVING num > 2 #程序 4
ORDER BY num DESC #程序 6
LIMIT 2 #程序 7

三、SQL 执行打算

• 为什么要学习 SQL 的执行打算？

因为一个 sql 的执行打算能够通知咱们很多对于如何优化 sql 的信息。通过一个 sql 打算，如何拜访表中的数据（是应用全表扫描还是索引查找？）一个表中可能存在多个不同的索引，表中的类型是什么、是否子查问、关联查问等…

• 如何获取 SQL 的执行打算？

在 SQL 语句前加上 explain 关键词皆能够失去相应的执行打算。其中：在 MySQL8.0 中是反对对 select/delete/inster/replace/update 语句来剖析执行打算，而 MySQL5.6 前只反对对 select 语句剖析执行打算。replace语句是跟 instert 语句十分相似，只是插入的数据和表中存在的数据（存在主键或者惟一索引）抵触 的时候 **，****replace** 语句会把原来的数据替换新插入的数据，表中不存在惟一的索引或主键，则直接插入新的数据。

•如何剖析 SQL 语句的执行打算？

上面对 SQL 语句执行打算中的各个字段的含意进行介绍并举例说明。

◦id 列

id 标识查问执行的程序，当 id雷同 时，由上到下剖析执行，当 id 不同时，由大到小剖析执行。

id 列中的值只有两种状况，一组 数字（阐明查问的 SQL 语句对数据对象的操作程序）或者NULL（代表数据由另外两个查问的 union 操作后所产生的后果集）。

explain
select course_id,class_name,level_name,title,study_cnt
from imc_course a
join imc_class b on b.class_id=a.class_id
join imc_level c on c.level_id =a.level_id
where study_cnt > 3000

返回 3 行后果，并且 ID 值是一样的。由上往下读取 sql 的执行打算，第一行是 table c 表作为驱动表，等于是以 C 表为根底来进行循环嵌套的一个关联 查问。（4 *100*1 =400 总共扫描 400 行等到数据）

◦select_type 列

例如：查问学习人数大于 3000, 合并 课程是 MySQL 的记录。

EXPLAIN
SELECT 
course_id,class_name,level_name,title,study_cnt
FROM imc_course a
join imc_class b on b.class_id =a.class_id
join imc_level c on c.level_id = a.level_id
WHERE study_cnt > 3000

union

SELECT course_id,class_name,level_name,title,study_cnt
FROM imc_course a
join imc_class b on b.class_id = a.class_id
join imc_level c on c.level_id = a.level_id
WHERE class_name ='MySQL'

剖析数据表：先看 id 等于 2

id=2 则是查问 mysql 课程的 sql 信息，别离是 b,a,c 3 个表，是 union 操作，selecttype 为是 UNION。

id=1 为是查问学习人数 3000 人的 sql 信息，是 primary 操作的后果集，别离是 c,a,b3 个表，select_type 为 PRIMARY。

最初一行是 NULL, select_type 是 UNION RESULT 代表是 2 个 sql 组合的后果集。

◦table 列

指明是该 SQL 语句从哪个表中获取数据

EXPLAIN
SELECT
course id,class name,level name,title,study cnt
FROM imc course a
join imc class b on b.class id =a.class id
join imc level c on c.level id = a.level id
WHERE study cnt > 3000

union

SELECT course id,class name,level name,title,study _cnt
FROM imc course a
join imc class b on b.class id = a.class id
join imc level c on c.level id = a.level id
WHERE class name ='MySOL'

◦type 列

留神: 在 MySQL 中不肯定是应用 JOIN 才算是关联查问，实际上 MySQL 会认为每一个查问都是连贯查问，就算是查问一个表，对 MySQL 来说也是关联查问。

type的取值是体现了 MySQL 拜访数据的一种形式。type列的值 依照性能高到低排列 system \> const \> eq_ref \> ref \> ref_or_null \> index_merge \> range \> index \> ALL

•如果 where like“MySQL%”，type 类型为？

尽管 class_name 加了索引，然而应用where 的like% 右统配， 所以会走索引范畴扫描。

EXPLAIN
SELECT
course id,class name,level name,title,study_cnt
FROM imc course a
join imc class b on b.class id= a.class id
join imc level c on c.level id = a.level id
WHERE class namelike'MySQL%'

•如果 where like“%MySQL%”，type 类型为？

尽管 class_name 加了索引，然而应用where 的 %like% 左右统配， 所以会走全索引扫描，如果不加索引的话，左右统配会走全表扫描。

EXPLAIN
SELECT
course id,class name,level name,title,study_cnt
FROM imc course a
join imc class b on b.class id= a.class id
join imc level c on c.level id = a.level id
WHERE class namelike'%MySQL%'

◦possible_key、key 列

possible_keys阐明表可能用到了哪些索引，而 key 是指实际上应用到的索引。基于查问列和过滤条件进行判断。查问进去都会被列出来，然而不肯定会是应用到。

如果在表中没有可用的索引，那么 key 列 展现 NULL，possible_keys 是 NULL，这阐明查问到笼罩索引。

◦key_len 列

理论用的的索引应用的字节数。

留神，在联结索引中，如果有 3 列，那么总字节是长度是 100 个字节的话，那么 key_len 值数据可能少于 100 字节，比方 30 个字节，这就阐明了 查问中并没有应用联结索引的所有列。而只是利用到某一些列或者 2 列。

key_len 的长度是由表中的定义的字段长度来计算的，并不是存储的理论长度，所以满足数据最短的理论字段存储，因为会间接影响到生成执行打算的生成。

◦ref 列

指出那些列或常量被用于索引查找

◦rows 列

（有 2 个含意）1、依据统计信息预估的扫描行数。

2、另一方面是关联查问内嵌的次数，每获取匹配一个值都要对指标表查问，所以循环次数越多性能越差。

因为扫描行数的值是预估的，所以并不精确。

◦filtered 列

示意返回后果的行数占需读取行数的百分比。

filtered 列跟 rows 列是有关联的，是返回预估符合条件的数据集，再去取的行的百分比。也是预估的值。数值越高查问性能越好。

◦Extra 列

包含了不适宜在其余列中所显示的额定信息。

四、SQL 索引生效

◦最左前缀准则：要求建设索引的一个列都不能缺失，否则会呈现索引生效。

◦索引列上的计算，函数、类型转换（列类型是字符串在条件中须要应用引号，否则不走索引）、均会导致索引生效。

◦索引列中应用 is not null 会导致索引列生效。

◦索引列中应用 like 查问的前以 % 结尾会导致索引列生效。

◦索引列用 or 连贯时会导致索引生效。

五、理论优化慢 SQL 中遇到问题

上面是在慢 SQL 优化过程中所遇到的一些问题。

•MySQL 查问到的数据排序是稳固的么？

•force_index 的应用形式？

•为什么有时候 order by id 会导致索引生效？

•…….. 未残缺理中 ……

六、总结

通过本次对慢 SQL 的优化的需要进而发现无关 SQL 语句执行程序、执行打算、索引生效场景、底层 SQL 语句执行原理相干常识还存在盲区，得益于此次需要的开发，有深刻的对相干常识进行学习和总结。接下来会对 SQL 底层是如何执行 SQL 语句