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) 函數包裹列 → 索引失效。
2. 子查詢重複執行 UPPER()、GROUP BY、HAVING。
3. IN (subquery) MySQL 5.6 前優化極差,易退化為全表掃描。
-- 1. 建臨時表:預先大寫、去重、只保留重複值
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 TX_1 (Source);
-- 3. 主查詢直連臨時表
SELECT s.*
FROM staticcatalogue s
JOIN staticcatalogue_tmp st ON UPPER(s.Source) = st.Source
ORDER BY UPPER(s.Source) DESC;
UPPER(s.Source) 函數,但結合 JOIN 可利用 ref 存取,避免 IN 子查詢反覆執行。source_upper VARCHAR(...) GENERATED ALWAYS AS (UPPER(Source)) STORED(MySQL 5.7+ 虛擬列/8.0+ 存儲列)並建索引,徹底消除函數包裹。-- 分批刪除/標記重複行,避免長事務
DELETE s FROM staticcatalogue s
JOIN staticcatalogue_tmp st ON UPPER(s.Source) = st.Source
WHERE s.id NOT IN (
SELECT MIN(id) FROM staticcatalogue
WHERE UPPER(Source) = st.Source
)
LIMIT 10000;
「臨時表物化中間結果 + 索引加速關聯」是解決 MySQL 子查詢效能、函數索引失效、大小寫不敏感查重的通用套路。配合虛擬列持久化大寫值,可達到生產級穩定效能。