小集合 VS 大集合：MySQL 去重計數性能優化

前言
一、場景與問題 🔎
二、通俗執行流程對比
三、MySQL 執行計劃解析 📊
四、性能瓶頸深度剖析 🔍
五、終極優化方案 🏆
六、總結

前言

📈 測試結果：

在對百萬級 indicator_log 表進行 去重計數 的測試中，我們發現：

SQL1（先去重再計數）耗時 ≈ 0.9s，
SQL2（直接 COUNT(DISTINCT)）耗時 ≈ 1.0s。

🔍 核心原因：

SQL1 利用物化臨時表批量去重，I/O 可控；
SQL2 在內存哈希/排序中實時去重，內存與 CPU 負載更重，并觸發更多 spill-to-disk 。

最終，通過覆蓋式聯合索引、內存參數調優及Loose Index Scan等手段，能讓兩者在大數據量下都達到毫秒級。

一、場景與問題 🔎

表結構示例（示例參數）：

CREATE TABLE indicator_log (obj_id      INT,  -- 評估對象 ID (:obj_id)plan_id     INT,  -- 評估計劃 ID (:plan_id)del_flag    TINYINT,  -- 邏輯刪除標志 (:del_flag)INDEX idx_plan    (plan_id), -- 單獨索引 (:plan_id)INDEX idx_delflag (del_flag) -- 單獨索引 (:del_flag)
);

需求：統計某評估計劃中、未被邏輯刪除的唯一對象數。

SQL1（子查詢版）：

SELECT COUNT(obj_id)
FROM (SELECT DISTINCT obj_idFROM indicator_logWHERE plan_id = 312 AND del_flag = 0
) AS t;

SQL2（直接版）：

SELECT COUNT(DISTINCT obj_id)
FROM indicator_log
WHERE plan_id = 312 AND del_flag = 0;

二、通俗執行流程對比

SQL1：階段化去重
- 子查詢去重
```
SELECT DISTINCT obj_id
FROM indicator_log
WHERE plan_id = :plan_id  AND del_flag = :del_flag;
```
  - ?? 數據庫先從大表中抽取所有唯一的 obj_id，并將結果寫入“小籃子”（物化臨時表），
  - 此階段只做一次去重，借助外部排序或分區哈希批量處理，I/O 可控、穩定
- 外層快速計數
```
SELECT COUNT(obj_id)
FROM (… 上一步子查詢 …
) AS t;
```
  - ? 在“小籃子”上做 COUNT，不涉及任何去重邏輯，
  - 僅需對已去重的小結果集掃描一次，CPU 和 I/O 開銷極低
優勢：先縮小數據規模，再聚合，適合大數據量場景。
SQL2：一次性去重
```
SELECT COUNT(DISTINCT obj_id)
FROM indicator_log
WHERE plan_id = :plan_id  AND del_flag = :del_flag;
```
- 實時掃描去重
  - 🏃 MySQL 在全表掃描過程中，邊讀取每行邊將 obj_id 插入內存哈希表或進行內存排序，
  - 每次插入都需判斷是否已存在，CPU 和內存壓力陡增 。
- 礦山級 Hash / 排序
  - 🔄 若待去重行數超過 sort_buffer_size 或 tmp_table_size，會頻繁 spill-to-disk，
  - 導致磁盤 I/O 大幅增加，性能抖動明顯
劣勢：一次性完成去重+計數，對內存依賴高，遇大數據量易觸發磁盤溢寫。
索引合并（Index Merge）附加開銷 ??
- 在只有單列索引 idx_plan(plan_id) 與 idx_delflag(del_flag) 時，MySQL 必須：
  - 分別走兩個索引掃描；
  - 對掃描結果做行號交集（Index Merge Intersection）；
- 雙重掃描 + 交集 也為兩種寫法都增加了額外 I/O 和 CPU 消耗。

三、MySQL 執行計劃解析 📊

SQL1 的 EXPLAIN

EXPLAIN ANALYZE
SELECT COUNT(obj_id)
FROM (SELECT DISTINCT obj_idFROM indicator_logWHERE plan_id = 312AND del_flag = 0
) AS t;

在這里插入圖片描述

執行計劃解析
1. 聚合操作：計算 obj_id 的總數，執行成本和實際時間較低。
2. 表掃描：查詢對 t 表進行了全表掃描，掃描了約 280,269 行，實際執行時間為 902 毫秒。
3. 物化：將中間結果存儲在內存中，避免重復計算，時間與表掃描相同。
4. 臨時表：查詢創建了臨時表進行去重，去重操作與物化時間相同。
5. 過濾條件：通過 del_flag = 0 和 plan_id = 312 過濾數據，執行時間較長，返回 165,849 行。
6. 交集操作：從兩個索引掃描中交集數據，執行時間較長。
7. 索引掃描：
- 使用 idx_plan 掃描符合 plan_id = 312 的數據，執行非常快。
- 使用 idx_delflag 掃描符合 del_flag = 0 的數據，執行較慢，因為掃描了大量數據。

總結

1. Index Merge Intersection  ├─ idx_plan    (plan_id=:plan_id)  └─ idx_delflag (del_flag=:del_flag)  📚 :contentReference[oaicite:3]{index=3}  
2. Temporary table with deduplication       📚 :contentReference[oaicite:4]{index=4}  
3. Table scan on <temporary>  
4. Aggregate: COUNT(obj_id)

