Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: SQL 查询超过2分钟，执行计划显示limit慢
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue
[Encountered Issue: Issue Phenomenon and Impact]
SQL query takes more than 2 minutes, execution plan shows limit is slow
SELECT * from table
WHERE I_ID in (
SELECT I_ID FROM `table` use index (IDX_SYNC_STATUS)
WHERE I_SYNC_STATUS = 0 limit 100
)
Modified SQL to
SELECT * from table
WHERE I_ID in (
SELECT I_ID FROM `table` use index (IDX_SYNC_STATUS)
WHERE I_SYNC_STATUS = 0
)
It completed in 16 seconds.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
Dear experts, please enlighten me.
It’s faster without the limit? Also, it’s time to collect the statistics.
Post the execution plan and check the slow SQL page.
How large is the data volume?
The screenshot is the execution plan.
The total is over 2 billion, but there are 1000 entries with status=0.
How many values are there after status distinct? Probably not many, right?
Isn’t this a discount? It’s a full table.
I want to see the histogram statistics. It probably requires the whole table.
Collecting statistical information won’t work with this amount, right?
Modify the SQL:
SELECT
floor((t.row_num - 1) / 1000) + 1 AS page_num,
min(t.id) AS start_key,
max(t.id) AS end_key,
count(*) AS page_size
FROM (
SELECT id, row_number() OVER (ORDER BY id) AS row_num
FROM books
) t
GROUP BY page_num
ORDER BY page_num;
If “stats:pseudo” appears, you can directly use “analyze table” to collect statistics, it’s absolutely correct.
Without limit
With limit
Without limit 16s, with limit 3 minutes
Is this consistently reproducible? Feels like a bug.
Most of the time it happens like this, occasionally it works normally.
The image is not visible. Please provide the text you need translated.
Indeed, there are very few, but I have already used the index.
tikv_task: {proc max: 450ms, min: 59ms, p80: 137ms, p95: 189ms, iters: 1848, tasks: 1847}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}
|
username: 大飞飞呀
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: