Mysql基础(十五)：SQl执行计划和性能优化

目录

写在前面

1、SQL语句的执行计划和性能优化有什么关系？

2、以MySQL单表查询为例，执行计划包含哪些内容（1）？

4、重温写出各种SQL语句的时候，会用什么执行计划（1）

5、重温写出各种SQL语句的时候，会用什么执行计划（2）

6、重温写出各种SQL语句的时候，会用什么执行计划（3）

7、多表关联的SQL语句到底是如何执行的？（1）

8、多表关联的SQL语句到底是如何执行的？（2）

9、多表关联的SQL语句到底是如何执行的？（3）

10、MySQL是如何根据成本优化选择执行计划的？（上）

11、MySQL是如何根据成本优化选择执行计划的？（中）

12、MySQL是如何根据成本优化选择执行计划的？（下）

13、MySQL是如何基于各种规则去优化执行计划的？（上）

15、MySQL是如何基于各种规则去优化执行计划的？（下）

---

写在前面

        一般开发一个系统，都是先设计表结构，表结构必须满足业务需求，然后写代码，代码都写好之后，再根据你的代码如何查询表的，来设计里面的索引，考虑设计几个索引，是不是联合索引，选择哪些字段，字段顺序如何排列，才能让查询语句都用上索引。

1、SQL语句的执行计划和性能优化有什么关系？

执行计划：

        每次你提交一个SQL 给 MySQL ，他内核里的查询优化器，都会针对这个 SQL 语句的语义去生

成一个执行计划，这个执行计划就代表了他会怎么查各个表，用哪些索引，如何做排序和分组，看懂这个执行计划，你就学会了真正的SQL 优化的一半了！

2、以MySQL单表查询为例，执行计划包含哪些内容（1）？

const:        

•  聚簇索引
•  二级索引 （唯一索引）+ 聚簇索引

        对于select * from table where id=x，或者select * from table where name=x的语句，直接就可以通过聚簇索引或者二级索引+聚簇索引回表，轻松查到你要的数据，这种根据索引直接可以快速查找数据的过程，在执行计划里称之为const，意思是性能超高的常量级。

        故在执行计划里看到const 时，就知道是直接通过索引定位到数据，速度极快 。这里有一个要点， 二级索引必须是唯一索引，才是属于const方式的，也就是说你必须建立unique key唯一索引，保证一个二级索引的每一个值都是唯一的，才可 以。

ref:

• 二级索引（普通索引）+ 聚簇索引
• 联合索引（从索引最左侧开始连续多个列都是等值比较）
• 即使name是主键或者唯一索引，当用name IS NULL这种语法的话，还是只能走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) 。

ref_or_null:

• 二级索引里搜你要的值以及是NULL的值，然后再回表到聚簇索引里查

        如果是针对一个二级索引同时比较了一个值还限定了IS NULL ，类似于

select * from table where name=x and name IS NULL

那么此时在执行计划里就叫做ref_or_null。就是在二级索引里搜你要的值以及是NULL 的值，然后再回源去聚簇索引里查罢了， 因为同时有索引等值比较和NULL值查询，就叫做ref_or_null 了。

总结：

• 看到了const，肯定是通过主键或者唯一索引的访问，速度超高。
• 看到了ref，就是用了普通的索引，或者用主键/唯一索引搞了一个IS NULL/IS NOT NULL。
• 只要你看到const或者ref，那恭喜你，说明起码这部分执行速度是很快的！

3、以MySQL单表查询为例，执行计划包含哪些内容（2）？

range:

• range就是SQL里有范围查询的时候就会走该方式。

        比如写一个SQL 是 select * from table where age>=x and age <=x ，假设 age 就是一个普通索引，此时就必然利用索引来进行范围筛选，一旦利用索引做了范围筛选，就是range 。

        假设你在执行计划里看到了const 、 ref 和 range，他们都是说基于索引在查询，总之都是走索引，所以一般问题不是太大，除非你通过索引查出来的数据量太多了，比如上面那个范围筛选，一下子查出来 10 万条数据，那不是想搞死 MySQL 么！

index:

• 只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式       

        假设我们有一个表，里面完整的字段联合索引是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访问方式 ！

4、重温写出各种SQL语句的时候，会用什么执行计划（1）

1. const、ref和range，本质都是基于索引查询，基于索引树的二分查找和多层跳转来查询，只要索引查出来的数据量不是特别大，一般性能都极为高效。
2. index稍微次一点，需要遍历某个二级索引，但是因为二级索引比较小，所以遍历性能也还可以的。
3. all最次，就是全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行行数据扫描，如果一个表就几百条数据还好，如果有几万或几十万，几百万数据。全表扫描就跪了。

