本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
索引（B+ 树 / 覆盖 / 失效 / 优化） · 索引失效与执行计划

哪些操作会导致索引失效？(隐式类型转换、函数包裹列、!=、NOT IN、模糊查询前置%、OR 部分列无索引等)

What operations cause an index to not be used? (e.g., implicit type conversion, functions

答案语言

考察要点

这道题面试官想看你是否真正理解 B+ 树索引的工作原理，以及 MySQL 查询优化器的决策逻辑。高分回答不仅仅是罗列失效场景，而是要从“为什么优化器无法或放弃使用索引”的角度出发，解释其背后的根本原因——即 SQL 写法是否破坏了索引的有序性和可搜索性（SARGable）。

核心概念

索引失效的本质是查询谓词（WHERE 条件）不具备“可搜索性”（Searchable Argument, SARG）。一个 SARGable 的谓词通常是 列 操作符 常量 的形式，且操作符能让优化器利用索引的有序性来快速定位数据范围。当谓词 non-SARGable 时，优化器无法有效利用 B+ 树的结构，可能退化为全索引扫描（index）或全表扫描（ALL）。

以下是导致索引失效的典型场景，及其原理解释：

场景反例 (可能导致索引失效)正例 (可利用索引)原理解释
1. 对索引列使用函数WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-10'WHERE create_time >= '2023-10-01' AND create_time < '2023-11-01'B+ 树中存储的是 create_time 的原始值，并按其排序。对列使用函数后，MySQL 无法预知函数计算结果的顺序，必须对每一行都计算一次函数值，再与条件比较，这等同于全表扫描。
2. 隐式类型转换WHERE phone_number = 13800001111 (phone_number 是 VARCHAR)WHERE phone_number = '13800001111'MySQL 规则：当字符串和数字比较时，会将字符串转换为数字。上述查询会变成 WHERE CAST(phone_number AS SIGNED) = 13800001111，这又回到了场景 1，对索引列使用了函数。注意：如果列是数字类型，条件是字符串，如 WHERE user_id = '123'，通常能走索引，因为 MySQL 会将常量字符串转为数字，而不是对列进行转换。
3. 左模糊查询或双向模糊查询WHERE name LIKE '%张' 或 WHERE name LIKE '%三%'WHERE name LIKE '张%'B+ 树的有序性是从左到右的。'张%' 可以定位到以“张”开头的第一条记录，然后向后扫描。而以通配符 % 开头，无法确定起始点，只能全表扫描。
4. 使用 OR 连接，且部分条件列无索引WHERE indexed_col = 'A' OR unindexed_col = 'B'WHERE indexed_col = 'A' UNION ALL WHERE unindexed_col = 'B' (需评估性能)优化器需要找到满足任一条件的行。即使 indexed_col 能走索引，但 unindexed_col 必须全表扫描。优化器会认为，既然总要全表扫描一次，不如直接一次性全表扫描，同时判断两个条件，成本更低。例外：MySQL 5.0+ 引入了 index_merge 优化，若 OR 两边的列都有独立的索引，可能合并两个索引的扫描结果。
5. 使用 NOT IN 或 !=WHERE status NOT IN (1, 2) 或 WHERE status != 0WHERE status IN (0, 3, 4) 或 WHERE status > 0NOT IN 和 != 通常表示一个很大的、不连续的范围。优化器评估后，如果发现走索引扫描的成本（扫描大量索引页 + 大量回表）比直接全表扫描更高，就会放弃索引。这取决于数据的选择性（Selectivity）。如果 status != 0 排除的记录只占总数的 1%，那么很可能走索引；如果排除了 90%，则大概率全表扫描。
6. IS NULL / IS NOT NULLWHERE indexed_col IS NULL(无)早期 MySQL 版本中，NULL 值不进入索引。现代 InnoDB 中，NULL 值也会被索引（通常放在 B+ 树的最左侧）。因此 IS NULL 可以走索引。但和 != 类似，如果 NULL 值的占比非常高，优化器基于成本考虑也可能放弃索引。
7. 复合索引未使用最左前缀复合索引 idx(a, b, c)，查询 WHERE b = 2 AND c = 3WHERE a = 1 AND b = 2B+ 树对复合索引的排序规则是：先按 a 排序，a 相同再按 b 排序，以此类推。没有 a 作为查询的起始条件，优化器无法利用 (a, b, c) 的有序性来定位 b 和 c。

常见低分回答

  1. 绝对化结论：“用了 OR 索引就一定失效”。这是错误的，忽略了 index_merge 优化的可能性，也忽略了 OR 两边是同一个索引列的情况（如 id = 1 OR id = 100，可以转为 id IN (1, 100)，是典型的 range scan）。
  2. 函数误解：“WHERE 后面用了函数索引就失效”。这种说法不精确。应该是“对索引列本身使用函数”。如果函数用在常量上，如 WHERE create_time > NOW() - INTERVAL 1 DAY，索引是完全可以正常使用的。
  3. 混淆 COUNT(*) 和 COUNT(列)：当被问到索引优化时，错误地回答 COUNT(col) 会比 COUNT(*) 快，因为它不统计 NULL。虽然语法语义正确，但这和索引失效场景无关。正确的关联是：当存在一个二级索引时，COUNT(*) 可能会选择扫描这个更小的二级索引（Extra: Using index），而不是扫描主键索引或全表，这是一种优化，而非失效。
  4. 对隐式转换方向不敏感：只知道“类型转换会失效”，但说不清是“列类型向常量类型转换”还是反过来。高分回答必须点出是 MySQL 将列进行函数包装（CAST）导致的问题。
  5. 背诵口诀，不解释原理：只说“左模糊不走索引”，但无法解释 B+ 树的有序性决定了必须从左侧开始匹配。面试官一追问“为什么”，就无法深入回答。

工程实践与优化

  1. 首选工具 EXPLAIN：任何关于索引是否生效的疑问，都应以 EXPLAIN 的结果为准。

    • type：检查连接类型。从好到坏：const > eq_ref > ref > range > index > ALL。出现 index（全索引扫描）或 ALL（全表扫描）通常意味着索引利用不佳。
    • key：实际使用的索引。如果为 NULL，则表示没有使用索引。
    • key_len：索引使用的字节数。可用于判断复合索引是否完全被利用。
    • Extra：包含关键信息。Using index（覆盖索引）、Using where（在存储引擎层过滤后，服务层还需过滤）、Using filesort（无法利用索引排序）、Using temporary（使用了临时表）。
    sql
     1-- 示例：分析隐式类型转换
     2EXPLAIN SELECT * FROM users WHERE phone_number = 13800001111;
     3-- 查看 Extra 字段，可能会看到 "Using where"，type 可能是 "ALL"

  2. SQL 改写：

    • 函数/转换：将对列的操作转移到对常量值的操作上。 WHERE col/10 = 100 -> WHERE col = 1000 WHERE date_col < CURDATE() -> WHERE date_col < '2023-10-27' (在应用层计算好)
    • OR 优化：如果 OR 的两边 selectivity 都很高，可以考虑拆成 UNION ALL，让每个子查询都能走索引。但要注意 UNION 会引入去重开销。
    • 前缀模糊查询：如果业务确实需要，考虑使用全文索引（Full-Text Index）或引入外部搜索引擎如 Elasticsearch。

  3. 索引设计：

    • 函数索引 (MySQL 8.0+)：如果业务逻辑无法避免对列使用函数，可以创建函数索引。
      sql
       1-- 针对 WHERE JSON_EXTRACT(extra, '$.name') = '张三'
       2CREATE INDEX idx_extra_name ON user_info ((JSON_EXTRACT(extra, '$.name')));
    • 复合索引：遵循最左前缀原则，将过滤性最好的列（Cardinality 高）放在最左边。

常见追问与延伸题目

  1. 追问：如果 name LIKE '%keyword%' 这种需求非常频繁且性能要求高，该如何优化？

    • 回答要点 1 (DB 内部方案)：使用 MySQL 5.7+ 的 ngram 全文索引或 MySQL 8.0+ 的函数索引。ngram 会将文本拆分成固定长度的词组进行索引，可以支持任意位置的模糊匹配，但索引体积较大。
    • 回答要点 2 (外部系统方案)：引入专业的搜索引擎，如 Elasticsearch (ES) 或 OpenSearch。将需要搜索的数据同步到 ES 中，利用其强大的倒排索引和分词能力，可以实现毫秒级的复杂文本搜索，这是业界处理此类问题的标准方案。
    • 回答要点 3 (折中方案)：如果数据量不大，可以考虑创建一个反转列，如 name_rev，存储 name 列的反转字符串。查询 LIKE '%keyword' 就变成了 LIKE REVERSE('keyword')%，可以走 name_rev 列的索引。这是一种 trick，维护成本较高。

  2. 追问：EXPLAIN 结果中 type 是 index，这是好是坏？它和 ALL 有什么区别？

    • 回答要点 1 (定义)：type: index 指的是“全索引扫描”（Full Index Scan）。它会遍历整个索引树的叶子节点，而不是全表。
    • 回答要点 2 (与 ALL 的区别)：它比 type: ALL（全表扫描）要快，因为索引通常比表数据本身小得多，扫描索引的 I/O成本更低。尤其是在二级索引上进行全索引扫描，I/O 会显著减少。
    • 回答要点 3 (好坏判断)：它通常不是一个好的信号，因为它意味着查询没有利用索引的有序性来快速定位数据，而是遍历了整个索引。例如 SELECT id FROM users; 在 id 是主键时，如果 users 表很大，这个操作就会是 type: index。但如果查询是 COUNT(*) 并且有一个很小的二级索引，此时优化器选择 type: index 来扫描这个小索引，则是一种非常高效的优化（Extra: Using index）。所以好坏取决于具体的查询场景。

  3. 追问：如果我建了 idx(a, b) 复合索引，WHERE a > 10 AND b = 5 这个查询能用上索引吗？能用上多少？

    • 回答要点 1 (范围查询的影响)：这个查询能用上索引，但只能部分利用。MySQL 会使用索引 idx(a, b) 来定位 a > 10 的部分。
    • 回答要点 2 (最左前缀中断)：一旦在 a 列上进行了范围查找（如 >、<、BETWEEN），B+ 树的有序性对于后续的列（b 列）就失效了。因为满足 a > 10 的记录，其 b 列是无序的。
    • 回答要点 3 (EXPLAIN 结果)：EXPLAIN 的结果会显示 key 是 idx(a, b)，但 key_len 只会包含 a 列的长度，表示优化器只用索引的第一部分来定位数据，然后在定位到的这些行中，再通过 WHERE 条件去过滤 b = 5 的数据（Extra 中会出现 Using where）。
← Previous
字符串列何时需要转 hash 列加索引？身份证号、URL 等场景？
Next →
EXPLAIN 输出的关键字段（type, key, key_len, rows, Extra）各自含义？