《索引及其应用第11章索引及其应用》由会员分享，可在线阅读，更多相关《索引及其应用第11章索引及其应用（51页珍藏版）》请在金锄头文库上搜索。
1、第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据的方法 创建索引 设置索引选项 维护索引 用索引优化向导设置索引,索引介绍,数据存储和访问方法 使用索引的意义及代价 使用索引的指导原则,数据存储和访问方法,数据的存储方法 数据行存储在数据页中 每个数据页包括 8 KB 信息，八个邻近的页称为一个扩展盘区 数据行的存储是无序的，数据页也是无序的 数据页并不是通过链表连接 当行插入满的页的时候，数据页拆分 堆是一个表所有数据页的集成,数据的访问方法：两种方法 扫描表中所有的数据页称为表扫描 开始于表的起点 一页页地扫描表中的所有行 提取符合查询标准的行 使用索引 遍
2、历索引树结构，找到查询所请求的列 只提取符合查询标准的列,数据存储和访问方法（续）,使用索引的意义 索引在数据库中的作用类似于目录在书籍中的作用，用来提高查找信息的速度。 使用索引查找数据，无需对整表进行扫描，可以快速找到所需数据。 使用索引的代价 索引需要占用数据表以外的物理存储空间。 创建索引和维护索引要花费一定的时间。 当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。,使用索引的意义和代价,使用索引的指导原则,创建索引的列 主键 外键或在表联接操作中经常用到的列 在经常查询的字段上最好建立索引 不创建索引的列 很少在查询中被引用 包含较少的惟一值 定义为 text、nte
3、xt 或者 image 数据类型的列,第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据的方法 创建索引 设置索引选项 维护索引 用索引优化向导设置索引,索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询的速度 按存储方式分 聚集索引（clustered） 非聚集索引（nonclustered） 按维护和管理角度分 唯一索引 复合索引 系统自动创建的索引,索引分类,聚集索引,聚集索引的叶节点就是实际的数据页 在数据页中数据按照索引顺序存储,聚集索引的一些事实 行的物理位置和行在索引中的位置是相同的 每个表只能有一个聚集索引 聚集索引的平均大小大约
4、为表大小的5%左右,聚集索引（续）,非聚集索引,“系部代码”索引,“系部”表,(a),(b),非聚集索引的一些事实和指导原则 若未指定索引类型，则默认为非聚集索引 叶节点页的次序和表的物理存储次序不同 每个表最多可以有249个非聚集索引 在非聚集索引创建之前创建聚集索引,非聚集索引（续）,第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据的方法 创建索引 设置索引选项 维护索引 用索引优化向导设置索引,SQL Server 检索存储的数据的方法,SQL Server 中 sysindexes 表的使用 不使用索引查找行 使用非聚集索引在堆中查找行 在聚集索引中查找
5、行 使用基于聚集索引的非聚集索引查找行,SQL Server 中 sysindexes 表的使用,sysindexes 表内的页指针定位表和索引的所有页 包括表和索引的重要统计信息 每个表和索引在 sysindexes 表内都有一行记录，通过对象标识列（id）和索引标识列（indid）惟一标识 indid 列：为不同的目标定位数据页,不使用索引查找行,堆,127号扩展盘区,IAM,128号扩展盘区,129号扩展盘区,130号扩展盘区,SQL Server 查询 sysindexes 表，找到 IAM 页，然后顺序查找表的所有页,使用非聚集索引在堆中查找行,sysindexes,SELECT l
6、astname, firstname FROM member WHERE lastname BETWEEN Masters AND Rudd,在聚集索引中查找行,SELECT lastname, firstname FROM member WHERE lastname = Ota,Martin,Martin,使用基于聚集索引的非聚集索引查找行,sysindexes,SELECT lastname, firstname, phone FROM member WHERE firstname = Mike,Nagata,第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据
7、的方法 创建索引 设置索引选项 维护索引 用索引优化向导设置索引,创建索引,创建和删除索引 创建惟一索引 创建组合索引 获得现有索引的信息,创建和删除索引,创建索引 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名|视图名(列ASC|DESC ,.n ) WITH PAD_INDEX , FILLFACTOR = 填充因子 , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON 文件组,USE Northwind CREATE CLUSTERE
8、D INDEX IX_lastname ON employees(lastname),创建和删除索引（续）,关于创建索引的事实和指导原则 当在某列创建 PRIMARY KEY 约束或 UNIQUE 约束的时候，SQL Server 自动为此列创建索引 必须是表或视图的拥有者才能创建索引 在创建聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始表。因此，数据库上必须有足够的空闲空间，以容纳数据副本。 一个表最多可以创建249个非聚集索引，默认情况下，创建的索引是非聚集索引。 SQL Server 在 sysindexes 系统表内存储索引信息,创建和删除索引（续）,删除索引 语法：
9、DROP INDEX 表名.索引名 |视图名.索引名 , .n 关于删除索引的事实和指导原则 执行 DROP INDEX 语句后，SQL Server 收回被索引占用的磁盘空间 不能用 DROP INDEX 语句删除 PRIMARY KEY 约束或 UNIQUE 约束创建的索引。必须先删除约束，使索引自动删除 删除表的时候，表上的所有索引亦同时被删除 删除聚集索引的时候，表中所有的非聚集索引都会自动被重建,创建惟一索引,惟一索引确保索引列上的数据都是惟一的，不包含重复值 语法：在 CREATE INDEX 语句中使用 UNIQUE 选项,SELECT * FROM Students WHERE
10、 CardID = 330551198108070221,创建惟一索引（续）,惟一索引与聚集索引的区别 聚集索引只能有一个 惟一索引可以有多个 聚集索引可以创建在有重复值的字段上 惟一索引只能创建在具有唯一值的字段上,创建组合索引,组合索引指定多列作为键值，适用于 经常同时存取多列,且每列都含有重复值 当查询只引用索引中的列时,SELECT OrderID, ProductID FROM Order Details WHERE OrderID = 10248,创建组合索引（续）,关于创建组合索引的事实和指导原则 在一个组合索引中可以组合多达16个列 组合索引中所有列必须来自同一张表，除非索引是
11、创建在视图上 定义时将惟一性最好的列放在最前面。CREATE INDEX 语句中的第一列具有最高优先级 若要使查询优化器使用组合索引，查询中的 WHERE 子句必须引用组合索引中的第一列 在（列1，列2）上的索引和在（列2，列1）上的索引是不同的,获得现有索引的信息,在创建、修改、删除索引之前，可能需要现有索引的信息 企业管理器 系统存储过程 sp_helpindex 语法：EXEC sp_helpindex 表名 系统存储过程 sp_help 语法：EXEC sp_help 表名,第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据的方法 创建索引 设置索引选项
12、维护索引 用索引优化向导设置索引,设置索引选项,使用 FILLFACTOR 选项 用来指定各索引页叶级的填满程度 使用 PAD_INDEX 选项 用来指定索引中间级中每个节点的填充百分度,使用 FILLFACTOR 选项,FILLFACTOR 指定每个索引页的填满程度 当叶级索引页满时，如果有新节点插入，SQL Server 必须进行页拆分。 在旧的页后面增添新页 将旧页上约一半的数据移动到新页上,使用 FILLFACTOR 选项（续）,索引页,非叶级,INSERT member (last name) VALUES lastname = Jackson,Akhtar,Ganio,Jackso
13、n,叶级 （键值）,使用 FILLFACTOR 选项（续）,使用FILLFACTOR 的一些事实 一开始的时候在叶级索引页适当留出空间，可以减少页拆分的频率，提高性能 FILLFACTOR 选项仅在索引创建和重建时才应用，SQL Server 并不在索引页上动态维护指定填充率 填充因子值的选用,使用 PAD_INDEX 选项,PAD_INDEX 选项指定了非叶级索引页的填充百分度 只能和 FILLFACTOR 选项共同使用,USE Northwind CREATE INDEX OrderID_ind ON Orders (OrderID) WITH PAD_INDEX, FILLFACTOR
14、= 70,第11章 索引及其应用,索引介绍 索引分类 SQL Server 检索存储的数据的方法 创建索引 设置索引选项 维护索引 用索引优化向导设置索引,维护索引,数据碎片 DBCC SHOWCONTIG 语句 DBCC INDEXDEFRAG 语句 DROP_EXISTING 选项,数据碎片,碎片是如何产生的 数据行往表中添加或从表中删除 索引列的值发生改变，SQL Server 调整索引页以维护索引数据的存储页拆分 管理碎片的方法 删除并重新创建聚集索引并用 FILLFACTOR 选项指定填充因子的值 重建索引并指定填充因子的值 商务环境：对碎片的接受程度取决于环境 OLTP：碎片是有益
15、的，因为 OLTP 是写密集的，典型的OLTP 系统拥有大量同时进行添加和修改数据的用户 分析服务：碎片是有害的，因为此环境是读密集的,数据碎片（续）,碎片的类型 内部碎片，外部碎片 内部碎片：索引页内有空间 索引占用了比所需要的更多的空间，扫描整张表需要更多读操作 有时是有益的：填充因子 外部碎片：页的逻辑顺序和物理存储顺序不匹配，或属于一个表的扩展盘区不连续 对于需要顺序扫描表或索引的部分或全部的情况，是有害的,维护索引,DBCC SHOWCONTIG 显式指定表的数据和索引的碎片信息 DBCC INDEXDEFRAG可以对索引的叶级页进行碎片整理 DROP_EXISTING可以改变索引特
16、性或重建索引,DBCC SHOWCONTIG 语句,DBCC SHOWCONTIG 测定 表或指定索引是否产生了大量碎片 数据和索引页是否已满 DBCC SHOWCONTIG 语句 DBCC SHOWCONTIG (表名|表ID|视图名|视图ID,索引名| 索引 ID) WITHALL_INDEXES|FAST ,ALL_INDEXES | TABLERESULTS,ALL_INDEXES ,FAST|ALL_LEVELS,DBCC SHOWCONTIG 语句（续）,DBCC SHOWCONTIG 语句示例 语句 USE Northwind DBCC SHOWCONTIG (Customers, PK_Customers) 运行结果 DBCC SHOWCONTIG 正在扫描 Customers 表. 表: Customers（2073058421）；索引 ID:
