索引管理的基本原則就是，「不要建立過多的索引」，因為這會拖慢寫入的速度，也會消耗額外的空間。

索引管理工具箱

Index Monitoring

1. 有人在用嗎？
   ↓
   dm_db_index_usage_stats

2. 有哪些 Index？
   ↓
   sp_helpindex
   sys.indexes

3. 缺 Index 嗎？
   ↓
   dm_db_missing_index_details

4. Stats 健康嗎？
   ↓
   dm_db_stats_properties

5. 更新 Stats
   ↓
   sp_updatestats

6. Fragmentation？
   ↓
   dm_db_index_physical_stats

Avoid over-indexing
Drop unused indexes
Update statistics weekly
Reorganize & rebuild indexes weekly
Partition large table

執行計劃(Execution Plan)

Execution plan 其實就是 SQL Server 為了回答 Query，做了哪些事情？

可以用以下的心智圖去理解。

Execution Plan

├─ 1. 資料從哪裡來？
├─ 2. 怎麼取得缺少的欄位？
├─ 3. 怎麼 Join？
├─ 4. 怎麼 Aggregate？
├─ 5. 怎麼排序？
└─ 6. 哪裡最容易出問題？

1. 資料從哪裡來？

首先先分成兩大類：

找資料

├─ Seek
│   └─ 精準找
|    e.g. Index Seek, Clustered Index Seek
│
└─ Scan
    └─ 全部看
    e.g. Table Scan, Clustered/Non-clustered Index Scan

Table Scan

Full Table Scan 的意思是：資料庫從第一筆資料開始，一筆一筆檢查，直到最後一筆。

這就是最笨的方式，當資料表沒有適用的索引，或優化器判斷使用索引不划算時，就會出現 Full Table Scan。

Clustered/Non-clustered Index Scan

雖然你有建立 clustered index，但是如果 query 就是要對全表掃描，像是

SELECT COUNT(*)  
FROM FactSales;

SQL server就會掃描整棵 clustered index：

Root
 ↓
Intermediate
 ↓
Leaf Pages
 ↓
全部掃描

Index Seek

Index Seek 則代表資料庫可以透過索引直接「跳到」符合條件的範圍。只要查詢條件能符合索引的設計（例如使用索引的前導欄位），資料庫就有機會使用 Index Seek。

2. 怎麼取得缺少的欄位？

Key lookup

Key Lookup 則是發生在「已經使用索引找到資料位置，但索引裡缺少部分欄位」的情況。這個「回去補資料」的動作，在執行計畫中就是 Key Lookup。

舉一個具體例子。假設我們有一張 MemberOrders 表，並且建立了以下索引：

CREATE NONCLUSTERED INDEX IX_MemberOrders_Phone
ON dbo.MemberOrders (Phone);

現在有一段查詢：

SELECT MemberId, CreatedAt -- SELECT 清單中的欄位不在索引中
FROM dbo.MemberOrders
WHERE Phone = @Phone;

在這個情境中，索引中只有 Phone，並沒有 MemberId 與 CreatedAt。資料庫會先透過 Index Seek 快速找到所有符合 Phone = @Phone 的資料列位置，看起來很有效率。

但接下來問題出現了。因為索引中沒有 MemberId 與 CreatedAt，資料庫只能根據索引裡的 Row Locator，一筆一筆回到資料表，把這兩個欄位讀出來。這個過程就是 Key Lookup。

如果只找到一筆資料，這個動作幾乎感覺不到成本；但如果同一個電話對應到數百筆訂單，資料庫就必須做數百次回表讀取。這種「大量小次數的隨機讀取」通常比一次連續掃描更昂貴，因此效能會明顯下降。

Covering Index

要解決這個問題，可以把查詢需要回傳的欄位加入索引。例如改成：

CREATE NONCLUSTERED INDEX IX_MemberOrders_Phone
ON dbo.MemberOrders (Phone)
INCLUDE (MemberId, CreatedAt); -- 涵蓋 SELECT 清單中的欄位

此時索引的葉節點除了 Phone 之外，也儲存了 MemberId 與 CreatedAt。當查詢再次執行時，資料庫在索引中就已經取得所有需要的欄位，不必再回到資料表讀資料，也就不會出現 Key Lookup。

這種「索引本身就包含查詢所需全部欄位」的設計，稱為涵蓋索引（covering index）。涵蓋索引讓查詢可以在單一結構內完成，避免額外 I/O。

3. 怎麼 Join ?

Join

├─ Nested Loop
│   └─ 小資料
│
├─ Hash Match
│   └─ 大資料
│
└─ Merge Join
    └─ 已排序

Nested Loop

就像是：外層一筆內層找一次

