一.提高order by 的速度:
1.查询时使用order by 的列尽量使用索引,避免出现fileSort;
2.order by 时 select * 是一个大忌,只取query需要的字段,这点非常重要,在这里影响的是:
- 当query 字段大小总和小于 max_length_for_sort_data 而且排序字段不是TEXT或BLOB类型时,会用改进后的算法:单路排序;否则用老算法:多路排序。
- 两种算法的数据都有可能超出sort_buffer 的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是单路排序的算法风险会更大一些,所以要提高sort_buffer_size.3.尝试提高sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的。
3.尝试提高 max_length_for_sort_data
- 提高这个参数,会增加用改进算法的概率,但是如果设置的太高,数据总容量超出sort_buffer_size 的概率就会增大,明显症状是高的磁盘I/O活动和低的处理器使用率。
4.为排序使用索引:
- Mysql 两种排序方式:文件排序(fileSort)和扫描有序索引排序(using index);
- Mysql 能为排序与查询使用相同的索引;
- KEY a_b_c(a,b,c)
5.Order by 能使用最左前缀
……order by a
……order by a,b
……order by a,b,c
……order by a desc,b desc,c desc
如果where 使用索引的最左前缀定义为常量,则order by 能使用索引:
……. Where a=’20’ order by b,c
……. Where a=’20’ and b=’44’ order by c
……. Where a=’20’ and b> 60 order by b,c
不能使用索引进行排序:
…….. Order by a asc,b desc ,c desc –排序不一致
…….. Where h=’sa’ Order by b desc,c desc –丢失a索引
…….. Where a=’20’ order by c desc –丢失b索引
…….. Where a=’20’ order by a ,d —- d不是索引的一部分
…….. Where a in(…) order by b,c — 对于排序来说,多个相等条件也是范围查询
二. Mysql Group by 优化
优化原则:
Group by 的优化方式同 order by 的优化,区别在于 group by 是先排序后分组,遵照索引建的最佳左前缀;
当无法使用索引列时,增大max_length_for_sort_data 参数的设置+ 增大sort_buffer_size 参数的设置;
Where 高于 having,能写在 where 限定的条件就不要去having 限定了。