3.4 计算、函数、类型转换（自动或手动）导致索引失效
思考：这两条 sql 哪种写法更好？
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
从执行结果上说，上面sql执行结果没有区别。但是从运行效率上说，第1条sql比之后的要好，因为第一条可以使用上索引！而因为第二条使用了函数，即使建立索引也会导致索引失效。
为何使用函数时优化器会使索引失效呢？您想想，我们只是对student.name字段建立了索引，但并没有对LEFT(student.name，3)建立索引，使用函数后的关键字跟我们建立的B+树可对应不来，怎么能使用B+树优化查询呢？
3.5 类型转换导致索引失效
# 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123; # 使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
name = 123 发生类型转换，索引失效，原因与使用函数也一样，其实类型转换就是使用了隐式的类型转换函数。
3.6 范围条件右边的列索引失效
我们先调用下前面准备的存储过程删除除主键索引外的其它索引。
CALL proc_drop_index('atguigu_db2','student'); SHOW INDEX FROM student;
创建联合索引。
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
执行查询。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
执行结果如下。
注意到key_len是10，说明值使用到了idx_age_classId_name索引中的age与classId部分，而name则没有用上。这是因为classId>20是范围查询，导致其右边的列索引失效。
如果想要完全使用到索引，需要按如下方式创建索引：先写等值查询的列，再写范围查询的列。
create index idx_age_name_classid on student(age,name,classid);
🎨Q：为什么条件查询会导致范围条件后面的列索引失效？
比如说有三个字段 a b c，建立复合索引a_b_c
此时叶子节点的数据排序后可能为
(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)
假设查找 select a,b,c from table where a = 2 and b = 5 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b=5查到两条
(a=2 b=5 c=1) (a=2 b=5 c=2)
最后根据c=2查到目标数据
(a=2 b=5 c=2)
接下来 假设使用了范围条件
select a,b,c from table where a = 2 and b >1 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b>1查到四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
此时要查找c=2了 但是我们发现 这四条数据的c分别是
3,5,1,2 是无序的 所以索引失效了
总结：
因为前一个条件相同的情况下，后续列才会是有序的。
🎃Tips：
应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围设计到的字段写在最后）
3.7 不等于（!= 或者 <>）索引失效
为name字段创建索引
CREATE INDEX idx_name ON student(NAME);
查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ; EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
执行结果如下。
没有失效！！！这个原因还不是特别明确，可能mysql高版本中优化器又做了升级(毕竟不等于不过是等于的取反，确实可以实现优化)？笔者的mysql版本为8.2.06，如果有知道的大佬可以在评论区留言讨论。不过在实际生产或者面试中，这仍然可以作为一种需要关注的特殊情形。
3.8 is null可以使用索引，is not null无法使用索引
原因和原理一模一样。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
同样的，在低版本中索引会失效，高版本中，索引也不会失效哦。
🎑结论：最好在设计数据库的时候就将 字段设置为 NOT NULL 约束。比如可以将 INT 类型的字段，默认设置为 0。将字符串的默认值设置为空字符串（“”）。
扩展：同理，在查询中使用 not like 也无法使用索引，导致全表扫描
3.9 like 以通配符 % 开头索引失效
在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会其作用。只有“%”不在第一个位置，索引才会起作用。
使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
未使用到索引.
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
想想那颗b+树，前面模糊了那么排序还有什么用？
🎠拓展：Alibaba《Java 开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
3.10 OR前后存在非索引的列
在WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR 前后的两个条件中的列都是索引时，查询中才使用索引。
因为 OR 的含义就是两个只要满足一个即可，因此 只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。
查询语句使用 OR 关键字的情况：
#清除现有的索引 CALL proc_drop_index('mymysql', 'student') # 创建索引 CREATE INDEX idx_age ON student(age); # 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
这是因为or连接的查询条件都需要查询，如果一个使用索引，一个不用索引全表扫描，索引根本起不到优化性能的作用。还不如只进行一次全表扫描呢。
解决方式是给未使用索引的列创建索引。
# 再创建一个索引 CREATE INDEX idx_cid ON student(classid); #使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
又翻车了·。。。看来这个情况还是得特别小心啊。
再来。
EXPLAIN SELECT SQL_NO_CACHE age,classid FROM student WHERE age = 10 OR classid = 100;
总结：没事别用select *。
3.11 数据库和表的字符集不匹配
统一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
4.索引一般性建议
假设，index(a,b,c)，下面罗列了一些值得被注意的索引应用场景。
🎉建议
对于单列索引，尽量选择针对当前 query 过滤性更好的索引
在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
总之，书写 SQL 语句时，尽量避免造成索引失效的情况。
