您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL 語句效能調校深度指南：索引設計、查詢重寫、子查詢優化與實戰案例

編輯：MySQL 資料庫相關知識

一、資料庫設計與規劃

主鍵長度極小化：能用 SMALLINT 別用 INT；員工編號優於身分證字號
一般欄位同理：資料量 < 3 萬筆用 SMALLINT
固定長度字串用 CHAR/NCHAR：如身分證字號
變動長度字串用 VARCHAR/NVARCHAR：如地址，節省空間且提升 I/O 效率
給預設值並設 NOT NULL：NULL 儲存與查詢需額外運算
欄位過多時垂直切割：以同名 PK 一對多關聯，避免叢集索引掃描載入過多資料

二、適當建立索引

Foreign Key 必建索引
常用於 WHERE、ORDER BY、GROUP BY 的欄位建索引
索引欄位長度 ≤ 20 Bytes（避免長字串）
高選擇性（低重複率）欄位適合建索引（如姓名），低選擇性不適合（如性別）
低使用率欄位勿建索引
OLTP 系統避免過多索引（影響 INSERT/UPDATE/DELETE）
小資料表不必建索引（全表掃描可能更快）
大量符合條件時，非叢集索引可能不如全表掃描
叢集索引欄位選擇關鍵：整數、唯一、NOT NULL

三、正確使用索引

LIKE 模糊查詢

SELECT * FROM Orders WHERE CustomerID LIKE 'D%';  -- 使用索引
SELECT * FROM Orders WHERE CustomerID LIKE '%D';  -- 不使用索引

SQL Server 2005+ 可按 Ctrl+L 檢視執行計畫驗證。

負向運算子避免使用

NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN、NOT LIKE 易導致無法使用索引，建議改寫。

WHERE 欄位避免運算

-- 差：函數運算導致索引失效
SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996;

-- 優：範圍查詢利用索引
SELECT * FROM Orders WHERE OrderDate BETWEEN '19960101' AND '19961231';

-- 差：字串運算
SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D';

-- 優：LIKE 前綴匹配
SELECT * FROM Orders WHERE CustomerID LIKE 'D%';

叢集索引欄位優勢

資料依叢集索引順序儲存，包含該欄位的 BETWEEN、GROUP BY、ORDER BY 可提速。

複合索引順序

複合索引第一欄位才適合作為過濾條件。

四、避免 WHERE 子句中對欄位使用函數

函數運算等同於運算/串接，可能導致索引失效。更嚴重的是：10 萬筆資料需呼叫函數 10 萬次，系統上線後效能問題逐漸浮現。

五、AND 與 OR 使用策略

AND：只要一個條件用到索引即可提速

SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800; -- 使用索引

OR：所有條件都需有可用索引才能使用索引

SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800; -- 全表掃描

UNION 改寫 OR 查詢

SELECT * FROM Orders WHERE CustomerID='VINET'
UNION
SELECT * FROM Orders WHERE Freight=32.3800;

上半段用索引，下半段全表掃描，仍有改善。

六、謹慎使用子查詢

子查詢類型

獨立子查詢：可單獨執行
相關子查詢：外層每次查詢需引用內層，或內層每次需參考外層

極端效能差異案例

為 830 筆資料產生流水號：

-- ROW_NUMBER() 窗口函數：2 ms
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 編號 FROM dbo.Orders;

-- 傳統相關子查詢：170 ms（830 次全表掃描）
SELECT OrderID,
       (SELECT COUNT(*) FROM dbo.Orders 內圈 WHERE 內圈.OrderID <= 外圈.OrderID) AS 編號
FROM dbo.Orders 外圈 ORDER BY 編號;

31,465 筆資料：