面试常问的MySQL索引失效的场景

原文链接

https://juejin.im/post/5d8dc96a6fb9a04e3b1107cd

https://www.cnblogs.com/amunote/p/10352307.html

面试问到了mysql索引失效的情况，所以就查了资料，尽量齐全，这里给出来，希望大家以后遇到这种问题，回答让面试官满意。

MySQL索引失效的场景

1. WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引

2. 类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引

3. 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用

4. 如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；如果条件是LIKE '%abc'，MYSQL将不使用索引。

5. 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。

6. 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

7. 对于多列索引，不是使用的第一部分，则不会使用索引

8. 如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

9. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

10. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

一、准备工作

• 创建一张表 t_index ，脚本如下：

CREATE TABLE `t_index` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表记录标识号，数据库主键，不用于实际业务',
  `key1` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段1',
  `key2` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段2',
  `key3` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段3',
  `del_flag` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '删除标志。0未删除，1删除。',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='索引表';

二、针对普通索引，如下场景会导致查询走全表查询，不走索引

1.准备工作

• 在字段 key1 上创建索引，脚本如下：

ALTER TABLE `t_index` ADD INDEX idx_key1(key1);

• 先看下正常走索引的查询脚本

SELECT * FROM `t_index` WHERE key1 = '1';

• 查看执行计划

EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1';

• 结果显示走索引查询，如下图

2.查询条件使用不等式

• 查询脚本

SELECT * FROM `t_index` WHERE key1 <> '1';

• 查看执行计划，结果显示全表扫描，如下图

• 总结：不等式 <> 或 != 会导致索引失效

3.查询条件类型不一致

• 查询脚本

SELECT * FROM `t_index` WHERE key1 = 1;

• 查看执行计划，结果显示全表扫描，如下图

• 总结：字段 key1 为字符串，传入的值为数字类型，会导致索引失效

4.查询条件使用函数计算

• 查询脚本

SELECT * FROM `t_index` WHERE key1 + 1 = 1;
SELECT * FROM `t_index` WHERE CHAR_LENGTH(key1) = 1;

• 查看执行计划，结果显示全表扫描，如下图

• 总结：函数计算 x+1 、 x-1 、CHAR_LENGTH(x) 等会导致索引失效

5.模糊查询

• 查询脚本

SELECT * FROM `t_index` WHERE key1 LIKE  '3';
SELECT * FROM `t_index` WHERE key1 LIKE  '%3';
SELECT * FROM `t_index` WHERE key1 LIKE  '3%';

• 查看执行计划，如下图

• 总结：模糊查询查询条件前缀模糊不会走索引

三、针对复合索引，如下场景会导致查询走全表查询，不走索引

1.准备工作

• 删除刚才的索引，在字段 key1, key2, key3 上创建复合索引，脚本如下：

DROP INDEX idx_key1 ON `t_index`;
ALTER TABLE `t_index` ADD INDEX idx_key123(key1, key2, key3);

• 先看下正常走索引的查询脚本

SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';

• 查看执行计划

EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';

• 结果显示走索引查询，如下图

2.查询条件使用不等式

• 查询脚本，只要有一个条件含有不等式，都不会走索引

SELECT * FROM `t_index` WHERE key1 <> '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 <> '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 <> '3';

• 查看执行计划，结果显示全表扫描，三种情况结果一样，如下图

• 总结：逻辑普通索引

3.查询条件类型不一致

• 查询脚本

SELECT * FROM `t_index` WHERE key1 = 1 AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = 2 AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = 3;

• 查看执行计划，结果显示（第一个参数类型不一致走全表扫描，第二个参数类型不一致，索引仅仅能使用第一列，第三个参数类型不一致，索引能使用前两列），如下图

• 总结：从第一个查询条件开始，第N个参数类型不一致，索引能使用前N-1列

4.查询条件使用函数计算

• 查询脚本

SELECT * FROM `t_index` WHERE key1 + 1 = '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 + 1 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 + 1 = '3';

• 查看执行计划，结果同上（第一个参数类型不一致走全表扫描，第二个参数类型不一致，索引仅仅能使用第一列，第三个参数类型不一致，索引能使用前两列），如下图

• 总结：逻辑普通索引

5.不使用索引首列当查询条件

• 查询脚本

SELECT * FROM `t_index` WHERE key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key2 = '2';
SELECT * FROM `t_index` WHERE key3 = '3';

• 查看执行计划，结果显示（都不会走索引），三种情况结果一样，如下图

• 总结：查询条件不使用复合索引的首列，均会导致索引失效

猜你喜欢

• K-近邻算法通俗理解与实践

• 11种概率分布，你了解几个？

• 贝叶斯定理的通俗理解

• 朴素贝叶斯及经典实例讲解