Mysql | order by

在开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求，例如下列的sql

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

为避免全表扫描，我们需要在 city 字段加上索引。 在 city 字段上创建索引之后,用 explain 命令来看看这个语句的执行情况。

image.png

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

通常情况下，这个语句执行流程如下所示 ：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；

从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；

对 sort_buffer 中的数据按照字段 name 做快速排序；

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

怎么优化它呢？

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

加上联合索引，使用到了覆盖索引，

image.png

Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多

这样整个查询语句的执行流程就变成了：

从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回； 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

Mysql | order by