萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 不分大小寫查重 SQL 優化:臨時表+索引將十萬級查詢從 10 分鐘降至秒級

低效原寫法

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;

效能提升原理

  • 臨時表僅含重複值(極小),索引查找 O(log n)。
  • 主表仍需 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 子查詢效能、函數索引失效、大小寫不敏感查重的通用套路。配合虛擬列持久化大寫值,可達到生產級穩定效能。

copyright © 萬盛學電腦網 all rights reserved