order by的工作原理

AsiaYe

发布于 2020-06-22 16:57:24

1.1K0

文章被收录于专栏：DBA随笔DBA随笔

今天分享的内容关于order by的两种排序模式。全字段排序和rowid排序。

首先，创建表：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

其中，city上面有索引，id是主键。看下面这个SQL：

mysql> explain select city,name,age from t where city='西安' order by name limit 1000  ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ref  | city          | city | 50      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

where条件后面是city字段，然后根据name排序，可以看到，执行计划中有：using filesort字样。这是因为name字段没有索引，所以需要借助sort_buffer来进行排序操作。

关于，sort_buffer这里需要提一点，当我们有多个select操作都需要排序的时候，每个SQL线程都会有自己的一个sort_buffer,而不是公用同一个sort buffer。

全字段排序过程

SQL贴在这里：

select city,name,age from t
where 
city='西安' order by name limit 1000  ;

先来看全字段排序过程，主要的排序流程如下：

1、由于我们select的字段有city、name、age三个字段，首先会在sort buffer中初始化city、name、age这三个字段。

2、根据city字段在二级索引上过滤出来记录的主键id值，然后回表聚集索引查询到对应的name和age值，并将city、name、age三个字段放入sort_buffer中。

3、重复执行步骤2，直到找到不符合条件的city值。

4、对sort buffer中的记录按照name做排序操作

5、按照排序结果取前1000行返回给客户端。

其中，第4步的按照name字段排序，可能在内存中就能完成，可能需要借助磁盘上的临时文件。这取决于记录的总容量是否超过sort_buffer_size：

mysql> show variables like "%sort_buffer%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_sort_buffer_size | 1048576  |
| myisam_sort_buffer_size | 69206016 |
| sort_buffer_size        | 262144   |
+-------------------------+----------+
3 rows in set, 1 warning (0.00 sec)

如果超过这个值，则不得不借助磁盘上的临时文件。

rowid排序

全字段排序的方法，存在一个缺点，在一个有很多字段的表中，例如游戏类的表，如果我们select的列很多，那么sort buffer中的字段也会很多，这样sort buffer只能存放很少一部分数据。在排序的时候，要借助很多个磁盘临时文件，而磁盘临时文件的排序是会影响性能的。

那么，到底多少个字段才算多呢？MySQL中用这样一个参数来控制：

mysql> show variables like "%max_length_for_sort_data%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

默认是1kb，我们把这个值改小一点，让select的列的字节数之和大于这个数字，那么MySQL会认为单行的记录太大，需要使用rowid的方法进行排序，基于rowid的排序方法如下：

1、由于我们select的字段很多，无法放入sort buffer中，MySQL首先会在sort buffer中初始化name和id这2个字段。其中name是我们order by的目标列，id是主键

2、根据city字段在二级索引上过滤出来记录的主键id值，然后回表聚集索引查询到对应的name和id值，并将name和id这2个字段放入sort_buffer中。

3、重复执行步骤2，直到找到不符合条件的city值。

4、对sort buffer中的记录按照name做排序操作

5、按照排序结果取前1000行，并按照id值去聚集索引中取出city、name、age三个字段返回给客户端。

一点结论

1、MySQL中，排序是一个成本比较高的操作

2、rowid算法其实是为了防止排序内存过小导致的磁盘访问，如果内存够，MySQL会尽量使用内存来进行排序。

3、如果order by的字段上本身有索引，则数据记录本身有序，就不会使用sort buffer，之所以使用临时的磁盘文件来排序，是因为数据记录都是无序的。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-06-18，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

云数据库 SQL Server

order by的工作原理

order by的工作原理

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