SQL Server与Oracle数据库在查询优化上的差异

【IT专家网独家】一般来说，Oracle数据库在大型数据环境下，其运行性能比SQL Server数据库效率要高。单从查询优化上讲，两者就有比较大的差异。下面，笔者将数据库查询优化的差异做一个描述，若有不准确的地方，还请大家批评指教。

　　一、 在数据库排序查询优化上的差异。

　　在讲解这个内容之前，为了读者能够清楚我讲的内容，我要先谈一个概念。命中率，它是指从内存中取得数据而不从磁盘中取得数据的比率。我们在前几篇文章中都提到过，当在数据库中查询数据时，数据库服务器都是先从内存中寻找数据。只有在内存中数据不存在的情况下，才会去读取数据库文件的内容。而且，从内存中查询数据要比在数据库文件中查询数据效率高得多。从这方面讲，我们若能够提高这个查询的命中率，则显然可以提高数据库系统的查询效率。

　　虽然SQL Server与Oracle在这方面都有所作为，但是，笔者认为，Oracle数据库在这方面的优势比较明显。特别是Oracle数据库采用了临时段的管理机制，明显提高了数据库查询的命中率。

　　那什么叫作数据库的临时段呢?假设当我们刚查完员工信息表后，此时，员工信息表的内容就存在数据库服务器的内存中。此时，我们需要对这个表进行排序查询，如我们希望查询出工龄超过两年的员工，并且按工龄的长短进行排序。此时，Oracle数据库服务器会设法在内存中排序区对所有行进行排序。而这个排序区的大小则有数据库的初始化文件init.ora进行确定。当这个排序区不够大，不能够容纳我们所查询出来的员工信息记录数时，数据库就会在排序操作期间，在数据库服务器中开辟临时段。很明显，在查询操作过程中，若开辟临时段的话，会减低数据库的命中率，降低排序查询的效率。我们现在希望这个排序能够在内存中完成，而不需要开辟额外的临时段，如此的话，就可以消除向临时段写数据的开销，提高排序查询的效率。所以，当我们数据库中的数据比较庞大时，我们可以考虑增加这个排序区的长度，以避免临时段的需要。正是这个临时段的问题，如我们刚查询完员工信息表，查询完成之后，再对该表进行排序查询，就觉得好像仍然是新的查询一样。其实，这个排序查询的问题，我们可以通过一定的方法对此进行优化，以提高排序查询的效率。

　　修改方法：

　　第一步:先利用查询语句判断，是否有临时段需求的产生。

　　select * from v$sysstat where name='dtmfg(disk)' or name='dtmfg(memory)';

　　如我们可以定时利用以上语句，来查询是否有临时段需求的产生。这条语句中，dtmfg是具体的数据库实例名，我们只需要修改这个名字，就可以查询到我们需要的内容。若在查询结果中，发现有临时段需求产生的话，则就需要考虑修改相关的配置文件，以优化排序查询性能。

　　第二步：修改参数配置文件。

　　我们需要修改inint.ora文件，修改里面的SORT-AREA-SIZE的值。不过，修改这个配置文件之后，还必须重新启动数据库才会生效。一般情况下，数据库管理员需要定时查询这个临时段需求，然后根据情况，不断的进行调整，做好数据排序查询优化功能。

　　而在微软的SQL Server数据库中，笔者没有发现类似的功能。

　　二、 利用哈希联接，提高多表查询性能。

　　多表之间的关联查询，无论是哪种关联类型，到数据量比较大时，对于数据库服务器的查询性能都是一个非常大的考验。所以，在实际数据库设计中，当数据量比较大的时候，需要采用哈希联接，来提高数据库多表查询的效率。一般来说，哈希联接比其他几种表之间的连接方式，对于服务器来说开销要小得多，从而可以提高服务器的查询效率。

　　哈希联接一共有三种联接方式，分别为内存中的哈希联结、Gracle哈希联接与递归哈希联接。

　　所谓内存的哈希联接，是指先扫描或计算整个生成输入，然后在内存中生成哈希表。根据哈希键计算出哈希值，然后将每行插入哈希存储。如果整个生成输入比可用内存少，则可以将所有行都插入到哈希表中。生成阶段之后就是探测阶段。一次一行的3对整个探测输入进行扫描或者计算，并为每个探测行计算哈希健的值，并按一定的规则生成匹配项。

