创建和优化索引课件

1、第 6 章创建和优化索引第 6 章：创建和优化索引规划索引创建索引优化索引第 1 节：规划索引SQL Server 如何访问数据堆聚集索引非聚集索引SQL Server 如何访问数据索引查找SQL Server 读取所有表页 SQL Server 使用索引页查找行 表扫描SQL Server 2008 引入了增强的全文索引 堆 堆：无聚集索引的表 页不按任何特定顺序存储 堆idindex_id=0first_iam_pageIAM （索引分配映射）页：记录表有哪些页数据页聚集索引 每个表一个聚集索引 B 树以索引键的顺序存储数据页 叶节点idindex_id=1root_page数据页中间级索

2、引页根索引页非聚集索引 B 树引用底层堆或聚集索引 每个表最多 249 个非聚集索引 堆或聚集索引idindex_id1root_page数据页叶节点索引页根索引页第 2 节：创建索引创建索引概述唯一索引创建多列索引的注意事项何时创建计算列的索引已分区索引在索引中并入可用空间的选项获取索引信息的方法演示：创建索引 创建索引概述CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC , . . . n )INCLUDE ( column , . . . n ) WITH

3、 option , . . . n ON partition_scheme (column) | filegroup | “default” 使用 SQL Server Management Studio或CREATE INDEX Transact-SQL 语句唯一索引CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginIDON HumanResources.Employee ( LoginID ASC )EmployeeIDLoginIDGenderMaritalStatus216mike0MS231fukiko0MM242pat0MS291p

4、at0FS不允许重复键值包含列创建多列索引的注意事项复合索引先定义唯一性最高的列 键中最多包含 16 列和 900 字节 索引中包含非键列 CREATE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC) INCLUDE ( ContactID, NationalIDNumber )CREATE NONCLUSTERED INDEX K_Contact_LastName_FirstName ON Peson.Contact ( LastName ASC, FirstName ASC )

5、提高查询覆盖度和性能 何时创建计算列的索引以下情况下，可创建计算列的索引： NUMERIC_ROUNDABORT 选项设置为 OFF 表达式是确定性的且精确的 ANSI_NULLS connection_level 选项为 ON 列的计算值不可为 text、ntext 或 image 数据类型 创建索引或更改造成索引更新时，必需的选项设置为 ON 查询优化器可能忽略计算列的索引 索引按类似于已分区表的范围横向分区 已分区索引考虑与底层表对齐并在索引键（唯一）中引入分区 2005idindex_idpartition_numberhobt_id12167567567657222756756766

6、65sys.partitions在索引中并入可用空间的选项可用空间影响索引更新性能FILLFACTOR 决定了叶节点的可用空间量对于 OLTP 应用程序使用较低的 FILLFACTOR 对于 OLAP 应用程序使用较高的 FILLFACTOR PAD_INDEX 决定了非页索引节点上的可用空间量 CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee (LoginID ASC) WITH FILLFACTOR = 65, PAD_INDEX = ON)获取索引信息的方法SQL Server

7、Management Studio： 对象资源管理器 “索引属性”窗口 系统存储过程： sp_help目录视图 系统函数报告 sp_helpindex演示：创建索引本演示中，你将看到如何：使用 SQL Server Management Studio 创建索引使用 SQL Server Management Studio 查看索引使用 Transact-SQL 创建索引第 3 节：优化索引数据库引擎优化顾问演示：使用数据库引擎优化顾问 索引碎片索引碎片整理选项演示：索引碎片整理 数据库引擎优化顾问 分析数据库在工作负荷下的性能 图形界面和命令行界面 报表和建议 工作负荷数据库引擎优化顾问数据库

8、和数据库对象演示：使用数据库引擎优化顾问在本演示中，你将看到如何：使用 Transact-SQL 脚本分析 AdventureWorks 中的索引查看索引建议索引碎片为何会出现碎片？修改数据时，SQL Server 重新组织索引页 造成索引页拆分 SQL Server Management Studio 索引属性 系统函数 - sys.dm_db_index_physical_stats检测碎片 内部 页未满 外部 页的逻辑顺序失序 碎片类型：索引碎片整理选项重新组织小于 30% 的碎片 大于 30% 的碎片ALTER INDEX AK_Product_Name ON Production.Product REORGANIZE重新生成ALTER INDEX AK_Product_Name ONProduction.Product