Home >Database >Mysql Tutorial >Do you really understand MySQL's order by?
This article brings you relevant knowledge about order by sorting in mysql. I hope it will be helpful to you.
My first impression of the word "sort" is that almost all apps have a sorting place. Taobao products are sorted by purchase time, and comments on Station B are sorted by popularity. Sorting... Of course, what we are talking about today is not how to elegantly sort under big data or how to improve sorting performance. Let's talk about sorting in MySQL.
For MySQL, when it comes to sorting, what is the first thing that comes to your mind? Keyword order by? Is it best to have an index for the order by field? Are the leaf nodes already sequential? Or should we try not to sort inside MySQL?
The cause of the matter
Now suppose there is a user’s friend table:
CREATE TABLE `user` ( `id` int(10) AUTO_INCREMENT, `user_id` int(10), `friend_addr` varchar(1000), `friend_name` varchar(100), PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB;
There are currently two points in the table Need to pay attention to:
The user's user_id, the friend's name friend_name, the friend's address friend_addr
user_id is indexed
One day, there was a junior development engineer Xiao Yuan , received a request from Xiao Wang, a junior product manager:
Xiao Wang: Comrade Xiao Yuan, now we need to add a function in the background. This function should support the ability to find the names and addresses of all his friends based on the user ID. , and require friends' names to be sorted according to dictionary.
Xiaoyuan: Okay, this function is simple, I will go online right away.
So Xiaoyuan wrote this sql:
select friend_name，friend_addr from user where user_id=? order by name
In a flash of light, Xiaoyuan went online proudly. Everything went smoothly until one day there was an operation The classmate caused such a query:
select friend_name，friend_addr from user where user_id=10086 order by name
However, this query was much slower than usual. The database reported a slow query. At this time, the little monkey panicked: What is going on? User_id obviously has an index, and cleverly I only used select friend_name, friend_addr instead of select *. The little ape kept comforting himself at this time, saying that he had to be calm, and then he suddenly thought of the explain command. Use explain to check the execution plan of the sql. When the little ape used explain, he found that there was a Dangerous-looking word: using filesort.
"This query actually uses the legendary file sorting, but if a person does not have many friends, even if he uses file sorting, it should be very fast." Unless this user_id=10086 has many friends, later Xiaoyuan went to check and found that this user has more than 100,000 friends.
The little ape, who was lost in thought, thought to himself: The blame seems to have been determined, 100,000 data is a bit big, and what is the sorting principle of using filesort?
Some people may say that the above problem is that the 100,000 data is too large, and it is slow even if it is not sorted. This actually makes sense. 10,000 data can be found at one time, whether it is MySQL memory buffer occupancy and network bandwidth consumption are both very large. So what if I add a limit of 1000? The problem of network bandwidth has definitely been solved, because the overall data packet size has become smaller, but the problem of using filesort is still not solved. Seeing this, you may have questions, does using filesort sort files? How are they sorted in the file? Or I ask this: If you were asked to design and sort, how would you deal with it? With these questions and thoughts, let's take a look at what technical difficulties are involved in using filesort and how to solve them?
First of all, our user_id is indexed, so we will first retrieve our target data on the user_id index tree, that is, the data of user_id=10086, but what we want to query is friend_name and Friend_addr field, unfortunately, the values of these two fields cannot be found by relying on the user_id index alone
So we need to return to the table and search in the primary key index tree through the primary key corresponding to user_id, ok , we found the first friend_name and friend_addr fields of user_id=10086
What should we do now? It is definitely wrong to return directly, because I need to sort friend_name. How to sort? The data has not been found yet, so you have to put the found data in one place first. This place is sort_buffer. I think you should guess it when you see the name. Yes, sort_buffer is used for sorting in this case. Buffer, it should be noted here that each thread will have a separate sort_buffer. The main purpose of this is to avoid lock competition problems caused by multiple threads operating on the same memory.
When the friend_name and friend_addr of the first piece of data have been put into the sort_buffer, this is of course not over. The synchronization steps will be repeated until all the friend_name and friend_addr of user_id=10086 are put. It ends after entering the sort_buffer
The data in the sort_buffer has been put in, and it is time to sort. Here MySQL will perform a quick sort on the friend_name. After the quick sort, the data in the sort_buffer friend_name is in order
Finally return the first 1000 items in sort_buffer and end.
一切看起来很丝滑，但是 sort_buffer 占用的是内存空间，这就尴尬了，内存本身就不是无限大的，它肯定是有上限的，当然 sort_buffer 也不能太小，太小的话，意义不大。在 InnoDB 存储引擎中，这个值是默认是256K。
mysql> show variables like 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+
也就是说，如果要放进 sort_buffer 中的数据是大于256K的话，那么采用在 sort_buffer 中快排的方式肯定是行不通的，这时候，你可能会问：MySQL难道不能根据数据大小自动扩充吗？额，MySQL是多线程模型，如果每个线程都扩充，那么分给其他功能buffer就小了（比如change buffer等），就会影响其他功能的质量。
这时就得换种方式来排序了，没错，此时就是真正的文件排序了，也就是磁盘的临时文件，MySQL会采用归并排序的思想，把要排序的数据分成若干份，每一份数据在内存中排序后会放入临时文件中，最终对这些已经排序好的临时文件的数据再做一次合并排序就ok了，典型的分而治之原理，它的具体步骤如下：
先将要排序的数据分割，分割成每块数据都可以放到 sort_buffer 中
对每块数据在 sort_buffer 中进行排序，排序好后，写入某个临时文件中
当所有的数据都写入临时文件后，这时对于每个临时文件而言，内部都是有序的，但是它们并不是一个整体，整体还不是有序的，所以接下来就得合并数据了
假设现在存在 tmpX 和 tmpY 两个临时文件，这时会从 tmpX 读取一部分数据进入内存，然后从 tmpY 中读取一部分数据进入内存，这里你可能会好奇为什么是一部分而不是整个或者单个？因为首先磁盘是缓慢的，所以尽量每次多读点数据进入内存，但是不能读太多，因为还有 buffer 空间的限制。
对于 tmpX 假设读进来了的是 tmpX[0-5] ,对于 tmpY 假设读进来了的是 tmpY[0-5]，于是只需要这样比较：
如果 tmpX[0] tmpY[0]，那么 tmpY[0] 肯定是第二小的...，就这样两两比较最终就可以把 tmpX 和 tmpY 合并成一个有序的文件tmpZ，多个这样的tmpZ再次合并...，最终就可以把所有的数据合并成一个有序的大文件。
通过上面的排序流程我们知道，如果要排序的数据很大，超过 sort_buffer 的大小，那么就需要文件排序，文件排序涉及到分批排序与合并，很耗时，造成这个问题的根本原因是 sort_buffer 不够用，不知道你发现没有我们的 friend_name 需要排序，但是却把 friend_addr 也塞进了 sort_buffer 中，这样单行数据的大小就等于 friend_name 的长度 + friend_addr 的长度，能否让 sort_buffer 中只存 friend_name 字段，这样的话，整体的利用空间就大了，不一定用得到到临时文件。没错，这就是接下来要说的另一种排序优化rowid排序。
rowid 排序的思想就是把不需要的数据不要放到 sort_buffer 中，让 sort_buffer 中只保留必要的数据，那么你认为什么是必要的数据呢？只放 friend_name？这肯定不行，排序完了之后，friend_addr 怎么办？因此还要把主键id放进去，这样排完之后，通过 id 再回次表，拿到 friend_addr 即可，因此它的大致流程如下：
根据 user_id 索引，查到目标数据，然后回表，只把 id 和 friend_name 放进 sort_buffer 中
重复1步骤，直至全部的目标数据都在 sort_buffer 中
对 sort_buffer 中的数据按照 friend_name 字段进行排序
排序后根据 id 再次回表查到 friend_addr 返回，直至返回1000条数据，结束。
这里面其实有几点需要注意的：
这种方式需要两次回表的
sort_buffer 虽然小了，但是如果数据量本身还是很大，应该还是要临时文件排序的
那么问题来了，两种方式，MySQL 该如何选择？得根据某个条件来判断走哪种方式吧，这个条件就是进 sort_buffer 单行的长度，如果长度太大（friend_name + friend_addr的长度），就会采用 rowid 这种方式，否则第一种，长度的标准是根据 max_length_for_sort_data 来的，这个值默认是1024字节：
mysql> show variables like 'max_length_for_sort_data'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 1024 | +--------------------------+-------+
不想回表，不想再次排序
其实不管是上面哪种方法，他们都需要回表+排序，回表是因为二级索引上没有目标字段，排序是因为数据不是有序的，那如果二级索引上有目标字段并且已经是排序好的了，那不就两全其美了嘛。
没错，就是联合索引，我们只需要建立一个 （user_id，friend_name，friend_addr）的联合索引即可，这样我就可以通过这个索引拿到目标数据，并且friend_name已经是排序好的，同时还有friend_addr字段，一招搞定，不需要回表，不需要再次排序。因此对于上述的sql，它的大致流程如下：
通过联合索引找到user_id=10086的数据，然后读取对应的 friend_name 和 friend_addr 字段直接返回，因为 friend_name 已经是排序好的了，不需要额外处理
重复第一步骤，顺着叶子节点接着向后找，直至找到第一个不是10086的数据，结束。
联合索引虽然可以解决这种问题，但是在实际应用中切不可盲目建立，要根据实际的业务逻辑来判断是否需要建立，如果不是经常有类似的查询，可以不用建立，因为联合索引会占用更多的存储空间和维护开销。
对于 order by 没有用到索引的时候，这时 explain 中 Extra 字段大概是会出现 using filesort 字眼
出现 using filesort 的时候也不用太慌张，如果本身数据量不大，比如也就几十条数据，那么在 sort buffer 中使用快排也是很快的
如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序，这部分是由 MySQL 优化器决定的
如果查询的字段很多，想要尽量避免使用临时文件排序，可以尝试设置下 max_length_for_sort_data 字段的大小，让其小于所有查询字段长度的总和，这样放入或许可以避免，但是会多一次回表操作
实际业务中，我们也可以给经常要查询的字段组合建立个联合索引，这样既不用回表也不需要单独排序，但是联合索引会占用更多的存储和开销
大量数据查询的时候，尽量分批次，提前 explain 来观察 sql 的执行计划是个不错的选择。
推荐学习：mysql视频教程
The above is the detailed content of Do you really understand MySQL's order by?. For more information, please follow other related articles on the PHP Chinese website!