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