说明

文章的图片来源《MySQL是怎么运行的：从根儿上理解MySQL》,本篇文章只是个人学习总结，欢迎大家买一本小册看看，对于mysql是由浅入深的讲解非常细致

14.基于规则优化

条件化简

除去括号

• ((a = 5 AND b = c) OR ((a > c) AND (c < 5)))括号太多太杂
• (a = 5 and b = c) OR (a > c AND c < 5)

常量传递

• a = 5 AND b > a
• a = 5 AND b > 5

等值传递

• a = b and b = c and c = 5
• a = 5 and b = 5 and c = 5

移除没用的条件

• (a < 1 and b = b) OR (a = 6 OR 5 != 5)这条语句上面5!=5和b=b很明显都是没用的

表达式计算

• a = 5 + 1这种要直接赋值

having和where子句合并

如果没有聚集函数和group那么having和where就应该合并起来

常量表检测

查询快的语句

• 表只有一条数据或者是0条（通过统计数据，抽样平均值）
• 使用主键或者是唯一二级索引来进行等值匹配
• 这两种方式的查询就是常量表

SELECT * FROM table1 INNER JOIN table2

ON table1.column1 = table2.column2

WHERE table1.primary_key = 1;

• 对于上面这条语句采用的方式就是把table1的相关的全部替换成常量值。意思就是主键其实就只有一个，那么不如直接把主键上面这条记录拿出来替换成常量。table1就变成了常量表，可以直接查询table2。

SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 ON table1表column1列的常量值 = table2.column2;

外连接消除

测试使用的表

CREATE TABLE t1 (
m1 int,
n1 char(1)
) Engine=InnoDB, CHARSET=utf8;
CREATE TABLE t2 (
m2 int,
n2 char(1)
) Engine=InnoDB, CHARSET=utf8;

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;

• 上面这个查询已经和内连接没什么区别了，原因就是他把那些原本驱动表有的但是被驱动表没有对应的映射但仍需加入结果集记录都删除了。

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

• 这条就是隐含不包含null的意思。

• 这种类型都可以直接使用内连接

子查询优化

按返回的结果集区分子查询

• 标量子查询：只返回一个值SELECT (SELECT m1 FROM t1 LIMIT 1);
• 行子查询：只返回一条记录的子查询SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
• 列子查询:返回一个列但是多条数据SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
• 表子查询：多条记录多个列SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

按外层查询关系区分子查询

• 不相关子查询：不依赖外层的查询结果，能够自己独立查询出结果
• 相关子查询：下面的这个语句的n1就是t1的列，也就是需要知道t1之后才能够去查询t2的列。

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

子查询在布尔表达式的使用

标量子查询：SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);

行子查询： SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

• 上面这两句就可以使用对应的等值查询。=、>、<、>=、<=、<>、!=、<=>这些标识符如果要使用，那么后面的子查询就需要只有一条记录或者是一个数据。

in、some、all子查询

SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);

• in或者是not in判断t1的这两个字段的值是否在子查询结果集中。如果存在那么就留用，其它过滤掉

SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);

• any、some。上面这个意思就是只要存在一个m2是小于m1的那么就是true否则就是false

SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

• all就是所有值都需要比m1小的时候才会返回true。

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

• exists：这个语句的意思就是t2到底有没有数据，如果存在那么就返回true。

子查询的规则

• 子查询使用小括号
• select 之后的一定要是一个标量子查询，多列和多行都不允许
• 如果要得到标量子查询或者是行子查询那么就可以使用limit1
• 对于in 、any、some、all子句来说子查询不可以有limit语句

多余的

• 子查询里面的order by、distinct、没有聚集函数的group by
• 不允许增删改的时候还有子查询

标量子查询、行子查询的执行方式

不相关的标量子查询

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = ‘a’ LIMIT 1);

• 其实就是简单的两个单表查询，先查询好子查询里面的结果，然后根据这个结果再去查询对应的外层查询。

相关的标量子查询

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

• 对于相关子查询的逻辑
  • 先从s1中获取一条记录
  • 然后找到这条记录s1.key3接着就是进行一次子查询
  • 然后把查询出来的结果集和当前s1.key1进行比较，如果是正确那么就加入到真正的结果集
  • 然后再次执行第一步。直到查询完所有的s1的记录

IN子查询优化

不相关的in子查询

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);

• 对于in来说，可能最后的结果集太大导致内存放不下

