查询优化,首先是建立索引,那么建立好的索引,相信同学们都遇到过索引失效的情况,你们是怎么处理索引失效的呢?
下面进行索引失效情况的重新以及对应的处理方式。
- 索引失效场景及相对应的解决方案
先建好要使用的数据表及测试数据,如下:
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.中间兄弟不能断(索引后面的其他列)-- 通过上面建好的索引,我们来写一个索引失效的 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,结果为:
-
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
-
存储引擎不能使用索引中范围条件右边的列(范围以后全失效)
-
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
-
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
-
is null ,is not null 也无法使用索引
-
like以通配符开头(%abc…)mysql索引失效会变成全表扫描的操作(%放在右边,使用range 范围索引)
面试题: 使用 like (%xxx%)会导致索引失效,那么业务中必须使用此种方式,如何保证索引不失效?
答:使用覆盖索引处理索引失效问题,具体参考下面的例子进行说明,深刻理解覆盖索引。
1、没有创建任何索引的情况下,查看结果如下:
2、创建覆合索引,来解决 like 包含索引失效的情况
create INDEX idx_user_nameAge on test_tbl_user(name,age);
9. 字符串不加单引号索引失效
10. 少用or,用它来连接时会索引失效
案例总结:
通过此篇文章,可以深刻理解到了索引失效的场景,以及对应的解决方式,相信小伙伴们以后在开发过程中写 SQL也会有自己的一些想法了。