索引及其应用第11章索引及其应用.ppt

第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 索引介绍 数据存储和访问方法使用索引的意义及代价使用索引的指导原则 数据存储和访问方法 数据的存储方法数据行存储在数据页中每个数据页包括8KB信息 八个邻近的页称为一个扩展盘区数据行的存储是无序的 数据页也是无序的数据页并不是通过链表连接当行插入满的页的时候 数据页拆分堆是一个表所有数据页的集成 数据的访问方法 两种方法扫描表中所有的数据页 称为表扫描开始于表的起点一页页地扫描表中的所有行提取符合查询标准的行使用索引遍历索引树结构 找到查询所请求的列只提取符合查询标准的列 数据存储和访问方法 续 使用索引的意义索引在数据库中的作用类似于目录在书籍中的作用 用来提高查找信息的速度 使用索引查找数据 无需对整表进行扫描 可以快速找到所需数据 使用索引的代价索引需要占用数据表以外的物理存储空间 创建索引和维护索引要花费一定的时间 当对表进行更新操作时 索引需要被重建 这样降低了数据的维护速度 使用索引的意义和代价 使用索引的指导原则 创建索引的列主键外键或在表联接操作中经常用到的列在经常查询的字段上最好建立索引不创建索引的列很少在查询中被引用包含较少的惟一值定义为text ntext或者image数据类型的列 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 索引是在数据库表或者视图上创建的对象 目的是为了加快对表或视图的查询的速度按存储方式分聚集索引 clustered 非聚集索引 nonclustered 按维护和管理角度分唯一索引复合索引系统自动创建的索引 索引分类 聚集索引 聚集索引的叶节点就是实际的数据页在数据页中数据按照索引顺序存储 聚集索引的一些事实行的物理位置和行在索引中的位置是相同的每个表只能有一个聚集索引聚集索引的平均大小大约为表大小的5 左右 聚集索引 续 非聚集索引 系部代码 索引 系部 表 a b 非聚集索引的一些事实和指导原则若未指定索引类型 则默认为非聚集索引叶节点页的次序和表的物理存储次序不同每个表最多可以有249个非聚集索引在非聚集索引创建之前创建聚集索引 非聚集索引 续 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 SQLServer检索存储的数据的方法 SQLServer中sysindexes表的使用不使用索引查找行使用非聚集索引在堆中查找行在聚集索引中查找行使用基于聚集索引的非聚集索引查找行 SQLServer中sysindexes表的使用 sysindexes表内的页指针定位表和索引的所有页包括表和索引的重要统计信息每个表和索引在sysindexes表内都有一行记录 通过对象标识列 id 和索引标识列 indid 惟一标识indid列 为不同的目标定位数据页 不使用索引查找行 堆 127号扩展盘区 IAM 128号扩展盘区 129号扩展盘区 130号扩展盘区 SQLServer查询sysindexes表 找到IAM页 然后顺序查找表的所有页 使用非聚集索引在堆中查找行 sysindexes SELECTlastname firstnameFROMmemberWHERElastnameBETWEEN Masters AND Rudd 在聚集索引中查找行 SELECTlastname firstnameFROMmemberWHERElastname Ota Martin Martin 使用基于聚集索引的非聚集索引查找行 sysindexes SELECTlastname firstname phoneFROMmemberWHEREfirstname Mike Nagata 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 创建索引 创建和删除索引创建惟一索引创建组合索引获得现有索引的信息 创建和删除索引 创建索引CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX索引名ON 表名 视图名 列 ASC DESC n WITH PAD INDEX FILLFACTOR 填充因子 IGNORE DUP KEY DROP EXISTING STATISTICS NORECOMPUTE SORT IN TEMPDB ON文件组 USENorthwindCREATECLUSTEREDINDEXIX lastnameONemployees lastname 创建和删除索引 续 关于创建索引的事实和指导原则当在某列创建PRIMARYKEY约束或UNIQUE约束的时候 SQLServer自动为此列创建索引必须是表或视图的拥有者才能创建索引在创建聚集索引时 将会对表进行复制 对表中的数据进行排序 然后删除原始表 因此 数据库上必须有足够的空闲空间 以容纳数据副本 一个表最多可以创建249个非聚集索引 默认情况下 创建的索引是非聚集索引 SQLServer在sysindexes系统表内存储索引信息 创建和删除索引 续 删除索引语法 DROPINDEX表名 索引名 视图名 索引名 n 关于删除索引的事实和指导原则执行DROPINDEX语句后 SQLServer收回被索引占用的磁盘空间不能用DROPINDEX语句删除PRIMARYKEY约束或UNIQUE约束创建的索引 必须先删除约束 使索引自动删除删除表的时候 表上的所有索引亦同时被删除删除聚集索引的时候 表中所有的非聚集索引都会自动被重建 创建惟一索引 惟一索引确保索引列上的数据都是惟一的 不包含重复值语法 在CREATEINDEX语句中使用UNIQUE选项 SELECT FROMStudentsWHERECardID创建惟一索引 续 惟一索引与聚集索引的区别聚集索引只能有一个惟一索引可以有多个聚集索引可以创建在有重复值的字段上惟一索引只能创建在具有唯一值的字段上 创建组合索引 组合索引指定多列作为键值 适用于经常同时存取多列 且每列都含有重复值当查询只引用索引中的列时 SELECTOrderID ProductIDFROM OrderDetails WHEREOrderID 10248 创建组合索引 续 关于创建组合索引的事实和指导原则在一个组合索引中可以组合多达16个列组合索引中所有列必须来自同一张表 除非索引是创建在视图上定义时将惟一性最好的列放在最前面 CREATEINDEX语句中的第一列具有最高优先级若要使查询优化器使用组合索引 查询中的WHERE子句必须引用组合索引中的第一列在 列1 列2 上的索引和在 列2 列1 上的索引是不同的 获得现有索引的信息 在创建 修改 删除索引之前 可能需要现有索引的信息企业管理器系统存储过程sp helpindex语法 EXECsp helpindex表名系统存储过程sp help语法 EXECsp help表名 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 设置索引选项 使用FILLFACTOR选项用来指定各索引页叶级的填满程度使用PAD INDEX选项用来指定索引中间级中每个节点的填充百分度 使用FILLFACTOR选项 FILLFACTOR指定每个索引页的填满程度当叶级索引页满时 如果有新节点插入 SQLServer必须进行页拆分 在旧的页后面增添新页将旧页上约一半的数据移动到新页上 使用FILLFACTOR选项 续 索引页 非叶级 INSERTmember lastname VALUESlastname Jackson Akhtar Ganio Jackson 叶级 键值 使用FILLFACTOR选项 续 使用FILLFACTOR的一些事实一开始的时候在叶级索引页适当留出空间 可以减少页拆分的频率 提高性能FILLFACTOR选项仅在索引创建和重建时才应用 SQLServer并不在索引页上动态维护指定填充率填充因子值的选用 使用PAD INDEX选项 PAD INDEX选项指定了非叶级索引页的填充百分度只能和FILLFACTOR选项共同使用 USENorthwindCREATEINDEXOrderID indONOrders OrderID WITHPAD INDEX FILLFACTOR 70 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 维护索引 数据碎片DBCCSHOWCONTIG语句DBCCINDEXDEFRAG语句DROP EXISTING选项 数据碎片 碎片是如何产生的数据行往表中添加或从表中删除索引列的值发生改变 SQLServer调整索引页以维护索引数据的存储 页拆分管理碎片的方法删除并重新创建聚集索引并用FILLFACTOR选项指定填充因子的值重建索引并指定填充因子的值商务环境 对碎片的接受程度取决于环境OLTP 碎片是有益的 因为OLTP是写密集的 典型的OLTP系统拥有大量同时进行添加和修改数据的用户分析服务 碎片是有害的 因为此环境是读密集的 数据碎片 续 碎片的类型内部碎片 外部碎片内部碎片 索引页内有空间索引占用了比所需要的更多的空间 扫描整张表需要更多读操作有时是有益的 填充因子外部碎片 页的逻辑顺序和物理存储顺序不匹配 或属于一个表的扩展盘区不连续对于需要顺序扫描表或索引的部分或全部的情况 是有害的 维护索引 DBCCSHOWCONTIG 显式指定表的数据和索引的碎片信息DBCCINDEXDEFRAG 可以对索引的叶级页进行碎片整理DROP EXISTING 可以改变索引特性或重建索引 DBCCSHOWCONTIG语句 DBCCSHOWCONTIG测定表或指定索引是否产生了大量碎片数据和索引页是否已满DBCCSHOWCONTIG语句DBCCSHOWCONTIG 表名 表ID 视图名 视图ID 索引名 索引ID WITH ALL INDEXES FAST ALL INDEXES TABLERESULTS ALL INDEXES FAST ALL LEVELS DBCCSHOWCONTIG语句 续 DBCCSHOWCONTIG语句示例语句USENorthwindDBCCSHOWCONTIG Customers PK Customers 运行结果DBCCSHOWCONTIG正在扫描 Customers 表 表 Customers 2073058421 索引ID 1 数据库ID 6已执行TABLE级别的扫描 扫描页数 3 扫描扩展盘区数 2 扩展盘区开关数 1 每个扩展盘区上的平均页数 1 5 扫描密度 最佳值 实际值 50 00 1 2 逻辑扫描碎片 0 00 扩展盘区扫描碎片 0 00 每页上的平均可用字节数 246 7 平均页密度 完整 96 95 DBCC执行完毕 如果DBCC输出了错误信息 请与系统管理员联系 小于100 则存在碎片 页的填满程度 越大越好 DBCCSHOWCONTIG语句 续 何时执行当表被大量修改时当表包含重要数据时当表的查询性能十分低下时 DBCCINDEXDEFRAG语句 DBCCINDEXDEFRAG语句DBCCINDEXDEFRAG database name database id 0 table name table id view name view id index name index id WITHNO INFOMSGS USENorthwindDBCCINDEXDEFRAG northwind customers PK Customers PagesScannedPagesMovedPagesRemoved 311 1row s affected DBCCINDEXDEFRAG语句 续 索引碎片整理与DBCCDBREINDEX的比较DBCCINDEXDEFRAG是个联机操作 不持有长期锁 不阻塞运行中的查询和更新碎片不多的情况下 碎片整理比重建快 但如果碎片很多 则碎片整理比重建慢碎片整理期间 索引是可用的 而索引重建则不然 DROP EXISTING选项 改变索引的特性类型 可以将非聚集索引改成聚集索引 但不能把聚集索引改成非聚集索引索引列 可以改变索引的列 往组合索引添加或者从组合索引删除特定列 以及改变索引的 惟一 属性选项 可以改变FILLFACTOR或PAD INDEX的百分比的值重建索引不用先删除再重新创建加快建立聚集索引和非聚集索引的过程 第11章索引及其应用 索引介绍索引分类SQLServer检索存储的数据的方法创建索引设置索引选项维护索引用索引优化向导设置索引 用索引优化向导设置索引 确定何时使用索引优化向导确定怎样使用索引优化向导 确定何时使用索引优化向导 索引优化向导可以帮助在一个新的数据库上创建适当的索引或者检验当前数据库上存在的索引是否最佳根据给定的工作负荷或跟踪文件 通过使用查询优化器分析该工作负荷中的查询 为数据库推荐或检验最佳索引配置 确定怎样使用索引优化