索引
改进 SQL Server 中的查询性能
Dean Smith
Founder, Atamai Analytics
什么是索引？
- 一种提升表数据访问速度的结构
- 可快速定位数据，无需全表扫描
- 对含筛选条件的查询性能提升明显
- 应用于表列
- 通常由数据库管理员添加
聚集与非聚集索引
聚集索引（Clustered Index）
- 类比：字典
- 表数据页按索引列排序
- 每表仅允许一个
- 加速查找
聚集与非聚集索引
聚集索引（Clustered Index）
- 类比：字典
- 表数据页按索引列排序
- 每表仅一个
- 加速查找
非聚集索引（Non-clustered Index）
- 类比：教材末尾的索引
- 结构为有序索引指针层，指向无序的数据页
- 一张表可有多个
- 改善插入和更新性能
聚集索引：B 树结构
- 根节点
- 分支节点
- 页节点
聚集索引：B 树结构
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, ...
Passons à la pratique !
改进 SQL Server 中的查询性能
