TiDB 优化器 | 执行计划管理及实践

原创

于 2024-12-12 13:59:34 发布 · 1.1k 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#tidb #数据库

导读

在 TiDB 中，优化器的作用至关重要，它决定了 SQL 查询的执行计划，从而直接影响查询性能。尽管 TiDB 优化器采用了代价模型来选择最优执行计划，但由于统计信息、估算误差等因素，优化器并不能保证每次都选中最佳计划。本文深入解析了 TiDB 优化器的执行计划生成过程及其局限性，介绍了如何通过 Hint、SQL Binding、执行计划缓存等技术手段进行执行计划管理，确保查询性能的稳定性和高效性。

背景与现状

优化器和执行计划的影响

在数据库中，优化器负责将用户的 SQL 转换成执行计划，执行计划决定了数据库会怎么执行这条 SQL，如：

通过什么方式（全表扫描还是索引扫描）访问数据；
多表 Join 的顺序（先 Join 那几个表）及方式（HashJoin 还是 IndexJoin 等）；
在 HTAP 系统中访问行存还是列存存储引擎等。

不同计划的执行效率可能有数量级的差异，错误计划可能带来很大的负面影响，以下面这个 SQL 为例：

create table customers (id int primary key, address varchar(20), key addr(address));
select count(*) from customers where address in ("X", "Y");

这个 SQL 查询特定区域的用户数，对于这个 SQL 访问数据的方式有两种：

使用 primary key 扫描数据，此时无法利用 address 的条件，需要扫描全部数据；
使用 key(addr) 索引扫描，可以把 address in (...) 的条件转换为此索引上的一小段扫描范围，减少需要扫描的数据量，提高执行效率。

比如这张表有 1000000 数据，但是 “X” 和 “Y” 的用户只有 20 个，那选择 key(addr) 仅需要扫描 20 行数据，而 primary key 则需要扫描 1000000 行。

TiDB 如何基于 CBO 选择执行计划

简单来说 TiDB 优化器生成执行计划有 3 步：

解析这个 SQL，得到一个最基础的 Plan；
在此 Plan 上进行改写和探索，得到多个相同语义的 Plan；
利用代价模型对这些 Plan 估算代价，选代价最低的那个。

我们以上面的 select count(*) from customers where address in ("X", "Y") 为例。

此 SQL 有两种扫描数据方式，我们用 explain format='cost_trace' 让优化器输出对应的代价计算过程。

如果使用 key(addr) 索引：

mysql> explain format='cost_trace' select count(*) from customers where address in ("X", "Y");
+-----------------------------+---------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------------------------------+----------------------------------------------+
| id                          | estRows | estCost | costFormula                                                                                                                                                                                                                                                                          | task      | access object                        | operator info                                |
+-----------------------------+---------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------------------------------+----------------------------------------------+
| StreamAgg_17                | 1.00    | 290.57  | ((((scan(20*logrowsize(9)*tikv_scan_factor(40.7))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) + (group(20*cols(0)*tikv_cpu_factor(49.9)))) + (net(1*rowsize(8)*tidb_kv_net_factor(3.96))))/15.00) + (agg(1*aggs(1)*tidb_cpu_factor(49.9))) + (group(1*cols(0)*tidb_cpu_factor(49.9))) | root      |                                      | funcs:count(Column#5)->Column#3              |
| └─IndexReader_18            | 1.00    | 240.67  | (((scan(20*logrowsize(9)*tikv_scan_factor(40.7))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) + (group(20*cols(0)*tikv_cpu_factor(49.9)))) + (net(1*rowsize(8)*tidb_kv_net_factor(3.96))))/15.00                                                                                       | root      |                                      | index:StreamAgg_9                            |
|   └─StreamAgg_9             | 1.00    | 3578.32 | (scan(20*logrowsize(9)*tikv_scan_factor(40.7))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) + (group(20*cols(0)*tikv_cpu_factor(49.9)))                                                                                                                                                | cop[tikv] |                                      | funcs:count(1)->Column#5                     |
|     └─IndexRangeScan_16     | 20.00   | 2580.32 | scan(20*logrowsize(9)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                        | cop[tikv] | table:customers, index:addr(address) | range:["X","X"], ["Y","Y"], keep order:false |
+-----------------------------+---------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------------------------------+----------------------------------------------+

costFormula 这一列直接输出每个算子（包含其孩子节点）的代价公式：

IndexRangeScan_16：优化器估计会访问 20 行数据，行宽是 9 bytes，TiKV 的扫描因子是 40.7，所以对于这个算子，带入优化器的代价公式则是 20*log(9)*40.7 = 2580.32；
StreamAgg_9：优化器估计他需要处理 20 行数据，他有 1 个 agg 函数，TiKV 的 CPU 计算因子是 49.9，因此聚合函数的代价是 20 * 1 * 49.9 = 998；由于没有 group by 子句，后面 group 代价部分的 col 是 0，因此后面 group 的代价则是 0，这个算子及其孩子的代价就是 998+2580.32+0 = 3578.32；
IndexReader_18：同理，会考虑上网络传输的代价，以及执行计划并发（默认的并发为 15）；
StreamAgg_17：同理，考虑最终对结果做聚合的代价，最终得到代价 290.57 作为此执行计划的代价。

作为对比，再看下使用 primary key 的代价：（我们使用 Hint 让优化器忽略 addr 索引）

mysql> explain format='cost_trace' select /*+ ignore_index(customers, addr) */ count(*) from customers where address in ("X", "Y");
+------------------------------+---------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------------+--------------------------------------+
| id                           | estRows | estCost    | costFormula                                                                                                                                                                                                                                                                                                                              | task      | access object   | operator info                        |
+------------------------------+---------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------------+--------------------------------------+
| StreamAgg_20                 | 1.00    | 80072.07   | (((((cpu(5020*filters(1)*tikv_cpu_factor(49.9))) + (scan(5020*logrowsize(25)*tikv_scan_factor(40.7)))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) + (group(20*cols(0)*tikv_cpu_factor(49.9)))) + (net(1*rowsize(8)*tidb_kv_net_factor(3.96))))/15.00) + (agg(1*aggs(1)*tidb_cpu_factor(49.9))) + (group(1*cols(0)*tidb_cpu_factor(49.9))) | root      |                 | funcs:count(Column#5)->Column#3      |
| └─TableReader_21             | 1.00    | 80022.17   | ((((cpu(5020*filters(1)*tikv_cpu_factor(49.9))) + (scan(5020*logrowsize(25)*tikv_scan_factor(40.7)))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) + (group(20*cols(0)*tikv_cpu_factor(49.9)))) + (net(1*rowsize(8)*tidb_kv_net_factor(3.96))))/15.00                                                                                       | root      |                 | data:StreamAgg_9                     |
|   └─StreamAgg_9              | 1.00    | 1200300.83 | ((cpu(5020*filters(1)*tikv_cpu_factor(49.9))) + (scan(5020*logrowsize(25)*tikv_scan_factor(40.7)))) + (agg(20*aggs(1)*tikv_cpu_factor(49.9))) +