tidb 执行计划不稳定问题

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

【概述】 场景 + 问题概述
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/)


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

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

您好,我看了,时间范围不大,就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



这两个索引的字段都能被这个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

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

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

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

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

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


不大吧

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

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

tidb_enable_column_tracking 查看下这个变量的值。

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

image

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

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

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

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

这个图能截取全一点吗 ?

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