前言
作者简介：半旧518，长跑型选手，立志坚持写10年博客，专注于java后端
专栏简介：mysql基础、进阶，主要讲解mysql数据库sql刷题、进阶知识，包括索引、数据库调优、分库分表等
文章简介：本文将介绍索引失效的10种情况及原理，绝对不需要死记硬背，建议收藏备用。
相关推荐：
MySql进阶索引篇01——深度讲解索引的数据结构：B+树
Mysql进阶索引篇02——InnoDB存储引擎的数据存储结构
Mysql进阶索引篇03——2个新特性，11+7条设计原则教你创建索引
Mysql进阶优化篇01——四万字详解数据库性能分析工具(深入、全面、详细，收藏备用)
大厂SQL面试真题大全

文章目录

1️⃣ 数据库调优的场景
2️⃣数据准备
- (1)建表
- (2)设置参数
- (3)创建函数
- (4)创建存储过程
- (5)调用存储过程
- (6)删除某表上的索引
3️⃣索引失效的情况
- 3.1 全值匹配我最爱(索引最佳)
- 3.2 不遵守最左前缀匹配原则
- 3.3 不按照递增顺序插入主键
- 3.4 计算、函数、类型转换（自动或手动）导致索引失效
- 3.5 类型转换导致索引失效
- 3.6 范围条件右边的列索引失效
- 3.7 不等于（!= 或者）索引失效
- 3.8 is null可以使用索引，is not null无法使用索引
- 3.9 like 以通配符 % 开头索引失效
- 3.10 OR前后存在非索引的列
- 3.11 数据库和表的字符集不匹配
4.索引一般性建议

1️⃣ 数据库调优的场景

上一篇mysql进阶优化篇，我们介绍了数据库的性能分析工具，知道了怎么发现数据库的性能问题，这一篇博客我们将介绍索引失效的10种情况及原理

先来了解下可能需要进行数据库优化的场景。

索引失效(按准则编写、调整sql)
没有充分利用到索引(建立索引)
关联查询太多的JOIN(JOIN查询性能与表的数目成指数相关，一般不超过三张，否则需要进行sql优化或者进行反范式化设计，增加必要的冗余)
服务器调优与各个参数的设置，如缓存、线程数等(修改my.conf)
数据过高(已经在软件层面充分调优，但仍不能面对高并发场景，可以考虑分表分库分散服务器压力)

接下来我们介绍下sql查询优化。虽然sql查询优化技术很多，但是大致不离 物理查查询优化 和 逻辑查询优化 两大块。

物理查询优化：通过索引和表连接方式进行优化
逻辑查询优化：通过sql语句的等价代换，实现数据库查询的优化。

2️⃣数据准备

学员表插50万条，班级表插1万条。

(1)建表

#班级表CREATE TABLE `class` (`id` INT(11) NOT NULL AUTO_INCREMENT,`className` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,`monitor` INT NULL ,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;#学员表CREATE TABLE `student` (`id` INT(11) NOT NULL AUTO_INCREMENT,`stuno` INT NOT NULL ,`name` VARCHAR(20) DEFAULT NULL,`age` INT(3) DEFAULT NULL,`classId` INT(11) DEFAULT NULL,PRIMARY KEY (`id`)#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

(2)设置参数

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

(3)创建函数

保证每条数据都不同。

#随机产生字符串DELIMITER //CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGINDECLARE chars_str VARCHAR(100) DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1;END WHILE;RETURN return_str;END //DELIMITER ;#假如要删除#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号DELIMITER //CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num +RAND()*(to_num - from_num+1));RETURN i; END //DELIMITER ;#假如要删除#drop function rand_num;

(4)创建存储过程

创建往stu表中插入数据的存储过程

#创建往stu表中插入数据的存储过程DELIMITER //CREATE PROCEDURE insert_stu(START INT , max_num INT )BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; #设置手动提交事务REPEAT#循环SET i = i + 1;#赋值INSERT INTO student (stuno, name ,age ,classId ) VALUES((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); UNTIL i = max_num END REPEAT; COMMIT;#提交事务END //DELIMITER ;#假如要删除#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往class表添加随机数据DELIMITER //CREATE PROCEDURE `insert_class`( max_num INT )BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0;REPEAT SET i = i + 1; INSERT INTO class ( classname,address,monitor ) VALUES(rand_string(8),rand_string(10),rand_num(1,100000)); UNTIL i = max_num END REPEAT; COMMIT;END //DELIMITER ;#假如要删除#drop PROCEDURE insert_class;

