MySQL-16：order by如何运作

迷南。 2022-12-26 11:15 125阅读 0赞

**order by**：根据提供的字段进行排序；用法：order by column\[ASC(默认) | DESC\]。

**limit：** 从结果集上再进行刷选；用法：limit offset ,rows。

如下语句建表：给city建索引

CREATE table a(
        id int(11) not null,
    		name varchar(16) not null,
    		age VARCHAR(16) not null,
    		city VARCHAR(16) not null,
    		PRIMARY key(id),
    		key city (city)
    )ENGINE = INNODB

当我们查询city = “南京”时的sql语句。

SELECT name,age,city from a WHERE city="南京" ORDER BY age limit 10

我们用explain命令看下语句，发现它使用的是city索引，而且Extra是Using filesort，表示是需要排序的。

MySQL会给每个线程分配一块内存用于排序，叫做**sort\_buffer**。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

#### 16.1 全字段排序 ####

**sql语句的执行流程：**

1.  初始化sort\_buffer，确定name，age，city三个字段
2.  去city索引中找到第一个满足city="南京"的数据，得到id
3.  带着上面的id去id主键索引树中寻找对应的数据行，取出name，age，city字段值存入sort\_buffer
4.  从city索引去下一个满足条件的id
5.  重复3,4步，直到从city索引中取不到满足条件的id
6.  对sort\_buffer中的数据根据age进行排序

**流程示意图如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

**sort\_buffer\_size：**

上面流程的第五步，根据age将数据进行排序，可能是在内存中进行，也可能借助了临时文件。这取决于参数`sort_buffer_size`的大小与所需要的内存大小。如果排序数据量过大，无法在内存中完成排序，就必需要借助于磁盘临时文件辅助排序。排序磁盘临时文件的算法是归并算法，归并算法的思想是分而治之。

#### 16.2 row id 排序 ####

MySQL中有个参数`max_length_for_sort_data`表示用于排序的当行长度，如果我们排序的字段有a,b,c…h这么多，内存中存下数据有限，必定会借用更多的磁盘临时表，性能不佳。

当出现单行的数据长度超过参数设定值，排序的列只会是 主键 id和 需要排序列。

**sql语句的执行流程：**

1.  初始化sort\_buffer，确定主键id，age列
2.  从city索引中查找到第一个满足city = “南京”数据并取到id
3.  带着主键id去id索引树找到对应的数据行，取出age，id两列放入sort\_buffer中
4.  去city索引树继续找到下一个满足条件的数据，取出id
5.  重复3,4步，直到city索引树找不到满足条件的数据。
6.  对sort\_buffer中的数据根据age进行排序
7.  遍历排序结果，根据id去id索引树中取到name，age，city返回给客户端

**流程示意图：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]

#### 16.3讨论及优化 ####

1.  使用临时表排序会造成较大的性能消耗，能用内存尽量用内存，减少磁盘访问。
2.  利用**索引覆盖**规则，我们创建name,age,city可以减少回表的步骤，直接得到结果。
3.  利用磁盘文件的目的是为了排序，那我们通过建立联合索引，在当初数据放置的时候就已经排序好，我们取出来可以直接用。

像这样做：

alter TABLE a add index city_age(city,age)

**练习问题：**

1.  order by 与 limit 的用法
2.  sort\_buffer是啥？什么参数控制？
3.  全字段排序执行流程？
4.  row id排序执行流程？
5.  优化方案？
6.  MySQL的**OPTIMIZER\_TRACE** 特性？

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/774e3eeb70214588a53f4dd97e8c9670.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/c21f246221304f9c8565aaeb97c09e06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/28e782ab61224951b44bfaf27931e759.png