MySQL(三)：MySQL的执行原理
1、单表访问之索引合并 - index merge
MySQL中使用多个索引来完成一次查询的执行方法称之为 索引合并(index merge)。索引合并算法有 Intersection合并、Union合并、Sort-Union合并。
1.1、Intersection合并
Intersection合并，某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。
SELECT * FROM order_info WHERE order_no = 'a' AND expire_time = 'b';
若这个查询使用 Intersection合并的方式执行，过程如下：先从 idx_order_no 二级索引对应的B+树中取出order_no='a'的相关记录；再从 idx_expire_time 二级索引对应的B+树中取出expire_time='b'的相关记录。
二级索引的记录都是由索引列 + 主键构成的，获得这两个结果集中id值的交集。按照生成的id值列表进行回表操作，即从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。
1.1.1、执行成本
思考：为什么不直接使用 idx_order_no或者idx_expire_time 只根据某个搜索条件去读取一个二级索引，回表后再过滤另外一个搜索条件呢？因为两种查询执行方式之间的成本代价不同。
·只读取一个二级索引的成本：按照某个搜索条件读取一个二级索引 -> 根据从该二级索引得到的主键值进行回表操作 -> 然后再过滤其他的搜索条件。
·读取多个二级索引之后取交集成本：按照不同的搜索条件分别读取不同的二级索引 -> 将从多个二级索引得到的主键值取交集 -> 最后根据主键值进行回表操作。
读取多个二级索引比读取一个二级索引消耗性能，但大部分读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以，若只读取一个二级索引时需要回表的记录数很多，而 读取二级索引后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。
1.1.2、Intersection索引合并的必要条件
MySQL在某些特定的情况下才可能会使用到Intersection索引合并，详情如下：
2.1、等值匹配
二级索引列必须是等值匹配的情况
对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
2.2、主键列可以是范围匹配
主键的索引是有序的，按照有序的主键值去回表取记录称为：Rowid Ordered Retrieval，简称ROR。而二级索引的用户记录是由索引列 + 主键构成的，所以根据范围匹配出来 的主键就是乱序的，导致回表开销很大。
在二级索引列都是等值匹配的情况下可使用Intersection索引合并，因为只有在这种场景下根据二级索引查询出的结果集是按照主键值排序的。
Intersection索引合并会把从多个二级索引中查询出的主键值求交集，若从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，求交集的过程比较容易。
上边说的两种情况只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使符合Intersection的条件，也不一定发生Intersection索引合并，是否进行索引合并，需要看优化器的判断。
优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。
1.2、Union合并
查询时，将符合某个搜索条件的记录取出来，也将符合另外的某个搜索条件的记录取出来，不同的搜索条件之间是OR关系，对于OR关系的不同搜索条件会使用到不同的索引。
SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'
Intersection是交集的意思，适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。
与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并。
1.2.1、Union合并必要条件
等值匹配、主键列可以是范围匹配。
1.2.2、使用Intersection索引合并的搜索条件
搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，如下面的查询：
SELECT * FROM order_info WHERE create_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');
优化器可能采用这样的方式来执行这个查询：
1、先按照搜索条件order_no = 'a' AND expire_time = 'b'从索引idx_order_no和idx_expire_time中使用Intersection索引合并的方式得到一个主键集合。
2、再按照搜索条件 create_time ='a' AND order_status = 'b' AND expire_time = 'c'从联合索引 u_idx_time_status 中得到另一个主键集合。
3、采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。
查询条件符合上述情况不一定会采用Union索引合并，需要优化器进行判断。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小的时候才会使用Union索引合并。
1.3、Sort-Union合并
Union索引合并必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，如下面的查询就无法使用到Union索引合并：
SELECT * FROM order_info WHERE order_no < 'a' OR expire_time > 'z'
因为根据order_no < 'a'从idx_order_no索引中获取的二级索引记录的主键值不是排好序的，同时根据 expire_time> 'z'从idx_expire_time 索引中获取的二级索引记录的主键值也不是排好序的，但是order_no < 'a'和 expire_time> 'z''这两个条件又是必须要使用的，所以可以这样执行：
1、先根据order_no < 'a' 条件从idx_order_no二级索引中获取记录，并按照记录的主键值进行排序;
2、再根据expire_time > 'z'条件从idx_expire_time二级索引中获取记录，并按照记录的主键值进行排序;
3、因为上述的两个二级索引主键值都是排好序的，剩下的操作与Union索引合并方式一样。
先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。
查询条件符合上述情况也不一定会采用Sort-Union索引合并，得看优化器的判断。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Sort-Union索引合并后进行访问的代价比全表扫描更小时才会使用Sort-Union索引合并。
2、连接查询
2.1、连接的本质
把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。将e1和e2两个表连接起来的过程如下图所示：
这个过程是把e1表的记录和e2的记录连起来组成新的更大的记录，这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，这样的结果集称之为 笛卡尔积 。
表e1中有3条记录，表e2中也有3条记录，两个表连接之后的笛卡尔积就有3×3=9行记录。
在MySQL中，连接查询的语法只要在FROM语句后边跟多个表名就好了，如把e1表和e2表连接起来的查询语句可以写成这样：
SELECT * FROM e1, e2;
2.2、连接过程
SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';
涉及单表的条件：e1.m1 > 1 只针对e1表；e2.n2 < 'd' 只针对e2表。
涉及两表的条件：e1.m1 = e2.m2 涉及两个表。
连接查询大致执行过程如下：
2.2.1、确定驱动表(t1)
先确定第一个需要查询的表，该表称之为驱动表。单表中执行查询语句只需要选取代价最小的那种访问方法去执行单表查询语句，即从执行计划中找const、ref、ref_or_null、range、index、all等这些执行方法中选取代价最小的去执行查询。
此处假设使用e1作为驱动表，需要到e1表中找满足e1.m1 > 1的记录，因表中的数据太少，未在表上建立二级索引，此处查询e1表的访问方法设定为all，即采用全表扫描的方式执行单表查询。
2.2.2、遍历驱动表结果，到被驱动表(t2)中查找匹配记录
针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到e2表中查询匹配的记录，指的是符合过滤条件的记录。
因为是根据e1表中的记录去找e2表中的记录，e2表也被称之为 被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次e2表。
整个连接查询的执行过程如下图所示：
整个连接查询最后的结果只有两条符合过滤条件的记录：
从上边两个步骤可以看出来，这个两表连接查询共需要查询1次e1表，2次e2表。
在特定的过滤条件下的结果，若把e1.m1 > 1这个条件去掉，那么从e1表中查出的记录就有3条，就需要查询3次e2表了。即在两表连接查询中， 驱动表只需要访问一次，被驱动表可能被访问多次。
2.3、内连接和外连接
|
内连接
|
驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
|
|
外连接
|
驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集
|
根据选取驱动表的不同，外连接可以细分为2种：
|
左外连接
|
选取左侧的表为驱动表
|
|
右外连接
|
选取右侧的表为驱动表
|
2.3.1、过滤条件
WHERE子句中的过滤条件：
不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。
ON子句中的过滤条件：
外连接的驱动表的记录，若无法在被驱动表中找到匹配ON子句中的过滤条件的记录，该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，若把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，即：内连接中的WHERE子句和ON子句是等价的。
一般情况下，把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，把放到ON子句中的过滤条件也称之为连接条件。
2.3.2、左(外)连接
左连接语法：
SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];
中括号里的OUTER单词是可以省略的。
对于LEFT JOIN类型的连接来说，把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。
e1为外表或者驱动表，e2是内标或者被驱动表。对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。
2.3.3、右(外)连接
右（外）连接和左（外）连接的原理相同，只是LEFT换成RIGHT，语法如下：
SELECT * FROM e1 RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];
驱动表是右边的表e2，被驱动表是左边的表e1。
2.3.4、内连接
内连接和外连接的根本区别：在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集。
SELECT * FROM e1 [INNER | CROSS] JOIN e2 [ON 连接条件] [WHERE 普通过滤条件];
即在MySQL中，如下几种内连接的写法都是等价的：
SELECT * FROM e1 JOIN e2; SELECT * FROM e1 INNER JOIN e2; SELECT * FROM e1 CROSS JOIN e2;
内连接，上述写法和直接把需要连接的表名放到FROM语句之后，用逗号,分隔开的写法是等价的：
SELECT * FROM e1, e2;
连接的本质是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。
对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系非常重要，即左外连接和右外连接的驱动表和被驱动表不能轻易互换。
3、MySQL执行连接
3.1、嵌套循环连接（Nested-LoopJoin）
对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。
对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，即左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。
若有3个表进行连接，首先两表连接得到的结果集为新的驱动表，第三个表为被驱动表，伪代码的过程如下：
# 遍历满足对e1单表查询结果集中的每一条记录，N条 for each row in e1 { # 对于某条e1表的记录来说，遍历满足对e2单表查询结果集中的每一条记录，M条 for each row in e2 { # 对于某条e1和e2表的记录组合，对t3表进行单表查询，L条 for each row in t3 { ... } } }
该过程就像嵌套的循环，驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为 嵌套循环连接（ Nested-Loop Join ），时间复杂度是O（N * M * L）。
3.2.使用索引加快连接速度
利用索引来加快查询速度。e1表和e2表进行内连接查询如下：
SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';
嵌套循环连接算法执行的其实是连接查询，查询驱动表e1后的结果集中有两条记录，嵌套循环连接算法需要对被驱动表查询2次，当e1.m1 = 2时，去查询一遍e2表，对e2表的查询语句相当于：
SELECT * FROM e2 WHERE e2.m2 = 2 AND e2.n2 < 'd';
当e1.m1 = 3时，再去查询一遍e2表，此时对e2表的查询语句相当于：
SELECT * FROM e2 WHERE e2.m2 = 3 AND e2.n2 < 'd';
原来的e1.m1 = e2.m2这个涉及两个表的过滤条件在针对e2表做查询时关于e1表的条件已经确定，只需要单单优化对e2表的查询，上述两个对e2表的查询语句中利用到的列是m2和n2列，可以在e2表的m2列上建立索引。
3.3.基于块的嵌套循环连接（Block Nested-Loop Join）
扫描一个表的过程实际上是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。
内存里可能并不能完全存放大表中所有的记录，在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，需要把前边的记录从内存中释放掉。
采用嵌套循环连接算法的两表连接过程中，被驱动表要被访问好多次，若这个被驱动表中的数据特别多且不能使用索引进行访问，即相当于从磁盘上读好几次这个表，I/O代价非常大，需要想办法尽量减少访问被驱动表的次数。
当被驱动表中的数据很多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后会被从内存中清除掉。再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，驱动表结果集中有多少条记录，就把被驱动表从磁盘上加载到内存中多少次。
MySQL提出了 join buffer 的概念，可以一次性和多条驱动表中的记录做匹配，大大减少了重复从磁盘上加载被驱动表的代价。join buffer是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在此 join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，匹配的过程都是在内存中完成的，这样可以显著减少被驱动表的I/O代价。使用join buffer 过程如下图所示：
当join buffer能容纳驱动表结果集中的所有记录，这种加入了 join buffer 的嵌套循环连接算法称之为 基于块的嵌套连接（ Block Nested-Loop Join ）算法。join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为 262144字节（也就是256KB），最小可设置为128字节。
show variables like 'join_buffer_size' ;
对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，若不能使用索引，并且自己的机器的内存可以尝试调大 join_buffer_size的值来对连接查询进行优化。
驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，为了让join buffer中放置更多的记录，只需要把需要的列放到查询列表中，最好不要把 * 作为查询列表。
4、MySQL查询成本
4.1、查询成本
MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者代价最低的方案去真正的执行查询。MySQL中一条查询语句的执行成本由 I/O成本 和 CPU成本 组成。
I/O成本：MyISAM、InnoDB存储引擎都是将数据和索引存储到磁盘上，当想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。从 磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对InnoDB存储引擎而言，页是磁盘和内存之间交互的基本单位。
MySQL规定读取一个页面花费的成本默认是1.0（I/O成本），读取以及检测一条记录是否符合搜索条件的成本默认是0.2（CPU成本）。读取记录时需不需要检测是否满足搜索条件，哪怕是空数据，成本都算是0.2。
4.2、单表查询的成本
在一条单表查询语句真正执行前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，过程如下：
4.2.1、根据搜索条件，找出所有可能使用的索引
B+树索引，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=或者LIKE操作符连接起来，就能产生一个范围区间，MySQL把一个查询中可能使用到的索引称之为possible keys。
4.2.2、计算全表扫描的代价
对InnoDB存储引擎而言，全表扫描就是把聚簇索引（主键索引）中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。查询成本=I/O成本+CPU成本，计算全表扫描的代价需要两个信息：
2.1、聚簇索引占用的页面数
2.2、该表中的记录数
MySQL为每个表维护了一系列的统计信息，通过SHOW TABLE STATUS语句来查看表的统计信息，若要看指定的某个表的统计信息，就在该语句后加对应的LIKE语句，如要查看 order_info 这个表的统计信息，详情如下：
SHOW TABLE STATUS LIKE 'order_info'
查询后会出现很多统计选项，只需要关注两个选项：
·Rows
该选项表示表中的记录条数，对于使用MyISAM存储引擎的表而言，该值是准确的；对于使用InnoDB存储引擎的表而言，该值是一个估计值。
·Data_length
该选项表示表占用的存储空间字节数，对于使用MyISAM存储引擎的表而言，该值是数据文件的大小；对于使用InnoDB存储引擎的表而言，该值相当于聚簇索引占用的存储空间大小，该值大小：
Data_length = 聚簇索引的页面数量 × 每个页面的大小
4.2.3、计算使用不同索引执行查询的代价
MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本
MySQL计算查询成本依赖两个方面的数据：范围区间数量、需要回表的记录数。
范围区间数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
需要回表的记录数：优化器需要计算二级索引的某个范围区间到底包含多少条记录。
4.2.4、对比各种执行方案的代价，找出成本最低的一个
4.3、连接查询的成本
MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，对于两表连接查询来说，它的查询成本由 单次查询驱动表的成本 + 多次查询被驱动表的成本(具体查询多少次取决于对驱动表查询的结果集中有多少条记录) 两部分构成。
对驱动表进行查询后得到的记录条数称之为驱动表的 扇出（英文名：fanout）。驱动表的扇出值越小，对被驱动表的查询次数越少，连接查询的总成本也越低。当查询优化器想计算整个连接查询所使用的成本时，需要计算出驱动表的扇出值。
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 × 单次访问被驱动表的成本。
对于左（外）连接和右（外）连接查询来说，它们的驱动表示固定的，想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。
对于内连接而言，驱动表和被驱动表的位置是可以互换的，要考虑两个方面的问题：不同的表作为驱动表最终的查询成本可能是不同的，需要考虑最优的表连接顺序，分别为驱动表和被驱动表选择成本最低的访问方法。
5、MySQL的查询重写规则
5.1、条件化简
5.1.1、移除不必要的括号
((a = 5 AND b =c) OR ((a > c) AND (c < 5)))
优化器将用不到的括号去除：
(a = 5 and b =c) OR (a > c AND c < 5)
5.1.2、常量传递(constant_propagation)
当表达式和其他涉及列a的表达式使用AND连接起来时，可将其他表达式中的a的值替换为5，如：
a = 5 AND b >a
可被转换为：
a = 5 AND b >5
等值传递(equality_propagation)，当多个列之间存在等值匹配的关系，如：
a = b and b = c and c = 5
表达式可以被简化为：
a = 5 and b = 5 and c = 5
5.1.3、移除没用的条件(trivial_condition_removal)
对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，如：
(a < 1 and b= b) OR (a = 6 OR 5 != 5)
简化后表达式如下：
(a < 1 and TRUE) OR (a = 6 OR FALSE)
继续被简化为：
a < 1 OR a =6
5.2、外连接消除
内连接的驱动表和被驱动表的位置可以相互转换，而左(外)连接和右(外)连接的驱动表和被驱动表是固定的，会导致内连接可能通过优化表的连接顺序来降低整体的查询成本，但外连接却无法优化表的连接顺序。
外连接和内连接的本质区别：对于外连接的驱动表的记录而言，若无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录，若无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。
查询效果如下：
SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;
上述例子中的（左）外连接来说，由于驱动表e1中m1=1, n1='a'的记录无法在被驱动表e2中找到符合ON子句条件e1.m1 = e2.m2的记录，所以就直接把这条记录加入到结果集，对应的e2表的m2和n2列的值都设置为NULL。