　　其他两种哈希联接也各有各的用途，在这里就不重复描述了。下面，笔者谈谈在这两个数据库中，对于哈希联接所持的不同态度。

　　在微软的SQL Server数据库中，默认情况下，是采用哈希联接的。在优化过程中并不能够确定到底采用上面的那种联接方式。所以，微软的数据库系统默认情况下，实采用内存中的哈希联接，然后再根据生成输入的大小逐渐转换到GRACLE哈希联接以及递归哈希联接。

　　但是，若是在数据量不大的情况下，哈希联接不但不会提高数据库的查询效率，反而会有所下降。所以，Oracle数据库在默认情况下，是没有启用哈希联接的，而是在实际需要时，可添加一些设置使得数据库在有多大联接查询发生时才利用哈希联结。也就是说，Oracle数据库可以设置判断条件，数据库管理员可以指定，当满足一定的条件时才调用哈希联结，利用它来提高多表查询的效率。

　　三、 大表查询优化。

　　一般来说，在数据库设计中，数据库设计人员会利用索引等技术来提高数据库的查询效率。但是，索引的作用，也不是无限扩大的，它受到一定的限制。一般来说，他跟数据量是成反比的，当数据量越大时，他的作用就越小。确切的说，刚开始时随着数据量的增大其对数据库查询的优化作用会逐渐增大;但是，当数据数量累积到一定程度时，其效果就会逐渐减小。当数据量达到一定的程度，如一百万条时，索引的作用就非常微小了。

　　针对这种大容量记录的表，若需要查询，其查询效率不高。为此，数据库该采用什么方式来提高这个大表的查询效率呢?

　　这两大数据库不约而同的采用了哈希族的方式，来提高大表的查询。

　　如现在在设计一个图书馆管理系统，这个系统中，读者的信息有几百万、几千万条。当读者的信息存储在一个普通表中的时候，这些记录按照存储到数据库中的先后顺序，物理地保存到分配的块中。也就是说，数据库服务器的数据文件，或者数据表，就好像一个个抽屉，数据库是按先后顺序一条记录一条记录地从上到下存放数据。当整个表的容量逐渐增加时，该表相应的速度就会非常慢。

　　在刚开始的时候，人们想到了利用镞表的方式来提高这个性能。也就是说，把整个抽屉分成几排，然后每排给他们归一类，如按办图书卡时的年龄进行分类，10岁以下的一类，10岁到15岁的一类等等。如此的话，在存读者信息的时候，就不会简单的按照办卡时间来存储，而是按照类别来存储。如果是属于10岁到15岁整个类，就会被物理的存储在同一个系列的块中。如此的话，就可以分类查找信息的速度。如果可以按类别查找数据信息，速度会非常快。

　　但是，随着数据库中的镞块增加，会影响数据库的整体运行性能。这个问题发生之后，数据库开发人员又想到了利用哈希函数来解决这个问题。哈希函数将会给定一个数值用来限定镞块数的数量的预计范围。

　　也就是说，现在我们要建立一个图书馆用户的表格，我们可以利用图书卡的卡号作为镞主键将有利于数据的存储分布。但是，当读者增加时，就需要使用一个哈希函数来约束镞块的数量。

　　不过Oracle数据库在使用镞技术来优化大表数据查询之外，还采用了另外一种独有的技术，即分区表的形式，来提高用户对于大表的查询效率。

　　在Oracle数据库中，可以将一个大表分开放置在几个逻辑分区中，或者是将一个大表分成几张小表。在查询时，即可以单独的对这些小表进行查询，而且，也可以利用union all参数进行一起查询。

　　如在设计销售订单管理系统时，我们可以按年度把销售订单表分割成几张小表，如此的话，后续的查询效率会比一张大表高很多。不过，这个技术的应用，关键在于如何对表进行分割，以及如何把表放置在几个逻辑分区中，这需要有一定经验的数据库设计工程师才能设计出一个好的方案。

四、在SQL语句上的查询优化。

