如何使用 FORCE INDEX / IGNORE INDEX / USE INDEX 干预优化器选择？

考察要点

这道题面试官想看你是否理解 MySQL 查询优化器的局限性，以及在何种情况下需要、并有能力通过索引提示（Index Hint）来人为干预执行计划。这不仅考察你对 USE INDEX, FORCE INDEX, IGNORE INDEX 语法和语义的掌握，更深层次是考察你对查询优化、执行计划分析以及工程实践中权衡利弊的深刻理解。

核心概念

MySQL 的查询优化器是基于成本的优化器（Cost-Based Optimizer, CBO）。它会估算多种执行计划（如使用不同索引、全表扫描）的 I/O、CPU 成本，并选择成本最低的那个。然而，由于统计信息不准、模型估算偏差等原因，优化器有时会做出次优选择。索引提示就是提供给开发者一种干预执行计划的机制。

这三种提示都是 MySQL 特有的语法，并非 SQL 标准。

示例 DDL 与数据场景

假设我们有一张用户表 orders：

 1CREATE TABLE orders (
 2  id INT PRIMARY KEY AUTO_INCREMENT,
 3  user_id INT NOT NULL,
 4  status TINYINT NOT NULL, -- 0:待支付, 1:已支付, 2:已取消
 5  amount DECIMAL(10, 2) NOT NULL,
 6  create_time DATETIME NOT NULL,
 7  KEY idx_status_time (status, create_time),
 8  KEY idx_user_id (user_id)
 9);

假设 status=0 (待支付) 的订单占比极低（约 0.1%），而 status=1 (已支付) 的订单占比很高（约 95%）。

场景分析:

1. 查询低基数（Low Cardinality）的列：

   sql

   ⧉⛶

    1-- 查询所有待支付订单
    2SELECT id, user_id FROM orders WHERE status = 0;

   由于 status=0 的数据量很少，优化器大概率会正确选择 idx_status_time 索引，这是理想情况。

2. 查询高基数（High Cardinality）的列：

   sql

   ⧉⛶

    1-- 查询所有已支付订单
    2SELECT id, user_id FROM orders WHERE status = 1;

   由于 status=1 的数据占了 95%，优化器可能会认为通过 idx_status_time 索引进行大量回表的成本，高于直接全表扫描的成本，从而选择全表扫描。

使用 Hint 干预：

• USE INDEX: 如果在上述场景 2 中，我们根据业务判断，即使回表多，走索引也比全表扫描快（例如，表非常大，全表扫描 I/O 巨大），我们可以建议优化器使用索引。

  sql

  ⧉⛶

   1SELECT id, user_id FROM orders USE INDEX (idx_status_time) WHERE status = 1;

  但如果优化器计算后仍认为全表扫描成本更低，它可能不听。

• FORCE INDEX: 如果我们非常确定必须走 idx_status_time 索引，不管优化器怎么想，就用 FORCE INDEX。

  sql

  ⧉⛶

   1SELECT id, user_id FROM orders FORCE INDEX (idx_status_time) WHERE status = 1;

  这会强制优化器通过 idx_status_time 索引扫描，然后对每一条满足 status=1 的记录进行回表。

• IGNORE INDEX: 假设一个查询 WHERE user_id = 123 AND status = 0，优化器错误地选择了 idx_user_id（可能因为 user_id 的选择性更好），但我们知道先用 idx_status_time 筛选出少量 status=0 的订单再过滤 user_id 会更快。

  sql

  ⧉⛶

   1SELECT * FROM orders IGNORE INDEX (idx_user_id) WHERE user_id = 123 AND status = 0;

  这样优化器就只能从 idx_status_time 或全表扫描中选择了。

常见低分回答

1. 混淆 USE INDEX 和 FORCE INDEX：认为两者作用相同，都是强制使用索引。这是一个重大错误，USE 只是建议，而 FORCE 是强制，强度完全不同。
2. 将索引提示作为首选优化方案：一看到慢查询就想用 Hint 解决，而不是先分析索引设计是否合理、查询语句是否可以改写、统计信息是否准确。这暴露了候选人缺乏体系化的优化思路。
3. 无法解释优化器为何会选错索引：只知道“优化器选错了”，但说不出具体原因，如统计信息陈旧、Cardinality 估算错误、对回表成本估算不准等。这表明对优化器工作原理理解不深。
4. 滥用 IGNORE INDEX(PRIMARY)：为了避免回表，有时会有人禁用主键索引，但这通常是错误的做法。主键查找效率极高，禁用它往往会导致更差的性能。
5. 认为 Hint 是永久有效的：写下 FORCE INDEX 后就认为万事大吉。实际上，随着数据分布、数据量的变化，或者 MySQL 版本升级后优化器变得更聪明，曾经“正确”的 Hint 可能会变成性能瓶颈。

