7.5.6版本查询条件的查询列为空值时执行计划不准确

Hacker_Yv76YjBL · 2025 年5 月 20 日 02:10

【TiDB 使用环境】生产环境 /测试/ Poc
【TiDB 版本】v7.5.6
【其他附件：截图/日志/监控】
表结构

查询SQL
SELECT
item.*
FROM
pink_operate_item AS item
WHERE
item.tenant_id = ‘1’
AND item.order_no = ‘’
AND item.is_del = 0
ORDER BY
item.id ASC
LIMIT
1;
v5.3.3版本

explain analyze

+-------------------------------+-------------+----------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+------+
| id                            | estRows     | actRows  | task      | access object                            | execution info                                                                                                                                                                                                                                                                 | operator info                                                                                   | memory  | disk |
+-------------------------------+-------------+----------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+------+
| TopN_9                        | 1.00        | 0        | root      |                                          | time:28.4ms, loops:1                                                                                                                                                                                                                                                           | hupu_du_pink.pink_operate_item.id, offset:0, count:1                                            | 0 Bytes | N/A  |
| └─IndexLookUp_22              | 1.00        | 0        | root      |                                          | time:28.4ms, loops:2,                                                                                                                                                                                                                                                          |                                                                                                 | 1.12 KB | N/A  |
|   ├─Selection_19(Build)       | 57938810.94 | 0        | cop[tikv] |                                          | time:28ms, loops:1, cop_task: {num: 161, max: 12.3ms, min: 967.1µs, avg: 1.89ms, p95: 6.5ms, tot_proc: 1ms, tot_wait: 250ms, rpc_num: 161, rpc_time: 302.2ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:666ms, min:35ms, p80:427ms, p95:526ms, iters:60670, tasks:161}  | ne(hupu_du_pink.pink_operate_item.order_no, "")                                                 | N/A     | N/A  |
|   │ └─IndexRangeScan_17       | 61189000.69 | 61379051 | cop[tikv] | table:item, index:idx_order_no(order_no) | tikv_task:{proc max:610ms, min:33ms, p80:384ms, p95:483ms, iters:60670, tasks:161}                                                                                                                                                                                             | range:["",""], keep order:false                                                                 | N/A     | N/A  |
|   └─TopN_21(Probe)            | 1.00        | 0        | cop[tikv] |                                          |                                                                                                                                                                                                                                                                                | hupu_du_pink.pink_operate_item.id, offset:0, count:1                                            | N/A     | N/A  |
|     └─Selection_20            | 48680878.13 | 0        | cop[tikv] |                                          |                                                                                                                                                                                                                                                                                | eq(hupu_du_pink.pink_operate_item.is_del, 0), eq(hupu_du_pink.pink_operate_item.tenant_id, "1") | N/A     | N/A  |
|       └─TableRowIDScan_18     | 57938810.94 | 0        | cop[tikv] | table:item                               |                                                                                                                                                                                                                                                                                | keep order:false                                                                                | N/A     | N/A  |
+-------------------------------+-------------+----------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+------+

v7.5.6版本

