order by也可以利用最左前缀
KEY abc_index (a,b,c)
以下可以使用最左前缀索引
order by a
order by a,b
order by a,b,c
where a=x order by b,c
where a=x and b=y order by b,c
where a=x and b>y order by b,c
where a=x and b>y and b<z order by b,c
where a=x and b>y and b<z order by b,c
以下不可以使用最左前缀索引
where b=x order by a,c
where a>x and b=y and c=z
where a>x order by b,c
where a in (x,y,z) order by b,c
order by a desc , b asc, c desc 排序类型不同
实战分析
以下是我建立的索引
通过EXPLAIN测试:
1、可以适用索引的情况
where a=x order by b
可以使用索引
where a=x and b>y and b<z order by c
可以使用索引
where a=x and b>y order by c
可以使用索引
where a=x order by a,b
可以使用索引
where a=x and b=y order by b
可以使用索引
where a=x and b=y order by a,b
可以使用索引
2、不可以适用索引的情况
where a>x order by b
不能使用索引
where a>x and b=y
不能使用索引
where a>x order by a, b
不能使用索引
order by a asc, b desc` 排序类型不同,不能使用索引
where b=x order by a ,b ` 排序类型不同,不能使用索引