Mysql——索引失效

1、建表

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间') CHARSET utf8 COMMENT '员工记录表' ;  

2、插入数据

INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());  

3、创建索引

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);  

4、索引失效
 a、全值匹配(查询条件的顺序和索引的顺序一致，这种方式最好，能够充分发挥索引的作用，使用and连接的查询提交也可以不与索引的顺序一致，mysql会自动优化)

 b、最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列，尤其是索引的头。如果查询条件中没有索引的头，会导致索引失效，也就是说使用索引的时候是从头开始，且索引键之间不能断

 c、不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描，在查询语句中不要使用表达式，会严重影响性能

  Tip：索引列上不要使用表达式，如where substr(a, 1, 3) = ‘hhh’、where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有订单记录的数据，再在程序中去筛选。
 d、存储引擎不能使用索引中范围条件右边的列，遇到范围，则范围条件后的索引全部失效，所以最好不要在索引列中使用范围

 e、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

 f、mysql 在使用不等于(!= 或者<>)的时候无法使用索引，会导致全表扫描

 g、is null 、is not null 也无法使用索引

 h、like以通配符开头(’%abc…’)，mysql索引会失效，会变成全表扫描的操作，不以通配符开头时可以

 i、字符串不加单引号索引失效

 j、少用or，用它来连接时会导致索引失效

 小总结： 假设index(a,b,c)

where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4	N，索引必须从头开始
where a = 3 and c = 5	使用到a， 但是C不可以，中间断了
where a = 3 and b > 4 and c = 7	使用到a和b， c在范围之后，断了
where a = 3 and b like ‘kk%’ and c = 4	使用到a和b， c在模糊查询的通配符之后，断了

 注意：
  1️⃣出现在Order by 后的索引列都是用于排序的，不会用于查找，所以索引无效
  2️⃣定值、范围还是排序，一般order by是给个范围
  3️⃣group by 基本上都需要进行排序，会有临时表产生