SQL 優化核心策略

偽代碼示例，現實比這個復雜

1. 子查詢優化

(1) 避免低效的?IN?和?NOT IN

• 問題：
  NOT IN?可能導致全表掃描，尤其是子查詢結果集較大時。

• 優化方案：

  • 替換為?LEFT JOIN：

    -- 原查詢（低效）
    SELECT * FROM table_a 
    WHERE id NOT IN (SELECT id FROM table_b);-- 優化后
    SELECT a.* 
    FROM table_a a
    LEFT JOIN table_b b ON a.id = b.id
    WHERE b.id IS NULL;

  • 適用場景：
    子查詢結果集較大，且關聯字段有索引。

(2) 優先使用?EXISTS?而非?IN

• 優勢：
  EXISTS?在找到第一條匹配后終止掃描，效率更高。

• 示例：

  -- 低效（子查詢結果集大時）
  SELECT * FROM users 
  WHERE id IN (SELECT user_id FROM orders);-- 高效
  SELECT * FROM users u
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

---

2. JOIN 優化

(1) 減少?DISTINCT，改用?GROUP BY

• 問題：
  DISTINCT?可能導致全表排序和去重，內存消耗大。

• 優化方案：

  -- 低效
  SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合）
  SELECT user_id, order_date 
  FROM orders 
  GROUP BY user_id, order_date;

(2) 避免關聯字段使用函數或操作符

• 問題：
  關聯字段的表達式（如?||、CONCAT）會導致索引失效。

• 優化示例：

  -- 低效
  SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合）
  SELECT user_id, order_date 
  FROM orders 
  GROUP BY user_id, order_date;

  -- 高效（直接字段匹配）
  SELECT * FROM table_a a
  JOIN table_b b ON a.ticket_no = b.ticket_no AND a.ticket_serial = b.ticket_serial;

---

3. 數據操作優化

(1) 增刪改寬表數據先創建臨時表

把先寫入后改的結果表的程序，改為一次性寫入，從而避免update操作鎖表

比如：

1.insert 結果表（大表）

2.update 結果表（大表）

改為：

insert 臨時表

update 臨時表

insert?結果表（大表）

復雜查詢改為：

1.insert 臨時表 1

2.insert 臨時表 2

3.insert 結果表 from 臨時表1 left join 臨時表2

把update ,delete結果表（大表）的語句延后執行，減少鎖表時間

比如：

1.update 或者 delete 結果表

2.許多待查詢的臨時表

3.insert 結果表

改為：

1.許多待查詢的臨時表

2.update 或者 delete 結果表

3.insert 寬表

(2) 類型轉換優化策略

核心原則：先篩選數據，后執行類型轉換

在 SQL 查詢中，優先通過原始字段類型完成數據篩選，將類型轉換操作推遲到最終結果處理階段。此策略可顯著減少需處理的數據量，提升性能。

優化優勢

1. 減少計算開銷

   • 僅對篩選后的結果進行類型轉換，避免對全表數據的冗余處理。

   • 示例：若從 100 萬行數據中篩選出 1 萬行，類型轉換操作量減少 99%。

2. 避免索引失效

   • 在?WHERE?或?JOIN?條件中對字段進行類型轉換（如?CAST(amount AS VARCHAR)）會導致索引失效，引發全表掃描。

   • 優化后：直接基于原字段類型（如數值型?amount）篩選，確保索引生效。

3. 降低內存與 IO 壓力

   • 大數據場景下，減少中間結果集的數據處理量，降低內存和磁盤 IO 負載。

具體策略

篩選階段保持字段原生類型，轉換放在最后

-- 先篩選，再轉換
SELECT id, CAST(created_at AS DATE) AS create_date  -- 轉換放在最后
FROM orders 
WHERE created_at >= '2023-01-01';           -- 用原生類型過濾

---

4. 表設計與維護

(1) 統一關聯字段類型

• 問題：
  字段類型不匹配（如?INT?vs?VARCHAR）會導致隱式轉換和性能下降。

• 優化方案：
  與上游協商統一字段類型

(2) 視圖轉結果表

• 場景：
  高頻查詢的復雜視圖（如報表接口）。

• 優化步驟：

  1. 將視圖轉為結果表：

     在查詢結果表之前 用存儲過程將結果寫入結果表，然后再進行查詢

  2. 查詢時直接查詢結果表，提升查詢效果

---

5. 內存與 IO 優化

(1) 合理使用臨時表

內存臨時表減少磁盤 IO，但需注意內存容量。

(2) 分頁查詢優化

• 避免?OFFSET?深度分頁：
  使用?WHERE?條件+游標方式（如基于時間或主鍵）。

-- 低效（OFFSET 100000）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;-- 高效（基于上次查詢的末尾 ID）
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 10;

6. 定期維護統計信息

更新表的統計信息（如?ANALYZE table），幫助優化器生成高效計劃。?