sql创建和优化索引课件.ppt

《sql创建和优化索引课件.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、创建和优化索引SQLServer访问数据的方式表扫描SQLServer扫描表的所有页索引SQLServer使用索引页找到行没有聚集索引的表不按特定顺序存储的页堆堆IAM页数据页idindex_id=0first_iam_pagesys.partitions聚集索引索引页叶节点一张表只能有一个聚集索引B-树按索引键的顺序存储数据页idindex_id=1root_pagesys.partitions索引页中间级根索引页…………B树参考下面的堆和聚集索引每张表最多249个非聚集索引非聚集索引idindex_id>1root_pagesys.partitions数据页堆

2、或者聚集索引索引页叶节点…………根索引页创建索引时遵循一般原则在需要经常搜索的列上面创建索引在主键上创建索引在外键上创建索引在经常用于Where子句的列上创建索引在经常需要排序的列上创建索引对于数据类型为text，image，bit的列不应该增加索引当需要Update性能远高于Select性能时不应该增加索引WITH选项目的ALLOW_ROW_LOCKS在索引上允许/不允许行级锁ALLOW_PAGE_LOCKS在索引上允许/不允许页级锁ONLINE在创建的时候允许/不允许访问索引FILLFACTOR控制在叶级页上释放空间PAD_INDEX控制在非叶级页上释放空间C

3、REATE[UNIQUE][CLUSTERED

4、NONCLUSTERED]INDEXindex_nameON{table

5、view}(column[ASC

6、DESC][,...n])INCLUDE(column[,...n])[WITHoption[,...n]][ON{partition_scheme(column)

7、filegroup

8、"default"}]CREATEINDEX语句惟一索引确保在索引键上不存在相同的值CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources

9、].[Employee]([LoginID]ASC)EmployeeIDLoginIDGenderMaritalStatus…216mike0MS…231fukiko0MM…242pat0MS…291pat0FS……不允许存在相同值创建多列索引复合索引最多可组合16个列以及键的长度不超过900字节首先定义惟一性最高的列包含性列带包含性列的索引提高查询“范围”，提高查询性能CREATENONCLUSTEREDINDEXAK_Employee_LoginIDONHumanResources.Employee(LoginIDASC)INCLUDE(ContactID

10、,NationalIDNumber)CREATENONCLUSTEREDINDEXK_Contact_LastName_FirstNameONPerson.Contact(LastNameASC,FirstNameASC)非键列可以是不允许作为索引键列的数据类型在计算索引键列的数目或索引键大小时，不会被考虑在内USEAdventureWorks;GOCREATEINDEXIX_Document_TitleONProduction.Document(Title,Revision)INCLUDE(FileName);使用Transact-SQL创建具有包含性列的索引

11、对计算列创建索引的场合在以下情况下，可对计算列创建索引：计算列表达式是确定性的和精确的ANSI_NULLS连接级选项为ON求值结果不为text,ntext或者image数据类型的列当创建索引和修改索引的时候要求的选项设置为ONNUMERIC_ROUNDABORT选项设置为OFF查询优化器可能忽略计算列上的索引<20032003-20042004-2005>2005与分区表类似，根据值范围将索引进行水平分区idindex_idpartition_numberhobt_id1216756756765722275675676665…………sys.partitions考虑

12、下面表中的列以及考虑索引键中的分区键（唯一键）已分区索引获得索引信息的方法SQLServerManagementStudio对象资源管理器索引“属性”窗口报告系统存储过程sp_helpsp_helpindex目录视图系统函数分析数据库工作负荷下的性能图形化和命令行界面数据库引擎优化顾问报告和建议工作负荷数据库引擎优化顾问数据库和数据库对象索引碎片索引碎片发生当数据被更改的时候，SQLServer重新整理索引页，对索引页进行拆分。碎片类型内部–页使用不充分外部–页的逻辑顺序错误检测碎片SQLServerManagementStudio–索引属性窗口系统函数–sys.

13、dm_db