+------------------------------+--------------+-----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                           | estRows      | actRows   | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                                                                   | operator info                                                                                                                                                                                     | memory  | disk |
+------------------------------+--------------+-----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Limit_11                     | 1.00         | 0         | root      |               | time:5m33.2s, loops:1, RU:7852597.518634                                                                                                                                                                                                                                                                                                                                                                         | offset:0, count:1                                                                                                                                                                                 | N/A     | N/A  |
| └─TableReader_26             | 1.00         | 0         | root      |               | time:5m33.2s, loops:1, cop_task: {num: 5358, max: 2.87ms, min: 0s, avg: 931.3ms, p95: 1.25s, tot_proc: 1h22m22.3s, tot_wait: 2.95s, copr_cache_hit_ratio: 0.03, build_task_duration: 8.45ms, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:5358, total_time:1h23m9.6s}}                                                                                                                                   | data:Limit_25                                                                                                                                                                                     | 7.97 KB | N/A  |
|   └─Limit_25                 | 1.00         | 0         | cop[tikv] |               | tikv_task:{proc max:2.58s, min:2ms, avg: 949.4ms, p80:1.13s, p95:1.24s, iters:950817, tasks:5358}, scan_detail: {total_process_keys: 914188466, total_process_keys_size: 406494393586, total_keys: 934744530, get_snapshot_time: 2.73s, rocksdb: {delete_skipped_count: 29461172, key_skipped_count: 1883639737, block: {cache_hit_count: 2224143, read_count: 10401889, read_byte: 81.5 GB, read_time: 1m51s}}} | offset:0, count:1                                                                                                                                                                                 | N/A     | N/A  |
|     └─Selection_24           | 1.00         | 0         | cop[tikv] |               | tikv_task:{proc max:2.58s, min:2ms, avg: 949.3ms, p80:1.13s, p95:1.24s, iters:950817, tasks:5358}                                                                                                                                                                                                                                                                                                                | eq(hupu_du_pink.pink_operate_item.is_del, 0), eq(hupu_du_pink.pink_operate_item.order_no, ""), eq(hupu_du_pink.pink_operate_item.tenant_id, "1"), ne(hupu_du_pink.pink_operate_item.order_no, "") | N/A     | N/A  |
|       └─TableFullScan_23     | 948775674.00 | 948780929 | cop[tikv] | table:item    | tikv_task:{proc max:2.52s, min:2ms, avg: 919.7ms, p80:1.1s, p95:1.2s, iters:950817, tasks:5358}                                                                                                                                                                                                                                                                                                                  | keep order:true                                                                                                                                                                                   | N/A     | N/A  |
+------------------------------+--------------+-----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+```
v7.5.6强制绑定索引
![image|690x322](upload://qxzA310WRrXtN0sTfhjSmHiRCDr.png)

WalterWj · 2025 年5 月 20 日 02:21

看老版本 index scan 6 千万，估计新版本优化器判断回表代价高然后走的全表扫。

你试试新版本价格 hint 走索引的效率。

Hacker_Yv76YjBL · 2025 年5 月 20 日 02:26

v7.5.6强制绑定索引

WalterWj · 2025 年5 月 20 日 02:31

发个 explain Analyze 和上面对比下。可能是命中率或者一些算子并发新版本调整了。

WalterWj · 2025 年5 月 20 日 02:45

https://docs.pingcap.com/zh/tidb/stable/system-variables/#tidb_opt_ordering_index_selectivity_threshold-从-v700-版本开始引入

可以试试调大这个的效果。

tidb菜鸟一只 · 2025 年5 月 20 日 03:03

这条件是什么意思，我怎么看不懂？

zhanggame1 · 2025 年5 月 20 日 06:14

7.5.6执行快还是5版本执行快，时间是多少

Miracle · 2025 年5 月 20 日 13:52

这个sql跟你截图里执行计划的sql不一样吧。执行计划感觉写错了？写了一个=’‘，又写一个!=‘’
item.order_no = ‘’
order_no != ‘’

有猫万事足 · 2025 年5 月 20 日 15:08

这就差不多了吧，原来这个表里就6000w，，你7.5的表里面有近9.5亿数据，打了十几倍，走索引的时间到秒级返回我觉得合理。

这块优化器唯一的问题就是没发现条件是冲突的，可以直接返回空行给你。取都不用取数据。

Hacker_Yv76YjBL · 2025 年5 月 21 日 02:08

改了也不行

WalterWj · 2025 年5 月 21 日 02:24

好吧，那就发下新版本用 hint 干扰走 index range scan 后的 explain Analyze 看看。

看看能不能对比出来为啥新版本走索引为啥也慢。

WalterWj · 2025 年5 月 21 日 07:59

github.com/pingcap/tidb

Planner: Need to ignore the ordering index path even if the index selectivity is 1.

已打开 05:42PM - 24 Mar 25 UTC

已关闭 05:56AM - 26 Mar 25 UTC

King-Dylan

type/enhancement sig/planner affects-7.1 affects-7.5 affects-8.1 report/customer affects-8.5

## Bug Report Please answer these questions before submitting your issue. Thank…s! In some versions, we still need to set tidb_opt_ordering_index_selectivity_threshold to control whether we want to use the index order property to scan rows. However, when the index selectivity is equal to 1 and we set tidb_opt_ordering_index_selectivity_threshold=1, we still can't ignore the ordering index plan. This is because we always require [index_selectivity < selectivity_threshold](https://github.com/pingcap/tidb/blob/release-7.1-20240802-v7.1.5/planner/core/find_best_task.go#L2471) to eliminate the ordering path. Actually, we just need to replace `<` with `<=`. ### 1. Minimal reproduce step (Required) ``` +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tttt1 | CREATE TABLE `tttt1` ( `id1` int NOT NULL, `id2` int NOT NULL, `id3` varchar(20) NOT NULL, `id4` int NOT NULL, `id5` int NOT NULL, `id6` int NOT NULL, KEY `idx_id1` (`id3`,`id2`), KEY `idx_id2` (`id4`,`id2`), KEY `idx` (`id1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) mysql> set session tidb_opt_ordering_index_selectivity_threshold=1; Query OK, 0 rows affected (0.00 sec) ``` ### 2. What did you expect to see? (Required) ``` mysql> explain select * from tttt1 where id6=1 order by id1 limit 10; +------------------------------+------------+-----------+---------------+----------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+------------+-----------+---------------+----------------------------------------------------+ | TopN_8 | 10.00 | root | | test.tttt1.id1, offset:0, count:10 | | └─TableReader_16 | 10.00 | root | | data:TopN_15 | | └─TopN_15 | 10.00 | cop[tikv] | | test.tttt1.id1, offset:0, count:10 | | └─Selection_14 | 2097.15 | cop[tikv] | | eq(test.tttt1.id6, 1) | | └─TableFullScan_13 | 2097152.00 | cop[tikv] | table:tttt1 | keep order:false, stats:partial[id6:unInitialized] | +------------------------------+------------+-----------+---------------+----------------------------------------------------+ 5 rows in set (0.00 sec) ``` ### 3. What did you see instead (Required) ``` mysql> explain select * from tttt1 where id6=1 order by id1 limit 10; +-----------------------------------+----------+-----------+-----------------------------+------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------------+----------+-----------+-----------------------------+------------------------------------------------------------------------------------------------+ | Limit_12 | 10.00 | root | | offset:0, count:10 | | └─Projection_21 | 10.00 | root | | test.tttt1.id1, test.tttt1.id2, test.tttt1.id3, test.tttt1.id4, test.tttt1.id5, test.tttt1.id6 | | └─IndexLookUp_20 | 10.00 | root | | | | ├─IndexFullScan_17(Build) | 10000.00 | cop[tikv] | table:tttt1, index:idx(id1) | keep order:true, stats:partial[id6:unInitialized] | | └─Selection_19(Probe) | 10.00 | cop[tikv] | | eq(test.tttt1.id6, 1) | | └─TableRowIDScan_18 | 10000.00 | cop[tikv] | table:tttt1 | keep order:false, stats:partial[id6:unInitialized] | +-----------------------------------+----------+-----------+-----------------------------+------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` ### 4. What is your TiDB version? (Required) v7.1.6

不行的原因应该是这个问题，需要 7.5.7 修复。。。等着吧。。。

system · 2025 年5 月 28 日 07:59

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。