萬盛學電腦網

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

SQL 查詢優化九大鐵律:從掃描到尋找、從全表到索引

優化核心原則

**減少 I/O、減少 CPU、減少鎖爭用、利用索引、避免排序/雜湊溢寫**。每條 SQL 提交都會經歷解析→綁定→優化→執行,優化器依賴統計資訊與索引做決策。

九大鐵律詳解

  1. 按需選欄,拒絕 SELECT *
    -- 差
    SELECT * FROM Orders WHERE CustomerID = 1001;
    -- 優
    SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 1001;

    減少網路傳輸、緩衝池佔用、覆蓋索引機會、觸發器/計算欄位開銷。每少一欄位,速度提升與欄位大小成正比。

  2. 存在性判斷用 EXISTS,不用 COUNT(*)
    -- 差
    IF (SELECT COUNT(*) FROM Orders WHERE CustomerID = 1001) > 0
    -- 優
    IF EXISTS (SELECT 1 FROM Orders WHERE CustomerID = 1001)

    EXISTS 支援**短路**:找到第一筆即停止;COUNT 必須掃描所有匹配行。

  3. NOT IN 改 NOT EXISTS / LEFT JOIN ... IS NULL
    -- 差 (NULL 陷阱 + 效能差)
    SELECT * FROM Dept WHERE DeptNo NOT IN (SELECT DeptNo FROM Emp);
    -- 優
    SELECT * FROM Dept d WHERE NOT EXISTS (SELECT 1 FROM Emp e WHERE e.DeptNo = d.DeptNo);
    -- 或
    SELECT d.* FROM Dept d LEFT JOIN Emp e ON d.DeptNo = e.DeptNo WHERE e.DeptNo IS NULL;

    NOT IN 遇到子查詢含 NULL 會返回空結果集;NOT EXISTS/LEFT JOIN 語意正確且可用索引。

  4. OR 改寫為 UNION ALL / IN / 範圍查詢
    -- 差:導致全表掃描
    SELECT * FROM Orders WHERE CustomerID = 1001 OR SalesPersonID = 5;
    -- 優:分開查詢合併 (注意去重)
    SELECT * FROM Orders WHERE CustomerID = 1001
    UNION ALL
    SELECT * FROM Orders WHERE SalesPersonID = 5 AND CustomerID <> 1001;

    OR 通常阻礙索引搜尋 (Index Seek),改寫後各分支可獨立用索引。

  5. WHERE 條件順序遵循「索引順序、選擇性高優先」
    • 複合索引 (A, B, C) → WHERE 條件順序盡量 A = ? AND B > ? AND C = ?。
    • 等值條件 (=, IN) 先於範圍條件 (>, LIKE 'prefix%')。
    • 高選擇性 (過濾行數少) 條件前置。
  6. 用 >= 代替 > (整數欄位)
    -- 差
    WHERE ID > 1000
    -- 優
    WHERE ID >= 1001

    某些優化器對 >= 能更精確估算範圍行數,並可能觸發索引搜尋而非掃描。

  7. 熟知索引結構,避免導致索引失效的寫法
    • ❌ 函數/運算在索引欄上:WHERE YEAR(OrderDate) = 2024 → WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'。
    • ❌ 隱含轉換:WHERE VarcharCol = 123 → WHERE VarcharCol = '123'。
    • ❌ IS NULL, <>, !=, NOT, NOT IN, NOT LIKE 通常導致掃描 (除非有篩選索引/計算欄索引)。
    • ❌ 前導萬用字元 LIKE '%abc' → 全掃或全文索引。
  8. 運算/函數移至等號右側 (SARGable 原則)
    -- 差
    WHERE SUBSTRING(ID,1,1) = 'A'
    WHERE Amount * 1.05 > 1000
    -- 優
    WHERE ID LIKE 'A%'
    WHERE Amount > 1000 / 1.05

    使優化器能直接對索引欄位進行搜尋 (Seek)。

  9. 善用覆蓋索引與 INCLUDE 欄位
    CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date_Cover
      ON Orders (CustomerID, OrderDate DESC)
      INCLUDE (TotalAmount, Status);  -- 非鍵欄位,不計入索引樹深度

    查詢 SELECT TotalAmount, Status FROM Orders WHERE CustomerID = ? ORDER BY OrderDate DESC 可**完全在索引層完成**,避免回表 (Key Lookup/RID Lookup)。

輔助工具與習慣

  • SET STATISTICS IO, TIME ON; 觀察邏輯讀次數、CPU/經過時間。
  • SET SHOWPLAN_XML ON; / SSMS「顯示實際執行計畫」分析運算子、估算/實際行數、缺失索引建議。
  • 定期 UPDATE STATISTICS / sp_updatestats 保持統計資訊新鮮。
  • 建立 命名規範:IX_<Table>_<KeyCols>[_Include<Cols>]、UK_...、FK_...。

將九大鐵律內化為編碼肌肉記憶,配合持續監控與執行計畫分析,查詢效能將穩步提升。

copyright © 萬盛學電腦網 all rights reserved