我有一个带有多列索引(author_id,reply_time,id)的文章表。
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id>85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id<85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id>85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id<85669107 order by reply_time
将使用索引条件推送(说明输出的“额外”字段为“使用索引条件”)
但是,以下所有查询将不使用索引条件推送(explain输出的“ extra”字段为“ Using where”)
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id>85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id<85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id>85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id<85669107 order by reply_time desc
MySQL是否仅按索引顺序使用索引条件推送?
author_id, reply_time, id
除非您确定id
相同会有多个s,否则使用索引by几乎没有意义author_id, reply_time
。该指数不会比author_id, reply_time
单独存在更好。
您最好使用两个索引,一个索引一个索引,author_id, reply_time
另一个索引一个索引,author_id, id
计划者将使用最有可能(从收集到的统计信息中)的索引,该索引将呈现较少数量的必须顺序过滤的行。
不管排序是升序还是降序都是无关紧要的,计划者可以利用索引进行排序,也可以将索引用于升序或降序。但是有时它根本无法使用它,或者使用它对行集进行排序最昂贵。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句