MySQL连接查询的成本

港控/mmm° 2022-09-09 06:05 137阅读 0赞

## Condition filtering介绍 ##

连接查询至少是要有两个表的，这里使用的是t\_emp和salaries。

我们前边说过，MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

*  单次查询驱动表的成本
 *  多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值。

有的时候扇出值的计算是很容易的，比如下边这个查询：

使用t\_emp表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。

但是有的时候扇出值的计算就变得很棘手，比方说下边这个查询：

这个查询只不过对于驱动表t\_emp多了一个`last_name like 'a%'`的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜扇出的记录里有多少条记录满足这个条件。

说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。MySQL把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程非常复杂。

在MySQL 5.7之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。在MySQL 5.7中，MySQL引入了这个condition filtering的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的，MySQL称之为启发式规则。

## 两表连接的成本分析 ##

连接查询的成本计算公式是这样的：连接查询总成本= 单次访问驱动表的成本 + 驱动表扇出数 \* 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序，然后分别为驱动表和被驱动表选择成本最低的访问方法。

很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询。

我们来看看内连接，比如对于下边这个查询来说：

mysql> create table t_emp2 like t_emp;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into t_emp2 select * from t_emp;
    
    mysql> explain select * from t_emp t1 inner join t_emp t2 on t1.emp_no = t2.emp_no where t1.hire_date>='1990-11-01' and t2.birth_date>='1990-12-01';

可以选择的连接顺序有两种：

*  t1连接t2，也就是t1作为驱动表，t2作为被驱动表。
 *  t2连接t1，也就是t2作为驱动表，t1作为被驱动表。查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。

我们定性的分析一下，不像分析单表查询那样定量的分析了：

### 使用t1作为驱动表的情况 ###

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

*  t1.hire\_date>=‘1990-11-01’

所以这个查询可能使用到idx\_hire\_date索引，从全表扫描和使用idx\_hire\_date这两个方案中选出成本最低的那个，假设使用idx\_hire\_date执行查询的成本更低些。

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

*  t2.emp\_no=常数（这是因为对驱动表t1结果集中的每一条记录，都需要进行一次被驱动表t2的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表t2了。）
 *  t2.birth\_date>=‘1990-12-01’

此时访问t2表时可用的方案全表扫描、emp\_no(PRIMARY)、idx\_hire\_date，肯定是使用emp\_no(PRIMARY)的成本更小。

所以此时使用t1作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：使用idx\_hire\_date访问t1的成本 + t1的扇出 \* 使用emp\_no(PRIMARY)访问t2的成本

### 使用t2作为驱动表的情况 ###

首先看一下涉及t2表单表的搜索条件有哪些：

*  t2.birth\_date>=‘1990-12-01’

所以这个查询可能使用到idx\_birth\_date索引，从全表扫描和使用idx\_birth\_date这两个方案中选出成本最低的那个，假设使用idx\_birth\_date执行查询的成本更低些。

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

*  t1.emp\_no=常数
 *  t1.hire\_date>=‘1990-11-01’

那么优化器需要从全表扫描、emp\_no(PRIMARY)、idx\_hire\_date这几个方案里选出一个成本最低的方案。假设使用emp\_no(PRIMARY)的成本最小，所以此时使用t2作为驱动表时的总成本就是：使用idx\_birth\_date访问t2的成本 + t2的扇出 \* 使用idx\_hire\_date访问t1的成本。

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

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

### 使用EXPLAIN输出连接成本 ###