select * from table where x1=xx or x2>=xx

        该SQL语句要查一个表，用了x1和x2两个字段，此时有人觉得要是对x1和x2建了一个联合索引，那不就直接可以通过索引去扫描了？但万一要是你建的索引是两个呢？比如(x1,x3)，(x2,x4)，你建了两个联合索引，此时你这个SQL只能选择其中一个索引去用，此时会选择哪个呢？

        MySQL负责生成执行计划的查询优化器，一般会选择在索引里扫描行数比较少的那个条件。

        比如说x1=xx ，在索引里只要做等值比较，扫描数据比较少，那么可能就会挑选 x1 的索引，做一个索引树的查找，在执行计划里，其实就是一个ref 的方式，找到几条数据之后，接着做一个回表，回到聚簇索引里去查出每条数据完整数据，接着加载到内存里，根据每条数据的x2 字段的值，根据 x2>=xx 条件做一个筛选。

select * from table where x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL

        在一个SQL 的所有筛选条件里，就一个 x1 是有索引的，其他字段都是没有索引的。这种情况其实也是非常常见的，一般我们在写好一个系统之后，针对所有的 SQL 分析时，当然不可能针

对所有的 SQL 里的每一个 where 里的字段都加一个索引，那是不现实的， 最终我们只能在所有的SQL语句里，抽取部分经常在where里用到的字段来设计两三个联合索引 。

        所以在这种情况下，必然很 多SQL语句里，可能where后的条件有好几个，结果就一个字段可以用到索引的，此时查询优化器生成的执行计划，就会仅仅针对x1字段走一个ref访问，直接通过x1字段的索引树快速查找到指定的一波数据 。

        接着对这波数据都回表到聚簇索引里去，把每条数据完整的字段都查出来，然后都加载到内存里去。接着就可以针对这波数据的c1 、 c2 、 c3 字段按照条件进行筛选和过滤，最后 拿到的就是符合条件的数据了。

        所以x 1索引的设计，必然尽可能是要让x1=xx这个条件在索引树里查找出来的数据量比较少，才能保证后续的 性能比较高。

5、重温写出各种SQL语句的时候，会用什么执行计划（2）

        一般一个SQL语句只能用到一个二级索引，但是有一些特殊的情况下，可能会对一个SQL语句用到多个二级索引。

select * from table where x1=xx and x2=xx

        然后x1和 x2 两个字段分别都有一个索引，其实也有一定的可能会让查询优化器生成一个执行计划， 执行计划里，就先对x1字段的索引树进行查找，查出一波数据，接着对x2的索引树查出一波数据，然后对两波数据，按照主键值做一个交集 。这个交集就是符合两个条件的数据了，接着回表到聚簇索引去查完整数据就可以了。

        之前我们对这种情况一直是选择x1 或者 x2 其中一个字段的索引，就查一个字段的索引，找出一波数据，接着直接回表到聚簇索引查完整数据，然后根据另外一个字段的值进行过滤就可以了。

        那么到底什么情况下，会直接对两个字段的两个索引一起查，然后取交集再回表到聚簇索引呢？也就是什么情况下可能会对一个SQL 执行的时候，一下子查多个索引树呢？

1. 假设该SQL语句，比如你x1和x2两个字段，如果你先查x1字段的索引，一下子弄出来上万条数据，这上万条数据都回表到聚簇索引查完整数据，再根据x2来过滤，效果不是太好。
2.  如果说同时从x2的索引树里也查一波数据做一个交集，一下就可以让交集数据量变成几十条再回表查询速度就很快。一般来说，查索引树都很快，但到聚簇索引回表查询会慢一些。

•  所以如果同时查两个索引树取一个交集后，数据量很小，然后再回表到聚簇索引去查，此时会提升性能。
• 但是如果要在一个SQL里用多个索引，那有很多硬性条件的要求，比如说如果有联合索引，你必须把联合索引里每个字段都放SQL里，而且必须都是等值匹配；
• 或者是通过主键查询+其他二级索引等值匹配，也有可能会做一个多索引查询和交集。

select * from table where x1=xx or x2=xx

        也可能会用多个索引，只不过查多个大索引树之后，会取一个并集。

记住： 在执行SQL语句的时候，有可能是会同时查多个索引树取个交集，再回表到聚簇索引。

6、重温写出各种SQL语句的时候，会用什么执行计划（3）

