美团面试：mysql 索引失效？怎么解决？

尼恩说在前面

在40岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、shein 希音、shopee、百度、网易的面试资格，遇到很多很重要的面试题：

mysql索引失效，主要场景是什么？

mysql索引失效，如何解决？

前几天小伙伴面试美团，遇到了这个问题。但是由于没有回答好，导致面试挂了。

小伙伴面试完了之后，来求助尼恩。

那么，关于索引失效这个问题，该如何才能回答得很漂亮，才能让面试官刮目相看、口水直流。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典》V145版本PDF集群，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

最新《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取，后台回复：领电子书

在这里插入图片描述

1. 准备工作：造50W数据

所以，尼恩决定建表和造50W数据，给大家一步步演示效果，尽量做到有理有据。

首先创建一张测试表 test_user表
为function指定一个参数
创建函数：随机产生字符串
创建函数：用于随机产生多少到多少的编号
创建存储过程：往test_user表中插入50万条数据
查看结果

首先创建一张测试表 test_user表

创建一张user表，表中包含：id、id_card、age、user_name和height、address字段。

CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_card` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `user_name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id_card_age_user_name` (`id_card`,`age`,`user_name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

执行结果如下

在这里插入图片描述

脚本中，创建了三个索引：

聚族索引 id：数据库的聚族索引
联合索引 idx_id_card_age_user_name：由id_card、age和user_name三个字段组成的联合索引。
非聚族索引 idx_height：普通索引

创建函数：随机产生字符串，给身份证、用户名字段使用

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
	DECLARE 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 $$

#假如要删除
#drop function rand_string;

执行创建函数前，可能会报错

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决报错：

  SET GLOBAL log_bin_trust_function_creators = 1;

存储过程函数有可能导致主从的数据不一致，如果未使用主从复制，则设置为信任即可。

创建函数：用于随机整数，年龄字段使用



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$$

在这里插入图片描述

#假如要删除  函数
#drop function rand_num;

使用存储过程，插入50W条测试数据

DELIMITER $$
CREATE PROCEDURE  insert_test_user(  START INT ,  max_num INT )
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit设置成0  
	 SET autocommit = 0;    
	 REPEAT  
		 SET i = i + 1;  
		 INSERT INTO test_user (`id`, `id_card` ,`age`  ,`user_name`  , `height`  ,`address` ) VALUES ((START+i) ,rand_string(18)   , rand_num(30,50),rand_string(10), rand_num(100,180),rand_string(6) );  
		 UNTIL i = max_num  
	 END REPEAT;  
	 COMMIT;  
END$$

#删除
DELIMITER ;
drop PROCEDURE insert_test_user;

# 执行存储过程，往insert_test_user表添加50万条数据
DELIMITER ;
CALL insert_test_user(1,500000);

监控MySQL存储过程执行时间

作为一名经验丰富的开发者，我将教会你如何实现监控MySQL存储过程的执行时间。

步骤一：创建一个监控表
在MySQL数据库中创建一个表，用于记录存储过程的执行时间。表的结构如下：


CREATE TABLE proc_execution_time (
  id INT AUTO_INCREMENT PRIMARY KEY,
  proc_name VARCHAR(255),
  start_time DATETIME,
  end_time DATETIME,
  execution_time INT
);

步骤二：修改存储过程
在需要监控执行时间的存储过程中添加代码，以记录开始时间和结束时间。


DELIMITER $$
CREATE PROCEDURE  insert_test_user(  START INT ,  max_num INT )
BEGIN  
 	DECLARE i INT DEFAULT 0;   
  DECLARE start_time DATETIME;
  DECLARE end_time DATETIME;
  -- 记录开始时间
  SET start_time = NOW();
	-- set autocommit =0 把autocommit设置成0  
	SET autocommit = 0;   
   
	REPEAT  
		 SET i = i + 1;  
		 INSERT INTO test_user (`id`, `id_card` ,`age`  ,`user_name`  , `height`  ,`address` ) VALUES ((START+i) ,rand_string(18)   , rand_num(30,50),rand_string(10), rand_num(100,180),rand_string(6) );  
		 UNTIL i = max_num  
	END REPEAT;  

	 
	   -- 记录结束时间
  SET end_time = NOW();
  
    -- 计算执行时间
  -- 这里我们使用函数TIMESTAMPDIFF来计算两个时间的差值
  SET @execution_time := TIMESTAMPDIFF(SECOND, start_time, end_time);
  
  -- 将执行时间记录到监控表
  INSERT INTO proc_execution_time (`proc_name`, `start_time`, `end_time`, `execution_time`)
  VALUES ('test_01', start_time, end_time, @execution_time);
  
  	COMMIT;  
END$$

为了方便给大家做演示，我特意向user表中插入了50万条数据：



    # 执行存储过程，往insert_test_user表添加50万条数据
    DELIMITER ;
    CALL insert_test_user(1,500000);

等待执行结束

这里等待的时间很长

在这里插入图片描述

时间: 351.058s

在这里插入图片描述

尼恩提示： 50w数据的插入，时间比较长哈。大家稍安勿躁。

查看执行计划

在mysql中，如果你想查看某条sql语句是否使用了索引，或者已建好的索引是否失效，可以通过explain关键字，查看该sql语句的执行计划，来判断索引使用情况。

例如：

explain select * from test_user where id=1;

执行结果：在这里插入图片描述
从图中可以看出，由于id字段是主键，该sql语句用到了主键索引。

