mysql 高级知识【order by 排序优化】

---

前言

在开发时，我们经常要使用 order by 进行数据排序，经常会出现 useing filesort，导致 mysql 性能会急剧下降，因此我们要非常重视 order by。

一、mysql 支持的两种排序

filesort 排序 和 index

• using index

   	是指 mysql 通过扫描索引完成了自身的排序，index 效率高。
  

• filesort

   filesort 有两种排序算法：双路排序和单路排序。
   		
   1.双路排序
   	在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。
   	读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。
   	即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。
   2.单路排序
   	从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。
  

二、什么时候会用到 using index

1. order by 语句使用索引最左前列。
2. where 语句 和 order by 语句 组合起来满足了索引左前列
3. 使用到了索引最左前列并且 排序字段排序方式相同

案例说明

	表结构：
		create table test(
				id int primary key auto_increment,
				c1 varchar(10),
				c2 varchar(10),
				c3 varchar(10),
				c4 varchar(10),
				c5 varchar(10)
			) ENGINE=INNODB default CHARSET=utf8;
			
	索引：
		create index idx_t1t2 on test(c1,c2,c3,c4)

1. 未使用最左前列索引排序

   explain select * from test where c1>'a1' order by c2
   

   
   解决方法：

   //方案一：补全索引最左前列
   explain select c1 from test where c1>'a1' order by c1,c2
   
   //方案二：where 语句 和 order by 语句 组合满足最左前列索引
   explain select c1 from test where c1='a1' order by c2
   

2. 排序方式不同

   explain select c1 from test where c1>'a1' order by c1 asc,c2 desc
   

   

   //方案一：将 C1 设置为常量。这个可能有点绕，其实原理很简单。
   		//如果 C1 字段条件为常量，那么表示 C1 字段的结果都是同一个值，C1就不用排序。
   		//所以变相的等于 C1 字段 和 C2 字段是同一个排序类型。
   explain select c1 from test where c1='a1' order by c1 asc,c2 desc
   
   //方案二：将两个排序顺序调整为一致
   	//全部调整为升序
   explain select c1 from test where c1>'a1' order by c1 asc,c2 asc
   	//全部调整为降序
   explain select c1 from test where c1>'a1' order by c1 desc,c2 desc
   

二、因为业务需求，一定会产生 filesort，怎么办？

因为 mysql 4.1 后使用了单路排序，在读取到数据后会把数据保存在内存中。当读取数据超过了 sort_buffer 的容量时，就会导致多次读取数据，并创建临时表，产生多次 I/O，反而增加了 I/O 运算。

调整方案

• 尝试提高sort_buffer_size
• 尝试提高max_length_for_sort_data
• 在使用order by时，不要用select *，只查询所需的字段！！！（重点）
  因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。

order by 总结

• 尽量使用索引最左前列 进行 order by 查找
• 使用 order by 时，一定不要使用 select *
• 多写多练慢慢能提升自己
• 本编文章可能有错误的地方，欢迎指正！

---

感谢

MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷
MySQL高级知识（八）——ORDER BY优化