排序优化

数据库优化 学习笔记

一、排序优化

1.1、分析

1. 观察，至少跑一天，看看生产的慢SQL情况
2. 开启慢查询日志，设置阙值，比如超过 5 秒钟的就是慢 SQL，并抓取出来
3. explain + 慢 SQL 分析
4. show profile
5. 进行 SQL 数据库服务器的参数调优（运维 orDBA 来做）
   
   

1.2、总结

1. 慢查询的开启并捕获
2. explain + 慢 SQL 分析
3. show profile 查询 SQL 在 MySQL 服务器里面的执行细节
4. SQL 数据库服务器的参数调优
   
   

1.3、小表驱动大表

	-- 例如外层循环为连接数据库, 内层循环为对数据库进行操作
	-- 那么显然是第一种比较好, 因为连接数据库需要消耗更多的资源, 因此连接次数越少越好
	
	for i in range(5):
	    for j in range(1000):
	        pass

	for i in range(1000):
	    for j in range(5):
	        pass

---

二、order by 优化

2.1、建表，插入测试数据

	create table tbla(
	    age int,
	    birth timestamp not null
	);

	insert into tbla(age,birth) values(22,now());
	insert into tbla(age,birth) values(23,now());
	insert into tbla(age,birth) values(24,now());

2.2、建立索引

	create index idx_tbla_agebrith on tbla(age,birth);

2.3、分析

        MySQL 支持两种方式的排序，filesort 和 index，index 效率高，MySQL 扫描索引本身完成排序。filesort 方式效率较低。

• order by 满足两种情况下，会使用 index 方式排序
  
  1.order by 语句使用索引最左前列。（即，创建索引是字段的顺序与order by的字段的顺序一致）
  
  2.使用 where 子句与 order by 子句条件组合满足索引最左前列。
  
  
  
  
  

• filesort 有两种算法 —— 双路排序和单路排序
  
  1.双路排序： MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
  
  2.单路排序： 从磁盘读取查询需要的所有列，按照 order by 列在 buffer（默认大小为 2 M） 对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间。
  
  3.优化策略调整MySQL参数（遇到性能瓶颈时才调）

	增加 sort_buffer_size 参数设置
	增大 max_lenght_for_sort_data 参数的设置

2.4、提高 order by 的速度

• order by 时 select * 是一个大忌，只写需要的字段
  
  1. 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法 —— 单路排序。
  
  2. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建 tmp文件进行合并排序，导致多次 I/O。
  
  

• 尝试提高 sort_buffer_size

• 尝试提高 max_length_for_sort_data
  
  

---

三、练习

	-- 索引 a_b_c(a,b,c)
	-- 判断下来语句是否会出现文件内排序

	order by a,b											-- 不会
	order by a,b,c											-- 不会
	order by a desc,b desc,c desc							-- 不会

	where a = const order by b,c							-- 不会, (a=一个常量) 构成最左前列
	where a = const and b = const order by c				-- 不会, (a=一个常量)且(b=一个常量) 构成最左前列
	where a = const and b > const order by b,c				-- 不会, (a=一个常量)且(order by 字段顺序符合最左前列)

	order by a asc,b desc,c desc  							-- 会, 字段的升降不一致
	where g = const order by b,c   							-- 会, 不满足最左前列
	where a = const order by c    							-- 会, 丢失 b 字段索引
	where a = const order by a,d 							-- 会, d 不是索引字段