优化器cost_model_version=2情况下对回表操作评估存在问题导致执行计划有问题

人如其名 · 2023 年9 月 17 日 15:43

Bug 反馈
清晰准确地描述您发现的问题，提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 TiDB 版本】5.7.25-TiDB-v6.5.3
【 Bug 的影响】cost_model_version=2的所有版本

【可能的问题复现步骤】
使用tpch sf=10情况下做如下操作：
alter table customer add index idx1(C_NATIONKEY);
analyze table customer;

【看到的非预期行为】

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v6.5.3 |
+--------------------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_cost_model_version';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| tidb_cost_model_version | 2     |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql>  explain analyze select  * from nation a  join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+----------------------------------+------------+---------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                               | estRows    | actRows | task      | access object                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | operator info                                                                                                                                                                       | memory   | disk |
+----------------------------------+------------+---------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_14                 | 1500000.00 | 1500000 | root      |                                  | time:7.33s, loops:1466, inner:{total:7.33s, concurrency:5, task:1, construct:24.2µs, fetch:6.79s, build:9.12µs, join:540.3ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                 | inner join, inner:IndexLookUp_11, outer key:tpch10.nation.n_nationkey, inner key:tpch10.customer.c_nationkey, equal cond:eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey) | 406.9 MB | N/A  |
| ├─TableReader_37(Build)          | 25.00      | 25      | root      |                                  | time:854.5µs, loops:3, cop_task: {num: 1, max: 847.3µs, proc_keys: 25, rpc_num: 1, rpc_time: 818.5µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                                                                                                    | data:TableFullScan_36                                                                                                                                                               | 3.02 KB  | N/A  |
| │ └─TableFullScan_36             | 25.00      | 25      | cop[tikv] | table:a                          | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 49.2µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 7}}}                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                    | N/A      | N/A  |
| └─IndexLookUp_11(Probe)          | 1500000.00 | 1500000 | root      |                                  | time:6.77s, loops:1466, index_task: {total_time: 6.54s, fetch_handle: 196.3ms, build: 332.3µs, wait: 6.35s}, table_task: {total_time: 33.2s, num: 77, concurrency: 5}, next: {wait_index: 2.58ms, wait_table_lookup_build: 448.5µs, wait_table_lookup_resp: 5.61s}                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                     | 29.5 MB  | N/A  |
|   ├─IndexRangeScan_9(Build)      | 1500000.00 | 1500000 | cop[tikv] | table:b, index:idx1(C_NATIONKEY) | time:49.7ms, loops:1474, cop_task: {num: 48, max: 93.3ms, min: 624µs, avg: 12ms, p95: 46.6ms, max_proc_keys: 5088, p95_proc_keys: 2016, tot_proc: 1ms, tot_wait: 124ms, rpc_num: 48, rpc_time: 574.1ms, copr_cache_hit_ratio: 0.85, distsql_concurrency: 15}, tikv_task:{proc max:58ms, min:0s, avg: 17.1ms, p80:33ms, p95:53ms, iters:1650, tasks:48}, scan_detail: {total_process_keys: 12064, total_process_keys_size: 554944, total_keys: 12071, get_snapshot_time: 1.45ms, rocksdb: {key_skipped_count: 12064, block: {cache_hit_count: 56}}}                            | range: decided by [eq(tpch10.customer.c_nationkey, tpch10.nation.n_nationkey)], keep order:false                                                                                    | N/A      | N/A  |
|   └─TableRowIDScan_10(Probe)     | 1500000.00 | 1500000 | cop[tikv] | table:b                          | time:32.2s, loops:1619, cop_task: {num: 155, max: 666.5ms, min: 1.2ms, avg: 276.7ms, p95: 581.3ms, max_proc_keys: 20640, p95_proc_keys: 20026, tot_proc: 38.4s, tot_wait: 305ms, rpc_num: 155, rpc_time: 42.9s, copr_cache_hit_ratio: 0.03, distsql_concurrency: 15}, tikv_task:{proc max:633ms, min:1ms, avg: 242.7ms, p80:404ms, p95:535ms, iters:2162, tasks:155}, scan_detail: {total_process_keys: 1498923, total_process_keys_size: 305007264, total_keys: 1557156, get_snapshot_time: 3.27ms, rocksdb: {key_skipped_count: 118972, block: {cache_hit_count: 7446827}}} | keep order:false                                                                                                                                                                    | N/A      | N/A  |
+----------------------------------+------------+---------+-----------+----------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set (7.33 sec)