关于explain关键字的用法，请参见尼恩Java面试宝典的mysql 专栏，绝世好的面试题目专栏。

2. 破坏最左匹配原则，导致索引失效

尼恩给大家说一个破坏最左匹配原则，从而导致索引失效的一个例子，这个例子是：

使用联合索引查询时，跳过第一列。

之前已经给id_card、age和user_name 3个字段的联合索引：idx_id_card_age_user_name。

该索引字段的顺序是：

id_card
age
user_name

如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效喔，不信我们一起往下看。

什么是联合索引的最左匹配原则？

MySQL 的 最左匹配原则 是指在使用复合索引（即多个列组成的索引）时，查询条件中的字段必须从索引的最左边开始依次匹配，才能有效利用索引来加速查询。

最左匹配原则原则有助于在数据库最大化通过联合索引索引的提升性能。

假设有一个包含三列 A, B, C 的复合索引 (A, B, C)，那么这个索引的匹配规则遵循“最左匹配原则”，即 MySQL 会优先从最左边的列开始依次使用索引。

全匹配（满足最左匹配原则）：

SELECT * FROM table WHERE A = 1 AND B = 2 AND C = 3;

这条查询完全匹配了复合索引中的所有列，MySQL 将有效使用 (A, B, C) 索引来加速查询。

部分匹配：

匹配第一列（满足最左匹配原则）：

SELECT * FROM table WHERE A = 1;

在这条查询中，MySQL 只使用索引的第一列 A，仍然可以有效利用 (A, B, C) 复合索引。

匹配前两列（满足最左匹配原则）：

SELECT * FROM table WHERE A = 1 AND B = 2;

此查询使用了索引的前两列 A 和 B，MySQL 会利用索引进行查询加速。

匹配第1列，中间有跳列（满足最左匹配原则）：

SELECT * FROM table WHERE A = 1 AND C = 3;

此查询使用了索引的两列 A 和 C，跳过 B，MySQL 会利用联合索引进行查询加速。

跳过第一列的匹配：如果跳过了索引中的第一列，MySQL 将无法利用索引的剩余部分。比如：

SELECT * FROM table WHERE B = 2 AND C = 3;

因为查询条件没有涉及到索引的最左列 A，MySQL 无法使用复合索引 (A, B, C) 来加速这个查询。

范围查询的影响：

在使用范围查询（如 <、>、BETWEEN、LIKE 等）时，复合索引只会匹配到范围查询前的部分。

例如：

SELECT * FROM table WHERE A = 1 AND B > 2 AND C = 3;

MySQL 可以利用索引中的 A 和 B 列进行过滤，但对于 C 列，无法继续利用索引进行优化，因为 B 是一个范围查询。

最左匹配原则简单来说就是：复合索引从最左边开始，必须按照顺序依次使用列，不能跳过某一列。

这一原则帮助你设计高效的索引，并使查询能够最大程度地利用索引来提升性能。

2.1 联合索引的场景：哪些情况，索引是有效的呢？

对于联合索引来说，就是要满足最左匹配原则。

假如，查询下面的一条用户数据，并使用 explain 进行sql 的解释。

在这里插入图片描述

第一种情况，查询 id_card

explain select * from test_user where id_card='rDUKToLQUcDJMJTAVe';

在这里插入图片描述

key= idx_id_card_age_user_name

这种情况，用到了索引 idx_id_card_age_user_name

type=ref

表示 MySQL 使用了非唯一索引（例如普通索引或复合索引中的一部分列）进行查询，能够通过匹配某个索引的某些列来查找数据

`type=ref` 的含义

非唯一索引匹配：
- ref 表示 MySQL 使用了索引，但这个索引并不是唯一的。即在查询时，可能会找到多条符合条件的记录，而不仅仅是一条。
- 这种情况常见于查询条件中使用了非唯一索引（例如普通索引或复合索引中的一部分列），因此查询返回的结果可能包含多行数据。
匹配单个值或前缀：
- type=ref 适用于那些匹配某个索引列的单个值或前缀查询。例如，假设有一个索引 (A, B)，查询语句使用了 A 列的条件，那么 MySQL 会使用 A 列上的索引来过滤数据，访问类型为 ref。

在 MySQL 的 EXPLAIN 输出中，type=ref 是一种查询访问类型，表示 MySQL 使用了非唯一索引（或前缀索引）进行查询，能够通过匹配某个索引的某些列来查找数据。这通常比 ALL（全表扫描）和 index（全索引扫描）更高效，但不如 const 或 eq_ref 等类型的性能更好。

第2种情况，查询 id_card 和 age

explain select * from test_user where id_card='rDUKToLQUcDJMJTAVe' and age=43

在这里插入图片描述

key= idx_id_card_age_user_name

这种情况，用到了索引 idx_id_card_age_user_name

type=ref

表示 MySQL 使用了非唯一索引（例如普通索引或复合索引中的一部分列）进行查询，能够通过匹配某个索引的某些列来查找数据

第3种情况，查询 id_card 和 age 和 user_name

explain select * from test_user where id_card='rDUKToLQUcDJMJTAVe' and age=43 and user_name='dryssKwdbY';

在这里插入图片描述

key= idx_id_card_age_user_name

这种情况，用到了索引 idx_id_card_age_user_name

type=ref

表示 MySQL 使用了非唯一索引（例如普通索引或复合索引中的一部分列）进行查询，能够通过匹配某个索引的某些列来查找数据

第4种情况，查询 id_card 和 user_name

