【概述】 sql语句,在加上limit 0,1后,走了全表扫描.
【现象】
- 两张表
t_exam_item,t_exam_paper作联表查询,ep的exam_paper_id为主键,ei有联合索引(exam_id,paper_id),read_status上没有索引 - 查看执行计划
explain
select 1
from t_exam_item ei, t_exam_paper ep
where
ei.exam_id = ep.exam_id
and ei.paper_id = ep.paper_id
AND ep.exam_paper_id = 184114
and ei.read_status = 0
limit 0,1;
执行计划如下:
estRows为3000,但实际执行时整个表都扫描到了
实际执行结果(
explain analyze):
将limit去掉后,执行计划如下:
estrows为52W
强制使用/*+INL_JOIN(ei)*/后,执行计划与limit去掉后类似
explain
select /*+INL_JOIN(ei)*/1
from t_exam_item ei, t_exam_paper ep
where
ei.exam_id = ep.exam_id
and ei.paper_id = ep.paper_id
AND ep.exam_paper_id = 184114
and ei.read_status = 0
limit 0,1;
【问题】
单从执行计划来看,全表扫描的estRows为3000,远小于使用索引的52W,从而选择了全表扫描,但3000的estRows与实际的actRows完全不符,当limit 0,20,estRows变成了5W,大概是期望扫描3000行可以得到一条结果;
从现象看我猜测可能是只用了read_status = 0作条件,t_exam_item中每3000行有一条read_status为0的数据,exam_id和paper_id的条件被忽略了.
这里想了解实际造成的原因是什么,有没有什么方法去解决或临时规避这个问题.
【TiDB 版本】
v5.0.3
【补充信息】
- analyze table t_exam_item已经执行过了,但没什么用
- 之前的版本是v4.0.12,没有遇到这样的问题.
- 加上order by 条件后,也会走索引.
【附件】 相关日志及配置信息
- TiUP Cluster Display 信息
- TiUP CLuster Edit config 信息
server_configs:
tidb:
binlog.enable: true
binlog.ignore-error: true
compatible-kill-query: true
log.level: warn
log.slow-threshold: 200
mem-quota-query: 1073741824
oom-use-tmp-storage: true
performance.max-procs: 0
performance.max-txn-ttl: 7200000
performance.txn-entry-size-limit: 67108864
performance.txn-total-size-limit: 8073741824
tikv:
log-level: warn
raftstore.raft-entry-max-size: 64MB
raftstore.sync-log: false
server.max-grpc-send-msg-len: 1048576000
两张表的表结构:
create table t_exam_item
(
exam_item_id bigint auto_increment comment '考试学生明细Id'
primary key,
exam_id bigint not null comment '考试Id',
school_id bigint not null comment '学校id',
class_id bigint not null comment '班级id',
student_id bigint not null comment '学生id',
paper_id bigint not null comment '试卷ID',
course_id bigint not null comment '课程id',
question_number int(4) not null comment '试卷试题编号',
structure_number varchar(128) not null comment '前台试卷结构显示的编号',
read_type int(2) not null comment '是否需要老师批阅标识，冗余自试卷结构表（0：不需要批阅，1系统批阅，2老师批阅）',
read_status tinyint default 0 not null comment '是否已经批改， 0 未批改， 1 已批改',
recognition_value varchar(50) not null comment '客观题图像识别后反馈的结果，主观题为空',
score_value decimal(5,1) not null comment '分值',
finally_score decimal(10,2) not null,
save_file_url varchar(800) null comment '题目对应的学生答题卡图片路径，如果有多个，用","隔开',
creator_id bigint not null comment '创建人ID',
creator_name varchar(20) not null comment '创建人',
create_date_time datetime not null comment '创建时间',
modifier_id bigint not null comment '修改人ID',
modifier_name varchar(20) not null comment '修改人',
modify_date_time datetime not null comment '修改时间',
constraint logic
unique (exam_id, paper_id, question_number, student_id)
)
comment '考试学生成绩明细表';
create index examCourseIdx
on t_exam_item (exam_id, course_id);
create index idx_examid_paperid_schoolid_readtype_studentid
on t_exam_item (exam_id, paper_id, read_type, school_id, student_id);
create index t_exam_item_student_id_exam_id_paper_id_index
on t_exam_item (student_id, exam_id, paper_id);
create table t_exam_paper
(
exam_paper_id bigint auto_increment comment '考试试卷ID'
primary key,
exam_id bigint not null comment '考试表主键',
course_id bigint not null comment '课程id',
course_name varchar(20) not null comment '课程名',
paper_id bigint not null comment '试卷ID',
paper_name varchar(500) not null comment '试卷名称',
full_mark decimal(10,1) not null comment '考试科目满分分数,非空',
arts_science tinyint(2) default 0 not null comment '分科标示（0：未分科，1：文科, 2：理科) ,文理数，文理综才会是1或2，其它为0',
exam_paper_status tinyint(2) not null comment '考试科目状态 1：上传答案；5：答题切割；10：阅卷安排；15：阅卷中；20：阅卷完成；',
creator_id bigint not null comment '创建人ID',
creator_name varchar(20) not null comment '创建人',
create_date_time datetime not null comment '创建时间',
modifier_id bigint not null comment '修改人ID',
modifier_name varchar(20) not null comment '修改人',
modify_date_time datetime not null comment '修改时间',
constraint logic
unique (exam_id, paper_id)
);
create index t_exam_paper_paper_id_index
on t_exam_paper (paper_id);
stat信息
t_exam_paper (3.7 MB)
t_exam_item (3.5 MB)