SQL优化——order by优化

1.1.order by优化

1.1.1.知识点回顾

在讲解order by优化前，先回顾一下order by的语法知识。

• order by是DQL（Data Query Language ）查询语句中用于给字段排序的语句。其语法规则为：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

其中，排序方式有升序（asc，默认值），降序（desc）

• 如果未指定排序方式，表示默认升序，例如根据age字段进行升序排序可以写成：

SELECT * FROM tb ORDER BY age;
SELECT * FROM tb ORDER BY age asc;

• 如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ，例如：

SELECT * FROM tb ORDER BY id asc , age desc;

1.1.2.两种排序方式

• MySQL有两种排序方式Using filesort和Using index，Using index的性能高于Using filesort，我们在优化排序操作时，尽量要优化为 Using index
• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

举个栗子：

假设在表tb_user中包含有两个字段age和phone，我们想通过这两个字段进行排序，且事先我们没有创建age和phone字段的索引，直接进行order by排序：

select age,phone from tb_user order by age, phone ;

查看执行过程：

explain select age,phone from tb_user order by age, phone ;

结果如下：由于字段age和phone都没有建立索引，因此使用Using filesort排序

假如我们先创建字段age和phone的联合索引，再进行order by排序，查看执行情况：

create index idx_user_age_phone_aa on tb_user(age,phone);

explain select age,phone from tb_user order by age; 

结果如下：创建字段age和phone的联合索引后为using index排序，性能更好。

1.1.3.order by优化案例演示

1.1.3.1.案例A

在上面我们创建了字段age和phone的联合索引，而且没有指定索引的排序顺序，此时索引在表中默认是按照升序排列的。

我们可以通过以下指令，查看表tb_user当中的索引情况：

show index from tb_user;

可以发现age和phone的联合索引默认按照A，即升序排列

假如查询时字段age, phone都进行order by排序，且均为降序排序，查看执行情况：

explain select age,phone from tb_user order by age desc , phone desc ; 

结果如下：显示Backward index scan，表示反向扫描索引。因为在MySQL中默认索引是升序排序的，而此时我们查询排序时两个字段均按照降序查询，因此均为反向扫描索引，也属于using index排序，性能较好。

1.1.3.2.案例B

假如根据phone，age两个字段进行order by排序，但是phone在前，age在后，查看执行情况：

explain select age,phone from tb_user order by phone , age; 

结果如下：由于此时排序顺序为phone在前，age在后，而创建联合索引时的顺序是age在前，phone在后，不满足索引使用的最左前缀法则，因此通过using filesort排序，性能较差

1.1.3.3.案例C

假如查询时根据age, phone两个字段进行order by排序，age升序，phone降序，查看执行情况：

explain select id,age,phone from tb_user order by age asc , phone desc ;

结果如下：因为创建联合索引时未指定顺序，索引默认按照升序排序，而查询时，如果是一个升序，一个降序，此时就会出现Using filesort，性能较差。

1.1.3.4.案例D

创建字段age和phone一个新的联合索引，并且指定了新索引叶子节点中字段排序的顺序：age 升序排序，phone 倒序排序：

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

查询时再根据age, phone两个字段进行order by排序，age升序，phone降序，查看执行情况：

explain select age,phone from tb_user order by age asc , phone desc ;

结果如下：因为新的联合索引指定了索引的排序是age升序，phone降序，而order by排序时也是按照相同的顺序，因此此时为using index，性能较好。

1.1.4.索引结构可视化

如果查询时对age和phone都进行order by排序，且均指定为升序排序：

explain select id,age,phone from tb_user order by age asc , phone asc ;

对应的索引结构图如下：在联合索引的叶子节点中，先按照字段age进行升序排序，当age相同时，再按照字段phone升序排序

如果按照字段age和phone一个升序排序，一个降序排序：

explain select id,age,phone from tb_user order by age asc , phone desc ;

1.1.5.总结

由上述的案例，我们得出order by排序优化原则:

• MySQL有两种排序方式Using filesort和Using index，在优化排序操作时，尽量要优化为 Using index
• 根据排序字段建立合适的索引，多字段排序时，索引需要遵循最左前缀法则。
• 尽量使用覆盖索引，避免回表查询。
• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。