SELECT条件使用in会慢很多

PerfectXD · 2020 年2 月 13 日 05:49

【TiDB 版本】：3.0.3
【问题描述】：

示例表：

create table `t_example` (
  `_id` bigint(20) unsigned not null auto_increment,
  `room_id` int(10) unsigned not null,
  `begin_time` datetime not null,
  `end_time` datetime not null,
  `score_type` enum('normal','practice') default null,
  primary key (`_id`,`end_time`),
  key `idx_begintime` (`begin_time`),
  key `idx_endtime` (`end_time`)
) engine=innodb default charset=utf8 collate=utf8_bin
partition by range ( month(`end_time`) ) (
  partition p1 values less than (2),
  partition p2 values less than (3),
  partition p3 values less than (4),
  partition p4 values less than (5),
  partition p5 values less than (6),
  partition p6 values less than (7),
  partition p7 values less than (8),
  partition p8 values less than (9),
  partition p9 values less than (10),
  partition p10 values less than (11),
  partition p11 values less than (12),
  partition p12 values less than (maxvalue)
);

数据量：>1亿

查询语句1：

select room_id,begin_time,end_time
from t_example 
where _id=626308755
order by b.end_time
limit 10;

查询语句2：

select room_id,begin_time,end_time
from t_example 
where _id in (626308755)
order by b.end_time
limit 10;

为什么语句2耗时比语句1严重？

不懂就问 · 2020 年2 月 13 日 07:29

explain 对比看下两个语句的执行计划

PerfectXD · 2020 年2 月 13 日 07:51

语句1

Projection_32	10.00	root	room_id, begin_time, end_time
└─TopN_35	10.00	root	end_time:asc, offset:0, count:10
  └─Union_39	120.00	root	
    ├─Limit_45	10.00	root	offset:0, count:10
    │ └─Projection_68	9.21	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_67	9.21	root	
    │     ├─IndexScan_65	9.21	cop	table:t_example, partition:p1, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_66	9.21	cop	table:t_example, partition:p1, keep order:false
    ├─Limit_73	10.00	root	offset:0, count:10
    │ └─Projection_93	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_92	10.00	root	
    │     ├─Limit_91	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_89	72477.48	cop	table:t_example, partition:p2, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_90	10.00	cop	table:t_example, partition:p2, keep order:false, stats:pseudo
    ├─Limit_102	10.00	root	offset:0, count:10
    │ └─Projection_122	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_121	10.00	root	
    │     ├─Limit_120	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_118	146627.19	cop	table:t_example, partition:p3, index:_id, END_TIME, range:[626308755,626308755], keep order:true, stats:pseudo
    │     └─TableScan_119	10.00	cop	table:t_example, partition:p3, keep order:false, stats:pseudo
    ├─Limit_131	10.00	root	offset:0, count:10
    │ └─Projection_151	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_150	10.00	root	
    │     ├─Limit_149	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_147	146627.19	cop	table:t_example, partition:p4, index:_id, END_TIME, range:[626308755,626308755], keep order:true, stats:pseudo
    │     └─TableScan_148	10.00	cop	table:t_example, partition:p4, keep order:false, stats:pseudo
    ├─Limit_160	10.00	root	offset:0, count:10
    │ └─Projection_180	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_179	10.00	root	
    │     ├─Limit_178	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_176	146627.19	cop	table:t_example, partition:p5, index:_id, END_TIME, range:[626308755,626308755], keep order:true, stats:pseudo
    │     └─TableScan_177	10.00	cop	table:t_example, partition:p5, keep order:false, stats:pseudo
    ├─Limit_189	10.00	root	offset:0, count:10
    │ └─Projection_209	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_208	10.00	root	
    │     ├─Limit_207	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_205	146627.19	cop	table:t_example, partition:p6, index:_id, END_TIME, range:[626308755,626308755], keep order:true, stats:pseudo
    │     └─TableScan_206	10.00	cop	table:t_example, partition:p6, keep order:false, stats:pseudo
    ├─Limit_218	10.00	root	offset:0, count:10
    │ └─Projection_238	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_237	10.00	root	
    │     ├─Limit_236	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_234	146627.19	cop	table:t_example, partition:p7, index:_id, END_TIME, range:[626308755,626308755], keep order:true, stats:pseudo
    │     └─TableScan_235	10.00	cop	table:t_example, partition:p7, keep order:false, stats:pseudo
    ├─Limit_247	10.00	root	offset:0, count:10
    │ └─Projection_267	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_266	10.00	root	
    │     ├─Limit_265	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_263	31.26	cop	table:t_example, partition:p8, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_264	10.00	cop	table:t_example, partition:p8, keep order:false, stats:pseudo
    ├─Limit_277	10.00	root	offset:0, count:10
    │ └─Projection_300	9.80	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_299	9.80	root	
    │     ├─IndexScan_297	9.80	cop	table:t_example, partition:p9, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_298	9.80	cop	table:t_example, partition:p9, keep order:false
    ├─Limit_305	10.00	root	offset:0, count:10
    │ └─Projection_325	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_324	10.00	root	
    │     ├─Limit_323	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_321	89.33	cop	table:t_example, partition:p10, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_322	10.00	cop	table:t_example, partition:p10, keep order:false, stats:pseudo
    ├─Limit_334	10.00	root	offset:0, count:10
    │ └─Projection_354	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_353	10.00	root	
    │     ├─Limit_352	10.00	cop	offset:0, count:10
    │     │ └─IndexScan_350	32.74	cop	table:t_example, partition:p11, index:_id, END_TIME, range:[626308755,626308755], keep order:true
    │     └─TableScan_351	10.00	cop	table:t_example, partition:p11, keep order:false, stats:pseudo
    └─Limit_363	10.00	root	offset:0, count:10
      └─Projection_383	10.00	root	_id, room_id, begin_time, end_time
        └─IndexLookUp_382	10.00	root	
          ├─Limit_381	10.00	cop	offset:0, count:10
          │ └─IndexScan_379	10.00	cop	table:t_example, partition:p12, index:_id, END_TIME, range:[626308755,626308755], keep order:true
          └─TableScan_380	10.00	cop	table:t_example, partition:p12, keep order:false, stats:pseudo

