前言：

在MySQL数据库中，有时会由于开发者对索引底层实现不够透彻导致SQL语句的crud性能大幅下降，在本文中，我会讲解一下MySQL中常见的索引失效情况原因分析并给出对应的解决方法。

1) 左或左右模糊匹配（LIKE 的 % 在左侧或两侧）

示例

SELECT * FROM users WHERE name LIKE '%smith';
SELECT * FROM users WHERE name LIKE '%smi%';

为什么失效（本质）

B+ 树索引利用列的有序性做范围查找。当模式以常量前缀开始（例如 name LIKE 'smith%'）时，可以用索引定位到前缀范围；但当模式以 % 开头，查询要匹配任意前缀，无法把范围缩到一个连续区间，索引树无法直接定位到“以某串结尾或包含”的位置，必须全表扫描或至少全索引扫描（回表也多）。

如何检测

EXPLAIN SELECT ... 会显示 type = ALL（全表）或 type = index（全索引扫描）而不是 range/ref/const。

解决办法

• 尽量避免前导 %。若能改为 name LIKE 'smith%' 则可使用索引。

• 使用反向索引（对字符串做反转并建索引）来支持以结尾查找：建立 idx_rev_name 在 REVERSE(name) 上（注意：这是变通且有写入/查询开销）。

• 全文索引（FULLTEXT）或外部搜索（ElasticSearch）用于“包含”型检索。

2) 使用函数（在索引列上应用函数）

示例

SELECT * FROM orders WHERE DATE(create_time) = '2025-11-02';
SELECT * FROM users WHERE UPPER(email) = 'ABC@X.COM';

为什么失效（本质）

当你在索引列上使用函数（或表达式）时，搜索条件变成了“函数(col) = 常量”，索引存的是原始列值，DB 无法用 B+ 树直接找到匹配项，因为树上排序的是 col 而不是 f(col) 的顺序（除非是函数索引/表达式索引被支持并建立了索引）。

如何检测

EXPLAIN 显示不会使用索引，且条件里出现函数。

解决办法

• 把常量也做相同的变换，改写为范围查询：DATE(create_time) = '2025-11-02' → create_time >= '2025-11-02 00:00:00' AND create_time < '2025-11-03 00:00:00'，这样能走 create_time 的索引。

• 在支持的 MySQL 版本（较新版本）可使用“生成列（generated column）”并对生成列建索引，比如 create_date DATE GENERATED ALWAYS AS (DATE(create_time))，然后索引 create_date。

• 避免在 WHERE 中直接对索引列套函数。

3) 表达式计算（在列上做算术/字符串拼接等）

示例

SELECT * FROM people WHERE age + 1 = 30;
SELECT * FROM product WHERE price * discount = 100;

为什么失效（本质）

age + 1 是对列值的变换，索引存储的是 age 的值；要查 age + 1 = 30 等价于 age = 29，但数据库不会自动反推（有些简单表达式可以手工改写），因此无法直接使用索引。

如何检测

EXPLAIN 显示未使用索引；条件里有表达式。

解决办法

• 手工代数变换到“列 = 常量”的形式：age + 1 = 30 → age = 29。

• 如果表达式复杂，考虑引入计算列/生成列并对其建索引。

• 避免在 WHERE 中把列放在表达式左边，尽量把常量或右侧转为列等价形式。

4) 隐式类型转换（列类型与常量类型不同）

示例
假设 phone 列是 VARCHAR(20)，但你写了：

SELECT * FROM users WHERE phone = 13800138000;  -- 整数字面量

或 id 是字符串，但你提供 WHERE id = 123（数字）。

为什么失效（本质）

当比较两边类型不一致时，MySQL 会做隐式类型转换，可能把索引列转换成其它类型，导致索引无法有效利用或转换后的比较无法走索引（取决于转换方向）。例如，将索引列从字符串转为数字可能破坏索引范围查找。
另外，字符串与数字比较可能导致全表扫描或不稳定的比较行为。

