SQL语句的执行计划

设计索引、并让查询使用到索引，只是SQL优化的一部分。

SQL执行计划：

对于涉及到多表的上百行的SQL，每个表都有自己的索引，那么这个SQL跑起来的时候，是如何使用到各个索引，如何读取数据的？

在实际的MYSQL底层，针对磁盘上的大量数据表、聚簇索引、二级索引，如何检索查询，如何筛选过滤、如何使用函数、如何进行排序、分组，这就是SQL执行计划。

了解了SQL执行计划，才能进行SQL优化！

单表查询的执行计划：

通过explaint + sql语句，查看type字段，来判断是否走了索引：

• Const:  主键索引，唯一索引

• Ref: 普通索引，联合索引的等值比较，或是唯一索引或主键索引搞了一个IS NULL/IS NOT NULL

• Ref_or_null: 普通索引中搞了个IS NULL/IS NOT NULL

• Range: 普通索引中使用了范围搜索

• Index:  索引是KEY(x1,x2,x3) 查询是：select x1,x2,x3 from table where x2=xxx，此时没办法使用到联合索引，但是联合索引的字段可以覆盖所有select的字段，所以可以只查找联合索引所在B+树，不需要回表查询

• All: 全表扫描

Select * from table where x1=xx and x2>=xx,  Index(x1,x3),  Index(x2,x4)：

此时，Mysql只能选择一个索引去执行此条SQL，这里MYSQL负责生成执行计划的查询优化器，一般会选择在索引里扫描行数比较少的那个索引。

比如x1，在索引里做等值比较，扫描数据比较少，那么就会挑选Index(x1,x3)，做一个索引树的查找(ref)，找到几条数据之后，接着做一个回表，回到聚簇索引中找到每条数据的完整数据，接着加载到内存中去，根据每条数据的x2字段的值，根据x2>=xx做一个筛选。

Select * from table where x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL, Index(x1):

此时优化器生成的执行计划中，就仅仅针对x1字段走一个ref访问，直接通过x1字段的索引树找出来一波数据，接着对这波数据都回表查询，然后加载到内存中，接着对c1,c2,c3做过滤和筛选。

Select * from table where x1=xx and x2=xx， Index(x1), Index(x2)：

一般情况下，一个SQL语句只能用到一个二级索引。但是如果选择扫描行数最少的那个索引（比如x1）之后，过滤出来的数据量还是有上万条，如果直接回表查询，性能不佳。此时，会同时从x2的索引树中也查出来一波数据，然后做交集之后，数据量会减少很多，此时再做回表查询，性能会提示很多。

多表关联的执行计划：

嵌套循环关联：

有两个表要执行关联，此时会在一个驱动表里根据他的where条件筛出一批数据，然后把这批数据的每一条数据到被驱动表里查找数据，找出来的数据进行关联。类似与Java里面的for循环嵌套。

所以关联表一定不要超过三张。

保证驱动表走索引，同时被驱动表也走索引。

MYSQL如何根据成本优化，选择执行计划的？

把一个数据页从磁盘中读取到内存中的IO成本假定为1.0

在内存中鉴定一条数据是否符合条件的CPU成本假定为0.2

全表扫描的成本：

IO成本：数据页的数量 * 1.0 + 微调值

CPU成本：行记录数量 * 0.2 + 微调值

总成本 = IO成本 + CPU成本

假设一张表有数据2万条，数据页100个，那么全表扫描的总成本 = 100 * 1.0 + 20000 * 0.2 = 4100

二级索引的成本：

1、将二级索引的数据页读取到内存中：因为二级索引存储的字段值很少，一般一个数据页可以存放大量二级索引值，所以可以粗暴的认为，二级索引涉及到N个范围则为N个数据页。IO成本 = 1.0 * 1，或是1.0 * n

2、假设经过二级索引筛选之后，数据量为100，将二级索引的数据页在内存中进行CPU判定，CPU成本 =  0.2 * 数据量 = 0.2 * 100 = 20

3、回表到聚簇索引中，将指定的数据页加载到内存中，由于经过二级索引筛选之后的id值并不连续，所以加载数据页的数量大概等于id值数量，也就是100个数据页，IO成本 = 1.0 * 数据量 = 1.0 * 100 = 100

4、聚簇索引的数据页在内存中进行CPU判定，CPU成本 = 0.2 * 数据量 = 0.2 * 100 = 20

5、总成本 = 1 + 20 + 100 + 20 = 141

MYSQL是如何根据各种规则去优化执行计划的？

其实就是MYSQL对你写的SQL进行重写，从而便于执行。

去括号

常量替换

多表联查时的常量替换

子查询：先查出来子查询的结果集，然后再进行外部查询

IN子查询：先查出来子查询的结果集，然后对结果集数量和外部表数量进行比较，如果子查询结果集数量 > 外部数据表行数，则遍历外部数据表的每个值，去子查询中去判断；反之，则遍历子查询的每个值去外部数据表中去判断。总之，遵循一个原则：驱动表是数据量少的表！外层嵌套的数量值应该小于内层。

Mysql内部架构_jerry_dyy的博客-CSDN博客

Mysql存储模型_jerry_dyy的博客-CSDN博客

InnoDB内部架构_jerry_dyy的博客-CSDN博客

Buffer Pool 核心原理_jerry_dyy的博客-CSDN博客

Buffer Pool生产实践_jerry_dyy的博客-CSDN博客

Mysql事务隔离机制_jerry_dyy的博客-CSDN博客

Mysql的锁机制_jerry_dyy的博客-CSDN博客

Mysql的索引深度讲解_jerry_dyy的博客-CSDN博客

Mysql索引的使用_jerry_dyy的博客-CSDN博客

SQL语句的执行计划_jerry_dyy的博客-CSDN博客_sql语句的执行计划