如何在Mysql中优化order by语句

这篇文章给大家介绍如何在Mysql中优化order by语句，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。MySQL中的两种排序方式1.通过有序索引顺序扫描直接返回有序数据因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。E开发云主机域名XPLAIN分析查询时，Extra显示为Using index。2.Filesort排序，对返回的数据进行排序所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort。ORDER BY优化的核心原则尽量减少额外的排序，通过索引直接返回有序数据。ORDER BY优化实战用于实验的customer表的索引情况：首先要注意：MySQL一次查询只能使用一个索引，如果要对多个字段使用索引，建立复合索引。ORDER BY优化1.查询的字段，应该只包含此次查询使用的索引字段和主键，其余的非索引字段和索引字段作为查询字段则不会使用索引。只查询用于排序的索引字段，可以利用索引排序：explain select store_id,email from customer order by store_id,email;但是要注意，排序字段在多个索引中,无法使用索引排序,查询一次只能使用一个索引：explain select store_id,email,last_name from customer order by store_id,email,last_name;只查询用于排序的索引字段和主键，可以利用索引排序：画外音：MySQL默认的InnoDB引擎在物理上采用聚集索引这种方式，按主键进行搜索，所以InnoDB引擎要求表必须有主键，即使没有显式指定主键，InnoDB引擎也会生成唯一的隐式主键，也就是说索引中必定有主键。explain select customer_id,store_id,email from customer order by store_id,email;查询用于排序的索引字段和主键之外的字段，不会利用索引排序：explain select store_id,email,last_name from customer order by store_id,email;explain select * from customer order by store_id,email;WHERE + ORDER BY 优化1.排序字段在多个索引中,无法利用索引排序排序字段在多个索引（不在同一个索引）中,无法利用索引排序：explain select * from customer where last_name='swj' order by last_name,store_id;画外音：当排序字段不在同一个索引时，无法满足在一颗B+树中完成排序，必须再进行一次额外的排序排序字段在一个索引中,并且WHERE条件和ORDER BY使用相同的索引,可以利用索引排序：explain select * from customer where last_name='swj' order by last_name;当然组合索引也可以利用索引排序：注意字段store_id,email在一个组合索引中explain select * from customer where store_id = 5 order by store_id,email;2.排序字段顺序与索引列顺序不一致,无法利用索引排序画外音：这条是针对组合索引而言的，我们都知道使用组合索引必要要遵循最左原则，WHERE子句必须有索引中第一列，虽然ORDER BY子句没有这个要求，但是也要求排序字段顺序和组合索引列顺序匹配。我们平常在使用组合索引的时候，一定要养成按照组合索引列顺序书写的好习惯。排序字段顺序与索引列顺序不一致,无法利用索引排序：explain select * from customer where store_id > 5 order by email,store_id;应该确保排序字段顺序与索引列顺序一致,这样可以利用索引排序：explain select * from customer where store_id > 5 order by store_id,email;ORDER BY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不可以：explain select * from customer where store_id = 5 order by email;explain select * from customer where store_id > 5 order by email;画外音：其原因其实也很简单，范围查询时，第一列a肯定是排序好的（默认是升序），而第二个字段b其实就不是排序的了。但是如果a字段有相同的值时，那么b字段就是排序的了。所以如果是范围查询，就只能对b做一次额外的排序。3.升降序不一致,无法利用索引排序ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。explain select * from customer where store_id > 5 order by store_id,email;explain select * from customer where store_id > 5 order by store_id desc,email desc;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语句就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

相关推荐: Window10下mysql 5.7.21 安装配置方法图文教程

本文记录了mysql 5.7.21 安装配置方法，分享给大家。1.下载安装包下面是官网windows系统的mysql下载地址Mysql下载地址 点进去页面如下根据你电脑的系统选择对应的mysql数据库下载即可。下载完毕后点击下载的msi文件直接进入安装步骤… …