Mysql进阶优化篇02——索引失效的10种情况及原理（三）

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;

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%';

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 语句时，尽量避免造成索引失效的情况