Mysql order by 和 group by 性能优化

数据库 新民 7701℃ 已收录 1评论

提高order by 的速度:

1、查询时使用order by 的列尽量使用索引,避免出现fileSort;

2、order by 时 select * 是一个大忌,值query需要的字段,这点非常重要,在这里影响的是:

2.1 当query 字段大小总和小于 max_length_for_sort_data 而且排序字段不是TEXT或BLOB类型时,会用改进后的算法———单路排序;否则用老算法————多路排序。

2.2   两种算法的数据都有可能超出sort_buffer 的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是单路排序的算法风险会更大一些,所以要提高sort_buffer_size.

 

3、尝试提高sort_buffer_size

  1. 不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的。

4、尝试提高 max_length_for_sort_data

  1. 提高这个参数,会增加用改进算法的概率,但是如果设置的太高,数据总容量超出sort_buffer_size 的概率就会增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

 

为排序使用索引:

 

Mysql 两种排序方式:文件排序(fileSort)和扫描有序索引排序(using index);

Mysql 能为排序与查询使用相同的索引;

 

KEY a_b_c(a,b,c)

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 ,cdesc    –排序不一致

…….. Where  h=’sa’  Order by b desc,cdesc    –丢失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 限定了。

 

本站文章如未注明,均为原创丨本网站采用BY-NC-SA协议进行授权,转载请注明转自:https://www.snowruin.com/?p=1669
喜欢 (2)or分享 (0)
发表我的评论
取消评论
表情 代码 贴图 加粗 链接 私信 删除线 签到

Hi,请填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)条精彩评论。
  1. 就来看看、感谢分享
    春熙路2017-05-03 19:10 回复| Google Chrome 45.0.2454.101| Windows XP