走了IndexHashJoin，效率较低。

【期望看到的行为】
走HashJoin，如下：

mysql> set tidb_cost_model_version=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  explain analyze select  * from nation a  join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+-----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| id                          | estRows    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                        | operator info                                                                  | memory  | disk    |
+-----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| HashJoin_30                 | 1500000.00 | 1500000 | root      |               | time:829.4ms, loops:1468, build_hash_table:{total:1.43ms, fetch:1.41ms, build:16.7µs}, probe:{concurrency:5, total:4.15s, max:829.7ms, probe:722.8ms, fetch:3.43s}                                                                                                                                                                                    | inner join, equal:[eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey)] | 50.2 KB | 0 Bytes |
| ├─TableReader_37(Build)     | 25.00      | 25      | root      |               | time:1.35ms, loops:2, cop_task: {num: 1, max: 1.47ms, proc_keys: 25, rpc_num: 1, rpc_time: 1.43ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                               | data:TableFullScan_36                                                          | 3.03 KB | N/A     |
| │ └─TableFullScan_36        | 25.00      | 25      | cop[tikv] | table:a       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 27.6µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 5, read_count: 1, read_byte: 63.9 KB, read_time: 33µs}}}                                                                                      | keep order:false                                                               | N/A     | N/A     |
| └─TableReader_39(Probe)     | 1500000.00 | 1500000 | root      |               | time:614.9ms, loops:1468, cop_task: {num: 54, max: 112.5ms, min: 668.7µs, avg: 38.3ms, p95: 108.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.43s, tot_wait: 22ms, rpc_num: 54, rpc_time: 2.06s, copr_cache_hit_ratio: 0.39, distsql_concurrency: 15}                                                                                  | data:TableFullScan_38                                                          | 22.6 MB | N/A     |
|   └─TableFullScan_38        | 1500000.00 | 1500000 | cop[tikv] | table:b       | tikv_task:{proc max:48ms, min:0s, avg: 18ms, p80:34ms, p95:45ms, iters:1678, tasks:54}, scan_detail: {total_process_keys: 1237215, total_process_keys_size: 251730228, total_keys: 1237248, get_snapshot_time: 1.17ms, rocksdb: {key_skipped_count: 1237215, block: {cache_hit_count: 4302, read_count: 2, read_byte: 127.8 KB, read_time: 43.5µs}}}  | keep order:false                                                               | N/A     | N/A     |
+-----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
5 rows in set (0.83 sec)

【相关组件及具体版本】优化器成本评估模型tidb_cost_model_version=2的所有版本。

原因分析：
当tidb_cost_model_version=1时，索引回表扫描的成本评估为：tidb_opt_seek_factor=20，顺序扫描成本评估为tidb_opt_scan_factor=1.5。

mysql> show variables like '%factor%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_fill_factor                  |       |
| tidb_opt_concurrency_factor         | 3     |
| tidb_opt_copcpu_factor              | 3     |
| tidb_opt_correlation_exp_factor     | 1     |
| tidb_opt_cpu_factor                 | 3     |
| tidb_opt_desc_factor                | 3     |
| tidb_opt_disk_factor                | 1.5   |
| tidb_opt_memory_factor              | 0.001 |
| tidb_opt_network_factor             | 1     |
| tidb_opt_scan_factor                | 1.5   |
| tidb_opt_seek_factor                | 20    |
| tidb_opt_tiflash_concurrency_factor | 24    |
+-------------------------------------+-------+
12 rows in set (0.00 sec)

