大數據高並發資料庫優化全景指南：設計、查詢、演算法、索引四大維度

前言：設計先行，補丁最貴

系統上線後才發現效能問題，修復成本是設計階段的 10-100 倍。必須在高並發、大數據場景下預判瓶頸，繪製資料流向圖分析。

一、資料庫結構設計

範式與反範式的平衡

• 邏輯設計：高範式（3NF/BCNF），減少冗余，保證一致性
• 物理設計：適度反範式化，針對高頻連接查詢引入冗余欄位，減少 JOIN
• 案例：用戶地區資訊冗余到訂單表，避免每次 JOIN 地區表

主鍵選擇原則

• 避免自增 ID 作為主鍵並對外暴露關聯：遷移/恢復時極難處理（映射關係丟失）
• 業務鍵（如 UserID）作為主鍵，自增僅作內部叢集索引
• 大表拆分：核心欄位（ID、Name、Password）獨立表，其他屬性垂直拆分

欄位型別與長度優化

1. 行長度 ≤ 8020 位元組，否則跨頁造成碎片
2. 優先用數字型別（電話號碼用 bigint），字串比較逐字元，數字單次比較
3. CHAR 固定長、查詢快、佔空間；VARCHAR 變長、省空間、略慢。
   → 固定長欄位（密碼、代碼）用 CHAR，變長（評論、描述）用 VARCHAR
4. 長度設定：滿足業務最大值前提下，越短越好（索引更小、查詢更快）

二、查詢優化核心 25 條

SARG 原則：讓優化器用上索引

SARG (Search ARGument) 形式：欄位 運算子 常數/變數 或 常數/變數 運算子 欄位。
違反 SARG → 全表掃描。

具體規則

1. 避免 WHERE 欄位 IS NULL → 設預設值 0，查 num=0
2. 避免 != / <> → 優化器無法估算命中行數
3. 避免 OR 連接條件 → 改 UNION ALL 分開查
4. 慎用 IN / NOT IN → 連續值用 BETWEEN
5. 避免前導萬用字元：LIKE '%L%' 無法用索引，LIKE 'L%' 可用
6. 變數參數導致全掃 → 強制索引：WITH(INDEX(idx_name))
7. 避免 WHERE 欄位做運算/函數 → 移至等號右側
   F1/2=100 → F1=200
SUBSTRING(CARD_NO,1,4)='5378' → CARD_NO LIKE '5378%'
DATEDIFF(yy,dob,GETDATE())>21 → dob < DATEADD(yy,-21,GETDATE())
8. 複合索引必須用到前導欄，欄位順序與索引順序一致
9. EXISTS 替代 IN，NOT EXISTS 替代 NOT IN（子查詢不產生大量鎖定掃描）
10. 表變數替代臨時表（大量資料時注意僅有 PK 索引）
11. 避免頻繁建/刪臨時表，一次性大量插入用 SELECT INTO，小量用 CREATE TABLE + INSERT
12. 程序結束顯式清理臨時表：TRUNCATE TABLE → DROP TABLE
13. 程序/觸發器開頭 SET NOCOUNT ON，減少 DONE_IN_PROC 訊息
14. 避免大事務，提高並發
15. 避免回傳大結果集，評估需求合理性
16. 避免不相容型別比較：float vs int、char vs varchar、binary vs varbinary → 程式端轉型
17. 充分利用連接條件：多寫連接條件可大幅加速
18. 視圖加速重複查詢：預先排序、篩選，減少 I/O
19. DISTINCT 替代 GROUP BY（單純去重時）
20. UNION ALL 替代 UNION（無需去重時）
21. 避免 SELECT INTO（導致表鎖定）
22. 驗證工具：查詢分析器 Ctrl+L 看執行計畫，比較索引使用、掃描次數、成本百分比

三、演算法優化

• 避免游標：>1萬行必改寫。優先尋找集合解法
• 小資料集 FAST_FORWARD 游標可接受
• 「合計」類需求用 ROLLUP/CUBE 或視窗函數，比游標快
• 不得已用游標時：先篩選符合條件資料到臨時表，再對臨時表開游標
• 封裝邏輯到預存程序，減少網路往返

四、高效索引策略

叢集 vs 非叢集選擇表

三大迷思破解

1. 主鍵 ≠ 叢集索引：自增 ID 做叢集是浪費（查詢不按 ID、值唯一違反「少量不同值」原則）。叢集應給範圍查詢高頻欄位（如日期）
2. 建索引 ≠ 快：同欄位，叢集索引比非叢集快數量級（範圍查詢只找首尾 vs 逐行書籤查找）
3. 複合叢集索引：前導欄必須是最高頻查詢欄位。僅用非前導欄查詢 → 索引失效

填充因子

頻繁寫入的表，設較小 Fill Factor（如 70-80%），預留頁面空間減少分頁。

總結

大數據高並發優化是系統工程：設計留冗余、查詢守 SARG、演算法去游標、索引給範圍查詢欄位。持續監控、持續調優。