[大数据]MySQL中order by的优化方案

MySQL中有两种排序方式：

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况下使用的是using index，不需要额外的排序，操作效率高。

上述的这两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作的时候，尽量优化为Using index。

上面都是理论，我们直接上测试！

数据准备：

这里我用的表是tb_user，且其中已经有一些数据了，而且还有一些索引，我们需要先把多余的索引删除掉

表中数据：

原有索引：

删除索引所需的sql语句：

DROP INDEX idx_email_5 ON tb_user;
DROP INDEX idx_user_pro_age_sta ON tb_user;
DROP INDEX idx_user_name ON tb_user;
DROP INDEX idx_user_phone ON tb_user;
DROP INDEX idx_user_age ON tb_user;

这时候就只有一个主键索引了：

这时候准备工作都做完了，开始测试！

先来执行一条排序SQL，这时候排序没有用到索引

SELECT id,age,phone FROM tb_user ORDER BY age;

但是单纯执行上面这条语句的话，只会显示这条语句的执行结果，并不能得知mysql对这条语句的执行计划，所以就用到了mysql中的explain命令，网上对它的解释是这样的：

explain命令主要用于查看SQL语句的执行计划，该命令可以模拟优化器执行SQL查询语句，从而知道mysql是如何处理用户的SQL语句，语法为“explain SQL语句;”。

所以我们的sql语句改为

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age;

可以看到因为排序所用到的条件age并没有对应的索引，索引会用到排序缓冲区，也就是把数据读出来，然后在排序缓冲区中进行排序，最后展示出来。

再来看一个例子，sql语句为

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age,phone;

同样还是使用的排序缓冲区。

那我们该怎么将Using filesort优化为Using index呢，这时候就要加索引了，如下

-- 给tb_user中的age和phone创建索引
CREATE INDEX idx_user_age_phone_ap ON tb_user(age,phone);

创建完索引后，根据age和phone再次进行升序排序，看看执行计划有什么变化

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age,phone;

这时候就可以很清楚的看到使用的key为我们创建的索引，由之前的Using filesort变为了Using index了，性能比较高了，毕竟走索引了嘛

这时候就又有一个问题了，order by默认是升序排序，正好我们建的age和phone的索引也是升序排序。那如果我们指定order by是降序排序呢，会出现什么问题

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age DESC,phone DESC;

可以从Extra发现，出现了Using index，但是此时Extra中还出现了Backward index scan，这个代表反向扫描索引，因为我们在MySQL中创建age和phone的索引的时候，默认索引的叶子节点都是从小到大进行排序的，而此时我们查询排序时，是从大到小的，所以，在扫描时，就是反向扫描，就会出现Backward index scan。尽管使用了反向扫描，但是性能和升序的一样啊，因为都是基于索引排序的。 PS：在MySQL8的版本中，支持降序索引，我们也可以创建降序索引

再来看一种情况，我使用了age和phone作为排序条件，但是吧，我把phone放在前面，age放在后面，这时候还会走索引吗

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY phone,age;

会发现，order by使用条件的时候，也需要满足最左前缀法则，否则会出现filesort。因为在创建索引的时候，age是第一个字段，phone是第二个字段，所以排序的时候，也需要按照这个顺序来，否则就会出现Using filesort。

又出现一个问题，我如果根据age和phone进行排序，一个升序，一个降序的话，会Using index吗

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age ASC,phone DESC;

我们在创建索引的时候，如果未指定顺序，默认都是按照升序排序，而查询时，一个升序，一个降序，此时就会出现Using filesort。

为了解决上面的问题，我们可以创建一个索引，这个联合索引中age升序排序，phone降序排序，这样问题就解决了。

CREATE INDEX idx_user_age_phone_apd ON tb_user(age ASC,phone DESC);

EXPLAIN SELECT id,age,phone FROM tb_user ORDER BY age ASC,phone DESC;

问题解决，一起来看看升序/降序索引结构图示：

经过上面这么多的测试，可以得出order by的优化原则：

• 根据排序字段建立合适的索引，多字段排序的时候，也遵循最左前缀法则。

• 尽量使用覆盖索引

  简单解释一下覆盖索引：

  

  就比如我上面用红圈框起来的两行数据，我们使用SELECT id,age,phone FROM tb_user ORDER BY age ,phone ;的时候，发没发现我们要找的id、age、phone字段在框里面都有，这时候我们就不用回表查询了，如果我们的语句为：SELECT id,age,phone,name FROM tb_user ORDER BY age ,phone ;，这时候我们会发现name不在这颗索引树上，但是我们有想要查询name的id啊，MySQL就会根据这个id，也就是1，从主键索引上根据这个id为1进行查询，唉，这时候就不单单使用我们创建的age和phone的索引了，还用到了id的主键索引，又会用到了Using filesort了。

• 多字段排序的时候，一个升序，一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

• 如果不可避免地出现了filesort，大数据量排序地时候，可以适当增加排序缓冲区大小，sort_buffer_size（默认256K）