1、以MySQL单表查询来举例，看看执行计划包含哪些内容

1、const

我们之前都知道，假设你写一个select from table where id=x，或者select from table where name=x的语句，直接就可以通过聚簇索引或者二级索引+聚簇索引回源，轻松查到你要的数据，这种根据索引直接可以快速查找数据的过程，在执行计划里称之为const，意思就是性能超高的常量级的。 所以你以后在执行计划里看到const的时候，就知道他就是直接通过索引定位到数据，速度极快，这就是const的意思。

但是这里有一个要点，你的二级索引必须是唯一索引，才是属于const方式的，也就是说你必须建立 unique key唯一索引，保证一个二级索引的每一个值都是唯一的，才可以。

2、ref

那么如果你是一个普通的二级索引呢？就是个普通的KEY索引，这个时候如果你写一个select * from table where name=x的语句，name是个普通二级索引，不是唯一索引，那么此时这种查询速度也是很快的，他在执行计划里叫做ref。

如果你是包含多个列的普通索引的话，那么必须是从索引最左侧开始连续多个列都是等值比较才可以是属于ref方式，就是类似于select * from table where name=x and age=x and xx=xx，然后索引可能是个KEY(name,age,xx)。

3、ref_or_null

然后一个例外，就是如果你用name IS NULL这种语法的话，即使name是主键或者唯一索引，还是只能走ref方式。但是如果你是针对一个二级索引同时比较了一个值还有限定了IS NULL，类似于select * from table where name=x and name IS NULL，那么此时在执行计划里就叫做ref_or_null

说白了，就是在二级索引里搜你要的值以及是NULL的值，然后再回源去聚簇索引里查罢了，因为同时有索引等值比较和NULL值查询，就叫做ref_or_null了，其实也没啥。那这个ref就说完了，到这里大家先停顿一下，稍微来点深度思考，我们换个角度看，假设你以后在分析执行计划的时候看到了const，那是什么？对，肯定是通过主键或者唯一索引的访问，速度超高。

如果你看到了ref是什么意思？对，就是用了普通的索引，或者用主键/唯一索引搞了一个IS NULL/IS NOT NULL。

4、range

先说说range这个东西，这个东西顾名思义，其实就是你SQL里有范围查询的时候就会走这个方式。比如写一个SQL是select * from table where age>=x and age <=x，假设age就是一个普通索引，此时就必然利用索引来进行范围筛选，一旦利用索引做了范围筛选，那么这种方式就是range。接着停下脚步做个总结，假设你在执行计划里看到了const、ref和range，他们是什么意思？别担心，他们都是说基于索引在查询，总之都是走索引，所以一般问题不是太大，除非你通过索引查出来的数据量太多了，比如上面那个范围筛选，一下子查出来10万条数据，那不是想搞死MySQL么！是不是！

5、index

下面我们来讲一种比较特殊的数据访问方式，就是index，可能有的人看到这个index，天真的认为，这不就是通过索引来获取数据么，从索引根节点开始一通二分查找，不停的往下层索引跳转，就可以了，速度超快，感觉上跟ref或者range是一回事。

那你就大错特错了！假设我们有一个表，里面完整的字段联合索引是KEY(x1,x2,x3)，好，现在我们写一个SQL语句是select x1,x2,x3 from table where x2=xxx，相信大多数同学看到这里，都会觉得，完蛋了，x2不是联合索引的最左侧的那个字段啊！对的，这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的，那么他会怎么执行呢？不知道大家是否发现这个SQL里要查的几个字段，就是联合索引里的几个字段，巧了！所以针对这种SQL，在实际查询的时候，就会直接遍历KEY(x1,x2,x3)这个联合索引的索引树的叶子节点，大家还记得聚簇索引和普通索引的叶子节点分别存放了什么吗？聚簇索引的叶子节点放的是完整的数据页，里面包含完整的一行一行的数据，联合索引的叶子节点放的也是页，但是页里每一行就x1、x2、x3和主键的值！所以此时针对这个SQL，会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到 x2=xxx 的那个数据，就把里面的x1，x2，x3三个字段的值直接提取出来就可以了！这个遍历二级索引的过程，要比遍历聚簇索引快多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小多了，所以速度也快！

也就是说，此时只要遍历一个KEY(x1,x2,x3)索引就可以了，不需要回源到聚簇索引去！针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式！是不是跟大家一开始理解的很不一样？

没错，所以理解执行计划的前提，是对索引结构和使用索引的原理有一个透彻的理解，在这个基础之上，很容易就可以理解各种各样的执行计划里的访问方式了，脑子里甚至直接可以知道不同的访问方式在图里的执行路径。现在我们停一下脚步，思考一下，之前说的const、ref和range，本质都是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的，然后接下来到index这块，速度就比上面三种要差一些了，因为他是走遍历二级索引树的叶子节点的方式来执行了，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描好一些的。

2、深入探索多表关联的SQL语句到底是如何执行的？