比较特殊的场景，跳过了中间的 age 列：

explain select * from test_user where id_card='rDUKToLQUcDJMJTAVe' and user_name='dryssKwdbY';

执行结果：

在这里插入图片描述

查询条件原本的顺序是：id_card、age、user_name，但这里只有id_card和user_name中间断层了，掉了age字段，这种情况也能走id_card字段上的索引。

key= idx_id_card_age_user_name

这种情况，用到了索引 idx_id_card_age_user_name

type=ref

表示 MySQL 使用了非唯一索引（例如普通索引或复合索引中的一部分列）进行查询，能够通过匹配某个索引的某些列来查找数据

Extra = Using index condition

表明 MySQL 可以利用索引中的部分信息来缩小查询结果范围，但无法只通过索引获取查询所需的所有列数据。数据库还是需要读取相关数据行，以获取完整的数据。

"Extra = Using index condition" 的具体介绍

"Using index condition" 表示 MySQL 在查询过程中可以利用索引来进行数据过滤，但仍需要访问数据表的行（而不是完全通过索引获取所有信息）。这是 MySQL 查询优化中的一种情况。

"Using index condition" 表明 MySQL 可以利用索引中的部分信息来缩小查询结果范围，但无法只通过索引获取查询所需的所有列数据。数据库还是需要读取相关数据行，以获取完整的数据。

Using index condition 与 "Using index" 的区别

Using index：表示查询可以完全通过索引来获取数据，不需要访问数据表的行（即 "索引覆盖"）。这种情况性能较好，因为只访问了索引，没有读表。
Using index condition：表示 MySQL 只使用了索引进行部分过滤，但仍然需要读取表中的数据行。虽然通过索引进行了一定的优化，但相比完全使用索引，性能会略逊一筹。

最左匹配原则

上面4种情况，都有id_card字段，并且 id_card是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。

这就是我们所说的最左匹配原则。

2.2 联合索引的场景：哪些情况，索引是失效的呢？

接下来，我们重点看看哪些情况下索引会失效。

跳过第一列的匹配：如果跳过了索引中的第一列，MySQL 将无法利用索引的剩余部分。比如：

SELECT * FROM table WHERE B = 2 AND C = 3;

因为查询条件没有涉及到索引的最左列 A，MySQL 无法使用复合索引 (A, B, C) 来加速这个查询。

第1种情况，跳过第一列，查询age

explain select * from test_user where  age=43 ;

在这里插入图片描述

key= null

这种情况，联合索引 idx_id_card_age_user_name 没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

Extra = Using where是一个表示查询优化器正在有效利用WHERE子句的条件来优化查询执行的标志。

重点：索引扫描和全表扫描的区别

在type这一列，有如下一些可能的选项：

system：系统表，少量数据，往往不需要进行磁盘IO
const：常量连接
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
ref：非主键非唯一索引等值扫描
range：范围扫描
index：索引树扫描
ALL：全表扫描(full table scan)

索引树，就是会遍历聚簇索引树，底层是一颗B+树，叶子节点存储了所有的实际行数据。

全表扫描, 也是扫描的聚簇索引树，因为聚簇索引树的叶子节点中存储的就是实际数据，只要扫描遍历聚簇索引树就可以得到全表的数据了。

那索引扫描和全表扫描究竟有什么区别呢？

索引树扫描可以从树根往下做类似的二分查找，时间复杂度是 o（logn）；全表扫描是扫描所有的叶子节点，时间复杂度是 o（n）；
全表扫描不仅仅需要扫描索引列，还需要扫描每个索引列中指向的实际数据，这里包含了所有的非索引列数据。

具体地，我们通过下面一张图来更直观地看一下索引扫描和全表扫描：

在这里插入图片描述

从上面的图我们可以看到，对于索引扫描来讲，它只需要读取叶子节点的所有key，也就是索引的键，而不需要读取具体的data行数据；

而对于全表扫描来说，它无法仅仅通过读取索引列获得需要的数据，还需要读取具体的data数据才能获取select中指定的非索引列的具体值。

所以，全表扫描的效率相比于索引树扫描，差距是很大的。

第2种情况，跳过第1和第2列，查询user_name


explain select * from test_user where  user_name='dryssKwdbY' ;

在这里插入图片描述

和前面是一样的，尼恩在这里略过了解释哈

第3种情况，跳过第1列，查询age，user_name


explain select * from test_user where age=43 and user_name='dryssKwdbY' ;

在这里插入图片描述

和前面是一样的，尼恩在这里略过了解释哈

说明以上3种情况不满足最左匹配原则，没有包含联合索引的最左边的索引字段，即字段id_card。

跳过第一列的匹配：如果跳过了索引中的第一列，MySQL 将无法利用索引的剩余部分。比如：

SELECT * FROM table WHERE B = 2 AND C = 3;

因为查询条件没有涉及到索引的最左列 A，MySQL 无法使用复合索引 (A, B, C) 来加速这个查询。

3. 破坏索引覆盖原则，导致的索引失效

尼恩揭秘一下，一个破坏联合索引的索引覆盖，导致的索引失效的例子是：使用select *

在查询的过程中，大家很喜欢使用 select ，因为这个很方便，但是 select 很容易破坏索引覆盖导致索引失效，从而变成全表扫描。

什么是索引覆盖？


尼恩用一句话简单来说：

索引覆盖：查询的字段全部在索引的字段中。