•   const、ref、range都是性能最好的方式，说明在底层直接基于某个索引树快速查找了数据 ，但有时可能在用了索引之后，还会回表到聚簇索引里查完整数据，接着根据其他条件过滤。
• index方式是扫描二级索引，就是说不通过索引树的根节点开始快速查找，而是直接对 二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，大家尽量还是别出现这种情况。
• all就是直接全表扫描了，也就是直接扫描聚簇索引的叶子节点，那是相当的慢，index虽然扫描的是二级索引的叶子节点，但是起码二级索引的叶子节点数据量比较小，相对all要快一些。
• 可能一个SQL里用多个索引，意思就是可能对多个索引树进行查找，接着用 intersection交集、union并集的方式来进行合并。

7、多表关联的SQL语句到底是如何执行的？（1）

多表关联查询的执行原理：

select * from t1,t2 where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx

• 首先，如果你在 from 字句后直接来了两个表名，这意思就是要针对两个表进行查询了，而且会把两个表的数据给关联起来，假设你要是没有限定什么多表连接条件，那么可能会搞出一个笛卡尔积的东西。
  • 举个例子，假设t1表有10条数据，t2表有5条数据，那么此时select * from t1,t2，其实会查出来50条数据，因为t1表里的每条数据都会跟t2表里的每条数据连接起来返回给你，那么不就是会查出来10 * 5 = 50条数据吗？这就是笛卡尔积。通常都会在多表关联语句中的WHERE子句里引入一些关联条件。

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条数据分别关联起来，最终会返回给你两条关联后的数据。

执行过程：

•  首先，根据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的两条数据，关联起来，就可以。

记住： 他可能是先从一个表里查一波数据，这个表叫做“驱动表”，再根据这波数据去另外一个表里查一波数据进行关联，另外一个表叫做“被驱动表” 。

8、多表关联的SQL语句到底是如何执行的？（2）

假设我们有一个员工表，还有一个产品销售业绩表，员工表里包含了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 万

执行原理：

从员工表里走全表扫描，找出每个员工，然后针对每个员工的id 去业绩表里找 employee_id 跟员工 id 相等的数据，可能每个员工的 id 在业绩表里都会找到多条数据，因为他可能有多个产品的销售业绩。 然后就是把每个员工数据跟他在业绩表里找到的所有业绩数据都关联起来，比如张三这个员工就关联了 业绩表里的三条数据，李四这个员工关联上了业绩表里的两条数据。

内连接inner join，就是要求两个表里的数据必须是完全能关联上的，才能返回回来。

那么现在有这么一个问题，假设员工表里有一个人是新员工，入职到现在一个单子都没开过，也就没有任何的销售业绩，那么此时还是希望能够查出来这个员工的数据，只不过他的销售业绩那块可以给个NULL就行了，表示他没任何业绩。 但是如果仅仅是使用上述 SQL 语法，似乎是搞不定的，因为那种语法要求，必须要两个表能关联上的数 据才会查出来，像你员工表里可能有个王五，根本在业绩表里关联不上任何数据，此时这个人是不会查出来的。

外连接outer join，分为左外连接和右外连接。

• 左外连接：在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来。
• 右外连接：在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来。
• 内连接，那么连接条件是可以放在where语句里的。
• 外连接一般是把连接条件放在ON字句里的。

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

9、多表关联的SQL语句到底是如何执行的？（3）

嵌套循环关联（nested-loop join）：假设有两个表要一起执行关联，此时会先在一个驱动表里根据他的where筛选条件找出一波数据，比如说找出10条数据吧。接着呢，就对这10条数据走一个循环，用每条数据都到另外一个被驱动表里去根据ON连接条件和WHERE里的被驱动表筛选条件去查找数据，找出来的数据就进行关联。依次类推，假设驱动表里找出来10条数据，那么就要到被驱动表里去查询10次！

        那么如果是三个表进行关联呢？那就更夸张了，你从表1 里查出来 10 条数据，接着去表 2 里查 10 次，假设每次都查出来3 条数据，然后关联起来，此时你会得到一个 30 条数据的结果集，接着再用这批数据去表3 里去继续查询 30 次！

这种方法的伪代码有点类似下面这样：

         上面那伪代码其实就是3个表关联的伪代码，用的就是最笨的嵌套循环关联方法。不知道大家有没有发现上面那种多表关联方法的问题在哪里？

