「MySQL」必知的Mysql索引失效场景

什么是索引

索引 ( index）是帮助Mysql 高效获取数据 的 数据结构(有序)
最典型的例子就是查新华字典，通过查找目录快速定位到要查找的字。

使用索引的好处与好处？

优点：避免全表扫描、提高查询效率
缺点：会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引

索引的分类有哪些？

1. 普通索引：最基本的索引，没有包含空值
2. 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，允许有空值
3. 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key来约束
4. 联合索引（复合索引）：多个字段上建立的索引，列值的组合必须唯一，能够加速复合查询条件的检索
5. 全文索引：需要扩展

Mysql索引的数据结构是哪一种？

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的数据结构，目前索引的数据结构是B+树

为什么使用B+树作为索引的数据结构?

使用B+树索引，就不需要加载所有数据，B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升，

普通索引B+树示例图

联合索引B+树示例图

索引失效的场景

欢迎阅读：15个必知的Mysql索引失效场景，别再踩坑了

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

1、 联合索引不满足最左匹配原则

联合索引

KEY `union_idx` (`id_no`,`username`,`age`),

走索引的情况

explain select * from t_index_user where id_no = '1002';
explain select * from t_user where id_no = '1002' and username = 'Tom2';
explain select * from t_user where id_no = '1002' and age = 12;

不走索引的情况

explain select * from t_user where username = 'Tom2' and age = 12;

2、使用了select *

覆盖索引走索引

explain select id_no, username, age from t_user where username = 'Tom2';
explain select id_no, username, age from t_user where age = 12;

*号查询不走索引

explain select * from t_index_user where age = 12;

3、索引列参与运算

不走索引

explain select * from t_user where id + 1 = 2 ;

走索引

-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;

4、索引列参使用了函数

不走索引

explain select * from t_user where SUBSTR(id_no,1,3) = '100';

5、错误的Like使用

不走索引

explain select * from t_user where id_no like '%00%';
explain select * from t_index_user where id_no like '%00';

走索引

explain select * from t_index_user where id_no like '00%';

6、类型隐式转换

不走索引

explain select * from t_user where id_no = 1002;

id_no字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描。出现索引失效的原因是：varchar和int是两个种不同的类型。

走索引

explain select * from t_user where id_no = '1002';

7、使用OR操作

索引失效

explain select * from t_user where id = 2 or username = 'Tom2';
explain select * from t_user where id  > 1 or id  < 80;

查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效。

8、两列做比较

如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。
不走索引

explain select * from t_user where id > age;

9、不等于比较

当查询使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。
不走索引

explain select * from t_index_user where id_no <> '1002';
explain select * from t_index_user where create_time != '2022-02-27 09:56:42';

走索引

explain select * from t_index_user where id != 2;

查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

10、is not null

不走索引

explain select * from t_user where id_no is not null;

查询条件使用is null时正常走索引，使用is not null时，不走索引。

11、not in和not exists

走索引

explain select * from t_user where id in (2,3);
explain select * from t_user where id_no in ('1001','1002');
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
explain select * from t_user where id_no between '1002' and '1003';
-- 把条件列换成主键
explain select * from t_user where id not in (2,3);

不走索引

-- 非主键列
explain select * from t_user where id_no not in('1002' , '1003');

查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

12、order by导致索引失效

不走索引
普通索引列排序

explain select * from t_user order by id_no limit 10;

走索引
对主键索引排序

explain select * from t_user order by id desc;

走索引
覆盖索引的场景也是可以正常走索引

explain select * from t_user order by id,id_no desc;
explain select * from t_user order by id,id_no desc limit 10;
explain select * from t_user order by id_no desc,username desc;

当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证

13、参数不同导致索引失效

不走索引
全表扫描比走索引效率更高，因此就放弃了走索引

explain select * from t_user where create_time > '2022-02-27 09:04:23';

走索引

explain select * from t_user where create_time > '2023-02-24 09:04:23';

14、其他

Mysql优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。