索引覆盖（Index Covering）是指一个查询所需的所有数据都可以从索引中直接获取，而不需要访问表的行本身。

在使用索引进行查询时，如果索引包含了查询中所需的所有列，那么就可以实现索引覆盖。

在MySQL中，当一个索引包含了查询所需的所有字段时，数据库引擎可以直接使用这个索引来响应查询请求，而不需要回表（即不需要访问实际的表数据行）去获取数据。

索引覆盖（Index Covering）可以显著减少数据访问量，提高查询性能，因为索引通常比表数据行要小，而且索引是专门为快速查找设计的。索引覆盖的示例：

假设有一个表employees，包含以下列：

employee_id（主键）
name
age
department

并且有一个索引idx_name_age包含name和age列。

如果执行以下查询：

SELECT name, age FROM employees WHERE name = '尼恩';

由于索引idx_name_age已经包含了查询所需的name和age列，MySQL可以直接使用这个索引来获取数据，而不需要回表查询。

在EXPLAIN查询计划的输出中，如果一个查询实现了索引覆盖，你通常会看到Using index的提示。

这意味着MySQL正在使用索引来满足查询，而不需要访问表的行。

索引覆盖是数据库查询优化的一个重要方面，合理地设计索引可以显著提高数据库的性能

索引覆盖的优点包括：

减少I/O操作：由于不需要访问表的数据行，因此减少了磁盘I/O操作，这对于磁盘存储的数据库来说尤其重要。
提高查询速度：索引通常比表数据更加紧凑，并且是经过优化的，所以使用索引覆盖可以加快查询速度。
减少数据访问量：数据库引擎只需要读取索引，而不需要读取整个表，这减少了数据访问量。

使用了select * 破坏索引覆盖，导致索引失效

select * 没有用到索引覆盖的例子：

explain  select * from test_user where user_name='dryssKwdbY' ;

在这里插入图片描述

explain的参数解释：

key= null

这种情况，联合索引 idx_id_card_age_user_name 没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

Extra = Using where是一个表示查询优化器正在有效利用WHERE子句的条件来优化查询执行的标志。

在sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。

而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。

所以：在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select * 。

使用select 索引列，满足索引覆盖，避免索引失效

使用 SELECT 查询时，如果只选择那些被索引覆盖的列，可以避免索引失效并实现索引覆盖。

例如，假设有一个名为 users 的表，它有以下结构：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

并且有一个索引覆盖了 name 和 email 列：

CREATE INDEX idx_name_email ON users(name, email);

如果执行以下查询：

SELECT name, email FROM users WHERE name = 'John Doe';

这个查询是索引覆盖的.

因为 name 和 email 都在 idx_name_email 索引中，数据库引擎可以直接使用这个索引来获取数据，而不需要全表扫描。

为了确保查询满足索引覆盖，你可以：

明确指定需要的列：不要使用 SELECT *，而是只选择那些被索引覆盖的列。
创建合适的索引：确保你的索引包含了查询中需要的所有列。
使用 EXPLAIN 分析查询：在MySQL中，你可以使用 EXPLAIN 关键字来分析查询是否会使用覆盖索引。如果 Extra 列中包含 Using index，那么查询就是索引覆盖的。

在咱们的50w数据查询的例子中，使用 select 索引列的方案为：

explain select id,age,user_name from test_user where user_name='dryssKwdbY' ;

在这里插入图片描述

explain的参数解释：

key= idx_id_card_age_user_name

这种情况，联合索引 idx_id_card_age_user_name 有用到

type= index

索引扫描
Extra = Using where; Using index

Using index：查询使用了覆盖索引，直接通过索引获取了所需的数据。Using where：尽管使用了覆盖索引，但仍有一部分 WHERE 条件需要在索引过滤之后，对结果集进行进一步的过滤。

4. 破坏了前缀匹配原则，导致索引失效

尼恩给大家说一个破坏模糊查询的前缀匹配, 导致索引失效的一个例子： like左边包含%

什么是前缀匹配？

在 MySQL 中，前缀匹配是指使用索引在查询中匹配字符串的开头部分，这种方式可以大大提升查询的性能。其基本原则如下：

索引前缀匹配：

MySQL 可以利用 B-Tree 索引高效查找字符串前缀匹配的记录。例如，给定一个建立在字段 name 上的 B-Tree 索引，以下查询可以利用到索引：
```
SELECT * FROM users WHERE name LIKE 'John%';
```
这里的 LIKE 'John%' 会匹配以 "John" 开头的所有字符串。
索引适用于左前缀：

MySQL 的索引结构适用于从左到右的顺序查找。

因此，只能利用字符串从左侧开始的前缀进行匹配，比如 LIKE 'prefix%'，而不能利用 %Suffix 或 %Suffix% 这样的查询。这是因为后者要求扫描整个表来找到匹配项，不符合索引的顺序结构。
复合索引的前缀匹配：

当查询中涉及多个列时，如果使用复合索引，MySQL 会根据列的顺序从左至右依次匹配前缀。

只有符合索引定义顺序的最左部分才能被利用。例如，如果有索引 (col1, col2)，只有以下查询可以利用该索引：
```
SELECT * FROM table WHERE col1 = 'value1' AND col2 LIKE 'value2%';
```
如果 col1 没有出现在查询条件中，索引将无法使用。
限制前缀长度的索引：

MySQL 允许在某些数据类型（如 VARCHAR、TEXT）上建立前缀长度索引，例如对前 10 个字符建立索引。前缀索引可以减少索引大小，但只适合前缀查询。

