MySQL优化从执⾏计划开始（explain超详细），⾼级⾯试题+解析
查询缓存：当⼀个SQL进来时，如果开启查询缓存功能，MySQL会优先去查询缓存中检查是否有数据匹配，如果匹配上，就不会再去解析对应的SQL啦，但如果语句中有⽤户⾃定义函数、存储函数、⽤户变量、临时表、mysql库中的系统表时，都不会⾛缓存； 对于查询缓存来说，在MySQL8.0已经去除，官⽅回应的是在⼀定场景上，查询缓存会导致性能上的瓶颈。
解析器：对于⼀个SQL语句，MySql根据语法规则需要对其进⾏解析，并⽣成⼀个内部能识别的解析树；
优化器：负责对解析器得到的解析树进⾏优化，MySQL会根据内部算法到⼀个MySQL认为最优的执⾏计划，后续就按照这个执⾏计划执⾏。所以后续我们分析的就是MySQL针对SQL语句选择出来的最优执⾏计划，结合业务，根据规则对SQL进⾏优化，从⽽让SQL语句在MySQL内部达到真正的最优。
执⾏器：得到执⾏计划之后，就会到对应的存储引擎，根据执⾏计划给出的指令依次执⾏。
存储引擎：数据的存储和提取最后是靠存储引擎；MySQL内部实现可插拔式的存储引擎机制，不同的存储引擎执⾏不同的逻辑；
物理⽂件：数据存储的最终位置，即磁盘上；协同存储引擎对数据进⾏读写操作。
关于MySql的逻辑结构，以上只是简单描述，业务逻辑层的功能模块远不⽌上⾯提到的，⼩伙伴有兴趣可以专门研究⼀下，这⾥的⽬的就是为了体现SQL语句到服务器上时经过的⼏个关键步骤，⽅便后续优化的理解。
2. SQL语句的中关键字执⾏顺序须知
在编写⼀条查询语句时，习惯性的从头到尾开始敲出来，应该都是从select 开始吧，但似乎没太注意它们真正的执⾏顺序；既然要优化，肯定需要得知道⼀条SQL语句⼤概的执⾏流程，结合执⾏计划，⽬的就更加清晰啦；上⼀张⼀看就明⽩的图：
关键字简述：
FROM：确定数据来源，即指定表；
JOIN…ON：确定关联表和关联条件；
WHERE：指定过滤条件，过滤出满⾜条件的数据；
GROUP BY：按指定的字段对过滤后的数据进⾏分组；
HAVING：对分组之后的数据指定过滤条件；
SELECT：查想要的字段数据；
DISTINCT：针对查出来的数据进⾏去重；
ORDER BY：对去重后的数据指定字段进⾏排序；
LIMIT：对去重后的数据限制获取到的条数，即分页；
好啦，⼤概了解MySQL的逻辑结构和SQL查询关键字执⾏顺序之后，接下来就可以好好说说执⾏计划啦。
3. 好好说说执⾏计划
通过上⾯的逻辑结构，
《⼀线⼤⼚Java⾯试题解析+后端开发学习笔记+最新架构讲解视频+实战项⽬源码讲义》
【docs.qq/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享
当⼀个SQL发送到MySQL执⾏时，需要经过内部优化器进⾏优化，⽽使⽤explain关键字可以模拟优化器执⾏SQL查询语句，从⽽知道MySQL是如何处理SQL的，即SQL的执⾏计划；根据explain提供的执⾏计划信息分析SQL语句，然后进⾏相关优化操作。接下来的⽰例演⽰⽤到五张表：USER(⽤户表)、MENU(菜单表)、ROLE(⾓⾊表)、USER_ROLE(⽤户⾓⾊关系表)、ROLE_MENU(⾓⾊菜单关系表)、ADDR(⽤户地址表，这⾥认为和⽤户⼀⼀对应)、FRIEND(朋友表，⼀对多关系)，它们的关系这⾥就不详细说了吧，⼩伙伴肯定都明⽩，这是管控菜单权限的五张基础表和两个基础信息表；
演⽰⽤的版本是MySql5.5，各版本之间会有不同，所以⼩伙伴⽤的版本测试结果不⼀样的时候，千万别骂我渣哦；其实重要的是查看的思路，整体是⼤同⼩异。(求原谅…)
通过explain会输出如下信息，很多⼩伙伴只关注红框标注部分(即索引)，但其实是不够的，接下来就⼀个⼀个好好说说。
id
这个id和咱们平时表结构设计的主键ID不太⼀样，这⾥的id代表了每⼀条SQL语句执⾏计划中表加载的顺序，分为三种情况：id相同的时候：这时是从上到下依次执⾏；
abap面试题及解答
EXPLAIN SELECT t.ID,t.USER_NAME,r.ROLE_NAME FROM USER t
JOIN USER_ROLE tr ON t.ID = tr.USER_ID
JOIN ROLE r ON tr.ROLE_ID = r.ID
执⾏如下语句，得如下结果：
如上图所⽰，id⼀样，从上到下依次执⾏，所对应表加载顺序为t->tr->r(这⾥的表是别名)；
id不同的时候：当id不同的时，id越⼤的越先执⾏；java遍历json数组
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm
WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1))
⼦查询会导致id递增，结果如下：
如上图所⽰，id递增啦，所对应表的加载顺序为ur->rm->t(这⾥的表是别名)；
id相同和不同同时存在时：id相同的认为是同⼀组，还是从上往下加载；不⼀样的情况还是越⼤越优先执⾏
EXPLAIN SELECT t.ROLE_ID,m.ID,m.MENU_NAME,m.MENU_URL FROM
(SELECT ROLE_ID FROM USER_ROLE WHERE USER_ID=3) t,ROLE_MENU rm,MENU m
WHERE t.ROLE_ID=rm.ROLE_ID
AND rm.MENU_ID=m.ID
执⾏结果如下：
如上图所⽰，id有⼀样的，也有不同的，则对应表的加载顺序为USER_ROLE->derived2 (衍⽣表)->rm->m；衍⽣表表名后⾯的2代表的是id，所以可以通过衍⽣表表名后⾯的id知道是哪⼀步产⽣的，即derived2衍⽣表是id为2的这⼀步产⽣的。
git无法克隆代码到本地
select_typestruts2的作用
select_type 是表⽰每⼀步的查询类型，⽅便分析⼈员很直接的看到当前步骤执⾏的是什么查询，有多种类型，见下图：
1> SIMPLE：简单的SELECT查询，不包含⼦查询或UNION的那种；
EXPLAIN SELECT * FROM USER;
输出结果如下：
2> PRIMARY：查询语句中包含其他⼦查询或UNION操作，那最外层的SELECT就被标记为该类型；
如上图所⽰，查询中包含⼦查询，最外层查询被标记为PRIMARY；
3> SUBQUERY：在SELECT或WHERE中包含的⼦查询会被标记为该类型；
见PRIMARY图，当存在⼦查询时，会将⼦查询标记为SUBQUERY
4> MATERIALIZED：被物化的⼦查询，即针对对应的⼦查询将其物化为⼀个临时表；
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm
WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1));
测试物化⽤的是MySQL8.0，和5.*版本有所不同，输出结果如下：
如上图所⽰，将⼦查询物化为⼀个临时表subquery2，这个功能是可以通过设置优化器对应的开关的。
python实例化对象是什么意思5> DERIVED：在FROM之后的⼦查询会被标记为该类型，同样会把结果放在⼀个临时表中；
mysql面试题sql
EXPLAIN SELECT tm.MENU_NAME,rm.ROLE_ID FROM
(SELECT * FROM MENU WHERE ID >3 ) tm ,ROLE_MENU rm
WHERE tm.ID=rm.MENU_ID AND rm.ROLE_ID=1
输出结果：
如图所⽰，FROM后⾯跟的⼦查询就被标记为DERIVED，对应步骤产⽣的衍⽣表为derived2。⾼版本好像对其进⾏了优化，8.0版本这种形式认为是简单查询。
6> UNION：UNION操作中，查询中处于内层的SELECT；
EXPLAIN SELECT * FROM USER_ROLE T1 WHERE T1.USER_ID=1
UNION
SELECT * FROM USER_ROLE T2 WHERE T2.USER_ID=2
输出结果如下：
如上图所⽰，将第⼆个SELECT标注为UNION ，即对应加载的表为T2。
**7> UNIOIN RESULT：**UNION操作的结果，对应的id为空，代表的是⼀个结果集；
见UNIOIN图，UNIOIN RESULT代表的是UNION之后的结果，对应id为空。
table
table代表对应步骤加载的是哪张表，中间会出现⼀些临时表，⽐如subquery2、derived2等这种，最后的数字代表产⽣该表对应步骤的id。
type
代表访问类型，MySQL内部将其分为多类型，常⽤的类型从好到差的顺序展⽰如下：
system->const->eq_ef->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL;
⽽在实际开发场景中，⽐较常见的⼏种类型如下：const->eq_ref->ref->range->index->ALL(顺序从好到差)，通常优化⾄少在range级别或以上，⽐如ref算是⽐较不错的啦；
上⾯说到的从好到差指的是查询性能。
1>const：表⽰通过索引⼀次就到数据，⽤于⽐较primary key或者unique索引，很快就能到对应的数据；
2>eq_ref：唯⼀性索引扫描，对于每个索引键，表中只有⼀条记录与之匹配，常⽤于主键或唯⼀索引扫描；
3>ref：⾮唯⼀索引扫描，返回匹配的所有⾏，如建⽴⼀个朋友维护表，维护⽤户对应的朋友，⽽在⽤户ID建⽴⾮唯⼀索引；
4>range：使⽤⼀个索引检索指定范围的⾏，⼀般在where语句中会出现between、<、>、in等范围查询；
5>index：全索引扫描，只遍历索引树；
6>ALL：全表扫描，到匹配⾏。与index⽐较，ALL需要扫描磁盘数据，index值需要遍历索引树。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。