441-MySQL(排序,分组)

排序order by的使用

select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25 order by age asc; 
select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25 order by age desc;

示例：


不添加，默认是ASC，升序。

添加DESC，是降序

我们还可以用多个字段排序
前1个字段不相同，按前1个字段排序，前1个字段相同，按后1个字段排序，以此类推。

到name字段有相同的话，age就按照升序asc或者降序desc

可以添加where过滤一下

排序order by的性能分析

我们知道age没有索引。我们加入explain看看

ALL表示整张表搜索
using filesort 文件排序

我们的八大排序算法是在内存上排序。
但是如果我们的磁盘的数据非常大，而我们内存的数据是有限的，比如说我们有20G的数据，内存只有4G，不可能弄到内存排序，就涉及到文件排序，外排序，一般用N路归并排序来进行外排序操作，外排序操作就是磁盘的数据太大了，无法一次性加载到内存，所以采用了N路归并的思想，涉及到很多磁盘I/O，磁盘I/O一涉及，效率就低了。
ORDER BY 的效率太慢了，涉及到外排序，涉及到磁盘I/O，效率低了。

如何优化？？？

如果是select name

用1个索引就OK了！现在用到了索引

我们知道age没有索引。所以用到了外排序

我们现在发现，order by 不仅仅和待排序的字段有没有建索引有关，还和select的列有没有建索引有关。

select *要回表，所以using filesort
select name不用回表，直接在辅助索引树找到。

分组group by的语法

按照指定的字段，相同的归为一组。

select sex from user group by sex; 
select count(id),sex from user group by sex; 
select count(id),age from user group by age having age>20;

示例：
首先，先去重，查看表中年龄的分布

现在我们要统计，看年龄分布在20岁上的学生有多少个？
按age进行分组，age相同的在一组。

总共有3组。

下图这种情况，只是显示出分组里的第一条，这条SQL语句实质上是没有意义的。

我们使用count统计输出

group by所做的操作就是把我们选择出来的数据按照group by分组的字段进行分组，字段相同的记录放在一组，然后我们可以通过聚合函数进行统计操作。

如果分组后还要加条件。得使用having

当然还是推荐使用where，如果age涉及到索引，where是可以利用age索引的
我们也可以使用多个字段进行分组
把age和sex相同的分为1组


count(*） MySQL进行优化，主键来表示，相当于统计行数
可以按照降序显示

分组group by的性能分析

我们看到，id是主键，有主键索引，name是唯一键，有唯一键索引，其余字段没有索引。

我们用explain来查看group by

在group by的时候，会自动按照分组的顺序放在一个临时表里面，进行排序的。因为age没有索引，此时 group by使用到using filesort外排序，数据量一大起来就效率低了。

那如果是select name，group by name。name有唯一键索引，我们来看看

外排序还真没了！
从索引树上直接取出来数据
group by 后的字段加索引还是非常有必要的。