SELECT * FROM tbl_name WHERE column IN (a, b);

• in参数太多，导致每次都需要进行匹配,比如上面这个每个记录都要和a和b进行匹配，无法使用索引，因为in参数太多，可能会导致外层循环全表扫描。

所以不将子查询的结果集作为外层的参数，而是把结果集写入到临时表，过程

• 临时表的列就是要子查询结果集的列

• 写入临时表记录，并且去重（使用唯一二级索引）

• 使用基于内存的memory存储引擎，并且建立哈希索引（匹配速度非常快）

• 子查询结果集到临时表的过程称为物化，物化表内存有hash，磁盘有B+树

物化表转连接

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);

• 物化表的列是m_val,表被称为materialized_table

• 对于s1.key1如果存在物化表中那么这条记录就需要加入结果集

• 对于子查询物化表来说，如果能在s1找到key1和自己相同的值的记录，就可以加入到结果集

也就是说对于上面的查询来说就像是进行一次内连接

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

• 这个时候只需要通过优化器来决定连接顺序，找到最低成本的连接

1. 如果使用s1作为驱动表
   1. 物化子查询需要的成本
   2. 扫描s1所需要的成本
   3. s1的记录数量乘以通过m_val=xx在物化表单表访问成本
2. 如果使用materialized_table作为驱动表
   1. 物化子查询需要的成本
   2. 扫描物化表记录的成本
   3. 物化表查出的记录数 * s1.key1=xxx对于s1表的访问成本

就在上面选出最优方案，实际上就是内连接的两个表的成本选择。子查询建立临时表的好处就是可以使用内连接，内连接的成本主要就是看哪个表的扫描记录数更少，还有就是单表查询的一个成本问题，更重要的是原本如果in的参数太多导致外层无法使用索引，但是现在可以通过改变表的连接顺序，让另一表来完成。原本的那种方式就是先查询子查询，然后外层查询一条之后匹配子查询的结果集导致耗费很多时间，而且子查询的结果集可能非常大，导致内存装不完，需要进行IO操作的成本也很大。

将子查询转换为semi-join

意思其实就是不去建立临时表，直接来把子查询的表和外层表进行一个连接。（问题就是直接进行连接会导致没有进行去重，而最后多条记录加入到结果集）

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);

• 对于上面这条语句实际上就是和连接查询差不多。

• SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = ‘a’;

• 也就是在s2.key3='a’找到多条记录之后，把common_field与s1的key1进行对比，如果相同那么就加入结果集。另一种意思就是s1的key1和s2的common_field进行内连接，并且把s2.key3='a’的情况全部筛选出来。

• 对于s1.key1和s2.common_field来说，s1的key1可能对应着s2的多条记录，但是对于我们只需要查询s1的记录来说，s2的多条记录会导致多条s1的相同记录加入到结果集，所以这个时候需要把s1的记录只加入一条。

• SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field WHERE key3 = ‘a’;所以改写成这样的一条语句，那么怎么实现这种半连接？

Table pullout子查询表上拉

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = ‘a’);

• 如果key2是唯一二级索引或者是主键，那么就可以把s2上拉连接，因为这个时候s2的列都只有唯一的值，不可能重复所以直接进行内连接是可以的。

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = ‘a’;

重复值消除

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);

• 这个时候如果直接转换成内连接，问题就是s2可能会有多条匹配s1.key1的记录，导致s1的记录被重复添加

CREATE TABLE tmp ( id PRIMARY KEY );

• 解决办法创建一个临时表，如果s1的记录加入结果集，首先先把id加入到表，加入成功才能加入到结果集

松散索引扫描

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > ‘a’ AND key1 < ‘b’);

• 如果这个时候s2可以使用到索引，那么就可以从s2为驱动表开始扫描，它有多条相同的记录，但是实际上只需要取相同记录的一条去扫描s1找到相同的值，这样就能保证只有一条记录加入结果集，这种就是松散索引扫描

• Semi-joinMaterialization execution strategy对于in的物化表和外层表来说，物化表是没有重复值，也就是一种特殊的semi-join。这种就是提前把物化表的每个记录进行唯一化。（s1的角度就是key1是否存在物化表，物化表的角度就是每个值是否存在于s1的key1中。相当于就是只要存在那么记录就可以被添加到结果集）

• FirstMatch execution strategy首次匹配意思就是外层拿出一条记录，然后去到子查询进行匹配，只要有就可以加入到结果集，也是可以防止重复的记录产生。