交集掃描：分別走兩個單列索引，再取交集，得到 N 條候選行
物化去重：寫入臨時表后批量排序去重，I/O 可控
快速計數：對臨時小表直接 COUNT，耗時極低。

查詢的瓶頸主要在于對 del_flag 的過濾和交集操作，建議優化索引或減少數據量。

SQL2 的 EXPLAIN

EXPLAIN ANALYZE
SELECT COUNT(DISTINCT obj_id)
FROM indicator_log
WHERE plan_id = 312AND del_flag = 0;

在這里插入圖片描述

執行計劃解析
1. 聚合操作：count(distinct indicator_log.obj_id)，計算 obj_id 的去重總數，執行成本和時間較低，實際執行時間為 964 毫秒。
2. 過濾條件：查詢對 indicator_log 表進行了過濾，條件為 del_flag = 0 和 plan_id = 312。過濾后返回了 165,849 行數據，執行時間為 341 到 838 毫秒。
3. 交集操作：通過 INTERSECT 操作結合兩個索引掃描結果，篩選符合條件的數據。執行時間為 341 到 837 毫秒，結果包含 165,849 行。
4. 索引掃描：
- 使用 idx_plan 索引掃描 plan_id = 312 的數據，執行非常快，時間為 0.148 到 85.3 毫秒，掃描了 279,786 行。
- 使用 idx_delflag 索引掃描 del_flag = 0 的數據，執行較慢，時間為 0.051 到 426 毫秒，掃描了大約 1.5 百萬行。
總結
```
1. Index Merge Intersection  ├─ idx_plan  └─ idx_delflag  
2. Filter predicates  
3. Aggregate: COUNT(DISTINCT obj_id)  🔄  
```
- 同樣交集得出 N 行；
- 內存去重：逐行插入 HashSet 或排序，邊去重邊計數
- 瓶頸：大量內存操作易觸發 spill-to-disk 或頻繁 GC，性能抖動明顯
查詢主要瓶頸在于對 del_flag = 0 條件的過濾，因為這個條件掃描了大量數據。可以通過優化索引或減少數據量來提高查詢性能。

四、性能瓶頸深度剖析 🔍

索引合并（Index Merge）開銷

單列索引需做兩次范圍掃描并交集，I/O 與 CPU 成本陡增。
覆蓋式聯合索引可一步到位，跳過合并與回表，大幅縮短掃描范圍。

去重策略對比

特性	臨時表批量去重 (SQL1)	內存哈希/排序 (SQL2)
實現方式	外部排序 + 臨時表 I/O	HashSet/排序，內存優先
穩定性	高（I/O 可控）	受限于 `tmp_table_size`/`sort_buffer_size`
典型場景	中大規模去重	小數據量、快速響應

I/O vs 內存權衡

SQL1：I/O 適當增加，換取穩定去重；
SQL2：依賴內存，當數據量超出配置時表現不穩。

統計信息影響

高選擇性 (plan_id) 與 低選擇性 (del_flag) 配合不當，容易讓優化器選錯計劃；
保持準確統計信息，定期 ANALYZE TABLE 是必備流程。

五、終極優化方案 🏆

覆蓋式聯合索引 ?
```
CREATE INDEX idx_opt ON indicator_log(plan_id, del_flag, obj_id);
```
- 一次掃描完成所有條件過濾：plan_id → del_flag → 取出 obj_id，無需再做索引合并或回表
- 支持索引覆蓋（Covering Index），減少磁盤 I/O，聚合與去重都可在索引層直接完成
內存與臨時表參數調優 🔧
```
SET GLOBAL tmp_table_size        = 256M;
SET GLOBAL max_heap_table_size   = 256M;
SET GLOBAL sort_buffer_size      = 64M;
```
- 增大內存閾值，讓大多數臨時表都在內存中完成，避免頻繁落盤
- 提高排序緩沖區，減少 COUNT(DISTINCT) 或 ORDER BY 時的 spill-to-disk
啟用 Loose Index Scan 🚀
```
SET SESSION optimizer_switch = 'loose_index_scan=on';
```
- 對于 COUNT(DISTINCT obj_id)，MySQL 5.6+ 可以利用“松散索引掃描”
- 在覆蓋索引場景下，只需依次跳讀不同值的第一條記錄，即可高效去重
物化視圖 / 預聚合表 🗄?

寫時維護：在插入/更新階段，通過觸發器或應用邏輯同步維護 (plan_id, unique_obj_count)
定時批處理：夜間或低峰期，將去重結果寫入專用聚合表，查詢時直接讀取，無需在線去重

六、總結

🧺 SQL1 = 小集合計數

先執行子查詢：SELECT DISTINCT obj_id …，把所有唯一值抽取到“小籃子”中（臨時表或物化表），然后再對這“小籃子”做 COUNT(obj_id)。拆分去重和計數兩步，使得 I/O 可控、壓力分散，性能更穩定。
? SQL2 = 大集合實時計數

直接在大表上執行 COUNT(DISTINCT obj_id)，MySQL 需要邊掃描邊在內存中維護哈希表或做外部排序來去重并計數。這種“一次性”實時去重對內存和 CPU 依賴極高，一旦超過內存閾值就會頻繁 spill-to-disk，性能抖動明顯。

👉 真·性能優化，絕非單點發力，而是「SQL 寫法 + 執行計劃 + 索引設計 + 系統參數」四位一體，才能在海量數據面前保持高效穩定