MySQL 慢查詢深度剖析：從 EXPLAIN 到 Profile 的一條 SQL 優化全記錄

MySQL 慢查詢深度剖析：從 EXPLAIN 到 Profile 的一條 SQL 優化全記錄

慢查詢優化是「觀察 → 假設 → 驗證 → 迭代」的科學實驗。本文記錄一條 2000 萬行表上的 GROUP BY ... MAX(id) 慢查詢優化全過程：EXPLAIN 執行計劃解讀、子查詢改 JOIN、臨時表參數調優、Profile 火焰圖定位、多種寫法效能對比，展示從「跑不出結果」到「秒級返回」的工程化優化閉環。

一、場景與表結構

CREATE TABLE `tt` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` CHAR(16) NOT NULL DEFAULT '',
  `rule_id` INT UNSIGNED NOT NULL DEFAULT '0',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ttx` (`name`, `rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 數據量：~2000 萬行

業務需求**：找出「相同 name、相同 rule_id、status=1」分組下的最後一條記錄（最大 id）。

二、初始寫法：子查詢 IN + 聚合

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

2.1 EXPLAIN 分析

+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY            | tt    | ALL  | NULL          | NULL | NULL    | NULL | 1176818 | Using where |
|  2 | DEPENDENT SUBQUERY | tt    | index| NULL          | ttx  | 52      | NULL |       1 | Using index |
+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+

核心問題**：外層 type=ALL 全表掃描 117 萬行，子查詢雖走覆蓋索引但為 DEPENDENT SUBQUERY 逐行執行，極其低效。

三、優化一：子查詢改 LEFT JOIN

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

3.1 EXPLAIN 分析

+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY     |  | ALL    | NULL          | NULL    | NULL    | NULL | 1176503 |             |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |       1 |             |
|  2 | DERIVED     | tt         | index  | NULL          | ttx     | 52      | NULL | 1176818 | Using index |

改進**：派生表掃描 117 萬行，但主表走 eq_ref 主鍵回表，總耗時 1.78s。

四、Profile 火焰圖定位熱點

SET profiling=1;
-- 執行查詢
SHOW PROFILE FOR QUERY 6;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| Sending data                 | 1.101490 |
| converting HEAP to MyISAM    | 0.070147 |
| Sending data                 | 1.193679 |
| removing tmp table           | 0.001491 |
+------------------------------+----------+

關鍵發現**：converting HEAP to MyISAM 表明內存臨時表超過 max_heap_table_size（預設 16MB）轉磁碟 MyISAM，導致大量 I/O。

五、調優臨時表參數

SELECT @@max_heap_table_size/1024/1024;  -- 16MB
SET max_heap_table_size = 16777216 * 4;  -- 64MB
SELECT @@max_heap_table_size/1024/1024;  -- 64MB

效果**：耗時 1.78s → 1.69s（-5%），Profile 中 converting HEAP to MyISAM 消失，熱點集中在 Sending data（存儲引擎層讀取行）。

六、優化二：MAX + CONCAT_WS 繞過派生表

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

6.1 EXPLAIN

+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | tt    | index| NULL          | ttx  | 52      | NULL | 1176818 |       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+

6.2 實測耗時

1.83s（反而更慢），Profile 顯示純 Sending data 1.82s，無臨時表轉換但單線程掃描 117 萬行索引葉子節點，字串拼接開銷大。

七、效能對比總結

八、根因與終極優化方向

1. 核心瓶頸**：GROUP BY name, rule_id 必須掃描全表索引（117 萬行），無法通過索引直接定位分組最大值
2. 硬體層面**：更換 PCIe NVMe SSD + RAID10，Sending data I/O 待機時間可大幅降低
3. 架構層面**：
   • 分區表：按 name Hash 分區，分區裁剪減少掃描範圍
   • 分庫分表：按 rule_id 或 user_id 分片，單表數據量降階
   • 中間件：ShardingSphere/Vitess 透明化路由
4. 業務層面**：若允許最終一致性，引入 Redis 緩存「分組最大 id」映射，查詢降維為 IN (id_list) 主鍵批量查詢

九、優化方法論沉澱

1. EXPLAIN 看 type/key/rows/Extra：識別全表掃描、派生表、臨時表、filesort
2. Profile 看 Status/Duration：定位 Sending data、converting HEAP to MyISAM、Copying to tmp table
3. 參數調優看即時效果：max_heap_table_size/tmp_table_size、sort_buffer_size、join_buffer_size
4. 寫法變換看收益：子查詢↔JOIN、派生表↔CTE（8.0+）、聚合↔窗口函數
5. 認清物理極限**：單表千萬行 GROUP BY 全掃描，SQL 層面優化收益邊際遞減，必須上架構分片

慢查詢優化沒有銀彈，只有「觀測 → 假設 → 驗證 → 迭代」的工程紀律。建立「慢查詢收集 → EXPLAIN/Profile 分析 → 參數/寫法/索引/架構多維嘗試 → 效果驗證 → 知識庫沉澱」閉環，讓優化從藝術變為工程。