1.索引失效
先创建符合索引，三个字段
ALTER table abilityassessrecord add INDEX idx_customerno_roomno_abilityassessrecord_customername (customerno,roomno,customername);
SHOW INDEX FROM abilityassessrecord;
2简单使用索引
1)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1
索引生效，key_len长度为4，只用到了索引的的customerno字段
2)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND roomno = 1
索引生效，key_len长度为12，用到了索引的的customerno和roomno字段
3)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND roomno = 1 AND customername = 'aaa'
索引生效，key_len长度为75，用到了索引的的customerno和roomno和customername字段
3复合索引的第一个字段必须使用到
1)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1
索引生效，key_len长度为4，只用到了索引的的customerno字段
2)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE roomno = 1
没有用到索引。复合索引要生效的话，复合索引的第一个字段需要被使用到
3)示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername = 'aaa'
没有用到索引。复合索引要生效的话，复合索引的第一个字段需要被使用到
4索引字段需连续使用不断开
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND roomno = 1
索引生效，key_len长度为12，用到了索引的的customerno和roomno字段
2）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND customername = ''
索引生效，key_len长度为4，用到了索引的的customerno，但是customername没有用到。因为复合索引除了第一个字段需要被使用到，字段的使用需要连续(这里的连续是指按照插件索引时的三个字段的顺序，在条件里面哪个写在前面哪个写在后面不影响，只有用到了就行)。这里查询条件用到customerno和customername，但是索引中第二个字段roomno没有用到，断开了，索引只有customerno生效
3）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername = 'aaa' AND roomno = 1 AND customerno = 1
索引以生效，三个字段都用到了，在条件里面哪个写在前面哪个写在后面不影响，只有用到了就行
4）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE roomno = 1 AND customername = ''
没有用到索引，因为索引的第一个字段没有用到
5只查询索引字段
1）示例
EXPLAIN SELECT * FROM abilityassessrecord
没有用到索引，数据全文检索
2）示例
EXPLAIN SELECT customerno FROM abilityassessrecord
EXPLAIN SELECT roomno FROM abilityassessrecord
EXPLAIN SELECT customername FROM abilityassessrecord
EXPLAIN SELECT customerno,roomno FROM abilityassessrecord
EXPLAIN SELECT customerno,customername FROM abilityassessrecord
EXPLAIN SELECT roomno,customername FROM abilityassessrecord
EXPLAIN SELECT customerno,roomno,customername FROM abilityassessrecord
用到索引。这里由于查询的只是索引中包含的字段，所以可以直接在索引中查询返回，不用再去找数据表
3）示例
EXPLAIN SELECT customerno,roomno,customername,sex FROM abilityassessrecord
没有用到索引，效果和select*是一样的
4）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1
索引生效
6索引字段使用到计算、函数、类型转换会导致该字段及之后的字段失效
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno+1 = 1
索引失效，条件里面索引字段使用到计算、函数、类型转换(手动或者自动的)，会导致索引失效
2）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE MOD(customerno,2) = 1
索引失效，条件里面索引字段使用到计算、函数、类型转换(手动或者自动的)，会导致索引失效
3）示例
EXPLAIN SELECT *,MOD(customerno,2) mo FROM abilityassessrecord WHERE customerno = 1
索引生效,在select用到函数等不影响
4）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND roomno = 1
索引生效，key_len为12，用到了customerno和roomno
5）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername = 'aaa' AND MOD(roomno,2) = 1 AND customerno = 1
索引生效，key_len为4，因为roomno用到了函数，失效了，roomno它及它后面的customername 也跟着失效
7索引字段使用到!=、<>会导致该字段之后的字段失效
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername = 'aaa' AND roomno != 1 AND customerno = 1
索引生效，key_len124，用到了customerno和roomno，customername失效
8索引字段使用到is not null会导致该字段和之后的字段失效
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername = 'aaa' AND roomno is not null AND customerno = 1
索引生效，用到了customerno字段，roomno和customername失效
2）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername is null AND roomno = 1 AND customerno = 1
索引生效，三个字段都用到了，is null不影响索引的使用
9索引字段模糊查询like使用%开头会导致该字段和之后的字段失效
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername like '%aa%' AND roomno = 1 AND customerno = 1
索引生效，用到了customerno和roomno字段，roomno和customername失效
2）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customername like 'aa%' AND roomno = 1 AND customerno = 1
索引生效，三个字段都用到了
10使用or索引失效
1）示例
EXPLAIN SELECT * FROM abilityassessrecord WHERE customerno = 1 AND roomno = 1 or customername = 'aa'
索引失效