(5)调用存储过程

往class表添加1万条数据

#执行存储过程，往class表添加1万条数据 CALL insert_class(10000);

往stu表添加50万条数据,这个时间会稍微有点长，请耐心等待哟。

#执行存储过程，往stu表添加50万条数据 CALL insert_stu(100000,500000);

查询下数据是否插入成功。

SELECT COUNT(*) FROM class;SELECT COUNT(*) FROM student;

(6)删除某表上的索引

创建删除索引存储过程。这是为了方便我们的学习，因为我们在演示某个索引的效果时，可能需要删除其它索引，如果需要一个个手工删除，就太费劲了。

DELIMITER //CREATEPROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))BEGIN DECLARE done INT DEFAULT 0; DECLARE ct INT DEFAULT 0; DECLARE _index VARCHAR(200) DEFAULT ''; DECLARE _cur CURSOR FORSELECTindex_nameFROMinformation_schema.STATISTICSWHERE table_schema=dbname AND table_name=tablename ANDseq_in_index=1 ANDindex_name 'PRIMARY' ;#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束 DECLARECONTINUE HANDLER FOR NOT FOUND set done=2 ; #若没有数据返回,程序继续,并将变量done设为2OPEN _cur;FETCH _cur INTO _index;WHILE _index'' DO SET @str = CONCAT("drop index " , _index , " on " , tablename ); PREPARE sql_str FROM @str ; EXECUTE sql_str; DEALLOCATE PREPARE sql_str; SET _index=''; FETCH _cur INTO _index;END WHILE; CLOSE _cur;END //DELIMITER ;

执行存储过程(可以先保留不执行)

CALL proc_drop_index("dbname","tablename");

3️⃣索引失效的情况

这里我们以InnoDB的B+树的索引结构作为讲解的重点，讲解索引失效的案例(3.1讲解索引最佳的实践)。之所以会出现索引失效的情况，其实是因为我们的优化器经过了成本开销的计算，决定不用索引。用不用索引都是优化器说了算，Sql语句是否会使用索引，跟数据库版本、数据量和数据选择度都有关系。

3.1 全值匹配我最爱(索引最佳)

全值匹配可以充分的利用组合索引。

在没有建立索引时会进行数据查询速度会比较慢。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

SQL_NO_CACHE表示不使用查询缓存。

下图是在没有创建索引的情况下，第一条sql的执行效果。其查询时间是0.048s。

下面建立下索引。

CREATE INDEX idx_age ON student(age);CREATE INDEX idx_age_classid ON student(age,classId);CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

Q 上面三个索引有什么区别，为什么这么建立索引？
上面建立索引是与三条sql的使用场景对应的，遵守了全值匹配的规则，就是说建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

再次执行查询sql，就可以使用到索引idx_age。并且其查询耗时会变短为0.024s。

执行如下sql。选择的索引则是:idx_age_classid。思考下为什么？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;

这是因为我们在构建索引idx_age_classid的B+树时，会先按照age排序，在按照calssId排序，对于这个sql来说，更加高效。

但是上面的索引可能不生效哦，在数据量较大的情况下，我们进行全值匹配SELECT *，优化器可能经过计算发现，我们使用索引查询所有的数据后，还需要对查找到的数据进行回表操作，性能还不如全表扫描。这里我们没有造这么多数据，所以就不演示效果咯。

3.2 不遵守最左前缀匹配原则

运行如下sql。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';

将使用索引idx_age。

下面的sql不会使用索引，因为我没没有创建classId或者name的索引。或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';

Q:为什么不会使用idx_age_classid索引？
索引idx_age_classid的B+树会先使用age排序，在使用classId给age相同的数据排序，这个索引根本用不上哟。这就是下面的最左前缀原则。

在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段，对于多列字段，过滤条件要使用所以那必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法使用。如果查询条件中没有使用这些字段中的第一个字段时，多列索引不会被使用。

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

下面的sql查询就是遵守这一原则的正确打开方式。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';

