假设t1表存在索引key1(key_part1,key_part2),key2(key2)
可以利用索引避免排序的SQL
SELECT * FROM t1 ORDER BY key_part1,key_part2;
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
注意
1、mysql 中默认排序是 升序ASC
2、当需要排序的 字段 在联合索引当中,要确保按照索引的顺序来,不然无法使用索引。
比如,key(a,b,c)。
查找1:``SELECT * FROM t1 where a = 10 ORDER BY c;`` 不命中索引
查找2:``SELECT * FROM t1 where a = 10 ORDER BY b;`` 命中索引
查找3:``SELECT * FROM t1 ORDER BY b, a;`` 不命中索引
查找4:``SELECT * FROM t1 ORDER BY a, b;`` 命中索引
不能利用索引避免排序的SQL
//排序字段在多个索引中,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1,key_part2, key2;
//排序键顺序与索引中列顺序不一致,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part2, key_part1;
//升降序不一致,无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
//key_part1是范围查询,key_part2无法使用索引排序
SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
参考:
1、https://juejin.im/post/5b49ba926fb9a04fc436b1b4
2、https://www.cnblogs.com/moss_tan_jun/p/6021822.html