但是当tidb_cost_model_version=2时，索引回表扫描的成本评估为：tikv_scan_factor=40.70，顺序扫描成本评估也是用到相同的变量tikv_scan_factor=40.70，没有对顺序扫描和回表扫描做分离评估。
version=2的评估写死在代码了，没有暴露出来（但是GA了后我认为应该可供配置比较好）
https://github.com/pingcap/tidb/blob/21db64fa76179464955ae28df679f21661bf55ff/planner/core/plan_cost_ver2.go#L925

参考我之前的发帖：tidb_cost_model_version=2情况下为何没有seek_factor

能否优化tidb_cost_model_version=2时回表成本评估？并将其设计成变量形式暴露给用户进行调整？

qw4990-PingCAP · 2023 年9 月 18 日 03:45

@人如其名感谢反馈，代价模型 IndexJoin 的选择确实是 “老生常谈” 的问题。
其实 model 2 考虑了回表代价，只是在 v2 里回表代价因子被重命名了，改成了 TiDBRequest:

然后 IndexJoin 的孩子的代价，会把这个回表代价考虑进去，比如这里的 IndexLookup_11；

不过后续我们也发现 IndexJoin 代价受到回表请求数量的影响比较大，但是回表请求的最终数量很难估算准确，比如在这个 case 中，25 行就造成了最终 48+155 超过 200 次的回表请求。
后续从 v6.6 开始我们引入了一个单独的变量，来允许条件对 IndexJoin 选择的倾向性：https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_index_join_double_read_penalty_cost_rate-从-v660-版本开始引入

人如其名 · 2023 年9 月 18 日 05:20

感谢大佬指正，学习了。
计算层算子并发度、存储层随机查和顺序扫读放大等都是影响成本评估的因素，对于分布式来讲成本评估确实更复杂了。

另外IndexHashJoin_14 内存占用很大406MB内存，但是在走HashJoin_30的时候内存占用才50.2 KB。主要原因是IndexHashJoin需要把符合左表“一小批数据”的匹配的右表记录都拿到tidb-server上来，但是因为一次性匹配符合条件的记录太多则导致内存占用太大，在实际生产中容易导致语句级OOM（IndexJoin不能落盘），是否应该考虑这点在一些场景下更倾向于走HashJoin呢？

人如其名 · 2023 年9 月 18 日 06:22

根据大佬的建议，设置这个参数后可以走想要的执行计划了，但是回表代价调整放大了50倍（tidb_index_join_double_read_penalty_cost_rate=50）才能走到想要的执行计划，在实际场景中应该不太敢设置，如果5倍以内的设置可以评估更准确，感觉还比较敢设置。


mysql> set tidb_index_join_double_read_penalty_cost_rate=50;
Query OK, 0 rows affected (0.00 sec)

mysql> explain  select  * from nation a  join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+-----------------------------+------------+-----------+---------------+--------------------------------------------------------------------------------+
| id                          | estRows    | task      | access object | operator info                                                                  |
+-----------------------------+------------+-----------+---------------+--------------------------------------------------------------------------------+
| HashJoin_30                 | 1500000.00 | root      |               | inner join, equal:[eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey)] |
| ├─TableReader_37(Build)     | 25.00      | root      |               | data:TableFullScan_36                                                          |
| │ └─TableFullScan_36        | 25.00      | cop[tikv] | table:a       | keep order:false, stats:pseudo                                                 |
| └─TableReader_39(Probe)     | 1500000.00 | root      |               | data:TableFullScan_38                                                          |
|   └─TableFullScan_38        | 1500000.00 | cop[tikv] | table:b       | keep order:false, stats:partial[idx2:missing]                                  |
+-----------------------------+------------+-----------+---------------+--------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

qw4990-PingCAP · 2023 年9 月 18 日 07:58

确实有这个问题，而且对代价上的调整可能会影响其他 query，所以实际操作中，除非出现了大面积的 query 选错，不然还是建议用 hint / binding 去解决。
如果发现了比较大面积的 IndexJoin 选错（比如因为一些数据分布原因造成），可以尝试用这个进行调整，不过调整的过程中也需要注意会不会引起其他回退。（总之这是没有办法的最后一步了）。