思考：下面sql会不会使用索引呢？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';

答案是会！因为优化器会执行优化的哦，会调整查询条件的顺序。不过在开发过程中我们还是要保持良好的开发习惯哟。

思考：删去索引idx_age_classid和idx_age,只保留idx_age_classid_name

DROP INDEX idx_age_classid ON student;DROP INDEX idx_age ON student;

执行如下sql，会不会使用索引？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHEREstudent.age = 30 AND student.name='abcd';

答案是会，但是只会用一部分。看看执行结果。

使用了idx_age_classid_name，但是key_len是5，也就是说只使用了age部分的排序，因为age是int类型，4个字节加上null值列表一共5个字节哦。想想就知道，B+树是先按照age排序，再按照classid排序，最后按照name排序，因此不能跳过classId的排序直接就使用name的排序哦。

3.3 不按照递增顺序插入主键

对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间：

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图:

可这个数据页已经满了，再插进来咋办呢” />3.4 计算、函数、类型转换（自动或手动）导致索引失效

思考：这两条 sql 哪种写法更好？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

从执行结果上说，上面sql执行结果没有区别。但是从运行效率上说，第1条sql比之后的要好，因为第一条可以使用上索引！而因为第二条使用了函数，即使建立索引也会导致索引失效。

为何使用函数时优化器会使索引失效呢？您想想，我们只是对student.name字段建立了索引，但并没有对LEFT(student.name，3)建立索引，使用函数后的关键字跟我们建立的B+树可对应不来，怎么能使用B+树优化查询呢？

3.5 类型转换导致索引失效

# 未使用到索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;# 使用到索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

name = 123 发生类型转换，索引失效，原因与使用函数也一样，其实类型转换就是使用了隐式的类型转换函数。

3.6 范围条件右边的列索引失效

我们先调用下前面准备的存储过程删除除主键索引外的其它索引。

CALL proc_drop_index('atguigu_db2','student');SHOW INDEX FROM student;

创建联合索引。

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

执行查询。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

执行结果如下。

注意到key_len是10，说明值使用到了idx_age_classId_name索引中的age与classId部分，而name则没有用上。这是因为classId>20是范围查询，导致其右边的列索引失效。

如果想要完全使用到索引，需要按如下方式创建索引：先写等值查询的列，再写范围查询的列。

create index idx_age_name_classid on student(age,name,classid);

Q：为什么条件查询会导致范围条件后面的列索引失效？
比如说有三个字段 a b c，建立复合索引a_b_c
此时叶子节点的数据排序后可能为
(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)
假设查找 select a,b,c from table where a = 2 and b = 5 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b=5查到两条
(a=2 b=5 c=1) (a=2 b=5 c=2)
最后根据c=2查到目标数据
(a=2 b=5 c=2)
接下来假设使用了范围条件
select a,b,c from table where a = 2 and b >1 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b>1查到四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
此时要查找c=2了但是我们发现这四条数据的c分别是
3,5,1,2 是无序的所以索引失效了
总结：
因为前一个条件相同的情况下，后续列才会是有序的。

Tips：
应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围设计到的字段写在最后）

3.7 不等于（!= 或者）索引失效

为name字段创建索引

CREATE INDEX idx_name ON student(NAME);

查看索引是否失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name  'abc' ;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

执行结果如下。

没有失效！！！这个原因还不是特别明确，可能mysql高版本中优化器又做了升级(毕竟不等于不过是等于的取反，确实可以实现优化)？笔者的mysql版本为8.2.06，如果有知道的大佬可以在评论区留言讨论。不过在实际生产或者面试中，这仍然可以作为一种需要关注的特殊情形。

3.8 is null可以使用索引，is not null无法使用索引

原因和原理一模一样。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

同样的，在低版本中索引会失效，高版本中，索引也不会失效哦。

结论：最好在设计数据库的时候就将字段设置为 NOT NULL 约束。比如可以将 INT 类型的字段，默认设置为 0。将字符串的默认值设置为空字符串（“”）。
扩展：同理，在查询中使用 not like 也无法使用索引，导致全表扫描

3.9 like 以通配符 % 开头索引失效

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会其作用。只有“%”不在第一个位置，索引才会起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';

未使用到索引.

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

想想那颗b+树，前面模糊了那么排序还有什么用？

