人人都会AI编程

函数运算、隐式类型转换、模糊查询、范围查询右侧截断、OR 连接非索引列

更新时间：2026-07-11

索引失效是开发中最常遇到的性能陷阱。明明建了索引，一条 SQL 却依然慢到超时，往往是踩了下面这些坑。每种情况都有明确的产生原因、识别方法和修正策略。

函数运算：索引列上使用函数或表达式

现象：对索引列进行函数运算或表达式计算后，优化器无法直接匹配索引中存储的原始值，导致索引失效。

-- 索引失效：对索引列 apply_time 使用了函数
SELECT * FROM orders WHERE DATE(apply_time) = '2024-01-01';

-- 索引生效：使用范围条件，索引可被利用
SELECT * FROM orders WHERE apply_time >= '2024-01-01 00:00:00' AND apply_time < '2024-01-02';

原理：B+ 树索引存储的是列的原始值（或原始值组合），而不是 DATE(apply_time) 计算结果。优化器无法提前知道运算后的值与索引的关系，只能放弃索引，转全表扫描。

常见运算符和函数包括：+、-、*、/、DATE()、YEAR()、SUBSTRING()、CONCAT() 等。

实用解决方案：

  • 让条件作用于数据，而非索引列：将函数运算移到等号右边，或重写为范围查询。
  -- 不良：WHERE YEAR(create_time) = 2024
  -- 优良：WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'
  • 使用生成列 + 函数索引（MySQL 8.0）：如果必须按函数结果查询，可以创建一个虚拟生成列并用该列建索引。
  ALTER TABLE orders ADD COLUMN apply_date DATE GENERATED ALWAYS AS (DATE(apply_time)) STORED;
  CREATE INDEX idx_apply_date ON orders(apply_date);

查询时直接使用 WHERE apply_date = '2024-01-01'，索引即可生效。

隐式类型转换：索引列与参数类型不匹配

现象：当 SQL 中索引列的类型与传入值的类型不一致时，MySQL 可能会对索引列进行隐式类型转换，导致索引失效。最常见的是字符串列与数字的比较。

-- phone 列是 varchar 类型，但传入的是数字 13800000000
-- 索引失效：MySQL 会将 phone 列的字符串值隐式转换为数字再比较
SELECT * FROM users WHERE phone = 13800000000;

-- 索引生效：传入字符串 '13800000000'
SELECT * FROM users WHERE phone = '13800000000';

原理：MySQL 的类型转换规则：当字符串与数字比较时，会将字符串转为数字。这个转换发生在索引列上，等价于 CAST(phone AS DECIMAL)，所以索引失效。反过来，如果数字与字符串比较，数字可能被转为字符串，通常索引列不受影响，但具体行为取决于上下文。最稳妥的做法是始终保持类型一致。

真实教训：前端表单提交用户 ID 时，后端因未做类型处理直接将数字传入 SQL，而 user_id 定义为 VARCHAR(32)，导致查询走全表扫描，CPU 飙升。更危险的是，隐式转换可能引起查询语义变化（比如 SELECT * FROM t WHERE a = 0 会将所有不以数字开头的字符串转换为 0，命中的行远多于预期）。

实用解决方案：

  • 应用程序侧保证类型匹配：在构建 SQL 时使用参数化查询，并传入正确的数据类型。Java 中 PreparedStatement.setString() 要对应字符串列。
  • 表设计注意：ID 类字段如果全是数字，但可能包含前导零，可考虑定义为 char 类型，避免隐式转换。
  • 排查方法：运行 SHOW WARNINGS 会提示 Type conversion 字样，或使用 EXPLAIN 看到 type = ALL 且 Extra 无索引使用信息。

模糊查询：前缀通配符导致索引失效

现象：LIKE 模糊查询以 % 开头时，索引失效；% 在中间或末尾则可以正常使用索引。

-- 索引失效：通配符在左侧
SELECT * FROM products WHERE name LIKE '%手机%';

-- 索引生效：通配符在右侧（前缀匹配）
SELECT * FROM products WHERE name LIKE '华为手机%';

原理：B+ 树索引按照列值从左到右的顺序排列。LIKE 'abc%' 可以用索引快速定位到以 abc 开头的区域，因为此时是一个范围扫描（range）。而 LIKE '%abc' 或 LIKE '%abc%' 无法确定扫描起点，优化器只能全表扫描。