之前我们已经用很大的篇幅讲完了针对单表的查询SQL语句，通常都会使用哪些执行计划，如何去使用索引去查找数据，想必大家都已经透彻的掌握这些知识了，比如以后在执行计划里看到const、ref、 range、index、all以及多索引查询合并的一些字样，都知道具体在磁盘数据层面是如何执行的了那么今天开始，我们来进入一块极为重要的知识领域，那就是MySQL的多表关联查询SQL语句是如何执行的？大家都知道，平时一般如果我们仅仅是执行一下单表查询，那都是比较简单的，而且通常你把索引给建好了，让他尽可能走索引，性能都不是什么大问题。但是往往我们平时基于MySQL做一些系统开发的时候，比较多的是写一些多表关联语句，因为有时候想要查找你需要的数据，不得不借助多表关联的语法去编写SQL语句，才能实现你想要的逻辑和语义，但是往往使用多表关联的时候，你的SQL性能就可能会遇到一些问题。那么今天开始，我们就一起来看看，这个多表关联SQL语句到底是如何执行的吧。今天先来给大家讲解一个超级简单，最最基础的多表关联查询的执行原理，假设我们有一个SQL语句是：select from t1,t2 where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx 就这么一个SQL语句，大家知道他是什么意思吗？首先，如果你在FROM字句后直接来了两个表名，这意思就是要针对两个表进行查询了，而且会把两个表的数据给关联起来，假设你要是没有限定什么多表连接条件，那么可能会搞出一个笛卡尔积的东西。举个例子，假设t1表有10条数据，t2表有5条数据，那么此时select from t1,t2，其实会查出来50条数据，因为t1表里的每条数据都会跟t2表里的每条数据连接起来返回给你，那么不就是会查出来10 5 = 50条数据吗？这就是笛卡尔积不过通常一般没人会傻到写类似这样的SQL语句，因为查出来这种数据实在是没什么意义。所以通常都会在多表关联语句中的WHERE子句里引入一些关联条件，那么我们回头看看之前的SQL语句里的 WHERE子句：where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx 首先呢，t1.x1=xxx，这个可以明确，绝对不是多表关联的连接条件，他是针对t1表的数据筛选条件，本质就是从t1表里筛选一波数据出来再跟t2表做关联的意思。然后t2.x3=xxx，也不是关联条件，他也是针对t2表的筛选条件。其实真正的关联条件是t1.x2=t2.x2，这个条件，意思就是说，必须要让t1表里的每条数据根据自己的x2 字段的值去关联上t2表里的某条记录，要求是t1表里这条数据的x2值和t2表里的那条数据的x2字段值是相等的。举个例子，假设t1表里有1条数据的x2字段的值是265，然后t2表里有2条数据的x2字段的值也是265，那么此时就会把t1表里的那条数据和t2表的2条数据分别关联起来，最终会返回给你两条关联后的数据。那么基本概念理解清楚了，具体到上面的SQL语句：select from t1,t2 where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx 其实这个SQL执行的过程可能是这样的，首先根据t1.x1=xxx这个筛选条件，去t1表里查出来一批数据，此时可能是const、ref，也可能是index或者all，都有可能，具体看你的索引如何建的，他会挑一种执行计划访问方式。然后假设从t1表里按照t1.x1=xxx条件筛选出2条数据，接着对这两条数据，根据每条数据的x2字段的值，以及t2.x3=xxx这个条件，去t2表里找x2字段值和x3字段值都匹配的数据，比如说t1表第一条数据的x2字段的值是265，此时就根据t2.x2=265和t2.x3=xxx这俩条件，找出来一波数据，比如找出来2条吧。此时就把t1表里x2字段为265的那个数据跟t2表里t2.x2=265和t2.x3=xxx的两条数据，关联起来，就可以了，t1表里另外一条数据也是如法炮制而已，这就是多表关联最最基本的原理。记住，他可能是先从一个表里查一波数据，这个表叫做“驱动表”，再根据这波数据去另外一个表里查一波数据进行关联，另外一个表叫做“被驱动表”

今天我们来继续跟大家聊聊多表关联语句是如何执行的这个问题，上次讲了一个最最基础的两个表关联的语句和执行过程，其实今天我们稍微来复习一下，然后接着上次的内容，引入一个“内连接”的概念来。假设我们有一个员工表，还有一个产品销售业绩表，员工表里包含了id（主键）、name（姓名）、 department（部门），产品销售业绩表里包含了id（主键）、employee_id（员工id）、产品名称（product_name）、销售业绩（saled_amount）。现在假设你想看看每个员工对每个产品的销售业绩，写个SQL： select e.name,e.department,ps.product_name,ps.saled_amount from employee e,product_saled pa where e.id=pa.employee_id 此时看到的数据可能如下：员工部门产品业绩张三大客户部产品A 30万张三大客户部产品B 50万张三大客户部产品C 80万李四零售部产品A 10万李四零售部产品B 12万至于上述SQL的执行原理，相信大家应该都理解，其实就是从员工表里走全表扫描，找出每个员工，然后针对每个员工的id去业绩表里找 employee_id 跟员工id相等的数据，可能每个员工的id在业绩表里都会找到多条数据，因为他可能有多个产品的销售业绩。然后就是把每个员工数据跟他在业绩表里找到的所有业绩数据都关联起来，比如张三这个员工就关联了业绩表里的三条数据，李四这个员工关联上了业绩表里的两条数据。其实大家已经在不知不觉中学会了最基本的一个SQL关联语法，就是内连接，这个内连接，英语是inner join，意思就是要求两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接。那么现在有这么一个问题，假设员工表里有一个人是新员工，入职到现在一个单子都没开过，也就没有任何的销售业绩，那么此时还是希望能够查出来这个员工的数据，只不过他的销售业绩那块可以给个 NULL就行了，表示他没任何业绩。但是如果仅仅是使用上述SQL语法，似乎是搞不定的，因为那种语法要求，必须要两个表能关联上的数据才会查出来，像你员工表里可能有个王五，根本在业绩表里关联不上任何数据，此时这个人是不会查出来的。所以此时就要到外连接了，也就是outer join，这个outer join分为左外连接和右外连接，左外连接的意思就是，在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来，右外连接反之，在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来。而且，这里还有一个语法限制，如果你是之前的那种内连接，那么连接条件是可以放在where语句里的，但是外连接一般是把连接条件放在ON字句里的，所以此时可以写出如下的SQL语句： SELECT e.name, e.department, ps.product_name, ps.saled_amount FROM employee e LEFT OUTER JOIN product_saled pa ON e.id=pa.employee_id 此时返回的数据里，你可能会看到如下的结果：员工部门产品业绩张三大客户部产品A 30万张三大客户部产品B 50万张三大客户部产品C 80万李四零售部产品A 10万李四零售部产品B 12万王五零售部 NULL NULL 所以说，到这里为止，想必大家都很清楚了，其实一般写多表关联，主要就是内连接和外连接，连接的基本语义和实现过程，大家应该也有一定的理解了。

