The performance of partition table is significantly lower than normal table in `indexLookUpReaderExec`

[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:[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.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.

[mjonss]

@Defined2014 should not the IndexRangeScan for a global index be the same as for a non-partitioned table? That there are 10 IndexLookup/TableRowIDScan is probably expected?

[Defined2014]

@Defined2014 should not the IndexRangeScan for a global index be the same as for a non-partitioned table? That there are 10 IndexLookup/TableRowIDScan is probably expected?

Yes, 10 cop tasks are expected. But we can increase its concurrency which makes SQL faster.

After deep into the code, I think the problems is we handle SelectResults one by one. There are two plans below, one has 5 cop tasks, another one only has 1 cop task.

5 cop tasks

[IndexLookUp_7 50.00 5 root partition:p262,p263,p264,p265,p266 time:232.3µs, loops:2, index_task: {total_time: 88.3µs, fetch_handle: 84.5µs, build: 1.71µs, wait: 2.12µs}, table_task: {total_time: 426µs, num: 5, concurrency: 5}, next: {wait_index: 79.9µs, wait_table_lookup_build: 32.6µs, wait_table_lookup_resp: 110.2µs}  69.6 KB N/A],
[├─IndexRangeScan_5(Build) 50.00 5 cop[tikv] table:t, index:idx(col) time:83µs, loops:15, cop_task: {num: 5, max: 28µs, min: 20.4µs, avg: 24.5µs, p95: 28µs, copr_cache_hit_ratio: 0.00, build_task_duration: 10.4µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:91.2µs}}, tikv_task:{proc max:16.9µs, min:12.6µs, avg: 15.1µs, p80:16.9µs, p95:16.9µs, iters:0, tasks:5} range:[5382,5382], [6407,6407], [7432,7432], [8457,8457], [9482,9482], keep order:false, stats:pseudo N/A N/A],
[└─TableRowIDScan_6(Probe) 50.00 5 cop[tikv] table:t time:236.1µs, loops:10, cop_task: {num: 5, max: 34.1µs, min: 17.7µs, avg: 27.4µs, p95: 34.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 87.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:120.3µs}}, tikv_task:{proc max:29.5µs, min:13.5µs, avg: 21.6µs, p80:29.5µs, p95:29.5µs, iters:0, tasks:5} keep order:false, stats:pseudo N/A N/A]

1 cop task

[IndexLookUp_7 50.00 3 root  time:191.5µs, loops:2, index_task: {total_time: 53µs, fetch_handle: 51.9µs, build: 541ns, wait: 542ns}, table_task: {total_time: 103.1µs, num: 1, concurrency: 5}, next: {wait_index: 76.4µs, wait_table_lookup_build: 38.5µs, wait_table_lookup_resp: 67.5µs}  17.9 KB N/A],
[├─IndexRangeScan_5(Build) 50.00 3 cop[tikv] table:t, index:idx(col) time:49.8µs, loops:3, cop_task: {num: 1, max: 27.2µs, proc_keys: 0, copr_cache_hit_ratio: 0.00, build_task_duration: 8.58µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:22.4µs}}, tikv_task:{time:19.5µs, loops:0} range:[7396,7396], [8421,8421], [9446,9446], [10471,10471], [11496,11496], keep order:false, stats:pseudo N/A N/A],
[└─TableRowIDScan_6(Probe) 50.00 3 cop[tikv] table:t time:59.7µs, loops:2, cop_task: {num: 1, max: 35.8µs, proc_keys: 0, copr_cache_hit_ratio: 0.00, build_task_duration: 15.8µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:32.7µs}}, tikv_task:{time:30µs, loops:0} keep order:false, stats:pseudo N/A N/A]]

The time diff is 232.3 - 191.5 = 40.8us, the fetch_handle diff is 84.5 - 51.9 = 32.6us, we could say most of time diff is come from fetch_hanlde which is

tidb/pkg/executor/distsql.go

Line 1071 in 01797cb

for i := 0; i < len(results); {

which causes head-of-line blocking to occur. If we can get handle concurrency, the performance of the SQL should be better.

And the max_distsql_concurrency in plan only display the concurrency of one selectResult which means concurrency in one partition. So 10 and 1 are all expected.

[Defined2014]

Create two different tables

create table t (pk int auto_increment, col int, index idx (col));
SPLIT TABLE t index idx BETWEEN (0) AND (10000) REGIONS 10;
SPLIT TABLE t BETWEEN (0) AND (10000) REGIONS 10;

create table t1 (pk int auto_increment, col int, index idx (col)) partition by hash(col) partitions 1024;

And use the code below to do test.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"golang.org/x/exp/rand"
)

func main() {
	// MySQL 数据库连接配置
	dsn := "root:@tcp(127.0.0.1:4000)/test" // 替换为你的数据库连接信息

	// 连接数据库
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal("数据库连接失败: ", err)
	}
	defer db.Close()

	// 测试数据库连接
	err = db.Ping()
	if err != nil {
		log.Fatal("数据库无法访问: ", err)
	}
	fmt.Println("成功连接到数据库")

	// 性能测试，执行 N 次查询
	N := 1000
	startTime := time.Now()

	for i := 0; i < N; i++ {
		err := testQuery(db)
		if err != nil {
			log.Fatal("查询执行失败: ", err)
		}
	}

	endTime := time.Now()

	// 计算性能结果
	duration := endTime.Sub(startTime)
	fmt.Printf("执行 %d 次查询共耗时: %v\n", N, duration)
	fmt.Printf("每次查询平均耗时: %v\n", duration/time.Duration(N))
}

// 执行单次查询
func testQuery(db *sql.DB) error {
	n := rand.Intn(5000)
	query := fmt.Sprintf("select * from t1 where col in (%d, %d, %d, %d, %d)", n, n+1025, n+2050, n+3075, n+4100)
	rows, err := db.Query(query)
	if err != nil {
		return err
	}
	defer rows.Close()

	// 模拟处理结果
	for rows.Next() {
		var a, b any
		err := rows.Scan(&a, &b)
		if err != nil {
			return err
		}
	}
	return nil
}

results

normal table with split: 642.465µs per SQL

normal table without split: 528.449µs per SQL

partition table: 804.993µs per SQL

partition table + concurrency: 757.608µs per SQL

[Defined2014]

Every selectResult will call copr.buildCopTasks once. Maybe we could set all keyRanges in one selectResult and build partition handles in indexWorker.extractTaskHandles function , which can reduce the copr.buildCopTasks calls and also make it easier for us to control the concurrency of the indexLookUp operator in the partition table, ref issue #42244.

Tried that, no much improvement, could reduce ~20us