Mysql索引失效的情况

联合索引不满足最左匹配原则

联合索引 ：数据库表中对于多个列组合建立的索引。例如INDEX idx_name (col1, col2, col3)。
最左匹配原则：索引安装定义的列的顺序存储和排序，在查询的时候必须包含最左边的列才能使用索引。

失效场景：在联合索引的场景下，查询条件不满足最左匹配原则。

使用select *

在阿里巴巴开发手册中有一个强制的规范：

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗，尤其是 text 类型的字段。
但同时，关于索引的方面，禁止使用select *的语句的好处之一就是可以走覆盖索引。

失效场景：在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引。

索引列参与运算

explain select * from t_user where id + 1 = 2 ;
explain结果：

由于where后面的列进行了计算处理导致无法正常的走索引。同时对于这种列有运算的情况，还会增加数据库的计算负担，数据库需要全表扫描所有的id字段，然后对其计算，计算之后再和参数值进行比较，这样子会对性能有很大的损耗。

失效场景：索引参与了运算，导致全表扫描，索引失效。

索引列参使用了函数

explain select * from t_user where SUBSTR(id_no,1,3) = '100';
explain结果：

索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。

失效场景：索引列参与了函数处理，会导致全表扫描，索引失效。

错误的Like使用

explain select * from t_user where id_no like '%00%';
explain的结果：

常见的like的使用方式：

• 方式一：like ‘%abc’；
• 方式二：like ‘abc%’；
• 方式三：like ‘%abc%’；
  其中方式一和方式二，由于占位符出现在首部，导致无法走索引。这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

失效场景：模糊查询时（like语句），模糊匹配的占位符位于条件的首部。

类型隐式转换

explain select * from t_user where id_no = 1002;

id_no字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描。出现索引失效的原因是：varchar和int是两个种不同的类型。解决方案就是将参数1002添加上单引号或双引号。
但同时有一个特例就i是，如果字段是int类型，而查询条件添加了单引号或者是双引号，则Mysql会参数转换为Int类型，虽然使用了单引号。

失效场景:参数类型和字段类型不匹配，导致类型发生了隐式转换，索引失效。

使用or操作

explain select * from t_user where id = 2 or username = 'Tom2';
explain结果：

如果单独使用username字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，前面id的条件再走一次索引反而是浪费了。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。

但同时如果两边同时使用">"和"<"，则索引也会失效
explain select * from t_user where id > 1 or id < 80;
explain结果：

失效场景：查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效。

两列做比较

如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。
这里举个不恰当的示例，比如age小于id这样的两列（真实场景可能是两列同维度的数据比较，这里迁就现有表结构）：
explain select * from t_user where id > age;
explain结果：

这里虽然id有索引，age也可以创建索引，但当两列做比较时，索引还是会失效的。
失效场景：两列数据做比较，即便两列都创建了索引，索引也会失效。