　　前台的应用程序要在数据库服务器上起作用，最终靠的都是应用程序中的一条条SQL语句。据不完全统计，SQL语句消耗了数据库服务器80%左右的资源。所以，如何提高SQL语句的执行效率，是在数据库查询优化中必须要考虑的一个问题。

　　但是，在实际工作中，许多程序员有个误区，他们认为数据库查询优化不是他们的事情，而应该是数据库管理系统的任务，这是很多程序员的一个错误认识。他们错误地认为，他们所开发的应用程序的性能，跟他们所编写的SQL语句关系不大。一个好的查询语句往往可以使得应用程序的性能提高数十倍，而且，随着记录量的增加，这个效果还会以几何级数上升。

　　另外，SQL语句是独立于程序设计逻辑的，也就是说，无论你的业务逻辑是怎么设计的，最后分解成SQL语句，就是那么几个语法，所以，相对于应用程序源代码的优化，SQL查询语句的优化在时间与风险上，都要低许多。

　　对于SQL语句的优化，Oracle数据库与SQL Server数据库有类似的地方，也有一些差异。下面笔者就这两者的差异与共同点做一个综合介绍。

　　1、 通过索引来提高SQL语句的执行效率。

　　一般来说，对于一些经常需要查询的表，如产品信息表，我们可以通过建立外键来提高查询效率。但是，也不是说每个字段都要指定为外键。对于一些没有指定外键的字段，我们可以为其建立索引，来提高数据表的查询效率。

　　一般情况，在以下几种情况下，我们可以为表建立索引来提高SQL语句的执行效率。

　　一是对于一些经常需要查询的表，我们出于某种考虑，没有设置外键，而是通过设置索引来提高对于表的查询效率。在数据库表中，外键的设置往往受到一些限制;而相对于外键来说，索引的限制则要小得多。所以，在一些不使用外键的情况下，我们可以采用索引来提高对于表的查询效率。

　　二是在需要频繁进行排序或者分组的表上，建立索引，可以极大地提高查询效率。如ERP系统在设计的时候，可能需要频繁地查询采购订单明细，而且，这份报表是需要根据采购订单的号码进行排序。如此的话，在数据库设计的时候，就可以把采购订单的号码设置为索引，在每次运行采购订单明细作业的时候，前台ERP程序的性能就会高许多。而有时候，可能需要按供应商来统计当天的进货金额，此时，最好能够在进货明细表中，给供应商字段添加索引，这对于提高当天进货汇总表作业的运行效率，会有非常大的帮助。总之，在分组查询或者排序查询的表上，设置索引对于提高应用程序的整体性能，具有不可忽视的作用。

　　三是如果待排序的列有多个，则需要在这些列上建立复合索引。如前台应用程序在生成当天的进货明细表时，需要按供应商、采购订单号、产品编号进行排序。此时，也就是说，在生成进货明细表这份报表时，要按这三个字段进行排序。遇到这种情况时，对这些字段建立复合索引，提高查询效率，是一个不错的选择。

　　以上这些SQL语句优化，Oracle数据库与SQL Server数据库都可以实现。虽然具体的实现语句可能稍有区别，但都是换汤不换药，没有本质区别。

　　2、 把索引与数据文件存放在不同的磁盘中。

　　当索引或者数据库文件比较庞大时，把他们放在同一个磁盘中会加大输入输出等竞争，从而抵消了索引的作用。为了解决过多的索引导致输入输出效率降低的问题，在数据库设计的时候，最好把索引跟用户的表空间建立在不同的磁盘中。如把数据库的表空间建立在一块硬盘中，而把索引建立在另外一块硬盘中。如此的话，就可以明显地降低输入输出竞争。也就是说，这样设计，随着索引的增加，不会导致输入输出效率的低下。

　　不过，根据笔者的了解，索引与数据文件存放在不同的磁盘中，现在好像只有Oracle 数据库可以做到，而微软的SQL Server数据库则无法实现这一点。

　　这也许根他们的定位不同。甲骨文的数据库系统是针对大型的数据库应用而设计，所以，对于查询的效率要求更加高。

　　3、 合理利用群集索引来提高SQL语句的执行效率。