理解这些前缀匹配的原则可以帮助在设计索引时进行优化，确保查询可以充分利用索引来提升性能。

例子1：使用前缀匹配进行模糊查询

在日常的工作中，模糊查询使用频率还是比较高的。

比如现在有个需求：想查询姓王的同学有哪些?

首先给我们的表，加上一个 user_name的索引：

CREATE INDEX idx_user_name ON test_user (user_name);

然后开始查询姓 a 的用户：

select * from test_user where user_name like 'a%';

在这里插入图片描述

explain 一下，看看有没有命中索引

explain select * from test_user where user_name like 'a%';

在这里插入图片描述

explain的参数解释：

key= idx_user_name

这种情况，使用了二级索引 idx_user_name

type= index

索引扫描
Extra = Using index condition

当 EXPLAIN 的输出中看到 Extra 列包含 Using index condition 时，这意味着：

MySQL 正在使用索引条件推送优化查询。
部分查询条件在索引层面被评估，而不是在索引查找之后回表进行评估。
这通常意味着查询优化器认为在索引层面进行过滤比回表过滤更高效。

Using index condition 是 MySQL 优化查询性能的一种方式，它通过减少不必要的回表操作来提高查询效率。

例子2：破坏了模糊查询的前缀匹配

但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。

例子3：后缀匹配的例子

explain  select * from test_user where user_name like '%a';

在这里插入图片描述

explain的参数解释：

key= null

这种情况，二级索引 idx_user_name 没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

Extra = Using where是一个表示查询优化器正在有效利用WHERE子句的条件来优化查询执行的标志。

例子4：中间匹配的例子

explain  select * from test_user where user_name like '%a%';

在这里插入图片描述

explain的参数解释：

key= null

这种情况，二级索引 idx_user_name 没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

Extra = Using where是一个表示查询优化器正在有效利用WHERE子句的条件来优化查询执行的标志。

下面用一句话总结一下规律：

当like语句中的%，出现在查询条件的左边时，索引会失效。

那么，为什么会出现这种现象呢？

答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。

我们在查目录时，通常会先从左边第一个字母进行匹对，

如果相同，再匹对左边第二个字母，
如果再相同匹对其他的字母，以此类推。

通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。

但如果硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，那么目录的有序性就失效了。

5. order by 排序不当，导致的索引失效

在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。

在 MySQL 中，ORDER BY 子句通常用于根据一个或多个列对结果集进行排序。

然而，如果不正确地使用 ORDER BY，它可能会导致索引失效，从而影响查询性能。

以下是一些可能导致 ORDER BY 导致索引失效的情况和相应的解决方案：

索引列和 ORDER BY 列不匹配：

如果 ORDER BY 子句中引用的列没有被索引覆盖，MySQL 可能无法使用索引来排序，而必须进行额外的排序操作（Using filesort）。

为了解决这个问题，可以创建一个包含 ORDER BY 列的索引。
使用 SELECT*：

使用 SELECT * 可能会导致索引失效，因为查询返回了所有列，而不仅仅是索引列。

这可能会迫使数据库进行额外的行查找以获取非索引列的数据。
ORDER BY 与 WHERE 子句不匹配：

如果 WHERE 子句中的条件列和 ORDER BY 子句中的排序列不一致，且没有相应的索引，那么索引可能不会被使用。
ORDER BY 子句中的列没有索引的最左列：如果 ORDER BY 子句中的列没有索引的最左列或不遵循最左匹配原则，索引可能不会被使用。
ORDER BY 子句中的列使用了函数或表达式：对列进行函数操作或计算可能会阻止 MySQL 使用索引。
ORDER BY 子句中的列有 DESC 和 ASC 混合使用：如果 ORDER BY 子句中既有升序又有降序的排序，可能会引起 Using filesort。

场景1：索引列和 ORDER BY 列不匹配

比如，对不同的索引做order by , 下面是一个例子

explain select * from test_user  order by id_card, height;

执行结果：

在这里插入图片描述
从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，二级索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using filesort

Using filesort 文件排序，通常意味着 MySQL 会将结果集加载到内存中，然后进行排序，这可能会影响查询性能，尤其是在处理大量数据时。

Using filesort 文件排序，其实文件排序的话，会有很多种情况，

比如说：根据要排序的内容大小，就有内部排序和外部排序；

如果，排序的内容比较小，那么，在内存中就可以搞定，这就是内部排序（使用快排）；
如果，要排序的内容太大，那么，就得需要通过磁盘的帮助了，这个就是外部排序（使用归并）。

还有，就是根据一行的大小来进行区分，

如果，一行的内容不是很大，那么，就整个字段读取出来进行排序，称为全字段排序；
如果，整个字段内容很大，那么，就采用rowid排序，读取rowid和该字段先进行排序，然后，再回表查找其他的内容；

场景2：使用 SELECT *

使用 SELECT*：

使用 SELECT * 可能会导致索引失效，因为查询返回了所有列，而不仅仅是索引列。

这可能会迫使数据库进行额外的行查找以获取非索引列的数据。

具体的原因，尼恩在前面已经介绍，这里不做展开

场景3：ORDER BY 与 WHERE 子句不匹配：

如果 WHERE 子句中的条件列和 ORDER BY 子句中的排序列不一致，且没有相应的索引，那么索引可能不会被使用。

比如下面的例子

explain select height from test_user where  id_card  like '%a%'  order by height;

where 一个索引， order by 一个索引

