连接查询的成本

最新推荐文章于 2023-12-24 18:54:40 发布

lang20150928

最新推荐文章于 2023-12-24 18:54:40 发布

阅读量141

点赞数

分类专栏： sql mysql 文章标签： mysql

本文链接：https://blog.csdn.net/m0_37607945/article/details/122519988

版权

mysql 同时被 2 个专栏收录

61 篇文章 1 订阅

订阅专栏

sql

48 篇文章 0 订阅

订阅专栏

MySQL学习系列

Condition filtering 介绍

连接查询至少是要有两个表的，课程的讲述中可能使用 order_exp 表的派生表 s1、 s2 和 order_exp2。

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

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

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名： fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：
查询一：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;

假设使用 s1 表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573
当作在 s1 表的扇出值。

查询二：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 
WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09';

仍然假设 s1 表是驱动表的话，很显然对驱动表的单表查询可以使用idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-22 18:35:09’)中有多少条记录，那么扇出值就是多少。

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

查询三：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note > 'xyz';

本查询和查询一类似，只不过对于驱动表 s1 多了一个 order_note > 'xyz’的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这 10573 记录里有多少条记录满足 order_note > 'xyz’条件。

查询四：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 
WHERE s1.expire_time > '2021-03-22 18:28:28' AND s1.expire_time <= '2021-03-22 18:35:09'
AND s1.order_note > 'xyz';

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

mysql> explain SELECT * FROM order_exp AS s1 WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s1.order_note > 'xyz';
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_expire_time | idx_expire_time | 5       | NULL |   39 |    33.33 | Using index condition; Using where |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

查询五：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 
WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09'
AND s1.order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND s1.order_note > 'xyz';

本查询和查询四类似，不过在驱动表 s1 选取 idx_expire_time 索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
order_note > ‘xyz’
也就是优化器需要猜在 39 条记录中有多少符合上述两个条件的。
说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

MySQL 把这个猜的过程称之为 condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是 MySQL 单纯的瞎猜，整个评估过程非常复杂，所以我们不去细讲。

在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。

在 MySQL 5.7 中， MySQL 引入了这个 condition filtering 的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的， MySQL 称之为启发式规则。

两表连接的成本分析

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

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 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';

可以选择的连接顺序有两种：
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’

所以这个查询可能使用到 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 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

EXPLAIN 输出连接成本

连接查询在输出成本时和单表查询稍有不同，如下：


mysql> explain format=json 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'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,     # 整个查询语句只有 1 个 SELECT 关键字， 该关键字对应的 id 号为 1
    "cost_info": {
      "query_cost": "852.30"    # 整个查询的执行成本
    },
    "nested_loop": [           # 几个表之间采用嵌套循环连接算法执行
      {
        "table": {
          "table_name": "s2",           # s2 表是驱动表
          "access_type": "range",       # 访问方法为 range 
          "possible_keys": [
            "idx_expire_time"
          ],
          "key": "idx_expire_time",
          "used_key_parts": [
            "expire_time"
          ],
          "key_length": "5",
          "rows_examined_per_scan": 321,      # 查询 s2 表大致需要扫描 321 条记录
          "rows_produced_per_join": 321,       # 驱动表 s2 的扇出是 321
          "filtered": "100.00",                # condition filtering 代表的百分比
          "index_condition": "((`test`.`s2`.`expire_time` > '2021-03-22 18:35:09') 
          and (`test`.`s2`.`expire_time` <= '2021-03-22 18:35:59'))",
          "cost_info": {
            "read_cost": "386.21",
            "eval_cost": "64.20",
            "prefix_cost": "450.41",             # 查询 s1 表总共的成本， read_cost + eval_cost
            "data_read_per_join": "152K"         # 读取的数据量 
          },
          "used_columns": [
            "id",
            "order_no",
            "order_note",
            "insert_time",
            "expire_duration",
            "expire_time",
            "order_status"
          ]
        }
      },
      {
        "table": {
          "table_name": "s1",                    # s1 表是被驱动表
          "access_type": "ref",
          "possible_keys": [
            "idx_order_no",
            "idx_expire_time"
          ],
          "key": "idx_order_no",
          "used_key_parts": [
            "order_no"
          ],
          "key_length": "152",
          "ref": [
            "test.s2.order_note"
          ],
          "rows_examined_per_scan": 1,              # 查询一次 s1 表大致需要扫描 1 条记录  
          "rows_produced_per_join": 16,             # 被驱动表的扇出是 16（由于没有多余的表进行连接， 所以这个值无用）
          "filtered": "4.79",						# condition filtering 代表的百分比
          "index_condition": "(`test`.`s1`.`order_no` = `test`.`s2`.`order_note`)",
          "cost_info": {
            "read_cost": "334.91",
            "eval_cost": "3.21",
            "prefix_cost": "852.30",                 # 单次查询 s2、 多次查询 s1 表总共的成本
            "data_read_per_join": "7K"
          },
          "used_columns": [
            "id",
            "order_no",
            "order_note",
            "insert_time",
            "expire_duration",
            "expire_time",
            "order_status"
          ],
          "attached_condition": "((`test`.`s1`.`expire_time` > '2021-03-22\\n18:28:28') 
          and (`test`.`s1`.`expire_time` <= '2021-03-22 18:35:09'))"
        }
      }
    ]
  }
}
1 row in set, 1 warning (0.01 sec)

