sqlserver索引:SQL Server中索引使用及维护来源: 发布时间:星期六, 2008年12月20日 浏览:51次 评论:0
="t18">在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败
 标准 而采用索引来加快数据处理速度也成为广大数据库用户所接受优化思路方法 在良好 数据库设计基础上 能有效地使用索引是SQL Server取得高性能基础SQL Server采用基于代价优化模型它对每 个提交有关表查询决定是否使用索引或用哪个索引 查询执行大部分开销是磁盘I/O使用索引 提高性能个主要目标是避免全表扫描全表扫描需要从磁盘上读表每个数据页如果有索引指向数据值则查询只需读几次磁盘就可以了所以如果建 立了合理索引优化器就能利用索引加速数据查询过程但是索引并不总是提高系统性能在增、删、改操作中索引存在会增加定工作量因此在 适当地方增加适当索引并从不合理地方删除次优索引将有助于优化那些性能较差SQL Server应用实战表明合理索引设计是建立在对各种查询分析和预测上只有正确地使索引和 结合起来,才能产生最佳优化方案本文就 SQL Server索引性能问题进行了些分析和实战 、聚簇索引(clustered indexes)使用 聚簇索引是 种对磁盘上实际数据重新组织以按指定个或多个列值排序由于聚簇 索引索引页面指针指向数据页面所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快每张表只能建个聚簇索引并且建聚簇索引需要至少相当该表 120%附加空间以存放该表副本和索引中间页建立聚簇索引思想是: 1、大多数表都应该有聚簇索引或使用分区来降低对表尾页 竞争在个高事务环境中对最后页封锁严重影响系统吞吐量 2、在聚簇索引下 数据在物理上按顺序排在数据页上重复值也排在起因而在那些 包含范围检查(between、<、<=、>、>=)或使用group by或order by查询时旦找到具有范围中第个键值行具有后续索引值行保证物理上毗连在起而不必进步搜索避免了大范围扫描可以大大提高查询速度 3、在 个频繁发生插入操作表上建立聚簇索引时不要建在具有单调上升值列(如IDENTITY)上否则会经常引起封锁冲突 4、在聚簇索引中不要包含经常修改 列码值修改后数据行必须移动到新位置 5、选择聚簇索引应基于where子句和连接操作 类型 聚簇索引 侯选列是: 1、主键列,该列在where子句中使用并且插入是随机 2、按范围存取 列如pri_order > 100 and pri_order < 200 3、在group by或order by中使用 列 4、不经常修改 列 5、在连接操作中使用 列 2、非聚簇索引(nonclustered indexes) 使用 SQL Server缺省情况下建立 索引是非聚簇索引由于非聚簇索引不重新组织表中数据而是对每行存储索引列值并用个指针指向数据所在页面换句话 说非聚簇索引具有在索引结构和数据本身的间个额外级个表如果没有聚簇索引时,可有250个非聚簇索引每个非聚簇索引提供访问数据区别排序顺 序在建立非聚簇索引时要权衡索引对查询速度加快和降低修改速度的间利弊另外还要考虑这些问题: 1、索引需要使用多少空间 2、合适 列是否稳定 3、索引键是如何选择 扫描效果是否更佳 4、是否有许多重复值 对更新频繁 表来说表上非聚簇索引比聚簇索引和根本没有索引需要更多额外开 销对移到新页每行而言指向该数据每个非聚簇索引页级行也必须更新有时可能还需要索引页分理从个页面删除数据进程也会有类似开销 另外删除进程还必须把数据移到页面上部以保证数据连续性所以建立非聚簇索引要非常慎重非聚簇索引常被用在以下情况: 1、某列常用于集合  (如Sum,....) 2、某列常用于join,order by,group by 3、查寻出 数据不超过表中数据量20% 3、覆盖索引(covering indexes) 使用 覆盖索引是指那些索引项中包含查寻所需要 全部信息非聚簇索引这种索引的所以比较快也正是索引页中包含了查寻所必须数据,不需去访问数据页如果非聚簇索引中包含结果数据,那么它查询速度将快于聚簇索引 但是由于覆盖索引 索引项比较多,要占用比较大空间而且update操作会引起索引值改变所以如果潜在覆盖查询并不常用或不太关键则覆盖索引增加反而会降低性能 4、索引 选择技术 p_detail是住房公积金管理系统中记录个人明细 表有890000行观察 在区别索引下查询运行效果测试在C/S环境下进行客户机是IBM PII350(内存64M),服务器是DEC Alpha1000A(内存128M),数据库为SYBASE11.0.3 1、 select count(*) from p_detail where op_date>’19990101’ and op_date<’ 19991231’ and pri_surplus1>300 2、 select count(*),sum(pri_surplus1) from p_detail where op_date>’19990101’ and pay_month between‘199908’ and’199912’ 不建任何索引查询1 1分15秒 查询2 1分7秒 在op_date上建非聚簇索引查询1 57秒 查询2 57秒 在op_date上建聚簇索引查询1 <1秒 查询2 52秒 在pay_month、op_date、pri_surplus1上建索引查询1 34秒 查询2 <1秒 在op_date、pay_month、pri_surplus1上建索引查询1 <1秒 查询2 <1秒 从以上查询效果分析 索引有无建立方式区别将会导致区别查询效果选择什么样索引基于用户对数据查询条件,这些条件体现于where从句和join表达式中般来说建立索引思路是: (1)、主键时常作为where子句 条件应在表主键列上建立聚簇索引尤其当经常用它作为连接时候 (2)、有大量重复值且经常有范围查询和排序、分组发生 列或者非常频繁地被访问列可考虑建立聚簇索引 (3)、经常同时存取多列 且每列都含有重复值可考虑建立复合索引来覆盖个或组查询并把查询引用最频繁列作为前导列如果可能尽量使关键查询形成覆盖查询 (4)、如果知道索引键 所有值都是唯那么确保把索引定义成唯索引 (5)、在 个经常做插入操作表上建索引时使用fillfactor(填充因子)来减少页分裂同时提高并发度降低死锁发生如果在只读表上建索引则可以把fillfactor置为100 (6)、在选择索引键时 设法选择那些采用小数据类型列作为键以使每个索引页能够容纳尽可能多索引键和指针通过这种方式可使个查询必须遍历索引页面降到最小此外尽可能地使用整数为键值它能够提供比任何数据类型都快访问速度 5、索引 维护 上面讲到,某些不合适 索引影响到SQL Server性能,随着应用系统运行,数据不断地发生变化,当数据变化达到某个程度时将会影响到索引使用这时需要用户自己来维护索引索引维护包括: 1、重建索引 随着数据行 插入、删除和数据页分裂有些索引页可能只包含几页数据另外应用在执行大块I/O时候重建非聚簇索引可以降低分片维护大块I/O效率重建索引实际上是重新组织B-树空间在下面情况下需要重建索引: (1)、数据和使用模式大幅度变化 (2)、排序 顺序发生改变 (3)、要进行大量插入操作或已经完成 (4)、使用大块I/O 查询磁盘读次数比预料要多 (5)、由于大量数据修改 使得数据页和索引页没有充分使用而导致空间使用超出估算 (6)、dbcc检查出索引有问题 当重建聚簇索引时,这张表 所有非聚簇索引将被重建 2、索引统计信息 更新 当在 个包含数据表上创建索引时候SQL Server会创建分布数据页来存放有关索引两种统计信息:分布表和密度表优化器利用这个页来判断该索引对某个特定查询是否有用但这个统计信息并不 动态地重新计算这意味着当表数据改变的后统计信息有可能是过时从而影响优化器追求最有工作目标因此在下面情况下应该运行update statistics命令: (1)、数据行 插入和删除修改了数据分布 (2)、对用truncate table删除数据 表上增加数据行 (3)、修改索引列 值 6、结束语 实战表明 不恰当索引不但于事无补反而会降低系统执行性能大量索引在插入、修改和删除操作时比没有索引花费更多系统时间例如下面情况下建立索引是不恰当: 1、在查询中很少或从不引用 列不会受益于索引索引很少或从来不必搜索基于这些列行 2、只有两个或 3个值 列如男性和女性(是或否)从不会从索引中得到好处 另外 鉴于索引加快了查询速度但减慢了数据更新速度特点可通过在个段上建表而在另个段上建其非聚簇索引而这两段分别在单独物理设备上来改善操作性能0
相关文章
读者评论发表评论 |
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
