MySQL深入13-order by

# MySQL深入-order by

某些业务会要求按照某一个字段来排序显示结果，这个时候我们往往会使用order by来进行排序。比如我们去查询一张市民表，要求返回按照姓名排序前1000个人的姓名、年龄，且这些人的城市为杭州。


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

为了避免全表扫描，我们需要在city字段上加上索引，下面通过explain命令来查看这个语句执行的情况：

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

Extra这个字段中的using filesort表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

通过上图，我们来对该SQL语句的执行过程进行分析：

上诉过程执行的示意图如下：

图中按照name排序这个过程，是可能在内存中进行排序，也可能需要使用外部排序，这取决排序所需的内存和参数sort_buffer_size。

sort_buffer_size：就是MySQL给sort_buffer开辟的内存大小，如果要排序的数据量小于sort_buffer_size，那么排序就在内存中进行，反之就需要利用磁盘临时文件去复制排序。

临时文件数（number_of_tmp_files）：当使用外部排序的时候，就会用到临时文件。临时文件可以理解为MySQL把要排序的数据分成N份，每份单独排序后存在这些临时文件中，然后把这N份有序文件再合并成一个有序的大文件。

全字段排序中会分配一个排序的缓冲区，然后会根据待排序的数据量和sort_buffer_size来决定进行内存排序还是外部排序。

但是如果我们查询的字段很多的话，那么sort_buffer里面一行数据就会有很多字段，导致内存同时放下的数据行很少，要分成很多个临时文件，排序的性能会很差。因此如果当行数据量很大的时候，使用全字段排序效率就不太好。


SET max_length_for_sort_data = 16;

max_length_for_sort_data：是MySQL中专门控制用于排序的行数据长度的参数。如果单行数据的长度超过这个数值，MySQL就会换一种排序算法。

执行器查看表定义，发现name、city、age字段的长度之和超过max_length_for_sort_data，所以初始化sort_buffer的时候只放入id和name字段。

执行流程如下：

上诉执行流程的示意图如下：

可以看到rowid排序和全字段排序最大的区别就是它多去访问了一次主键索引树。

需要说明的是，最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

MySQL如果认为内存大，会优先选择全字段排序，把需要的字段放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

其实两者最终的选择是考虑到内存，内存的考虑是sort_buffer放入的字段，字段太多就会导致单行数据量过大，单行数据量大，意味着内存就放不下那么多数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

由于rowId排序会回表造成磁盘读，因此不会被优先选择。

经过上面的分析，可以看到order by是一个成本比较高的操作。但是并非所有的order by操作都需要排序的，之所以需要进行排序是原来的数据是无序的。如果我们可以通过某种方式让需要排序字段本身就是有序的呢？

在上述案例中，我们可以让name字段本身就是递增排序的,通过创建一个city和name的联合索引：

alter table t add index city_user(city, name);

现在我们来分析一下在这个联合索引下查询语句的执行过程：

可以看到这个查询过程中是不需要临时表、也不需要排序的，我们使用explain命令进行验证。

从图中可以看到，Extra 字段中没有 Using filesort 了，也就是不需要排序了。而且由于 (city,name) 这个联合索引本身有序，所以只需要找到满足条件的前1000条记录就可以退出了。

进一步优化

由于我们只需要name、age所以我们可以使用覆盖索引的方式来进一步优化这个查询语句，减少回表的次数。

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。


alter table t add index city_user_age(city, name, age);

执行流程如下：

从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

上次更新: 2024/06/29, 15:13:44