索引的使用

1.where 条件句

带有索引字段的条件排在前面。
联合索引不要拆开，遵循最左原则。

2.order by

可以使用索引的情况：

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

无法使用索引的情况：

• The query uses ORDER BY on different indexes:

SELECT * FROM t1 ORDER BY key1, key2;

• The query uses ORDER BY on nonconsecutive parts of an index:

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;

• The query mixes ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

• The index used to fetch the rows differs from the one used in the ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

• The query uses ORDER BY with an expression that includes terms other than the index column name:

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

asc和desc混合不能使用索引
分别建立不同的索引，key1,key2，无法使用
key_part1,key_part3 不连续无法使用
使用函数表达式不能使用索引

当order by字段出现在where条件中时，才会利用索引而无需排序操作
这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by、union、distinct等。

3.group by

4.having