MYSQL ORDER BY Optimization
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL ORDER BY Optimization,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4789字,纯文字阅读大概需要7分钟。
内容图文
![MYSQL ORDER BY Optimization](/upload/InfoBanner/zyjiaocheng/449/176d43696c154279a1c75e8809cbe7d0.jpg)
某些情况下,MYSQL可以使用index排序而避免额外的sorting.
即使order by语句列不能准确的匹配index,只要没有index中(不在order by的列)在where语句以常量形式出现。(最左前缀)
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC; SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC; SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
某些情况下,依旧使用Index来查找匹配where子句的行,但MYSQL不用index来解决order by:
1:order by子句中使用不同indexes:
SELECT * FROM t1 ORDER BY key1, key2;
2:使用不连续的index部分(联合key的非最左前缀)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
3:混合使用asc 和desc:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
4:获得数据行的index(where子句中)和Order by 中使用的不一样:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5:order by子句中使用index的表达式:
SELECT * FROM t1 ORDER BY ABS(key); SELECT * FROM t1 ORDER BY -key;
6:join操作时,order by子句的列不全是第一个非 const表;
7: 不同的order by,group by表达式:
8:只对order by子句列的前缀加index,这种情况下index不能解决sort. e.g : order by列包含一个char(20)类型,但是只对前10bytes加index;
9:table index无序,the index of hash(memory表);
一个index是否排序可用可能受列的别名影响,表t1列 a 为索引:
可以利用index来排序:
SELECT a FROM t1 ORDER BY a;
不能:
SELECT ABS(a) AS a FROM t1 ORDER BY a;
该语句中,order by引用的是列a, select子句的列名也是a, 但是他是别名,引用的是abs(a);
下面的语句中,order by引用列名和select list中的列名不一样,但是select用到a,index sort可以使用(该语句的排序结果和以abs(a)排序的完全不一样)
SELECT ABS(a) AS b FROM t1 ORDER BY a;
默认的,mysql对所有的组col1,col2(group by col1,col2)排序,如果一个查询包含group by但是想避免sort的负载,可以压制排序通过order by null.
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
依赖隐式的group by 排序在mysql5.6中被舍弃。更可取的是使用准确的order by子句。
MYSQL有两种filesort算法来获得结果。原始的方法只使用order by中的列list. 改写过的方法不仅仅使用order by子句中的列,而是查询中所使用到的列。
优化器选择哪个filesort算法?正常情况下使用第二种(BLOB TEXT等大对象列外),两种算法,都使用到sort_buffer_size系统变量:
原始的filesort算法工作:
1:根据key值或者scan all the table(where条件)读取所有满足条件的行,跳过不满足where子句的行。
2:对于每一行,存储(key value, row id)对在sort buffer中。
3: 如果所有上述对能全部放在sort buffer中,临时文件不会被创建,否则,当sort buffer满时,内存中执行quicksort并且把结果写进临时文件中,保存一个指针执行这个 sorted block.
4:重复执行上述的过程,直到所有的行都被读取。
5:执行一个多路归并排序,把第一个文件的block转移到另外一个临时文件中。重复执行,直到第一个文件内容全部在第二个文件中。
6:一直merge buffer直到剩下2个block
7:最后一次merge,只写入rowid到结果表
8.根据排序结果中的rowid顺序读取数据。(手册中还提到了一个优化方案,但是我不认为能起到优化作用)。
该filesort中出现两次读取操作,第一次在where子句判断,另外一次是在拍完value pairs后。然而即使第一次访问是连续读取(e.g. scan all the table),但是第二次他们是随机访问(尽管key排过序了,但是行位置没有~!);
第二种filesort算法:(避开二次读,不是记录rowID,而是记录查询所使用的引用列)
1:读取满足where子句的所有行
2:对于每一行,元组记录key value和查询所引用到的列
3:当buffer满时,排序并写入临时文件
4:merge sort所有的临时文件,检索有序的行数据,直接从排过序的元组中读取需要的列而不是两次访问基表
修改后的方法,列长于原来的方法。很有可能会产生大量IO,让排序变得很慢。为了避免这个问题,优化器会所有读取列的长度小于max_length_for_sort_data系统变量,才会选择修改后的算法。
当filesort完成,explain输出中extra会有using filesort,优化器跟踪输出中filesort_summary块:
"filesort_summary": { "rows": 100, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 25192, "sort_mode": "<sort_key, additional_fields>" }
其中sort mode就说了算法:
<sort_key,rowid>表示原始的算法
<sort_key,addtitional_filed>表示是修改后的算法
为了提高排序速度,可以检查是否可以使用索引,如果不能使用:
1.增加sort_buffer_size的大小
2.增加read_rnd_buffer_size的大小
3.通过表设计减少空间占用
4.修改tmpdir目录指向专用文件系统
如果order by没有使用索引,但是有limit子句,那么优化器可能可以避免合并临时文件,直接在内存中排序
MYSQL ORDER BY Optimization
标签:
本文系统来源:http://www.cnblogs.com/onlysun/p/4535351.html
内容总结
以上是互联网集市为您收集整理的MYSQL ORDER BY Optimization全部内容,希望文章能够帮你解决MYSQL ORDER BY Optimization所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。