设计与优化索引
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
聚集索引
根索引页 根索引页
➢ 可以使得索引键变化引起的波 动更小
➢ 索引中的数据列越少，数据分 布的统计维护的成本就越小
24
ID Name att1 att2 att3 att4 att5 att6 att7 att8 att9
1
A
a
x
2
B
d
f
3
C
t
j
4
D
m
u
5
E
k
l
6
F
t
k
o
7G
w
Hale Waihona Puke Baidu
创建非簇索引，使用include包含FileName
ProductModelID上的簇或非簇索引
表之间连接
SELECT a.ProductAssemblyID, b.Name, a.PerAssemblyQty FROM Production.BillOfMaterials AS a JOIN Production.Product AS b ON a.ProductAssemblyID = b.ProductID WHERE b.ProductID = 900;
id index_id>=2
Index Pages
叶子节点 包含行编号
root_page
id index_id=0 数据页面
堆
根索引页面
id index_id>=2
索引页
叶子节点 包含键值
root_page
Clustering Key
id index_id=1
root_page
包含数据的索引页
1
A
a
x
2
B
d
f
3
C
t
j
4
D
m
u
5
E
k
l
6
F
t
k
o
7G
w
8
H
h
u
9
I
b
CREATE INDEX IX_A ON T(att1) WHERE Name = ‘A’ OR Name = ‘E’ SELECT Name FROM T WHERE att1 = ‘a’
CREATE INDEX IX_A ON T(att4) WHERE Name = ‘C’ OR Name = ‘G’ SELECT Name FROM T WHERE att4 = ‘t’
精确匹配值到一个in(X,Y,Z) SELECT EmployeeID, Title
EmployeeID上的簇或非簇索引
列表
FROM HumanResources.Employee
WHERE EmployeeID IN (288, 30, 15);
一组范围值
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID BETWEEN 1 and 5; - or WHERE ProductModelID >= 1 AND ProductModelID <= 5
在ProductID 和 ProductAssemblyID上的簇或非簇索 引
Like运算符
SELECT CountryRegionCode, Name FROM Person.CountryRegion WHERE Name LIKE N'D%'
Name上的簇或非簇索引
排序和聚合 主键或唯一值约束 级联更新或级联删除
查询
SELECT a.WorkOrderID, b.ProductID, a.OrderQty, a.DueDate FROM Production.WorkOrder AS a JOIN Production.WorkOrderRouting AS b ON a.WorkOrderID = b.WorkOrderID ORDER BY a.WorkOrderID;
表扫描
SQL Server 读取所有的表页面
索引
SQL Server 使用索引也来查找行
• 表中的页面是没有顺序的 • 每一页中德行是没有顺序的 • 插入的数据和修改的数据会放在表的任意位置
id index_id=0
first_iam_page
IAM Page
堆
数据页面
当行被移动以后，会在原来的位置保留一个转向指针
在定义约束的字段上创建簇或非簇索引
在外键上加簇或非簇索引
在查询的列表，但不是谓词的 字段
SELECT Title, Revision, FileName FROM Production.Document WHERE Title LIKE N'%Maintenance%' AND Revision >= 0';
8
H
h
u
9
I
b
CREATE INDEX IX_A ON T(att1) WHERE Name = ‘A’ OR Name = ‘E’ SELECT Name FROM T WHERE att1 = ‘a’
25
ID Name att1 att2 att3 att4 att5 att6 att7 att8 att9
• 表页面按照逻辑顺序保存 • 表页面中的行按照逻辑顺序保存 • 一个表只能有一个聚集索引
id index_id=1
root_page
Root Index Page
索引页 数据页
• 聚集索引可以是唯一或者不唯一的 • SQL Server 必须能够识别每一行
• 当非唯一的情况下需要使用4字节的标识符 • 总是保证聚集索引是唯一的
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate) VALUES ('OZ1', 'OuncesTest', GetDate());
CASCADE选项
考虑添加的索引
在聚合列或排序列上添加簇或非簇索引 在排序上 在排序列上考虑使用ASC或DESC
26
工作载荷
数据库引擎 优化顾问
数据库和数据库对象
报表和建议
34
查询
考虑添加的索引
精确匹配特定值
SELECT EmployeeID, Title FROM HumanResources.Employee WHERE EmployeeID = 228;
EmployeeID上的簇或非簇索引
根索引页 根索引页
➢ 可以使得索引键变化引起的波 动更小
➢ 索引中的数据列越少，数据分 布的统计维护的成本就越小
24
ID Name att1 att2 att3 att4 att5 att6 att7 att8 att9
1
A
a
x
2
B
d
f
3
C
t
j
4
D
m
u
5
E
k
l
6
F
t
k
o
7G
w
Hale Waihona Puke Baidu
创建非簇索引，使用include包含FileName
ProductModelID上的簇或非簇索引
表之间连接
SELECT a.ProductAssemblyID, b.Name, a.PerAssemblyQty FROM Production.BillOfMaterials AS a JOIN Production.Product AS b ON a.ProductAssemblyID = b.ProductID WHERE b.ProductID = 900;
id index_id>=2
Index Pages
叶子节点 包含行编号
root_page
id index_id=0 数据页面
堆
根索引页面
id index_id>=2
索引页
叶子节点 包含键值
root_page
Clustering Key
id index_id=1
root_page
包含数据的索引页
1
A
a
x
2
B
d
f
3
C
t
j
4
D
m
u
5
E
k
l
6
F
t
k
o
7G
w
8
H
h
u
9
I
b
CREATE INDEX IX_A ON T(att1) WHERE Name = ‘A’ OR Name = ‘E’ SELECT Name FROM T WHERE att1 = ‘a’
CREATE INDEX IX_A ON T(att4) WHERE Name = ‘C’ OR Name = ‘G’ SELECT Name FROM T WHERE att4 = ‘t’
精确匹配值到一个in(X,Y,Z) SELECT EmployeeID, Title
EmployeeID上的簇或非簇索引
列表
FROM HumanResources.Employee
WHERE EmployeeID IN (288, 30, 15);
一组范围值
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID BETWEEN 1 and 5; - or WHERE ProductModelID >= 1 AND ProductModelID <= 5
在ProductID 和 ProductAssemblyID上的簇或非簇索 引
Like运算符
SELECT CountryRegionCode, Name FROM Person.CountryRegion WHERE Name LIKE N'D%'
Name上的簇或非簇索引
排序和聚合 主键或唯一值约束 级联更新或级联删除
查询
SELECT a.WorkOrderID, b.ProductID, a.OrderQty, a.DueDate FROM Production.WorkOrder AS a JOIN Production.WorkOrderRouting AS b ON a.WorkOrderID = b.WorkOrderID ORDER BY a.WorkOrderID;
表扫描
SQL Server 读取所有的表页面
索引
SQL Server 使用索引也来查找行
• 表中的页面是没有顺序的 • 每一页中德行是没有顺序的 • 插入的数据和修改的数据会放在表的任意位置
id index_id=0
first_iam_page
IAM Page
堆
数据页面
当行被移动以后，会在原来的位置保留一个转向指针
在定义约束的字段上创建簇或非簇索引
在外键上加簇或非簇索引
在查询的列表，但不是谓词的 字段
SELECT Title, Revision, FileName FROM Production.Document WHERE Title LIKE N'%Maintenance%' AND Revision >= 0';
8
H
h
u
9
I
b
CREATE INDEX IX_A ON T(att1) WHERE Name = ‘A’ OR Name = ‘E’ SELECT Name FROM T WHERE att1 = ‘a’
25
ID Name att1 att2 att3 att4 att5 att6 att7 att8 att9
• 表页面按照逻辑顺序保存 • 表页面中的行按照逻辑顺序保存 • 一个表只能有一个聚集索引
id index_id=1
root_page
Root Index Page
索引页 数据页
• 聚集索引可以是唯一或者不唯一的 • SQL Server 必须能够识别每一行
• 当非唯一的情况下需要使用4字节的标识符 • 总是保证聚集索引是唯一的
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate) VALUES ('OZ1', 'OuncesTest', GetDate());
CASCADE选项
考虑添加的索引
在聚合列或排序列上添加簇或非簇索引 在排序上 在排序列上考虑使用ASC或DESC
26
工作载荷
数据库引擎 优化顾问
数据库和数据库对象
报表和建议
34
查询
考虑添加的索引
精确匹配特定值
SELECT EmployeeID, Title FROM HumanResources.Employee WHERE EmployeeID = 228;
EmployeeID上的簇或非簇索引