之前我们把连接的基本语义和基本原理讲了一下，今天开始正式来深入探索一下SQL关联语法的实现原理首先，先给大家提出一个名词叫做：嵌套循环关联（nested-loop join），这其实就是我们之前给大家提到的最基础的关联执行原理。简单来说，假设有两个表要一起执行关联，此时会先在一个驱动表里根据他的where筛选条件找出一波数据，比如说找出10条数据吧接着呢，就对这10条数据走一个循环，用每条数据都到另外一个被驱动表里去根据ON连接条件和 WHERE里的被驱动表筛选条件去查找数据，找出来的数据就进行关联。依次类推，假设驱动表里找出来10条数据，那么就要到被驱动表里去查询10次！那么如果是三个表进行关联呢？那就更夸张了，你从表1里查出来10条数据，接着去表2里查10次，假设每次都查出来3条数据，然后关联起来，此时你会得到一个30条数据的结果集，接着再用这批数据去表3里去继续查询30次！

不知道大家有没有发现上面那种多表关联方法的问题在哪里？没错，就是我们往往从驱动表里查出来一波数据之后，要对每一条数据都循环一次去被驱动表里查询数据，所以万一你要是被驱动表的索引都没建好，总不能每次都全表扫描吧？这就是一个很大的问题！另外一个，刚开始对你的驱动表根据WHERE条件进行查询的时候，也总不能全表扫描吧？这也是一个问题！所以说，为什么有的时候多表关联很慢呢？答案就在这里了，你两个表关联，先从驱动表里根据 WHERE条件去筛选一波数据，这个过程如果你没给驱动表加索引，万一走一个all全表扫描，岂不是速度很慢？其次，假设你好不容易从驱动表里扫出来一波数据，接着又来一个for循环一条一条去被驱动表里根据 ON连接条件和WHERE筛选条件去查，万一你对被驱动表又没加索引，难道又来几十次或者几百次全表扫描？那速度岂不是慢的跟蜗牛一样了！所以说，通常而言，针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！

3、 MySQL是如何根据成本优化选择执行计划的？

之前已经给大家讲解清楚了 MySQL 在执行单表查询时候的一些执行计划，比如说const、ref、range、 index、all之类的，也讲了多表关联的时候是如何执行的，本质其实就是先查一个驱动表，接着根据连接条件去被驱动表里循环查询，现在大家对MySQL执行查询的一些基本原理都有了一个了解了。好，那么从今天开始，我们再更深入一步，因为其实大家之前或多或少也感觉到了一个问题，就是其实我们在执行单表查询也好，多表关联也好，似乎都有多种执行计划可以选择，比如有的表可以全表扫描，也可以用索引A，也可以用索引B，那么到底是用哪种执行计划呢？所以今天开始，我们用为期两周的时间，彻底给大家讲解清楚MySQL是如何对一个查询语句的多个执行计划评估他的成本的？如何根据成本评估选择一个成本最低的执行计划，保证最佳的查询速度？大家耐心学习，我们已经一点一点接近了MySQL查询原理的本质了，当大家透彻理解了这些内容，再去学习通过explain看真实的SQL语句的执行计划，就会完全明白是怎么回事了。当你能透彻理解了 explain看SQL执行计划之后，那么任何SQL语句的调优都不在话下。我们先了解一下MySQL里的成本是什么意思，简单来说，跑一个SQL语句，一般成本是两块，首先是那些数据如果在磁盘里，你要不要从磁盘里把数据读出来？这个从磁盘读数据到内存就是IO成本，而且 MySQL里都是一页一页读的，读一页的成本的约定为1.0。然后呢，还有一个成本，那就是说你拿到数据之后，是不是要对数据做一些运算？比如验证他是否符合搜索条件了，或者是搞一些排序分组之类的事，这些都是耗费CPU资源的，属于CPU成本，一般约定读取和检测一条数据是否符合条件的成本是0.2. 这个所谓1.0和0.2就是他自定义的一个成本值，代表的意思就是一个数据页IO成本就是1.0，一条数据检测的CPU成本就是0.2，就这个意思罢了。然后呢，当你搞一个SQL语句给MySQL的时候，比如： select from t where x1=xx and x2=xx 此时你有两个索引，分别是针对x1和x2建立的，就会先看看这个SQL可以用到哪几个索引，此时发现x1 和x2的索引都能用到，他们俩索引就是possible keys。接着会针对这个SQL计算一下全表扫描的成本，这个全表扫描的话就比较坑了，因为他是需要先磁盘IO 把聚簇索引里的叶子节点上的数据页一页一页都读到内存里，这有多少数据页就得耗费多少IO成本，接着对内存里的每一条数据都判断是否符合搜索条件的，这有多少条数据就要耗费多少CPU成本。所以说，此时就得计算一下这块成本有多少，怎么算呢？简单，教大家一个命令： show table status like “表名” 可以拿到你的表的统计信息，你在对表进行增删改的时候，MySQL会给你维护这个表的一些统计信息，比如这里可以看到rows和data_length两个信息，不过对于innodb来说，这个rows是估计值。 rows就是表里的记录数，data_length就是表的聚簇索引的字节数大小，此时用data_length除以1024 就是kb为单位的大小，然后再除以16kb（默认一页的大小），就是有多少页，此时知道数据页的数量和rows记录数，就可以计算全表扫描的成本了。 IO成本就是：数据页数量 1.0 + 微调值，CPU成本就是：行记录数 * 0.2 + 微调值，他们俩相加，就是一个总的成本值，比如你有数据页100个，记录数有2万条，此时总成本值大致就是100 + 4000 = 4100，在这个左右。

