The performance of partition table is significantly lower than that of normal table in some scenarios

[Defined2014]

Enhancement

Normal table

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL,
  `c` char(120) NOT NULL,
  `pad` char(60) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=10490991

Partition table

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL,
  `c` char(120) NOT NULL,
  `pad` char(60) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */ /*T![global_index] GLOBAL */,
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=10360579
PARTITION BY HASH (`k`) PARTITIONS 100;

SQL

explain analyze
SELECT
  id,
  k,
  c,
  pad
FROM
  sbtest1
WHERE
  k IN (702964, 686226, 703114, 711745, 703313, 703821, 702838, 703181, 703898, 702840);

Partition table, slower one

| id                        | estRows | estCost   | actRows | task      | access object                                     | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | operator info                                                                                                                                                                                                        | memory  | disk  |
| IndexLookUp_10            | 57.13   | 111607.58 | 0       | root      | partition:p14,p18,p31,p38,p49,p51,p62,p63,p65,p89 | time:5.92ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                      | 1.77 KB | N/A   |
| ├─IndexRangeScan_8(Build) | 57.13   | 11625.53  | 0       | cop[tikv] | table:sbtest1, index:k_1(k)                       | time:2.6ms, loops:10, cop_task: {num: 10, max: 4.03ms, min: 1.27ms, avg: 2.3ms, p95: 4.03ms, tot_proc: 2.62ms, tot_wait: 12.4ms, copr_cache_hit_ratio: 0.00, build_task_duration: 3.13ms, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:10, total_time:22.8ms}}, tikv_task:{proc max:1ms, min:0s, avg: 100µs, p80:0s, p95:1ms, iters:10, tasks:10}, scan_detail: {total_keys: 100, get_snapshot_time: 1.14ms, rocksdb: {block: {cache_hit_count: 598, read_count: 2, read_byte: 36.5 KB, read_time: 121.6µs}}}, time_detail: {total_process_time: 2.62ms, total_wait_time: 12.4ms, total_kv_read_wall_time: 1ms, tikv_wall_time: 15.6ms} | range:[8582989,8582989], [8590538,8590538], [8593762,8593762], [8593918,8593918], [8593931,8593931], [8594351,8594351], [8594549,8594549], [8594765,8594765], [8594814,8594814], [8595263,8595263], keep order:false | N/A     | N/A   |
| └─TableRowIDScan_9(Probe) | 57.13   | 17529.34  | 0       | cop[tikv] | table:sbtest1                                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | keep order:false                                                                                                                                                                                                     | N/A     | N/A   |

Normal table, faster one

| id                        | estRows | estCost   | actRows | task      | access object               | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | operator info                                                                                                                                                                                    | memory    | disk  |
| IndexLookUp_10            | 57.17   | 112202.78 | 0       | root      |                             | time:1.1ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                  | 261 Bytes | N/A   |
| ├─IndexRangeScan_8(Build) | 57.17   | 11633.62  | 0       | cop[tikv] | table:sbtest1, index:k_1(k) | time:1.03ms, loops:1, cop_task: {num: 1, max: 992.4µs, proc_keys: 0, tot_proc: 365.3µs, tot_wait: 326.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 15.4µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:980.1µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 10, get_snapshot_time: 307.3µs, rocksdb: {block: {cache_hit_count: 60}}}, time_detail: {total_process_time: 365.3µs, total_wait_time: 326.1µs, tikv_wall_time: 759.7µs} | range:[686226,686226], [702838,702838], [702840,702840], [702964,702964], [703114,703114], [703181,703181], [703313,703313], [703821,703821], [703898,703898], [711745,711745], keep order:false | N/A       | N/A   |
| └─TableRowIDScan_9(Probe) | 57.17   | 18285.81  | 0       | cop[tikv] | table:sbtest1               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                                 | N/A       | N/A   |

The max_distsql_concurrency for both SQL are equal to one, but partition table has 10 cop tasks, so it's 5x slower than normal table.

We could let partition table has a larger concurrency or let global index supports non-unqiue index.