@人如其名另外能否将你的 schema 和统计信息用 plan replayer 导出一下，我们这边搜集作为一个 case，后续用于改进。

人如其名 · 2023 年9 月 18 日 08:59

replayer_D8MQbKVBQSEfTHN59IRl3Q==_1695027299225316789.zip|attachment (70.9 KB)

已上传，感谢。目前看是小表（小结果集）join 大表（大结果集）返回大结果集的情况下更应该倾向于走HashJoin，但这里走了IndexHashJoin。

另外，如下是基于统计信息和真实运行值评估的统计信息，看是否也有所帮助：

--基于统计信息cost值评估
mysql> explain analyze format='verbose' select /*+ hash_join(a,b) */ * from nation a join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+-----------------------------+------------+--------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| id                          | estRows    | estCost      | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                         | operator info                                                                  | memory  | disk    |
+-----------------------------+------------+--------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| HashJoin_30                 | 1500000.00 | 158260319.14 | 1500000 | root      |               | time:604.1ms, loops:1468, RU:4677.543332, build_hash_table:{total:845.3µs, fetch:832.7µs, build:12.6µs}, probe:{concurrency:5, total:3.02s, max:604.6ms, probe:1.04s, fetch:1.98s}                                                                                                                     | inner join, equal:[eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey)] | 66.0 KB | 0 Bytes |
| ├─TableReader_32(Build)     | 25.00      | 1588.68      | 25      | root      |               | time:816.1µs, loops:2, cop_task: {num: 1, max: 833.2µs, proc_keys: 25, tot_proc: 110.8µs, tot_wait: 188.7µs, rpc_num: 1, rpc_time: 798.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.4µs, max_distsql_concurrency: 1}                                                                       | data:TableFullScan_31                                                          | 3.05 KB | N/A     |
| │ └─TableFullScan_31        | 25.00      | 7495.23      | 25      | cop[tikv] | table:a       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 23.6µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}}                                                                                           | keep order:false, stats:pseudo                                                 | N/A     | N/A     |
| └─TableReader_34(Probe)     | 1500000.00 | 128313913.46 | 1500000 | root      |               | time:332.3ms, loops:1472, cop_task: {num: 88, max: 185.4ms, min: 388.7µs, avg: 40.9ms, p95: 153.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2.11s, tot_wait: 32.5ms, rpc_num: 88, rpc_time: 3.59s, copr_cache_hit_ratio: 0.61, build_task_duration: 14µs, max_distsql_concurrency: 7}   | data:TableFullScan_33                                                          | 45.0 MB | N/A     |
|   └─TableFullScan_33        | 1500000.00 | 483664701.94 | 1500000 | cop[tikv] | table:b       | tikv_task:{proc max:91ms, min:0s, avg: 23.5ms, p80:47ms, p95:66ms, iters:1811, tasks:88}, scan_detail: {total_process_keys: 1265952, total_process_keys_size: 257589695, total_keys: 1265986, get_snapshot_time: 4.82ms, rocksdb: {key_skipped_count: 1265952, block: {cache_hit_count: 8493}}}        | keep order:false, stats:partial[idx2:missing]                                  | N/A     | N/A     |
+-----------------------------+------------+--------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
5 rows in set, 1 warning (0.60 sec)


