创建和优化索引课件

第

6章

创建和优化索引第6章

创建和优化索引第

6章：创建和优化索引规划索引创建索引优化索引第6章：创建和优化索引规划索引第

1节：规划索引SQLServer如何访问数据堆聚集索引非聚集索引第1节：规划索引SQLServer如何访问数据SQLServer如何访问数据索引查找SQLServer读取所有表页SQLServer使用索引页查找行表扫描SQLServer2008引入了增强的全文索引SQLServer如何访问数据索引查找SQLServe堆

堆：无聚集索引的表页不按任何特定顺序存储堆idindex_id=0first_iam_pageIAM（索引分配映射）页：记录表有哪些页数据页堆页不按任何特定顺序存储聚集索引

每个表一个聚集索引B树以索引键的顺序存储数据页叶节点idindex_id=1root_page数据页中间级索引页根索引页聚集索引B树以索引键的顺序存储数据页非聚集索引

B树引用底层堆或聚集索引每个表最多249个非聚集索引堆或聚集索引idindex_id>1root_page数据页叶节点索引页根索引页非聚集索引每个表最多249个非聚集索引第

2节：创建索引创建索引概述唯一索引创建多列索引的注意事项何时创建计算列的索引已分区索引在索引中并入可用空间的选项获取索引信息的方法演示：创建索引

第2节：创建索引创建索引概述创建索引概述CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])INCLUDE(column[,...n])[WITHoption[,...n]][ON{partition_scheme(column)|filegroup|“default”}]使用

SQLServerManagementStudio或CREATEINDEXTransact-SQL语句创建索引概述CREATE[UNIQUE][CLUSTER唯一索引CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources].[Employee]([LoginID]ASC)EmployeeIDLoginIDGenderMaritalStatus…216mike0MS…231fukiko0MM…242pat0MS…291pat0FS……不允许重复键值唯一索引CREATEUNIQUENONCLUSTERED包含列创建多列索引的注意事项复合索引先定义唯一性最高的列键中最多包含16列和900字节索引中包含非键列CREATENONCLUSTEREDINDEXAK_Employee_LoginIDONHumanResources.Employee(LoginIDASC)INCLUDE(ContactID,NationalIDNumber)CREATENONCLUSTEREDINDEXK_Contact_LastName_FirstNameONPeson.Contact(LastNameASC,FirstNameASC)提高查询覆盖度和性能包含列创建多列索引的注意事项复合索引先定义唯一性最高的列何时创建计算列的索引以下情况下，可创建计算列的索引：NUMERIC_ROUNDABORT选项设置为

OFF

表达式是确定性的且精确的üANSI_NULLSconnection_level选项为

ONü

列的计算值不可为

text、ntext或

image数据类型ü

创建索引或更改造成索引更新时，必需的选项设置为

ONüü查询优化器可能忽略计算列的索引何时创建计算列的索引以下情况下，可创建计算列的索引：

索引按类似于已分区表的范围横向分区已分区索引考虑与底层表对齐并在索引键（唯一）中引入分区<20032003-20042004-2005>2005idindex_idpartition_numberhobt_id1216756756765722275675676665…………sys.partitions 索引按类似于已分区表的范围横向分区已分区索引在索引中并入可用空间的选项可用空间影响索引更新性能FILLFACTOR决定了叶节点的可用空间量

对于

OLTP应用程序使用较低的

FILLFACTOR

对于

OLAP应用程序使用较高的

FILLFACTORPAD_INDEX决定了非页索引节点上的可用空间量CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources].[Employee]([LoginID]ASC)WITHFILLFACTOR=65,PAD_INDEX=ON)在索引中并入可用空间的选项可用空间影响索引更新性能FILLF获取索引信息的方法SQLServerManagementStudio：

对象资源管理器“索引属性”窗口系统存储过程：

sp_help目录视图系统函数报告

sp_helpindex获取索引信息的方法SQLServerManagement演示：创建索引本演示中，你将看到如何：使用SQLServerManagementStudio创建索引使用SQLServerManagementStudio查看索引使用Transact-SQL创建索引演示：创建索引本演示中，你将看到如何：第

3节：优化索引数据库引擎优化顾问演示：使用数据库引擎优化顾问

索引碎片索引碎片整理选项演示：索引碎片整理

第3节：优化索引数据库引擎优化顾问数据库引擎优化顾问

分析数据库在工作负荷下的性能

图形界面和命令行界面报表和建议

工作负荷数据库引擎优化顾问数据库和数据库对象数据库引擎优化顾问分析数据库在工作负荷下的性能演示：使用数据库引擎优化顾问在本演示中，你将看到如何：使用Transact-SQL脚本分析AdventureWorks中的索引查看索引建议演示：使用数据库引擎优化顾问在本演示中，你将看到如何：索引碎片为何会出现碎片？修改数据时，SQLServer重新组织索引页–造成索引页拆分SQLServerManagementStudio–索引属性系统函数-sys.dm_db_index_physical_stats检测碎片内部–页未满外部–页的逻辑顺序失序碎片类型：索引碎片为何会出现碎片？修改数据时，SQLServer重索引碎片整理选项重新组织小于30%的碎片大于30%的碎片ALTERINDEXAK_Product_NameON