• 问题1: 就是我们往往从驱动表里查出来一波数据之后，要对每一条数据都循环一次去被驱动表里查询数据，所以万一你要是被驱动表的索引都没建好，总不能每次都全表扫描吧？
• 问题2；刚开始对你的驱动表根据WHERE条件进行查询的时候，也总不能全表扫描吧？

        所以说，为什么有的时候多表关联很慢呢？答案就在这里了，你两个表关联，先从驱动表里根据WHERE条件去筛选一波数据，这个过程如果你没给驱动表加索引，万一走一个 all 全表扫描，岂不是速度很慢？

        其次，假设你好不容易从驱动表里扫出来一波数据，接着又来一个for 循环一条一条去被驱动表里根据ON连接条件和 WHERE 筛选条件去查，万一你对被驱动表又没加索引，难道又来几十次或者几百次全表扫描？那速度岂不是慢的跟蜗牛。

总结：
         针对多表查询的语句，尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，对被驱动表查询也通过索引去查找 。如果做到这一点，多表关联语句性能就会很高！

10、MySQL是如何根据成本优化选择执行计划的？（上）

全表扫描执行计划的成本计算方法：        

MySQL是如何对一个查询语句的多个执行计划评估他的成本的？如何根据成本评估选择一个成本最低的执行计划，保证最佳的查询速度？

MySQL里的成本是什么意思？

 就是跑一个SQL语句，一般成本是两块：

• IO成本：首先是那些数据如果在磁盘里，从磁盘读数据到内存就是IO成本，而且MySQL里都是一页一页读的，读一页的成本的约定为1.0。
• CPU成本：你拿到数据之后，是不是要对数据做一些运算？比如验证他是否符合搜索条件了，或者是搞一些排序分组之类的事，这些都是耗费CPU资源的，属于CPU成本，一般约定读取和检测一条数据是否符合条件的成本是0.2。

        这个所谓1.0 和 0.2 就是他自定义的一个成本值，代表的意思就是一个数据页 IO 成本就是 1.0 ，一条数据检测的CPU 成本就是0.2。

举例：

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 是估计值。 r ows就是表里的记录数，data_length就是表的聚簇索引的字节数大小，此时用data_length除以1024就是kb为单位的大小，然后再除以16kb（默认一页的大小），就是有多少页，此时知道数据页的数量和rows记录数 ，就可以计算全表扫描的成本了。

IO成本就是：数据页数量 * 1.0 + 微调值，CPU成本就是：行记录数 * 0.2 + 微调值，他们俩相加，就是一个总的成本值。

比如你有数据页100 个，记录数有 2 万条，此时总成本值大致就是100 + 4000 =4100，在这个左右。

11、MySQL是如何根据成本优化选择执行计划的？（中）

索引的成本计算方法：

        因为除了全表扫描之外，还可能多个索引都可以使用，但是当然同时一般只能用一个索引，所以不同索引的使用成本都得计算一下。

        直接根据主键查，那就直接走一个聚簇索引就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 ，所以，很多时候，使用索引和全表扫描，他的成本差距是非常之大的。所以一般就会针对全表扫描和各个索引的成本，都进行估算，然后比较一下，选择一个成本最低的执行计划。

12、MySQL是如何根据成本优化选择执行计划的？（下）

多表关联查询是如何选择执行计划的：

       单表查询的时候，主要就是对这个表的多种访问方式（全表查询 ，各个索引查询）来根据一定的公式计算出来每种访问方式的成本，接着选择一个成本最低的访问方式，那么就可以确定下来这个表怎么访问了。

        因为要在一个查询执行之前，就可以针对不同的访问方法精准计算他的成本，那是根本不现实的，最后只能是根据一些相对较为简单粗暴的办法，大致估算一下，估算结果可能不是太准确，但是也没办法了，反正算出来也就这么比较就是了。

多表关联的语句，比如：

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 表里把数据给查找出来，就可以，这就完成了多表关联！所以大家可以看到，其实多表关联的成本估算以及执行计划选择方式，跟单表关联基本上是差不多的。只不过多表关联要多查几个表罢了。

13、MySQL是如何基于各种规则去优化执行计划的？（上）

        MySQL在执行一些相对较为复杂的SQL语句的时候是如何对查询进行重写来优化具体的执行计划的，因为他有时候可能会觉得你写的SQL一点都不好，直接按你的SQL生成的执行，计划效率还是不够高，需要自动帮你改改。

• 首先，要是MySQL觉得你的SQL里有很多括号，那么无关紧要的括号他会给你删除了。
• 其次比如你有类似于i = 5 and j > i这样的SQL，就会改写为i = 5 and j > 5，做一个常量替换。
• 还有比如x = y and y = k and k = 3这样的SQL，都会给你优化成x = 3 and y = 3 and k = 3，本质也是做个常量替换。
• 或者是类似于什么b = b and a = a这种一看就是乱写的SQL，一看就是没意义的，就直接给你删了。

