MySQL 索引失效情况


防止索引失效的方式:

  1. 索引全值匹配。
  2. 最佳左前缀法则。
  3. 不再索引上做任何操作(计算、函数、类型转换等),否则索引失效。
  4. 存储引擎不能使用索引范围条件右侧的列。
  5. 尽量使用覆盖索引。
  6. MySQL 在使用不等条件时,索引失效。
  7. is not null 无法使用索引。
  8. like 以通配符开头,MySQL 索引会失效;但以通配符结尾,索引不受影响。
  9. 字符串不加单引号导致索引失效(类型转换)。
  10. 使用 or ,索引失效。

SQL 索引失效分析:

注:其中有些索引的使用在数据量极小时可能会生效,但是一般不会有此情况

-- 按照用户表进行索引测试
create table user(
    uid int PRIMARY KEY,
		uname varchar(255) comment '用户名',
		level int(1) comment '等级',
		age int(2) comment '年龄',
		class int(1) comment '班级'
);

-- 插入数据
INSERT INTO USER VALUES(1015, "kafka",      1, 37, 3);
INSERT INTO USER VALUES(1014, "spring",     1, 37, 3);
INSERT INTO USER VALUES(1013, "redis",      1, 37, 3);
INSERT INTO USER VALUES(1012, "activemq",   1, 37, 3);
INSERT INTO USER VALUES(1011, "dubbo",      1, 37, 3);
INSERT INTO USER VALUES(1010, "rabbitmq",   1, 37, 3);
INSERT INTO USER VALUES(1001, "zhangsan",   1, 35, 3);
INSERT INTO USER VALUES(1002, "lisi",       1, 35, 1);
INSERT INTO USER VALUES(1003, "wangwu",     1, 32, 3);
INSERT INTO USER VALUES(1004, "songliu",    2, 28, 3);
INSERT INTO USER VALUES(1005, "sanqi",      2, 28, 1);
INSERT INTO USER VALUES(1006, "yujiangjun", 2, 25, 1);
INSERT INTO USER VALUES(1007, "ruofeng",    3, 25, 2);
INSERT INTO USER VALUES(1008, "kuake",      3, 23, 2);
INSERT INTO USER VALUES(1009, "xinyu",      3, 20, 2);
INSERT INTO USER VALUES(1016, "yangw",      2, 37, 3);
INSERT INTO USER VALUES(1017, "liuqi",      1, 37, 3);
INSERT INTO USER VALUES(1018, "susuo",      3, 37, 2);
INSERT INTO USER VALUES(1019, "deyik",      1, 37, 2);
INSERT INTO USER VALUES(1020, "makua",      2, 37, 2);

-- 创建索引
create index idx_lev_age_clazz on user(level, age, class);
create index idx_name_age on user(uname, age);

-- 最佳左前缀法则
EXPLAIN select uid, uname, level, age, class from user where level = 1 and age = 35 and class = 3;
EXPLAIN select uid, uname, level, age, class from user where level = 1 and age = 33;
EXPLAIN select uid, uname, level, age, class from user where class = 3 and level = 1 and age = 33;
EXPLAIN select uid, uname, level, age, class from user where uname = "xinyu" and age = 20;

--  where 阶段 破坏最佳左前缀法则 ** 覆盖索引可以抗住所有伤害,呜呜呜 **
EXPLAIN select uid, uname, level, age, class from user where age = 35 and class = 3;  -- 破坏最佳左前缀法则,无法走索引
EXPLAIN select uid, uname, level, age, class from user where level = 1 and class = 3; -- 无法走 level 后的索引,破坏最佳左前缀法则的连续性。
EXPLAIN select uid, uname, level, age, class from user where level > 1;  -- 不会走索引,采用了范围查询。
EXPLAIN select level, age, class from user where level > 1 and age = 25; -- 覆盖索引也不可以走!!! age 的索引没有走。
EXPLAIN select uid, uname, level, age, class from user where level = level + 1; -- 不会走索引,因为进行了计算操作。
EXPLAIN select uid, uname, level, age, class from user where uname like "%xinyu" and age = 20; -- 不会使用索引,使用了范围查询,且 % 打头,无法进行索引查询。
EXPLAIN select uid, uname, level, age, class from user where uname like "xinyu%" and age = 20; -- 可以走索引,因为 % 在末尾。
EXPLAIN select uid, uname, level, age, class from user where level = 1 and age != 35 and class = 3; -- 不走 不等式 后面的索引。
EXPLAIN select uid, uname, level, age, class from user where level is null and age = 35; -- is null 可以走索引
EXPLAIN select uid, uname, level, age, class from user where level is not null and age = 35; -- is not null 不可以走任何索引
EXPLAIN select uid, uname, level, age, class from user where uname = 20 and age = 20; -- 字符串类型不使用 引号,会发生类型转换,不会走索引。
EXPLAIN select uid, uname, level, age, class from user where level = 1 or age = 35 or class = 3; -- 使用 or 进行连接,不会使用索引。

EXPLAIN select uname, age from user where uname = 20 and age = 20; -- 覆盖索引可以走,类型转换型。
EXPLAIN select uname, age from user where uname like "%xinyu" and age = 20; -- 使用索引,覆盖索引,模糊搜索 % 在前。
EXPLAIN select level, age, class from user where level = level + 1; -- 覆盖索引可以,where 后进行了计算操作。
EXPLAIN select level, age, class from user where level is not null and age = 35; -- 覆盖索引可以运行,使用 is not null 情况。
EXPLAIN select level, age, class from user where level = 1 or age = 35 or class = 3; -- 覆盖索引可以运行,使用 or 连接符情况下。

-- order by 阶段 filesort 
EXPLAIN select uid, uname, level, age, class from user where level = 1 order by uname ; -- 出现了 filesort,因为没有使用索引字段。
EXPLAIN select uid, uname, level, age, class from user where age = 35 and class = 3 order by class; -- 虽然不会走索引,但也没出现 filesort,因为使用了 where 域的字段。
EXPLAIN select uid, uname, level, age, class from user where level = 1 and age = 33 order by class; -- 不会出现 firesort,使用了最佳左前缀法则。
EXPLAIN select uid, uname, level, age, class from user where level = 1 order by class ; -- 出现 filesort,破坏了最佳左前缀法则的连续性。
EXPLAIN select uid, uname, level, age, class from user where level = 1 order by uid ; -- 没有出现 filesort