如何检测

EXPLAIN 看 ref/const 还是 ALL；也可以通过 SHOW WARNINGS 查看隐式转换产生的提示（有时）。

解决办法

• 确保常量类型与列类型一致：WHERE phone = '13800138000'。

• 在代码中统一类型（Java 后端：把变量转成正确类型或使用 PreparedStatement 的 setString/setInt）。

• 对于混合类型查询，最好修改列类型为合适的类型（兼顾历史数据及兼容性）。

5) 联合索引非最左前缀匹配（最左前缀原则）

示例
有索引：INDEX idx_abc (a, b, c)
以下查询：

SELECT * FROM t WHERE b = 2;           -- 仅 b
SELECT * FROM t WHERE a = 1 AND c = 3; -- a + c（跳过 b）

为什么失效（本质）

MySQL 的联合索引遵循“最左前缀”原则：只有从联合索引最左边的列开始连续使用，才可以利用索引的前缀范围查找。若查询不包含最左列 a，仅以 b 开始，索引无法作为有序前缀定位（除非 MySQL 做索引下推或有特殊优化，但通常不走最优路径）。

如何检测

EXPLAIN 中 key 字段通常为 NULL 或者 type 不是 ref/range。如果只匹配 a 或 a,b 则会用；若是 b 单独，会失效。

解决办法

• 设计索引时考虑查询模式：若常常单独按 b 查询，应建立 INDEX (b) 或 INDEX (b, a)。

• 将常用过滤列放在联合索引的左侧（按最常用的过滤/排序列设计）。

• 对于 a 和 c 联合使用但不同顺序的情况，可建立额外合适的索引（权衡写成本与空间）。

6) WHERE 子句中的 OR（多列 OR，且没有合适的索引）

示例

SELECT * FROM t WHERE a = 1 OR b = 2;

为什么失效（本质）

当 OR 两侧的列都没有单列索引时，DB 需要扫描全表（或做两个索引扫描再合并）。即使其中一个列有索引，复杂的 OR 组合有时仍会导致非最佳计划（MySQL 在某些版本会对 OR 做索引合并，但效果依赖版本和统计信息）。此外，OR 与函数/表达式混合会进一步阻止使用索引。

如何检测

EXPLAIN 可能显示 type = ALL 或显示索引合并（type = index）但成本高。观察 Extra 字段是否提到 Using where 但没有 Using index.

解决办法

• 若可能，把 OR 改写为 UNION：

  SELECT * FROM t WHERE a = 1
  UNION
  SELECT * FROM t WHERE b = 2;
  

  这样可以分别利用各自列的索引（注意是否需要去重，用 UNION ALL 可跳过去重）。

• 为 OR 中涉及的列各建索引（根据查询频率权衡写入开销）。

• 重构业务查询逻辑（比如先按一个条件筛，再在结果上按另一条件过滤）——要注意性能与正确性。

---

辅助检查：如何用 EXPLAIN 快速判断索引是否被用到（简要）

在 MySQL 中执行 EXPLAIN SELECT ...，关注以下字段：

• type：理想为 const/eq_ref/ref/range；出现 ALL 则为全表扫描。

• key：显示使用的索引名；为 NULL 则没用索引。

• rows：估算扫描行数（数值越小越好）。

• Extra：若出现 Using index（覆盖索引） 是好；出现 Using where; Using filesort 等可能有额外开销。

举例：如果 EXPLAIN 显示 type = ALL 且 key = NULL 就说明索引没被利用，回去检查是否存在上面这些失效模式。

---

小结（记忆要点）

• 最左前缀：联合索引必须从左到右连续使用。

• 不要在索引列上变形：函数、算术或表达式会破坏直接查找；用范围或生成列替代。

• 避免前导 %：LIKE '%x' 无法走索引；考虑全文搜索或反向索引。

• 统一类型：保证查询常量类型与列类型一致，避免隐式转换。

• OR 谨慎：必要时改写成 UNION 或分别为条件列建索引。