MySQL 查詢重複數據 SQL 優化：臨時表 + 索引替代子查詢 + UPPER 函數

MySQL 查詢重複數據 SQL 優化：臨時表 + 索引替代子查詢 + UPPER 函數

業務需不區分大小寫查重 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;

三大毒點：

1. WHERE UPPER(Source) IN (subquery)：外層每行調用 UPPER()，無法用索引。
2. 子查詢 GROUP BY UPPER(Source)：臨時表無索引，重複計算 UPPER()。
3. ORDER BY UPPER(Source)：再次全表函數計算 + filesort。

優化方案：臨時表物化去重集

-- 1. 物化大寫去重集，建索引
CREATE TABLE staticcatalogue_tmp 
SELECT UPPER(Source) AS Source FROM staticcatalogue 
GROUP BY UPPER(Source) HAVING COUNT(UPPER(Source)) > 1;

ALTER TABLE staticcatalogue_tmp ADD INDEX idx_source (Source);

-- 2. 主查詢關聯臨時表，消除函數調用
SELECT s.* FROM staticcatalogue s 
WHERE UPPER(s.Source) IN (SELECT st.Source FROM staticcatalogue_tmp st)
ORDER BY UPPER(s.Source) DESC;

優化效果對比

進一步優化建議

1. 函數索引（MySQL 8.0+）：ALTER TABLE staticcatalogue ADD INDEX idx_upper_source ((UPPER(Source))); 直接支持 WHERE UPPER(Source) = ? 走索引，無需臨時表。
2. 生成列（5.7+）：ALTER TABLE staticcatalogue ADD COLUMN source_upper VARCHAR(255) GENERATED ALWAYS AS (UPPER(Source)) STORED, ADD INDEX idx_source_upper (source_upper); 同等效果，兼容 5.7。
3. 應用層預存大寫：寫入時同步維護 source_upper 列，查詢完全走索引，零函數開銷。
4. 大表分批處理：若重複集極大，分批 INSERT ... SELECT 物化，避免長事務鎖擴大。

子查詢 + 函數 = 性能殺手。物化中間結果 + 索引 = 經典破局鑰匙。MySQL 8.0 函數索引 / 5.7 生成列讓這類優化更優雅。