【MYSQL基础】4-MYSQL索引失效场景

基础数据

CREATE TABLE `t_user`
(
    `id`          int 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                                                   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

然后插入了一些数据，使用的是mysql8

1. 索引列使用函数

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

2. 索引列参与计算

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

3. 类型隐式转换

explain select * from t_user where id_no = 1002;

4. 使用 LIKE 开头的模糊匹配

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

5. 使用OR

explain select * from t_user where id = 2 or username = 'Tom2';

5. 使用不等于

explain select * from t_user where id_no <> '1002';

6. 连表查询表字符集不一致

会进行字符集的转换导致索引失效

7. 联合索引不满足最左前缀

详见【MYSQL基础】2-MYSQL的索引

8. 字段之间做比较

explain select * from t_user where id > age;

9. order by导致索引失效

explain select * from t_user order by id_no ;

虽然 id_no 是 union_idx(id_no, username, age) 的最左列，理论上可用于排序，但 MySQL 可能仍选 type=ALL（全表扫描）+ Using filesort

SELECT * 不是覆盖索引
- union_idx 不含 create_time（以及完整行），走它就要大量回表。
- 路径变成：扫二级索引 -> 每行回主键索引取整行。
无 WHERE，要读几乎全表
- 既然都要读很多行，优化器常判断“全表扫后排序”更便宜。
表数据量不大时更明显

小表下全扫+排序成本很低，优化器更倾向它。

10. not in 或者 not exist

当使用not in 是如果是非主键索引就会失效，主键索引不会失效，可以使用下列sql验证

explain select * from t_user where id_no not in('1002' , '1003');
explain select * from t_user where id not in (2,3);

原理是索引是为了快速查询存在的数据，不存在的只能全表扫描

顺便记录一下，这些是会走索引的：in、exists、not in、not exists、between and

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';

11. in 的值太多

优化器觉得不如全表扫描快就不会走索引

12. MSQL自己判断

MYSQL基于自己的判断或者数据更新不及时等自己选择不走索引

13. SELECT *

尽量不要使用SELECT *,因为写明字段有的时候可以走联合索引。