比较有意思的改写，比如下面的 SQL 语句：

select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1

        这个SQL 明显是针对 t1 表的 id 主键进行了查询，同时还要跟 t2 表进行关联，其实这个 SQL 语句就可能在执行前就先查询t1 表的 id=1 的数据，然后直接做一个替换，把 SQL 替换为：

select t1 表中 id=1 的那行数据的各个字段的常量值 , t2.* from t1 join t2 on t1 表里 x1 字段的常量值=t2.x1

上面的SQL就是直接把 t1 相关的字段都替换成了提前查出来的 id=1那行数据的字段常量值了。        

 记住：其实你写的SQL语句真正执行时，可能Mysq是会对SQL进行各种改动的。

14、 MySQL是如何基于各种规则去优化执行计划的？（中）

       

        子查询是如何执行的**，以及他的执行计划是如何优化的**

select * from t1 where x1 = (select x1 from t2 where id=xxx)

        这就是一典型子查询也就是说上面的SQL语句在执行的时候，其实会被拆分为两个步骤：

• 第一个步骤先执行子查询，也就是：select x1 from t2 where id=xxx，直接根据主键定位出一条数据的x1字段的值。
• 接着再执行select * from t1 where x1=子查询的结果值，这个SQL语句。

还有一种子查询，就是：

select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)

        你会发现 子查询里的where条件依赖于t1表的字段值，所以这种查询就会效率很低下 ，他需

要遍历 t1 表里每一条数据，对每一条数据取出 x2 字段的值，放到子查询里去执行，找出 t2 表的某条数据的x1 字段的值，再放到外层去判断，是否符合跟 t1 表的 x1 字段匹配。

IN语句结合子查询的一个优化手段：

假设有如下的一个 SQL 语句：

select * from t1 where x1 in (select x2 from t2 where x3=xxx)

        这个SQL 语句就是典型的一个子查询运用，子查询查一波结果，然后判断 t1 表哪些数据的 x1 值在这个结果集里。

•   这个可能大家会想当然的认为先执行子查询，然后对t1表再进行全表扫描，判断每条数据是否在这个子查询的结果集里，但是这种方式其实效率是非常低下的。
• 所以其实对于上述的子查询，执行计划会被优化为，先执行子查询，也就是select x2 from t2 where x3=xxx这条SQL语句，把查出来的数据都写入一个临时表里，也可以叫做物化表，意思就是说，把这个中间结果集进行物化。
  • 这个物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能采用普通的b+树聚簇索引的方式放在磁盘里。但是无论如何，这个物化表都会建立索引，所以大家要清楚，这波中间结果数据写入物化表是有索引的。

        接着大家可能会想，此时是不是全表扫描t1 表，对每条数据的 x1 值都去物化表里根据索引快速查找一下是否在这个物化表里？如果是的话，那么就符合条件了。但是这里还有一个优化的点，那就是他可以反过来思考。

        也就是说，假设t1 表的数据量是 10 万条，而 物化表的数据量只有500条，那么此时完全可以改成全表扫描物化表，对每个数据值都到t1表里根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1索引树里 ，如果在的话，那么就符合条件了。

15、MySQL是如何基于各种规则去优化执行计划的？（下）

        MySQL里对子查询的执行计划进行优化的一种方式，就是semi join，也就是半连接

其实就是假设你有一个子查询语句：

select * from t1 where x1 in (select x2 from t2 where x3=xxx)

此时其实可能会在底层把他转化为一个半连接，有点类似于下面的样子：

select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx

当然，其实并没有提供 semi join 这种语法，这是 MySQL 内核里面使用的一种方式，上面就是给大家说那么个意思，其实上面的 semi join的语义，是和IN语句+子查询的语义完全一样的 ，他的意思就是说，对于t1 表而言，只要在 t2 表里有符合 t1.x1=t2.x2 和 t2.x3=xxx 两个条件的数据就可以了，就可以把 t1 表的数据筛选出来了。

注意：

        在互联网公司里，我们比较崇尚的是尽量写简单的SQL，复杂的逻辑用Java系统来实现就可以了，SQL能单表查询就不要多表关联，能多表关联就尽量别写子查询，能写几十行SQL就别写几百行的SQL，多考虑用Java代码在内存里实现一些数据就的复杂计算逻辑，而不是都放SQL里做。