# 可以想成是這樣：
for each row in A:
	去 B 找 match

SELECT *
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID

如果Customers夠小而且Orders.CustomerID有index的情況下通常才可以接受。

Hash Match

就像是：先建 Hash Table，再比對。

Step 1. Build phase (用比較小的表)
把 table A 做成 hash table

Step 2. Probe phase
掃描 table B，去 hash table 找 match，每次 O(1)

如果兩張表沒有建立index，optimizer可能會選擇這個做法。

Merge Join

就像是：兩邊都排序好，一起往前走，適合兩邊都已經排序好的情況。

4. 怎麼 Aggregate？

Aggregate

├─ Stream Aggregate
│   └─ 當資料已排序
│
└─ Hash Match (Aggregate)
    └─ 未排序先 Hash

統整

Execution Plan

├─ 找資料
│
│  ├─ Index Seek
│  ├─ Clustered Index Seek
│  ├─ Index Scan
│  ├─ Clustered Index Scan
│  └─ Table Scan
│
├─ 回表
│
│  ├─ Key Lookup
│  └─ RID Lookup
│
├─ Join
│
│  ├─ Nested Loop
│  ├─ Hash Match
│  └─ Merge Join
│
├─ Aggregate
│
│  ├─ Stream Aggregate
│  └─ Hash Aggregate
│
├─ 排序
│
│  └─ Sort
│
└─ 危險訊號
    │
    ├─ Key Lookup 可能導致I/O大幅增加
    ├─ Sort Spill
    ├─ Hash Spill
    ├─ Table Scan
    └─ Clustered Index Scan

Performance Tips

Always check execution plan to confirm performance improvements

注意 SQL query 對效能的影響

不要使用不必要的 DISTINCT 和 ORDER BY，排序和去重複都很花計算資源
當只是想看看資料的時候，善用 TOP 10
對於常常出現在WHERE用來篩選的欄位，或是用在JOIN condition 的欄位，可以考慮加上 non-clustered index

避免在 WHERE 條件中對欄位套用函數（Function on Column），因為通常會讓索引失效（Non-SARGable）

-- 不佳
WHERE YEAR(OrderDate) = 2024
-- 較佳
WHERE OrderDate BETWEEN '2024-01-01'
                AND '2024-12-31'

-- 不佳
WHERE SUBSTRING(CustomerCode, 1, 3) = 'ABC'
-- 較佳
WHERE CustomerCode LIKE 'ABC%'

Wildcard 開頭的字串會讓索引難以使用，因為資料庫不知道字串開頭是什麼，只能逐筆掃描
```
-- 不佳
WHERE Name LIKE '%Smith'
```

JOIN

INNER JOIN因為集合最小，所以表現最好

兩張表JOIN時，如果有需要篩選資料，有三個時機：

Filter after JOIN

SELECT *
FROM Orders o
JOIN Customers c
    ON o.CustomerID = c.CustomerID
WHERE c.Country = 'TW'; -- 把條件放在 WHERE

Filter during JOIN

SELECT *
FROM Orders o
JOIN Customers c
    ON o.CustomerID = c.CustomerID
   AND c.Country = 'TW'; -- 把條件直接放在 ON 裡面

Filter before JOIN

SELECT *
FROM Orders o
JOIN (
	-- 先用 subquery 篩選表後才 JOIN
    SELECT *
    FROM Customers
    WHERE Country = 'TW'
) c
ON o.CustomerID = c.CustomerID;

雖然 optimizer 可能很聰明，但如果不做太多假設的話，先讓 Filter 盡可能早發生是最好的做法，也就是 filter before JOIN。

避免在 JOIN 條件中使用 OR，優先考慮拆成多個查詢再用 UNION ALL，因為 OR 常使 Join Predicate 變得不 SARGable，降低 Optimizer 使用索引的能力

-- 較差
JOIN B
  ON A.Id = B.Id
  OR A.Email = B.Email
  
-- 較佳
SELECT ...
FROM A JOIN B ON A.Id = B.Id
 
UNION ALL
 
SELECT ...
FROM A JOIN B ON A.Email = B.Email

JOIN vs EXISTS vs IN

-- JOIN
SELECT c.*
FROM Customers c
JOIN Orders o
  ON c.CustomerId = o.CustomerId

-- EXISTS
SELECT *
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
)

-- IN
SELECT *
FROM Customers
WHERE CustomerId IN (
    SELECT CustomerId
    FROM Orders
)

IN 沒有 early-stop的機制，他會處理所有的 rows，所以效能最差，通常 EXISTS和JOIN效能差不多。

Lai's Blog

探索

資料庫效能優化3——索引管理、執行計劃、效能優化