作者： 小龙虾爱大龙虾原文来源：https://tidb.net/blog/885886a8
引言
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，将参数带入公式中
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 是 Selection_11 算子的预估返回行数，再带入到整个公式中 plan-cost = (child-cost + net-cost) / concurrency，这步的成本为
(263.490650+0.015048)/15=17.5670465
复制代码
可见 TableReader 算子的 cost 主要和进入该算子的预估行数、行长、扫表并发度有关。
总结
TiDB 使用基于成本的优化器来选择最合适的执行计划。成本估算考虑了多种因素，包括预估行数、行长、过滤操作符数量、扫描并发度等。当前的成本估算公式虽然已经考虑了多种影响因素，但仍有改进空间，例如考虑 Region 分布、索引聚簇因子以及实际集群物理环境的影响。相信随着 TiDB 的持续迭代，预计成本估算公式将不断完善，执行计划的选择会更加精确和智能。
评论