语句2

Projection_32	10.00	root	room_id, begin_time, end_time
└─TopN_35	10.00	root	end_time:asc, offset:0, count:10
  └─Union_39	120.00	root	
    ├─Limit_44	10.00	root	offset:0, count:10
    │ └─Projection_64	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_63	10.00	root	
    │     ├─IndexScan_60	1138.92	cop	table:t_example, partition:p1, index:END_TIME, range:[NULL,+inf], keep order:true
    │     └─Selection_62	10.00	cop	in(_id, 626308755)
    │       └─TableScan_61	1138.92	cop	table:t_example, partition:p1, keep order:false
    ├─TopN_71	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_83	10.00	root	
    │   ├─TopN_82	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_80	146627.19	cop	table:t_example, partition:p2, index:_id, END_TIME, range:[626308755,626308755], keep order:false
    │   └─TableScan_81	10.00	cop	table:t_example, partition:p2, keep order:false, stats:pseudo
    ├─TopN_101	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_113	10.00	root	
    │   ├─TopN_112	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_110	146627.19	cop	table:t_example, partition:p3, index:_id, END_TIME, range:[626308755,626308755], keep order:false, stats:pseudo
    │   └─TableScan_111	10.00	cop	table:t_example, partition:p3, keep order:false, stats:pseudo
    ├─TopN_131	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_143	10.00	root	
    │   ├─TopN_142	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_140	146627.19	cop	table:t_example, partition:p4, index:_id, END_TIME, range:[626308755,626308755], keep order:false, stats:pseudo
    │   └─TableScan_141	10.00	cop	table:t_example, partition:p4, keep order:false, stats:pseudo
    ├─TopN_161	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_173	10.00	root	
    │   ├─TopN_172	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_170	146627.19	cop	table:t_example, partition:p5, index:_id, END_TIME, range:[626308755,626308755], keep order:false, stats:pseudo
    │   └─TableScan_171	10.00	cop	table:t_example, partition:p5, keep order:false, stats:pseudo
    ├─TopN_191	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_203	10.00	root	
    │   ├─TopN_202	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_200	146627.19	cop	table:t_example, partition:p6, index:_id, END_TIME, range:[626308755,626308755], keep order:false, stats:pseudo
    │   └─TableScan_201	10.00	cop	table:t_example, partition:p6, keep order:false, stats:pseudo
    ├─TopN_221	10.00	root	end_time:asc, offset:0, count:10
    │ └─IndexLookUp_233	10.00	root	
    │   ├─TopN_232	10.00	cop	end_time:asc, offset:0, count:10
    │   │ └─IndexScan_230	146627.19	cop	table:t_example, partition:p7, index:_id, END_TIME, range:[626308755,626308755], keep order:false, stats:pseudo
    │   └─TableScan_231	10.00	cop	table:t_example, partition:p7, keep order:false, stats:pseudo
    ├─Limit_254	10.00	root	offset:0, count:10
    │ └─Projection_274	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_273	10.00	root	
    │     ├─IndexScan_270	3865.01	cop	table:t_example, partition:p8, index:END_TIME, range:[NULL,+inf], keep order:true
    │     └─Selection_272	10.00	cop	in(_id, 626308755)
    │       └─TableScan_271	3865.01	cop	table:t_example, partition:p8, keep order:false
    ├─Limit_284	10.00	root	offset:0, count:10
    │ └─Projection_304	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_303	10.00	root	
    │     ├─IndexScan_300	1211.14	cop	table:t_example, partition:p9, index:END_TIME, range:[NULL,+inf], keep order:true
    │     └─Selection_302	10.00	cop	in(_id, 626308755)
    │       └─TableScan_301	1211.14	cop	table:t_example, partition:p9, keep order:false
    ├─Limit_314	10.00	root	offset:0, count:10
    │ └─Projection_334	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_333	10.00	root	
    │     ├─IndexScan_330	11045.32	cop	table:t_example, partition:p10, index:END_TIME, range:[NULL,+inf], keep order:true
    │     └─Selection_332	10.00	cop	in(_id, 626308755)
    │       └─TableScan_331	11045.32	cop	table:t_example, partition:p10, keep order:false
    ├─Limit_344	10.00	root	offset:0, count:10
    │ └─Projection_364	10.00	root	_id, room_id, begin_time, end_time
    │   └─IndexLookUp_363	10.00	root	
    │     ├─IndexScan_360	4048.83	cop	table:t_example, partition:p11, index:END_TIME, range:[NULL,+inf], keep order:true
    │     └─Selection_362	10.00	cop	in(_id, 626308755)
    │       └─TableScan_361	4048.83	cop	table:t_example, partition:p11, keep order:false
    └─Limit_374	10.00	root	offset:0, count:10
      └─Projection_394	10.00	root	_id, room_id, begin_time, end_time
        └─IndexLookUp_393	10.00	root	
          ├─IndexScan_390	1236.48	cop	table:t_example, partition:p12, index:END_TIME, range:[NULL,+inf], keep order:true
          └─Selection_392	10.00	cop	in(_id, 626308755)
            └─TableScan_391	1236.48	cop	table:t_example, partition:p12, keep order:false

