业务场景
TiDB v7.5.6 版本集群，业务 SQL SELECT * FROM `job_queue` AS `t` 会出现较长的执行时间，同时高并发的执行会对 tikv 造成较大的压力。该表在业务流程中用于接口记录的持久化，使用方式类似队列，job_queue 表内持续插入新的任务记录，等任务流程完成后，删除表内对应的任务记录，每日共会记录约几十万任务记录。执行计划中可以看出 actRows 和total_process_keys 都是 32，但是 total_keys 有 420609，比例相差悬殊，说明多版本数据量比例非常大。
| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
| Projection_3 | 182.00 | 10143.49 | 32 | root | | time:785.5ms, loops:2, Concurrency:OFF | 列名脱敏 | 26.1 KB | N/A |
| └─TableReader_5 | 182.00 | 10034.51 | 32 | root | | time:785.4ms, loops:2, cop_task: {num: 1, max: 785.2ms, proc_keys: 32, tot_proc: 168.7ms, tot_wait: 440.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 8.47µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:785.2ms}} | data:TableFullScan_4 | 2.08 KB | N/A |
| └─TableFullScan_4 | 182.00 | 52499.67 | 32 | cop[tikv] | table:t | tikv_task:{time:780ms, loops:2}, scan_detail: {total_process_keys: 32, total_process_keys_size: 2372, total_keys: 420609, get_snapshot_time: 21.6µs, rocksdb: {delete_skipped_count: 181, key_skipped_count: 420789, block: {cache_hit_count: 740}}} | keep order:false, stats:pseudo | N/A | N/A |
性能不佳的原因
TiDB 的 MVCC 多版本数据存储实现机制，在 Key 上会标识数据版本。
-- TiDB 数据存储结构示例
Key: table_id{row_id}_timestamp
Value: column_data + transaction_info
-- 实际存储格式：
-- Key: t{123}_r1_v5 (table 123, row 1, version 5)
-- Value: {name: "John", age: 25, start_ts: 5, commit_ts: 10}
集群通过 gc_life_time 配置所有历史数据版本的保留周期，TiDB 的后台 Compaction 机制再定期清理过期旧版本数据。在本项目的场景中，数据记录被删除时，也仅是增加一份删除标识的版本，所以全表扫描时会访问所有在 GC 时间（生产上配置为 26 小时）内的MVCC 多版本数据，相当于访问当前的有效数据和已经删除的无效版本数据（过去 26 小时内的所有记录数据），导致访问压力过大。
做为对比， MySQL 是基于 InnoDB 的 undo log 实现 MVCC，原数据位置只保留新版本的数据，原版本迁移到 UNDO 区，通过版本链接到 UNDO 区，所以 MySQL 上不会出现全表查询时需要访问到过期数据的行为。
升级到 8.5.3 版本启用 IME 功能
TiKV MVCC 内存引擎 (In-Memory Engine, IME) 主要用于加速需要扫描大量 MVCC 历史版本的查询，即查询扫描的总共版本数量 (total_keys) 远大于处理的版本数量 (processed_keys)。内存引擎加速扫描了大量 MVCC 版本的读请求。
TiKV MVCC 内存引擎适用于以下场景：
- 业务需要查询频繁更新或删除的记录。
- 业务需要调整 tidb_gc_life_time，使 TiDB 保留较长时间的历史版本（比如 24 小时）。
开启内存引擎之后，TiKV 会根据 Region 的读流量和 MVCC 放大程度，选择要自动加载的 Region。具体流程如下：
- Region 按照最近时间段的 next (RocksDB Iterator next API) 和 prev (RocksDB Iterator prev API) 次数进行排序。
- 使用 mvcc-amplification-threshold 配置项对 Region 进行过滤，该配置项的默认值为 10。MVCC amplification 衡量读放大程度，计算公式为 (next + prev) / processed_keys）。
- 载入前 N 个 MVCC 放大严重的 Region，其中 N 基于内存估算而来。
从机制说明上可以看出，IME 特性不仅适用于单行记录不断被 update 的多版本的场景，如热销商品的库存扣减等，还适用于 Region 内有效 keys 数据非常少的场景，类似本例中表内数据不断被删除但是需要全表查询的队列场景。
IME 本地模拟测试
测试过程
进程一：模拟循环写入和删除过程
for i in {1..100}
do
$dbcmd 2>/dev/null -e "insert into db1.t1(c2,c3,c4) select c2+FLOOR(1 + RAND() * 10000),c3,c4 from db1.tt;";
done
echo "Job $nm insert data complete."
$dbcmd 2>/dev/null -e "delete from db1.t1;"
echo "Job $nm clear data complete."
进程二：模拟查询过程
TiKV 启用 IME 的查询计划
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 9200 | root | | time:90.1ms, loops:11, RU:40.843020, cop_task: {num: 11, max: 76ms, min: 608.5µs, avg: 8.81ms, p95: 76ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 80ms, tot_wait: 924µs, copr_cache_hit_ratio: 0.45, build_task_duration: 31.4µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:11, total_time:96.6ms}} | data:TableFullScan_4 | 278.9 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 9200 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:0s, avg: 354.9ms, p80:830ms, p95:872ms, iters:37, tasks:11}, scan_detail: {total_process_keys: 9200, total_process_keys_size: 587260, total_keys: 477434, get_snapshot_time: 280.8µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 80ms, total_suspend_time: 24.5µs, total_wait_time: 924µs, total_kv_read_wall_time: 79ms, tikv_wall_time: 83.7ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.092 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 2700 | root | | time:57.2ms, loops:4, RU:24.485293, cop_task: {num: 9, max: 51.6ms, min: 720.9µs, avg: 7.03ms, p95: 51.6ms, max_proc_keys: 1004, p95_proc_keys: 1004, tot_proc: 52.7ms, tot_wait: 721.7µs, copr_cache_hit_ratio: 0.56, build_task_duration: 26.5µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:9, total_time:63.1ms}} | data:TableFullScan_4 | 101.9 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 2700 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:1ms, avg: 431ms, p80:858ms, p95:872ms, iters:23, tasks:9}, scan_detail: {total_process_keys: 2700, total_process_keys_size: 172343, total_keys: 123004, get_snapshot_time: 240.5µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 52.7ms, total_suspend_time: 15.9µs, total_wait_time: 721.7µs, total_kv_read_wall_time: 54ms, tikv_wall_time: 56.3ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.059 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 3600 | root | | time:103.3ms, loops:5, RU:40.630365, cop_task: {num: 9, max: 96.2ms, min: 664.2µs, avg: 11.9ms, p95: 96.2ms, max_proc_keys: 1904, p95_proc_keys: 1904, tot_proc: 98.5ms, tot_wait: 1.19ms, copr_cache_hit_ratio: 0.56, build_task_duration: 28µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:9, total_time:107.3ms}} | data:TableFullScan_4 | 147.5 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 3600 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:0s, avg: 435.8ms, p80:858ms, p95:872ms, iters:23, tasks:9}, scan_detail: {total_process_keys: 3600, total_process_keys_size: 229794, total_keys: 144104, get_snapshot_time: 264.7µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 98.5ms, total_suspend_time: 27.2µs, total_wait_time: 1.19ms, total_kv_read_wall_time: 97ms, tikv_wall_time: 101.3ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.105 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 9600 | root | | time:42.9ms, loops:11, RU:25.976801, cop_task: {num: 11, max: 26.5ms, min: 1.07ms, avg: 4.41ms, p95: 26.5ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 34.2ms, tot_wait: 1.39ms, copr_cache_hit_ratio: 0.45, build_task_duration: 30.6µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:11, total_time:48.2ms}} | data:TableFullScan_4 | 299.1 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 9600 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:0s, avg: 350.6ms, p80:830ms, p95:872ms, iters:37, tasks:11}, scan_detail: {total_process_keys: 9600, total_process_keys_size: 612781, total_keys: 149806, get_snapshot_time: 424.9µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 34.2ms, total_suspend_time: 64.4µs, total_wait_time: 1.39ms, total_kv_read_wall_time: 32ms, tikv_wall_time: 38.1ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.046 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 5300 | root | | time:34.9ms, loops:7, RU:19.597421, cop_task: {num: 10, max: 26.2ms, min: 714.8µs, avg: 4.03ms, p95: 26.2ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 29.1ms, tot_wait: 1.45ms, copr_cache_hit_ratio: 0.50, build_task_duration: 34.7µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:10, total_time:40.1ms}} | data:TableFullScan_4 | 183.4 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 5300 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:0s, avg: 385.2ms, p80:858ms, p95:872ms, iters:29, tasks:10}, scan_detail: {total_process_keys: 5300, total_process_keys_size: 338309, total_keys: 165505, get_snapshot_time: 192.1µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 29.1ms, total_suspend_time: 39.4µs, total_wait_time: 1.45ms, total_kv_read_wall_time: 27ms, tikv_wall_time: 32.9ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.036 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 183864.00 | 7600 | root | | time:46.5ms, loops:9, RU:24.679955, cop_task: {num: 11, max: 30.8ms, min: 528.7µs, avg: 4.03ms, p95: 30.8ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 34.7ms, tot_wait: 602µs, copr_cache_hit_ratio: 0.45, build_task_duration: 34.4µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:12, total_time:44.5ms},ResolveLock:{num_rpc:1, total_time:5.06ms}} | data:TableFullScan_4 | 257.0 KB | N/A |
| └─TableFullScan_4 | 183864.00 | 7600 | cop[tikv] | table:t1 | tikv_task:{proc max:872ms, min:0s, avg: 350.8ms, p80:830ms, p95:872ms, iters:35, tasks:11}, scan_detail: {total_process_keys: 7600, total_process_keys_size: 485136, total_keys: 187806, get_snapshot_time: 231.4µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 34.7ms, total_suspend_time: 22.2µs, total_wait_time: 602µs, total_kv_read_wall_time: 34ms, tikv_wall_time: 36.8ms} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.048 sec)
TiKV 关闭 IME 的查询计划
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 10000 | root | | time:1.26s, loops:12, RU:1818.446342, cop_task: {num: 13, max: 1.26s, min: 2.21ms, avg: 512.5ms, p95: 1.26s, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 5.41s, tot_wait: 1.22s, copr_cache_hit_ratio: 0.00, build_task_duration: 25.5µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:13, total_time:6.66s}} | data:TableFullScan_4 | 319.4 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 10000 | cop[tikv] | table:t1 | tikv_task:{proc max:890ms, min:0s, avg: 415.9ms, p80:749ms, p95:890ms, iters:40, tasks:13}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 638330, total_keys: 19410809, get_snapshot_time: 393.4µs, rocksdb: {delete_skipped_count: 15200, key_skipped_count: 19425996, block: {cache_hit_count: 31728}}}, time_detail: {total_process_time: 5.41s, total_suspend_time: 1.88ms, total_wait_time: 1.22s, total_kv_read_wall_time: 5.41s, tikv_wall_time: 6.63s} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (1.258 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 5200 | root | | time:714.1ms, loops:7, RU:379.670041, cop_task: {num: 12, max: 713.7ms, min: 1.06ms, avg: 93.6ms, p95: 713.7ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 1.11s, tot_wait: 2.65ms, copr_cache_hit_ratio: 0.50, build_task_duration: 22.4µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:12, total_time:1.12s}} | data:TableFullScan_4 | 178.3 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 5200 | cop[tikv] | table:t1 | tikv_task:{proc max:890ms, min:0s, avg: 449.3ms, p80:746ms, p95:890ms, iters:31, tasks:12}, scan_detail: {total_process_keys: 5200, total_process_keys_size: 331931, total_keys: 4088202, get_snapshot_time: 441µs, rocksdb: {delete_skipped_count: 35200, key_skipped_count: 4123396, block: {cache_hit_count: 6554}}}, time_detail: {total_process_time: 1.11s, total_suspend_time: 12.7µs, total_wait_time: 2.65ms, total_kv_read_wall_time: 1.11s, tikv_wall_time: 1.11s} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.717 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 6500 | root | | time:395.1ms, loops:8, RU:141.154363, cop_task: {num: 12, max: 384ms, min: 1.1ms, avg: 33.8ms, p95: 384ms, max_proc_keys: 2788, p95_proc_keys: 2788, tot_proc: 387.4ms, tot_wait: 1.99ms, copr_cache_hit_ratio: 0.58, build_task_duration: 34.5µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:12, total_time:405.7ms}} | data:TableFullScan_4 | 244.2 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 6500 | cop[tikv] | table:t1 | tikv_task:{proc max:890ms, min:0s, avg: 448.6ms, p80:746ms, p95:890ms, iters:32, tasks:12}, scan_detail: {total_process_keys: 6500, total_process_keys_size: 414910, total_keys: 1569501, get_snapshot_time: 302.1µs, rocksdb: {delete_skipped_count: 55200, key_skipped_count: 1624696, block: {cache_hit_count: 2341}}}, time_detail: {total_process_time: 387.4ms, total_suspend_time: 26.6µs, total_wait_time: 1.99ms, total_kv_read_wall_time: 385ms, tikv_wall_time: 394ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.399 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 9000 | root | | time:1.06s, loops:11, RU:856.101323, cop_task: {num: 13, max: 1.06s, min: 582.1µs, avg: 195.3ms, p95: 1.06s, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 2.52s, tot_wait: 2.56ms, copr_cache_hit_ratio: 0.38, build_task_duration: 34.6µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:13, total_time:2.54s}} | data:TableFullScan_4 | 268.7 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 9000 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:0s, avg: 449.8ms, p80:746ms, p95:1.06s, iters:39, tasks:13}, scan_detail: {total_process_keys: 9000, total_process_keys_size: 574487, total_keys: 6710804, get_snapshot_time: 362.7µs, rocksdb: {delete_skipped_count: 65200, key_skipped_count: 6775996, block: {cache_hit_count: 2346, read_count: 8419, read_byte: 24.8 MB, read_time: 89.9ms}}}, time_detail: {total_process_time: 2.52s, total_suspend_time: 559.6µs, total_wait_time: 2.56ms, total_kv_read_wall_time: 2.52s, tikv_wall_time: 2.53s} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (1.066 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 8100 | root | | time:414.3ms, loops:10, RU:147.802642, cop_task: {num: 13, max: 395.8ms, min: 653µs, avg: 32.4ms, p95: 395.8ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 401.2ms, tot_wait: 1.32ms, copr_cache_hit_ratio: 0.54, build_task_duration: 29.9µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:13, total_time:421.4ms}} | data:TableFullScan_4 | 257.0 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 8100 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:0s, avg: 449.6ms, p80:746ms, p95:1.06s, iters:38, tasks:13}, scan_detail: {total_process_keys: 8100, total_process_keys_size: 517029, total_keys: 1610802, get_snapshot_time: 466.6µs, rocksdb: {delete_skipped_count: 75200, key_skipped_count: 1685996, block: {cache_hit_count: 2343}}}, time_detail: {total_process_time: 401.2ms, total_suspend_time: 316.5µs, total_wait_time: 1.32ms, total_kv_read_wall_time: 400ms, tikv_wall_time: 405.7ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.416 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 7600 | root | | time:438.6ms, loops:9, RU:155.607615, cop_task: {num: 13, max: 418.9ms, min: 1.14ms, avg: 34.2ms, p95: 418.9ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 424.7ms, tot_wait: 2.55ms, copr_cache_hit_ratio: 0.54, build_task_duration: 35.1µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:14, total_time:446.3ms},ResolveLock:{num_rpc:1, total_time:2.37ms}} | data:TableFullScan_4 | 257.0 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 7600 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:0s, avg: 451.4ms, p80:746ms, p95:1.06s, iters:37, tasks:13}, scan_detail: {total_process_keys: 7600, total_process_keys_size: 485121, total_keys: 1630602, get_snapshot_time: 407.5µs, rocksdb: {delete_skipped_count: 85200, key_skipped_count: 1715796, block: {cache_hit_count: 2343}}}, time_detail: {total_process_time: 424.7ms, total_suspend_time: 34.1µs, total_wait_time: 2.55ms, total_kv_read_wall_time: 423ms, tikv_wall_time: 430.4ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.441 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 3400 | root | | time:427.4ms, loops:5, RU:148.626396, cop_task: {num: 11, max: 418.4ms, min: 699.4µs, avg: 39.7ms, p95: 418.4ms, max_proc_keys: 1704, p95_proc_keys: 1704, tot_proc: 420.3ms, tot_wait: 1.74ms, copr_cache_hit_ratio: 0.64, build_task_duration: 47.6µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:11, total_time:436.8ms}} | data:TableFullScan_4 | 137.4 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 3400 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:0s, avg: 533.1ms, p80:746ms, p95:1.06s, iters:25, tasks:11}, scan_detail: {total_process_keys: 3400, total_process_keys_size: 217031, total_keys: 1646500, get_snapshot_time: 356.5µs, rocksdb: {delete_skipped_count: 95200, key_skipped_count: 1741696, block: {cache_hit_count: 2339}}}, time_detail: {total_process_time: 420.3ms, total_suspend_time: 24.1µs, total_wait_time: 1.74ms, total_kv_read_wall_time: 419ms, tikv_wall_time: 425.3ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.432 sec)
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 10000 | root | | time:535.2ms, loops:12, RU:191.412069, cop_task: {num: 13, max: 509ms, min: 1.04ms, avg: 66.4ms, p95: 509ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 526.5ms, tot_wait: 313.1ms, copr_cache_hit_ratio: 0.54, build_task_duration: 43.4µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:13, total_time:863.2ms}} | data:TableFullScan_4 | 319.4 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 10000 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:2ms, avg: 459.3ms, p80:746ms, p95:1.06s, iters:40, tasks:13}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 638325, total_keys: 1660878, get_snapshot_time: 335µs, rocksdb: {delete_skipped_count: 132112, key_skipped_count: 1792984, block: {cache_hit_count: 2343}}}, time_detail: {total_process_time: 526.5ms, total_suspend_time: 95.6µs, total_wait_time: 313.1ms, total_kv_read_wall_time: 526ms, tikv_wall_time: 851.9ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.537 sec)
MySQL [db1]>
MySQL [db1]>
MySQL [db1]> explain analyze select * from t1;
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 5200.00 | 6100 | root | | time:446.8ms, loops:8, RU:156.705616, cop_task: {num: 12, max: 428.8ms, min: 845.2µs, avg: 37.7ms, p95: 428.8ms, max_proc_keys: 2388, p95_proc_keys: 2388, tot_proc: 433.8ms, tot_wait: 1.37ms, copr_cache_hit_ratio: 0.58, build_task_duration: 24.4µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:13, total_time:452.6ms},ResolveLock:{num_rpc:1, total_time:1.25ms}} | data:TableFullScan_4 | 223.9 KB | N/A |
| └─TableFullScan_4 | 5200.00 | 6100 | cop[tikv] | table:t1 | tikv_task:{proc max:1.06s, min:1ms, avg: 489.8ms, p80:746ms, p95:1.06s, iters:32, tasks:12}, scan_detail: {total_process_keys: 6100, total_process_keys_size: 389390, total_keys: 1669101, get_snapshot_time: 395.8µs, rocksdb: {delete_skipped_count: 105200, key_skipped_count: 1774296, block: {cache_hit_count: 2341}}}, time_detail: {total_process_time: 433.8ms, total_suspend_time: 37.9µs, total_wait_time: 1.37ms, total_kv_read_wall_time: 433ms, tikv_wall_time: 437.8ms} | keep order:false | N/A | N/A |
+-----------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.449 sec)
测试结论
IME 开启时的性能表现
- 查询时间范围：28ms - 103ms
- 平均查询时间：约50-60ms
- scan_detail 显示较少的 total_keys（10万-18万级别)
IME 关闭后的性能表现
-
查询时间范围：395ms - 1.26s
-
平均查询时间：约500-700ms
-
scan_detail 显示较多的 total_keys（大部分情况下需要扫描 160 万以上）， rocksdb 中 key_skipped_count 也较大。
关键差异对比
指标 |
IME开启 |
IME关闭 |
性能提升 |
|---|---|---|---|
平均查询时间 |
～50ms |
～600ms |
12倍 |
扫描键数量 |
10～18万 |
190～420万 |
减少20～30倍 |
删除键跳过 |
无 |
1.5～16万 |
显著减少 |
对比开启IME和关闭IME的SQL执行结果，相同查询SQL的执行耗时看，开启IME后SQL执行耗时远低于关闭IME后的SQL执行耗时。