因为除了全表扫描之外，还可能多个索引都可以使用，但是当然同时一般只能用一个索引，所以不同索引的使用成本都得计算一下。这个使用索引访问数据的方式，大家应该都还记得，其实很简单，除非你直接根据主键查，那就直接走一个聚簇索引就ok了，否则普通索引，一般都是两步走，先从二级索引查询一波数据，再根据这波数据的主键去聚簇索引回表查询。这个过程的成本计算方法稍微有点特别，首先，在二级索引里根据条件查一波数据的IO成本，一般是看你的查询条件涉及到几个范围，比如说name值在25~100，250~350两个区间，那么就是两个范围，否则name=xx就仅仅是一个范围区间。一般一个范围区间就粗暴的认为等同于一个数据页，所以此时可能一般根据二级索引查询的时候，这个 IO成本都会预估的很小，可能就是1 1.0 = 1，或者是n 1.0 = n，基本就是个位数这个级别。但是到此为止，还仅仅是通过IO读取了二级索引的数据页而已，这仅仅是二级索引读取的IO成本，但是二级索引数据页到内存里以后，还得根据搜索条件去拿出来一波数据，拿这波数据的过程就是根据搜索条件在二级索引里搜索的过程。此时就要估算从二级索引里读取符合条件的数据的成本了，这需要估算一下在二级索引里会查出多少条数据，这个过程就稍微有点复杂了，不细讲了，总之呢，他会根据一个不是怎么太准确的算法去估算一下根据查询条件可能会在二级索引里查出多少条数据来。估算出来之后，比如估算可能会查到100条数据，此时从二级索引里查询数据的CPU成本就是100 0.2 + 微调值，总之就是20左右而已。接着你拿到100条数据之后，就得回表到聚簇索引里去查询完整数据，此时先估算回表到聚簇索引的IO 成本，这里比较粗暴的直接默认1条数据就得回表到聚簇索引查询一个数据页，所以100条数据就是100 个数据页的IO成本，也就是100 1.0 + 微调值，大致是100左右。接着因为在二级索引里搜索到的数据是100条，然后通过IO成本最多回表到聚簇索引访问100个数据页之后，就可以拿到这100条数据的完整值了，此时就可以针对这100条数据去判断，他们是否符合其他查询条件了，这里耗费的CPU成本就是100 * 0.2 + 微调值，就是20左右。把上面的所有成本都加起来，就是1 + 20 + 100 + 20 = 141，这就是使用一个索引进行查询的成本的计算方法，其实大家看明白这个过程了，那么每一个索引的成本计算过程就都明了了，假设你直接根据主键查询，那么也参考上述估算过程就可以了，那就不过是仅仅查询一个聚簇索引罢了。总之，上次讲到全表扫描发现成本是4100左右，这次根据索引查找可能就141，所以，很多时候，使用索引和全表扫描，他的成本差距是非常之大的。所以一般就会针对全表扫描和各个索引的成本，都进行估算，然后比较一下，选择一个成本最低的执行计划。

其实多表查询的执行计划选择思路，基本跟单表查询的执行计划选择思路是类似的，因为大家应该都记得，单表查询的时候，主要就是对这个表的多种访问方式（全表查询，各个索引查询）来根据一定的公式计算出来每种访问方式的成本，接着选择一个成本最低的访问方式，那么就可以确定下来这个表怎么访问了。可能有的人看了之前的两讲，会觉得似乎这种成本计算的方式也不是太靠谱，因为里面有些过程感觉怪怪的，不过这个没办法，其实即使让你来设计，也很难设计出完全公平、完全精准的成本预估算法来因为要在一个查询执行之前，就可以针对不同的访问方法精准计算他的成本，那是根本不现实的，最后只能是根据一些相对较为简单粗暴的办法，大致估算一下，估算结果可能不是太准确，但是也没办法了，反正算出来也就这么比较就是了。那么接着如果我们要看看多表关联的成本计算访问和执行计划选择方式，那就很简单了，因为大家应该还记得，多表关联的语句，比如： select * from t1 join t2 on t1.x1=t2.x1 where t1.x2=xxx and t1.x3=xxx and t2.x4=xxx and t2.x5=xxx 就这么一个语句，大家应该还记得他里面的访问过程一般来说，都会先选择一个驱动表，比如t1作为驱动表，此时就需要根据t1.x2=xxx和t1.x3=xxx这个条件从表里查询一波符合条件的数据出来，此时就有一个问题了，这里用到了t1的两个字段来筛选数据，可能x2和x3字段都建了索引了，此时到底选择哪个索引呢？或者干脆直接就是全表扫描？此时就会按照之前讲的那套方法来计算针对t1表查询的全表扫描和不同索引的成本，选择一个针对t1表的最佳访问方式，用最低成本从t1表里查出符合条件的数据来，接着就根据这波数据得去t2表里查数据，按照连接条件t1.x1=t2.x1去查，同时要符合t2.x4=xxx和t2.x5=xxx这两个条件。此时一样会根据之前讲解的办法去估算，针对t2表的全表扫描以及基于x4、x5、x1几个字段不同索引的访问的成本，挑选一个成本最低的方法，然后从t2表里把数据给查找出来，就可以，这就完成了多表关联！所以大家可以看到，其实多表关联的成本估算以及执行计划选择方式，跟单表关联基本上是差不多的，只不过多表关联要多查几个表罢了。

