MySQL索引失效的11种情况-学习笔记

1.全值匹配

当一个select语句中有若干个等值判断时，尽量创建由相关字段组成的联合索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
-- OK!
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

CREATE INDEX idx_age ON student(age);

CREATE INDEX idx_age_classid ON student(age,classId);
-- OK
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

2. 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，检索数据时从联合索引的最左边开始匹配，来选择使用哪一个索引。

3. 主键插入顺序

由于InnoDB底层为B+树，如果主键频繁乱序插入，会导致频繁的页面分裂，所以主键最好使用自增ID。

4. 计算、函数、类型转换（自动或手动）导致索引失效

失效案例

-- 失效原因：计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
-- 失效原因：函数
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

5.类型转换导致索引失效

6. 范围条件右边的列索引失效

应该将将范围查询条件放置语句最后

7.不等于(!= 或者<>)索引失效

8.is null可以使用索引，is not null无法使用索引

-- OK
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
-- NO
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

9.like以通配符%开头索引失效

10 OR 前后存在非索引的列，索引失效

-- 未使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
-- 使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel'

11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。