我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的输出内容， s2 表的"cost_info"中 prefix_cost 就是单独查询 s2 表的成本。
对于 s1 表的"cost_info"中，由于 s1 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，而 s1 表中的prefix_cost 的值代表的是整个连接查询预计的成本。

看完了上面的执行计划的输出，可能大家有疑惑，驱动表 S2 的查询成本为450.41，总查询成本为 840.51，也就是说对被驱动表 S1 的查询成本也就是 390左右，看起来用 S1 做驱动表好像更省一点。真的这样吗？我们把 SQL 语句改造一下，将 INNER JOIN 替换为 STRAIGHT_JOIN：


mysql> explain format=json SELECT * FROM order_exp AS s1 STRAIGHT_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'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "17621.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "range",
          "possible_keys": [
            "idx_order_no",
            "idx_expire_time"
          ],
          "key": "idx_expire_time",
          "used_key_parts": [
            "expire_time"
          ],
          "key_length": "5",
          "rows_examined_per_scan": 39,
          "rows_produced_per_join": 39,
          "filtered": "100.00",
          "index_condition": "((`test`.`s1`.`expire_time` > '2021-03-22 18:28:28') and (`test`.`s1`.`expire_time` <= '2021-03-22 18:35:09'))",
          "cost_info": {
            "read_cost": "47.81",
            "eval_cost": "7.80",
            "prefix_cost": "55.61",
            "data_read_per_join": "18K"
          },
          "used_columns": [
            "id",
            "order_no",
            "order_note",
            "insert_time",
            "expire_duration",
            "expire_time",
            "order_status"
          ]
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "range",
          "possible_keys": [
            "idx_expire_time"
          ],
          "key": "idx_expire_time",
          "used_key_parts": [
            "expire_time"
          ],
          "key_length": "5",
          "rows_examined_per_scan": 321,
          "rows_produced_per_join": 1251,
          "filtered": "10.00",
          "index_condition": "((`test`.`s2`.`expire_time` > '2021-03-22 18:35:09') and (`test`.`s2`.`expire_time` <= '2021-03-22\\n18:35:59'))",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "15062.19",
            "eval_cost": "250.38",
            "prefix_cost": "17621.60",
            "data_read_per_join": "596K"
          },
          "used_columns": [
            "id",
            "order_no",
            "order_note",
            "insert_time",
            "expire_duration",
            "expire_time",
            "order_status"
          ],
          "attached_condition": "(`test`.`s1`.`order_no` = `test`.`s2`.`order_note`)"
        }
      }
    ]
  }
}
1 row in set, 1 warning (0.00 sec)

从上面的执行结果不难看出，最后的查询成本多了很多。虽然此时驱动表的扇出值由321变为39，但是被驱动表查询一条记录的记录数由1变为了321。

多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：
对于两表连接，比如表 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> show variables like '%optimizer_search_depth%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+
1 row in set (0.00 sec)

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

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


mysql> show variables like '%optimizer_prune_level%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| optimizer_prune_level | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)

lang20150928

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
连接查询的成本

MySQL学习系列Condition filtering 介绍连接查询至少是要有两个表的，课程的讲述中可能使用 order_exp 表的派生表 s1、 s2 和 order_exp2。我们前边说过， MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：单次查询驱动表的成本多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）对驱动表进行查询后得到的记录条.
复制链接

扫一扫