tidb 执行计划不稳定问题

hygame · 2022 年8 月 3 日 01:32

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【概述】场景 + 问题概述
tidb5.4 3tidb-server 3pd 3kv 1tiflash

【应用框架及开发适配业务逻辑】

【背景】做过哪些操作

【现象】业务和数据库现象

【问题】当前遇到的问题
现在我们有一张大表，表结构如下：
CREATE TABLE parking_record (
id bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘自增ID’,
upload_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘岗亭上传的id’,
record_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘停车记录类型76系统77临停’,
card_car_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘卡id’,
temp_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘临时ID’,
entrance_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘入场时间’,
entrance_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘入场图片’,
entrance_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场岗亭id’,
exit_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘出场时间’,
exit_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘出场图片’,
exit_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场岗亭id’,
receivable_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘应收金额’,
actual_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实收金额’,
admin_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘管理员ID’,
ontime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘停车记录岗亭创建时间’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘停车记录岗亭更新时间’,
parking_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘车场ID’,
park_name varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘车场名称’,
area_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘区域ID’,
group_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘集团ID’,
is_fixed int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘449=特殊放行’,
state int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘111含义不明,449=特殊放行,452=特殊金额放行，0=默认，其他=cartypeid’,
car_owner_parking_fixed_location_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘长租费率ID’,
exit_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场路口’,
entrance_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场路口’,
synid varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘主键synid’,
free_time int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘免费时长’,
plate_number varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘车牌号’,
upload_insert datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘云端创建时间’,
upload_update datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘云端更新时间’,
remarks varchar(500) NOT NULL DEFAULT ‘’ COMMENT ‘备注’,
online_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘电子支付金额’,
entrance_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场置信度’,
exit_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场置信度’,
entrance_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场车牌颜色 0-无 1-蓝 2-黑 3-黄 4-白 5-绿’,
exit_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场车牌颜色’,
operate_user_id int(11) NOT NULL DEFAULT ‘-1’,
is_finish int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘是否终结0为未终结1为终结’,
motor_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘0-非机动车，1-机动车停车记录表’,
motor_type_detail tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘机动车详细类型’,
brand varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘汽车品牌’,
character_code varchar(250) NOT NULL DEFAULT ‘’ COMMENT ‘特征码’,
status tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘状态（1：有效；2：无效）’,
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=‘停车记录’
问题sql如下：
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
大部分执行计划会走到idx_parking_id_update_time_status_exit_time索引，效率会很高，但是有时候执行计划会走到idx_parkingid_exittime索引，一下子就变成慢sql了，请问是什么原因引起的，我看了一下这张表的健康度是96%，explain 和axplain analyze同一个sql都会使用正确的执行计划

+---------------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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  |
+---------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+

【业务影响】

【TiDB 版本】
5.4
【附件】相关日志及监控（https://metricstool.pingcap.com/)

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

ddhe9527 · 2022 年8 月 3 日 01:42

看起来可能与update_time的范围有关。如果范围很大，走idx_parking_id_update_time_status_exit_time回表代价高，优化器可能选择使用idx_parkingid_exittime索引。如果update_time范围不大，则更倾向于idx_parking_id_update_time_status_exit_time索引。具体可以测试一下

hygame · 2022 年8 月 3 日 01:46

您好，我看了，时间范围不大，就8个小时的范围，而且同一个sql，条件都一样，explain和explain ANALYZE就会使用idx_parking_id_update_time_status_exit_time，而dashboard里面报出来的执行计划使用的是idx_parkingid_exittime，且我们查询这个之前会有一个count（*）操作，条件是一样的，用作分页，不涉及回表，也用到了idx_parkingid_exittime索引，而实际explain和explain ANALYZE就会使用idx_parking_id_update_time_status_exit_time

hygame · 2022 年8 月 3 日 01:48

ddhe9527 · 2022 年8 月 3 日 02:04

这两个索引的字段都能被这个SQL完全匹配上，估计优化器评估的这两个索引的代价相差很小，从而引起了执行计划不稳定。下面建议二选一：
1、调整索引idx_parking_id_update_time_status_exit_time的字段顺序，parking_id保持第一位，将exit_time放在第二位，其余字段往后放，然后删除冗余索引idx_parkingid_exittime，让优化器不纠结
2、用SPM绑定执行计划到现有idx_parking_id_update_time_status_exit_time索引上
https://docs.pingcap.com/zh/tidb/stable/sql-plan-management

hygame · 2022 年8 月 3 日 02:05

如果调整顺序，exit_time放第二位，不是等值查询了，后面的值就走不了索引了吧

ddhe9527 · 2022 年8 月 3 日 02:14

<>走不走索引与字段是否NOT NULL有关，调整顺序后走索引的性能应该在原idx_parking_id_update_time_status_exit_time和idx_parkingid_exittime之间

这里纠正一个，TiDB里面不等于是否走索引和NOT NULL应该没关系(可以走)，我测试确认了一下。Oracle有关，MySQL也无关

hygame · 2022 年8 月 3 日 07:53

您好，这个sql我绑定了执行计划，但是偶尔执行1min，我看执行计划走的是IndexFullScan，但是我explain ANALYZE，走的是IndexRangeScan，这个是什么原因

hygame · 2022 年8 月 3 日 07:54

ddhe9527 · 2022 年8 月 3 日 08:46

dashboard上看到走IndexFullScan的SQL，是否具有很大范围的update_time，看一下是否具有相关性，我看你截图的范围就很大了

hygame · 2022 年8 月 3 日 08:48

不大吧

ddhe9527 · 2022 年8 月 3 日 09:24

噢噢，是我看错了，第一个时间是exit_time，后面那一对才是update_time。

这个执行计划确实有问题，TiDB的hint目前来看还无法控制使用index range scan还是index full scan，binding也无法绑PLAN_DIGEST，只能绑SQL文本，我找版主群大佬帮你看看

songxuecheng · 2022 年8 月 3 日 09:37

tidb_enable_column_tracking 查看下这个变量的值。

Kamner · 2022 年8 月 3 日 09:39

可以复现吗？是不是update_time 两个过滤条件时间一样就会出现 index full scan ?

hygame · 2022 年8 月 3 日 09:40

hygame · 2022 年8 月 3 日 09:40

复现不了，我自己执行怎么执行都走index_range_scan

songxuecheng · 2022 年8 月 3 日 09:46

https://docs.pingcap.com/zh/tidb/dev/statistics#analyze-配置持久化

可以参考这个配置一下持久化，然后在观察观察。

hygame · 2022 年8 月 3 日 09:47

执行计划使用到的索引固定了，但是我发现使用了full scan

Meditator · 2022 年8 月 3 日 10:24

这个图能截取全一点吗？

h5n1 · 2022 年8 月 3 日 14:36

换种方式试试，spm不指定用哪个索引，而是指定不用哪些不正确的索引 IGNORE_INDEX(t1_name, idx1_name [, idx2_name …])