相关的in子查询

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);

• 上面那条语句转变成半连接SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;
• 然后可以使用上面的三种策略进行处理重复值。
• 而且相关子查询并不是一个单表查询所以无法使用物化表

semi-join的适用条件

SELECT … FROM outer_tables WHERE expr IN (SELECT … FROM inner_tables …) AND …

• 上面这种格式才可以

SELECT … FROM outer_tables WHERE (oe1, oe2, …) IN (SELECT ie1, ie2, … FROM inner_tables …) AND …

• 或者是这种。

• 子查询语句一定是和in组成布尔表达式，而且在where和on出现

• 外层可以有其他查询条件，但是一定是and才可以

• 子查询是一个单一查询

• 子查询不可以有任何聚集函数

semi-join不适用条件

• SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’) OR key2 > 100;这种，外层通过or来进行连接

• SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = ‘a’)使用的是not in而不是in

• SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’) FROM s1 ;放在了select也是不可以的。

• SELECT * FROM s1 WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);子查询里面包含了聚集函数

• SELECT * FROM s1 WHERE key1 IN ( SELECT common_field FROM s2 WHERE key3 = ‘a’ UNION SELECT common_field FROM s2 WHERE key3 = ‘b’ );子查询包括了union的情况

mysql为了处理这种不适用的情况，创建了两种处理方式

1. 对于不相关子查询来说not in，只能就是扫描s1，然后看key1是否存在于临时表。也就是物化之后再进行查询，可以使用到创建的hash索引快速定位是否存在。但是不能转换为连接
2. in查询转换成exists查询（不满足semi-join和物化表成本太大），只需要这个子查询是放到where或者on后面就可以进行转换

outer_expr IN (SELECT inner_expr FROM … WHERE subquery_where)转换前

EXISTS (SELECT inner_expr FROM … WHERE subquery_where AND outer_expr=inner_expr)转换后

• 转换之所以可以成功是因为where后面无论是null还是false，下面就是两个转换的实例

1. SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) OR key2 > 1000;

2. SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 where s1.common_field = s2.common_field AND s2.key3 = s1.key1) OR key2 > 1000;

• 那么为什么一定要转换成exists，如果是相关子查询的in那么问题就是不能够使用索引。上面的common_field很明显就是不可以使用索引，但是key3可以。可是key3是被查询列，无法使用索引，所以可以通过转换成exists来进行替换最后让s2能够使用到key3的索引，避免全表扫描（最初的表）

总结

• in子查询转化成semi-join之后还会进行策略选择，找到成本最低的记录去重
  • Table pullout
  • DuplicateWeedout
  • LooseScan
  • Materialization
  • FirstMatch
• 如果不符合semi-join
  • 把子查询物化，再进行查询
  • in到exists的一个转换

ANY/ALL子查询优化

• 如果是不想关子查询，那么any可以转换为min聚集函数，all可以转换为max聚集函数

• < ANY(SELECT inner_expr …) < (SELECT MAX(inner_expr) …)比如这样。any大于对面，只需要最大的那个大于那么就可以了。其它也是如此类推

[NOT] EXISTS子查询的执行

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = ‘a’) OR key2 > 100;

• 对于不相关子查询，那么就先执行子查询。转换成true之后继续执行。

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

• 对于相关子查询来说只能是查一条外层，然后子查询一次来进行排匹配。

对于派生表的优化

SELECT * FROM ( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = ‘a’ ) AS derived_s1 WHERE d_key3 = ‘a’;

• 先把派生表进行物化，但是这种物化是延迟的。优先处理后面的表的一个条件过滤，如果后面的表是空的那么就不会进行物化

SELECT * FROM (SELECT * FROM s1 WHERE key1 = ‘a’) AS derived_s1;

• 上面这个就相当于是 select * from s1 where key1=‘a’ AS derived_s1也就是外层和内层的进行合并

SELECT * FROM ( SELECT * FROM s1 WHERE key1 = ‘a’ ) AS derived_s1 INNER JOIN s2 ON derived_s1.key1 = s2.key1 WHERE s2.key2 = 1;

SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.key1 = ‘a’ AND s2.key2 = 1;

• 上面这个也是合并的其中一种。就是把s1抽出来，然后条件放到后面进行一个合并

15.Explain详解(上)

一条执行语句经过mysql查询优化器之后就会基于成本和规则优化后生成执行计划，如果要查看可以使用explain

