數據庫優化方案 31 條軍規與實戰案例分析

數據庫優化方案 31 條軍規與實戰案例分析

整理自面試高頻考點與生產實踐，涵蓋 SQL 優化、索引策略、架構設計等核心領域。

SQL 查詢優化篇

1. 避免全表掃描：優先在 WHERE、ORDER BY 涉及的列上建立索引
2. 拒絕 NULL 判斷：WHERE num IS NULL 導致索引失效 → 設置默認值 0，改為 WHERE num = 0
3. 慎用 != / <>：同樣導致全表掃描
4. OR 條件優化：WHERE num=10 OR name='admin' 若其中一列無索引會全表掃描 → 改用 UNION ALL 分開查詢
5. IN / NOT IN 謹慎用：連續值用 BETWEEN，子查詢優先考慮 EXISTS 替代 IN
6. LIKE '%abc%'：前導通配符不可用索引 → 考慮全文檢索
7. 參數化查詢陷阱：WHERE num = @num 編譯時無法確定值 → 強制指定索引 WITH(INDEX(...))
8. 列上避免運算/函數：WHERE num/2=100 → WHERE num=200；SUBSTRING(name,1,3)='abc' → name LIKE 'abc%'
9. 復合索引最左前綴原則：必須包含索引第一列，順序盡量一致

索引設計篇

10. 拒絕 SELECT *：明確列出需要的列，減少 I/O 和網絡傳輸
11. 索引不是越多越好：單表建議 ≤6 個，權衡讀寫性能（寫入需維護索引）
12. 避免更新聚簇索引列：會導致物理順序重排，開銷極大
13. 優先數字類型：字符串比較逐字符，數字一次比較，性能差異明顯
14. VARCHAR 替代 CHAR：節省空間，縮小掃描範圍

DML 與架構篇

15. UPDATE 僅更新必要列：避免全列更新產生大量日誌
16. 大表 JOIN 先分頁後關聯：減少邏輯讀
17. 杜絕無條件 COUNT(*)：全表掃描無業務意義
18. 表變量替代臨時表：注意表變量僅支持主鍵索引
19. 臨時表管理：大量據用 SELECT INTO 避免大量日誌，用完顯式 TRUNCATE + DROP
20. 游標慎用：>1萬行考慮改寫為集合操作
21. 優先集合操作：基於集合的方案通常優於游標/臨時表
22. 存儲過程設置 SET NOCOUNT ON：減少網絡往返
23. 避免大事務：提升並發能力
24. 控制返回數據量：大數據量考慮分頁或流式處理
25. PreparedStatement 替代 Statement：預編譯提升性能，防 SQL 注入

實戰案例：百萬級數據刪除不鎖表

場景：線上網站需刪除歷史日誌，直接 DELETE 會鎖表導致服務不可用。

-- 批量刪除模板（MySQL）
WHILE 1=1 DO
    DELETE FROM logs WHERE log_date <= '2012-11-01' LIMIT 1000;
    IF ROW_COUNT() = 0 THEN
        LEAVE;  -- 刪除完成退出
    END IF;
    DO SLEEP(0.05);  -- 釋放鎖，讓其他線程可訪問
END WHILE;

關鍵點：

• LIMIT 1000 控制單次事務行數，縮短鎖持有時間
• SLEEP/USLEEP 主動讓出 CPU，允許其他查詢插入
• 同理適用於大批量 INSERT、UPDATE

總結

數據庫優化是系統工程，需結合業務場景、數據分佈、硬件資源綜合考量。上述 31 條為通用原則，具體執行時請務必 EXPLAIN 實測、壓測驗證、灰度發布。