- 首页
- 上一级
- A,B,C的联合索引，按照AB,AC,BC查询，能走索引吗？.md
- InnoDB中的索引类型？.md
- InnoDB中的表级锁、页级锁、行级锁？.md
- InnoDB为什么使用B+树实现索引？.md
- InnoDB和MyISAM有什么区别？.md
- InnoDB如何解决脏读、不可重复读和幻读的？.md
- InnoDB支持哪几种行格式？.md
- InnoDB的一次更新事务是怎么实现的？.md
- Innodb加索引，这个时候会锁表吗？.md
- Innodb的RR到底有没有解决幻读？.md
- MyISAM的索引结构是怎么样的，它存在的问题是什么？.md
- MySQL5.x和8.0有什么区别？.md
- MySQL中like的模糊查询如何优化.md
- MySQL中如何查看一个SQL的执行耗时.md
- MySQL中的事务隔离级别？.md
- MySQL为什么会有存储碎片？有什么危害？.md
- MySQL为什么是小表驱动大表，为什么能提高查询性能？.md
- MySQL主从复制的过程.md
- MySQL只操作同一条记录，也会发生死锁吗？.md
- MySQL怎么做热点数据高效更新？.md
- MySQL执行大事务会存在什么问题？.md
- MySQL是AP的还是CP的系统？.md
- MySQL是如何保证唯一性索引的唯一性的？.md
- MySQL用了函数一定会索引失效吗？.md
- MySQL的BLOB和TEXT有什么区别_.md
- MySQL的HashJoin是什么？.md
- MySQL的binlog有几种格式.md
- MySQL的limit+orderby为什么会数据重复？.md
- MySQL的select_会用到事务吗？.md
- MySQL的主键一定是自增的吗？.md
- MySQL的优化器的索引成本是怎么算出来的？.md
- MySQL的并行复制原理.md
- MySQL的数据存储一定是基于硬盘的吗？.md
- MySQL的深度分页如何优化.md
- MySQL的行级锁锁的到底是什么？.md
- MySQL的驱动表是什么？MySQL怎么选的？.md
- MySQL索引一定遵循最左前缀匹配吗？.md
- MySQL自增主键用完了会怎么样？.md
- MySQL获取主键id的瓶颈在哪里？如何优化？.md
- SQL中PK、UK、CK、FK、DF是什么意思？.md
- SQL执行计划分析的时候，要关注哪些信息？.md
- SQL语句如何实现insertOrUpdate的功能？.md
- Usingfilesort能优化吗，怎么优化？.md
- a,b两个单独索引，wherea=xxandb=xx走哪个索引？为什么？.md
- binlog、redolog和undolog区别？.md
- bufferpool的读写过程是怎么样的？.md
- char和varchar的区别？.md
- count(1)、count(_)与count(列名)的区别.md
- limit0,100和limit10000000,100一样吗？.md
- on和where有什么区别？.md
- orderby是怎么实现的？.md
- truncate、delete、drop的区别？.md
- undolog会一直存在吗？什么时候删除？.md
- uuid和自增id做主键哪个好，为什么？.md
- where条件的顺序影响使用索引吗？.md
- 一个查询语句的执行顺序是怎么样的？.md
- 为什么MySQL8.0要取消查询缓存？.md
- 为什么MySQL会选错索引，如何解决？.md
- 为什么MySQL默认使用RR隔离级别？.md
- 为什么不建议使用存储过程？.md
- 为什么不推荐使用外键？.md
- 为什么大厂不建议使用多表join？.md
- 为什么默认RR，大厂要改成RC？.md
- 乐观锁与悲观锁如何实现？.md
- 二级索引在索引覆盖时如何使用MVCC？.md
- 什么情况会导致自增主键不连续？.md
- 什么时候索引失效反而提升效率？.md
- 什么是InnoDB的页分裂和页合并.md
- 什么是MySQL的字典锁？.md
- 什么是OnlineDDL.md
- 什么是ReadView，什么样的ReadView可见？.md
- 什么是bufferpool？.md
- 什么是事务的2阶段提交？.md
- 什么是回表，怎么减少回表的次数？.md
- 什么是意向锁？.md
- 什么是排他锁和共享锁？.md
- 什么是数据库事务？.md
- 什么是数据库的主从延迟，如何解决？.md
- 什么是数据库的锁升级，Innodb支持吗？.md
- 什么是数据库范式，为什么要反范式？.md
- 什么是最左前缀匹配？为什么要遵守？.md
- 什么是索引合并，原理是什么？.md
- 什么是索引覆盖、索引下推？.md
- 什么是索引跳跃扫描.md
- 什么是聚簇索引和非聚簇索引？.md
- 什么是脏读、幻读、不可重复读？.md
- 介绍一下InnoDB的数据页，和B+树的关系是什么？.md
- 介绍下InnoDB的锁机制？.md
- 介绍下MySQL5.7中的组提交.md
- 从innodb的索引结构分析，为什么索引的key长度不能太长_.md
- 区分度不高的字段建索引一定没用吗？.md
- 唯一索引和主键索引的区别？.md
- 如何优化一个大规模的数据库系统？.md
- 如何理解MVCC？.md
- 如何进行SQL调优？.md
- 当前读和快照读有什么区别？.md
- 怎么比较两个索引的好坏？.md
- 慢SQL的问题如何排查？.md
- 执行计划中，key有值，还是很慢怎么办？.md
- 数据库乐观锁的过程中，完全没有加任何锁吗？.md
- 数据库加密后怎么做模糊查询？.md
- 数据库怎么做加密和解密？.md
- 数据库扫表任务如何避免出现死循环.md
- 数据库死锁如何解决？.md
- 是否支持emoji表情存储，如果不支持，如何操作？.md
- 有了关系型数据库，为什么还需要NOSQL？.md
- 用了索引还是很慢，可能是什么原因？.md
- 索引失效的问题如何排查？.md
- 联合索引是越多越好吗？.md
- 设计索引的时候有哪些原则？.md
- 说一说MySQL一条SQL语句的执行过程？.md
- 阿里的数据库能抗秒杀的原理.md
- 高并发情况下自增主键会不会重复，为什么？.md
✅索引失效的问题如何排查？
典型回答
MySQL的索引失效是一个比较常见的问题，这种情况一般会在慢SQL发生时需要考虑，考虑是否存在索引失效的问题。
在排查索引失效的时候，第一步一定是找到要分析的SQL语句，然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。
我们需要通过key+type+extra来判断一条SQL语句是否用到了索引。如果有用到索引，那么是走了覆盖索引呢？还是索引下推呢？还是扫描了整颗索引树呢？或者是用到了索引跳跃扫描等等。
一般来说，比较理想的走索引的话，应该是以下几种情况：
首先，key一定要有值，不能是NULL
其次，type应该是ref、eq_ref、range、const等这几个
还有，extra的话，如果是NULL，或者using index，using index condition都是可以的
如果通过执行计划之后，发现一条SQL没有走索引，比如 type = ALL， key = NULL , extra = Using where
那么就要进一步分析没有走索引的原因了。我们需要知道的是，到底要不要走索引，走哪个索引，是MySQL的优化器决定的，他会根据预估的成本来做一个决定。
那么，有以下这么几种情况可能会导致没走索引：
1、没有正确创建索引：当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。
2、索引区分度不高：如果索引的区分度不够高，那么可能会不走索引，因为这种情况下走索引的效率并不高。
3、表太小：当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引
4、查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效
这时候我们就需要从头开始逐一分析：
1、如果没有正确创建索引，那么就根据SQL语句，创建合适的索引。如果没有遵守最左前缀那么就调整一下索引或者修改SQL语句
2、索引区分度不高的话，那么就考虑换一个索引字段。
3、表太小这种情况确实也没啥优化的必要了，用不用索引可能影响不大的
4、排查具体的失效原因，然后针对性的调整SQL语句就行了。
扩展知识
可能导致索引失效的情况
假设我们有一张表（以下SQL实验基于Mysql 5.7）：
CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `age` (`age`),
KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into mytable(id,name,age,create_time) values (1,"hollis",20,now());
insert into mytable(id,name,age,create_time) values (2,"hollis1",21,now());
insert into mytable(id,name,age,create_time) values (3,"hollis2",22,now());
insert into mytable(id,name,age,create_time) values (4,"hollis3",20,now());
insert into mytable(id,name,age,create_time) values (5,"hollis4",14,now());
insert into mytable(id,name,age,create_time) values (6,"hollis5",43,now());
insert into mytable(id,name,age,create_time) values (7,"hollis6",32,now());
insert into mytable(id,name,age,create_time) values (8,"hollis7",12,now());
insert into mytable(id,name,age,create_time) values (9,"hollis8",1,now());
insert into mytable(id,name,age,create_time) values (10,"hollis9",43,now());
索引列参与计算
select * from mytable where age = 12;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
以上SQL是可以走索引的，但是如果我们在字段中增加计算的话，就会索引失效：
select * from mytable where age +1 = 12;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
但是如果是如下形式的计算还是可以走索引的：
select * from mytable where age = 12 - 1;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
对索引列进行函数操作
SELECT * FROM mytable WHERE create_time = '2023-04-01 00:00:00';
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | create_time | create_time | 6 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
以上SQL是可以走索引的，但是如果我们在字段中增加函数操作的话，就会索引失效：
SELECT * FROM mytable WHERE YEAR(create_time) = 2022;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用OR
SELECT * FROM mytable WHERE name = 'Hollis' and age > 18;
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | const | name,age | name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
但是如果使用OR的话，并且OR的两边存在<或者>的时候，就会索引失效，如：
SELECT * FROM mytable WHERE name = 'Hollis' OR age > 18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name,age | NULL | NULL | NULL | 10 | 40.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
但是如果OR两边都是=判断，并且两个字段都有索引，那么也是可以走索引的，如：
mysql> explain SELECT * FROM mytable WHERE name = 'Hollis' OR age = 18;
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | mytable | NULL | index_merge | name,age | name,age | 202,5 | NULL | 2 | 100.00 | Using union(name,age); Using where |
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
like操作
SELECT * FROM mytable WHERE name like '%Hollis%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE name like '%Hollis';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE name like 'Hollis%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range| name | name | NULL | NULL | 10 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE name like 'Holl%is';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range| name | name | NULL | NULL | 10 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
以上四种like，"Hollis%"和"Holl%is"这两种可以走索引，但是如果是"%Hollis%"和"%Hollis"就没办法走索引了。
隐式类型转换
select * from mytable where name = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
以上情况，name是一个varchar类型，但是我们用int类型查询，这种是会导致索引失效的。
这种情况有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，这种情况也能走索引：
select * from mytable where age = '1';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
不等于比较
SELECT * FROM mytable WHERE age != 18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | age | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
但是也不全是，比如以下的用ID进行!=比较的时候，是可能走索引的：
SELECT * FROM mytable WHERE id != 18;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
!=这种情况，索引失效与索引的选择性、数据分布情况等因素有关，不能简单地说是由于查询条件中包含 != 这个操作符而导致索引失效。
is not null
以下情况是索引失效的：
SELECT * FROM mytable WHERE name is not null
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 90.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
order by
SELECT * FROM mytable order by age
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
当进行order by的时候，如果数据量很小，数据库可能会直接在内存中进行排序，而不使用索引。
in
使用in的时候，有可能走索引，也有可能不走，一般在in中的值比较少的时候可能会走索引优化，但是如果选项比较多的时候，可能会不走索引：
mysql> explain select * from mytable where name in ("Hollis");
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | const | name | name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
mysql> explain select * from mytable where name in ("Hollis","hsh");
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | mytable | NULL | range | name | name | 202 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> explain select * from mytable where name in ("Hollis","hsh","s");
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 11 | 27.27 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+