Mysql的索引失效

MySQL 的索引失效指的是：尽管在表上建立了索引，但在某些查询场景下，MySQL 优化器却没有利用这些索引，从而导致查询走了全表扫描，性能大大降低。下面详细说明几种常见的导致索引失效的情况及其原因：

1. 对索引列使用函数或表达式

• 问题描述：如果在 WHERE 子句中对索引列使用函数（如 LENGTH(), SUBSTR(), ROUND() 等）或进行算术运算（例如 id+1=10），MySQL 无法直接利用索引，因为索引中存储的是列的原始值，而非经过计算后的结果。
• 举例：

  SELECT * FROM t_user WHERE LENGTH(name) = 6;
  SELECT * FROM t_user WHERE id + 1 = 10;
  

• 建议：尽量避免在索引列上直接进行函数调用或运算；如果必须使用，可以考虑使用“生成列”（virtual column）并对生成列建立索引。

2. 隐式类型转换

• 问题描述：当查询条件中传入的参数类型与索引列的定义类型不一致时，MySQL 会进行隐式类型转换，这通常会让索引失效。例如：
  • 表中字段 code 定义为 VARCHAR，但查询时写作 WHERE code = 101（数字而非字符串）。
• 举例：

  -- 错误写法：由于 101 是数字，MySQL 需要将 `code` 列的值转换后比较，导致索引失效
  SELECT * FROM t_user WHERE code = 101;
  
  -- 正确写法：使用字符串常量
  SELECT * FROM t_user WHERE code = '101';
  

• 建议：保证查询条件的参数类型与列的数据类型一致，尽量使用相同类型的值。

3. LIKE 模糊匹配中的前置通配符

• 问题描述：B+树索引是按顺序存储的，只能有效用于前缀匹配。如果 LIKE 模式中通配符 % 出现在最前面，如 LIKE '%abc' 或 LIKE '%abc%'，则无法利用索引进行快速定位。
• 举例：

  SELECT * FROM t_user WHERE name LIKE '%林';
  

• 建议：尽量使用前缀匹配，例如 LIKE '林%' 这种写法能利用索引。

4. 联合索引的最左匹配原则

• 问题描述：对于联合索引（例如 (code, age, name)），MySQL 只能利用索引中从最左边开始连续出现的列。如果查询条件未包含联合索引的最左边列，则无法使用整个联合索引。
• 举例：
  • 如果联合索引为 (code, age, name)，查询条件 WHERE age = 21 或 WHERE name = '张三' 将无法利用该索引；而查询 WHERE code = '101' 或 WHERE code = '101' AND name = '张三' 则能部分或完全利用索引。
• 建议：设计查询时尽量使条件包含联合索引的最左列；在建索引时考虑查询条件的使用顺序。

5. OR 条件导致索引失效

• 问题描述：在 WHERE 子句中使用 OR 时，如果 OR 两边的条件中有任一条件没有索引，MySQL 往往会放弃使用索引，从而走全表扫描。
• 举例：

  SELECT * FROM t_user WHERE id = 1 OR age = 18;
  

  如果 age 字段没有建立索引，即使 id 有索引，整个查询也可能无法利用索引。
• 建议：尽量保证 OR 条件中的每个条件都能使用索引，或考虑使用 UNION 替代 OR。

6. NOT IN 与 NOT EXISTS 等否定条件

• 问题描述：对于 NOT IN 和 NOT EXISTS 这类条件，MySQL 通常无法有效利用索引，会导致全表扫描。尤其是在普通索引字段上使用时，索引往往失效；（注意：对于主键字段有时还能利用索引，但不一定理想）。
• 建议：在设计查询时尽量避免使用 NOT IN，可考虑改写为 NOT EXISTS 或其他逻辑方式来优化查询。

7. 返回行数过多（全表扫描判断）

• 问题描述：当查询条件返回的数据量占表中记录数较大（通常超过 10%~30%）时，MySQL 优化器可能认为全表扫描比利用索引更高效，从而选择不使用索引。
• 建议：如果确实需要返回大量数据，可以尝试重新调整查询条件或优化数据结构，以降低扫描行数。

8. NULL 值的影响

• 问题描述：单列索引通常不存储 NULL 值，而复合索引则不存储全部为 NULL 的记录。在涉及 IS NULL 或 IS NOT NULL 的查询中，索引的利用情况可能会受到影响。
• 建议：在设计表结构时，尽量避免让索引列允许大量 NULL 值；如果必须允许，可根据具体情况调整查询写法。

总结与优化建议

• 尽量避免在索引列上直接使用函数、表达式或进行运算；如果需要，请考虑使用生成列和函数索引（MySQL 8.0 以后支持）。
• 保持数据类型一致：确保查询条件的常量类型与表中列的类型一致，避免隐式转换。
• 优化 LIKE 查询：尽量使用前缀匹配（如 LIKE 'abc%'），而不是左模糊（LIKE '%abc'）。
• 联合索引设计：使用联合索引时遵循最左匹配原则，设计时考虑最常用的查询条件。
• 谨慎使用 OR 与否定条件：确保 OR 条件中的所有子条件都能利用索引，或尝试改写查询逻辑。
• 合理估算返回数据量：如果数据返回比例过高，优化器可能自动选择全表扫描，必要时可以通过调整查询条件或重构 SQL 来改善。
• 注意 NULL 的处理：合理设计字段的 NULL 属性，避免因大量 NULL 值影响索引性能。

通过合理设计表结构和索引，并注意查询语句的写法，可以大大提高 MySQL 查询的性能，避免因索引失效而带来的性能瓶颈。