MySQL 接口 10秒卡死排查实录：从全表扫描到联合索引的逆袭之路

一、 故障背景：神秘的 10秒超时断连

在排查线上接口性能问题时，我们发现 /orderInfo/listOrderInfo 接口存在严重的性能瓶颈。
当请求中传入 hlhtOperatorId（互联ID）参数时，接口响应时间固定达到 10秒左右，最终抛出 CommunicationsException: Communications link failure 异常，导致前端页面卡死、数据无法加载。

二、 案发现场：EXPLAIN 揪出“隐形杀手”

面对超时问题，我们没有盲目修改代码，而是通过 EXPLAIN 命令对底层 SQL 进行了深度剖析。执行计划（Execution Plan）暴露出两个致命问题：

  1. 全分区扫描（Full Partition Scan）：
    • 在 partitions 列中，MySQL 扫描了从 2018 年到 2029 年的上百个月份分区。由于查询条件中缺乏精确的时间范围限制，数据库被迫在所有历史分区中逐行查找，这是导致查询极慢的根本原因。
  2. 冗余索引误导优化器：
    • 在 key 列中，数据库并没有选择我们期望的联合索引 order_info_hlht_operator_id_end_time_index，而是退而求其次选择了单列索引 idx_hlht_operator_id。同时，rows 列显示预估扫描行数高达 23876 行。

三、 深度归因：为什么优化器会“舍本逐末”？

经过深入分析，我们确认了导致执行计划劣化的核心逻辑：

  1. 成本误判与回表代价：
    • 当 MySQL 使用单列索引 idx_hlht_operator_id 时，它找到了该操作员下的数万条记录。接着，数据库需要拿着这几万个主键 ID 去主键索引里频繁“回表”读取完整数据，再用 end_time 等其他条件进行过滤。
      虚高的估值吓退优化器：在全分区扫描状态下，MySQL 对扫描行数的估值严重失真（虚高至 2.3 万行）。优化器在计算成本时认为“走索引回表的 IO 代价太高，不如直接全表扫描”，从而放弃了更优秀的联合索引。
  2. 冗余索引的干扰：
  • 由于联合索引 (A, B) 的最左前缀已经包含了 hlht_operator_id，单独存在的 idx_hlht_operator_id 纯属冗余。它的存在不仅浪费了写入时的维护成本，还成了优化器做出错误决策的“绊脚石”。

四、 破局之战：三步走彻底根治

针对上述病因，我们实施了以下优化方案：

  1. 清理冗余索引，让联合索引接管：
    • 遵循 MySQL 8.0+ 的最佳实践，我们先将单列索引 idx_hlht_operator_id 设置为不可见（INVISIBLE），验证业务无异常后彻底删除。这迫使优化器重新评估，最终成功命中了完美的联合索引 order_info_hlht_operator_id_end_time_index。
  2. 强制分区裁剪（Partition Pruning）：
    • 在业务代码层面，严格保障查询时必须传入精确的时间范围（如 end_time BETWEEN ‘2026-07-01’ AND ‘2026-07-31’）。这一改动让 partitions 列从上百个骤减为单个目标分区，扫描行数估值断崖式下降。
  3. 优化分页统计 SQL：
    • 针对分页插件自动生成的 SELECT count(0) FROM (20张表JOIN的复杂SQL)，我们在 XML 中补充了自定义的 _COUNT 精简版统计 SQL，避免了统计总数时的无效多表关联。

五、 最终效果与经验沉淀

经过上述优化，该接口的执行计划从“全分区扫描 + 高频回表”完美蜕变为“精确分区扫描 + 索引条件下推（ICP）”。接口耗时从 10秒+ 骤降至毫秒级，卡死和超时断连问题被彻底消灭。
本次实战为我们留下了三条宝贵的避坑指南：

  1. 警惕冗余索引：
    • 已有联合索引 (A, B) 时，绝不可再建单列索引 (A)，以免优化器“舍本逐末”。
  2. 分区表必带分区键：
    • 对于大表分区，查询条件中必须包含分区字段（如时间），否则会导致极其昂贵的全分区扫描。
  3. 善用 EXPLAIN：
    • 遇到慢查询，先用 EXPLAIN 看 key（实际索引）、rows（扫描行数）和 partitions（分区情况），让数据说话，精准定位性能瓶颈。