4、透彻研究通过explain命令得到的SQL执行计划

今天我们正式进入研究explain命令得到的SQL执行计划的内容了，只要把explain分析得到的SQL执行计划都研究透彻，完全能看懂，知道每个执行计划在底层是怎么执行的，那么后面学习SQL语句的调优就非常容易了。首先，我们现在应该都知道每条SQL语句，mysql都会经过成本和规则的优化，对这个SQL选择对应的一些访问方法和顺序，包括做一些特殊的改写确保执行效率是最优的，然后优化过后，就会得到一个执行计划。这个执行计划其实真没那么神秘，如果你把之前的内容都学习的比较透彻的话就会知道，所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序，其实这个计划到最后就是这点东西。

平时我们只要用类似于：explain select * from table，这种SQL前面加一个explain命令，就可以轻松拿到这个SQL语句的执行计划。今天我们就先来看看，这个所谓的执行计划里会有哪些东西。首先，当你执行explain命令之后，拿到的执行计划可能是类似下面这样的东西：

大家看到那所谓的id、select_type、table、partitions、type之类的东西了吗，其实这些就是所谓的执行计划里包含的东西大致来说，如果是一个简单的单表查询，可能这里就只有一条数据，也就是代表了他是打算如何访问这一个表而已。但是如果你的SQL语句极为的复杂，可能这里会有很多条数据，因为一个复杂的SQL语句的执行是要拆分为很多步骤的，比如先访问表A，接着搞一个排序，然后来一个分组聚合，再访问表B，接着搞一个连接，类似这样子。好，那么接下来我们就先来研究一下这个所谓的执行计划里包含的各个字段都是什么意思，首先是id这个东西这个id呢，就是说每个SELECT都会对应一个id，其实说穿了，就是一个复杂的SQL里可能会有很多个 SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id，这个没啥好说的。 select_type，顾名思义，说的就是这一条执行计划对应的查询是个什么查询类型，table就是表名，意思是要查询哪个表，partitions是表分区的概念，这个所谓的分区表我们会在后面给大家讲，这里先不用太关注他。 type，就是比较关键了，针对当前这个表的访问方法，这个之前我们都讲过很多，比如说const、ref、 range、index、all之类的，分别代表了使用聚簇索引、二级索引、全表扫描之类的访问方式。 possible_keys，这也很关键，他是跟type结合起来的，意思就是说你type确定访问方式了，那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里。key，就是在possible_keys里实际选择的那个索引，而key_len就是索引的长度。 ref，就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息。rows，是预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据。filtered，就是经过搜索条件过滤之后的剩余数据的百分比。extra是一些额外的信息，不是太重要。

今天我们就一步一步的来讲解不同的SQL语句的执行计划长什么样子，先来看第一条SQL语句，特别的简单，就是： explain select from t1 就这么一个简单的SQL语句，那么假设他这个里面有大概几千条数据，此时执行计划看起来是什么样的？

一起来分析一下上面的执行计划吧，学习当然得从最简单的地方开始一步一步得来，相信每个人都能成为分析SQL执行计划的高手。首先呢，id是1，这个不用管他了，select_type是SIMPLE，这个先不说他什么意思，你要知道顾名思义，这个表的查询类型是很普通的、而且简单的就可以了。 table是t1，这还用说么？表名就是t1，所以意思就是这里要访问t1这个表。type是all，这就是我们之前提到的多种访问方式之一了，all就是全表扫描，这没办法，你完全没加任何where条件，那当然只能是全表扫描了！而且如果大家记得我们之前讲解的底层访问方式，就会知道，这里直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据。 rows是3457，这说明全表扫描会扫描这个表的3457条数据，说明这个表里就有3457条数据，此时你全表扫描会全部扫描出来。filtered是100%，这个也很简单了，你没有任何where过滤条件，所以直接筛选出来的数据就是表里数据的100%占比。怎么样，有没有觉得稍微对执行计划有点感觉了，似乎也没那么难是吧？因为有了之前内容的大量铺垫和积累，大家对SQL语句的底层执行原理本身已经有了一定的理解了，所以看执行计划就会很简单的。接着再来看一个SQL语句的执行计划： explain select from t1 join t2 这是一个典型的多表关联语句，之前我们说过，这种关联语句，实际上会选择一个表先查询出来数据，接着遍历每一条数据去另外一个表里查询可以关联在一起的数据，然后关联起来，此时他的执行计划大概长下面这样子：

这个执行计划就比较有意思了，因为是一个多表关联的执行计划。首先很明显，他的执行计划分为了两条，也就是会访问两个表，先看他如何访问第一个表的，针对第一个表就是t1，明显是先用ALL方式全表扫描他了，而且扫出了3457条数据。接着对第二个表的访问，也就是t2表，同样是全表扫描，因为他这种多表关联方式，基本上是笛卡尔积的效果，t1表的每条数据都会去t2表全表扫描所有4568条数据，跟t2表的每一条数据都会做一个关联，而且extra里说了是Nested Loop，也就是嵌套循环的访问方式，跟我们之前讲解的关联语句的执行原理都是匹配的。另外大家会发现上面两条执行计划的id都是1，是一样的，实际上一般来说，在执行计划里，一个 SELECT会对应一个id，因为这两条执行计划对应的是一个SELECT语句，所以他们俩的id都是1，是一样。如果你要是有一个子查询，有另外一个SELECT，那么另外一个SELECT子查询对应的执行计划的id就可能是2了。

