集群版本6.5.6 分区表为20个分区，每个分区均匀分布10000条数据；
示例表结构
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))
测试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 |
+-------------------------+-----------+---------+-----------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+------+
测试结果
| test1(ms) | test2(ms) | test3(ms) | test4(ms) | test5(ms) | sum(ms) | avg(ms) | |
|---|---|---|---|---|---|---|---|
| Scan 1个分区 | 7.37 | 7.91 | 8.25 | 9.87 | 8,91 | 42.31 | 8.46 |
| Scan 3个分区 | 9.32 | 9.08 | 10.4 | 12.1 | 11.4 | 52.3 | 10.46 |
| Scan 5个分区 | 10.4 | 13.1 | 12.8 | 12.6 | 13.6 | 62.5 | 12.5 |
| Scan 7个分区 | 13.5 | 13.5 | 12.4 | 12.8 | 12.4 | 64.6 | 12.92 |
| Scan 9个分区 | 14.7 | 16.1 | 12.1 | 15.2 | 17.6 | 75.7 | 15.14 |
| Scan 11个分区 | 16.1 | 15.5 | 18.4 | 19.2 | 14.1 | 83.3 | 16.66 |
| Scan 13个分区 | 17.2 | 19.5 | 19.9 | 19.8 | 19.9 | 96.3 | 19.26 |
| Scan 15个分区 | 22.9 | 21.8 | 27.3 | 21.7 | 23 | 116.7 | 23.34 |
| Scan 17个分区 | 24.9 | 28 | 27.2 | 29.7 | 25.7 | 135.5 | 27.1 |
| Scan 19个分区 | 25.8 | 27.8 | 28.5 | 29.6 | 28.8 | 140.5 | 28.1 |
结论
基于上述测试可见，在统计信息完整的情况下，执行计划走动态裁剪。扫描10个分区的时长大约是扫描1个分区的2倍。