• 售前

  • 售后

热门帖子
入门百科

Mysql优化order by语句的方法详解

[复制链接]
教风三应博 显示全部楼层 发表于 2021-10-26 14:06:26 |阅读模式 打印 上一主题 下一主题
本篇文章我们将相识ORDER BY语句的优化,在此之前,你须要对索引有根本的相识,不相识的老小爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。
MySQL中的两种排序方式
1.通过有序索引顺序扫描直接返回有序数据
因为索引的布局是B+树,索引中的数据是按照一定顺序进行排列的,以是在排序查询中假如能使用索引,就能制止额外的排序利用。EXPLAIN分析查询时,Extra显示为Using index。
2.Filesort排序,对返回的数据进行排序
所有不是通过索引直接返回排序结果的利用都是Filesort排序,也就是说进行了额外的排序利用。EXPLAIN分析查询时,Extra显示为Using filesort。
ORDER BY优化的焦点原则
只管镌汰额外的排序,通过索引直接返回有序数据。
ORDER BY优化实战
用于实验的customer表的索引情况:

首先要留意:
MySQL一次查询只能使用一个索引,假如要对多个字段使用索引,创建复合索引。
ORDER BY优化
1.查询的字段,应该只包罗此次查询使用的索引字段和主键,别的的非索引字段和索引字段作为查询字段则不会使用索引。
只查询用于排序的索引字段,可以使用索引排序:
  1. explain select store_id,email from customer order by store_id,email;
复制代码

但是要留意,排序字段在多个索引中,无法使用索引排序,查询一次只能使用一个索引:
  1. explain select store_id,email,last_name from customer order by store_id,email,last_name;
复制代码

只查询用于排序的索引字段和主键,可以使用索引排序:
画外音:MySQL默认的InnoDB引擎在物理上接纳聚集索引这种方式,按主键进行搜索,以是InnoDB引擎要求表必须有主键,纵然没有显式指定主键,InnoDB引擎也会生成唯一的隐式主键,也就是说索引中肯定有主键。
  1. explain select customer_id,store_id,email from customer order by store_id,email;
复制代码

查询用于排序的索引字段和主键之外的字段,不会使用索引排序:
  1. explain select store_id,email,last_name from customer order by store_id,email;
复制代码
  1. explain select * from customer order by store_id,email;
复制代码

WHERE + ORDER BY 优化
1.排序字段在多个索引中,无法使用索引排序
排序字段在多个索引(不在同一个索引)中,无法使用索引排序:
  1. explain select * from customer where last_name='swj' order by last_name,store_id;
复制代码

画外音:当排序字段不在同一个索引时,无法满足在一颗B+树中完成排序,必须再进行一次额外的排序
排序字段在一个索引中,而且WHERE条件和ORDER BY使用雷同的索引,可以使用索引排序:
  1. explain select * from customer where last_name='swj' order by last_name;
复制代码

当然组合索引也可以使用索引排序:
留意字段store_id,email在一个组合索引中
  1. explain select * from customer where store_id = 5 order by store_id,email;
复制代码

2.排序字段顺序与索引列顺序不一致,无法使用索引排序
画外音:这条是针对组合索引而言的,我们都知道使用组合索引须要要遵循最左原则,WHERE子句必须有索引中第一列,虽然ORDER BY子句没有这个要求,但是也要求排序字段顺序和组合索引列顺序匹配。我们平常在使用组合索引的时候,一定要养成按照组合索引列顺序誊写的好风俗。
排序字段顺序与索引列顺序不一致,无法使用索引排序:
  1. explain select * from customer where store_id > 5 order by email,store_id;
复制代码

应该确保排序字段顺序与索引列顺序一致,如许可以使用索引排序:
  1. explain select * from customer where store_id > 5 order by store_id,email;
复制代码

ORDER BY子句不要求必须索引中第一列,没有仍旧可以使用索引排序。但是有个前提条件,只有在等值过滤时才可以,范围查询时不可以:
  1. explain select * from customer where store_id = 5 order by email;
复制代码
  1. explain select * from customer where store_id > 5 order by email;
复制代码

画外音:


其原因实在也很简朴,范围查询时,第一列a肯定是排序好的(默认是升序),而第二个字段b实在就不是排序的了。但是假如a字段有雷同的值时,那么b字段就是排序的了。以是假如是范围查询,就只能对b做一次额外的排序。
3.起落序不一致,无法使用索引排序
ORDER BY排序字段要么全部正序排序,要么全部倒序排序,否则无法使用索引排序。
  1. explain select * from customer where store_id > 5 order by store_id,email;
复制代码
  1. explain select * from customer where store_id > 5 order by store_id desc,email desc;
复制代码
  1. explain select * from customer where store_id > 5 order by store_id desc,email asc;
复制代码

总结:
上面的优化实在可以汇总为:WHERE条件和ORDER BY使用雷同的索引,而且ORDER BY的顺序和索引顺序雷同,而且ORDER BY的字段都是升序大概降序。否则肯定须要额外的排序利用,就会出现Filesort。
Filesort优化
通过创建合适的索引能够镌汰Filesort的出现,但是在某些情况下,无法完全让Filesort消散,此时只能想办法加速Filesort的利用。
Filesort的两种排序算法:
1.两次扫描算法
首先根据条件取出排序字段和行指针信息,之后在排序区sort buffer中排序。这种排序算法须要访问两次数据,第一次获取排序字段和行指针信息,第二次根据行指针获取记录,第二次读取利用大概会导致大量随即I/O利用。优点是排序的时候内存开销较小。
2.一次扫描算法
一次性取出满足条件的行的所有字段,然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比力大,但是排序服从比两次扫描算法要高。
根据两种排序算法的特性,适当加大系统变量max_length_for_sort_data的值,能够让MySQL选择更优化的Filesort排序算法。而且在誊写SQL语句时,只使用须要的字段,而不是SELECT * 所有的字段,如许可以镌汰排序区的使用,进步SQL性能。
总结
以上所述是小编给各人先容的Mysql优化order by语句的方法详解,希望对各人有所帮助,假如各人有任何疑问请给我留言,小编会实时复兴各人的。在此也非常感谢各人对脚本之家网站的支持!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作