　　在一些特殊情况下，我们需要用到群集索引。如在ERP系统中，采购部门经常需要按月来查询采购订单明细。如需要查询2008年8月份的采购订单明细，而且这份报表需要按照供应商、采购订单号码、产品品号、交货期等进行排序，有时候还需要对供应商进行汇总。这一份简单的报表，用到了范围查询、多个字段记录排序、记录汇总等技术。此时，若能够建立群集索引的话，对于提高这份报表的查询效率，具有非常明显的效果，特别是在数据记录比较多的情况下，效果特别明显。

　　所谓的群集索引与非群集索引的区别，主要是在于数据存放记录上的差异。若我们采用群集索引的话，在存放记录的时候，会按群集索引指定的规则存放。如对于采购订单中的供应商ID字段采用群集索引，则在存放记录的时候，会把相同的供应商存放在一起。如此的话，在查询的时候，效率就会高得多。而若没有采用群集索引的话，则记录保存时就是按记录保存的先后顺序来进行记录的存储。

　　在建立群集索引的时候，Oracle 数据库有一个，就是必须在数据库表建立的时候，数据还没导入之前就建立群集索引。也就是说，若数据库表中有记录的话，则无法建立群集索引，这一点我们需要引起注意。

　　同时，若给某个表中的字段建立了群集索引，在记录保存时，为了能够按照群集索引所指定的规则存储数据，需要对数据表中的记录进行一些调整，以符合原有的规则，如此的话，就会让数据库进行一些额外的动作，从而影响数据库的性能。如在建立某个供应商的采购记录时，为了把相同供应商的记录保存在一起(如我们把供应商ID设置为群集索引)，就需要调整原有的记录存储结构。虽然在保存的时候，牺牲一点效率，但是，这对于后续数据查询，效率就会高许多。所以，对于群集索引的话，要让其取得比较高的效果，有一个应用前提，就是这个表中的数据要是经常查询的。如在ERP系统中，有一个库存历史交易报表，这个查询就会经常用到，而且，在查询的过程中，都需要用到范围查询、排序、汇总等功能。所以，用在库存历史交易等数据库表中，则效果会好得多。

　　若利用一句话来区分群集索引与非群集索引的区别，那就是群集索引“更新慢，查询难快”。

　　在实际应用中，如果利用SQL Server设计数据库系统的过程中，很少用到群集索引技术(根据笔者的了解)。而在Oracle数据库系统中，则应用的相对比较广泛一点。

　　不过，两个数据库在群集索引上都有一个共同点，就是要利用索引的话，必须在数据表建立的时候，就要设置群集索引。当数据库中有记录的话，是不能建立群集索引的。

　　说起区别，具体的实现语法有点差异，但是没有什么本质的区别。另外，对于甲骨文的数据库来说，可以把群集索引跟数据库文件存放在不同的磁盘中，从而提高输入输出效率。但是，微软的SQL Server数据库则不行。

　　4、 使用Oracle数据库自带的优化器优化SQL语句。

　　在Oracle数据库中，自带了一个SQL语句的优化工具，Oracle语句优化器。利用这个工具，可以提高SQL语句的执行效率。

　　一方面，Oracle数据库语句优化器是跟行锁管理工具一起使用的，两者往往需要配合使用，才能够起到意想不到的作用。另一方面，对于“扶不起的阿斗”，Oracle语句优化器也无能为力。也就是说，对于一些实在写得很糟糕的SQL语句，语句优化器对其也没有丝毫办法;只有对一些本来就比较合理的SQL语句，语句优化器与行锁管理工具，才能够在这个基础之上，再找到一些可以改善的地方，然后提出可行的改善意见。

　　具体来说，语句优化器，一方面确定SQL语句的最小代价执行计划，同时，确定数据的访问路径，如是否采用索引或者表扫描;采用合理的表连接方式以及顺序;判断索引不可使用时是否需要进行排序等等。综合以上因素，然后给我们提出一个改善的建议。

　　在实际工作中，语句优化器可以给我们找出一个SQL语句优化过程中的盲点。而这个工具是微软SQL Server数据库所缺乏的，或者跟甲骨文的数据库比起来，有差距的地方。