mysql> explain analyze format='verbose' select /*+ inl_hash_join(a,b) */ * from nation a join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+----------------------------------+------------+--------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                               | estRows    | estCost      | actRows | task      | access object                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | operator info                                                                                                                                                                       | memory   | disk |
+----------------------------------+------------+--------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_14                 | 1500000.00 | 98202514.61  | 1500000 | root      |                                  | time:3.3s, loops:1466, RU:9913.581754, inner:{total:3.3s, concurrency:5, task:1, construct:11.8µs, fetch:2.71s, build:8.2µs, join:595ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | inner join, inner:IndexLookUp_11, outer key:tpch10.nation.n_nationkey, inner key:tpch10.customer.c_nationkey, equal cond:eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey) | 407.2 MB | N/A  |
| ├─TableReader_30(Build)          | 25.00      | 1588.68      | 25      | root      |                                  | time:754µs, loops:3, cop_task: {num: 1, max: 816.6µs, proc_keys: 25, tot_proc: 98.8µs, tot_wait: 104µs, rpc_num: 1, rpc_time: 794.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.35µs, max_distsql_concurrency: 1}                                                                                                                                                                                                                                                                                                                                                                               | data:TableFullScan_29                                                                                                                                                               | 3.05 KB  | N/A  |
| │ └─TableFullScan_29             | 25.00      | 7495.23      | 25      | cop[tikv] | table:a                          | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 23.4µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}}                                                                                                                                                                                                                                                                                                                                                                                              | keep order:false, stats:pseudo                                                                                                                                                      | N/A      | N/A  |
| └─IndexLookUp_11(Probe)          | 1500000.00 | 117823847.31 | 1500000 | root      |                                  | time:2.68s, loops:1466, index_task: {total_time: 2.61s, fetch_handle: 142.5ms, build: 270.4µs, wait: 2.47s}, table_task: {total_time: 13.2s, num: 77, concurrency: 5}, next: {wait_index: 2.64ms, wait_table_lookup_build: 318.9µs, wait_table_lookup_resp: 1.35s}                                                                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                     | 35.7 MB  | N/A  |
|   ├─IndexRangeScan_9(Build)      | 1500000.00 | 12210000.00  | 1500000 | cop[tikv] | table:b, index:idx2(C_NATIONKEY) | time:42.9ms, loops:1476, cop_task: {num: 63, max: 143.8ms, min: 252.6µs, avg: 23.9ms, p95: 122.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 532.5ms, tot_wait: 22.7ms, rpc_num: 63, rpc_time: 1.5s, copr_cache_hit_ratio: 0.57, build_task_duration: 27.7µs, max_distsql_concurrency: 6}, tikv_task:{proc max:113ms, min:0s, avg: 28.2ms, p80:64ms, p95:83ms, iters:1707, tasks:63}, scan_detail: {total_process_keys: 485824, total_process_keys_size: 22347904, total_keys: 485859, get_snapshot_time: 1.21ms, rocksdb: {key_skipped_count: 1457472, block: {cache_hit_count: 1911}}}     | range: decided by [eq(tpch10.customer.c_nationkey, tpch10.nation.n_nationkey)], keep order:false, stats:partial[idx2:missing]                                                       | N/A      | N/A  |
|   └─TableRowIDScan_10(Probe)     | 1500000.00 | 19346588.08  | 1500000 | cop[tikv] | table:b                          | time:12s, loops:1657, cop_task: {num: 280, max: 261.3ms, min: 3.16ms, avg: 106.5ms, p95: 194ms, max_proc_keys: 15718, p95_proc_keys: 9045, tot_proc: 13.7s, tot_wait: 208.3ms, rpc_num: 280, rpc_time: 29.8s, copr_cache_hit_ratio: 0.00, build_task_duration: 228.8ms, max_distsql_concurrency: 6}, tikv_task:{proc max:178ms, min:0s, avg: 65.1ms, p80:110ms, p95:141ms, iters:2719, tasks:280}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1559447, get_snapshot_time: 20.6ms, rocksdb: {key_skipped_count: 121554, block: {cache_hit_count: 378675}}} | keep order:false, stats:partial[idx2:missing]                                                                                                                                       | N/A      | N/A  |
+----------------------------------+------------+--------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set, 1 warning (3.30 sec)




