MySQL高级 避免索引失效的规则
索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。很多时候我们因为而在使用SQL时违背了一些规则，导致查询未走索引，效率降低。我们希望索引生效，让执行效率提高。
（1）全值匹配
对索引中所有的列都指定具体值。
比如我们现在在患者表的sex, homtown, cur_condition建立一个多列索引。因为在系统中根据这三项查询患者的的功能时提供给用户的，所以这也是一条使用率很高的SQL语句。建立索引后对我们的查询作explain查看执行情况。
create index find_patient on s_patient(sex,hometown,cur_condition); explain select * from s_patient where sex='男' and hometown='上海' and cur_condition='治愈';
explain select * from s_patient where sex='男' and cur_condition='治愈' and hometown='西安';
#只要字段全，顺序可以互换
（2）最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from s_patient where sex='男';
#符合法则
explain select * from s_patient where hometown='上海' and cur_condition='治愈';
#违背最左前缀法则，索引失效
explain select * from s_patient where sex='男' and cur_condition='治愈';
#符合最左，但是跳过中间列，则只有最左列索引生效
（3）范围查询
包含范围查询时，范围查询列右边的列不走索引；与上面一样，左右都是指建立索引时的左右顺序，不是表序，也不是查询后where接的字段序。下面的结果中只有性别一列走了索引（通过key_len可以看出）。
explain select * from s_patient where hometown>'1' and sex='男';
（4）索引列不加运算
如果在索引列上进行运算操作， 列右边开始索引将失效；如果字符类别不加' '也算运算，底层会作类型转换相当于运算。下面的查询只有hospital_id走索引。
create index find_patient2 on s_patient(hospital_id,username,hometown); explain select * from s_patient where hospital_id=1 and substring(username,1,0)='张三';
（5）使用覆盖索引
尽量使用覆盖索引，避免select *，即只访问索引包含的列，这样可以免去回表查。下图中第一条没有满足覆盖索引，Extra中的信息为Using index condition，表示用到了索引但是需要回表查询，此效率低于Using where; Using index；而系统中对这条SQL也只需要返回其中的username，所以可作此优化。
explain select * from s_patient where username='张三' and hospital_id=1 and hometown='北京'; explain select username from s_patient where username='张三' and hospital_id=1 and hometown='北京';
（6）or连接
用or分割开的条件， 如果or条件连接的列中有没有索引的，则整体索引失效。下面查询中，手机号列没有索引，导致整体查询索引为NULL。
explain select * from s_patient where sex='男' or mobile='123';
（7）like模糊匹配
如果仅仅是xxx%结尾的尾部模糊匹配，索引不会失效，多列索引也可以生效。如果包含%开头的头部模糊匹配，索引失效。
explain select * from s_patient where username like '%张%';
解决方式：覆盖索引
（8）数据特征
如果MySQL评估使用索引比全表更慢，则不使用索引。这种情况出现的原因是数据特征不显著，比如下面的查询，表中只有一条数据的mobile是'1234'，其余的都是'123'，这种情况下按='1234'查是走索引的，而如果按='123'查，MySQL会发现走索引不如遍历整表更快，则索引失效。is NULL和is NOT NULL原理相同，数据在表中太不显著，则不走索引。
create index find_doctor on s_doctor(mobile); explain select * from s_doctor where mobile='123'; explain select * from s_doctor where mobile='1234';
（9）in走索引，not in不走索引
下面的查询是系统中比较复杂的SQL之一，利用了子查询，如果是not in则表示找出非武汉地区患者的用药情况。
create index med_find_patient_hometown on s_medicine(patient_id); explain select * from s_medicine where patient_id in (select id as patient_id from s_patient where hometown='武汉'); explain select * from s_medicine where patient_id not in (select id as patient_id from s_patient where hometown='武汉');
（10）单列索引与复合索引
能使用复合索引的情况下，尽量使用复合索引而少使用单列索引。下面的例子回到最开始建的索引上。
复合索引：
create index find_patient on s_patient(sex,hometown,cur_condition); -- 相当于创建了三个索引： -- sex -- sex + hometown -- sex + hometown + cur_condition
单列索引：
create index find_patient_sex on s_patient(sex); create index find_patient_hometown on s_patient(hometown); create index find_patient_condition on s_patient(cur_condition); -- 数据库会使用一个最优的索引（辨识度最高的索引），而不会全使用
以上就是使用索引时的一些规则，最后补充一个查看索引使用情况：
show status like 'handler_read%';
-- Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低 -- 越好）。 -- Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的 -- 性能改善不高，因为索引不经常使用（这个值越高越好）。 -- Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列， -- 该值增加。 -- Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。 -- Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。 -- 你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应 -- 该建立索引来补救。 -- Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说 -- 明你的表索引不正确或写入的查询没有利用索引。
