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

WalterWj · 2025 年5 月 21 日 07:59

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

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 修复。。。等着吧。。。