长达1。7万字的explain关键字指南奉上！请你别再说不会

　　当你的数据里只有几千几万，那么SQL优化并不会发挥太大价值，但当你的数据里去到了几百上千万，SQL优化的价值就体现出来了！因此稍微有些经验的同学都知道，怎么让MySQL查询语句又快又好是一件很重要的事情。要让SQL又快又好的前提是，我们知道它病在哪里，而explain关键字就是MySQL提供给我们的一把武器！
　　在我们所执行的SQL前面加上explain关键字，MySQL就不会真正去执行这条语句，而是模拟优化器执行SQL查询语句，最后会输出一系列的指标告诉我们这条语句的性能如何，如下图所示。mysqlexplainselectfromstudentwhereid1Gid：1selecttype：SIMPLEtable：subjectpartitions：NULLtype：constpossiblekeys：PRIMARYkey：PRIMARYkeylen：4ref：constrows：1filtered：100。00Extra：NULL
　　总的来说，explain关键字可以告诉我们下面这么多信息：表的读取顺序如何数据读取操作有哪些操作类型哪些索引可以使用哪些索引被实际使用表之间是如何引用每张表有多少行被优化器查询。。。。。。
　　今天，我们就来介绍explain关键字的各个指标的含义。系好安全带，准备发车了！
　　为了方便讲解，这里新建了几张表，并初始化了一些数据（建表语句见附录）。这些表的关系如下：一共有老师、学生、课程三个实体，分别为：teacher、student、course。三个实体间的关系分别为：老师教学生的关系（teacherstudent）、学生的课程分数（studentcourse）。ID字段
　　ID字段的值及其排列顺序，表明MySQL执行时从各表取数据的顺序。一般情况下遵循下面两个原则：ID相同的组，其执行优先级按照其顺序由上到下。ID越大的组，其执行优先级越高。
　　对于下面这个例子：EXPLAINSELECTteacher。FROMteacher，teacherstudentWHEREteacherstudent。studentnames001ANDteacher。NAMEteacherstudent。teachername
　　该例子的输出为：
　　上面的输出一共有2条记录，其ID都为1，这表示其归为一组。对于ID相同的组，MySQL按照顺序从上到下执行，即：先拿teacherstudent表的数据，再拿teacher表的数据。
　　再来看下面这个例子：EXPLAINSELECTFROMteacherWHERENAMEIN（SELECTteachernameFROMteacherstudentWHEREstudentnameS002）
　　该例子的输出为：
　　上面的输出一共有3条记录，其中第1、2条的ID相同，第3条ID不同。那么其执行顺序就是ID值越大，其越早执行。ID相同的，按顺序执行。上面的例子，最早拿teacherstudent表的数据，之后是一个子查询组成的表，最后拿teacher表的数据。结合SQL分析，这也符合我们的常识。因为我们必须先把子查询的值算出来，因此需要先把teacherstudent表里的数据拿出来，之后才可以拿去teacher表里查询。selecttype字段
　　selecttype字段表示该SQL是什么查询类型，一共有以下6种：SIMPLE：简单查询，不包含子查询或union查询PRIMARY：主键查询SUBQUERY：在select或where中包含子查询DERIVED：from中包含子查询UNION：UNIONRESULTSIMPLE
　　简单查询，不包含子查询或union查询。查询T001老师都教了哪些学生EXPLAINSELECTstudent。FROMteacher，teacherstudent，studentWHEREteacher。NAMET001ANDteacher。NAMEteacherstudent。teachernameANDteacherstudent。studentnamestudent。NAME
　　可以看出其3个查询都是简单（SIMPLE）查询。因为ID相同，所以其查询顺序是按顺序来的。首先从teacher表中取出数据，之后从student表取出数据，最后teacherstudent表取数据。PRIMARY
　　一般情况下，如果查询中包含了任何复杂的子查询，那么最外层查询会被标记为主查询。PRIMARY查询哪些老师教授了选修数学课的学生EXPLAINSELECTFROMteacherWHERENAMEIN（SELECTteachernameFROMteacherstudentWHEREstudentname（SELECTstudentnameFROMstudentcourseWHEREcoursenameshuxue））
　　在上面的查询中，首先是执行ID为3的查询，即去studentcourse表取出选修了数学课的学生名字，之后再去进行最外层的查询。可以看到最外层查询的selecttype为PRIMARY。SUBQUERY
　　在select或where中包含子查询，那么selecttype会被标记为SUBQUERY。以上面的查询为例：PRIMARY查询哪些老师教授了选修数学课的学生EXPLAINSELECTFROMteacherWHERENAMEIN（SELECTteachernameFROMteacherstudentWHEREstudentname（SELECTstudentnameFROMstudentcourseWHEREcoursenameshuxue））
　　在该查询中，where中包含了子查询，因此在explain中有一个ID为3的查询被标记为SUBQUERY。DERIVED
　　在FROM中包含子查询，那么selecttype会被标记为SUBQUERY。UNION
　　类似包含union关键字的会被标记成UNION类型，这种查询方式比较少，这里不做深入讲解。UNIONRESULT
　　类似包含union关键字的会被标记成UNIONRESULT类型，这种查询方式比较少，这里不做深入讲解。type字段
　　type字段表示访问情况，通常用来衡量SQL的查询效率。其值的查询效率从最好到最差分别为：NULLsystemconsteqrefreffulltextrefornullindexmergeuniquesubqueryindexsubqueryrangeindexALLNULL
　　NULL表示MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。explainselectmax（id）fromteacher
　　system
　　表只有一行记录（等于系统表），这是const类型的特列。
　　出现的情况较少，这里不深入介绍。const
　　const表示该表最多有一个匹配记录。
　　通常情况下是SQL中出现了主键索引或唯一索引。explainselectfromteacherwherenameT002
　　上面例子中，teacher。name字段为唯一索引字段，所以通过该字段只能唯一找到一条记录，因此其type类型为const。eqref
　　eqref表示主键索引或唯一索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
　　与const类型非常相似，唯一的区别是eqef通常出现在联表的情况下，而const通常出现在单表情况下。EXPLAINSELECTFROMteacher，teacherstudentWHEREteacher。NAMEteacherstudent。teachername
　　从上面的执行结果可以看出，其首先全表扫描了teacherstudent表，之后使用teacher。name唯一索引去将联合teacher表的每一条记录。
　　要注意的是，eqref这种情况重点在于：读取本表中和关联表表中的每行组合成的一行。如果并没有关联表中每行这个概念，那么就不会出现eqref这种类型。例如我在上面的SQL中加上age为24这个条件，即SQL为：EXPLAINSELECTFROMteacher，teacherstudentWHEREteacher。NAMEteacherstudent。teachernameandteacher。age24
　　执行计划变为：
　　会看到type类型都变为ref了，eqref消失了。ref
　　ref表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。
　　与const非常类似，只不过ref会匹配到多个记录，而const则只会匹配到单个记录。explainselectfromteacherwhereage24
　　age为普通索引，表中有2条记录。
　　表中数据为：
　　refornull
　　类似ref，但是可以搜索值为NULL的行。explainselectfromteacherwhereage24orageisnull
　　当我们增加ageisnull查询条件后，其type字段就变成了refornull。
　　indexmerge
　　表示使用了索引合并的优化方法。
　　索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。EXPLAINSELECTfromteacherwhereid1orage24
　　执行计划为：
　　可以看到使用了indexmerge的查询类型。在teacher表中id和age都是索引，其将两个字段的索引结果进行合并了。range
　　range表示检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。
　　一般就是在你的where语句中出现between、、in等的范围查询。EXPLAINSELECTFROMTEACHERwhereagebetween10and20
　　执行计划为：
　　上面语句中，我们使用between进行范围查询，因此type类型为range。index
　　index表示只遍历索引树，且只从索引树中获取数据。EXPLAINSELECTid，ageFROMTEACHER
　　上面SQL中的id、age都是索引字段，可以直接从索引树中读取。因此其type字段为index，表示此次查询数据可以直接从索引树获取到。但是如果查询的字段不在索引树中，那么就是全表扫描了。例如：EXPLAINSELECTid，entertimeFROMTEACHER
　　查询SQL的entertime字段不是索引，所以上面的查询就变成了全表查询（ALL）。ALL
　　ALL表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。table字段
　　表示数据来自哪张表possiblekeys字段
　　显示可能应用在这张表中的索引，一个或多个。
　　查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。key字段
　　实际使用到的索引，如果为NULL，则没有使用索引。
　　查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。selectfromteacherwherenameT001
　　上面这个查询中，key字段显示使用了udxname这个索引，也就是name这个字段作为索引。keylen字段
　　这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，filmactor的联合索引idxfilmactorid由filmid和actorid两个int列组成，并且每个int是4字节。通过结果中的keylen4可推断出查询使用了第一个列：filmid列来执行索引查找。mysqlexplainselectfromfilmactorwherefilmid2；idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmactorrefidxfilmactorididxfilmactorid4const1Usingindex
　　keylen计算规则如下：字符串char（n）：n字节长度varchar（n）：2字节存储字符串长度，如果是utf8，则长度3n2数值类型tinyint：1字节smallint：2字节int：4字节bigint：8字节时间类型date：3字节timestamp：4字节datetime：8字节其他
　　如果字段允许为NULL，需要1字节记录是否为NULLref字段
　　这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film。id）。rows列
　　这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。Extra列
　　这一列展示的是额外信息。distinct
　　一旦mysql找到了与行相联合匹配的行，就不再搜索了。mysqlexplainselectdistinctnamefromfilmleftjoinfilmactoronfilm。idfilmactor。idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmindexidxnameidxname33NULL3UUsingtemporary1SIMPLEfilmactorrefidxfilmactorididxfilmactorid4test。film。id1UDistinctUsingindex
　　这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmindexNULLPRIMARY4NULL3Usingindex
　　上面例子中，我只是选择了id列，这个列本身是索引，其信息直接在索引树中就可以拿到，因此不需要再访问行记录。Usingwhere
　　mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按where条件进行检查，符合就留下，不符合就丢弃。mysqlexplainselectfromfilmwhereid1；idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmindexPRIMARYidxname33NULL3UUsingindexUsingtemporary
　　mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。1。actor。name没有索引，此时创建了张临时表来idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEactorALLNULLNULLNULLNULL2Usingtemporary2。film。name建立了idxname索引，此时查询时extra是usingindex，没有用临时表idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmindexidxnameidxname33NULL3UsingindexUsingfilesort
　　MySQL中无法利用索引完成的排序操作称为文件排序。
　　在MySQL中的ORDERBY有两种排序实现方式：利用有序索引获取有序数据文件排序
　　在explain中分析查询的时候，利用有序索引获取有序数据显示Usingindex，文件排序显示Usingfilesort。至于什么时候使用索引排序，什么时候使用文件排序，这个问题太过于复杂，这里不做深入介绍。1。actor。name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEactorALLNULLNULLNULLNULL2Usingfilesort2。film。name建立了idxname索引，此时查询时extra是idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEfilmindexNULLidxname33NULL3Usingindex建表语句
　　建表语句如下：NavicatPremiumDataTransferSourceServer：localhostSourceServerType：MySQLSourceServerVersion：80019SourceHost：localhost：3306SourceSchema：testTargetServerType：MySQLTargetServerVersion：80019FileEncoding：65001Date：2206202008：59：15SETNAMESutf8mb4；SETFOREIGNKEYCHECKS0；TablestructureforcourseDROPTABLEIFEXISTSCREATETABLEcourse（idintNOTNULLAUTOINCREMENT，namevarchar（20）DEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYudxname（name））ENGINEInnoDBAUTOINCREMENT4DEFAULTCHARSETutf8mb4COLLATEutf8mb40900RecordsofcourseBEGIN；INSERTINTOcourseVALUES（2，shuxue）；INSERTINTOcourseVALUES（3，yingyu）；INSERTINTOcourseVALUES（1，yuwen）；COMMIT；TablestructureforstudentDROPTABLEIFEXISTSCREATETABLEstudent（idintNOTNULLAUTOINCREMENT，namevarchar（20）DEFAULTNULL，ageintDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYudxname（name），UNIQUEKEYidxage（age）USINGBTREE）ENGINEInnoDBAUTOINCREMENT4DEFAULTCHARSETutf8mb4COLLATEutf8mb40900RecordsofstudentBEGIN；INSERTINTOstudentVALUES（1，S001，24）；INSERTINTOstudentVALUES（2，S002，23）；INSERTINTOstudentVALUES（3，S003，22）；COMMIT；TablestructureforstudentcourseDROPTABLEIFEXISTSCREATETABLEstudentcourse（idintNOTNULLAUTOINCREMENT，studentnamevarchar（20）DEFAULTNULL，coursenamevarchar（20）DEFAULTNULL，PRIMARYKEY（id），KEYidxstudentname（studentname），KEYidxcoursename（coursename））ENGINEInnoDBAUTOINCREMENT7DEFAULTCHARSETutf8mb4COLLATEutf8mb40900RecordsofstudentcourseBEGIN；INSERTINTOstudentcourseVALUES（1，S001，yuwen）；INSERTINTOstudentcourseVALUES（2，S001，shuxue）；INSERTINTOstudentcourseVALUES（3，S001，yingyu）；INSERTINTOstudentcourseVALUES（4，S002，yuwen）；INSERTINTOstudentcourseVALUES（5，S002，shuxue）；INSERTINTOstudentcourseVALUES（6，S003，yuwen）；COMMIT；TablestructureforteacherDROPTABLEIFEXISTSCREATETABLEteacher（idintNOTNULLAUTOINCREMENT，namevarchar（20）DEFAULTNULL，entertimedatetimeDEFAULTNULL，ageintDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYudxname（name），KEYidxage（age）USINGBTREE）ENGINEInnoDBAUTOINCREMENT6DEFAULTCHARSETutf8mb4COLLATEutf8mb40900RecordsofteacherBEGIN；INSERTINTOteacherVALUES（1，T001，2020061621：51：54，12）；INSERTINTOteacherVALUES（2，T002，2020061521：52：02，12）；INSERTINTOteacherVALUES（3，T003，2020061421：52：08，24）；INSERTINTOteacherVALUES（4，T004，2020061421：52：08，24）；COMMIT；TablestructureforteacherstudentDROPTABLEIFEXISTSCREATETABLEteacherstudent（idintNOTNULLAUTOINCREMENT，teachernamevarchar（20）DEFAULTNULL，studentnamevarchar（20）DEFAULTNULL，PRIMARYKEY（id），KEYidxteachername（teachername），KEYidxstudentname（studentname））ENGINEInnoDBAUTOINCREMENT7DEFAULTCHARSETutf8mb4COLLATEutf8mb40900RecordsofteacherstudentBEGIN；INSERTINTOteacherstudentVALUES（1，T001，S001）；INSERTINTOteacherstudentVALUES（2，T001，S002）；INSERTINTOteacherstudentVALUES（3，T001，S003）；INSERTINTOteacherstudentVALUES（4，T002，S001）；INSERTINTOteacherstudentVALUES（5，T002，S002）；INSERTINTOteacherstudentVALUES（6，T003，S001）；COMMIT；SETFOREIGNKEYCHECKS1；原文：https：mp。weixin。qq。comszlWVJrzSeIFr7f2X5H78Dg
　　作者：陈树义
　　如果感觉本文对你有帮助，点赞关注支持一下