MySQL 性能优化 -索引失效及处理

查询优化，首先是建立索引，那么建立好的索引，相信同学们都遇到过索引失效的情况，你们是怎么处理索引失效的呢？
下面进行索引失效情况的重新以及对应的处理方式。

• 索引失效场景及相对应的解决方案
  先建好要使用的数据表及测试数据，如下：

CREATE TABLE test_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'员工记录表';
INSERT INTO test_staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO test_staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO test_staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

-- 建立一个覆合索引
ALTER TABLE test_staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

几种失效的场景如下：

1. 全值匹配

2. 最佳左前缀法则（如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列）

   针对覆合索引
   1.带头大哥不能死（索引的第一列）
   2.中间兄弟不能断（索引后面的其他列）

   -- 通过上面建好的索引，我们来写一个索引失效的 SQL，理解一下，全值匹配这种场景
   
   -- 下面三种情况，使用索引，没有失效，因为查询条件与索引列的顺序完全一致
   select * from test_staffs where name = 'July';
   select * from test_staffs where name = 'July' and  age = '23';
   select * from test_staffs where name = 'July' and  age = '23' and pos = 'dev';
   
   -- 索引失效
   select * from test_staffs where age = '23';
   select * from test_staffs where pos = 'dev';
   select * from test_staffs where age = '23' and pos = 'dev' and name = 'July';
   

   使用 explain 分析索引失效场景的三种 SQL，结果为：
   
   

3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
   

4. 存储引擎不能使用索引中范围条件右边的列(范围以后全失效)
   

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

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

7. is null ,is not null 也无法使用索引

8. like以通配符开头(%abc…)mysql索引失效会变成全表扫描的操作（%放在右边，使用range 范围索引）
   

   面试题： 使用 like （%xxx%）会导致索引失效，那么业务中必须使用此种方式，如何保证索引不失效？
   答：使用覆盖索引处理索引失效问题，具体参考下面的例子进行说明，深刻理解覆盖索引。
   1、没有创建任何索引的情况下，查看结果如下：
   
   
   2、创建覆合索引，来解决 like 包含索引失效的情况
   create INDEX idx_user_nameAge on test_tbl_user(name,age);
   

9. 字符串不加单引号索引失效
10. 少用or,用它来连接时会索引失效

案例总结：
通过此篇文章，可以深刻理解到了索引失效的场景，以及对应的解决方式，相信小伙伴们以后在开发过程中写 SQL也会有自己的一些想法了。