为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【TiDB 版本】
v4.0.11
【问题描述】
有两张表 t_wrong_item(数据量2亿),t_question_knowledge(100万),
两表关联查询,关联字段和查询字段都有索引
SELECT tqk.knowledge_id
FROM t_wrong_item twi, t_question_knowledge tqk
WHERE
twi.student_id = 110655225744
and TWI.question_id = tqk.question_id;
执行计划如下,小表走了全表扫描,耗时一秒多
Projection_7,1386490.00,16,root,"","time:1.09s, loops:2, Concurrency:4",exam.t_question_knowledge.knowledge_id,9.95 KB,N/A
└─MergeJoin_8,1386490.00,16,root,"","time:1.09s, loops:2","inner join, left key:exam.t_question_knowledge.question_id, right key:exam.t_wrong_item.question_id",83.6 KB,0 Bytes
├─IndexReader_24(Build),1974026.70,8,root,"","time:1.12ms, loops:2, cop_task: {num: 1, max: 1.09ms, proc_keys: 8, rpc_num: 1, rpc_time: 1.08ms, copr_cache: disabled}",index:IndexRangeScan_23,567 Bytes,N/A
│ └─IndexRangeScan_23,1974026.70,8,cop[tikv],"table:twi, index:logic(student_id, question_id)","tikv_task:{time:0s, loops:1}","range:[110655225744,110655225744], keep order:true",N/A,N/A
└─IndexReader_22(Probe),1109192.00,1109198,root,"","time:853.1ms, loops:1086, cop_task: {num: 2, max: 852.1ms, min: 439.4ms, avg: 645.8ms, p95: 852.1ms, max_proc_keys: 737355, p95_proc_keys: 737355, tot_proc: 984ms, tot_wait: 2ms, rpc_num: 2, rpc_time: 1.29s, copr_cache: disabled}",index:IndexFullScan_21,67.7 MB,N/A
└─IndexFullScan_21,1109192.00,1109198,cop[tikv],"table:tqk, index:logic(question_id, knowledge_id)","tikv_task:{proc max:473ms, min:251ms, p80:473ms, p95:473ms, iters:1093, tasks:2}",keep order:true,N/A,N/A
手工指定执行计划后,执行计划如下,耗时10ms左右
SELECT /*+INL_JOIN(tqk)*/tqk.knowledge_id
FROM t_wrong_item twi, t_question_knowledge tqk
WHERE
twi.student_id = 110655225744
and TWI.question_id = tqk.question_id;
执行计划如下:
IndexJoin_10,1386490.00,16,root,"","time:1.5ms, loops:2, inner:{total:1.03ms, concurrency:4, task:1, construct:26.4µs, fetch:998µs, build:8.84µs}, probe:8.82µs","inner join, inner:IndexReader_9, outer key:exam.t_wrong_item.question_id, inner key:exam.t_question_knowledge.question_id, equal cond:eq(exam.t_wrong_item.question_id, exam.t_question_knowledge.question_id)",45.6 KB,N/A
├─IndexReader_14(Build),1974026.70,8,root,"","time:465.1µs, loops:3, cop_task: {num: 1, max: 483.6µs, proc_keys: 8, rpc_num: 1, rpc_time: 472.3µs, copr_cache: disabled}",index:IndexRangeScan_13,563 Bytes,N/A
│ └─IndexRangeScan_13,1974026.70,8,cop[tikv],"table:twi, index:logic(student_id, question_id)","tikv_task:{time:0s, loops:1}","range:[110655225744,110655225744], keep order:false",N/A,N/A
└─IndexReader_9(Probe),0.70,16,root,"","time:927.8µs, loops:2, cop_task: {num: 1, max: 850.7µs, proc_keys: 16, tot_proc: 1ms, rpc_num: 1, rpc_time: 839.3µs, copr_cache: disabled}",index:IndexRangeScan_8,1.31 KB,N/A
└─IndexRangeScan_8,0.70,16,cop[tikv],"table:tqk, index:logic(question_id, knowledge_id)","tikv_task:{time:1ms, loops:1}","range: decided by [eq(exam.t_question_knowledge.question_id, exam.t_wrong_item.question_id)], keep order:false",N/A,N/A
建表语句如下(删除了不必要的字段)
create table t_question_knowledge
(
question_knowledge_id bigint auto_increment primary key,
question_id varchar(64) not null,
knowledge_id varchar(64) not null,
constraint logic unique (question_id, knowledge_id)
);
create index t_question_knowledge_knowledge_id_index on abc.t_question_knowledge (knowledge_id);
create table t_wrong_item
(
wrong_item_id bigint auto_increment primary key,
student_id bigint not null,
question_id varchar(64) not null,
constraint logic unique (student_id, question_id)
);
stats_healthy
是正常的,一个98,一个96;
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。