发现区别在keep order上

不懂就问 · 2020 年2 月 13 日 10:45

稍等，问题正在跟进。

QBin · 2020 年2 月 13 日 11:17

麻烦执行下收集一下表的统计信息 (注意在业务低峰期操作，由于数据量比较大所以时间可能会较长) ，再执行一下两个 SQL 的 explain 。收集统计信息的方法可以参考这里：
https://pingcap.com/docs-cn/stable/reference/performance/statistics/#手动收集

PerfectXD · 2020 年2 月 13 日 14:41

我的表是一张日志表，只有select和insert，是执行analyze t_example后，再explain看下执行计划吗？，

QBin · 2020 年2 月 14 日 01:40

是的，麻烦 analyze table 之后，再执行一下两个 select

francis0407 · 2020 年2 月 28 日 03:40

这个主要是索引选择问题，在这个 SQL 里，如果用 _id = x order by b.end_time 可以直接利用 (_id, end_time) 这个索引。但是如果用 _id in (x) order by b.end_time 这种方式查询，在 3.0.x 版本中只能够用 (_id, end_time) 索引来过滤数据或者使用 (end_time) 索引来 KeepOrder。这个问题已经在 4.0 版本进行了优化，当 in 语句内只有一个值的时候，TiDB 会自动将其改写为等值条件。

system · 2022 年10 月 31 日 19:05

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。