id_card是联合索引的第一个字段，在where中使用了，而height 一个独立的所以，在order by中使用。

执行结果：

在这里插入图片描述

解决办法：创建合适的索引，确保where 字句和 ORDER BY 子句中的列被索引覆盖。

下面是一个索引覆盖了 where 字句和 ORDER BY 子句的例子

explain select age from test_user where  id_card  like '%a%'  order by age;

在这里插入图片描述

总结：order by 排序导致的索引失效的解决方案

order by 排序导致的索引失效的解决方案可能包括：

创建合适的索引，确保 ORDER BY 子句中的列被索引覆盖。
避免使用 SELECT *，只选择必要的列。
使用 FORCE INDEX 或 USE INDEX 来强制查询使用特定的索引。
重新设计查询，以确保 ORDER BY 子句中的列可以有效地使用索引。

在某些情况下，MySQL 优化器可能会决定全表扫描比使用索引更快，尤其是在数据量不大或者索引统计信息不准确时。

在这种情况下，可以通过分析查询计划（EXPLAIN）来确定是否确实需要优化索引策略。

如果需要，可以通过调整索引或查询逻辑来改善性能。

6. or关键字使用不当，导致索引失效

尼恩提示：平时使用or关键字的场景非常多，但如果你稍不注意，就可能让已有的索引失效。

在 SQL 查询中，OR 关键字用于组合多个条件，使得只要满足其中之一的条件就会被选中。

然而，如果 OR 使用不当，可能会导致索引失效，从而影响查询性能。

一些关于 OR 使用不当导致索引失效的情况和解决方法：

导致索引失效的情况，大致如下：

多个列的 OR 条件：

如果 OR 条件跨越了多个列，而没有建立复合索引，那么索引可能不会被使用。

例如，如果有两个列 A 和 B，而查询条件是 A = x OR B = y，且只有单独在 A 或 B 上建立的索引，那么索引可能不会被使用。
范围查询与等值查询的 OR 组合：

对于像 A > x OR B = y 这样的条件，如果 A 列的索引是针对范围查询优化的，而 B 列的索引是针对等值查询优化的，MySQL 可能无法同时使用这两个索引。

场景1：OR跨越了多个列，而没有建立复合索引

explain select age from test_user where  id_card  like '%a%'  OR height=180;

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，二级索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

场景2：范围查询与等值查询的 OR 组合


explain select id_card from test_user where  id_card = 'hSQBimMXEbwbSlCdKr'  OR height>160;

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，二级索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

总结：or关键字使用不当，导致索引失效解决方法：

使用复合索引：

如果可能，创建一个包含所有 OR 条件列的复合索引。

这样，MySQL 可以更有效地使用索引来处理查询。
拆分查询：

将包含 OR 条件的查询拆分成多个独立的查询，然后使用 UNION 或 UNION ALL 来合并结果。

这样每个查询都可以独立地使用其相关的索引。
```
SELECT * FROM table WHERE A = x
UNION
SELECT * FROM table WHERE B = y;
```
使用 FORCE INDEX：

在某些情况下，可以使用 FORCE INDEX 来强制查询使用特定的索引，尽管这可能会限制查询优化器的灵活性。
调整查询逻辑：

重新设计查询逻辑，尽量减少使用 OR，或者将 OR 条件转换为等价的 IN 子句或其他可以更有效使用索引的形式。

7. 索引列上有计算或者函数，导致的索引失效

尼恩提示：在 SQL 查询中，如果在索引列上进行计算或者使用函数，通常会导致索引失效。

为啥：这是因为数据库的查询优化器无法对计算后的结果进行有效的索引查找。

例如，如果你有一个索引列 age，并且在查询中使用 age * 1.5 或 DATE(birthday)，索引将不会被使用。

场景1：索引列上有计算，导致的索引失效

 explain select *  from test_user where  id +1 =10000 ;

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

场景2：索引列上有函数，导致的索引失效

 explain select *  from test_user where  ceil(id) =10000 ;

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

索引列上有计算或者函数，导致的索引失效如何解决

为了避免这种情况，你可以采取以下措施：

避免在索引列上使用计算和函数：重写查询，将计算和函数移出索引列。例如，如果可能，可以在应用层进行计算，或者使用已经计算好的列（如果适用）。
使用合适的索引：如果某些函数操作是不可避免的，考虑创建一个计算列并为其建立索引。例如，如果经常需要根据 DATE(birthday) 来查询，可以创建一个存储日期的计算列并为其建立索引。

通过这些方法，可以最大限度地减少索引失效的情况，从而提高查询性能。

8.使用 not in和not exists不当，导致索引失效

日常工作中用得也比较多的，还有范围查询，常见的有：

in
exists
not in
not exists
between and

其中，NOT IN 和 NOT EXISTS 子句通常用于排除某些记录，如果不正确使用这些子句，它们可能会导致索引失效，从而影响查询性能。

以下是一些关于 NOT IN 和 NOT EXISTS 导致索引失效的情况和解决方法：

8.1 使用 `NOT IN` 不当，导致索引失效

下面来一个普通索引的 not in的例子


-- 没有用到了 索引
explain select * from test_user  where height  not   in (173,174,175,176);

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

换成普通索引 in 试试，下面的语句：

-- 用到了 索引
explain select * from test_user  where height    in (173,174,175,176);

在这里插入图片描述

从图中看出索引生效了。

explain的参数解释：

key= idx_height

这种情况，索引没有用到

type= range

