MySQL导致索引失效的常见行为有哪些？

①对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

②最左前缀原则

 1. 使用函数或表达式

对索引列使用函数或表达式会导致索引失效。这里要特别主要的是，是对索引列使用函数才会导致索引失效，原理也很容易理解，因为如果对索引列使用了函数，那么需要对每行的索引列通过函数计算后得出结果，再与输入参数对比，这样一来很可能打破索引的有序性，也就无法通过索引树快速查找了。而如果是对输入参数使用函数，则不会有这个问题，也就不会影响使用索引快速查找的呢你。切记！

常见的场景有：

①条件字段有函数操作

②数据类型不一致导致的隐式类型转换

③隐式字符编码转换，即连表查询时，两张表的字符编码不一致，导致连表字段（索引列）需要转换后才能匹配，这个过程中使用了函数

• 示例：

  SELECT * FROM table WHERE DATE(indexed_column) = '2023-12-01';

  解决： 改写查询，避免对索引列使用函数。例如：

  SELECT * FROM table WHERE indexed_column >= '2023-12-01' AND indexed_column < '2023-12-02';

---

2. 数据类型不一致

如果查询条件中的值类型与索引字段的数据类型不一致，MySQL 可能无法使用索引。原因就是可能会触发隐式类型转换，而隐式类型转换会对 索引列使用函数，从而导致索引失效，至于为什么对索引列使用函数会导致索引失效，请看第3条。

• 示例：

  SELECT * FROM table WHERE indexed_column = '123'; -- 假设 indexed_column 是 INT 类型

  解决： 确保查询值的类型与列的数据类型一致。

---

3. 隐式类型转换

当查询中涉及到隐式类型转换时，索引可能失效。第1条已经说明原因了。

• 示例：

  SELECT * FROM table WHERE indexed_column = 123; -- 假设 indexed_column 是 VARCHAR 类型

---

4. 模糊查询中的通配符位置

LIKE 查询中，通配符 % 或 _ 放在开头会导致索引失效。

• 示例：

  SELECT * FROM table WHERE indexed_column LIKE '%value';

  解决： 如果通配符不在开头，索引可以生效：

  SELECT * FROM table WHERE indexed_column LIKE 'value%';

5. 不满足索引最左前缀规则

对于复合索引，查询条件必须包含最左边的索引列，否则索引失效。

• 示例： 索引：INDEX(col1, col2, col3)

  SELECT * FROM table WHERE col2 = 'value' AND col3 = 'value'; -- 索引失效

  解决： 按最左前缀顺序使用索引：

  SELECT * FROM table WHERE col1 = 'value' AND col2 = 'value';

---

6. 查询条件中使用 != 或 <>

这些运算符可能会导致索引失效。

• 示例：

  SELECT * FROM table WHERE indexed_column != 1;

---

7. 查询条件中过多的范围查询

范围查询（如 <, >, BETWEEN, LIKE）可能会导致后续的索引列失效。

• 示例： 索引：INDEX(col1, col2)

  SELECT * FROM table WHERE col1 > 10 AND col2 = 5; -- col2 索引失效

---

8. 查询字段未被索引覆盖

如果查询的字段不完全被索引覆盖，MySQL 可能会选择全表扫描。

• 解决： 创建覆盖索引（覆盖查询所需的字段）。

---

9. 表统计信息过期

表统计信息（statistics）不准确可能导致 MySQL 优化器选择全表扫描。

• 解决： 定期更新统计信息：

  ANALYZE TABLE table_name;

---

通过注意这些情况并优化查询，可以尽量避免索引失效，提升 MySQL 查询性能。