您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 查詢重複資料優化方案：從子查詢到臨時表索引的效能躍升

編輯：MySQL 資料庫相關知識

MySQL 重複資料查詢優化：子查詢陷阱與臨時表破局

查詢不區分大小寫的重複資料是常見需求，但直觀的子查詢寫法在大數據量下效能極差。本文深度解析效能瓶頸，提供臨時表+索引的優化方案，實現數量級提速。

低效寫法：函數包裹欄位的子查詢

-- ❌ 效能極差，十萬級資料可能跑死資料庫
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 條件使用函數：UPPER(Source) 導致無法使用索引，全表掃描
重複計算：子查詢、外層查詢、ORDER BY 三處重複執行 UPPER()
子查詢優化弱：MySQL 對 IN 子查詢優化有限，易退化為全表掃描 + 臨時表

優化方案：臨時表 + 索引預計算

步驟 1：建立去重後的大寫臨時表

CREATE TABLE staticcatalogue_tmp AS
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;

優化原理解析

階段	原始方案	優化方案	提升倍數
分組去重	子查詢每次全表掃描+UPPER計算	一次性建表，持久化結果	10-100x
關聯查詢	IN 子查詢，無索引	臨時表有索引，ref/eq_ref 連接	100-1000x
排序	ORDER BY UPPER() 無索引	同樣問題，但資料量已大幅減少	10x
總體	十萬級 10 分鐘+ / 百萬級跑死	秒級完成	數量級

進階優化技巧

避免外層查詢仍用 UPPER()

若 Source 欄位本身區分大小寫，可考慮：

-- 方案 A：功能性索引 (MySQL 8.0+)
ALTER TABLE staticcatalogue ADD INDEX idx_upper_source ((UPPER(Source)));

-- 方案 B：新增冗餘欄位 source_upper，觸發器維護
ALTER TABLE staticcatalogue ADD COLUMN source_upper VARCHAR(255);
-- INSERT/UPDATE 觸發器 SET NEW.source_upper = UPPER(NEW.Source)
-- 然後直接查 WHERE source_upper IN (...) 可用索引

大數據量分批處理

-- 避免單次大事務鎖表
DELETE FROM staticcatalogue 
WHERE id IN (
    SELECT id FROM staticcatalogue 
    WHERE UPPER(Source) IN (SELECT Source FROM staticcatalogue_tmp)
    LIMIT 10000
);