mysql索引的正确使用

 

mysql索引的正确使用

1，mysql使用索引时，采用的是最左匹配原则。

如果是单列索引很容易理解，如果是多列索引，例如idx_a_b_c(a,b,c),则可以发挥索引功能组合为（a）,(b)(a,b,c),并且索引是一次遍历没有回溯的，所以如果要用到两列或者两列以上，那么除了最后一列外，前面的都需要精确匹配才行，因此下面的SQL可以用到索引：

select * from t order by a,b,c;

select * from t where a=constant order by b asc,c asc;

select * from t where a=constatn order by b desc,c desc;

select * from t where a=constant and b-constatn order by c;

select * from t where a=constant and b>constant order by b;

下面的情况用不到索引或用不到索引进行排序。

select *from t  order by a,d;

select *from t where b=constatn order by a;

select * from t where a=constatn order by b asc,c desc;

mysql 8.0已经支持混合排序了，所以mysql8.0之后会支持order by a desc ,b,asc,c desc 这样的语句。

2，mysql在计算列里无法使用索引。

select * from t where abs(a)=constant;

select * from t where f(a)=constant #f这里指任意函数

3，mysql在否定条件中不能使用索引

where 条件里面又<>,not in,not exists的时候，即便是在这些判断字段上加上索引，也不会骑作用。

4，mysql在join中连续字段类型如果不一致，则不能使用索引。

这很容易理解，int类型的字段没办法和varchar类型的字段join，char和 varchar如果在定义表的时候长度一样，就可以利用索引join，反之不行，如char(20)和varchar（20），可以利用索引，char(20)和varchar(25)则不行，不管varchar里面实际存储的值是多少。

另外，如果两个字段列的字符集不同，则不能join，如UTF8,UTF8MB4就不可以。

5，覆盖索引

mysql在利用B+树索引检索数据的时候，如果不是基于聚簇索引，或者说如果不是基于主键的检索，那么即便是SQL语句能够利用索引，但索引返回的信息也只是所需结果行的主键值，要取得全部数据，还需要通过这些主键值重新到数据文件里再做一次检索操作，这样就需要额外的IO，降低了查询效率，如果能优化SQL或索引，让MYSQL只需要通过索引就可以返回所需的数据，而不必回表，从而提升效率，这就是覆盖索引，covering index,如果explain 的extra列的值为using index，则表示目前mysql正在使用覆盖索引，由于覆盖索引减少了一次回表操作，所以它带来的性能提升显而易见，特表对高频查询的SQL，有时通过修改索引可以起到事半功倍的效果.

虽然覆盖索引提升了查询性能，但是由于增加了索引字段，会对写入数据造成一定的影响，同时会占用更能多的空间，所以在决定是否使用覆盖索引的是否，一定要权衡利弊，建议在一些高频的查询上使用覆盖索引。

6，其他一些需要注意的情况。

尽量避免使用like查询，尤其是左边模糊匹配的情形，使得mysql无法利用索引，避免出现using filesort和using temporary.如果有这种情况需要重点关注。