专栏首页JavaEEMySQL的排序和分组

MySQL的排序和分组

order bygroup by这两个要十分注意,因为一不小心就会产生文件内排序,即file sort,这个性能是十分差的。下面来看具体的案例分析。

首先建表:

create table `tblA`(
  `id` int not null primary key auto_increment comment '主键',
  `age` int not null comment '年龄',
  `birth` timestamp not null comment '生日'
) ;
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_age_birth on tblA(age, birth);

1. order by:

看看下面语句的执行计划:

explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by age,birth;

这两个个毫无疑问,可以用到索引。

执行计划

再来看看这个:

explain select * from tblA where age > 20 order by birth;

执行计划

显然我们可以看到这里产生了filesort,为什么呢?因为age是范围,且order by的直接是二楼,带头大哥没了,所以索引失效了。

那这样呢?

explain select * from tblA where age > 20 order by birth, age;
explain select * from tblA where age > 20 order by age, birth;

执行计划

第一个还是不行,因为范围后失效,且order by是从birth二楼开始的。第二个可以用到索引,不会产生filesort,是因为,虽然前面的age是范围,但是order by的又是从age开始,带头大哥在。

上面这些都好理解,看看这个:

explain select * from tblA  order by age desc, birth asc;

执行计划

奇了怪了,带头大哥在,也没有范围,为啥就出现了filesort了呢?

这是因为age是降序,birth又是升序,一升一降,就会导致索引用不上,就会产生filesort了。如果把两个都改成desc或者asc,那就没问题了。

注意:

MySQL的filesort有两种策略,
MySQL4.1之前,叫双路排序。
就是会进行两次磁盘I/O操作。读取行指针和order by的列,
对它们排序,然后扫描排好序的表,再从磁盘中取出数据来。

4.1之后的版本,叫单路排序,只进行一次I/O。
先将数据从磁盘读到内存中,然后在内存中排序。
但是,如果内存,即sort_buffer_size不够大,性能反而不如双路排序。

order by优化小总结:

  • 尽量避免select *;
  • 尝试增大sort_buffer_size,不管用哪种算法,增大这个都可以提高效率;
  • 尝试增大max_length_for_sort_data,增大这个,会增加用改进算法的概率。

2. group by:

group by 其实和order by一样,也是先排序,不过多了一个分组,也遵从最佳左前缀原则。要注意的一点是,where优于having,能用where时就不要用having。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Pandas|排序,分组,组内排序

    01 Pandas的基本排序 Pandas的主要数据结构有2个:DataFrame,Series,针对这两个类型的排序Demo如下: #coding=utf-...

    double
  • 第十课 分组数据创建分组过滤分组分组和排序

    ** having和where的区别 **: ** where在数据分组前进行过滤,having在数据分组后进行过滤,where过滤的是行,having过滤的...

    desperate633
  • 数组前半部分和后半部分有序的全排序

    例如: 已知数组a前半部分a[0,mid - 1],后半部分a[mid,num-1],现前半部分和后半部分均已排好序。要求:实现a数组的从小到大排序。空间复杂度...

    猿人谷
  • Python分组内排序

    py3study
  • mapreduce的二次排序-分区分组

    就是首先按照第一字段排序,然后再对第一字段相同的行按照第二字段排序,注意不能破坏第一次排序 的结果 。例如

    字母哥博客
  • Hadoop学习笔记—11.MapReduce中的排序和分组

      从上图中可以清楚地看出,在Step1.4也就是第四步中,需要对不同分区中的数据进行排序和分组,默认情况下,是按照key进行排序和分组。

    Edison Zhou
  • 分组合计且排序和显示名称

            分组合计的一个问题是,合计中最大的问题是:只能显示groupby的字段,不能显示其它的字段。有时还需要排序,就很麻烦。这里有一个实现。 SELE...

    用户1075292
  • Go寻找数组中最小的k个数——全部排序和部分排序

    今天分享的是数组中寻找k个最小数的解题思路,分别是全部排序和部分排序,一起来看看吧~

    陌无崖
  • MapReduce之GroupingComparator分组(辅助排序、二次排序)

    利用“订单id和成交金额”作为key,所以把每一行记录封装为bean。由于需要比较ID,所以实现了WritableComparable接口 OrderBean...

    孙晨c

扫码关注云+社区

领取腾讯云代金券