索引失效的情况及解决 (超详细)

大家都知道，一条查询语句走了索引和没走索引的查询效率是非常大的，在我们建好了表，建好了索引后，但是一些不好的 sql 会导致我们的索引失效，下面介绍一下索引失效的几种情况

数据准备

新建一张学生表，并添加 id 为主键索引，name 为普通索引，(name,age) 为组合索引

CREATE TABLE `student` (
  `id` int NOT NULL COMMENT 'id',
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

插入数据

INSERT INTO `student` VALUES (1, '张三', 18, '2021-12-23 17:12:44');
INSERT INTO `student` VALUES (2, '李四', 20, '2021-12-22 17:12:48');

1. 查询条件中有 or, 即使有部分条件带索引也会失效

例：

explain SELECT * FROM `student` where id =1

此时命中主键索引，当查询语句带有 or 后

explain SELECT * FROM `student` where id =1 or birthday = "2021-12-23"

发现此时 type=ALL, 全表扫描，未命中索引

总结：查询条件中带有 or, 除非所有的查询条件都建有索引，否则索引失效

2.like 查询是以 % 开头

例

explain select * from student where name = "张三"

非模糊查询，此时命中 name 索引，当使用模糊查询后

explain select * from student where name like "%三"

发现此时 type=ALL，全表扫描，未命中索引

3. 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引

例

explain select * from student where name = "张三"

此时命中 name 索引，当数据未携带引号后

explain select * from student where name = 2222

此时未命中 name 索引，全表扫描

总结：字符串的索引字段在查询是数据需要用引号引用

4. 索引列上参与计算会导致索引失效

例

explain select * from student where id-1 = 1

查询条件为 id，但是并没有命中主键索引，因为在索引列上参与了计算

正例

select * from student where id = 2

总结：将参与计算的数值先算好，再查询

5. 违背最左匹配原则

例

explain select * from student where age =18

age 的索引是和建立再 (name,age) 组合索引的基础上，当查询条件中没有第一个组合索引的字段 (name) 会导致索引失效

正例

explain select * from student where age =18 and name ="张三"

此时才会命中 name 和 (name,age) 这个索引

6. 如果 mysql 估计全表扫描要比使用索引要快，会不适用索引

7.other

没有查询条件，或者查询条件没有建立索引
在查询条件上没有使用引导列
查询的数量是大表的大部分，应该是 30％以上。
索引本身失效
查询条件使用函数在索引列上，或者对索引列进行运算，运算包括 (+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
对小表查询
提示不使用索引
统计数据不真实
CBO 计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的 block 要比索引小。
隐式转换导致索引失效. 这一点应当引起重视. 也是开发中经常会犯的错误. 由于表的字段 tu_mdn 定义为 varchar2(20), 但在查询时把该字段作为 number 类型以 where 条件传给 Oracle, 这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn=‘13333333333’;
1,<> 2, 单独的>,<,(有时会用到，有时不会)

13,like ”%_” 百分号在前.

4, 表没分析.

15, 单独引用复合索引里非第一位置的索引列.

16, 字符型字段为数字时在 where 条件里不添加引号.

17, 对索引列进行运算. 需要建立函数索引.

18,not in ,not exist.

19, 当变量采用的是 times 变量，而表的字段采用的是 date 变量时. 或相反情况。

20,B-tree 索引 is null 不会走, is not null 会走, 位图索引 is null,is not null 都会走

21, 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null 时必须要和建立索引第一列一起使用, 当建立索引第一位置条件是 is null 时, 其他建立索引的列可以是 is null（但必须在所有列都满足 is null 的时候）, 或者 = 一个值；当建立索引的第一位置是 = 一个值时, 其他索引列可以是任何情况（包括 is null = 一个值）, 以上两种情况索引都会走。其他情况不会走。

HL🌱数字花园

探索