如何优化Mysql中的order by语句

MySQL中的两种排序方法

1.通过有序索引顺序扫描直接返回有序数据

由于索引的结构是B+树，索引中的数据是按照一定的顺序排列的，所以如果可以使用索引在排序查询中，可以避免额外的排序操作。 EXPLAIN分析查询时，Extra显示为Using index。

2. Filesort排序，对返回的数据进行排序

凡是不直接通过索引返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。当EXPLAIN分析查询时，Extra显示为Using filesort。

ORDER BY优化的核心原则

最小化额外直接通过索引排序并返回有序数据。

ORDER BY优化实践

实验使用的客户表索引：

首先注意：

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;

只查询用于排序的索引字段和主键，就可以使用索引来排序：

Off-screenSound：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;

解释 select * from customer order by store_id,email;

WHERE + ORDER BY优化

1.排序g字段在多个索引中，无法使用索引排序

排序字段在多个索引中（不在同一个索引中），无法排序按索引排序：

解释 select * from customer where last_name='swj' order by last_name,store_id;

画外音：当排序字段不在同一个时索引，在B+树中无法完成排序，必须进行额外的排序

排序字段在索引中，WHERE条件和ORDER BY使用同一个索引，可以使用索引排序：

解释 select * from customer where last_name='swj' order by last_name;

当然可以合并索引还可以使用索引排序：

注意字段store_id和email在组合索引中

解释 select * from customer where store_id = 5 order by store_id,email ;

2.排序字段顺序与索引列顺序不一致，索引排序无法使用

画外音：本文为组合索引。我们都知道使用组合索引必须遵循最左原则。 WHERE 子句必须具有索引中的第一列。虽然ORDER BY子句没有这个要求，但它也要求排序字段顺序。匹配组合索引列顺序。我们平时使用组合索引的时候，一定要养成按照组合索引列的顺序书写的好习惯。

排序字段顺序与索引列顺序不一致，无法使用索引排序：

解释 select * from customer where store_id > 5 order by email,store_id ;

应保证排序字段顺序与索引列顺序一致，这样才能使用索引排序：

解释 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;

解释 select * from customer where store_id > 5 通过电子邮件订购；

画外音：

原因其实很简单。范围查询时，第一列a必须排序（默认为升序），但第二列b实际上没有排序。 。但如果a字段具有相同的值，则b字段被排序。所以如果是范围查询的话，只能对b进行额外的排序。

3.升序和降序不一致，无法使用索引排序

ORDER BY排序字段必须要么按正序排序，要么全部逆序排序，否则无法使用索引排序。

解释 select * from customer where store_id > 5 order by store_id,email;

解释 select * from customer where store_id > 5 order by store_id desc,email desc;

解释 select * from customer where store_id > 5 order by store_id desc,email asc;

< /p>

总结：

上面的优化其实可以总结为：WHERE条件和ORDER BY使用相同的索引，而ORDER BY的顺序是与索引顺序相同。 ORDER BY 字段按升序或降序排列。否则肯定需要额外的排序操作，就会出现Filesort。

Filesort优化

通过创建适当的索引可以减少Filesort的出现，但在某些情况下并不能完全杜绝Filesort。这时，只能想办法加快Filesort操作的速度。

Filesort的两种排序算法：

1.两次扫描算法

首先，根据The条件取出排序字段和行指针信息，然后在排序缓冲区中进行排序。该排序算法需要对数据进行两次访问，第一次获取排序字段和行指针信息，第二次根据行指针获取记录。第二次读操作可能会导致大量的随机I/O操作。优点是排序时的内存开销较小。

2.一次性扫描算法

一次性取出符合条件的行的所有字段，然后在排序缓冲区中排序，直接输出结果集。 。排序时内存开销较大，但排序效率比两次扫描算法要高。

根据两种排序算法的特点，适当增大系统变量max_length_for_sort_data的值可以让MySQL选择更加优化的Filesort排序算法节奏。并且在编写SQL语句时，只使用必填字段，而不是SELECT * 所有字段。这样可以减少排序区域的使用，提高SQL性能。

我在这里分享一下如何优化Mysql中的order by语句。希望以上内容能够对大家有所帮助，可以学到更多的知识。如果您觉得文章不错，可以分享出去，让更多的人看到。