作者：京东科技宋慧超

一、前语

最近经过SGM监控发现有两个SQL的履行时间占该任务总履行时间的90%，经过对该SQL进行剖析和优化的进程中，又从头对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

最终会得到满意相关条件的两张表的数据，不加相关条件会呈现笛卡尔积。

一文带你搞懂如何优化慢SQL

◦然后运用group by对数据进行分组。

依照SQL句子中的分组条件对数据进行分组，可是不会挑选数据。

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

一文带你搞懂如何优化慢SQL

◦然后分组后的数据别离履行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无法实现的。

当前的数据分组情况

一文带你搞懂如何优化慢SQL

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

一文带你搞懂如何优化慢SQL

然后再依据咱们要的数据进行select，一般字段查询或许聚合函数查询，假如是聚合函数，select的查询成果会增加一条字段。

分组结束之后，咱们再履行select句子，因为聚合函数是依赖于分组的，聚合函数会单独新增一个查询出来的字段，这儿咱们两个id重复了，咱们就保存一个id，重复字段名需求指向来自哪张表，不然会呈现仅有性问题。最后依照用户名去重。

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

一文带你搞懂如何优化慢SQL

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

一文带你搞懂如何优化慢SQL

◦然后将查询到的数据成果运用distinct关键字去重。

◦然后兼并各个分组的查询成果，依照order by的条件进行排序。

比方这儿依照id排序。假如此刻有limit那么查询到相应的咱们需求的记载数时，就不持续往下查了。

◦最后运用limit做分页。

记住limit是最后查询的，为什么呢？假设咱们要查询薪资最低的三个数据，假如在排序之前就截取到3个数据。实践上查询出来的不是最低的三个数据而是前三个数据了，记住这一点。

假设SQL句子履行次序是先做limit再履行order by，履行成果为3500,5500,7000了（正确SQL履行的最低工资的是3500,5500,5500）。

一文带你搞懂如何优化慢SQL

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句子履行计划中的各个字段的意义进行介绍并举例阐明。

一文带你搞懂如何优化慢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

一文带你搞懂如何优化慢SQL

回来3行成果，而且ID值是一样的。由上往下读取sql的履行计划，榜首行是table c表作为驱动表 ，等于是以C表为基础来进行循环嵌套的一个相关查询。 （4 *100*1 =400 一共扫描400行等到数据）

◦select_type列

值 意义
SIMPLE 不包含子查询或许UNION操作的查询（简略查询）
PRIMARY 查询中假如包含任何子查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY select列表中的子查询
DEPENDENT SUBQUERY 依赖外部成果的子查询
UNION union操作的第二个或许之后的查询值为union
DEPENDENT UNION 当union作为子查询时，第二或是第二个后的查询的值为select_type
UNION RESULT union发生的成果集
DERIVED 呈现在from子句中的子查询（派生表）

例如：查询学习人数大于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'

一文带你搞懂如何优化慢SQL

剖析数据表：先看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句子从哪个表中获取数据

值 意义
展示数据库表名（假如表取了别号显现别号）
<unionM, N> 由ID为M、N查询union发生的成果集
/ 由ID为N的查询发生的成果（一般也是一个子查询的暂时表） 
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'

一文带你搞懂如何优化慢SQL

◦type列

留意: 在MySQL中不一定是运用JOIN才算是相关查询，实践上MySQL会以为每一个查询都是衔接查询，就算是查询一个表，对MySQL来说也是相关查询。

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

值 意义
system const衔接类型的特例，当查询的表只要一行时运用
const 表中有且只要一个匹配的行时运用，如队逐步或仅有索引的查询，这是功率最高的衔接办法
eq_ref 仅有索引或主键查询，对应每个索引建，表中只要一条记载与之匹配【A表扫描每一行B表只要一行匹配满意】
ref_or_null 类似于ref类型的查询，可是附加了对NULL值列的查询
index_merge 表明运用了索引兼并优化办法
range 索引规模扫描，常见于between、>、<这样的查询条件
index FULL index Scan全索引扫描，同ALL的区别是，遍历的是索引树
ALL FULL TABLE Scan全表扫描，功率最差的衔接办法

一文带你搞懂如何优化慢SQL

•假如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%'

一文带你搞懂如何优化慢SQL

•假如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%'

一文带你搞懂如何优化慢SQL

◦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列

包含了不适合在其他列中所显现的额定信息。

值 意义
Distinct 优化distinct操作，在找到榜首匹配的元组后即中止找同样值得动作
Not exists 运用not exisits来优化查询
Using filesort 运用文件来进行排序，一般会呈现在order by 或group by查询中
Using index 运用了覆盖索引进行查询【查询所需求的信息用所用来获取，不需求对表进行拜访】
Using temporary MySQL需求运用暂时表来处理，常见于排序、子查询和分组查询
Using where 需求在MySQL服务器层运用where条件来过滤数据
select tables optimized away 直接经过索引来获取数据，不必拜访表

四、SQL索引失效

◦最左前缀原则：要求建立索引的一个列都不能缺失，不然会呈现索引失效。

◦索引列上的核算，函数、类型转换（列类型是字符串在条件中需求运用引号，不然不走索引）、均会导致索引失效。

◦索引列中运用is not null会导致索引列失效。

◦索引列中运用like查询的前以%最初会导致索引列失效。

◦索引列用or衔接时会导致索引失效。

五、实践优化慢SQL中遇到问题

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

•MySQL查询到的数据排序是稳定的么？

•force_index的运用办法？

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

•……..未完整理中……

六、总结

经过本次对慢SQL的优化的需求进而发现有关SQL句子履行次序、履行计划、索引失效场景、底层SQL句子履行原理相关常识还存在盲区，得益于此次需求的开发，有深入的对相关常识进行学习和总结。接下来会对SQL底层是怎么履行SQL句子