千萬級數據下 MySQL 慢查詢優化實戰：從 IN 子查詢到 JOIN 再到覆蓋索引

千萬級數據下 MySQL 慢查詢優化實戰：從 IN 子查詢到 JOIN 再到覆蓋索引

某業務表 2000 萬行，需求：查找相同 name、rule_id 下 status=1 的最大 id 記錄。本文記錄從原始 SQL 到最終優化方案的完整演進，含執行計劃分析、Profile 火焰圖定位、參數調優、硬件升級建議。

表結構與數據分布

CREATE TABLE `tt` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` CHAR(16) NOT NULL DEFAULT '',
  `rule_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ttx` (`name`, `rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 數據量：2000W 行，name/rule_id 組合基數約 117 萬

方案演進歷程

❌ 方案一：IN 子查詢（原始寫法）

SELECT * FROM tt 
WHERE id IN (SELECT MAX(id) FROM tt GROUP BY name, rule_id) 
AND status = 1;

執行計劃：外層 type=ALL 全表掃描 117 萬行，內層 DEPENDENT SUBQUERY 每行執行一次索引掃描 → 極慢，需殺進程

⚠️ 方案二：Derived Table + LEFT JOIN

SELECT a.id, b.name, b.rule_id 
FROM (SELECT MAX(id) AS id FROM tt GROUP BY name, rule_id) a
LEFT JOIN tt b ON a.id = b.id;

執行計劃：

• 衍生表 type=ALL（物化到臨時表）117 萬行
• JOIN type=eq_ref 主鍵查找

Profile 定位：converting HEAP to MyISAM 耗時 0.07s，Sending data 總耗時 1.77s

調優嘗試：max_heap_table_size=64M 避免臨時表落盤 → 耗時 1.69s（提升微乎其微）

⚠️ 方案三：MAX(CONCAT) 技巧（反模式）

SELECT MAX(CONCAT_WS(' ', LPAD(id,5,' '), status)) AS res, name, rule_id 
FROM tt GROUP BY name, rule_id;

結果：1.82s，更慢。GROUP BY 默認返回首行，MAX() 無法正確攜帶非聚合列值，屬於「看似優雅實則錯誤」的寫法。

核心性能瓶頸分析

最佳實踐方案（架構層面）

1. 業務妥協：若允許「近似實時」，定時任務物化結果到摘要表，查詢摘要表毫秒級返回
2. 分區表：按 rule_id 或 id 範圍分區，縮小掃描範圍
3. 分庫分表：rule_id 取模分片，中間件路由
4. 硬件升級：NVMe SSD + RAID10，隨機 I/O 能力提升 10-50 倍
5. 覆蓋索引優化：ALTER TABLE tt ADD INDEX idx_cover (name, rule_id, id, status); 使衍生表與 JOIN 均走覆蓋索引，避免回表

關鍵經驗總結

• 🔴 IN 子查詢在大表慎用：5.6+ 雖有優化，但 DEPENDENT SUBQUERY 仍可能退化
• 🟡 Derived Table 會物化到臨時表：5.7+ 支援 derived_merge 合併優化，但複雜聚合仍會物化
• 🟢 覆蓋索引是銀彈：將查詢所需列全部包含在索引中，徹底避免回表
• 🔵 MAX() + GROUP BY 不能攜帶非聚合列：MySQL 僅在 ONLY_FULL_GROUP_BY=OFF 允許，結果不可預期
• ⚫ 單表千萬級，純 SQL 優化有天花板：需結合分區、分片、硬件、緩存、異構索引綜合治理

驗證命令

-- 開啟 Profile
SET profiling=1;
-- 執行目標 SQL
-- 查看詳細耗時
SHOW PROFILES;
SHOW PROFILE FOR QUERY ;

-- 查看優化器追蹤 (5.6+)
SET optimizer_trace="enabled=on";
SELECT ...;
SELECT * FROM information_schema.optimizer_trace;