在讲解order by优化前,先回顾一下order by的语法知识。
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
其中,排序方式有升序(asc,默认值),降序(desc)
SELECT * FROM tb ORDER BY age;
SELECT * FROM tb ORDER BY age asc;
SELECT * FROM tb ORDER BY id asc , age desc;
举个栗子:
假设在表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排序,性能更好。
在上面我们创建了字段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排序,性能较好。
假如根据phone,age两个字段进行order by排序,但是phone在前,age在后,查看执行情况:
explain select age,phone from tb_user order by phone , age;
结果如下:由于此时排序顺序为phone在前,age在后,而创建联合索引时的顺序是age在前,phone在后,不满足索引使用的最左前缀法则,因此通过using filesort排序,性能较差
假如查询时根据age, phone两个字段进行order by排序,age升序,phone降序,查看执行情况:
explain select id,age,phone from tb_user order by age asc , phone desc ;
结果如下:因为创建联合索引时未指定顺序,索引默认按照升序排序,而查询时,如果是一个升序,一个降序,此时就会出现Using filesort,性能较差。
创建字段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,性能较好。
如果查询时对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 ;
由上述的案例,我们得出order by排序优化原则:
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。