作者：京东科技 宋慧超
一、前言
最近通过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 ... LIMIT2、select语句的执行程序（在MySQL和Oracle中，select执行程序基本相同）。
FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT以SQL语句举例，那么该语句的关键字程序和执行程序如下：
SELECT DISTINCT player_id, player_name, count(*) as num #程序5FROM player JOIN team ON player.team_id = team.team_id #程序1WHERE height > 1.80 #程序2GROUP BY player.team_id #程序3HAVING num > 2 #程序4ORDER BY num DESC #程序6LIMIT 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操作后所产生的后果集）。
explainselect course_id,class_name,level_name,title,study_cntfrom imc_course ajoin imc_class b on b.class_id=a.class_idjoin imc_level c on c.level_id =a.level_idwhere study_cnt > 3000返回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的记录。
EXPLAINSELECT course_id,class_name,level_name,title,study_cntFROM imc_course ajoin imc_class b on b.class_id =a.class_idjoin imc_level c on c.level_id = a.level_idWHERE study_cnt > 3000unionSELECT course_id,class_name,level_name,title,study_cntFROM imc_course ajoin imc_class b on b.class_id = a.class_idjoin imc_level c on c.level_id = a.level_idWHERE 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语句从哪个表中获取数据
| 值 | 含意 |
|---|---|
| <table name> | 展现数据库表名（如果表取了别名显示别名） |
| <unionM, N> | 由ID为M、N查问union产生的后果集 |
| <dirived N> / <subquery N> | 由ID为N的查问产生的后果（通常也是一个子查问的长期表） |
EXPLAINSELECTcourse id,class name,level name,title,study cntFROM imc course ajoin imc class b on b.class id =a.class idjoin imc level c on c.level id = a.level idWHERE study cnt > 3000unionSELECT course id,class name,level name,title,study _cntFROM imc course ajoin imc class b on b.class id = a.class idjoin imc level c on c.level id = a.level idWHERE class name ='MySOL'◦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全表扫描，效率最差的连贯形式 |
•如果where like “MySQL%”，type类型为？
尽管class_name 加了索引 ，然而应用where的like% 右统配， 所以会走索引范畴扫描。
EXPLAINSELECTcourse id,class name,level name,title,study_cntFROM imc course ajoin imc class b on b.class id= a.class idjoin imc level c on c.level id = a.level idWHERE class namelike'MySQL%'•如果where like “%MySQL%”，type类型为？
尽管class_name 加了索引 ，然而应用where的%like% 左右统配， 所以会走全索引扫描，如果不加索引的话，左右统配会走全表扫描。
EXPLAINSELECTcourse id,class name,level name,title,study_cntFROM imc course ajoin imc class b on b.class id= a.class idjoin imc level c on c.level id = a.level idWHERE 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列
包含了不适宜在其余列中所显示的额定信息。
| 值 | 含意 |
|---|---|
| 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语句