索引失效的几个典型场景总结

索引失效

1. or条件连接

在 MySQL 中，当使用 OR 连接多个条件时，有时候索引可能会失效。这是因为 OR 连接条件的查询涉及多个列或多个表，而不同条件可能使用不同的索引，导致 MySQL 无法有效地利用索引进行查询。
以下是一些常见导致索引失效的情况：

1.1 不同列上的索引：

如果 OR 连接的条件涉及到不同列，而这些列分别有各自的索引，MySQL 通常无法同时使用这些索引。

1.2 其中一个条件没有索引就会导致全部索引失效

因为一个条件没有索引都会导致全表扫描

1.3 复杂的逻辑条件：

当 OR 连接的条件非常复杂时，包括多个逻辑操作符、子查询等，MySQL 的优化器可能难以有效地选择和使用索引。

1.4 优化 or

使用 UNION： 将 OR 分解为多个 UNION 查询，每个查询只涉及一个条件，这样每个条件可以使用各自的索引。

2. 字段类型的隐式转换

第二种情况：索引字段是int类型

概述

隐式转换可能导致索引失效的主要原因在于，它会改变查询中的数据类型，使得查询条件无法有效匹配索引中的数据类型，从而使索引无法被利用。

2.1 第一种情况：索引字段是varchar类型

引是按照列的数据类型进行排序和存储的。在查询中进行隐式转换后，查询条件的数据类型可能与索引中的数据类型不匹配，导致无法有效地使用索引。
select * from user where index_filed=2;
因为等号两侧类型不一致，因此会发生隐式转换，cast(index_filed as signed)，然后和2进行比较。因为’2’，’ 2’，’2a’都会转化成2，因为字段存的索引是用字符类型，故MySQL无法使用索引只能进行全表扫描，造成了慢查询的产生。

2.2 第二种情况：索引字段是int类型

select * from user where index_filed=’2’;
这次等号右侧是’2’，注意带单引号哟，左侧的索引字段是int类型，因此也会发生隐式转换，但因为int类型的数字只有2能转化为’2’，是唯一确定的。所以虽然需要隐式转换，但不影响使用索引，不会导致慢查询。

2.3 总结

隐式转换可以总结为：如果转换后和字段类型一致，索引不会失效，如果类型不一样了，就会失效，因为索引存的是根据字段类型存的一种数据结构。

3. like 条件%开头

前缀索引的限制： 以 % 开头的 LIKE 查询会导致 MySQL 无法使用普通的 B-Tree 索引进行匹配，因为 B-Tree 索引是从左到右逐一匹配的。这样的查询需要从索引的起始位置开始进行全表扫描，而不是从索引的一侧开始匹配。

4. 对字段进行函数操作

4.1 函数计算破坏索引的有序性：

索引是按照特定的顺序存储数据的，以支持高效的查找。当对索引列进行函数计算时，计算结果可能会打破索引列的有序性，导致无法有效地使用索引。

函数计算可能导致全表扫描：

一些函数计算可能会涉及到整个表的数据，这就导致了全表扫描的需求，而无法利用索引。

5. 联合索引不符号最左匹配法则

6. 范围查询 也可能导致索引失效

6.1不连续的索引项：

索引是按照一定顺序（例如 B-Tree）存储的，而范围查询通常会涉及到多个不连续的索引项，导致无法有效地使用索引。

6.2 范围查询的模糊性：

范围查询可能会涉及到一个范围内的多个值，这使得优化器难以有效地使用索引进行快速定位和检索。
查询条件的左前缀： 对于 B-Tree 索引来说，如果查询条件不符合最左前缀法则，那么索引也可能无法被使用。最左前缀法则要求查询条件中的列要与索引的最左侧的列进行匹配。

6.3 优化

使用 >= 操作符进行范围查询时，索引通常能够保持有效。这是因为 >= 表示“大于等于”，在索引结构（比如 B-Tree 索引）中，可以迅速定位到起始位置，然后逐渐向右扫描找到满足条件的范围。

还有一些，慢慢更新。。。