（系统工程专业论文）基于NET平台的分页策略的研究与实现.pdf

摘要 随着信息技术的高速发展，计算机、数据库与网络已经成为信息系统的三大 支柱。与此同时，随着数据库应用系统的普及，用户对系统性能的要求也越来越 高，如何最大限度地提升系统的性能已经成为一个迫切需要解决的问题。 在基于s q ls e r v e r 的w e b 数据库应用系统的开发过程中，数据查询分页是 必备的功能之一。为了在实现分页功能的同时兼顾w e b 系统性能，本文探讨了 分页策略的两个层面：一个是数据库优化设计，主要研究了与分页密切相关的索 引、排序以及筛选的设计；另一个是分页设计，主要研究了w e b 、数据库两种分 页模式，阐明了采用数据库分页模式能够最大限度地提高分页执行效率。 以此为基础，本文全面探讨了分页策略的实现，主要包括以下三个层面： ( 1 ) 提出了s d a 、t o b 与rn 三种通用分页算法。其中，s d a 算法兼具 w e b 、数据库两种分页模式的特性，适用于小型的数据库环境；t o b 与rn 两 种算法都采用数据库分页模式，适用于大型的数据库环境。 ( 2 ) 基于n e t 平台，结合c 撑、a s r n e t 、t r a n s a c t s q l 和a d o n e t 四者 的编程特性，设计并实现了一个w e b 数据库应用系统。 ( 3 ) 基于该w e b 系统对三种算法进行了综合评估，以图文并茂的方式给出了 评估结论：当数据库为s q ls e r v e r2 0 0 0 时，推荐t o b 算法；当数据库为s q l s e r v e r2 0 0 5 时，推荐rn 算法。同时表明：采用数据库分页模式的算法具备良 好的分页执行效率，能够满足很大一部分的数据查询分页需求。 关键词：数据库优化：分页；实现 a b s t r a c t w i t ht h er a p i dd e v e l o p m e n to fi n f o r m a t i o nt e c h n o l o g y , c o m p u t e r , d a t a b a s ea n d n e t w o r kh a v ea l r e a d yb e c o m et h et h r e ep i l l a r so fi n f o r m a t i o ns y s t e m s a tt h es a m e t i m e ，c u s t o m e r sr e q u i r e m e n t st ot h es y s t e mp e r f o r m a n c ea r ea l s og e t t i n gh i g h e ra n d h i g h e rw i t ht h ep o p u l a r i t yo fd a t a b a s ea p p l i c a t i o ns y s t e m s h o wt om a x i m i z et h e p e r f o r m a n c eh a sa l r e a d yb e c o m eap r o b l e mt h a tc a l l sf o ri m m e d i a t es o l u t i o n a tt h ed e v e l o p m e n tp r o c e s so fw e bd a t a b a s ea p p l i c a t i o ns y s t e m sb a s e do ns q l s e r v e r , t h eq u e r ya n dp a g i n a t i o no fd a t ai so n eo ft h en e c e s s a r yf u n c t i o n s g i v e a t t e n t i o nt ot w oo rm o r e t h i n g sb o t ht h ef u n c t i o no fp a g i n a t i o na n dt h ep e r f o r m a n c eo f w e bs y s t e m s ，t h i sp a p e ri n v e s t i g a t e st w ol e v e l so fp a g i n a t i o ns t r a t e g y ：o n ei sd a t a b a s e o p t i m u md e s i g n ，m a i n l yo nt h ed e s i g no fi n d e x ，s o r ta n df i l t e r ；a n o t h e ri sp a g i n a t i o n d e s i g n ，m a i n l yo nt w op a g i n a t i o np a t t e r n so f w e ba n dd a t a b a s e b a s e do nt h et w ol e v e l s ，t h ep a p e re l a b o r a t e so nt h ei m p l e m e n t a t i o no ft h es t r a t e g y , w h i c hm a i n l yi n c l u d e st h e r el e v e l sa sf o l l o w s ： ( 1 ) b r i n gu pt h r e eg e n e r a la l g o r i t h m sf o rp a g i n a t i o n ：s d a ，t o ba n dr n ( 2 ) d e s i g na n di m p l e m e n ta k i n do fw e bs y s t e mb a s e do nt h e n e tp l a t f o r mw i t hf o u r p r o g r a m m i n gc h a r a c t e r i s t i c s ，d e r i v e d f r o m c 撑，a s p n e t , t r a n s a c t s q la n d a d o n e t ( 3 ) m a k eac o m p r e h e n s i v ea s s e s s m e n tt ot h et h r e ea l g o r i t h m sb a s e do nt h ew e b s y s t e ma n dg i v et h ea s s e s s m e n tc o n c l u s i o n sw i t hi l l u s t r a t i o ni nt h em e a n t i m e ：w h e n d b m si ss q ls e r v e r2 0 0 0 ，t h et o b a l g o r i t h mi sb e t t e r ；w h e nd b m s i ss q ls e r v e r 2 0 0 5 ，t h er na l g o r i t h m i sb e t t e r a n dt h ec o n c l u s i o n sa l s os u g g e s tt h a tt h e a l g o r i t h m sw h i c ha d o p tt h ep a g i n a t i o np a t t e r no fd a t a b a s eh a v eg o o dp a g i n a t i o n e f f i c i e n c i e s ，a n dt h e yc a nm e e t m o s to ft h ep a g i n a t i o nd e m a n d s k e yw o r d s ：d a t a b a s eo p t i m i z a t i o n ；p 晤n a t i o n ；i m p l e m e n t a t i o n 厦门大学学位论文原创性声明 兹呈交的学位论文，是本人在导师指导下独立完成的研究成果。 本人在论文写作中参考的其它个人或集体的研究成果，均在文中以明 确方式标明。本人依法享有和承担由此论文产生的权利和责任。 羔，参，言孑h 口年月。二r 日 厦门大学学位论文著作权使用声明 本人完全了解厦门大学有关保留、使用学位论文的规定。厦门大 学有权保留并向国家主管部门或其指定机构送交论文的纸质版和电 子版，有权将学位论文用于非赢利目的的少量复制并允许论文进入学 校图书馆被查阅，有权将学位论文的内容编入有关数据库进行检索， 有权将学位论文的标题和摘要汇编出版。保密的学位论文在解密后适 用本规定。 本学位论文属于 i 、保密() ，在年解密后适用本授权书。 2 、不保密( 们 ( 请在以上相应括号内打“ ) 作者签名： 对支孑日期：h 口g 年朋1 日 导师签名： 弓 他日期：沁妒年，月了日 第一章绪论 1 1 分页策略概述 第一章绪论 分页：以较小数据子集( 即页) 的形式返回查询结果集的过程，也即把数据 库中与用户设置好的查询条件匹配的结果集以每页一定数目( 如每页2 0 条) 的 形式呈现在用户面前。 策略：解决方案，一套切实可行的实施方案。 图1 1w e b 数据库应用系统示意图 在以数据为中心的w e b 数据库应用系统( 如图1 1 所示) 开发过程中，数 据的查询分页是必备的功能之一。但随着数据库中存储的数据与日俱增，不加处 理地把大规模查询结果集发送到客户端，一方面整个w e b 系统的运行效率将急 剧下降，另一方面用户也不方便浏览。在很多情况下，用户也不会完全浏览整个 结果集，而是更加关注查询运算出来的统计信息。因此，从系统性能、用户体验 等角度来考虑，分页返回查询结果集就显得相当的重要。 对于大规模查询结果集而言，采取适当的分页策略，对提升系统性能至关重 要，而一个适当的分页策略，它至少需要满足以下两点： 数据库服务器处理的数据量最小； 数据库服务器与w e b 服务器之间的数据传输量最小。 综上所述，分页策略将从两个层面展开探讨：为满足第一点，探讨数据库的 优化设计；为满足第二点，研究分页的设计。这两个层面共同构成了分页策略。 在此基础上，全面探讨了分页策略的实现，是本文研究的重点。 基于n e t 平台的分页策略的研究与实现 1 2 研究背景与意义 1 2 1研究背景 近年来，随着w e b ( i n t e m e t ，i n t r a n e t ) 应用的逐步普及，越来越多的企事业机 构把业务迁移到w e b 上来完成。随之而来的是数据量呈几何爆炸式增长，如何 有效地把这些数据呈现在用户面前无疑是w e b 数据库应用系统开发人员亟待解 决的难题，因为这关系到用户的体验，进而关系到w e b 应用系统的成败。 当然，诸如扩大网络带宽或升级服务器的措施，固然简单直观，但并不能从 本质上解决问题，在某种意义上，企业的最后一公里永远是窄带连接。经验表 明：完全通过系统级服务器性能优化( 如内存大小、文件系统类型、处理器的数 目与类型等) 解决性能问题并不现实，大多数性能问题不能用这种方法解决，必 须通过分析w e b 应用程序及其提交给数据库的查询等操作，并分析这些操作如 何与数据库交互等等方法解决性能问题。 1 2 2研究意义 随着信息技术的高速发展，计算机、数据库与网络已经成为信息系统的三大 支柱，w e b 数据库应用系统显示出了其强大的优势。随着w e b 应用系统的逐步 普及，用户对系统性能的要求也越来越高，如何最大限度地提升系统的性能已经 成为一个迫切需要解决的问题【1 3 1 。 在w e b 数据库应用中，对大型数据库表进行查询，当查询条件较为宽松时， 将查询出大规模满足条件的结果集，如果简单地将大规模结果集一次性全部显示 出来，不仅会降低w e b 页面的响应效率并损害系统性能，而且也不方便用户的 浏览并降低用户体验，这就要求对查询结果集进行分页处理，同时给予顺序浏览 直至尾页或跳跃式浏览到指定页的自由。 可见，数据查询分页功能是w e b 数据库应用系统的一个重要组成部分，对 分页策略进行研究具有积极的意义。 。 最后一公里：从用户驻地业务集中点到用户终端之间的传输及线路等相关设旋。 第一章绪论 1 3 分页算法研究现状 通过查阅大量相关的文献，分析得出目前主流的分页算法及其优缺点如下： ( 1 ) 利用d a t a g r i d 的内置分页功能实现分页【1 4 】 优点：复杂的分页逻辑对开发人员透明，实现非常便捷，降低了开发成本。 缺点：每次请求都必须把整个查询结果集发送给w e b 服务器。由于w e b 应 用的无序性特征，一个用户每次从一个页面转向另外一个页面时，d a t a g r i d 对象 都被销毁并重新创建，这就意味着数据库服务器每次都必须发送整个结果集【3 引。 当结果集规模很大时，系统性能将严重下降，因此实际应用范围受到了限制，只 适用于小型的数据库环境。 ( 2 ) 单纯利用d a t a a d a p t e r 的f i l l 方法实现分页 优点：浅显易懂，实现相对便捷。与( 1 ) 相比，分页操作不依赖于任何一种 界面控件，如d a t a g r i d ，因而移植性更好。 缺点：d a t a a d a p t e r 虽然仅使用所请求的记录来填充目标d a t a s e t ，但和( 1 ) 一样，对于每次请求仍旧必须把整个查询结果集发送给w e b 服务器，两者并没 有本质的区别，同样只适用于小型的数据库环境。 ( 3 ) 利用标识索引列实现分页 优点：数据库服务器与w e b 服务器之间传输的是请求页一页的数据量，有 效降低了数据库服务器与w e b 服务器之间网络传输量。与前两种算法有着本质 的区别，适用于大型的数据库环境。 缺点：数据表的标识索引列必须在数值上保持连续、有序甚至唯一，而实际 应用中难以保证这一前提，极大地限制了其应用范围。 ( 4 ) 利用游标、临时表或者表变量实现分页 优点：与( 3 ) 相比，规避了苛刻的前提条件，应用范围有所扩大。 缺点：因为涉及到游标、临时表或者表变量对数据库服务器的性能要求较高， 而且相比( 3 ) 支持的数据库环境等级反而有所降低。 ( 5 ) 利用各种缓冲技术 优点：早期的数据库应用系统经常有客户端程序中一次性读取大量数据做缓 冲来降低数据查询次数，理论上能有效提高系统性能。 缺点：算法实现复杂，关键参数设置不当可造成系统性能不稳定甚至下降。 基于n e t 平台的分页策略的研究与实现 况且鉴于数据库自身及其联接组件的缓冲技术的逐步提高，自行实现客户端数据 缓冲技术，能够超过以上两个缓冲性能的，已经比较困难了。 1 4 论文的主体内容与结构 1 4 1论文的主体内容 本文的主体内容由第二章、第三章以及第四章构成，这三章分别为数据库优 化设计、分页设计以及分页策略实现。 第二章探讨了数据库相关理论，阐明了本文分页策略研究与实现的数据库管 理系统类型s q ls e r v e r ；此外，着重探讨了数据库优化三方面的设计，包括 索引、排序以及筛选的设计，有别于一个完整的数据库设计步骤，着重探讨的是 与数据库查询分页功能密切相关的设计。 第三章从客观的角度探讨了分页问题，阐明了数据查询分页功能的必要性； 此外，着重探讨了分页模式，包括w e b 、数据库两种分页模式，阐明了采用数据 库分页模式能够最大限度地提高分页执行效率。 第四章是本文研究的重点，首先提出了三种分页算法s q l d a t 弧d a p t e r 、 t o p & o r d e rb y 以及r o wn u m b e r 分页，揭示了其运行原理；基于n e t 平台构建出一个w e b 数据库应用系统；基于该w e b 系统对算法进行了综合评估， 并以图文并茂的方式给出了评估结论；最后在算法的实现复杂度、应用环境与执 行效率等方面进行了比较，并针对不同的s q ls e r v e r 版本的，给予了推荐。 1 4 2论文的主体结构 第二章、第三章以及第四章作为本文的主体内容，并不是彼此孤立成章的， 而是存在密切的联系。对于分页策略的研究与实现，数据库优化设计与分页设计 两者缺一不可。 论文的主体结构如图1 2 所示。 第一章绪论 图1 2 论文主体结构 1 5 论文的特色及其创新点 撰写本文的目标之一是使人对数据查询分页有一个全面的认识，通过阅读并 理解本文后，能够自行实现满足实际应用需求的数据查询分页功能。本文的特色 及其创新点如下： ( 1 ) 探讨了分页策略的两个层面：数据库优化设计与分页设计。 ( 2 ) 提出了s d a 、t o b 与rn 三种通用分页算法。其中，s d a 算法适用于 小型的数据库环境；t o b 与rn 两者适用于大型的数据库环境。 ( 3 ) 基于n e t 平台，设计并实现了一个w e b 数据库应用系统。 ( 4 ) 基于该w e b 系统对三种算法进行了综合评估，以图文并茂的方式给出了 评估结论。 ( 5 ) 对于某些特殊的数据分页需求，参考本文分页策略实现的过程，可以全 面评估相应的专有分页算法的执行效率、适用的数据库环境等等。 基于n e t 平台的分页策略的研究与实现 第二章数据库优化设计 2 1 数据库及其选取原则 2 1 1数据库主要功能 数据库管理系统( d a t a b a s em a n a g e m e n ts y s t e m ，简称d b m s ) 是一个科学 地组织和存储数据，高效地获取和维护数据的系统软件。 在一般不引起混淆的情况下，通常把d b m s 简称为数据库。 d b m s 是介于用户和操作系统之问的一组软件，它实现对共享数据的有效组 织、管理和存取，其主要功能如下【3 】： ( 1 ) 数据定义功能 d b m s 提供数据定义语言( d a t ad e f i n i t i o nl a n g u a g e ，简称d d l ) ，用户通 过d d l 可以方便地对数据库中的数据对象进行定义。 ( 2 ) 数据操纵功能 d b m s 还提供数据操纵语言( d a t am a n i p u l a t i o nl a n g u a g e ，简称d m l ) ，用 户可以使用d m l 操纵数据实现对数据库的基本操作，如查询、插入、更新和删 除等。 ( 3 ) 数据库的运行管理 数据库在创建、运用和维护时由d b m s 统一管理、统一控制。以保护数据 的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。 ( 4 ) 数据库的创建和维护功能 包括数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库 的重组织功能和性能监视、分析功能等。这些功能通常是由一些实用程序完成的。 目前有许多数据库产品，如m i e r o s o i ts q ls e r v e r 、o r a c l e 、d b 2 、m y s q l 、 s y b a s e 、m i c r o s o i ta c c e s s 与v i s u a lf o x p r o 等产品，都以各自固有的特性在数据 库市场上占有一席之地。 第二章数据库优化设计 2 1 2 数据库选取原则 在选取数据库时，遵循的一般原则3 6 1 ，如下所示： ( 1 ) 构造数据库的难易程度 需要分析对d b m s 有没有范式的要求，即是否必须按照系统所规定的数据 模型分析现实世界，创建相应的模型；数据库管理语句是否满足国际标准，满足 国际标准则便于系统的维护、开发、移植；有没有面向用户的易用的开发工具； 所支持的数据库容量，数据库的容量特性决定了d b m s 的使用范围。 ( 2 ) 程序开发的难易程度 有无计算机辅助软件工程( c o m p u t e r - a s s i s t e ds o f t w a r ee n g i n e e r i n g ，简称 c a s e ) 工具叫a s e 工具可以帮助开发者根据软件工程的方法提供各开发阶 段的维护、编码环境，便于复杂软件的开发、维护；有无第四代语言的开发平台 第四代语言具有非过程语言的设计方法，用户不需编写复杂的过程性代码， 易学、易懂、易维护；有无面向对象的设计平台面向对象的设计思想十分接 近人类的逻辑思维方式，便于开发和维护；有无对多媒体数据类型的支持多 媒体数据需求是今后发展的趋势，支持多媒体数据类型的d b m s 必将减少应用 程序的开发和维护工作。 ( 3 ) d b m s 的性能分析 包括性能评估( 响应时间、数据单位时间吞吐量) 、性能监控( 内外存使用 情况、系统i o 速率、s q l 语句的执行，数据库元组控制) 、性能管理( 参数设 定与调整) 等。 ( 4 ) 对分布式应用的支持 包括数据透明与网络透明程度。数据透明是指用户在应用中不需指出数据在 网络中的什么节点上，d b m s 可以自动搜索网络，提取所需数据；网络透明是指 用户在应用中无需指出网络所采用的协议，d b m s 自动将数据包转换成相应的协 议数据。 ( 5 ) 并行处理能力 包括对称多处理( s y m m e t r i c a lm u l t i p r o c e s s i n g ，简称s m p ) 技术、大块并行 处( m a s s i v e l yp a r a l l e lp r o c e s s i n g ，简称m p p ) 技术、c o m a ( c a c h eo n l ym e m o r y a r c h i t e c t u r e ) 技术、n u m a ( n o n u n i f o r mm e m o r ya c c e s s ) 技术和群集技术等。 基于n e t 平台的分页策略的研究与实现 ( 6 ) 可扩展性 可扩展性包括垂直扩展和水平扩展两方面。垂直扩展要求新平台能够支持低 版本的平台，数据库客户机朋艮务器机制支持集中式管理模式，这样保证用户以 前的投资和系统；水平扩展要求满足硬件上的扩展，支持从单c p u 模式转换成 多c p u 并行机模式。 ( 7 ) 数据完整性约束 数据完整性指数据的正确性和一致性保护，包括实体完整性、参照完整性与 复杂的事务规则。 ( 8 ) 并发控制功能 对于分布式d b m s ，并发控制功能是必不可少的。因为它面临的是多任务分 布环境，可能会有多个用户点在同一时刻对同一数据进行读写操作，为了保证数 据的一致性，需要由d b m s 的并发控制功能来完成。评价并发控制的标准应从 下面几方面加以考虑： 保证查询结果一致性方法； 数据锁的颗粒度( 数据锁的控制范围，表、页与元组等) ； 数据锁的升级管理功z h 匕l - , ； 死锁的检测和解决方法。 ( 9 ) 容错能力 异常情况下对数据的容错处理。评价标准：硬件的容错，有无磁盘镜象处理 功能；软件的容错，有无软件方法异常情况的容错功能。 ( 1 0 ) 安全性控制 包括安全保密的程度( 帐户管理、用户权限、网络安全控制与数据约束) 。 ( 1 1 ) 支持汉字处理能力 包括数据库描述语言的汉字处理能力( 表名、域名与数据) 和数据库开发工 具对汉字的支持能力。 鉴于此，再结合自身的科研项目经历，本文涉及的分页策略是基于m i c r o s o f t s q ls e r v e r 研究与实现的。 第二章数据库优化设计 2 2 索引设计 2 2 1 索引的原则与建议 索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查 询速度，采用的是平衡树( b a l a n c et r e e ，简称b t r e e ) 结构【3 4 】。 索引是一个单独的、物理的数据库结构。数据库中表的存储由两部分组成： 一部分用来存放数据页面，另一部分存放索引页面。通常，索引页面相对于数据 页面来说小得多。数据查找花费的大部分开销是磁盘读写，没有索引就需要从磁 盘上读表的每一个数据页，如果有索引，则只需查找索引页面就可以了。所以创 建合理的索引，就能加速数据的查找过程【】。 在s q l s e r v e r 中，索引是按b t r e e 结构进行组织的，索引b t r e e 中的每一 页称为一个索引节点，b t r e e 的顶端节点称为根节点，索引中的底层节点称为叶 节点，根节点与叶节点之间的任何索引级别统称为中间级。 可以为表中的单个列创建索引，也可以为一组列创建索引。索引包含一个条 目，该条目有来自表中每一行的一个或多个列( 搜索关键字) ，b t r e e 按搜索关 键字排序，可以在搜索关键字的任何子词条集合上进行高效搜索。 1 创建索引的一般性原则 索引的目的是让查询优化器能有一条到达该数据项最有效的途径。不过，索 引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空 间。另外，为了维护索引，对数据进行插入、更新、删除操作所花费的时间会更 长。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源 方面的代价。因此，创建索引有利有弊，设计索引时需要遵循的一般性原则如下 所示【1 5 】： 如果s e l e c t 查询操作是系统瓶颈，则可以考虑创建适当的索引以提 高查询效率； 如果i n s e r t 、u p d a t e 和d e l e t e 修改操作是系统瓶颈，则可以考虑 删除某些索引； 一一读取记录的百分比越低，那么使用索引就越有效。根据d o n a l dk 基于n e t 平台的分页策略的研究与实现 b u r l e s o n 的提法，对于数据有原始排序的表，读取少于表记录数4 0 的查询应 该使用索引范围扫描，反之应全表扫描；对于未排序的表，读取少于表记录数 7 的查询应该使用索引范围扫描，反之应全表扫描。 2 创建索引的一般性建议 在某种程度上，索引创建适当与否，是系统性能高低的关键因素，关于创建 索引的一般性建议如下： 在经常进行联接的列上创建索引； 在频繁进行排序( o r d e rb y ) 或分组( g r o u pb y ) 的列上创建索引； 在条件表达式中经常用到的不同值较多的列上创建索引，避免在不同值 少的列上创建索引； 当数据库表更新大量数据后，删除并重建索引可以提高查询速度； 使用索引优化向导分析查询并获得索引建议； 对聚集索引使用整型键。另外，在唯一列、非空列或i d e n t i t y 列上 创建聚集索引可以获得性能收益； 如果索引列的所有值都是唯一的，那么确保把索引定义成唯一索引； 避免对小表创建索引。因为对小表进行表扫描通常效率更高，所以要避 免对非常小的表创建索引，这会节省加载和处理索引页的开销； 采用数字型列作为索引列以使每个索引页能够容纳尽可能多的索引列 值和指针。通过这种方式，可使一个查询必须遍历的索引页降至最小。 2 2 2索引类型及其建议 根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型： 一种是数据表的物理顺序与索7 1 ；顿序相同的聚集索引；另一种是数据表的物理顺 序与索引顺序不相同的非聚集索引，其中组合索引是其中最常使用的一种。 。 注：在不同的参考资料中，对是否使用索引的读取记录的百分比值不太一致，基本上是一个经验值， 但是读取记录的百分比越低，使用索引越有效。 第二章数据库优化设计 1 聚集索引及其建议 在聚集索引中，叶节点包含基础表的数据页，根节点和中间级节点包含存有 索引行的索引页，每个索引行包含一个键值和一个指针，该指针指向b - t r e e 上 的某一中间级页或叶级索引中的某个数据行，每级索引中的页均被链接在双向链 接列表中【3 7 1 。 聚集索引在单个分区中的结构如图2 1 所示。 来源：h t t p ：t e c h n e t m i c r o s o f t t o m 图2 1 聚集索引的结构 在聚集索引中，表中各行的物理顺序与键值的逻辑( 索引) 顺序相同，也可 以说是聚集索引确定表中数据的物理顺序，因而一个表只能包含一个聚集索引。 聚集索引对于那些需要频繁搜索范围值的列特别有效，因为使用聚集索引找 基于n e t 平台的分页策略的研究与实现 到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如 果一个查询频繁查找某一日期范围内的记录，则使用聚集索引可以迅速找到包含 开始日期的行，然后查找表中所有相邻的行，直到到达结束日期。 如果对从表中查找的数据进行排序( o r d e rb 时频繁用到某- - n ，则可以 在该列上聚集( 物理排序) ，也即在该列上创建聚集索引，这样可以避免每次查 询该列时都进行排序，因为表中的数据已经按照聚集索引列排序好了。因此，如 果需要使用索引来更有效地排序查询结果集，最佳的方式是在要排序的列上创建 聚集索引，换句话说就是o r d e rb y 按聚集索引列排序的效率最高。 此外，可考虑将聚集索引应用于以下情形： 包含大量非重复值的列； 被频繁访问的列； 被频繁用于w h e r e 子句和联接操作的列； 返回大型结果集的查询。 但是，聚集索引不适用于以下情形： 频繁修改的列。s q ls e r v e r 必须按物理顺序存储数据行，因此，频繁地 修改聚集索引列，将导致数据行的频繁移动； 宽数据的列。聚集索引的键值被当作非聚集索引的查找键使用，并且存 储在每个非聚集索引的叶条目内，因此在宽数据列上创建聚集索引，将导致每叶 条目内键值数目减少，从而一个查询必须遍历更多的索引页。 2 非聚集索引及其建议 非聚集索引与聚集索引具有相同的b - t r e e 结构，它们之间的显著差别在于 以下两点【3 7 】： 基础表的数据行不按非聚集键的顺序排序和存储； 非聚集索引的叶子层是由索引页而不是由数据页组成。 非聚集索引在单个分区中的结构如图2 2 所示。 第二章数据库优化设计 l 硼li n d e x _ i d 0i r o o t _ p a g el 报节点 l 险 j 懒姆黼鞭： 索弓 污 晴节赢 。| r 险氐 i 蔓嚣髯么页 公 簟江页 节一页 父 羔一嚣 带山页墨 袈0 彳象l i袈t l 7 毂嚣烫 l i n , 氐 奔。 ，| 奔， 鼢戮l 繁赫篪 公公 麓蝶7 i 滞赫甏 i - f t ， 毂爨 彳熬摅 髫 i i _ _ 来源：h t t p ：t e c h n e t m i c r o s o f t c o m 图2 2 非聚集索引的结构 非疆燕豢亏l 堰缀疆篪索弓l 在非聚集索引中，表中数据行的物理顺序与键值的逻辑顺序不一样。 s q ls e r v e r 缺省情况下创建的索引是非聚集索引，与聚集索引相比，其数据 访问效率较低，但是一张表上可以创建多个非聚集索引。 可考虑将非聚集索引应用于以下情形： 包含大量非重复值的列( 如果聚集索引已用于其它列) ； 不返回大规模结果集的查询； 返回精确匹配的查询条件( w h e r e 子句) 中经常使用的列； 频繁用于联接和分组操作中的列； 用于外键的列。 基于n e t 平台的分页策略的研究与实现 3 组合索引及其建议 组合索引是创建在多个列上的索引，是非聚集索引中最常使用的一种。 为频繁执行的数据查询操作创建组合索引是查询优化中最常使用的技术之 一。当表中包含很多列，但查询最常引用的只有很少几列时，此类索引尤其适用。 通过创建一个或多个组合索引，可以大大改进数据查询操作的性能，因为这些索 引要访问的数据量小得多，从而使产生的v o 开销更少。 需要注意的是，s q ls e r v e r 对组合索引列的排序是以创建在左侧的列为基准 的，所以如果查询参数中的列不是位于组合索引的左侧，那么该组合索引对于该 查询通常是无效的【3 8 】。例如，对于一个a 、b 、c 列上的组合索引，可以在a 以 及a 、b 和a 、b 、c 上对其进行高效搜索，否则通常是无效的。 此外，可考虑将组合索引应用于以下情形： 经常同时存取多列，且每列都含有重复值可考虑创建组合索引； 如果待排序的列有多个，可以在这些列上创建组合索引； 组合索引要尽量使关键查询形成索引覆盖，无论是否经常使用组合索引 的其他列，但其前导列一定要是使用最频繁的列。 一种查询效率很高的组合索引是覆盖索引，它是一种创建在查询涉及到的所 有列上的非聚集索引，它实际上是表的一个小型副本，包含该表中所有列的一个 子集。覆盖索引之所以高效是因为其索引页中包含了查询需要的所有数据，不需 要再去访问数据页。此外，利用覆盖索引，就无需使用表扫描或聚集索引来扫描 整个表，因而它的查询效率甚至高于聚集索引的查询效率。 通过覆盖索引，查询涉及到的所有列都是索引键值的一部分而存放在索引的 叶子层级。覆盖索引不是只包含在w h e r e 子句内出现的列，而是s e l e c t 子 句涉及的所有列，甚至在g r o u pb y 或o r d e rb y 子句中出现的列【l 】。 例如，一条查询语句如下所示： 假如s e l e c tl i s t 中包含c o l u m n a 列；s e a r c hc o n d i t i o n 包含c o l u m n b 列，则创 建的最佳覆盖索引应采用如下的方式： 第二章数据库优化设计 创建覆盖索引时，列顺序是十分重要的。将w h e r e 子句中的c o l u m n b 列 放在索引的第一位，是要利用它在索引的上层结构，也就是非叶子层查询出相应 条目，然后在存储记录的其他列叶子层读取所需的记录。 利用不同的索引结构，如聚集索引、非聚集索引、覆盖索引等等辅助同一 s q l 查询语句( 具体查询参数略) ，如下所示： 测试创建各种索引情况下所花的i o 成本，结果如表2 1 所示。 表2 1 通过各种索引执行同一查询所花的i o 成本 存取方式i o 数 数据表扫描 5 8 2 在c o l u n m b 列上创建聚集索引 4 0 5 在c o l u m n a ，c o l u m n b 两列上创建覆盖索引 2 8 7 在c o l u m n b ，c o l u m n a 两列上创建覆盖索引 1 7 2 来源：胡百敬m i c r o s o f ts q ls e r v e r 性能调校【m 】北京：电子工业出版社，2 0 0 5 由表2 1 可见，若通过聚集索引查询，因为缩小了数据表扫描范围，所以 效率较高。若创建覆盖索引，因为其数据结构远小于数据表本身，所以不管覆盖 索引的字段顺序是否适当，都能取得较高的查询效率。当然，按照前述的最佳覆 盖索引方式创建的覆盖索引，也即将c o l u m n b 列放在索引顺序的第一位，其查 询效率最高。 重申一点，因为s q ls e r v e r 在插入、更新以及删除记录时会自动维护索引， 所以创建的索引数目需要恰到好处。经验证明：大多数的数据表只需要少数索引 就可以满足系统整体性能的要求，一般限制数据表最多8 至1 0 个索引【l 】。 基于n e t 平台的分页策略的研究与实现 2 2 3索引及其性能示例 1 索引示例 数据库中的索引与书籍中的目录类似。在一本书中，利用目录可以快速查找 所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行 扫描，就可以在其中找到所需数据。书中的目录是一个词语列表，其中注明了包 含各个词语的页码。而数据库中的索引是一个表中所包含的值的列表，其中注明 了表中包含各个值的行所在的存储位置。 在随s q ls e r v e r2 0 0 0 提供的p u b s 示例数据库中，e m p l o y e e 表在e m p _ i d 列 上有一个索引。图2 3 显示索引如何存储每个e m pi d 值并指向表中包含各个值 的数据行。 当s q ls e r v e r 执行一个语句，在e m p l o y e e 表中根据指定的e m p _ i d 值查找数 据时，它能够识别e m p列的索引，并使用该索引查找所需数据。如果该索引id 不存在，它会从表的第一行开始，逐行搜索指定的e m p值。id e m p l o y e e s 表 图2 3 索引示意图 来源：m i c r o s o f ts q ls e r v e r2 0 0 0 联机丛书 第二章数据库优化设计 2 索引性能示例 对大型数据库环境而言，使用索引提高性能的一个主要目标是避免表扫描， 因为表扫描需要从磁盘上读表的每个数据页，如果表中存在有效的索引，则可有 效降低磁盘i o ，进而提高系统的性能。所以如果创建了合理的索引，查询优化 器就能利用索弓l j n 速数据的查询过程。 例如，产品订单数据表( o r d e r s ) 中有1 0 0 万条记录，执行查询： 若没有在o r d e r l d 列上创建索引，则需要比较1 0 0 万次；若有创建，则因为 索引列值都是b t r e e 结构有顺序地摆放，所以可采用二分查找法找数据，计算 方法如下所示： 2 ” 1 0 0 0 0 0 0 ( ，z ) = ，n 2 0 也即最多搜索2 0 次就可以找到所需记录。由2 0 次与1 0 0 万次的比较，可见 性能的巨大差异。由此看出，索引在数据库性能上扮演了非常重要的角色。 关于索引设计，最后再强调一点，通过创建索引的方式提高数据库性能的方 式也有一些缺点。例如，使用较多索引时，s e l e c t 查询的运行速度很可能会更 快。但是，i n s e r t 、u p d a t e 和d e l e t e 操作的速度将显著减慢，因为对于每 个操作都必须维护更多的索引。因此，如果查询主要包含s e l e c t 语句，则使 用较多的索引是非常有帮助的；如果应用程序需要执行许多d m l 操作，就有必 要控制创建的索引数。 基于n e t 平台的分页策略的研究与实现 2 3 排序设计 2 3 1 排序的索引选择 排序就是对查询结果集内的行进行有序组织的操作，具体来说就是可以确定 具体某列或一组列，这( 些) 列的值决定结果集内行的顺序，方式是在s q l 语 句中嵌入o r d e rb y 子句。 2 2 2 节提到o r d e rb y 按聚集索引列排序的效率最高，也可以说要使用索 引来更有效地排序查询数据，最佳的方式是在要排序的列上创建聚集索引。当按 聚集索引列排序时，s q ls e r v e r 已经按聚集索引的键值顺序来存储数据，因而查 询执行时不需要额外的排序操作。 现利用查询分析器显示图形化执行计划的功能来举例验证：o r d e rb y 按 聚集索引列排序的效率最高。 在s q ls e r v e r2 0 0 0 的n o r t h w i n d 示例数据库中，o r d e r s 数据表中在o r d e r l d 列上创建了聚集索引( p ko r d e r s ) ，现要求查询o r d e r s 数据表的所有数据，并按 照o r d e r i d 列排序。可预见s q ls e r v e r 将直接利用聚集索引来存取已预先排序好 的列，执行的查询语句如下： 上述查询的执行计划如图2 4 所示： 来存取数据，同时完成所需的排序操作， s q ls e r v e r 使用扫描聚集索引的方式 这与预期一样。 第二章数据库优化设计 国 成s 本e l e ：c 嘣t 腿餮酽 c l u s t e r e d1 n d e zsc 姐 扫描聚集素引，可以扫描全部内容，也可以只扫 描一个范围 圈 圜 s i j l e c r 成本：0 物理操作： 逻辑撰作： 行计数： 璜计行大小 i o 成本： c p u 成本： 执行次数： 成本： 子树成本： 硬计行计致 c l u s t e r e di n d e xs c u m c l u s t e r e di n d e xs c 姐 8 3 0 2 4 7 0 0 5 1 6 0 0 0 0 9 0 a l l 0 0 s 2 6 4 4 ( 1 0 0 憾) 0 0 5 2 6 8 3 0 参敦： o b j e c t ：( 1 f o r t h w i n d d b o o r d e r s p k0 r d e r = 】) ，o r d e r e df 0 姗 助 图2 4 按聚集索引扫描并排序的执行计划 成本s o ：t 3l 茹麟翟掌成本：3 l 茹镒豳幽 c l u s t e r e di n d e z $ c u 据垄犟豢蚕j j 可以扫描全部内客j 也可咀只扫 c l u s t e r e d _ - n d e xs c m c l u s t e ：e dl n d e zs c 甑 8 2 盯 c 0 5 l o 0 c 0 0 9 9 l l 0 0 5 2 6 4 4 ( 6 9 餐) r 八s 筛 8 3 0 参甄： 0 b j 田c t ：( n o r ：h 坩i a d 】【i 玷o 】 0 r i e r s p 匿_ o r d e r s j 图2 5 按非聚集索引扫描并排序的执行计划 为了做比较，现要求记录按照没有创建聚集索引的列( 该列上可以创建非聚 集索引，也可不创建任何索引) 叫u s t o m e r i d 来排序，执行的查询语句如下： ：小c：敦作作：大奉本致本计徘薹；戤欷斛；鑫撼：胜甜理辑计计m可行本树计物逻缸硬玎凹执成子硬 基于n e t 平台的分页策略的研究与