工程实践与优化

• Hint 是最后的手段：在生产环境中，使用 Hint 应该极其谨慎。它相当于在代码中硬编码了执行计划，使系统变得脆弱。优先考虑的优化顺序是：

  1. 优化/增加索引：如创建更合适的复合索引（覆盖索引）。
  2. 改写 SQL：避免在索引列上使用函数、调整 JOIN 顺序等。
  3. 更新统计信息：运行 ANALYZE TABLE your_table; 来帮助优化器做出更准确的判断。
  4. 万不得已再使用 Hint。

• 如何诊断与决策：

  1. 使用 EXPLAIN 或 EXPLAIN ANALYZE (MySQL 8.0+) 分析执行计划。
  2. 关注关键字段：type (是否是 ALL 全表扫描)、key (实际使用的索引)、rows (预估扫描行数)、Extra (是否出现 Using filesort, Using temporary)。
  3. 如果发现 key 不是你期望的最优索引，可以尝试使用 Hint，并再次 EXPLAIN 对比 rows 和 cost 的变化，结合实际执行时间来判断效果。
  4. 所有使用 Hint 的地方都必须有详尽的注释，说明为什么在这里使用了 Hint、当时的背景（数据量、数据分布、MySQL 版本）是什么，以便后续维护。

• 风险与维护成本：

  • 版本锁定：优化器在不断进化，新版本可能已经修复了导致你使用 Hint 的那个问题。Hint 会阻止系统享受到版本升级带来的优化红利。
  • 数据漂移：今天强制使用的索引，在数据量增长和分布变化后，可能不再是最佳选择，成为新的性能瓶颈。

常见追问与延伸题目

1. 追问：为什么优化器会放着一个看起来很好的索引不用，而去选择全表扫描？

   • 回答要点 1 (成本估算)：优化器认为走索引的成本更高。这个成本主要包括两部分：① 索引扫描的 I/O 成本（顺序读）+ ② 回表带来的 I/O 成本（随机读）。当查询的条件（如 WHERE status=1）能匹配表中大部分数据时，优化器会判断大量的随机读回表成本，可能超过一次性顺序读取全表（全表扫描）的成本。
   • 回答要点 2 (统计信息不准)：表的统计信息（如索引的基数 Cardinality）长时间未更新，导致优化器基于过时的数据做出了错误判断。例如，它可能认为 status=1 的数据很少，但实际上已经占了绝大多数。可以通过 ANALYZE TABLE 来更新统计信息。
   • 回答要点 3 (索引选择性)：当索引的选择性（Cardinality / Total Rows）很低时，意味着一个索引键值对应大量数据行，优化器倾向于不使用它。

2. 追问：除了使用 Hint，还有哪些更推荐的方法来引导优化器选择正确的索引？

   • 回答要点 1 (创建最优索引)：最根本的方法是设计出让优化器“无法拒绝”的索引。例如，创建一个覆盖索引，将查询所需的所有列都包含在索引中，这样就避免了回表，大大降低了走索引的成本，优化器自然会优先选择它。
   • 回答要点 2 (改写查询)：简化查询逻辑，避免在 WHERE 子句的索引列上使用函数、类型转换或计算，如 WHERE DATE(create_time) = '2023-01-01' 应改为 WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00'，确保索引生效。
   • 回答要点 3 (使用 ANALYZE TABLE)：定期更新表的统计信息，给优化器提供最新、最准的决策依据，是从根源上解决问题的方法。

3. 追问：这些 Hint 在其他数据库如 PostgreSQL 或 Oracle 中通用吗？

   • 回答要点 1 (MySQL 特有)：USE/FORCE/IGNORE INDEX 是 MySQL/MariaDB 的特有语法，不符合 SQL 标准，在 PostgreSQL、SQL Server、Oracle 等数据库中无法使用。
   • 回答要点 2 (PostgreSQL 的哲学)：PostgreSQL 社区强烈不推荐使用 Hint。其哲学是相信并不断改进优化器。虽然可以通过 SET enable_seqscan = off; 等参数临时影响执行计划，但这被视为调试手段而非生产实践。正确的做法是依赖 ANALYZE 更新统计信息和调整优化器相关的配置参数。
   • 回答要点 3 (Oracle 的实践)：Oracle 则拥有非常强大和成熟的 Hint 体系，以注释形式 /*+ ... */ 存在，是 Oracle DBA 进行性能调优的常规武器。例如 /*+ INDEX(orders idx_status_time) */。这体现了不同数据库在设计哲学上的差异。