各个列的详解

table

• 使用的表

id

• id相同出现在前边的就是驱动表

• 对于in语句来说可能出现的情况就是把语句进行了一次重写，下面很明显就是转换成了连接操作。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HdJhZw3d-1636121209572)(…/…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20211105152509479.png)]

• 对于union查询来说也是有点不一样，下面id有个是null的原因就是这个是一个临时表，用于去重使用的。他把第一和第二个表重新组合到第三个表，并且做了去重处理。如果是union all那么就不会有第三个临时表。

select_type

• Simple：不包含union和子查询都是simple级别的，连接查询也是simple级别的。
• primary：对于包含union、union All那么最左边那个表就是primary
• union：包含union和union all除了最左边的表其它都是union级别的。
• union result：union的临时表去重
• subquery：包含子查询不能转换为semi-join而且是不相关子查询，只可以使用子查询物化，因为是物化，所以只需要执行一次子查询
• dependent subquery：不可以转换为semi-join而且是相关子查询,由于不能执行物化，所以这种子查询需要执行多次，因为每次都需要等待s1查询出一条记录之后在s2进行一次扫描，记录越多成本就越大。

• dependent union：在包含union和union all的大查询，小查询如果依赖外层那么都是dependent union或者是dependent subquery

• derived：from后面的子查询的一个派生表

• materialized：物化+连接，物化id为2的表，然后物化表和s1进行一个连接。

partitions

通常都是null

type

mysql对某个表的访问方法

• system：只有一条记录，而且表使用的存储引擎的数据是准确的。比如MyISAM或者是Memory
• const：主键或者是唯一二级索引的等值匹配
• eq_ref:连接查询的时候是使用唯一二级索引或者是主键，s2是被驱动表，使用到了这个唯一二级索引或者是主键，但是会有多次连接。对于s1驱动表来说就是一个全表扫描

• ref：普通二级索引
• fulltext：全文索引
• ref_or_null：普通二级索引列的值可以为null
• index_merge：也就是所谓的索引合并。intersection（交集）或者是union并集。实际上就是先把两个索引的主键id查出来然后再求交集或者是并且最后再进行一次回表。不然就需要查一次就回表一次。多出很多次重复的查询回表。

• unique_subquery：in转换成exists而且还能够使用主键进行等值匹配。

• index_subquery：in转换成exists并且只能使用普通索引
• range：通过索引获取范围区间的记录
• index：索引覆盖
• all：全表扫描

possible_keys和key

• possible_keys：可能会使用到的索引
• key：真正决策可以使用的索引

key_len

• 固定长度索引varchar(100)，如果使用utf-8，那么占用空间是100 * 3个字节

• 如果可以存储null，那么就会比不存储null多一个字节

• 变长字段都会有两个字节空间控制列的长度

• 如果查询的索引是id，那么不是null那么就是4个字节，如果是null那么需要5个字节。对于变长类型也是这么进行的计算

• 那么为什么都是2个字节空间？原因就是因为这里执行计划只是为了让我们看出到底使用了多少个索引，而不是具体的存储引擎的使用的存储空间。

ref

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;

• 如果访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery那么ref的值就是const，因为这个地方的key1是一个等值匹配

• 对于下面这个来说那么就是以s1.id来作为匹配

• 如果是一个函数那么就是func

rows

• 代表需要扫描的行数

filtered

• 全表扫描的单表查询符合条件的记录数

• 对于连接查询来说这个filtered就非常重要，下面说明s1只有10%是可以符合条件，也就是最后只需要扫描968行记录，也就是s1的扇出值。s1作为驱动表，s2作为非驱动表。

IN SELECT * FROM s1 WHERE key1 = ‘a’;

• 如果访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery那么ref的值就是const，因为这个地方的key1是一个等值匹配

• 对于下面这个来说那么就是以s1.id来作为匹配

[外链图片转存中…(img-DokjWy0n-1636121209582)]

• 如果是一个函数那么就是func

[外链图片转存中…(img-6cfHFlab-1636121209583)]

rows

• 代表需要扫描的行数

filtered

• 全表扫描的单表查询符合条件的记录数

• 对于连接查询来说这个filtered就非常重要，下面说明s1只有10%是可以符合条件，也就是最后只需要扫描968行记录，也就是s1的扇出值。s1作为驱动表，s2作为非驱动表。

[外链图片转存中…(img-7M2ReLjR-1636121209584)]