如何正确的使用 order by

如何正确的使用 order by

阅读本文大概需要2.6分钟。

在日常开发中我们经常需要根据某个字段来对数据进行排序，假如我们用到的表的结构如下

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

现在有这样一个查询语句，

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

这个查询语句是如何执行的呢？

1. 全字段排序

1.1 sort_buffer

sort_buffer 是 MySQL 为线程排序分配的一块内存。

1.2 查询过程剖析

根据已有的知识，birth_city 字段出现在where条件中，我们在该字段上建立索引能加快访问速度。那么该语句的查询过程如下：

1. 初始化sort_buffer, 确定放入select中出现的字段birth_city, name, age。
2. 从索引 birth_city 中找到第一个满足条件birth_city = '西安' 的记录，取出其主键id。
3. 根据id回主键索引查询，取出来select中出现的字段，放入sort_buffer。
4. 从索引birth_city获取下一个满足条件的记录id。
5. 重复步骤3，4 直到不满足查询条件为止。
6. 现在sort_buffer 中已经存放了满足条件的数据，然后按照字段name 进行排序。
7. 对排序结果取前1000行数据。

上述的查询过程称为全字段索引排序。

在进行步骤6的过程中，会根据数据量的大小，安排在不同位置进行排序，有可能是内存或者硬盘。

2. rowid 排序

如果上述查询语句select 中出现的字段比较多，那么要占用的sort_buffer 的空间就会变大，此时MySQL会采取另一种策略。

select birth_city, name, age,hobby,score from t where birth_city = '西安' order by name desc limit 1000;

在初始化sort_buffer的时候，只会放入id和name，然后在查询基础上增加一次回表，完整流程如下所示：

1. 初始化sort_buffer, 确定放入两个字段，where条件中出现的字段name和主键id。
2. 从索引 birth_city 中找到第一个满足条件birth_city = '西安' 的记录，取出其主键id。
3. 根据id回主键索引查询，取出来name和id两个字段，放入sort_buffer。
4. 从索引birth_city获取下一个满足条件的记录id。
5. 重复步骤3，4 直到不满足查询条件为止。
6. 现在sort_buffer 中已经存放了满足条件的数据，然后按照字段name 进行排序。
7. 对排序结果取前1000行数据，获取主键id的列表。
8. 使用步骤7获取的主键id的列表，返回数据库中，获取完整的记录。

上述过程称为rowid排序

3. 如何抉择

全字段排序会占用较多的内存，而rowid排序虽然降低了内存使用，但是会多一次回表，增加磁盘的IO操作。至于孰优孰劣，需要根据自己的业务场景，作出自己的选择。

4. 如何对order by的过程进行优化

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

在上述语句的执行过程中，我们发现需要对name字段进行排序，那么我们能不能利用索引有序的的特点，省略对name字段排序的过程呢？答案是可以的。

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

我们在birth_city 和 name 字段上建立 联合索引，那么birth_city 和 name 就是有序的，我们再重新分析一下查询流程，以全字段排序为例。

1. 从索引city_user 中找到第一个满足条件birth_city = '西安' 的记录，取出其主键id。
2. 根据主键id取出来select中出现的字段，直接返回。
3. 从索引city_user获取下一个满足条件的记录id。
4. 重复步骤2，3直到获得1000条记录为止。

如果再优化一下，根据覆盖索引，我们建立索引如下

alter table t add index city_user_name_age(birth_city, name,age);

这样的话就省去了步骤2，减少了一次回表。

但是呢，架构的设计没有银弹，索引字段的增加必然会带来空间的增加以及维护成本的提升，作为开发人员的你还是需要根据业务场景作出自己的选择。

感谢大家的阅读，如果对MySQL想要有深入的了解，可以购买下书。

如果对你有帮助，分享，收藏，赞，在看走一波。