MySQL 查詢重複資料 SQL 優化：臨時表 + 索引替代子查詢 UPPER()

MySQL 查詢重複資料 SQL 優化：臨時表 + 索引替代子查詢 UPPER()

需求：在 staticcatalogue 表中查詢 Source 欄位不分大小寫的重複值。原始 SQL 使用子查詢 + UPPER()，十萬筆資料需 10 分鐘，百萬筆直接拖垮資料庫。

原始低效 SQL

SELECT * FROM staticcatalogue
WHERE UPPER(Source) IN (
    SELECT UPPER(Source) FROM staticcatalogue
    GROUP BY UPPER(Source) HAVING COUNT(UPPER(Source)) > 1
)
ORDER BY UPPER(Source) DESC;

效能殺手：

• WHERE / GROUP BY / ORDER BY 中重複呼叫 UPPER()，導致無法使用索引 (函數索引 MySQL 8.0 前不支援)。
• 子查詢每行執行一次全表掃描，複雜度 O(N²)。

優化方案：臨時表物化 + 索引

-- 1. 建立臨時表，只保留大寫後的重複 Source
CREATE TABLE staticcatalogue_tmp
SELECT UPPER(Source) AS Source
FROM staticcatalogue
GROUP BY UPPER(Source)
HAVING COUNT(UPPER(Source)) > 1;

-- 2. 為臨時表加索引 (關鍵！)
ALTER TABLE staticcatalogue_tmp ADD INDEX idx_source (Source);

-- 3. 主查詢關聯臨時表
SELECT s.*
FROM staticcatalogue s
WHERE UPPER(s.Source) IN (SELECT st.Source FROM staticcatalogue_tmp st)
ORDER BY UPPER(s.Source) DESC;

優化原理

1. 物化去重集合：子查詢結果集通常遠小於主表，臨時表將其持久化，避免重複計算。
2. 索引加速 IN 子查詢：臨時表 Source 欄位已為大寫且有索引，IN (SELECT ...) 可高效執行 Semi-Join。
3. 主表仍需 UPPER()：因主表原始資料為混合大小寫，無法避免。若頻繁查詢，建議新增虛擬欄位 source_upper VARCHAR(...) GENERATED ALWAYS AS (UPPER(Source)) STORED 並建索引 (MySQL 5.7+ 支援)，徹底消除函數調用。

效能對比 (百萬筆測試環境)

生產環境注意事項

• 臨時表建議使用 ENGINE=MEMORY (資料量小) 或 ENGINE=InnoDB (資料量大)，避免磁碟 I/O 瓶頸。
• 定期清理臨時表或使用 CREATE TEMPORARY TABLE (Session 級自動清理)。
• 若重複值查詢為高頻業務，考慮建立物化視圖或定時任務同步至 Redis Set，查詢直接命中記憶體。
• MySQL 8.0+ 可直接建立函數索引：CREATE INDEX idx_source_upper ON staticcatalogue ((UPPER(Source))); 免去臨時表步驟。