索引
改進 SQL Server 中的查詢效能
Dean Smith
Founder, Atamai Analytics
什麼是索引？
- 用於加速從資料表存取資料的結構
- 可快速定位資料，免掃描整個資料表
- 對含篩選條件的查詢有助於效能
- 套用在資料表欄位
- 通常由資料庫管理員新增
叢集與非叢集索引
叢集式索引（Clustered Index）
- 類比：字典
- 資料頁依索引欄位排序
- 每個資料表僅能有一個
- 加速搜尋操作
叢集與非叢集索引
叢集式索引（Clustered Index）
- 類比：字典
- 資料頁依索引欄位排序
- 每個資料表僅能有一個
- 加速搜尋操作
非叢集式索引（Non-clustered Index）
- 類比：教科書後方的索引
- 結構為有序的索引指標層，指向未排序的資料頁
- 一個資料表可有多個
- 改善插入與更新作業
叢集式索引：B-tree 結構
- 根節點（ROOT NODE）
- 分支節點（BRANCH NODES）
- 頁面節點（PAGE NODES）
叢集式索引：B-tree 結構
ROOT NODE:
A G O W
BRANCH NODES:
A B E F G H J K O P S T
PAGE NODES:
Page 1 Page 2 Page 3 Page 4
Index Column |... Index Column |... Index Column | ... Index Column | ...
A | ... E | ... I | ... M | ...
B | ... F | ... J | ... N | ...
C | ... G | ... K | ... O | ...
D | ... H | ... L | ... P | ...
... ... ... ...
無叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Page 1
CustomerID |...
ALFKI | ...
ANATR | ...
BLONP | ...
BSBEV | ...
...
無叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Page 1 Page 2
CustomerID |... CustomerID|...
ALFKI | ... FOLIG | ...
ANATR | ... FRANK | ...
BLONP | ... GALED | ...
BSBEV | ... GREAL | ...
... ...
無叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Page 1 Page 2 Page 3
CustomerID |... CustomerID|... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ...
ANATR | ... FRANK | ... LINOD | ...
BLONP | ... GALED | ... MEREP | ...
BSBEV | ... GREAL | ... MORGK | ...
... ... ...
無叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Page 1 Page 2 Page 3 Page 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ...
BSBEV | ... GREAL | ... MORGK | ...
... ... ... ...
具叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
ALFKI FOLIG OLDWO WOLZA
BRANCH NODES:
ALFKI BONAP DRACD FISSA FOLIG GALED LILAS NORTS OCEAN OLDWO QUICK WOLZA
PAGE NODES:
Page 1 Page 2 Page 3 Page 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
具叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
ALFKI BONAP DRACD FISSA FOLIG GALED LILAS NORTS OCEAN OLDWO QUICK WOLZA
PAGE NODES:
Page 1 Page 2 Page 3 Page 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
具叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Page 1 Page 2 Page 3 Page 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
具叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Page 4
CustomerID | ...
OCEAN | ...
PARIS | ...
PICCO | ...
QUICK | ...
...
具叢集索引的 Customers 資料表
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Page 4
CustomerID | ...
PARIS | ...
叢集式索引：範例
SET STATISTICS IO ON
SELECT *
FROM PlayerStats
WHERE Team = 'OKC'
PlayerStats 資料表未建立索引
Table 'PlayerStats'. ..., logical reads 12, ...
在 Team 上建立叢集式索引的 PlayerStats 資料表
Table 'PlayerStats'. ..., logical reads 2, ...
一起來練習吧！
改進 SQL Server 中的查詢效能
