mysql中连接查询的成本

大家好。上篇文章我们讲了mysql中成本的含义以及单表查询如何计算成本。现在我们接着讲讲mysql中连接查询的成本。

在讲之前，我们先创建两张一样的表single_table和single_table2，并在表中插入10000条数据。在下面的讲解中，我们称single_table为s1，single_table2为s2。

CREATE TABLE single_table ( 
   id INT NOT NULL AUTO_INCREMENT, 
   key1 VARCHAR(100), 
   key2 INT, 
   key3 VARCHAR(100), 
   key_part1 VARCHAR(100), 
   key_part2 VARCHAR(100), 
   key_part3 VARCHAR(100), 
   common_field VARCHAR(100), 
   PRIMARY KEY (id), 
   KEY idx_key1 (key1), 
   UNIQUE KEY idx_key2 (key2), 
   KEY idx_key3 (key3), 
   KEY idx_key_part(key_part1, key_part2, key_part3) 
);
CREATE TABLE single_table2 ( 
   id INT NOT NULL AUTO_INCREMENT, 
   key1 VARCHAR(100), 
   key2 INT, 
   key3 VARCHAR(100), 
   key_part1 VARCHAR(100), 
   key_part2 VARCHAR(100), 
   key_part3 VARCHAR(100), 
   common_field VARCHAR(100), 
   PRIMARY KEY (id), 
   KEY idx_key1 (key1), 
   UNIQUE KEY idx_key2 (key2), 
   KEY idx_key3 (key3), 
   KEY idx_key_part(key_part1, key_part2, key_part3) 
);

一、条件过滤（Condition filtering）

我们知道，MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：单次查询驱动表的成本和多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）。

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，下面我们看一下各种情况下的sql驱动表的扇出值如何计算：

查询1：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;

假设使用s1 表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也就是驱动表中所有的记录数。我们再通过SHOW TABLE STATUS语句来查看s1表的统计信息:

统计数据中s1表的记录行数是 10033，所以s1表的扇出值就是10033。

查询2：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 WHERE s1.key2 >10 AND s1.key2 < 1000;

假设s1表是驱动表的话，对驱动表的单表查询可以使用idx_key2索引执行查询。此时 idx_key2 的范围区间 (10, 1000)中有多少条记录，那么扇出值就是多少。假若idx_key2 的范围区间(10, 1000) 的记录数是990条，也就是说本查询中优化器会把990当作驱动表s1的扇出值。

下面我们再来看一下复杂一点的查询：

查询三：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2  WHERE s1.common_field > 'xyz';

本条sql查询驱动表s1时有一个common_field > ‘xyz’ 的搜索条件。查询优化器不会真正的去执行查询，所以它只能猜这10033记录里有多少条记录满足common_field > ‘xyz’ 条件。

查询四：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 WHERE s1.key2 > 10 AND s1.key2 < 1000 AND s1.common_field > 'xyz';

本查询和查询二类似，只是驱动表s1多了一个common_field > ‘xyz’ 的搜索条件。不过因为本查询可以使用idx_key2 索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合 common_field > ‘xyz’ 条件，也就是需要猜在990条记录中有多少符合 common_field > ‘xyz’ 条件。

查询五：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 WHERE s1.key2 > 10 AND s1.key2 < 1000 AND s1.key1 IN ('a', 'b', 'c') AND s1.common_field > 'xyz';

本查询和查询二类似，不过在驱动表s1选取idx_key2 索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：key1 IN (‘a’, ‘b’, ‘c’) 和 common_field > ‘xyz’ 也就是优化器需要猜在990条记录中有多少符合上述两个条件的。

我们总结一下：在以下这两种情况下计算驱动表扇出值时需要靠猜：

1. 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
2. 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
   这个猜的过程我们称之为condition filtering 。这个过程可能会使用到索引，也可能使用到统计数据，也可能通过启发式规则（heuristic）进行瞎猜，大家对启发式规则有兴趣的话可以自行了解一下。

二、两表连接的成本分析

连接查询的成本计算公式是这样的：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本。

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

1. 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。

2. 然后分别为驱动表和被驱动表选择成本最低的访问方法。

下边我们就以内连接为例来看看如何计算出最优的连接查询方案。

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2  ON s1.key1 = s2.common_field WHERE s1.key2 > 10 AND s1.key2 < 1000 AND s2.key2 > 1000 AND s2.key2 < 2000;

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

使用s1作为驱动表的情况： 我们看到涉及s1表单表的搜索条件是：s1.key2 > 10 AND s1.key2 < 1000。所以这个查询可能使用到idx_key2 索引，从全表扫描和使用 idx_key2 这两个方案中选出成本最低的那个，很显然使用idx_key2 执行查询的成本更低些。

然后分析对于被驱动表的成本最低的执行方案。此时涉及被驱动表idx_key2 的搜索条件就是：

s2.common_field = 常数 和 s2.key2 > 1000 AND s2.key2 < 2000。很显然，第一个条件由于common_field 没有用到索引，所以并没有用，此时访问 single_table2 表时可用的方案也是全表扫描和使用 idx_key2 两种，很显然使用 idx_key2 的成本更小。

所以此时使用s1作为驱动表时的总成本就是：使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本。

