Slow SQL Query

Translated
1

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

Original topic: SQL查询慢

| username: TiDBer_OzwiOwPc

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.1
Query SQL is very slow
Execution Plan:
id task estRows operator info actRows execution info memory disk
Projection_4 root 0.00 csxt_sale.sale_inv_pool_header.pool_header_id, csxt_sale.sale_inv_pool_header.inv_status 1 time:1m18.5s, loops:2, Concurrency:OFF 89.5 KB N/A
└─IndexLookUp_15 root 0.00 1 time:1m18.5s, loops:2, index_task: {total_time: 1m18.1s, fetch_handle: 27s, build: 1.79ms, wait: 51.1s}, table_task: {total_time: 4m43.9s, num: 772, concurrency: 5}, next: {wait_index: 13.3s, wait_table_lookup_build: 12.9ms, wait_table_lookup_resp: 1m5.1s} 30.6 MB N/A
├─IndexRangeScan_12(Build) cop[tikv] 1.46 table:sale_inv_pool_header, index:idx_pool_header_digital_inv_no(digital_inv_no), range:[“”,“”], keep order:false 15720750 time:26.1s, loops:15402, cop_task: {num: 424, max: 2.87s, min: 746.8µs, avg: 254.4ms, p95: 1.95s, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 9.39s, tot_wait: 143.8ms, rpc_num: 424, rpc_time: 1m47.8s, copr_cache_hit_ratio: 0.07, build_task_duration: 103.5µs, max_distsql_concurrency: 15}, tikv_task:{proc max:2.84s, min:0s, avg: 147.6ms, p80:50ms, p95:1.68s, iters:17041, tasks:424}, scan_detail: {total_process_keys: 15299502, total_process_keys_size: 841472610, total_keys: 15299903, get_snapshot_time: 5.36ms, rocksdb: {delete_skipped_count: 224, key_skipped_count: 17570451, block: {cache_hit_count: 29395}}} N/A N/A
└─Selection_14(Probe) cop[tikv] 0.00 eq(csxt_sale.sale_inv_pool_header.inv_code, “3200223160”), eq(csxt_sale.sale_inv_pool_header.inv_no, “00382760”), eq(csxt_sale.sale_inv_pool_header.inv_type, “01”) 1 time:4m42.3s, loops:773, cop_task: {num: 2640, max: 2.96s, min: 1.15ms, avg: 142.4ms, p95: 1.39s, max_proc_keys: 20480, p95_proc_keys: 13972, tot_proc: 30.5s, tot_wait: 860.7ms, rpc_num: 2641, rpc_time: 6m15.9s, copr_cache_hit_ratio: 0.00, build_task_duration: 26.5ms, max_distsql_concurrency: 13, max_extra_concurrency: 1}, backoff{regionMiss: 2ms}, tikv_task:{proc max:2.95s, min:0s, avg: 121.1ms, p80:25ms, p95:1.32s, iters:27328, tasks:2640}, scan_detail: {total_process_keys: 15720750, total_process_keys_size: 10827121850, total_keys: 15881756, get_snapshot_time: 31.5ms, rocksdb: {delete_skipped_count: 135653, key_skipped_count: 31928713, block: {cache_hit_count: 2198212, read_count: 93, read_byte: 1.81 MB, read_time: 856µs}}} N/A N/A
└─TableRowIDScan_13 cop[tikv] 1.46 table:sale_inv_pool_header, keep order:false 15720750 tikv_task:{proc max:2.95s, min:0s, avg: 120.4ms, p80:24ms, p95:1.32s, iters:27328, tasks:2640} N/A N/A

Table Index Status:

image
image1071×625 92.4 KB

SQL:
image

There is a unique index, why is it not a point query? The table and index information have also been collected. Now this SQL is causing a 24-hour read hotspot on the entire table. Seeking help from experts.

2
| username: TiDBer_OzwiOwPc | Original post link

The images you provided are not accessible. Please provide the text content that you need translated.

3
| username: zhaokede | Original post link

This depends on the original query SQL, the data model, and checking whether the indexes are effective.

4
| username: 小龙虾爱大龙虾 | Original post link

Selected the wrong index, currently using idx_pool_header_digital_inv_no, the passed value is an empty string. Creating a new composite index will solve the issue.

5
| username: forever | Original post link

Why are they all single-column indexes? Composite indexes would be better. The time is being spent on table lookups to filter data.

6
| username: zhanggame1 | Original post link

Did you manually analyze the table?

7
| username: TiDBer_OzwiOwPc | Original post link

I’ve done it all manually.

8
| username: zhanggame1 | Original post link

Have you tested the speed of other execution plans using hints?

9
| username: Kongdom | Original post link
  1. The primary key was not used because the WHERE condition did not include the primary key column.
  2. It is recommended to analyze a specific statement separately. Although it used digital_inv_no, if all values are empty, using the index or not using the index would be the same.

index: idx_pool_header_digital_inv_no(digital_inv_no), range: [“”, “”]

10
| username: 这里介绍不了我 | Original post link

The index selection is incorrect, and it is recommended to create a composite index to improve it.

11
| username: dba远航 | Original post link

Why not create composite indexes and instead use single-column indexes?

12
| username: Soysauce520 | Original post link

If there is no pool_header_id after where, point query cannot be performed. If the result is not large each time, it indicates that a composite index can be created for optimization.

13
| username: wangccsy | Original post link

Have you done any index optimization on the table?

14
| username: Kongdom | Original post link

There is an index, but it’s not a composite index. Adding a composite index might be better.

15
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.