２００６年第３期 Ｎｏ．３．２００６ 湖州职业技术学院学报 Ｊｏｕ ｒ ｈａｌ ｏｆ Ｈｕｚｈｏｕ Ｖｏｃａｔｉｏｎａｌ ａｎｄ Ｔｅｃｈｎｏｌｏｇｉｃａｌ Ｃｏｌｌｅｇｅ ２００６年９月 Ｓｅｐ．２００６ 基于ＳＱＬ Ｓｅｒｖｅｒ的数据库 查询优化分析 徐寿芳 ，方东傅。 （１．湖州职业技术学院经贸分院，浙江湖州 ３１３０００；２．湖州职业技术学院理工分院，浙江湖州 ３１３０００） 摘要：优化数据库的整体性能是数据库技术的另一个研究热点。 以ＳＯＬ Ｓｅｒｖｅｒ为基础，分析了数据库查询优化的常用方 法：索引优化和ＳＯＬ语句优化，证明可以提高数据库的整体性能。 关键字：索引；ＳＱＬ；ＳＥＬＥＣＴ语句；性能优化 中图分类号：ＴＰ３９２ 文献标识码：Ａ 文章编号：１６７２—２３８８（２００６）０３—００８６—０３ Ｄａｔａｂａｓｅ’Ｓ Ｑｕｅｒｙ Ｏｐｔｉｍｉｚａｔｉｏｎ Ｂａｓｅｄ ｏｎ ＳＱＬ Ｓｅｒｖｅｒ ＸＵ Ｓｈｏｕ—ｆａｎｇ，ＦＡＮＧ Ｄｏｎｇ—ｆｕ （１．Ｆａｃｕｌｔｙ ｏｆＥｃｏｎｏｍｙ ａｎｄ Ｔｒａｄｅ．Ｈｕｚｈｏｕ Ｖｏｃａｔｉｏｎａｌ ａｎｄ Ｔｅｃｈｎｏｌｏｇｉｃａｌ ＣｏＨｅｇｅ，Ｈｕｚｈｏｕ ３１３０００，Ｃｔｕ＇ｎａｌ Ｆａｃｕｌｔｙ ｏｆＳｃｉｅｎｃｅ ａｎｄ Ｅｎｇｉｎｅｅｒｉｎｇ。Ｈｕｚｈｏｕ Ｖｏｃａｔｉｏｎａｌ ａｎｄ Ｔｅｃｈｎｏｌｏｇｉｃａｌ Ｃｏｌｌｅｇｅ，Ｈｕｚｈｏｕ ３１３０００，Ｃ＆ｈａｔ） Ａｂｓｔｒａｃｔ：ｏｖｔｉｒ￣ｔｈｅ ｔｏｔａｌ ｐｅｒｆｏｍ肌ｃｅ ｏｆ ｄａｔａｂａｓｅ ｈａｓ ｂｅｃｏｍｅ ａｎｏｔｈｅｒ ｈｏｔ ｔｏｐｉｃ ｉｎ ｄａｔａｂａｓｅ’ｓ ｔｅｃｈｎｏｌｏｇｙ．Ｏｉｌ ｔｈｅ ｂａｓｅ ｏｆ ＳＱＬ Ｓｅｒｖｅｒ，ｗｅ ｔａｌｋ ａ． ｂｏｕｔ ｔｈｅ ｍｅｔｈｏｄ ｏｆ ａｔｄａｂａｓｅ’ｓ ｑｕｅｒｙ ｏｐｔｉｍｉｚａｔｉｏｎ：ｉｎｄｅｘ ｏｐｔｉｍｉｚａｄｏｎ ｎｄ ａＳＱＬ ｓｔａｔｅｍｅｎｔ ｏｐｔｉｍｉｚａｔｉｏｎ，ＳＯ ａｓ ｔｏ ｈｎｐｍｖｅ ｄａｔａｂａｓｅ’ｓ ｏｔａｌｔ ｐｅｒｆｏｍ∞ｃ皂 Ｋｅｙ ｗｏｒｄｓ：ｉｎｄｅｘ ｓｔｒｕｃｔｕｒｅ ｑｕｅｒｙ ｌａｎｇｕａｇｅ；ｓｅｌｅｃｔ ｓｔａｔｅｍｅｎｔ；ｐｅｒｆｏｒｍａｎｃｅ ｏｐｔｉｍｉｚａｔｉｏｎ 现代ＳＱＬ数据库的性能优化包括系统的硬件性能优化、操作系统性能优化、数据库系统配置优化、 应用程序性能优化。数据库系统配置和应用程序性能优化相互影响，相互作用。本文从软件开发的角 度，侧重数据库系统的查询性能优化，介绍几种可行的查询优化方法。 １ 优化数据库结构 在数据库结构设计时，对一些包含数值信息的字段尽量设计为数字型字段，而少用或不用字符型字 段。字符型字段会降低查询和连接的性能，并增加内存开销。原因很简单，字符型数据在处理查询和连 接时要求逐个字符进行比较，而数字型数据只需要比较一次。另外，在条件许可的情况下，增加冗余字段 也是提高查询性能的一个有效手段 在需要多个表连接的查询中，查询的性能较低，如果在适当的位置 增加冗余字段，相应地需要连接的表的数据就减少了，提高了查询的性能。尤其当两个大表连接时，或处 理一些需要计算后才能参与连接或查询的字段时，增加冗余字段可以显著地提高查询效率。 ２ 索引调整 索引是对数据库表中一个或多个列的值进行排序的结构。索引 ］（Ｐ ”提供指针以指向存储在表中指 ＊收稿日期：２００５—１２—２５ 作者简介：徐寿芳（１９７４一），女，江苏盐城人，湖州职业技术学院经贸分院讲师，硕士，主要从事信息技术和图像处理研究；方东傅 （１９６６一），男，浙江海盐人，湖州职业技术学院理工分院副院长，副教授，主要从事管理信息系统开发研究。 维普资讯 http://www.cqvip.com
第３期 徐寿芳，方东傅：基于ＳＱＬ Ｓｅｒｖｅｒ的数据库查询优化分析 ８７ 定列的数据值，然后根据指定的排序次序排列这些指针。利用它可以快速访问数据库表中的特定信息。 索引按数据库中数据的排列顺序和索引顺序是否一致，又可分为聚集索引和非聚集索引。根据索引的结 构，在查询优化时可以通过调整索引，以取得更好的查询效率。 ２．１ 索引结构 聚集索引Ｅ２３（Ｐｉｓ）基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为 数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别。只有当表包含聚集索引时，表 内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。由于数据行按基于 聚集索引键的排序次序存储，因此聚集索引对查找行很有效。特别对那些经常要搜索范围值的列特别有 效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。 非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个 键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。只有在表上创建了 聚集索引时，表内的行才按特定的顺序存储。这些行就基于聚集索引键按顺序存储。如果一个表只有非 聚集索引，它的数据行将按无序的堆集方式存储。 ２．２ 索引的使用 表１总结了何时使用聚集索引或非聚集索引。但在实践中以上规则却很容易被忽视或不能根据实 际情况进行综合分析。在实践中遇到索引使用的误区主要有两类。 表Ｉ 索引使用的场合 ２．２．１ 主键就是聚集索引，只要建立索引就能显著提高查询速度 聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则使得聚集索引变得更加珍贵。 使用聚集索引的最大好处就是能根据查询要求，迅速缩小查询范围，避免全表扫描。虽然ＳＱＬ Ｓｅｒｖｅｒ默 认是在主键上建立聚集索引的，但主键就是聚集索引是一种极端，有时是对聚集索引的一种浪费。 通常，在每个表的设计中都建立一个ＩＤ列，以区分每条记录数据，将该列设为主键，ＳＱＬ Ｓｅｒｖｅｒ会 将此列默认为聚集索引，这样可以实现记录数据在数据库中按照ＩＤ进行物理排序，但意义不大。因为 ＩＤ号是自动生成的，若不知道每条记录的ＩＤ号，很难在实践中用ＩＤ号来进行查询。这就使让ＩＤ号这 个主键作为聚集索引成为一种资源浪费。 ２．２．２把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度 在进行数据查询时都离不开数据列有多个，都很重要，可以把他们合并起来，建立一个复合索引。从 实际应用中，可以验证到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的 查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而 如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。如果复合索引的所 有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，无论是否 经常使用聚合索引的其他列，但其前导列应该是使用最频繁的列。 在实践中，索引使用有如下特点：（１）用聚合索引比用不是聚合索引的主键速度快；（２）用聚合索引比 用一般的主键作ｏｒｄｅｒ ｂｙ时速度快，特别是在小数据量情况下，但数据量如果很大的话，则二者的速度差 别不明显；（３）使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论 聚合索引使用了多少个；（４）日期列不会因为有分秒的输入而减慢查询速度。 ３ ＳＱＬ语句优化 ＳＱＬ查询处理处理器 。］（Ｐ 。 主要有查询编译、查询执行两部分组成，其中查询编译包括选择逻辑查询 维普资讯 http://www.cqvip.com
８８ 湖州职业技术学院学报 ２００６正 计划和选择物理查询计划两个主要部分，查询处理器执行查询过程如图１所示，ＳＱＬ查询经过查询编译 得到查询计划的过程如图２所示。 查询 １ ｒ 分析查询 查询表达式树 １ ｒ 元 选择逻辑查询计划 数 据 逻辑查询计划树 １ ｒ 选择物理查询计划 执行计划 圈１ 查询处理器的主要部分 圈２ 查询编译过程 在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一 个阶段可以被用作一个扫描参数（ＳＡＲＧ），［４］（Ｐ５１－５４）那么就称之为可优化的，并且可以利用索引快速获得 所需数据。ＳＡＲＧ是用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值的范围内的匹 配或者两个以上条件的ＡＮＤ连接。形式如下：列名操作符＜常数或变量＞或＜常数或变量＞操作符列名。 如果一个表达式不能满足ＳＡＲＧ的形式，那它就无法限制搜索的范围了，因此ＳＱＬ Ｓｅｒｖｅｒ必须对 每一行都判断它是否满足ＷＨＥＲＥ子句中的所有条件。 我们总结了在实践中使用ＳＡＲＧ的经验： ①Ｌｉｋｅ语句是否属于ＳＡＲＧ取决于所使用的通配符的类型。如：ｎａｍｅ １ｉｋｅ‘张 ’，这就属于 ＳＡＲＧ，而：ｎａｍｅ ｌｉｋｅ‘ 张’，就不属于ＳＡＲＧ。原因是通配符 在字符串的开通使得索引无法使用。 ②ｏｒ会引起全表扫描。Ｎａｍｅ一’张三’ａｎｄ价格＞５ ０００符合ＳＡＲＧ，而：Ｎａｍｅ＝’张三’ｏｒ价格＞ ５０００则不符合ＳＡＲＧ。使用ｏｒ会引起全表扫描。 ③非操作符、函数引起的不满足ＳＡＲＧ形式的语句。不满足ＳＡＲＧ形式的语句最典型的情况就是 包括非操作符的语句，如：ＮｏＴ、！一、＜＞、！＜、！＞、ＮｏＴ ＥＸＩＳＴＳ、ＮＯＴ ＩＮ、ＮｏＴ ＬＩＫＥ等，另外还 有函数。ＡＢＳ（价格）＜５０００、Ｎａｍｅ １ｉｋｅ‘ 三’等，而有些表达式，如：ＷＨＥＲＥ价格＊２＞５０００，ＳＱＬ ＳＥＲｖＥＲ也会认为是ＳＡＲＧ，ＳＱＬ ＳＥＲＶＥＲ会将此式转化为：ＷＨＥＲＥ价格＞２５００／２。但要注意有时 ＳＱＬ Ｓｅｒｖｅｒ不能保证这种转化与原始表达式是完全等价的。 ④ＩＮ的作用相当与ｏＲ。如语句：Ｓｅｌｅｃｔ－Ｘ－ｆｒｏｍ ｔａｂｌｅ１ ｗｈｅｒｅ ｒｉｄ ｉｎ（２，３）和Ｓｅｌｅｃｔ－Ｘ－ｆｒｏｍ ｔａｂｌｅｌ ｗｈｅｒｅ ｔｉｄ＝２ ｏｒ ｔｉｄ＝３是一样的，都会引起全表扫描，如果ｔｉｄ上有索引，其索引也会失效。 ４ 结束语 现代数据库规模越来越大，数据量呈指数级上升，数据库查询性能的优化越来越引起人们的重视。 本文基于ＳＱＬ的标准查询语句阐述了基本的数据库性能优化方法，通过优化数据库结构、调整索引、规 范ＳＱＬ语句来提高数据库系统的整体性能。 参考文献： ［１］Ｋａｒｅｎ Ｈｏｇｏｂｏｏｍ．ＳＹＢＡＳＥ系统管理员手册［Ｍ］．北京：机械工业出版社，１９９８． ［２］《程序员》杂志社．程序员２００３合订本（下）ＦＭ］．北京：电子工业出版社，２００４． ［３］郭盈发，张红娟．数据库原理（第二版）［Ｍ］．西安：西安电子科技大学出版社，２００２ ［４］尹萍．ＳＱＬ Ｓｅｒｖｅｒ数据库性能优化［刀．计算机应用与软件，２００５，２２，（Ｏ３）．
因篇幅问题不能全部显示，请点此查看更多更全内容