mysql> explain select * from t_emp t1 inner join t_emp t2 on t1.emp_no = t2.emp_no where t1.hire_date>='1990-11-01' and t2.birth_date>='1990-12-01';     +----+-------------+-------+------------+--------+------------------------+----------------+---------+---------------------+------+----------+-----------------------+
    | id | select_type | table | partitions | type   | possible_keys          | key            | key_len | ref                 | rows | filtered | Extra                 |
    +----+-------------+-------+------------+--------+------------------------+----------------+---------+---------------------+------+----------+-----------------------+
    |  1 | SIMPLE      | t2    | NULL       | range  | PRIMARY,idx_birth_date | idx_birth_date | 3       | NULL                |    1 |   100.00 | Using index condition |
    |  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY,idx_hire_date  | PRIMARY        | 4       | employees.t2.emp_no |    1 |    50.00 | Using where           |
    +----+-------------+-------+------------+--------+------------------------+----------------+---------+---------------------+------+----------+-----------------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> explain format=json select * from t_emp t1 inner join t_emp t2 on t1.emp_no = t2.emp_no where t1.hire_date>='1990-11-01' and t2.birth_date>='1990- 12-01';
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {
      "query_block": {
        "select_id": 1, # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
        "cost_info": {
          "query_cost": "3.61" # 整个查询的执行成本
        },
        "nested_loop": [ # 几个表之间采用嵌套循环连接算法执行
          {
            "table": {
              "table_name": "t2", # t2表是驱动表
              "access_type": "range", # 访问方式为range
              "possible_keys": [ # 可能用到的索引
                "PRIMARY",
                "idx_birth_date"
              ],
              "key": "idx_birth_date", # 实际用到的索引
              "used_key_parts": [
                "birth_date"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 1, # 查询t2表大致需要扫描1条记录
              "rows_produced_per_join": 1, # 驱动表t2的扇出是1
              "filtered": "100.00", # condition filtering代表的百分比
              "index_condition": "(`employees`.`t2`.`birth_date` >= '1990-12-01')",
              "cost_info": {
                "read_cost": "2.21",
                "eval_cost": "0.20",
                "prefix_cost": "2.41",  # 查询t2表总共的成本，read_cost + eval_cost
                "data_read_per_join": "48"  # 读取的数据量大小
              },
              "used_columns": [
                "emp_no",
                "birth_date",
                "first_name",
                "last_name",
                "gender",
                "hire_date"
              ]
            }
          },
          {
            "table": {
              "table_name": "t1", # t1表是被驱动表
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "idx_hire_date"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "emp_no"
              ],
              "key_length": "4",
              "ref": [
                "employees.t2.emp_no"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "50.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.10",
                "prefix_cost": "3.61",
                "data_read_per_join": "23" # # 单次查询t2、多次查询t1表总共的成本
              },
              "used_columns": [
                "emp_no",
                "birth_date",
                "first_name",
                "last_name",
                "gender",
                "hire_date"
              ],
              "attached_condition": "(`employees`.`t1`.`hire_date` >= '1990-11-01')"
            }
          }
        ]
      }
    } |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

我们把SQL语句改造一下，将inner join替换为straight\_join看看使用t1作为驱动表的成本是多少：

mysql> explain format=json select * from t_emp t1 straight_join t_emp t2 on t1.emp_no = t2.emp_no where t1.hire_date>='1990-11-01' and t2.birth_date>='1990 +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "240644.40" }, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "PRIMARY", "idx_hire_date" ], "rows_examined_per_scan": 299645, "rows_produced_per_join": 149821, "filtered": "50.00", "cost_info": { "read_cost": "30893.60", "eval_cost": "29964.40", "prefix_cost": "60858.00", "data_read_per_join": "6M" }, "used_columns": [ "emp_no", "birth_date", "first_name", "last_name", "gender", "hire_date" ], "attached_condition": "(`employees`.`t1`.`hire_date` >= '1990-11-01')" } }, { "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": [ "PRIMARY", "idx_birth_date" ], "key": "PRIMARY", "used_key_parts": [ "emp_no" ], "key_length": "4", "ref": [ "employees.t1.emp_no" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 7491, "filtered": "5.00", "cost_info": { "read_cost": "149822.00", "eval_cost": "1498.22", "prefix_cost": "240644.40", "data_read_per_join": "351K" }, "used_columns": [ "emp_no", "birth_date", "first_name", "last_name", "gender", "hire_date" ], "attached_condition": "(`employees`.`t2`.`birth_date` >= '1990-12-01')"
            }
          }
        ]
      }
    } |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.01 sec)

## 多表连接的成本分析 ##

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

*  对于两表连接，比如表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查询优化器要每一种连接顺序的成本都计算一遍么？那可是n!种连接顺序呀。其实真的是要都算一遍，不过MySQL用了很多办法减少计算非常多种连接顺序的成本的方法：

*  提前结束某种顺序的成本评估：MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说A、B、C 三个表进行连接，已经得到连接顺序ABC 是当前的最小连接成本，比方说10.0，在计算连接顺序BCA时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA这个连接顺序的成本了。
 *  系统变量optimizer\_search\_depth：为了防止无穷无尽的分析各种连接顺序的成本，MySQL提出了optimizer\_search\_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer\_search\_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。
 *  根据某些规则压根儿就不考虑某些连接顺序即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer\_prune\_level来控制到底是不是用这些启发式规则。