使用s2作为驱动表的情况： 我们看到涉及s2表单表的搜索条件是：s2.key2 > 10 AND s2.key2 < 1000。所以这个查询可能使用到idx_key2 索引，从全表扫描和使用 idx_key2 这两个方案中选出成本最低的那个，很显然使用idx_key2 执行查询的成本更低些。

然后分析对于被驱动表的成本最低的执行方案。此时涉及被驱动表idx_key2 的搜索条件就是：s1.key1 = 常数 和 s1.key2 > 1000 AND s1.key2 < 2000。这时使用idx_key1 可以进行 ref 方式的访问，使用 idx_key2可以使用 range 方式 的访问。这时优化器需要从全表扫描、使用idx_key1、使用 idx_key2 这几个方案里选出一个成本最低的方案。一般情况下，ref的访问方式要比range成本最低，这里假设使用idx_key1进行对 s2 的访问。

所以此时使用single_table作为驱动表时的总成本就是：使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本。

最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程可以看出，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化 重点其实是这两个部分：尽量减少驱动表的扇出和对被驱动表的访问成本尽量低。

这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

三、多表连接的成本分析

首先我们要考虑一下多表连接时可能产生出多少种连接顺序：

对于两表连接，比如表A和表B连接。只有 AB、BA这两种连接顺序。其实相当于2 × 1 = 2 种连接顺序。

对于三表连接，比如表A、表B、表C进行连接 有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于 3 × 2 × 1 = 6 种连接顺序。

对于四表连接的话，则会有4 × 3 × 2 × 1 = 24 种连接顺序。

对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序，就是n的阶乘种连接顺序， 也就是n! 。

有n个表进行连接，MySQL 查询优化器要每一种连接顺序的成本都计算一遍吗？答案肯定是否定的。MySQL通过很多办法减少计算非常多种连接顺序的成本的方法：

1. 提前结束某种顺序的成本评估

MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就不对该连接顺序继续往下分析了。比方说A、B、C三个表进行连接，已经得到连接顺序ABC是当前的最小连接成本，比方说10.0 ，在计算连接顺序BCA 时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA 这个连接顺序的成本了。

2. 系统变量optimizer_search_depth

为了防止无穷无尽的分析各种连接顺序的成本，MySQL设计了一个optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth 值相同数量的表进行穷举分析。该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长。

3. 根据某些规则压根儿就不考虑某些连接顺序

即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以就有了所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。MySQL提供了一个系统变量optimizer_prune_level 来控制到底是不是用这些启发式规则。

四、 调节成本常数

MySQL中有很多调节成本的常数，它们被存储到了mysql数据库的engine_cost和server_cost表中。
一条语句的执行其实是分为两层的：server层和存储引擎层。在server 层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server 层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost 表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了 engine_cost 表中。

1、server_cost 表

server_cost表中存有以下几种成本常数 ：

成本常数名称	默认值	描述
disk_temptable_create_cost	40.0	创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost	1.0	向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost	0.1	两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完成排序而不是filesort 。
memory_temptable_create_cost	2.0	创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内 存的临时表。
memory_temptable_row_cost	0.2	向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽 量少的创建基于内存的临时表。
row_evaluate_cost	0.2	检测一条记录是否符合搜索条件的成本，增大这个值可 能让优化器更倾向于使用索引而不是直接全表扫描。

这些成本常数在server_cost 中的初始值都是 NULL ，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：

对我们感兴趣的成本常数做更新操作。比如我们想把检测一条记录是否符合搜索条件的成本增大到0.4，那么就可以这样写更新语句：

UPDATE mysql.server_cost  SET cost_value = 0.4 WHERE cost_name = 'row_evaluate_cost';

让系统重新加载这个表的值。使用下边语句即可：

FLUSH OPTIMIZER_COSTS;

当然，在修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value的值设置为NULL ，再重新加载它就好了。

2、engine_cost 表

engine_cost表中存有以下几种成本常数 ：

成本常数名称	默认值	描述
io_block_read_cost	1.0	从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词儿。对于 InnoDB 存储引擎来说，一个 页就是一个块，不过对于MyISAM存储引擎来说，默认是以 4096 字节作为一个块的。
memory_block_read_cost	1.0	与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。

这两个成本常数一个是从内存中读取一个块，一个是和从磁盘上读取一个块，但是默认成本是一样的。这主要是因为在MySQL 目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以MySQL不管这个块有没有加载到内存中，使用的成本都是1.0。

与更新server_cost 表中的记录一样，我们也可以通过更新 engine_cost 表中的记录来更改关于存储引擎的成本常数，我们也可以通过为engine_cost 表插入新记录的方式来添加只针对某种存储引擎的成本常数：

插入针对某个存储引擎的成本常数 比如我们想增大InnoDB 存储引擎页面 I/O 的成本，书写正常的插入语句即可：

INSERT INTO mysql.engine_cost VALUES ('InnoDB', 0, 'io_block_read_cost', 2.0, CURRENT_TIMESTAMP, 'increase Innodb I/O cost');

让系统重新加载这个表的值。使用下边语句即可：

FLUSH OPTIMIZER_COSTS

好了，到这里我们就讲完了，欢迎大家在评论区留言讨论，也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！