MySQL索引失效的几种常见场景

前言

我们在使用MySQL查询数据的时候，总会遇见没有正确使用到索引的情况。
这里我们列举几种常见的，搜索条件使用了索引列却没有走索引的场景。
（以下测试均在MySQL8.0.28中完成，且所有数据均为脚本随机生成）

数据表结构

CREATE TABLE `student_info`  (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `school_id` int UNSIGNED NOT NULL,
  `class_id` int UNSIGNED NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `last_login_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_school_class`(`school_id` ASC, `class_id` ASC) USING BTREE,
  INDEX `idx_name`(`name` ASC) USING BTREE,
  UNIQUE INDEX `uk_mobile`(`mobile` ASC) USING BTREE,
  INDEX `idx_email`(`email` ASC) USING BTREE,
  INDEX `idx_last_login_time`(`last_login_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

索引列

school_id和class_id字段为一组普通复合索引。
name和last_login_time字段都是普通索引。
mobile字段是唯一索引。
email字段是普通索引，但是字段中可以包含NULL值。

场景

使用LIKE进行左模糊或全模糊查询

例：

SELECT * FROM student_info WHERE `name` LIKE '%安琪';
SELECT * FROM student_info WHERE `name` LIKE '%安%';

索引列发生隐式类型转换

例：

SELECT * FROM student_info WHERE mobile = 17271119709;

数据表中，mobile字段是varchar类型，但是SQL中的条件参数是数值类型，参数类型和字段类型不匹配，无法使用索引，走了全表扫描。
但并非所有情况都如此。
例：

SELECT * FROM student_info WHERE school_id = '101';
SELECT * FROM student_info WHERE id = '10001';

如果数据表中，字段是数值类型，查询的时候使用数值字符串也可以正常使用索引。

复合索引不满足最左匹配原则

例：

SELECT * FROM student_info WHERE class_id = 1001;

我们创建的复合索引是school_id+class_id，如果单纯使用class_id查询，不满足最左匹配，无法使用索引。
查询的时候需要加上school_id来满足匹配条件，或者单独使用school_id查询。
例：

SELECT * FROM student_info WHERE school_id = 101;
SELECT * FROM student_info WHERE school_id = 101 AND class_id = 1001;

错误使用OR关键字

例：

SELECT * FROM student_info WHERE `name` = '顾杰宏' OR create_time = '2021-12-30 02:20:07';

OR左侧的name字段是有索引的，但是右侧的create_time字段没有索引，因此只能走全表扫描。
使用OR查询，需要保证两侧的条件均能正确使用到索引。
例：

SELECT * FROM student_info WHERE `name` = '顾杰宏' OR mobile = '17751393867';

索引列使用函数

使用函数将某一字段中的数据进行转换或处理之后，再和SQL条件进行匹配，会导致无法使用索引。
例：

SELECT * FROM student_info WHERE UNIX_TIMESTAMP(last_login_time) > 1648483200;

索引列参数运算，也属于这种情况的范畴。
例：

SELECT * FROM student_info WHERE id + 1 = 11111;

总结

这里仅仅列举了几种不会使用索引，或绝大多数情况下都不会使用索引的几种场景。
像!=、IN、NOT IN、IS NULL、IS NOT NULL这些查询条件，能否使用索引，和实际数据分布情况有很大关系。
实际应用中，一定要结合EXPLIAN语句，来查看SQL的执行计划。
最后，diss一下那些写了一堆索引失效场景，却不提数据库版本甚至连数据库名字都不说的家伙们吧~