--基于真实结果cost值评估
mysql> explain analyze format='true_card_cost' select /*+ hash_join(a,b) */ * from nation a join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+-----------------------------+------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| id                          | estRows    | estCost      | costFormula                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                           | operator info                                                                  | memory  | disk    |
+-----------------------------+------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| HashJoin_30                 | 1500000.00 | 158260319.14 | (cpu(10*3*tidb_cpu_factor(49.9))) + (((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(1.5e+06*logrowsize(242.6)*tikv_scan_factor(40.7))) + (net(1.5e+06*rowsize(242.6)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashmem(25*165*tidb_mem_factor(0.2))) + (hashbuild(25*tidb_cpu_factor(49.9)))) + (cpu(25*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(1.5e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(1.5e+06*1*tidb_cpu_factor(49.9))) + (hashprobe(1.5e+06*tidb_cpu_factor(49.9)))))/5.00) | 1500000 | root      |               | time:673.2ms, loops:1469, RU:4798.995745, build_hash_table:{total:1.21ms, fetch:1.2ms, build:18.2µs}, probe:{concurrency:5, total:3.37s, max:673.5ms, probe:965.1ms, fetch:2.4s}                                                                                                                         | inner join, equal:[eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey)] | 50.2 KB | 0 Bytes |
| ├─TableReader_32(Build)     | 25.00      | 1588.68      | ((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 25      | root      |               | time:1.08ms, loops:2, cop_task: {num: 1, max: 1.39ms, proc_keys: 25, tot_proc: 113.1µs, tot_wait: 506.7µs, rpc_num: 1, rpc_time: 1.2ms, copr_cache_hit_ratio: 0.00, build_task_duration: 10.4µs, max_distsql_concurrency: 1}                                                                             | data:TableFullScan_31                                                          | 3.05 KB | N/A     |
| │ └─TableFullScan_31        | 25.00      | 7495.23      | scan(25*logrowsize(165)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 25      | cop[tikv] | table:a       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 29.8µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}}                                                                                             | keep order:false, stats:pseudo                                                 | N/A     | N/A     |
| └─TableReader_34(Probe)     | 1500000.00 | 128313913.46 | ((scan(1.5e+06*logrowsize(242.6)*tikv_scan_factor(40.7))) + (net(1.5e+06*rowsize(242.6)*tidb_kv_net_factor(3.96))))/15.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 1500000 | root      |               | time:409.2ms, loops:1472, cop_task: {num: 88, max: 287.8ms, min: 586.6µs, avg: 45.9ms, p95: 194.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2.48s, tot_wait: 22.6ms, rpc_num: 88, rpc_time: 4.04s, copr_cache_hit_ratio: 0.61, build_task_duration: 11.4µs, max_distsql_concurrency: 7}   | data:TableFullScan_33                                                          | 39.2 MB | N/A     |
|   └─TableFullScan_33        | 1500000.00 | 483664701.94 | scan(1.5e+06*logrowsize(242.6)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | 1500000 | cop[tikv] | table:b       | tikv_task:{proc max:126ms, min:1ms, avg: 25.8ms, p80:47ms, p95:77ms, iters:1811, tasks:88}, scan_detail: {total_process_keys: 1265952, total_process_keys_size: 257589695, total_keys: 1265986, get_snapshot_time: 1.45ms, rocksdb: {key_skipped_count: 1265952, block: {cache_hit_count: 8493}}}        | keep order:false, stats:partial[idx2:missing]                                  | N/A     | N/A     |
+-----------------------------+------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
5 rows in set, 3 warnings (0.68 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | cost formula: (cpu(10*3*tidb_cpu_factor(49.9))) + (((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00) + (((scan(1.5e+06*logrowsize(242.6)*tikv_scan_factor(40.7))) + (net(1.5e+06*rowsize(242.6)*tidb_kv_net_factor(3.96))))/15.00) + ((hashkey(25*1*tidb_cpu_factor(49.9))) + (hashmem(25*165*tidb_mem_factor(0.2))) + (hashbuild(25*tidb_cpu_factor(49.9)))) + (cpu(25*filters(0)*tidb_cpu_factor(49.9))) + (((cpu(1.5e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(1.5e+06*1*tidb_cpu_factor(49.9))) + (hashprobe(1.5e+06*tidb_cpu_factor(49.9)))))/5.00) |
| Warning | 1105 | factor costs: {"tidb_cpu_factor":29943992,"tidb_kv_net_factor":96070689,"tidb_mem_factor":825,"tikv_scan_factor":32244813.144939117}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Warning | 1105 | factor weights: {"tidb_cpu_factor":600080,"tidb_kv_net_factor":24260275,"tidb_mem_factor":4125,"tikv_scan_factor":792255.8512270052}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


mysql> explain analyze format='true_card_cost' select /*+ inl_hash_join(a,b) */ * from nation a join customer b on a.N_NATIONKEY=b.C_NATIONKEY;
+----------------------------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                               | estRows    | estCost      | costFormula                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | actRows | task      | access object                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                       | memory   | disk |
+----------------------------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_14                 | 1500000.00 | 98202514.61  | (cpu(10*3*tidb_cpu_factor(49.9))) + (((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(25*filters(0)*tidb_cpu_factor(49.9))) + (cpu(25*10*tidb_cpu_factor(49.9))) + ((() + ((((((net(60000*rowsize(16.25)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(32)*tikv_scan_factor(40.7))))/15.00) + (((((net(60000*rowsize(219.60000000000002)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(242.6)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(60000*tidb_cpu_factor(49.9))) + (doubleRead(tasks(96)*tidb_request_factor(6e+06)))))/5.00))*25.00)/6.00) + (cpu(1.5e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*0*tidb_cpu_factor(49.9))) + (hashmem(25*165*tidb_mem_factor(0.2))) + (hashbuild(25*tidb_cpu_factor(49.9)))))/5.00) | 1500000 | root      |                                  | time:3.13s, loops:1466, RU:9641.506148, inner:{total:3.13s, concurrency:5, task:1, construct:15.5µs, fetch:2.64s, build:8.81µs, join:488.4ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | inner join, inner:IndexLookUp_11, outer key:tpch10.nation.n_nationkey, inner key:tpch10.customer.c_nationkey, equal cond:eq(tpch10.nation.n_nationkey, tpch10.customer.c_nationkey) | 407.4 MB | N/A  |
| ├─TableReader_30(Build)          | 25.00      | 1588.68      | ((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 25      | root      |                                  | time:953.6µs, loops:3, cop_task: {num: 1, max: 877.9µs, proc_keys: 25, tot_proc: 109.6µs, tot_wait: 97.5µs, rpc_num: 1, rpc_time: 839.4µs, copr_cache_hit_ratio: 0.00, build_task_duration: 12.5µs, max_distsql_concurrency: 1}                                                                                                                                                                                                                                                                                                                                                                                                         | data:TableFullScan_29                                                                                                                                                               | 3.05 KB  | N/A  |
| │ └─TableFullScan_29             | 25.00      | 7495.23      | scan(25*logrowsize(165)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 25      | cop[tikv] | table:a                          | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 25.6µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}}                                                                                                                                                                                                                                                                                                                                                                                                                            | keep order:false, stats:pseudo                                                                                                                                                      | N/A      | N/A  |
| └─IndexLookUp_11(Probe)          | 1500000.00 | 117823847.31 | (((net(60000*rowsize(16.25)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(32)*tikv_scan_factor(40.7))))/15.00) + (((((net(60000*rowsize(219.60000000000002)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(242.6)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(60000*tidb_cpu_factor(49.9))) + (doubleRead(tasks(96)*tidb_request_factor(6e+06)))))/5.00)                                                                                                                                                                                                                                                                                                                                                                                                                                                   | 1500000 | root      |                                  | time:2.61s, loops:1466, index_task: {total_time: 2.52s, fetch_handle: 150ms, build: 228.3µs, wait: 2.37s}, table_task: {total_time: 12.8s, num: 77, concurrency: 5}, next: {wait_index: 19.5ms, wait_table_lookup_build: 192.7µs, wait_table_lookup_resp: 1.48s}                                                                                                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                     | 38.7 MB  | N/A  |
|   ├─IndexRangeScan_9(Build)      | 1500000.00 | 12210000.00  | scan(60000*logrowsize(32)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 1500000 | cop[tikv] | table:b, index:idx2(C_NATIONKEY) | time:35.8ms, loops:1476, cop_task: {num: 63, max: 175ms, min: 280.4µs, avg: 24.2ms, p95: 115.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 397.3ms, tot_wait: 55.6ms, rpc_num: 63, rpc_time: 1.52s, copr_cache_hit_ratio: 0.56, build_task_duration: 35.5µs, max_distsql_concurrency: 6}, tikv_task:{proc max:97ms, min:0s, avg: 27.6ms, p80:63ms, p95:77ms, iters:1707, tasks:63}, scan_detail: {total_process_keys: 535968, total_process_keys_size: 24654528, total_keys: 536005, get_snapshot_time: 1.01ms, rocksdb: {key_skipped_count: 1607904, block: {cache_hit_count: 2105}}}                                     | range: decided by [eq(tpch10.customer.c_nationkey, tpch10.nation.n_nationkey)], keep order:false, stats:partial[idx2:missing]                                                       | N/A      | N/A  |
|   └─TableRowIDScan_10(Probe)     | 1500000.00 | 19346588.08  | scan(60000*logrowsize(242.6)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 1500000 | cop[tikv] | table:b                          | time:11.9s, loops:1664, cop_task: {num: 276, max: 286.2ms, min: 960µs, avg: 103.2ms, p95: 192.3ms, max_proc_keys: 13960, p95_proc_keys: 9099, tot_proc: 12.9s, tot_wait: 188.6ms, rpc_num: 276, rpc_time: 28.4s, copr_cache_hit_ratio: 0.00, build_task_duration: 135.1ms, max_distsql_concurrency: 5, max_extra_concurrency: 1}, tikv_task:{proc max:227ms, min:0s, avg: 64.1ms, p80:108ms, p95:140ms, iters:2696, tasks:276}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1559018, get_snapshot_time: 13.4ms, rocksdb: {key_skipped_count: 120663, block: {cache_hit_count: 379095}}}  | keep order:false, stats:partial[idx2:missing]                                                                                                                                       | N/A      | N/A  |
+----------------------------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set, 3 warnings (3.13 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | cost formula: (cpu(10*3*tidb_cpu_factor(49.9))) + (((scan(25*logrowsize(165)*tikv_scan_factor(40.7))) + (net(25*rowsize(165)*tidb_kv_net_factor(3.96))))/15.00) + (cpu(25*filters(0)*tidb_cpu_factor(49.9))) + (cpu(25*10*tidb_cpu_factor(49.9))) + ((() + ((((((net(60000*rowsize(16.25)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(32)*tikv_scan_factor(40.7))))/15.00) + (((((net(60000*rowsize(219.60000000000002)*tidb_kv_net_factor(3.96))) + (scan(60000*logrowsize(242.6)*tikv_scan_factor(40.7))))/15.00) + ((double-read-cpu(60000*tidb_cpu_factor(49.9))) + (doubleRead(tasks(96)*tidb_request_factor(6e+06)))))/5.00))*25.00)/6.00) + (cpu(1.5e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(25*0*tidb_cpu_factor(49.9))) + (hashmem(25*165*tidb_mem_factor(0.2))) + (hashbuild(25*tidb_cpu_factor(49.9)))))/5.00) |
| Warning | 1105 | factor costs: {"tidb_cpu_factor":513221.5,"tidb_kv_net_factor":795333,"tidb_mem_factor":165,"tidb_request_factor":96000000,"tikv_scan_factor":893795.1052751924}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| Warning | 1105 | factor weights: {"tidb_cpu_factor":10285,"tidb_kv_net_factor":200841.66666666666,"tidb_mem_factor":825,"tidb_request_factor":16,"tikv_scan_factor":21960.567697179173}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

qw4990-PingCAP · 2023 年9 月 18 日 09:42

OK 感谢，我稍后用给你这个 case 复现一下，建一个 github issue

system · 2024 年6 月 3 日 09:45

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。