order by 原理与优化
发布时间：2022-03-25 21:21:51 所属栏目：MySql教程 来源：互联网
导读：简介 偏向于业务的(MySQL)DBA或者业务的开发者来说，order by 排序是一个常见的业务功能，将结果根据指定的字段排序，满足前端展示的需求。然而排序操作也是经常出现慢查询排行榜的座上宾。本文将从原理和实际案例优化，order by 使用限制等几个方面来逐步了
|
简介 偏向于业务的(MySQL)DBA或者业务的开发者来说，order by 排序是一个常见的业务功能，将结果根据指定的字段排序，满足前端展示的需求。然而排序操作也是经常出现慢查询排行榜的座上宾。本文将从原理和实际案例优化，order by 使用限制等几个方面来逐步了解order by 排序。 原理 在了解order by 排序的原理之前，强烈安利两篇关于排序算法的文章 《归并排序的实现》 《经典排序算法》。MySQL 支持两种排序算法，常规排序和优化，并且在MySQL 5.6版本中 针对order by limit M,N 做了特别的优化，这里列为第三种。 常规排序 a 从表t1中获取满足WHERE条件的记录 b 对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer c 如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法) d 若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的 e 循环执行上述过程，直到所有满足条件的记录全部参与排序 f 扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3) g 将获取的结果集返回给用户。 优化 通过上面的原理分析，我们知道排序的本质是通过一定的算法(耗费cpu 运算,内存,临时文件IO)将结果集变成有序的结果集。如何优化呢？答案是分两个方面利用索引的有序性(MySQL的B+ 树索引是默认从小到大递增排序)减少排序,最好的方式是直接不排序。 create table t1( id int not null primary key , key_part1 int(10) not null, key_part2 varchar(10) not null default '', key_part3 key idx_kp1_kp2(key_part1,key_part2,key_part4)， key idx_kp3(id) ) engine=innodb default charset=utf8 以下种类的查询是可以利用到索引 idx_kp1_kp2的 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 温馨提示 ，各位看官要辩证的看待官方给的例子，自己多动手实践。 无法利用到索引排序的情况，其实我觉得这是本文的重点，对于广大开发同学而言，记住那种不能利用索引排序会更简单些。 1 最常见的情况 用来查找结果的索引(key2) 和 排序的索引(key1) 不一样,where a=x and b=y order by id; SELECT * FROM t1 WHERE key2=constant ORDER BY key1; 2 排序字段在不同的索引中，无法使用索引排序 SELECT * FROM t1 ORDER BY key1,key2; 3 排序字段顺序与索引中列顺序不一致，无法使用索引排序，比如索引是 key idx_kp1_kp2(key_part1,key_part2) SELECT * FROM t1 ORDER BY key_part2, key_part1; 4 order by中的升降序和索引中的默认升降不一致，无法使用索引排序 SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; 5 ey_part1是范围查询，key_part2无法使用索引排序 SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2; 5 rder by和group by 字段列不一致 SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4; 6 索引本身是无序存储的，比如hash 索引，不能利用索引的有序性。 7 order by字段只被索引了前缀 ，key idx_col(col(10)) select * from t1 order by col ; 8 对于还有join的关联查询，排序字段并非全部来自于第一个表，使用explain 查看执行计划第一个表 type 值不是const 。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
