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)