今天我们继续来讲解不同SQL语句的执行计划长什么样子，来一起看一个包含子查询的SQL语句的执行计划： EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = ‘xxxx’; 这个SQL就稍微有一点点的复杂了，因为主SELECT语句的WHERE筛选条件是依赖于一个子查询的，而且除此之外还有一个自己的WHERE筛选条件，那么他的执行计划长什么样子呢？我们看看。

这个执行计划值得我们好好分析一下，首先，第一条执行计划的id是1，第二条执行计划的id是2，这是为什么？因为这个SQL里有两个SELECT，主查询SELECT的执行计划的id就是1，子查询SELECT的执行计划的id就是2 其次，第一条执行计划里，select_type是PRIMARY，不是SIMPLE了，说明第一个执行计划的查询类型是主查询的意思，对主查询而言，他有一个where条件是x3=’xxx’，所以他的possible_keys里包含了 index_x3，就是x3字段的索引，但是他的key实际是NULL，而且type是ALL，所以说他最后没选择用x3 字段的索引，而是选择了全表扫描这是为什么呢？其实很简单，可能他通过成本分析发现，使用x3字段的索引扫描xxx这个值，几乎就跟全表扫描差不多，可能x3这个字段的值几乎都是xxx，所以最后就选择还不如直接全表扫描呢。接着第二条执行计划，他的select_type是SUBQUERY，也就是子查询，子查询针对的是t2这个表，当然子查询本身就是一个全表查询，但是对主查询而言，会使用x1 in 这个筛选条件，他这里type是index，说明使用了扫描index_x1这个x1字段的二级索引的方式，直接扫描x1字段的二级索引，来跟子查询的结果集做比对。

接着我们来看另外一个union的SQL语句： EXPLAIN SELECT FROM t1 UNION SELECT FROM t2 这是一个典型的union语句，把两个表的查询结果合并起来，如果大家不理解union的意思，建议自己去网上查一下那么他的执行计划是什么样的呢？
这个执行计划的第一条和第二条很好理解对吧？两个SELECT字句对应两个id，就是分别从t1表和t2表里进行全表扫描罢了接着第三条执行计划是什么呢？其实union字句默认的作用是把两个结果集合并起来还会进行去重，所以第三条执行计划干的是个去重的活儿。所以上面他的table是，这就是一个临时表的表名，而且你看他的extra里，有一个using temporary，也就是使用临时表的意思，他就是把结果集放到临时表里进行去重的，就这么个意思。当然，如果你用的是union all，那么就不会进行去重了。

之前我们已经初步的对SQL执行计划有了一个了解了，现在开始，我们就来更加细致的探索一下执行计划的方方面面，把各种SQL语句的执行计划可能长什么样，都给大家分析出来，首先我们都知道，SQL 执行计划里有一个id的概念。这个id是什么意思呢？简单来说，有一个SELECT子句就会对应一个id，如果有多个SELECT那么就会对应多个id。但是往往有时候一个SELECT字句涉及到了多个表，所以会对应多条执行计划，此时可能多条执行计划的id是一样的。接着我们来看看这个select_type，select_type之前我们似乎看到过几种，有什么SIMPLE的，还有 primary和subquery的，那么这些select_type都是什么意思？除此之外，还有哪几种select_type呢？首先要告诉大家的是，一般如果单表查询或者是多表连接查询，其实他们的select_type都是SIMPLE，这个之前大家也都看到过了，意思就是简单的查询罢了。然后如果是union语句的话，就类似于select from t1 union select from t2，那么会对应两条执行计划，第一条执行计划是针对t1表的，select_type是PRIMARY，第二条执行计划是针对t2表的， select_type是UNION，这就是在出现union语句的时候，他们就不一样了。我们之前给大家讲过，在使用union语句的时候，会有第三条执行计划，这个第三条执行计划意思是针对两个查询的结果依托一个临时表进行去重，这个第三条执行计划的select_type就是union_result。另外，之前我们还看到过，如果是在SQL里有子查询，类似于select * from t1 where x1 in (select x1 ffrom t2) or x3=’xxx’，此时其实会有两条执行计划，第一条执行计划的select_type是PRIMARY，第二条执行计划的select_type是SUBQUERY，这个我们之前也看到过了。

那么现在我们来看一个稍微复杂一点的SQL语句：
EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2 WHERE x1 = ‘xxx’ UNION SELECT x1 FROM t1 WHERE x1 = ‘xxx’); 这个SQL语句就稍微有点复杂了，因为他有一个外层查询，还有一个内层子查询，子查询里还有两个 SELECT语句进行union操作，那么我们来看看他的执行计划会是什么样的呢？

第一个执行计划一看就是针对t1表查询的那个外层循环，select_type就是PRIMARY，因为这里涉及到了子查询，所以外层查询的select_type一定是PRIMARY了。然后第二个执行计划是子查询里针对t2表的那个查询语句，他的select_type是DEPENDENT SUBQUERY，第三个执行计划是子查询里针对t1表的另外一个查询语句，select_type是DEPENDENT UNION，因为第三个执行计划是在执行union后的查询，第四个执行计划的select_type是UNION RESULT，因为在执行子查询里两个结果集的合并以及去重

