Issues with Instability in TiDB Execution Plans

Translated
1

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

Original topic: tidb 执行计划不稳定问题

| username: hygame

To improve efficiency, please provide the following information. Clear problem descriptions can lead to quicker resolutions:

[Overview] Scenario + Problem Summary
tidb5.4 3tidb-server 3pd 3kv 1tiflash

[Application Framework and Business Logic Adaptation]

[Background] Operations performed

[Phenomenon] Business and database phenomena

[Problem] Current issue encountered
We have a large table with the following structure:
CREATE TABLE parking_record (
id bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘Auto-increment ID’,
upload_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘ID uploaded by the booth’,
record_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘Parking record type 76 system 77 temporary’,
card_car_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Card ID’,
temp_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Temporary ID’,
entrance_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘Entrance time’,
entrance_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Entrance image’,
entrance_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance booth ID’,
exit_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘Exit time’,
exit_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Exit image’,
exit_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit booth ID’,
receivable_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Receivable amount’,
actual_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Actual amount received’,
admin_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Admin ID’,
ontime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Parking record booth creation time’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Parking record booth update time’,
parking_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Parking lot ID’,
park_name varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘Parking lot name’,
area_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Area ID’,
group_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Group ID’,
is_fixed int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘449=Special release’,
state int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘111 meaning unclear, 449=Special release, 452=Special amount release, 0=Default, others=cartypeid’,
car_owner_parking_fixed_location_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Long-term rate ID’,
exit_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit road’,
entrance_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance road’,
synid varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Primary key synid’,
free_time int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Free time’,
plate_number varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘License plate number’,
upload_insert datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Cloud creation time’,
upload_update datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Cloud update time’,
remarks varchar(500) NOT NULL DEFAULT ‘’ COMMENT ‘Remarks’,
online_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Electronic payment amount’,
entrance_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance image confidence’,
exit_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit image confidence’,
entrance_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance license plate color 0-None 1-Blue 2-Black 3-Yellow 4-White 5-Green’,
exit_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit license plate color’,
operate_user_id int(11) NOT NULL DEFAULT ‘-1’,
is_finish int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Whether terminated 0 not terminated 1 terminated’,
motor_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘0-Non-motor vehicle, 1-Motor vehicle Parking record table’,
motor_type_detail tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘Detailed type of motor vehicle’,
brand varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘Car brand’,
character_code varchar(250) NOT NULL DEFAULT ‘’ COMMENT ‘Feature code’,
status tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘Status (1: valid; 2: invalid)’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY idx_synid (synid),
KEY idx_plate_number_color (plate_number,entrance_car_plate_color),
KEY idx_entrance_time (entrance_time),
KEY idx_exit_time (exit_time),
KEY idx_upload_insert (upload_insert),
KEY idx_upload_update (upload_update),
KEY idx_parkingid_exittime (parking_id,exit_time),
KEY idx_parkingid_entrancetime (parking_id,entrance_time),
KEY idx_parkingid_platenumber (parking_id,plate_number),
KEY idx_parking_id_update_time_status_exit_time (parking_id,update_time,status,exit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=4078488319 */ COMMENT=‘Parking record’
The problematic SQL is as follows:
SELECT
upload_id,
record_type,
card_car_id,
temp_id,
entrance_time,
entrance_car_image,
entrance_parking_box_id,
exit_time,
exit_car_image,
exit_parking_box_id,
receivable_fee,
actual_fee,
admin_id,
ontime,
update_time,
parking_id,
park_name,
area_id,
group_id,
is_fixed,
state,
car_owner_parking_fixed_location_id,
exit_road,
entrance_road,
synid,
free_time,
plate_number,
upload_insert,
upload_update,
remarks,
online_fee,
entrance_car_image_believe,
exit_car_image_believe,
entrance_car_plate_color,
exit_car_plate_color,
operate_user_id,
is_finish,
motor_type,
motor_type_detail,
brand,
STATUS
FROM
parking_record
WHERE
parking_id = ?
AND STATUS = ?
AND (
exit_time <> ?
AND update_time >= ?
AND update_time <= ?
)
ORDER BY
entrance_time DESC,
id ASC
LIMIT
0, 10
Most execution plans will use the idx_parking_id_update_time_status_exit_time index, which is very efficient, but sometimes the execution plan will use the idx_parkingid_exittime index, which suddenly becomes a slow SQL. What is causing this? I checked the health of this table, which is 96%, and both explain and explain analyze for the same SQL use the correct execution plan.

image
image1380×198 42.3 KB

+---------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                              | estRows    | task      | access object                                                                                                       | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_8                    | 10.00      | root      |                                                                                                                     | parking.parking_record.upload_id, parking.parking_record.record_type, parking.parking_record.card_car_id, parking.parking_record.temp_id, parking.parking_record.entrance_time, parking.parking_record.entrance_car_image, parking.parking_record.entrance_parking_box_id, parking.parking_record.exit_time, parking.parking_record.exit_car_image, parking.parking_record.exit_parking_box_id, parking.parking_record.receivable_fee, parking.parking_record.actual_fee, parking.parking_record.admin_id, parking.parking_record.ontime, parking.parking_record.update_time, parking.parking_record.parking_id, parking.parking_record.park_name, parking.parking_record.area_id, parking.parking_record.group_id, parking.parking_record.is_fixed, parking.parking_record.state, parking.parking_record.car_owner_parking_fixed_location_id, parking.parking_record.exit_road, parking.parking_record.entrance_road, parking.parking_record.synid, parking.parking_record.free_time, parking.parking_record.plate_number, parking.parking_record.upload_insert, parking.parking_record.upload_update, parking.parking_record.remarks, parking.parking_record.online_fee, parking.parking_record.entrance_car_image_believe, parking.parking_record.exit_car_image_believe, parking.parking_record.entrance_car_plate_color, parking.parking_record.exit_car_plate_color, parking.parking_record.operate_user_id, parking.parking_record.is_finish, parking.parking_record.motor_type, parking.parking_record.motor_type_detail, parking.parking_record.brand, parking.parking_record.status |
| └─TopN_10                       | 10.00      | root      |                                                                                                                     | parking.parking_record.entrance_time:desc, parking.parking_record.id, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   └─IndexLookUp_27              | 10.00      | root      |                                                                                                                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     ├─Selection_25(Build)       | 62661.13   | cop[tikv] |                                                                                                                     | eq(parking.parking_record.status, 2), ne(parking.parking_record.exit_time, 2000-01-01 00:00:00.000000)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|     │ └─IndexRangeScan_23       | 1014303.02 | cop[tikv] | table:parking_record, index:idx_parking_id_update_time_status_exit_time(parking_id, update_time, status, exit_time) | range:[3898 2022-08-03 00:00:00,3898 2022-08-03 08:55:58], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     └─TopN_26(Probe)            | 10.00      | cop[tikv] |                                                                                                                     | parking.parking_record.entrance_time:desc, parking.parking_record.id, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|       └─TableRowIDScan_24       | 62661.13   | cop[tikv] | table:parking_record                                                                                                | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+---------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                              | estRows    | actRows | task      | access object                                                                                                       | execution info                                                                                                                                                                                                                                                                                                                                                                                                    | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory    | disk |
+---------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_8                    | 10.00      | 0       | root      |                                                                                                                     | time:1.9ms, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                              | parking.parking_record.upload_id, parking.parking_record.record_type, parking.parking_record.card_car_id, parking.parking_record.temp_id, parking.parking_record.entrance_time, parking.parking_record.entrance_car_image, parking.parking_record.entrance_parking_box_id, parking.parking_record.exit_time, parking.parking_record.exit_car_image, parking.parking_record.exit_parking_box_id, parking.parking_record.receivable_fee, parking.parking_record.actual_fee, parking.parking_record.admin_id, parking.parking_record.ontime, parking.parking_record.update_time, parking.parking_record.parking_id, parking.parking_record.park_name, parking.parking_record.area_id, parking.parking_record.group_id, parking.parking_record.is_fixed, parking.parking_record.state, parking.parking_record.car_owner_parking_fixed_location_id, parking.parking_record.exit_road, parking.parking_record.entrance_road, parking.parking_record.synid, parking.parking_record.free_time, parking.parking_record.plate_number, parking.parking_record.upload_insert, parking.parking_record.upload_update, parking.parking_record.remarks, parking.parking_record.online_fee, parking.parking_record.entrance_car_image_believe, parking.parking_record.exit_car_image_believe, parking.parking_record.entrance_car_plate_color, parking.parking_record.exit_car_plate_color, parking.parking_record.operate_user_id, parking.parking_record.is_finish, parking.parking_record.motor_type, parking.parking_record.motor_type_detail, parking.parking_record.brand, parking.parking_record.status | 20.6 KB   | N/A  |
| └─TopN_10                       | 10.00      | 0       | root      |                                                                                                                     | time:1.9ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                               | parking.parking_record.entrance_time:desc, parking.parking_record.id, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | 0 Bytes   | N/A  |
|   └─IndexLookUp_27              | 10.00      | 0       | root      |                                                                                                                     | time:1.85ms, loops:2,                                                                                                                                                                                                                                                                                                                                                                                             |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 240 Bytes | N/A  |
|     ├─Selection_25(Build)       | 62661.13   | 0       | cop[tikv] |                                                                                                                     | time:1.76ms, loops:1, cop_task: {num: 1, max: 1.73ms, proc_keys: 109, tot_proc: 1ms, tot_wait: 1ms, rpc_num: 1, rpc_time: 1.72ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:3}, scan_detail: {total_process_keys: 109, total_process_keys_size: 8938, total_keys: 196, rocksdb: {delete_skipped_count: 0, key_skipped_count: 195, block: {cache_hit_count: 9, read_count: 6, read_byte: 234.4 KB}}} | eq(parking.parking_record.status, 2), ne(parking.parking_record.exit_time, 2000-01-01 00:00:00.000000)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | N/A       | N/A  |
|     │ └─IndexRangeScan_23       | 1014303.02 | 109     | cop[tikv] | table:parking_record, index:idx_parking_id_update_time_status_exit_time(parking_id, update_time, status, exit_time) | tikv_task:{time:1ms, loops:3}                                                                                                                                                                                                                                                                                                                                                                                     | range:[3898 2022-08-03 00:00:00,3898 2022-08-03 08:55:58], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A  |
|     └─TopN_26(Probe)            | 10.00      | 0       | cop[tikv] |                                                                                                                     |                                                                                                                                                                                                                                                                                                                                                                                                                   | parking.parking_record.entrance_time:desc, parking.parking_record.id, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A       | N/A  |
|       └─TableRowIDScan_24       | 62661.13   | 0       | cop[tikv] | table:parking_record                                                                                                |                                                                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A       | N/A  |
+---------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+

[Business Impact]

[TiDB Version]
5.4
[Attachments] Related logs and monitoring (https://metricstool.pingcap.com/)

For performance optimization and fault troubleshooting questions, please download the script and run it. Please be sure to select and copy all terminal output and upload it.

2
| username: ddhe9527 | Original post link

It seems to be related to the range of update_time. If the range is large, the cost of using the idx_parking_id_update_time_status_exit_time index to go back to the table is high, so the optimizer may choose to use the idx_parkingid_exittime index. If the update_time range is not large, it will be more inclined to use the idx_parking_id_update_time_status_exit_time index. You can test it specifically.

3
| username: hygame | Original post link

Hello, I have checked, the time range is not large, just within 8 hours, and for the same SQL with the same conditions, both explain and explain ANALYZE use idx_parking_id_update_time_status_exit_time, while the execution plan reported in the dashboard uses idx_parkingid_exittime. Additionally, before this query, we have a count(*) operation with the same conditions for pagination, which does not involve table lookups and also uses the idx_parkingid_exittime index. However, the actual explain and explain ANALYZE use idx_parking_id_update_time_status_exit_time.

4
| username: hygame | Original post link

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

5
| username: ddhe9527 | Original post link

Both of these indexes can be fully matched by this SQL, and it is estimated that the cost of these two indexes evaluated by the optimizer is very close, causing the execution plan to be unstable. Here are two suggestions to choose from:

  1. Adjust the field order of the index idx_parking_id_update_time_status_exit_time, keep parking_id as the first field, place exit_time second, and move the remaining fields back. Then delete the redundant index idx_parkingid_exittime to avoid confusion for the optimizer.
  2. Use SPM to bind the execution plan to the existing idx_parking_id_update_time_status_exit_time index.
    执行计划管理 (SPM) | PingCAP 文档中心
6
| username: hygame | Original post link

If the order is adjusted and exit_time is placed second, it will no longer be an equality query, and the subsequent values will not be able to use the index, right?

7
| username: ddhe9527 | Original post link

Whether an index is used or not is related to whether the field is NOT NULL. After adjusting the order, the performance of using the index should be between the original idx_parking_id_update_time_status_exit_time and idx_parkingid_exittime.

Here is a correction: In TiDB, whether an index is used or not should not be related to NOT NULL (it can be used). I tested and confirmed this. It is related in Oracle, but not in MySQL. :smile:

8
| username: hygame | Original post link

Hello, I have bound the execution plan for this SQL, but occasionally it takes 1 minute to execute. I see that the execution plan uses IndexFullScan, but when I use EXPLAIN ANALYZE, it uses IndexRangeScan. What is the reason for this?

9
| username: hygame | Original post link

The image you provided is not accessible. Please provide the text you need translated.

10
| username: ddhe9527 | Original post link

On the dashboard, you can see SQLs that perform IndexFullScan. Check if there is a large range of update_time to see if there is any correlation. From your screenshot, the range seems quite large.

11
| username: hygame | Original post link

Not really.

12
| username: ddhe9527 | Original post link

Oh, I misunderstood. The first time is exit_time, and the latter pair is update_time.

This execution plan does have issues. Currently, TiDB’s hint cannot control whether to use index range scan or index full scan, and binding cannot bind PLAN_DIGEST, only SQL text. I’ll ask the experts in the moderator group to help you take a look.

13
| username: songxuecheng | Original post link

Check the value of the variable tidb_enable_column_tracking.

14
| username: Kamner | Original post link

Can it be reproduced? Does an index full scan occur if the two filter conditions for update_time are the same?

15
| username: hygame | Original post link

tidb_enable_column_tracking

16
| username: hygame | Original post link

I can’t reproduce it. No matter how I execute it myself, it always goes through index_range_scan.

17
| username: songxuecheng | Original post link

You can refer to this to configure persistence, and then observe.

18
| username: hygame | Original post link

The index used by the execution plan is fixed, but I found that a full scan was used.

19
| username: Meditator | Original post link

Can you capture a bit more of this image?

20
| username: h5n1 | Original post link

Try a different approach: instead of specifying which index to use with SPM, specify which incorrect indexes not to use with IGNORE_INDEX(t1_name, idx1_name [, idx2_name …]).