0
0
1
0
专栏/.../

TiDB 执行计划代价模型分析

 小龙虾爱大龙虾  发表于  2024-08-04

引言

TiDB 的优化器是基于成本的优化器,在 SQL 执行过程中,会经历 compile 阶段,先通过逻辑优化对 SQL 进行等价改写,然后通过统计信息和代价模型,对成本进行估算,选择成本最小的物理执行计划,那成本究竟是如何计算的呢?本文就来简单分析一下。

获取执行计划 cost 信息

这里测试版本为 v8.1 版本,tidb_cost_model_version 参数设置为 2。

MySQL [test]> explain format='cost_trace' select /*+ INL_JOIN(t2)*/ t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t2.col1='abcdefg' where t1.col1='50e78ff215';
+------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | estCost | costFormula                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | task      | access object                 | operator info                                                                                                            |
+------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_16                       | 1.00    | 3952.51 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((net(1*rowsize(23.79)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(31.54)*tikv_scan_factor(40.7))))/15.00) + (((((net(1*rowsize(55.12)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(62.62)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(1*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.0016)*tidb_request_factor(6e+06)))))/5.00)) + (cpu(1*filters(0)*tidb_cpu_factor(49.9))) + (cpu(1*10*tidb_cpu_factor(49.9))) + ((() + ((((((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(38)*tikv_scan_factor(40.7)))) + (net(0.0001*rowsize(38)*tidb_kv_net_factor(3.96))))/15.00)*1.00)/6.00) + (cpu(0.0001*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(0.0001*0*tidb_cpu_factor(49.9))) + (hashmem(0.0001*38*tidb_mem_factor(0.2))) + (hashbuild(0.0001*tidb_cpu_factor(49.9)))))/5.00) | root      |                               | left outer join, inner:TableReader_12, outer key:test.t1.id, inner key:test.t2.id, equal cond:eq(test.t1.id, test.t2.id) |
| ├─IndexLookUp_26(Build)            | 1.00    | 1955.92 | (((net(1*rowsize(23.79)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(31.54)*tikv_scan_factor(40.7))))/15.00) + (((((net(1*rowsize(55.12)*tidb_kv_net_factor(3.96))) + (scan(1*logrowsize(62.62)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(1*tidb_cpu_factor(49.9))) + (doubleRead(tasks(0.0016)*tidb_request_factor(6e+06)))))/5.00)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | root      |                               |                                                                                                                          |
| │ ├─IndexRangeScan_24(Build)       | 1.00    | 202.65  | scan(1*logrowsize(31.54)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | cop[tikv] | table:t1, index:t1_col1(col1) | range:["50e78ff215","50e78ff215"], keep order:false, stats:partial[id:allEvicted]                                        |
| │ └─TableRowIDScan_25(Probe)       | 1.00    | 242.92  | scan(1*logrowsize(62.62)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | cop[tikv] | table:t1                      | keep order:false, stats:partial[id:allEvicted]                                                                           |
| └─TableReader_12(Probe)            | 0.00    | 17.57   | (((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(38)*tikv_scan_factor(40.7)))) + (net(0.0001*rowsize(38)*tidb_kv_net_factor(3.96))))/15.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | root      |                               | data:Selection_11                                                                                                        |
|   └─Selection_11                   | 0.00    | 263.49  | (cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(38)*tikv_scan_factor(40.7)))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | cop[tikv] |                               | eq(test.t2.col1, "abcdefg")                                                                                              |
|     └─TableRangeScan_10            | 1.00    | 213.59  | scan(1*logrowsize(38)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | cop[tikv] | table:t2                      | range: decided by [test.t1.id], keep order:false, stats:partial[id:allEvicted]                                           |
+------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.02 sec)

简单分析

可以看到 costFormula 列显示了 cost 的计算过程,其中包含一些参数,通过查阅代码,找到了这些参数的值。

var defaultVer2Factors = costVer2Factors{
    TiDBTemp:      costVer2Factor{"tidb_temp_table_factor", 0.00},
    TiKVScan:      costVer2Factor{"tikv_scan_factor", 40.70},
    TiKVDescScan:  costVer2Factor{"tikv_desc_scan_factor", 61.05},
    TiFlashScan:   costVer2Factor{"tiflash_scan_factor", 11.60},
    TiDBCPU:       costVer2Factor{"tidb_cpu_factor", 49.90},
    TiKVCPU:       costVer2Factor{"tikv_cpu_factor", 49.90},
    TiFlashCPU:    costVer2Factor{"tiflash_cpu_factor", 2.40},
    TiDB2KVNet:    costVer2Factor{"tidb_kv_net_factor", 3.96},
    TiDB2FlashNet: costVer2Factor{"tidb_flash_net_factor", 2.20},
    TiFlashMPPNet: costVer2Factor{"tiflash_mpp_net_factor", 1.00},
    TiDBMem:       costVer2Factor{"tidb_mem_factor", 0.20},
    TiKVMem:       costVer2Factor{"tikv_mem_factor", 0.20},
    TiFlashMem:    costVer2Factor{"tiflash_mem_factor", 0.05},
    TiDBDisk:      costVer2Factor{"tidb_disk_factor", 200.00},
    TiDBRequest:   costVer2Factor{"tidb_request_factor", 6000000.00},
}

TableRangeScan_10

这个算子是一个表范围扫的算子,从 costFormula 列可以看到计算方法为 scan(1*logrowsize(38)*tikv_scan_factor(40.7)),cost 为 213.59,括号内的值代表参数值,比如 tikv_scan_factor 值为 40.7,前边已经提到了,对比代码中的公式为 rows*math.Log2(rowSize)*scanFactor.Value ,将参数带入到公式中

1*log2(38)*40.7≈213.590650

可见表范围扫算子的 cost 主要和扫描的预估行数、行长有关。

PS:这里的 rowSize 与我统计信息里的行长稍有偏差,代码中是通过 getAvgRowSize 函数获得的。

Selection_11

这个算子是一个过滤算子,从 costFormula 列可以看到计算方法为 (cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(38)*tikv_scan_factor(40.7))),cost 为 263.49,它的成本已经包含了它的子算子 TableRangeScan_10,所以我们主要关注 cpu(1*filters(1)*tikv_cpu_factor(49.9))) 部分,对比代码中的公式为 rows*numFuncs*cpuFactor.Value,将参数带入公式中

