您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 語句優化實戰：從 1 小時縮減至 5 分鐘的千萬級數據優化案例

編輯：MySQL 綜合教學

SQL 語句優化實戰：從 1 小時縮減至 5 分鐘的千萬級數據優化案例

案例背景

數據統計處理程序
核心表達 千萬級 行
涉及多表 JOIN、子查詢、聚合統計
初始執行時間：~1 小時
優化後：~5 分鐘 (提升 12 倍)

優化策略一：索引優化 (效果最顯著)

1. 缺失索引補齊

問題：CREATE TABLE ... AS SELECT 建表不會自動複製索引

-- 錯誤做法
CREATE TABLE tmp_stats AS SELECT * FROM big_table WHERE ...;

-- 正確做法：建表後立即加索引
CREATE TABLE tmp_stats AS SELECT * FROM big_table WHERE ...;
ALTER TABLE tmp_stats ADD INDEX idx_join_key (join_key);

2. 索引欄位選擇原則

JOIN 條件欄位：`ON a.id = b.id` → 兩側都需索引
WHERE 過濾欄位：高選擇性 (唯一值/總行數 > 0.1) 優先
GROUP BY / ORDER BY 欄位：避免 filesort
覆蓋索引：`SELECT a,b FROM t WHERE c=1` → 索引 `(c,a,b)` 可索引掃描

3. 實測效果

單表 1000 萬行，無索引全表掃描 ~30 秒 → 加索引後 毫秒級

優化策略二：縮小查詢集合 (減少掃描行數)

1. 建立中間臨時表

-- 原始：直接查大表
SELECT ... FROM huge_table ht JOIN other o ON ht.id = o.id WHERE ht.status = 1;

-- 優化：先提取小集合
CREATE TEMPORARY TABLE tmp_ht AS
SELECT id, col1, col2 FROM huge_table WHERE status = 1; -- 從 1000萬 縮減至 50萬

-- 後續所有查詢針對 tmp_ht
SELECT ... FROM tmp_ht ht JOIN other o ON ht.id = o.id;

2. WHERE 條件順序優化

MySQL 優化器會自動重排，但複雜查詢時手動調整有幫助：

將過濾率最高的條件放最前 (或最右，視優化器版本)
使用 EXPLAIN 觀察 rows 估算行數變化

3. 子查詢優化

-- 低效：相關子查詢逐行執行
SELECT * FROM orders o WHERE o.user_id IN (SELECT id FROM users WHERE vip=1);

-- 高效：JOIN 或半連接
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip = 1;
-- 或 MySQL 8.0+ 半連接優化自動處理

優化策略三：去重優化

DISTINCT / GROUP BY 在大結果集上極耗資源
若同一集合多次使用，先建去重臨時表，後續查詢直接用
利用唯一索引隱性去重：`INSERT IGNORE INTO tmp_unique SELECT ...`

綜合優化清單

優化點	工具/命令	預期收益
補充缺失索引	EXPLAIN、pt-index-usage	★★★★★ (數量級)
拆分大查詢、建臨時表	CREATE TEMPORARY TABLE	★★★★☆
子查詢轉 JOIN	EXPLAIN select_type=DEPENDENT SUBQUERY	★★★★☆
避免 SELECT *	代碼審查	★★★☆☆ (減少 I/O、網路)
分頁優化 (深分頁)	延遲關聯/游標分頁	★★★★☆
調整 JOIN 順序	STRAIGHT_JOIN、小表驅動大表	★★★☆☆
使用覆蓋索引	EXPLAIN Extra=Using index	★★★★☆