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

MySQL 查詢優化深度指南：避免不必要掃描、重構複雜查詢、索引覆蓋與分頁優化

編輯：MySQL 綜合教學

MySQL 查詢優化深度指南

一、判斷是否請求了不必要的資料

問題類型	現象	解決方案
過度查詢	需 10 筆卻 SELECT 100 筆再 LIMIT	精確 SELECT 欄位，避免 `SELECT *`
多表關聯全欄位	JOIN 時返回所有表所有欄位	明確列出所需欄位
重複查詢	同一頁多次查用戶頭像 URL	應用層快取 (Redis/本地快取)

二、三大衡量指標：響應時間、掃描行數、返回行數

響應時間 = 服務時間 + 排隊時間
理想狀態：掃描行數 ≈ 返回行數
WHERE 條件應用層級 (優→劣)：
1. 索引層過濾 (存儲引擎層)
2. 索引覆蓋掃描 (Server 層，無需回表)
3. 回表後過濾 (Server 層，效能最差)

三、優化技巧：掃描大量行但返回少

索引覆蓋掃描：將查詢欄位全部包含在複合索引中
匯總表/物化視圖：預計算常用聚合結果
重寫複雜查詢：拆分、轉換邏輯

四、重構查詢策略

1. 複雜查詢 vs 多個簡單查詢

MySQL 內存掃描百萬行極快，但網傳結果給客戶端較慢。適度拆分大查詢為多個小查詢，減少單次傳輸量與鎖持有時間。

2. 切分大量刪除/更新 (避免長鎖表)

-- 原始：單次刪除 3 個月前資料，可能鎖表許久
DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

-- 優化：分批刪除，每批 1000 筆，間隔休眠
ROWS_AFFECTED=0;
DO {
    ROWS_AFFECTED = EXECUTE(
        "DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000"
    );
    SLEEP(0.05);  -- 50ms 釋放鎖讓其他查詢進入
} WHILE ROWS_AFFECTED > 0;

3. 分解關聯查詢 (應用層 JOIN)

快取效率更高：單表結果易快取
減少鎖競爭：單表查詢鎖粒度小
支援分庫分表：應用層組裝跨庫資料
避免重複存取：資料庫 JOIN 可能重複讀取同一維表

適用場景：應用可快取單表結果、跨庫分佈、可用 IN() 替代 JOIN、單表主導查詢

五、分頁優化 (延伸)

-- 深分頁優化：延遲關聯
SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) LIMIT 10;

-- 主鍵範圍分頁 (最佳)
SELECT * FROM t WHERE id > 1000000 AND id <= 1001000;