type= range 表示 MySQL 正在使用索引来检索位于某个范围内的行

Extra = Using index condition

`type = range` 的含义：

范围查询：range 表示 MySQL 正在使用索引来检索位于某个范围内的行。这通常发生在使用 BETWEEN...AND...、>、<、>=、<= 或 LIKE（当模式以通配符 % 结尾时除外）等操作符时。
索引扫描：range 表明 MySQL 正在执行索引扫描，这是介于全表扫描（type = ALL）和索引查找（如 type = ref 或 type = eq_ref）之间的一种访问方法。它比全表扫描更高效，但可能不如直接索引查找快。

换成PRIMARY 索引 in 试试，下面的语句：

explain select * from test_user  where id    not in (173,174,175,176);

在这里插入图片描述

从图中看出索引生效了。

explain的参数解释：

key= PRIMARY

这种情况，用到 PRIMARY 索引

type= range

type= range 表示 MySQL 正在使用索引来检索位于某个范围内的行

Extra = Using where

使用 NOT EXISTS 导致索引失效的原因

使用 NOT IN 可能导致 MySQL 索引失效的主要原因在于其处理方式与 MySQL 的优化器机制存在冲突，尤其是在处理大数据集和 NULL 值时。具体原因如下：

1. NOT IN 与 NULL 值的影响

NOT IN 查询的逻辑是从某个集合中排除匹配的值。

如果集合中包含 NULL 值，MySQL 的行为会变得不确定。

这是因为在 SQL 逻辑中，NULL 表示未知值，无法确定一个值是否“不在”包含 NULL 的集合中。

因此，NOT IN 查询在遇到 NULL 时会停止利用索引。

例如，以下查询中如果 subquery 的结果集包含 NULL，索引将无法有效利用：

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);

当 table2.id 包含 NULL 时，MySQL 无法正确处理，并且可能会选择全表扫描来确保结果正确性。

2. 优化器对索引的选择

MySQL 的查询优化器会在执行查询时, 会根据数据分布和索引结构做出选择。

对于 NOT IN 操作，优化器有时会认为全表扫描比使用索引更高效，尤其当需要过滤大量数据时。

在这种情况下，即便存在适合的索引，MySQL 仍可能选择不使用索引。

例如：


SELECT * FROM users WHERE id NOT IN (1, 2, 3);

当 id 列有索引，但如果 NOT IN 的结果集包含很多数据，优化器可能认为使用索引不是最佳选择，从而直接进行全表扫描。

这时候，尼恩认为，可以强制走索引：force index(idx_xxx) ，例如：

explain select * from test_user force index(idx_height)  where height  not   in (173,174,175,176)

在这里插入图片描述

强制走索引，从图中看出索引生效了。

explain的参数解释：

key= idx_height

这种情况，索引没有用到

type= range

type= range 表示 MySQL 正在使用索引来检索位于某个范围内的行

Extra = Using index condition

3. 反向逻辑的处理成本高

NOT IN 本质上是排除某些匹配项的操作，而排除操作相较于直接匹配操作，计算成本通常更高。

MySQL 需要检查每一行数据是否在某个集合中不存在，这种反向操作很难利用 B-Tree 等类型的索引结构，因为索引通常用于正向查找，而非排除式的匹配。

8.2 使用 `NOT EXISTS` 不当，导致索引失效

NOT EXISTS 子句通常用于检查一个子查询是否不返回任何记录。

如果NOT EXISTS 子查询没有使用索引，或者子查询的执行成本较高，可能会影响外部查询的索引使用。

一个来自于网络的示例：

SELECT * FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

一个来咱们的模拟示例：

explain select * from test_user  t1 where  not exists (select 1 from test_user t2 where t2.height=178 and t1.id_card=t2.id_card)

在这里插入图片描述

从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。

解决方法：

考虑重写查询，使用 LEFT JOIN 来替代 NOT EXISTS。

 explain select * from test_user t1  LEFT JOIN  test_user t2  on t1.id_card=t2.id_card where t2.height=178   and t2.id_card IS NULL;

在这里插入图片描述

使用 NOT EXISTS 导致索引失效的原因

在 MySQL 中，使用 NOT EXISTS 查询时可能导致索引失效，

主要原因是 MySQL 处理 NOT EXISTS 的方式和索引匹配机制存在一定的差异。

具体原因包括以下几点：

反向逻辑操作的处理

NOT EXISTS 本质上是在查询某个子查询中不存在某些记录。

MySQL 对于这种反向逻辑的处理需要扫描整个表，确认没有符合条件的记录。

这种全表扫描会导致索引的优势丧失，因为索引是为了优化数据的快速定位，而非逐一检查所有数据行。

SELECT * FROM table1 WHERE NOT EXISTS (
    SELECT * FROM table2 WHERE table1.id = table2.id
);

在这个查询中，MySQL 需要遍历 table2，逐行判断是否存在与 table1.id 匹配的记录。

如果 table2 的某列存在索引，但由于 NOT EXISTS 的反向操作，MySQL 通常无法高效利用索引来做排除式判断。

NULL 值的影响

NOT EXISTS 在处理与 NULL 相关的记录时可能遇到问题。

如果查询中包含 NULL 值，MySQL 可能无法使用索引，原因是索引通常不包含 NULL 值。

在这种情况下，即便定义了索引，MySQL 也可能选择全表扫描而非使用索引。

优化器选择

MySQL 的查询优化器在执行查询时，会根据数据的分布、索引的选择性以及表的大小来决定是否使用索引。

