mysql5.7 索引where和orderby排序问题

索引(a,b,c)

select * from table where a=1 and b=2 order by a;  索引排序
select * from table where a=1 and b=2 order by b;  索引排序
select * from table where a=1 and b=2 order by c;  索引排序
select * from table where a=1 order by a;  索引排序
select * from table where a=1 order by b;  索引排序
select * from table where a=1 order by c;  Using filesort

结论：全等情况下，

1、where a和b，abc索引排序，

2、where a，ab索引排序

select * from table where a>1 order by a;  索引排序
select * from table where a>1 order by b;  Using filesort
select * from table where a>1 and b=2 order by a;  索引排序
select * from table where a>1 and b=2 order by b;  索引排序
select * from table where a>1 and b=2 order by c;  Using filesort
select * from table where a>1 and b=2 and c=3 order by c;  索引排序
select * from table where a>1 and b>2 order by b;  Using filesort
select * from table where a>1 and b>2 and c= 3 order by c;  索引排序
select * from table where a=1 and b>2 order by b;  索引排序
select * from table where a=1 and b>2 order by c;  Using filesort
select * from table where a=1 and b>2 and c=3 order by c;  索引排序
select * from table where a=1 and b>2 and c>3 order by c;  Using filesort

结论：带范围的情况下，

1、order by字段必须出现在where中，除了第一次范围，order by字段必须在where中全等的情况下才能索引排序