面试官说：工作了这么久，应该知道SQL的执行计划了。下面说说Sql的执行计划吧！我看着面试官手臂上刺着的大花臂和一串看不懂的韩文，咽了回去。咽了口唾沫，暗示自己要冷静，整理思绪，缓缓对面试官说：我不知道怎么办。面试官：。...，回去等通知。I:%^&%$!@#1.前言当我们工作到一定年限的时候，一定要明白一些应该掌握的知识点，比如今天面试官问的SQL执行计划在什么时候我们执行一条SQL，我们可以直接对应到结果，但是你不知道它要经过隧道，经过连接器、查询缓存、分析器、优化器、执行器，会走多远、走多远。它可能会展示在我们面前。有时候等了N久，却显示超时，这时候你恨不得砸电脑，但是看完今天的SQL执行计划，你就不用再砸了。看完这篇文章，你就会知道这不是问题。让我们来揭开这其中的奥秘。在实际应用场景中，要想知道如何优化SQL语句的执行，需要查看SQL语句的具体执行过程，加快SQL语句的执行效率。通常使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。官网地址：https://dev.mysql.com/doc/refman/5.5/en/explain-output.html首先我们来看下面的sql语句，里面会有id,select_type,table等列等等，这些都是我们执行计划中包含的信息，我们需要搞清楚的就是这些列是干什么用的，每列可能有多少个值。解释select*fromemp;2、执行计划中包含的信息已经被贴心小农复制，只需要放入数据库执行即可。`;CREATETABLE`dept`(`DEPTNO`intNOTNULL,`DNAME`varchar(14)DEFAULTNULL,`LOC`varchar(13)DEFAULTNULL,PRIMARYKEY(`DEPTNO`))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTO`dept`VALUES('10','会计','纽约');INSERTINTO`dept`VALUES('20','RESEARCH','DALLAS');INSERTINTO`dept`VALUES('30','SALES','CHICAGO');INSERTINTO`dept`VALUES('40','OPERATIONS','BOSTON');DROPTABLEIFEXISTS`emp`;CREATETABLE`emp`(`EMPNO`intNOTNULL,`ENAME`varchar(10)DEFAULTNULL,`JOB`varchar(9)DEFAULTNULL,`MGR`intDEFAULTNULL,`HIREDATE`dateDEFAULTNULL,`SAL`double(7,2)DEFAULTNULL,`COMM`double(7,2)DEFAULTNULL,`DEPTNO`intDEFAULTNULL,PRIMARYKEY(`EMPNO`),KEY`idx_job`(`JOB`),KEY`jdx_mgr`(`MGR`),KEY`jdx_3`(`DEPTNO`),KEY`idx_3`(`DEPTNO`))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTO`emp`VALUES('7369','SMITH','CLERK','7902','1980-12-17','800.00',null,'20');插入整数O`emp`VALUES('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');INSERTINTO`emp`VALUES('7521','WARD','推销员','7698','1981-02-22','1250.00','500.00','30');INSERTINTO`emp`VALUES('7566','JONES','MANAGER','7839','1981-02-02','2975.00',null,'20');INSERTINTO`emp`VALUES('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');INSERTINTO`emp`VALUES('7698','BLAKE','MANAGER','7839','1981-01-05','2850.00',null,'30');INSERTINTO`emp`VALUES('7782','CLARK','MANAGER','7839','1981-09-06','2450.00',null,'10');INSERTINTO`emp`VALUES('7839','KING','PRESIDENT',null,'1981-11-17','5000.00',null,'10');INSERTINTO`emp`VALUES('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');INSERTINTO`emp`VALUES('7900','JAMES','CLERK','7698','1981-12-03','950.00',null,'30');INSERTINTO`emp`VALUES('7902','FORD','ANALYST','7566','1981-12-03','3000.00',null,'20');INSERTINTO`emp`VALUES('7934','MILLER','CLERK','7782','1982-01-23','1300.00',null,'10');DROPTABLEIFEXISTS`emp2`;CREATETABLE`emp2`(`id`intNOTNULLAUTO_INCREMENT,`empno`intDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCARSET=utf8;INSERTINTO`emp2`VALUES('1','111');INSERTINTO`emp2`VALUES('2','222');DROPTABLEIFEXISTS`salgrade`;CREATETABLE`salgrade`(`GRADE`intNOTNULL,`LOSAL`doubleDEFAULTNULL,`HISAL`doubleDEFAULTNULL,PRIMARYKEY(`GRADE`))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTO`salgrade`VALUES('1','700','120??0');INSERTINTO`salgrade`VALUES('2','120??1','1400');INSERTINTO`salgrade`VALUES('3','1401','2000');INSERTINTO`salgrade`VALUES('4','2001','3000');INSERTINTO`salgrade`VALUES('5','3001','9999');DROPTABLEIFEXISTS`t_job`;创建表`t_job`(`id`intNOTNULLAUTO_INCREMENT,`job`varchar(9)CHARACTERSETutf8COLLATEutf8_general_ciDEFAULTNULL,PRIMARYKEY(`id`),KEY`j`(`job`))ENGINE=InnoDBDEFAULTCHARSET=utf8;2.1idselect查询的序号，包含一组数字，表示select子句或操作表在查询中的执行顺序。id号分为三种情况：1.如果id相同，则执行顺序为从上到下--left-associationexplainselect*fromempeleftjoinindeptdone.deptno=d.deptno;--右关联eexplainselect*fromemperightjoindeptdone.deptno=d.deptno;通过leftjoin和rightjoin的验证；id相同（注意执行计划的table列），leftjoin先扫描e表，再扫描d表；rightjoin先扫描d表，再扫描e表2。如果id不一样，如果是子查询，id的序号会增加。id值越大，优先级越高，越早执行在下表中，先查询id为2的数据，也就是我们的d表（注意：可以看到d表中select_type是SUBQUERY，意思是子查询），然后根据deptnoin查询e表中的数据表d.3.相同和不同的id同时存在：相同可以认为是一个组，从上到下依次执行。在所有组中，id值越大，优先级越高，执行越早id相同则依次执行2.2select_type主要用于区分查询的类型，是普通查询、联合查询还是子查询--simple：简单查询，不包括子查询和unionexplainselect*来自；--primary：如果查询包含任何复杂的子查询，则最外层的查询被标记为主要查询--union：如果第二个select出现在union之后，则标记为unionexplainselect*fromempwheredeptno=10unionselect*fromempwheresal>2000;--dependentunion：与union类似，这里depentent是指union或unionall的结果会受到外部表explainselect*fromempewheree.empnoin(selectempnofromempwheredeptno=10unionselectempnofromempwheresal>2000);selectexplainselect*fromempwheredeptno=10unionselect*fromempwheresal>2000;--subquery:在select或where列表中包含子查询查询的影响explainselect*fromempewheree.deptno=(selectdistinctdeptnofromdeptwheredeptno=e.deptno);--DERIVED:from子句中出现的子查询也叫派生类，explainselect*from(selectenamestaname,mgrfromempwhereename='W'unionselectename,mgrfromempwhereename='E')a;--UNCACHEABLESUBQUERY：表示不能缓存子查询的结果select1fromdeptwhereemp.deptno=dept.deptnounionselect1fromdeptwheredeptno=10);2.3表对应行正在访问哪个表，表名或别名，可能是临时表，也可能是联合合并结果设置1，如果是具体的表名，表示数据是从实际的物理表中获取的，也可以是表的别名explainselect*fromempwheresal>(selectavg(sal)fromemp);2.表名是derivedN的形式，表示使用derivedtableexplainselect*from(selectenamestaname,mgrfromempwhereename='WARD'unionselectenamestaname,mgrfromempwhereename='SMITH')a;derived2：表示我们需要从中取数据派生表23、当有union结果时，表名是unionn1,n2等形式，n1,n2代表idexplainselect*fromempwheredeptno=10unionselect*fromempwheresal>2000参与union；2.4typetype表示访问类型，访问类型表示我如何访问我们的数据，最容易想到的就是全表扫描，直接暴力遍历一个表来查找需要的数据，效率很低，访问的类型有很多种，效率从好到坏的顺序是：system>const>eqref>ref>fulltext>refornull>indexmerge>uniquesubquery>indexsubquery>range>index>ALL一般来说，需要保证查询至少达到range级别，最好达到ref--all:全表扫描，一般出现这样的sql如果语句和数据量都比较大，那么就需要优化explainselect*fromemp;--index：全索引扫描比全部更高效。主要有两种情况。一种是当前查询覆盖了索引，即我们需要的数据可以在索引中获取，或者使用索引进行排序，从而避免数据重新排序explainselectempnofromemp；--range：表示在使用索引查询时限制范围，在指定范围内进行查询，避免了索引的全索引扫描。适用运算符：=,<>,>,>=,<,<=,ISNULL,BETWEEN,LIKE,orIN()explainselect*fromempwhereempnobetween7000and7500;--index_subquery：使用索引关联子查询，不再扫描全表explainselect*fromempwhereemp.jobin(selectjobfromt_job);--unique_subquery：这种连接类型类似于index_subquery，使用唯一索引explainselect*fromempewheree.deptnoin(selectdistinctdeptnofromdept);--ref_or_null：对于同时需要关联条件和空值的字段，查询优化器会选择这种访问方式explainselect*fromempewheree.mgrisnullore.mgr=7369;--ref:使用非唯一索引进行数据查找createindexidx_3onemp(deptno);explainselect*fromempe,deptdwheree.deptno=d.deptno;--eq_ref:使用唯一索引进行数据查找explainselect*fromemp,emp2whereemp.empno=emp2.??empno;--const:这张表最多只有一行匹配，explainselect*fromempwhereempno=7369;--system:该表只有一行记录（等于系统表），this是const类型的特例，一般不会出现2.5possible_keys表示可能应用到这张表的索引，一个或多个，如果查询涉及的字段上有索引，则列出该索引，但不是必须查询实际使用explainselect*fromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=10;2.6key实际使用的索引，如果为null则不使用索引，如果使用覆盖索引查询，索引和查询select字段重叠explainselect*fromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=10;2.7key_len表示索引使用的字节数，可以通过keylen计算查询使用的索引长度，不损失精度。越短越好。1、一般keylen等于索引列类型的字节长度。比如int类型是4个字节，bigint是8个字节；2、如果是字符串类型，还要考虑字符集因素。例如utf8字符集占1个字符3个字节，gbk字符集中一个字符占2个字节3。如果列类型定义允许NULL，则其keylen需要增加1个字节4。如果列类型是变长类型，比如VARCHAR(TEXT\BLOB不允许对整列创建索引，如果创建部分索引，也视为动态列类型)，以及它的keylen需要加上2个字节。字符集会影响索引长度和数据存储空间，为列选择合适的字符集；variablelengthfields需要额外的2bytes，而fixed-lengthfields不需要额外的字节。而null需要额外占用1个字节的空间，所以过去有一种说法：索引字段最好不要使用NULL，因为NULL会使统计变得更复杂，需要额外占用一个字节的存储空间。explainselect*fromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=10;2.8ref显示使用索引的哪一列，如果可能的话，是一个常数explainselect*fromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=10;2.9rows根据表的统计信息和索引的使用情况，粗略估计找到所需记录需要读取的行数。这个参数非常重要。它直接反映了SQL找到了多少数据。好解释select*fromemp;2.10extra包含额外的信息。--usingfilesort：表示mysql不能使用索引进行排序，只能使用排序算法进行排序，会消耗额外的位置explainselect*fromemmporderbysal；--usingtemporary：创建临时表保存中间结果，查询完成后删除临时表explainselectename,count(*)fromempwheredeptno=10groupbyename;--usingindex：表示当前查询覆盖索引，读取数据直接从索引而不访问数据表。如果同时存在usingwhere表名索引，则使用该索引进行索引键值的查找。如果不是，则使用表面索引读取数据，而不是实际寻找explainselectdeptno,count(*)frommempgroupbydeptnolimit10;过滤说明选择*fromemp2whereempno=1;3.总结执行计划到此结束。SQL的执行计划不是很难。最主要的是记住每一列的含义以及如何优化它。这需要大量的培训和实际操作才能实现。有兴趣的朋友可以自己试试。还是很有意思的。本文只是简单介绍一下MySQL的执行计划。如果想全面深入的了解MySQL，可以优先阅读MySQL官方手册。大家加油~
