10-Mysql内核查询成本计算实战-04

两表连接的成本分析

连接查询的成本计算公式是这样的：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。然后分别为驱动表和被驱动表选择成本最低的访问方法。很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询。
我们来看看内连接，比如对于下边这个查询来说：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no=
s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND
s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22
18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59';
1
2
3
4

可以选择的连接顺序有两种：
s1连接s2，也就是s1作为驱动表，s2作为被驱动表。
s2连接s1，也就是s2作为驱动表，s1作为被驱动表。
查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们定性的分析一下，不像分析单表查询那样定量的分析了：

使用s1作为驱动表的情况

分析对于驱动表的成本最低的执行方案，首先看一下涉及s1表单表的搜索条件有哪些：

s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09'
1

所以这个查询可能使用到idx_expire_time索引，从全表扫描和使用idx_expire_time这两个方案中选出成本最低的那个，很显然使用idx_expire_time执行查询的成本更低些。然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表s2的搜索条件就是：
1、s2.order_note = 常数（这是因为对驱动表s1结果集中的每一条记录，都需要进行一次被驱动表s2的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表s2了。）
2、s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’

很显然，第一个条件由于order_note没有用到索引，所以并没有什么用，此时访问s2表时可用的方案也是全表扫描和使用idx_expire_time两种，假设使用idx_expire_time的成本更小。
所以此时使用s1作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：
使用idx_expire_time访问s1的成本 + s1的扇出 × 使用idx_expire_time访问s2的成本

使用s2作为驱动表的情况

分析对于驱动表的成本最低的执行方案首先看一下涉及s2表单表的搜索条件有哪些：
s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’
所以这个查询可能使用到idx_expire_time索引，从全表扫描和使用idx_expire_time这两个方案中选出成本最低的那个，假设使用idx_expire_time执行查询的成本更低些。然后分析对于被驱动表的成本最低的执行方案
此时涉及被驱动表s1的搜索条件就是：
1、s1.order_no = 常数
2、s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22你18:35:09’
这时就很有趣了，使用idx_order_no可以进行ref方式的访问，使用idx_expire_time可以使用range方式的访问。那么优化器需要从全表扫描、使用idx_order_no、使用idx_expire_time这几个方案里选出一个成本最低的方案。这里有个问题，因为idx_expire_time的范围区间是确定的，怎么计算使用idx_expire_time的成本我们上边已经说过了，可是在没有真正执行查询前，s1.order_no = 常数中的常数值我们是不知道的，怎么衡量使用idx_order_no执行查询的成本呢？其实很简单，直接使用我们前面说过的索引统计数据就好了（就是索引列平均一个值重复多少次）。一般情况下，ref的访问方式要比range成本更低，这里假设使用idx_order_no进行对s1的访问。
所以此时使用s2作为驱动表时的总成本就是：
使用idx_expire_time访问s2的成本 + s2的扇出 × 使用idx_order_no访问s1的成本最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，一般来讲，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：
尽量减少驱动表的扇出
对被驱动表的访问成本尽量低
这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。