MySQL学习笔记之order by 的工作原理（十六）

迈不过友情╰ 2023-07-09 15:29 8阅读 0赞

> 声明：本文章内容是根据极客时间中林晓斌的课程《MYSQL45讲》，经过学习，加以自己的理解形成的笔记。具体原文可以到官网进行阅读。如有侵权请，告知删除。

#### 1.order by ####

order by 是我们平常在使用mysql的时候经常使用到的一个关键字。目的是让结果集按照 by 后面标注的字段，进行排序。如:

select city,name,age from t where city='杭州' order by name limit 1000 ;

为了避免全表扫描，我们需要在city字段上面加上索引。加上索引示意图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]  
**语句的执行流程如下：**  
**一、全字段排序**  
1、初始化 sort\_buffer，确定放入 name、city、age 这三个字段（MySQL 会给每个线程分配一块内存用于排序，称为 sort\_buffer。）  
2、从索引city找到第一个满足 city = "杭州"条件的主键ID，也就是途中的ID\_x  
3、回表取出整行，取 name、city、age三个字段的值，存入sort\_buffer中；  
4、从索引中city取出下一个记录的id；  
5、重复步骤3，4 直到条件不满足为止  
6、对sort\_buffer 中的数据按照字段name进行快速排序  
7.按照排序结果取前1000行返回给客户端  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 1]  
**上面那种模式存在一个问题，也就是如果取值的字段，有很多个，并且加起来都很长，那么在sort\_buffer中将会很消耗内存，所以还有另外一种排序方式 rowid排序也就是只将 排序的字段+主键id 放入到 sort\_buffer当中，进行排序。有序的结果 需要根据 主键id，回表再次取出完整值，然后返回给客户端，rowid排序方式就多了 一次回表的过程。**  
**二、rowid排序**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 2]

#### 2、改进措施 ####

上面两种方式都会使用到 sort\_buffer,其根本原因是因为排序的字段没有顺序。那么如果在(city,name)上添加一个组合索引，那么问题就变的简单来了，只要city相同，他的name一定是有序的

*  那么只需在city 和name的组合索引上找到 city='杭州’的值，
 *  然后取出id，再根据id回表查询当一行的记录。
 *  将返回结果集，根据客户端需要的字段返回

**上面这种使用组合索引，利用了索引的有序性，可以省去 sort\_buffer这个步骤 大大提升性能；但是还是存在一个问题 就是回表的操作，对于这个我们的改名方式可以是，使用《覆盖索引》、当然这也也会增加对索引维护的成本**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200229135322219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200229140413414.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020022914093949.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70