现在再来看一个更加复杂一点的SQL语句： EXPLAIN SELECT FROM (SELECT x1, count() as cnt FROM t1 GROUP BY x1) AS _t1 where cnt > 10;

上回我们通过一些复杂的SQL语句给大家讲解了执行计划里的select_type一般都会有哪些取值，这次我们再来看看执行计划里的type有哪些取值，其实select_type并不是很关键，因为他主要是代表了大SQL 里的不同的SELECT代表了一个什么角色，比如有的SELECT是PRIMARY查询，有的是UNION，有的是 SUBQUERY。但是这个type就非常关键了，因为他直接决定了对某个表是如何从里面查询数据的，关于这个查询方式我们之前早就讲过了，包括了const、ref、range、index、all这几种方式，分别是根据主键/唯一索引查询，根据二级索引查询，对二级索引进行全索引扫描，对聚簇索引进行全表扫描。那今天我们就重点来通过几个SQL语句来看看在什么情况下会有什么样的type取值。首先，假设是类似于select * fromt1 where id=110这样的SQL，直接根据主键进行等值匹配查询，那执行计划里的type就会是const，意思就是极为快速，性能几乎是线性的。事实也确实是极为快速的，因为主键值是不会重复的，这个唯一值匹配，在一个索引树里跳转查询，基本上几次磁盘IO就可以定位到了。

接着我们来看一个SQL语句： EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id 这里是通过两个表的id进行关联查询的，此时他的执行计划如下：
在这个执行计划里，我们会发现针对t1表是一个全表扫描，这个是必然的，因为关联的时候会先查询一个驱动表，这里就是t1，他没什么where筛选条件，自然只能是全表扫描查出来所有的数据了。接着针对t2表的查询type是eq_ref，而且使用了PRIMARY主键。这个意思就是说，针对t1表全表扫描获取到的每条数据，都会去t2表里基于主键进行等值匹配，此时会在t2表的聚簇索引里根据主键值进行快速查找，所以在连接查询时，针对被驱动表如果基于主键进行等值匹配，那么他的查询方式就是eq_ref 了。而如果要是正常基于某个二级索引进行等值匹配的时候，type就会是ref，而如果基于二级索引查询的时候允许值为null，那么查询方式就会是ref_or_null 另外之前讲过，有一些特殊场景下针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方式会是index_merge这种。而查询方式是range的话就是基于二级索引进行范围查询，查询方式是index的时候是直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据，最后如果是all的话就是全表扫描，也就是对聚簇索引的叶子节点扫描每条数据。

今天我们继续来讲解执行计划的一些细节，之前已经详细讲过了select_type和type，今天来先讲一下 possible_keys 这个possible_keys，顾名思义，其实就是在针对一个表进行查询的时候有哪些潜在可以使用的索引。比如你有两个索引，一个是KEY(x1, x2, x3)，一个是KEY(x1, x2, x4)，此时要是在where条件里要根据x1 和x2两个字段进行查询，那么此时明显是上述两个索引都可以使用的，那么到底要使用哪个呢？此时就需要通过我们之前讲解的成本优化方法，去估算使用两个索引进行查询的成本，看使用哪个索引的成本更低，那么就选择用那个索引，最终选择的索引，就是执行计划里的key这个字段的值了。而key_len，其实就是当你在key里选择使用某个索引之后，那个索引里的最大值的长度是多少，这个就是给你一个参考，大概知道那个索引里的值最大能有多长，就这么个意思。而执行计划里的 ref 也相对会关键一些，当你的查询方式是索引等值匹配的时候，比如const、ref、 eq_ref、ref_or_null这些方式的时候，此时执行计划的ref字段告诉你的就是：你跟索引列等值匹配的是什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？

比如SQL语句： EXPLAIN SELECT * FROM t1 WHERE x1 = ‘xxx’ 此时如果你看他的执行计划是下面这样的

大家在上面的查询计划里可以看到，针对t1表的查询，type是ref方式的，也就是说基于普通的二级索引进行等值匹配，然后possible_keys只有一个，就是index_x1，针对x1字段建立的一个索引，而实际使用的索引也是index_x1，毕竟就他一个是可以用的。然后key_len是589，意思就是说index_x1这个索引里的x1字段最大值的长度也就是589个字节，其实这个不算是太大，不过基本可以肯定这个x1字段是存储字符串的，因为是一个不规律的长度。比较关键的是ref字段，它的意思是说，既然你是针对某个二级索引进行等值匹配的，那么跟index_x1 索引进行等值匹配的是什么？是一个常量或者是别的字段？这里的ref的值是const，意思就是说，是使用一个常量值跟index_x1索引里的值进行等值匹配的。

假设你要是用了类似如下的语句： EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; 此时执行计划里的ref肯定不是const，因为你跟t1表的id字段等值匹配的是另外一个表的id字段，此时 ref的值就是那个字段的名称了，执行计划如下：
大家看执行计划，针对t1表作为驱动表执行一个全表扫描，接着针对t1表里每条数据都会去t2表根据t2 表的主键执行等值匹配，所以第二个执行计划的type是eq_ref，意思就是被驱动表基于主键进行等值匹配，而且使用的索引是PRIMARY就是使用了t2表的主键。至于ref，意思就是说，到底是谁跟t2表的聚簇索引里的主键值进行等值匹配呢？是常量值吗？不是，是test_db这个库下的t1表的id字段，这里跟t2表的主键进行等值匹配的是t1表的主键id字段，所以ref这里显示的清清楚楚的。最后简单说一下rows和filtered，这个rows顾名思义，就是说你使用指定的查询方式，会查出来多少条数据，而filtered意思就是说，在查询方

