本文整理自网络,侵删。
目录
- 思考题
- 范围查询-基础
- 场景一: a = 1 and b = 2 and c = 3
- 场景二: a > 1 and b = 2
- 场景三:a > 1 and b = 2 and c > 3
- 场景四: a > 1 ORDER BY b
- 场景五: a IN (1,2,3) and b > 1
- 总结
思考题
假设有一张订单表 order,主要包含了主键订单编码 order_no、订单状态 status、提交时间 create_time 等列,并且创建了 status 列索引和 create_time 列索引。此时通过创建时间降序获取状态为 1 的订单编码,以下是具体实现代码:
select order_no from order where status =1 order by create_time desc;
你知道其中的问题所在吗?我们又该如何优化?
解析
status和create_time单独建索引,在查询时只会遍历status索引对数据进行过滤,不会用到create_time列索引,将符合条件的数据返回到server层,在server对数据通过快排算法进行排序,Extra列会出现file sort;
应该利用索引的有序性,在status和create_time列建立联合索引,这样根据status过滤后的数据就是按照create_time排好序的,避免在server层排序
对的,为了避免文件排序的发生。因为查询时我们只能用到status索引,如果要对create_time进行排序,则需要使用文件排序filesort。
filesort是通过相应的排序算法将取得的数据在内存中进行排序,如果内存不够则会使用磁盘文件作为辅助。虽然在一些场景中,filesort并不是特别消耗性能,但是我们可以避免filesort就尽量避免。
阿里巴巴MySQL规范
【推荐】 如果有 order by 的场景,请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。
正例: where a=? and b=? order by c; 索引: a_b_c
反例: 索引如果存在范围查询, 那么索引有序性无法利用,如: WHERE a>10 ORDER BY b; 索引 a_b 无 法排序
范围查询-基础
讲联合索引,一定要扯最左匹配!
最左匹配 所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。 假设,我们对(a,b)字段建立一个索引,也就是说,你where后条件为
a = 1 a = 1 and b = 2
是可以匹配索引的。但是要注意的是~你执行
b= 2 and a =1
也是能匹配到索引的,因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。 相反的,你执行
b = 2
就匹配不到索引了。 而你对(a,b,c,d)建立索引,where后条件为
a = 1 and b = 2 and c > 3 and d = 4
那么,a,b,c三个字段能用到索引,而d就匹配不到。因为遇到了范围查询!
场景一: a = 1 and b = 2 and c = 3
如果sql为
SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
如何建立索引?
如果此题回答为对(a,b,c)建立索引,那都可以回去等通知了。
此题正确答法是,(a,b,c)或者(c,b,a)或者(b,a,c)都可以,重点要的是将区分度高的字段放在前面,区分度低的字段放后面。像性别、状态这种字段区分度就很低,我们一般放后面。
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » MySQL范围查询优化的场景实例详解
相关推荐
评论
管理员已关闭评论功能...