拓展：Alibaba《Java 开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

3.10 OR前后存在非索引的列

在WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR 前后的两个条件中的列都是索引时，查询中才使用索引。

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

查询语句使用 OR 关键字的情况：

#清除现有的索引CALL proc_drop_index('mymysql', 'student')# 创建索引CREATE INDEX idx_age ON student(age);# 未使用到索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

这是因为or连接的查询条件都需要查询，如果一个使用索引，一个不用索引全表扫描，索引根本起不到优化性能的作用。还不如只进行一次全表扫描呢。

解决方式是给未使用索引的列创建索引。

# 再创建一个索引CREATE INDEX idx_cid ON student(classid);#使用到索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

又翻车了·。。。看来这个情况还是得特别小心啊。

再来。

EXPLAIN SELECT SQL_NO_CACHEage,classid FROM student WHERE age = 10 OR classid = 100;

总结：没事别用select *。

3.11 数据库和表的字符集不匹配

统一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

4.索引一般性建议

假设，index(a,b,c)，下面罗列了一些值得被注意的索引应用场景。

建议
对于单列索引，尽量选择针对当前 query 过滤性更好的索引
在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
总之，书写 SQL 语句时，尽量避免造成索引失效的情况。

工欲善其事，必先利其器”。要想成为工作上的数据库高手，面试时的题霸，独步江湖，就必须拿到一份”武林秘籍”。

我个人强推牛客网：找工作神器|大厂java面经汇总|超全笔试题库

推荐理由：
1.刷题题库，题目特别全面，刷爆笔试再也不担心

链接：找工作神器|大厂java面经汇总|超全笔试题库
2.超全面试题、成体系、高质量，还有AI模拟面试黑科技

链接：工作神器|大厂java面经汇总|超全笔试题库
3.超多面经，大厂面经很多

4.内推机会，大厂招聘特别多

链接：找工作神器|大厂java面经汇总|超全笔试题库
5.大厂真题，直接拿到大厂真实题库，而且和许多大厂都有直接合作，题目通过率高有机会获得大厂内推资格。

链接：找工作神器|大厂java面经汇总|超全笔试题库

Mysql进阶优化篇02——索引失效的10种情况及原理

文章目录

1️⃣ 数据库调优的场景

2️⃣数据准备

(1)建表

(2)设置参数

(3)创建函数

(4)创建存储过程

(5)调用存储过程

(6)删除某表上的索引

3️⃣索引失效的情况

3.1 全值匹配我最爱(索引最佳)

3.2 不遵守最左前缀匹配原则

3.3 不按照递增顺序插入主键

3.5 类型转换导致索引失效

3.6 范围条件右边的列索引失效

3.7 不等于（!= 或者）索引失效

3.8 is null可以使用索引，is not null无法使用索引

3.9 like 以通配符 % 开头索引失效

3.10 OR前后存在非索引的列

3.11 数据库和表的字符集不匹配

4.索引一般性建议

最新关注

热文推荐

基于Java+Spring+vue+element商城销售平台设计和实现

Vue3 源码系列：项目调试和项目架构分析(一)

自定义的搭建solidity开发环境（以太坊）

开源大数据集群部署（九）Ranger审计日志集成（solr）

Qt使用函数指针处理信号和槽函数重载

华为云低代码技术：让矿区管理“智变”，一览无遗

Mysql进阶优化篇02——索引失效的10种情况及原理

文章目录

1️⃣ 数据库调优的场景

2️⃣数据准备

(1)建表

(2)设置参数

(3)创建函数

(4)创建存储过程

(5)调用存储过程

(6)删除某表上的索引

3️⃣索引失效的情况

3.1 全值匹配我最爱(索引最佳)

3.2 不遵守最左前缀匹配原则

3.3 不按照递增顺序插入主键

3.5 类型转换导致索引失效

3.6 范围条件右边的列索引失效

3.7 不等于（!= 或者 ）索引失效

3.8 is null可以使用索引，is not null无法使用索引

3.9 like 以通配符 % 开头索引失效

3.10 OR前后存在非索引的列

3.11 数据库和表的字符集不匹配

4.索引一般性建议

相关文章

最新关注

热文推荐

3.7 不等于（!= 或者）索引失效