实用解决方案：

  • 全文索引：对于中文分词搜索，使用 MySQL 内置的全文索引（FULLTEXT），配合 MATCH ... AGAINST 语法，支持自然语言查询和布尔查询。需要表引擎为 InnoDB，且创建全文索引后，MySQL 会自动维护倒排索引。
  ALTER TABLE products ADD FULLTEXT INDEX idx_ft_name(name);
  SELECT * FROM products WHERE MATCH(name) AGAINST('手机' IN NATURAL LANGUAGE MODE);
  • 搜索引擎外挂（推荐）：生产环境的中文搜索建议使用 Elasticsearch 等专业搜索引擎，MySQL 只做精确查询和范围查询。
  • 反向索引小技巧：如果必须用后缀匹配，可单独存储一列反转的字符串，然后对反转列建索引，将后缀匹配转换为前缀匹配。例如邮箱后缀搜索，将 example@mail.com 存储为 moc.liam@elpmaxe，然后 LIKE 'moc.liam@%' 使用索引。

范围查询右侧截断：联合索引中范围条件后的列失效

现象：对于联合索引，当查询条件中出现范围查询（>、<、BETWEEN、LIKE 'abc%' 等），该索引的后续列将无法被有效使用。

-- 联合索引 (status, create_time)
-- 索引生效良好：等值 and 范围
SELECT * FROM orders WHERE status = 'paid' AND create_time >= '2024-01-01';

-- 索引失效：create_time 是范围条件下，后面的 status 无法使用索引
-- 如果联合索引是 (create_time, status)，则 create_time 范围后 status 只能过滤，不能用于索引。
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND status = 'paid';

原理：联合索引的 B+ 树按第一列、第二列的顺序存储。第一列等值时，第二列是有序的，可以继续精确定位或范围扫描。但当第一列是范围条件时，满足第一列的所有行中第二列是无序的，优化器只能扫描完第一列范围后逐行判断第二列，因此第二列的索引作用消失（即“索引截断”）。

实用解决方案：

  • 合理设计联合索引列的顺序：将等值判断的列放在前面，范围条件列放在后面。如 (status, create_time) 优于 (create_time, status)。
  • 使用覆盖索引：即使第二列无法用于索引定位，但如果查询只需要索引中的列（覆盖索引），至少可以避免回表，性能仍可接受。
  • 精确化范围条件：尽量把范围缩小，或者拆成多次等值查询（比如按天循环查询再合并）。但这不是首选，合理索引顺序才是根本。

OR 连接非索引列：OR 的两端都需要合适的索引

现象：当 OR 连接的查询条件中，任意一侧的列没有索引（或索引无法使用），则整个查询可能使用全表扫描或执行非常低效的索引合并。

-- 假设 idx_name 在 name 上有索引，age 无索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

原理：MySQL 5.6 以前，碰到 OR 且任一端无法使用索引就直接全表扫描。之后引入了 索引合并（Index Merge） 技术，如果 OR 的两边都能各自使用索引，优化器会分别扫描两个索引，然后按行 ID 取并集。但若一边有索引一边无索引，InnoDB 可能宁愿全表扫描也不会先用索引扫大范围再和全表对比。执行计划中可能出现 type = index_merge 或 type = ALL。

真实情况：即使使用了索引合并，性能也未必好，尤其在结果集大的时候，合并过程耗时可能比全表扫还差。最好的办法是避免 OR 退化为无索引。

实用解决方案：

  • 改写为 UNION ALL（最佳实践）：
  SELECT * FROM users WHERE name = '张三'
  UNION ALL
  SELECT * FROM users WHERE age = 25 AND name != '张三';  -- 避免重复行

每条 SELECT 可以独立使用自己的索引，性能往往大幅优于 OR。

  • 强制索引提示（不推荐长期依赖）：SELECT * FROM users FORCE INDEX(idx_name) WHERE ...，但只适合临时救火。
  • 为 OR 的另一侧也建立索引：如果业务确实经常需要 OR 组合查询，不如补充一个复合索引或相应单列索引。
  • 尽量避免 OR：很多场景下 OR 代表数据模型或者查询设计不够合理，考虑能否通过应用层拆分为多次查询。

总结：这些索引失效场景都有一个共同点——违反了 B+ 树索引有序存储和等值匹配的基本规则。开发者在写 SQL 时，养成用 EXPLAIN 验证执行计划的习惯，就能在开发阶段发现并纠正绝大多数索引失效问题，避免它们进入生产环境。