order by 原理与优化

发布时间：2022-03-25 23:45:00 所属栏目：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 。

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

mysql重创某个用户账号	Linux运维定会的MySQL
MySQL视图存储步骤与	MySQL的server_uuid取