MySQL范围查询优化的场景实例详解

目录

• 思考题
• 范围查询-基础
• 场景一: 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)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。

例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候，优化器会 帮我们调整where后a,b,c的顺序，让我们用上索引。

阿里巴巴Java 开发手册

【强制】 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。

说明： 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

场景二: a > 1 and b = 2

如果sql为

SELECT * FROM table WHERE a > 1 and b = 2;

如何建立索引?

如果此题回答为对(a,b)建立索引，那都可以回去等通知了。

此题正确答法是，对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。

如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。

场景三:a > 1 and b = 2 and c > 3

如果sql为

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;

如何建立索引? 此题回答也是不一定，(b,a)或者(b,c)都可以，要结合具体情况具体分析。

拓展一下

SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3;

怎么建索引？嗯，大家一定都懂了！

场景四: a > 1 ORDER BY b

SELECT * FROM `table` WHERE a = 1 ORDER BY b;

如何建立索引？ 这还需要想？一看就是对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！ 那么

SELECT * FROM `table` WHERE a > 1 ORDER BY b;

如何建立索引？

对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。

拓展一下

SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

怎么建索引?

场景五: a IN (1,2,3) and b > 1

SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;

如何建立索引？

还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

拓展一下

SELECT * FROM `table` WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;

如何建立索引？此时c排序是用不到索引的。

总结

尽可能将范围查询转换成“等值”查询，如 “a>1 and a<5 and b>10” 可以写成“a in (1,2,3,4,5) and b > 10”，然后设置索引为 idx(a,b)。

将“等值”条件放在最左边，按最左匹配就可以命中索引。

参考链接1

参考链接2

到此这篇关于MySQL范围查询优化的文章就介绍到这了,更多相关MySQL范围查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！