Production.ProductREORGANIZE重新生成ALTERINDEXAK_Product_NameON

Production.ProductREBUILD索引碎片整理选项重新组织小于30%的碎片大于30%演示：索引碎片整理在本演示中，你将看到如何：找出有碎片的索引通过重新生成索引去除碎片演示：索引碎片整理在本演示中，你将看到如何：实验：创建和优化索引实验6-1：创建索引

实验6-2：优化索引

登录信息虚拟机NY-SQL-01用户名Student密码Pa$$w0rd预估时间：45分钟实验：创建和优化索引实验6-1：创建索引登录信息虚拟机N第

6章

创建和优化索引第6章

创建和优化索引第

6章：创建和优化索引规划索引创建索引优化索引第6章：创建和优化索引规划索引第

1节：规划索引SQLServer如何访问数据堆聚集索引非聚集索引第1节：规划索引SQLServer如何访问数据SQLServer如何访问数据索引查找SQLServer读取所有表页SQLServer使用索引页查找行表扫描SQLServer2008引入了增强的全文索引SQLServer如何访问数据索引查找SQLServe堆

堆：无聚集索引的表页不按任何特定顺序存储堆idindex_id=0first_iam_pageIAM（索引分配映射）页：记录表有哪些页数据页堆页不按任何特定顺序存储聚集索引

每个表一个聚集索引B树以索引键的顺序存储数据页叶节点idindex_id=1root_page数据页中间级索引页根索引页聚集索引B树以索引键的顺序存储数据页非聚集索引

B树引用底层堆或聚集索引每个表最多249个非聚集索引堆或聚集索引idindex_id>1root_page数据页叶节点索引页根索引页非聚集索引每个表最多249个非聚集索引第

2节：创建索引创建索引概述唯一索引创建多列索引的注意事项何时创建计算列的索引已分区索引在索引中并入可用空间的选项获取索引信息的方法演示：创建索引

第2节：创建索引创建索引概述创建索引概述CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])INCLUDE(column[,...n])[WITHoption[,...n]][ON{partition_scheme(column)|filegroup|“default”}]使用

SQLServerManagementStudio或CREATEINDEXTransact-SQL语句创建索引概述CREATE[UNIQUE][CLUSTER唯一索引CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources].[Employee]([LoginID]ASC)EmployeeIDLoginIDGenderMaritalStatus…216mike0MS…231fukiko0MM…242pat0MS…291pat0FS……不允许重复键值唯一索引CREATEUNIQUENONCLUSTERED包含列创建多列索引的注意事项复合索引先定义唯一性最高的列键中最多包含16列和900字节索引中包含非键列CREATENONCLUSTEREDINDEXAK_Employee_LoginIDONHumanResources.Employee(LoginIDASC)INCLUDE(ContactID,NationalIDNumber)CREATENONCLUSTEREDINDEXK_Contact_LastName_FirstNameONPeson.Contact(LastNameASC,FirstNameASC)提高查询覆盖度和性能包含列创建多列索引的注意事项复合索引先定义唯一性最高的列何时创建计算列的索引以下情况下，可创建计算列的索引：NUMERIC_ROUNDABORT选项设置为

OFF

表达式是确定性的且精确的üANSI_NULLSconnection_level选项为

ONü

列的计算值不可为

text、ntext或

image数据类型ü

创建索引或更改造成索引更新时，必需的选项设置为

ONüü查询优化器可能忽略计算列的索引何时创建计算列的索引以下情况下，可创建计算列的索引：

索引按类似于已分区表的范围横向分区已分区索引考虑与底层表对齐并在索引键（唯一）中引入分区<20032003-20042004-2005>2005idindex_idpartition_numberhobt_id1216756756765722275675676665…………sys.partitions 索引按类似于已分区表的范围横向分区已分区索引在索引中并入可用空间的选项可用空间影响索引更新性能FILLFACTOR决定了叶节点的可用空间量

对于

OLTP应用程序使用较低的

FILLFACTOR

对于

OLAP应用程序使用较高的

FILLFACTORPAD_INDEX决定了非页索引节点上的可用空间量CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources].[Employee]([LoginID]ASC)WITHFILLFACTOR=65,PAD_INDEX=ON)在索引中并入可用空间的选项可用空间影响索引更新性能FILLF获取索引信息的方法SQLServerManagementStudio：

对象资源管理器“索引属性”窗口系统存储过程：

sp_help目录视图系统函数报告

sp_helpindex获取索引信息的方法SQLServerManagement演示：创建索引本演示中，你将看到如何：使用SQLServerManagementStudio创建索引使用SQLServerManagementStudio查看索引使用Transact-SQL创建索引演示：创建索引本演示中，你将看到如何：第

3节：优化索引数据库引擎优化顾问演示：使用数据库引擎优化顾问

索引碎片索引碎片整理选项演示：索引碎片整理

第3节：优化索引数据库引擎优化顾问数据库引擎优化顾问

分析数据库在工作负荷下的性能

图形