1*1*49.9=49.9

numFuncs 应该是过滤算子中操作符的数量。再加上它的子算子 TableRangeScan_10 的 cost(213.590650),所以这步的成本为

49.9+213.590650=263.490650

可见过滤算子的 cost 主要和进入该算子的预估行数、行长、过滤操作符数量有关。

TableReader_12

这个算子是 TiDB 侧读取数据汇总的算子,从 costFormula 列可以看到计算方法为 (((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(38)*tikv_scan_factor(40.7)))) + (net(0.0001*rowsize(38)*tidb_kv_net_factor(3.96))))/15.00,它的成本同样包含了它的子算子的成本,所以我们主要关注 (net(0.0001*rowsize(38)*tidb_kv_net_factor(3.96))) 和最终除 15 的部分。

从代码注释中可以知道公式为 plan-cost = (child-cost + net-cost) / concurrency ,所以 15 为 concurrency,即参数 tidb_distsql_scan_concurrency 的值。

剩余 net-cost 就是 (net(0.0001*rowsize(38)*tidb_kv_net_factor(3.96))),对比代码中的公式为 rows*rowSize*netFactor.Value,讲参数带入公式中

0.0001*38*3.96=0.015048

0.0001 是 Selection_11 算子的预估返回行数,再带入到整个公式中 plan-cost = (child-cost + net-cost) / concurrency,这步的成本为

(263.490650+0.015048)/15=17.5670465

可见 TableReader 算子的 cost 主要和进入该算子的预估行数、行长、扫表并发度有关。

总结

TiDB 使用基于成本的优化器来选择最合适的执行计划。成本估算考虑了多种因素,包括预估行数、行长、过滤操作符数量、扫描并发度等。当前的成本估算公式虽然已经考虑了多种影响因素,但仍有改进空间,例如考虑 Region 分布、索引聚簇因子以及实际集群物理环境的影响。相信随着 TiDB 的持续迭代,预计成本估算公式将不断完善,执行计划的选择会更加精确和智能。

0
0
1
0

版权声明:本文为 TiDB 社区用户原创文章,遵循 CC BY-NC-SA 4.0 版权协议,转载请附上原文出处链接和本声明。

评论
暂无评论