这周其实我们主要就是研究一下执行计划里的extra这个字段里的内容都是代表什么的，其实很多人可能以为extra字段是无关紧要的，其实并不是，因为除了extra字段以外的其他内容，最多就是告诉你针对你SQL里的每个表是如何查询的，用了哪个索引，查出来了多少数据，但是很多时候，往往针对一个表可不是那么简单的。因为除了基于索引查询数据，可能同时还得基于where条件里的其他过滤条件去筛选数据，此时还会筛选出来一些数据。这个extra里的信息可能会非常非常的多，我们不可能给大家都讲一遍，很多其实也偶尔出现，也没多大意义，大家看到了自然也明白。我们主要是给大家讲一些平时常见的，比较有用的extra信息。

比如下面的SQL语句： EXPLAIN SELECT x1 FROM t1 WHERE x1 = ‘xxx’ 可以看看他的执行计划是什么样的这里我们可以看一下，这个执行计划现在什么意思，可以说是一清二楚。首先他是访问了t1表，使用的是ref访问方法，也就是基于二级索引去查找，找的是index_x1这个索引，这个索引的最大数据长度是 456字节，查找的目标是一个const代表的常量值，通过索引可以查出来25条数据，经过其他条件筛选过后，最终剩下数据是100%。好，那么我们看看extra的信息，是Using index，这是什么意思呢？其实就是说这次查询，仅仅涉及到了一个二级索引，不需要回表，因为他仅仅是查出来了x1这个字段，直接从index_x1索引里查就行了。如果没有回表操作，仅仅在二级索引里执行，那么extra里会告诉in是Using index。

另外，如果有个SQL语句是： SELECT * FROM t1 WHERE x1 > ‘xxx’ AND x1 LIKE ‘%xxx’ 此时他会先在二级索引index_x1里查找，查找出来的结果还会额外的跟x1 LIKE ‘%xxx’条件做比对，如果满足条件的才会被筛选出来，这种情况下，extra显示的是Using index condition。

今天我们继续讲执行计划里的extra的信息，给大家讲一个平时最常见到的东西，就是Using where，这个恐怕是最最常见的了，其实这个一般是见于你直接针对一个表扫描，没用到索引，然后where里好几个条件，就会告诉你Using where，或者是你用了索引去查找，但是除了索引之外，还需要用其他的字段进行筛选，也会告诉你Using where。比如说下面的SQL语句： EXPLAIN SELECT * FROM t1 WHERE x2 = ‘xxx’ 这里的x2是没有建立索引的，所以此时他的执行计划就是下面这样的大家注意看，这里说了，针对t1表进行查询，用的是全表扫描方式，没有使用任何索引，然后全表扫描，扫出来的是4578条数据，这个时候大家注意看extra里显示了Using where，意思就是说，他对每条数据都用了WHERE x2 = ‘xxx’去进行筛选。最终filtered告诉了你，过滤出来了15%的数据，大概就是说，从这个表里筛选出来了686条数据，就这个意思。

今天我们来看看执行计划里平时常见的最后两种，一个是Using filesort，一个是Using temprory。先来看看Using filesort是什么意思，首先大家要知道，有的时候我们在SQL语句里进行排序的时候，如果排序字段是有索引的，那么其实是直接可以从索引里按照排序顺序去查找数据的，比如这个SQL： EXPLAIN SELECT * FROM t1 ORDER BY x1 LIMIT 10 这就是典型的一个排序后再分页的语句，他的执行计划如下大家可以看到，这个SQL语句，他是用了index方式访问的，意思就是说直接扫描了二级索引，而且实际使用的索引也是index_x1，本质上来说，他就是在 index_x1索引里，按照顺序找你LIMIT 10要求的 10条数据罢了。所以大家看到返回的数据是10条，也没别的过滤条件了，所以filtered是100%，也就是10条数据都返回了。

比如这个SQL： EXPLAIN SELECT x2, COUNT(*) AS amount FROM t1 GROUP BY x2 这里的x2是没有索引的，所以此时的执行计划如下这个SQL里只能对全表数据放到临时表里做大量的磁盘文件操作，然后才能完成对x2字段的不同的值去分组，分组完了以后对不同x2值的分组去做聚合操作，这个过程也是相当的耗时的，性能是极低的。所以大家最后记住，其实未来在SQL调优的时候，核心就是分析执行计划里哪些地方出现了全表扫描，或者扫描数据过大，尽可能通过合理优化索引保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据。

MySQL必知必会

8、MySQL执行计划原理分析

1、以MySQL单表查询来举例，看看执行计划包含哪些内容

1、const

2、ref

3、ref_or_null

4、range

5、index

2、深入探索多表关联的SQL语句到底是如何执行的？

3、 MySQL是如何根据成本优化选择执行计划的？

4、透彻研究通过explain命令得到的SQL执行计划

8、MySQL执行计划原理分析

1、 以MySQL单表查询来举例，看看执行计划包含哪些内容

1、const

2、ref

3、ref_or_null

4、range

5、index

2、 深入探索多表关联的SQL语句到底是如何执行的？

3、 MySQL是如何根据成本优化选择执行计划的？

4、 透彻研究通过explain命令得到的SQL执行计划

1、以MySQL单表查询来举例，看看执行计划包含哪些内容

2、深入探索多表关联的SQL语句到底是如何执行的？

4、透彻研究通过explain命令得到的SQL执行计划