動態網頁資料庫優化十大原則：從索引、連線池到 SQL 重寫

前言

資料庫是動態網站的心臟，也是效能瓶頸的高發區。本文針對 ASP/ASP.NET/PHP 等動態網頁架構，整理 10 大經實戰驗證的優化原則，涵蓋索引、存儲過程、連線管理、SQL 寫法、鎖策略等核心面向。

原則 1：合理建立索引 — 效能倍增器

• 針對 WHERE、JOIN、ORDER BY、GROUP BY 欄位建立索引
• 複合索引遵循最左前綴，(a,b,c) 支援 a/a,b/a,b,c
• 避免過度索引：每張表 5-8 個以內，寫入密集表更少
• 定期 ANALYZE TABLE / UPDATE STATISTICS 更新統計資訊

原則 2：優先使用存儲過程 — 減少網路往返、預編譯優勢

• 商業邏輯封裝於 DB 端，EXEC usp_GetUser @UserID 取代多條 SQL
• 減少應用伺服器與 DB 間的網路封包數
• 預編譯計畫重用，降低 CPU 解析開銷
• 僅回傳必要欄位 (SELECT col1, col2 而非 SELECT *)

原則 3：選對資料庫引擎 — SQL Server vs Access

• Access (Jet/ACE)：檔案式、單機、併發極差 (>10 連線即崩潰)，僅適合桌面輕量應用
• SQL Server / MySQL / PostgreSQL：Client-Server 架構、行級鎖、交易、併發支撐千萬級
• 連線字串使用 OLEDB / 原生驅動 (如 SQLOLEDB、MySQL ODBC 8.0)，避免 DSN 額外層級

原則 4：使用正確的資料存取技術 — ADO.NET / PDO / mysqli

• 淘汰 DAO / RDO / ADO (Classic)：單執行緒、COM 介面、效能差
• 採用 ADO.NET (SqlClient)、PDO (PHP)、mysqli：連線池、非同步、參數化查詢、預處理
• 參數化查詢防 SQL Injection：cmd.Parameters.AddWithValue("@id", id)

原則 5：精確設定游標類型與鎖模式

原則 6：減少物件存取開銷 — 區域變數快取欄位值

反模式：

For i = 0 To rs.Fields.Count - 1
    Response.Write rs.Fields(i).Value  ' 每次存取 COM 介面
Next

優化：

Dim vals() : vals = rs.GetRows()  ' 一次取回二維陣列
For i = 0 To UBound(vals, 2)
    Response.Write vals(0, i) & "," & vals(1, i)
Next

或逐欄位存入區域變數：Dim name : name = rs("name")。

原則 7：不要快取 Connection 物件於 Application/Session

• Application 級：單一連線遭所有請求搶佔，嚴重阻塞
• Session 級：每用戶一連線，耗盡連線池、DB 端壓力大
• 最佳實務：每頁面/請求 取用 → 用畢 歸還，依賴 IIS/驅動內建連線池 (ODBC/OLEDB/ADO.NET 皆支援)
• ASP.NET：using (var conn = new SqlConnection(cs)) { conn.Open(); ... } 自動歸還池

原則 8：拒絕 SELECT * — 只取所需

• 減少網路傳輸、記憶體佔用、緩衝池壓力
• 利用覆蓋索引 (索引含所有查詢欄位) 避免回表
• Schema 變更時不易破壞相容性

原則 9：及時關閉/釋放資源 — 防洩漏

-- Classic ASP / VBScript
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing

-- ASP.NET / C#
using (var conn = new SqlConnection(cs)) {
    using (var cmd = new SqlCommand(sql, conn)) {
        using (var rdr = cmd.ExecuteReader()) { ... }
    }
} -- 自動 Dispose，即使發生例外

未釋放會導致連線池耗盡、DB 端閒置連線堆積、最終服務掛掉。

原則 10：OLEDB 直連 / 無 DSN 連線字串 — 部署彈性與效能

SQL Server (SQLOLEDB / SQLNCLI)

Provider=SQLOLEDB.1;Data Source=192.168.1.100;Initial Catalog=MyDB;User ID=app;Password=secret;Connect Timeout=15;

MySQL (MySQL ODBC 8.0 Unicode Driver)

Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.1.100;Database=MyDB;Uid=app;Pwd=secret;Option=3;

優勢：繞過 ODBC 管理層、免 DSN 設定、移植性強、連線建立較快。

附錄：OLEDB vs ODBC 效能基準參考 (Wrox ADO 2.0 測試)

總結

資料庫優化無銀彈，但這 10 大原則涵蓋了 90% 的常見效能坑。請從索引 → 存儲過程 → 連線池 → SQL 重寫 → 監控慢查詢 依序落地，並建立效能基線 → 壓測 → 優化 → 回歸 的持續改善循環。