索引优化

素材：student表

列信息：

一、复合索引按索引中的字段顺序进行检索数据

复合索引可以由好几个字段组成，在查询语句中若想使用该索引，必须按建索引字段的先后顺序使用。例如在student_num，name，age上建立一个复合索引

create index id_num_name_age on student(student_num, name, age);

例1：查询name=‘lzj’，age=27的记录

从explain分析中可以看到，type=ALL，表示全表进行的扫描；possible_keys和key都是NULL，表示没有使用索引。建立的复合索引是student_num、name、age，但是本例中只使用了name、age，略过了student_num，则不能正常使用该索引。
结论：复合索引从第一个字段开始，如果没有第一个字段，则不能正常使用复合索引。

例2：查询student_num=1, age=27的记录

从explain分析中可知，type=ref，表示非唯一索引检索；key=id_num_name_age，表示使用到了该复合索引；key_len=5，表示使用的索引长度为5，student_num是int类型，4个字节，null占一个字节，因此只使用了复合索引中的第一个字段student_num；ref=const表示student_num上用的一个常量1进行检索。本例中使用了student_num、age进行检索，实际上只使用了复合索引id_num_name_age的中的第一个索引student_num。
结论：对于使用复合索引时，一旦不能按建复合索引时字段的顺序进行使用，则连续中断的地方后面的索引无效，只使用了中断前面的索引。

二、不要在索引上做任何操作，否则导致索引失效，进而全表扫描

在用建立索引的字段进行查询时，不要在字段上进行计算、或调用mysql内置函数等，否则索引失效。
例如：查询student_num=1，name的前两个字符为’lz’的记录

从explain的分析中可以看出，key_len=5，表示只只使用了student_num字段上的索引；ref中只有一个const，表示student_num使用了常量1。因此只有studnet_num字段上的索引生效了。
结论：不要在索引字段上进行任何操作。

三、建立复合索引的几个列中，若中间的一个列是范围，则该列后面的列上的索引无效（该列是有效的）

删除前面已经建立的索引

drop index id_num_name_age on student;

然后建立新的索引

create index id_num_name_age_class on student(student_num, name, age, class);

下面查询student_num=1 and name=’lzj’ and age>20 and class=1条件下的记录

从explain分析中可以看出，type=range，表示索引检索的时范围；key_len=41，表示用到了student_num、name和age上的索引，studnet_num占用了5个字节，name时char(10)类型的并且默认为NULL，mysql中默认utf-8存储的，一个utf-8字符占3个字节，所以name=’lzj’占3*10个字节，另外一个NULL占一个字节，age占5个字节，共41个字节。因此上述条件中只用到了student_num、name、age上索引，age后面的class上的索引失效。

四、尽量使用覆盖索引

覆盖索引就是索引列与查询列一致，开发中应少使用select * 之类的。
继续使用上面的id_num_name_age_class索引
例1：查询student_num=1 and name=’lzj’ and age=27 and class=1的记录

例2：同样查询student_num=1 and name=’lzj’ and age=27 and class=1的记录，只查索引列

例2中用同样的条件去查询，但是只select索引类student_num、name、age、class列，在Extran中出现了Using index，使用了覆盖索引，查询时只需要从索引中查询需要的数据，不需要再去扫描表了，因此效率会更快。一般的查询会先从索引中找到记录，然后根据索引中记录去表中查找相应记录，因此效率不如覆盖索引高。

select student_num, name, age, class from student where student_num=1 and name='lzj';
select name from student where student_num=1 and name='lzj' and age=27 and class=1;

上面两条SQL都使用了覆盖索引，只要where条件中用到了索引，并且select中只查索引列，就会使用覆盖索引。覆盖索引是推荐使用的。

五、索引中使用 “!=” 或者 “<>”会导致索引失效

删除id_num_name_age_class索引，在name字段上建立id_name索引。

查询name=’lzj’的记录

查询name != ‘lzj’的记录

通过explain分析，key=NULL，没有使用上索引id_name，因此使用“!=”导致了索引失效，进而全表扫描。

六、is null， is not null导致索引失效

继续使用id_name索引。
例1: name列默认为NULL情况

在name列默认为空的前提下，查询name is null的情况下使用到了索引；查询name is not null 的情况下没有使用索引。

例2：name列默认为 NOT NULL的情况
修改name列的属性

alter table student modify column name char(10) NOT NULL;

当name列NOT NULL的情况下，此时查询name is null和name is not null时都没有使用上name列上的索引id_name。

七、like通配符导致索引失效

继续使用id_name索引。

通过explain分析，发现用like进行匹配时，只有首字母’lz%’不需要匹配的情况才使用上了索引，其它两种情况都没有使用上索引。

如果一定要用like形式，解决索引失效的情况，可以使用覆盖索引

从explain分析中可知，使用覆盖索引时使用到了id_name索引。也可以用主键索引进行查询，也可避免like索引失效的情况。

八、字符串不加单引号导致索引失效

继续使用id_name索引。
在student表中插入一条数据

insert into stuent values(8, 4, '110', 100, 1);

从explain分析中可见，name=110，字符串没有使用单引号，误认为是整形（但在使用时，mysql会自动转型为字符类型），导致索引失效。

九、慎用or，导致索引失效

继续使用id_name索引。
查询name=’lzj’ 或者name=’Bob’的记录

从explain分析可见，key=NULL，并没有使用上索引。