441-MySQL(排序,分组)

Bertha 。 2022-09-05 15:51 209阅读 0赞

## 排序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;

**示例：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**不添加，默认是ASC，升序。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
**添加DESC，是降序**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
**我们还可以用多个字段排序**  
**前1个字段不相同，按前1个字段排序，前1个字段相同，按后1个字段排序，以此类推。**

**到name字段有相同的话，age就按照升序asc或者降序desc**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]  
**可以添加where过滤一下**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]

## 排序order by的性能分析 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**我们知道age没有索引。我们加入explain看看**  
![在这里插入图片描述][833706b2562d4f0aa7b0b845a464b599.png]  
**ALL表示整张表搜索**  
**using filesort 文件排序**

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

**如何优化？？？**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]  
**如果是select name**  
![在这里插入图片描述][843dce68438a4999b18429ad563636a6.png]  
**用1个索引就OK了！现在用到了索引**

**我们知道age没有索引。所以用到了外排序**  
![在这里插入图片描述][5d25e54fe1954c3f8a099482d279fd05.png]  
**我们现在发现，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;

**示例：**  
**首先，先去重，查看表中年龄的分布**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
**现在我们要统计，看年龄分布在20岁上的学生有多少个？  
按age进行分组，age相同的在一组。**  
![在这里插入图片描述][17f924f9e4834ec89ec6374f721f6eb5.png]  
**总共有3组。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
**下图这种情况，只是显示出分组里的第一条，这条SQL语句实质上是没有意义的。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
**我们使用count统计输出**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
**group by所做的操作就是把我们选择出来的数据按照group by分组的字段进行分组，字段相同的记录放在一组，然后我们可以通过聚合函数进行统计操作。**

**如果分组后还要加条件。得使用having**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
**当然还是推荐使用where，如果age涉及到索引，where是可以利用age索引的**  
**我们也可以使用多个字段进行分组**  
**把age和sex相同的分为1组**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]  
**count(\*） MySQL进行优化，主键来表示，相当于统计行数**  
**可以按照降序显示**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]

## 分组group by的性能分析 ##

**我们看到，id是主键，有主键索引，name是唯一键，有唯一键索引，其余字段没有索引。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]  
**我们用explain来查看group by**  
![在这里插入图片描述][c2bae2514f6644c9b95b13293551e6d0.png]  
**在group by的时候，会自动按照分组的顺序放在一个临时表里面，进行排序的。因为age没有索引，此时 group by使用到using filesort外排序，数据量一大起来就效率低了。**

**那如果是select name，group by name。name有唯一键索引，我们来看看**  
![在这里插入图片描述][48b0a77daac44677ad980809b9951bb1.png]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/77f8035adb87471eb29b2aa5d91f73d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/02ae1c94559c47258c7e253c3e8ba645.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/ffcabda2c71b4e61b905b0e7678191aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/a5e96d3d9aeb4e76a2826f05a5765ba2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/1d50612edd6646c284a7309d05e0c0f3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/49f5c7bf778547d784b6dca25731d885.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/ba4c7b76bcad42d7b3d843274e7d4ac6.png
[833706b2562d4f0aa7b0b845a464b599.png]: /images/20220829/d20da676234a469a997979e18d5c4b78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/868e13f26d9f4845b6bc6fb8ee1e2803.png
[843dce68438a4999b18429ad563636a6.png]: /images/20220829/89fdd8c0336e4a94bc088b340d46f440.png
[5d25e54fe1954c3f8a099482d279fd05.png]: /images/20220829/520d89f3c30c4a5799ebff1987c2ec4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/ee7a41bde5214f7f95cc6d38e62c8cc3.png
[17f924f9e4834ec89ec6374f721f6eb5.png]: /images/20220829/b59d29f4cbcf4492ba42c84da139c7ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/88574be37d804e8ab9bb77ef03edbf0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/dd000be53d2e4ce397eb273b10866eb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/dcedfdf86caa4509815b4c98cc8ab01d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/1400a3ad31904a3cbf2e0cf9bd89193e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/d0544c5671f5403c9234c506b8793628.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]: /images/20220829/98f990351efc4b7a824702a74fae42fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]: /images/20220829/f41709c3d4e5454698817eef784c45b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]: /images/20220829/683873fd522c423984f58a748d24eb6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]: /images/20220829/fd5817d9a4cb449eb8e4f13b44d1f05a.png
[c2bae2514f6644c9b95b13293551e6d0.png]: /images/20220829/06debe4f2f7b4cc58afd04edff2b6854.png
[48b0a77daac44677ad980809b9951bb1.png]: /images/20220829/3b2db96fa5e34c80b08613a0b09c16c8.png