在某些情况下，即使查询理论上可以使用索引，优化器可能会因为某些特定原因选择全表扫描。

例如，当优化器认为表中的大部分数据都符合 NOT EXISTS 的条件时，它可能会放弃使用索引。

9. 其他场景，如：列的比对，导致索引失效

假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。

列的比对例子：

对比user表中id字段（primary索引）和height字段（idx_height索引），查询出这两个字段中值相同的记录。

在这里插入图片描述

这个需求很简单，sql可以这样写：

explain select * from test_user   where id=height

执行结果：

在这里插入图片描述

从图中看出索引也失效了。

explain的参数解释：

key= null

这种情况，索引没有用到

type= all

all 没用索引，全表扫描（通常没有建索引的列）

Extra = Using where

为什么会出现这种结果？

id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。

结论是：

如果把两个单独建了索引的列，用来做列对比时索引会失效。

说在最后：有问题找老架构取经‍

关于索引失效，尼恩给大家梳理的满分答案，已经彻底出来了

在这里插入图片描述

通过这个问题的深度回答，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。

很多小伙伴刷完后，吊打面试官，大厂横着走。

在刷题过程中，如果有啥问题，大家可以来找 40岁老架构师尼恩交流。

另外，如果没有面试机会，可以找尼恩来改简历、做帮扶。

遇到职业难题，找老架构取经，可以省去太多的折腾，省去太多的弯路。

尼恩技术圣经系列PDF

……完整版尼恩技术圣经PDF集群，请找尼恩领取

《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》PDF，请到下面公号【技术自由圈】取↓↓↓

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

美团面试：mysql 索引失效？怎么解决？ （重点知识，建议收藏，读10遍+）

美团面试：mysql 索引失效？怎么解决？

尼恩说在前面

1. 准备工作：造50W数据

首先创建一张测试表 test_user表

创建函数：随机产生字符串， 给身份证、用户名字段使用

创建函数：用于随机整数，年龄字段使用

使用存储过程，插入50W条测试数据

监控MySQL存储过程执行时间

等待执行结束

查看执行计划

2. 破坏 最左匹配 原则，导致 索引失效

什么是联合索引的 最左匹配原则？

2.1 联合索引的场景：哪些情况，索引是有效的呢？

第一种情况，查询 id_card

type=ref 的含义

第2种情况，查询 id_card 和 age

第3种情况，查询 id_card 和 age 和 user_name

第4种情况，查询 id_card 和 user_name

"Extra = Using index condition" 的具体介绍

Using index condition 与 "Using index" 的区别

最左匹配原则

2.2 联合索引的场景：哪些情况，索引是失效的呢？

第1种情况，跳过第一列，查询age

重点：索引扫描和全表扫描的区别

第2种情况，跳过第1和第2列，查询user_name

第3种情况，跳过第1列，查询age，user_name

3. 破坏 索引覆盖 原则，导致的 索引失效

什么是索引覆盖？

使用了select * 破坏索引覆盖， 导致索引失效

使用select 索引列 ，满足 索引覆盖，避免索引失效

4. 破坏了 前缀匹配原则，导致 索引失效

什么是 前缀匹配？

例子1：使用前缀匹配 进行 模糊查询

例子2：破坏了模糊查询的 前缀匹配

例子3：后缀匹配的例子

例子4：中间匹配的例子

5. order by 排序不当，导致的索引失效

场景1：索引列 和 ORDER BY 列不匹配

场景2：使用 SELECT *

场景3：ORDER BY 与 WHERE 子句不匹配：

总结：order by 排序导致的索引失效的解决方案

6. or关键字使用不当，导致索引失效

场景1：OR跨越了多个列，而没有建立复合索引

场景2：范围查询与等值查询的 OR 组合

总结：or关键字使用不当，导致索引失效解决方法：

7. 索引列上有计算或者函数，导致的索引失效

场景1：索引列上有计算，导致的索引失效

场景2：索引列上有函数，导致的索引失效

索引列上有计算或者函数，导致的索引失效如何解决

8.使用 not in和not exists不当，导致索引失效

8.1 使用 NOT IN 不当，导致索引失效

type = range 的含义：

使用 NOT EXISTS 导致索引失效 的原因

8.2 使用 NOT EXISTS 不当，导致索引失效

使用 NOT EXISTS 导致索引失效 的原因

9. 其他场景，如：列的比对，导致索引失效

说在最后：有问题找老架构取经‍

尼恩技术圣经系列PDF

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

美团面试：mysql 索引失效？怎么解决？（重点知识，建议收藏，读10遍+）

创建函数：随机产生字符串，给身份证、用户名字段使用

2. 破坏最左匹配原则，导致索引失效

什么是联合索引的最左匹配原则？

`type=ref` 的含义

3. 破坏索引覆盖原则，导致的索引失效

使用了select * 破坏索引覆盖，导致索引失效

使用select 索引列，满足索引覆盖，避免索引失效

4. 破坏了前缀匹配原则，导致索引失效

什么是前缀匹配？

例子1：使用前缀匹配进行模糊查询

例子2：破坏了模糊查询的前缀匹配

场景1：索引列和 ORDER BY 列不匹配

8.1 使用 `NOT IN` 不当，导致索引失效

`type = range` 的含义：

使用 NOT EXISTS 导致索引失效的原因

8.2 使用 `NOT EXISTS` 不当，导致索引失效

使用 NOT EXISTS 导致索引失效的原因