[var]
order by满足两种情况,会使用 index 方式排序:
- order by语句使用索引最左前列(最左匹配法则)
- where子句和order by子句条件列组合满足最左匹配法则(where条件使用索引的最左前缀为常量)
下面给出几个实例来讲明,以下所示我们创建表并为其创建组合索引(c1,c2,c3)。
CREATE TABLE `testc` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`c1` varchar(100) DEFAULT NULL,
`c2` varchar(100) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
`c4` varchar(100) DEFAULT NULL,
`c5` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `testc_c1_IDX` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
[var]
# c1 c2满足最左匹配法则
explain select * from testc where c1=’a1′ order by c2
# 与上面等价
explain select * from testc where c1=’a1′ order by c2,c3
key_len
标明查找用到了索引 c1
,Extra中是Using index condition
没有同时出现using where ,表明 c2 索援用来读取数据而非履行查找动作。
MySQL Innodb下的B+树本身就是多路平衡树,那末索引换句话就是排好序的快速查找数据结构。如果order by用到了索引且排序和索引次序一样,那末无疑效果是最好的。
[var]
以下所示,缺少了c2,order by不满足最左匹配法则。
explain select * from testc where c1=’a1′ order by c3
可以看到Extra中Using index condition; Using filesort
说明虽然where可以用到索引(单独c1满足最左匹配),但是排序不满足,故而出现了filesort。
[var]
以下c1不在,那末很明显不管查找或者排序都用不到索引。
explain select * from testc where c2=’a2′ order by c3
这里Extra是Using where; Using filesort
,说明通过where子句过滤结果,然后对结果进行文件排序。
[var]
以下所示,中间c2是个范围搜索,那末其后索引将失效也就是order by c3没法与where连接满足最左匹配法则。
explain select * from testc where c1=’a1′ and c2 > ‘a2’ order by c3
以下图所示,这里type = range
,ken_len表示用到了 c1,c2索引。Extra是Using index condition; Using filesort
表示查询用到了索引但是没法利用索引完成的排序操作。
这类情况怎么优化呢?order by c2,c3
!这样就能够保证索引排序而不需要filesort。
explain select * from agriculture.testc where c1=’a1′ and c2 > ‘a2’
order by c2,c3
[var]
以下所示,order by的次序没有与索引次序保持一致。这里Extra为Using index condition; Using filesort
。
explain select * from testc where c1=’a1′ order by c3,c2
[var]
前面几个都是select *
,这里查找索引列。
没有where,order by满足全值匹配,select查询的数据是索引列。
explain select c1 from testc order by c1, c2,c3
这里Extra中只有Using index;
没有where,order by 大哥丢失,select查询的数据是索引列。
explain select c1 from testc order by c2,c3
这里Extra中是Using index; Using filesort
。
这里Extra信息为Using where; Using index; Using filesort
。
explain select c1 from testc where c1=’a1′ order by c3,c2
[var]
filesort有两种机制:双路排序和单路排序。双路排序简单来说就是两次扫描磁盘,终究得到数据。单路排序则是只需要读取一次,也就是一次磁盘IO。
双路排序
MySQL4.1之前是使用双路排序,读取行指针和order by列,对他们进行排序,然后扫描已排序好的列表,依照列表中的值重新从列表中读取对应的数据输出(可以理解为从磁盘读取排序字段,在buffer进行排序,然后再从磁盘读取其他字段)。
取一批数据要进行两次磁盘IO,这是很耗时的。故而在MySQL4.1以后,出现了第二种改进的算法,也就是单路排序。
单路排序
从磁盘读取查询需要的所有列,依照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出。它的效力更快一点,避免了第二次读取数据,并且把随机IO变成了顺序IO。但是其会使用更多的空间,由于其缓存了数据在内存中。
单路的问题
可能取出的数据大小超过了sort_buffer的容量,致使每次只能取sort_buffer容量大小的数据进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小…从而屡次IO(可能比双路更多)。
可以尝试增大sort_buffer_size参数的设置或max_length_for_sort_data参数的设置。
总结
order by时select * 是一个大忌,应当是查询需要的字段。
当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时,会用改进后的算法–单路排序,否则使用双路排序。
两种算法的数据都有可能超越sort_buffer的容量,超越以后会创建tmp文件进行合并排序致使屡次IO。特别对单路排序来讲风险更大,所以需要适当调剂sort_buffer的容量。
提高max_length_for_sort_data会增加使用单路排序算法的几率。但是如果设置的太高,数据总容量超过sort_buffer的几率就增大,明显症状是磁盘IO高,CPU使用率低。
[var]
前面提到的规则针对group by均适用,group by 实质是先排序后分组,遵照索引建的最好左前缀。当没法使用索引时,增大max_length_for_sort_data和sort_buffer参数的值。
需要注意的是where优先级高于having,能写在where限定的条件尽可能不要通过having。
到此这篇关于MySQL order by与group by查询优化实现详解的文章就介绍到这了,更多相关MySQL order by与group by内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:MySQL order by与group by查询优化实现详解
https://www.wanzijz.com/view/61016.html