《Mysql是怎样运行的》读书笔记三

《Mysql是怎样运行的》读书笔记三

一、两个表的连接-连接的原理

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

1.1连接的过程

​ 任意连接数张表，如果没有任何限制条件，这些表连接起来产生的 笛卡尔积 可能是非常巨大的。所以在连接的时候过滤特定记录组合是有必要的，在连接查询中过滤条件可以分为：

• 单表过滤条件
• 涉及多表的条件

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

这个连接查询的大致执行过程:

1. 确定第一个需要查询的表，称之为 驱动表（选取代价最小的那种访问方法去执行单表查询语句）
2. 针对上一步的驱动表t1产生的结果集中的每一条记录，分别需要对t2表（被驱动查找匹配记录，所谓匹配记录，指符合被驱动表t2过滤条件的记录。

1.2 内连接和外连接

​ 驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。按这个思想分为驱动表和被驱动表

对于外连接而言，并不想把驱动表的全部记录都加入最后的结果集。有时候匹配失败要加入结果集，有时候又不要加入结果集。怎么办？把过滤条件分为两种 where 和 on

• where子句的过滤条件

不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。

• on子句的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

​ 内连接的where和on等价，所以内连接不要求强制写明ON子句。内连接的驱动表和被驱动表可以互换，不影响最后的查询结果。外连接的驱动表和被驱动表不可以轻易互换

1.3 连接的原理

1.3.1嵌套循环连接

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

对于这条含有两个表的内连接的SQL 查询的步骤：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

如果有3个表进行连接，那么步骤2 中得到的结果集 作为新的驱动表，第三个表顺势成为了被驱动表

​ 何为嵌套循环连接：驱动表之访问一次，但被驱动表可能被访问多次，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式，也是最笨的一种连接查询算法

1.3.2 使用索引加快连接速度

​ 在嵌套循环连接的步骤2 中可能需要 多次访问被驱动表，如果访问被驱动表的方式都是全表扫描，那需要好多次。

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

解决方案：给被驱动表 搜索条件的列加 索引

• 在m2列上建立索引，此时用到ref的访问方法，回表后只需要判断t2.n2 < ‘d’
• 给n2列建立索引，则使用到range访问方法

1.3.3 基于块的嵌套循环连接

​ 采用前两种方法，被驱动表仍然被访问很多次，如果表中的数据很庞大且不适用索引，需要从磁盘读多次被驱动表，这个I/O代价非常大，所以我们得找到一个 尽量减少访问被驱动表次数的 方法

Join Buffer：

​ 把被驱动表的记录加载到内存的时候，一次性和多条 驱动表中的记录进行匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价，这个时候Join Buffer油然而生，join buffer 就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中

注意：，驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中

二、Mysql基于成本的优化

​ mysql的成本主要有下面两个方面组成

• I/O成本

我们表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上，我们想查询表中的记录时，需要先把数据或索引加载到内存中然后再操作。这个磁盘到内存加载的过程损耗时间为I/O成本

• CPU成本

读取以及检测记录是否满足对应的搜索条件、多结果集进行排序等这些操作损耗的时间称之为CPU成本

2.1基于成本的优化步骤

前边谈及到 二级索引和回表其实谈到过， 这事是查询优化器该干的是，这里我们再次复述一次

• 根据搜索条件，找出所有可能使用的索引

• 计算全表扫描的代价

• 计算使用不同索引执行查询的代价

• 对比各种执行方案的代价，找出成本最低的那一个

主要分为：不同索引执行查询的代价、连接查询的成本

对于连接查询，由于外，连接查询的驱动表和被驱动表已被固定，这里只讨论内连接查询，查询优化器需要分别考虑 选择哪个表为驱动表得到的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。

三、Mysql基于规则的优化（含子查询优化）

​ 我们日常无法避免写一些很蠢很耗费性能的语句，Mysql制定了一些规则，竭尽全力把这个很蠢和糟糕的语句转换成某种可以较高执行的形式，这个过程称之为 查询重写，下面我们讨论一下比较重要的重写规则

3.1条件化简

• 移除不必要的括号

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
#查询优化器会将这些括号干掉
(a