Sharing Efficiency Test of Partitioned Table Scanning

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 分区表扫描效率测试分享

| username: Brian

Cluster version 6.5.6, partition table with 20 partitions, each partition evenly distributed with 10,000 rows of data;

Example Table Structure

CREATE TABLE `part` (
  `id` bigint(20) NOT NULL,
  `id2` int(110) NOT NULL,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`id2`) /*T![clustered_index] CLUSTERED */,
  KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`id2`)
(PARTITION `p0` VALUES LESS THAN (10000),
 PARTITION `p1` VALUES LESS THAN (20000),
 PARTITION `p2` VALUES LESS THAN (30000),
 PARTITION `p3` VALUES LESS THAN (40000),
 PARTITION `p4` VALUES LESS THAN (50000),
 PARTITION `p5` VALUES LESS THAN (60000),
 PARTITION `p6` VALUES LESS THAN (70000),
 PARTITION `p7` VALUES LESS THAN (80000),
 PARTITION `p8` VALUES LESS THAN (90000),
 PARTITION `p9` VALUES LESS THAN (100000),
 PARTITION `p10` VALUES LESS THAN (110000),
 PARTITION `p11` VALUES LESS THAN (120000),
 PARTITION `p12` VALUES LESS THAN (130000),
 PARTITION `p13` VALUES LESS THAN (140000),
 PARTITION `p14` VALUES LESS THAN (150000),
 PARTITION `p15` VALUES LESS THAN (160000),
 PARTITION `p16` VALUES LESS THAN (170000),
 PARTITION `p17` VALUES LESS THAN (180000),
 PARTITION `p18` VALUES LESS THAN (190000),
 PARTITION `p19` VALUES LESS THAN (200000)) 

Example Test SQL

mysql> explain analyze select * from part where id2<169696;
+-------------------------+-----------+---------+-----------+---------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
| id                      | estRows   | actRows | task      | access object                                                       | execution info                                                                                                                                                                                                                                                                          | operator info             | memory   | disk |
+-------------------------+-----------+---------+-----------+---------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
| TableReader_7           | 169695.86 | 169695  | root      | partition:p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16 | time:25.7ms, loops:182, cop_task: {num: 102, max: 10.6ms, min: 557.5µs, avg: 2.67ms, p95: 6.5ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 57ms, tot_wait: 11ms, rpc_num: 102, rpc_time: 271.8ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                        | data:Selection_6          | 542.4 KB | N/A  |
| └─Selection_6           | 169695.86 | 169695  | cop[tikv] |                                                                     | tikv_task:{proc max:2ms, min:0s, avg: 1.01ms, p80:2ms, p95:2ms, iters:561, tasks:102}, scan_detail: {total_process_keys: 169999, total_process_keys_size: 10598832, total_keys: 170101, get_snapshot_time: 5.92ms, rocksdb: {key_skipped_count: 169999, block: {cache_hit_count: 398}}} | lt(test.part.id2, 169696) | N/A      | N/A  |
|   └─TableFullScan_5     | 199999.00 | 169999  | cop[tikv] | table:part                                                          | tikv_task:{proc max:2ms, min:0s, avg: 980.4µs, p80:2ms, p95:2ms, iters:561, tasks:102}                                                                                                                                                                                                  | keep order:false          | N/A      | N/A  |
+-------------------------+-----------+---------+-----------+---------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
3 rows in set (0.03 sec)

mysql> explain analyze select * from part where id2<189696;
+-------------------------+-----------+---------+-----------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
| id                      | estRows   | actRows | task      | access object                                                               | execution info                                                                                                                                                                                                                                                                          | operator info             | memory   | disk |
+-------------------------+-----------+---------+-----------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
| TableReader_7           | 189695.20 | 189695  | root      | partition:p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18 | time:28.8ms, loops:204, cop_task: {num: 114, max: 11.1ms, min: 818.7µs, avg: 2.99ms, p95: 7.41ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 97ms, tot_wait: 26ms, rpc_num: 114, rpc_time: 340.3ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                       | data:Selection_6          | 570.2 KB | N/A  |
| └─Selection_6           | 189695.20 | 189695  | cop[tikv] |                                                                             | tikv_task:{proc max:4ms, min:0s, avg: 1.14ms, p80:2ms, p95:3ms, iters:627, tasks:114}, scan_detail: {total_process_keys: 189999, total_process_keys_size: 11858832, total_keys: 190113, get_snapshot_time: 2.92ms, rocksdb: {key_skipped_count: 189999, block: {cache_hit_count: 434}}} | lt(test.part.id2, 189696) | N/A      | N/A  |
|   └─TableFullScan_5     | 199999.00 | 189999  | cop[tikv] | table:part                                                                  | tikv_task:{proc max:4ms, min:0s, avg: 1.13ms, p80:2ms, p95:3ms, iters:627, tasks:114}                                                                                                                                                                                                   | keep order:false          | N/A      | N/A  |
+-------------------------+-----------+---------+-----------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+

Test Results

test1(ms) test2(ms) test3(ms) test4(ms) test5(ms) sum(ms) avg(ms)
Scan 1 partition 7.37 7.91 8.25 9.87 8.91 42.31 8.46
Scan 3 partitions 9.32 9.08 10.4 12.1 11.4 52.3 10.46
Scan 5 partitions 10.4 13.1 12.8 12.6 13.6 62.5 12.5
Scan 7 partitions 13.5 13.5 12.4 12.8 12.4 64.6 12.92
Scan 9 partitions 14.7 16.1 12.1 15.2 17.6 75.7 15.14
Scan 11 partitions 16.1 15.5 18.4 19.2 14.1 83.3 16.66
Scan 13 partitions 17.2 19.5 19.9 19.8 19.9 96.3 19.26
Scan 15 partitions 22.9 21.8 27.3 21.7 23 116.7 23.34
Scan 17 partitions 24.9 28 27.2 29.7 25.7 135.5 27.1
Scan 19 partitions 25.8 27.8 28.5 29.6 28.8 140.5 28.1

Conclusion

Based on the above tests, it can be seen that with complete statistics, the execution plan uses dynamic pruning. The time to scan 10 partitions is approximately twice the time to scan 1 partition.