防止索引失效的方式：
- 索引全值匹配。最佳左前缀法则。不再索引上做任何操作（计算、函数、类型转换等），否则索引失效。存储引擎不能使用索引范围条件右侧的列。尽量使用覆盖索引。MySQL 在使用不等条件时，索引失效。is not null 无法使用索引。like 以通配符开头，MySQL 索引会失效；但以通配符结尾，索引不受影响。字符串不加单引号导致索引失效（类型转换）。使用 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
