Skip to content

选错索引及其处理方法

一、 问题的提出：性能远低预期

  • 现象：一条 SQL 语句，存在合适的索引，但 MySQL 优化器选择了错误的索引（或全表扫描），导致执行效率远低于预期。
  • 例子：SELECT * FROM t WHERE a BETWEEN 10000 AND 20000; 在大量删除和插入数据后，可能放弃索引 a 而选择全表扫描。

二、 优化器的目标与逻辑

  1. 目标：找到 最优执行方案，以 最小代价 执行 SQL。
  2. 核心考量因素：
    • 扫描行数：估算需要访问的记录数量。越少通常越好。
    • 是否使用临时表。
    • 是否需要排序。
  3. 代价估算：优化器会综合评估不同执行计划（使用不同索引或全表扫描）的成本。成本不仅包括索引扫描本身，还包括：
    • 回表 (Table Access by Index)：使用非聚簇索引（二级索引）后，需要回到主键索引查找整行数据的代价。
    • 排序代价：如果需要排序，其资源消耗。

三、 选错索引的原因分析

  1. 不准确的索引统计信息：

    • 核心：优化器依赖 索引统计信息 来估算扫描行数，而这个信息可能不准。
    • 基数 (Cardinality)：索引中不重复值的数量。基数越大，索引区分度越好，优化器倾向于使用它。
    • 统计方式：MySQL 通常采用 采样统计（默认 N=8 或 N=20 个数据页）来估算基数，而非精确统计全表，成本低但容易不准。
    • 更新时机：统计信息会在数据变更超过一定比例 (默认 1/10 或 1/16) 时自动更新，或手动通过 ANALYZE TABLE t; 更新。
    • 问题：即使基数估算相对准确，优化器对具体查询条件的 扫描行数估算 (rows) 也可能偏差很大（如例子中估算 37116 行，实际 10001 行），导致错误决策。

  2. 错误的代价估算模型：

    • 回表代价：优化器可能认为“全表扫描（直接在主键索引上）”的代价低于“索引扫描 + 大量的回表操作”，即使索引扫描的行数更少。
    • 排序代价：优化器可能倾向于选择一个虽然扫描行数多但能避免排序的索引（如 ORDER BY b 恰好使用索引 b），认为其总代价更低。

四、 索引选择异常的处理方法

  1. ANALYZE TABLE table_name;

    • 作用：强制重新统计表的索引信息。
    • 适用场景：主要解决因 统计信息不准确 导致的索引选择错误。如果执行 EXPLAIN 发现 rows 估算值与实际情况差距悬殊，可尝试此方法。

  2. FORCE INDEX (index_name)

    • 作用：在 SQL 语句中 强制 优化器使用指定的索引。
    • 优点：直接、有效，能快速“矫正”优化器的错误选择。
    • 缺点：
      • 写法不优美，可维护性差（索引改名需同步修改 SQL）。
      • 数据库迁移可能不兼容。
      • 缺乏敏捷性：通常是线上出问题后才添加，需要修改、测试、发布流程。

  3. 修改 SQL 语句，引导优化器

    • 目的：通过调整 SQL 写法，影响优化器的代价估算，使其倾向于选择期望的索引。
    • 例子：
      • 将 ORDER BY b LIMIT 1 改为 ORDER BY b, a LIMIT 1，使得两个索引都需要排序，扫描行数成为主导因素。 (注意：需保证语义一致)
      • 使用子查询或调整 LIMIT 值等方式“诱导”优化器。
    • 缺点：不具备通用性，需要理解优化器逻辑和数据特征，可能只对特定语句有效。

  4. 修改或删除索引

    • 新建更合适的索引：提供一个代价明显更低的索引供优化器选择。
    • 删除误导优化器的索引：如果优化器错误选择的索引并非必要，或有更好的替代方案，可以考虑删除它。
    • 优点：从数据库层面解决问题，对应用透明。
    • 缺点：需要 DBA 或开发者评估索引的必要性，操作需谨慎。

五、 总结

  • MySQL 优化器基于成本估算选择索引，但其依赖的统计信息可能不准，且成本模型并非完美，导致有时会选错索引。
  • 遇到索引选择问题时，首先考虑用 ANALYZE TABLE 更新统计信息。
  • 如果统计信息准确但仍选错，可使用 FORCE INDEX 作为临时或最终手段，或尝试修改 SQL 语句引导优化器。
  • 最理想的情况是调整索引设计（增加或删除），从根本上解决问题。
  • 理解优化器的工作原理和局限性，有助于诊断和解决此类性能问题。