设为首页 加入收藏

1. 引言

数据库系统是管理信息系统的核心，基于数据库的联机事务处理(OLTP)和联机分析处理(OLAP)是各使用单位最为重要的计算机应用之一。从大多数系统的应用实例来看，查询、分析、统计是系统的最终应用，而查询、分析、统计操作所基于的SELECT语句在SQL语句中又是付出资源代价最大的语句。举个具体的例子，比如一个数据库表有上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟甚至几秒钟，由此可见查询优化技术的重要性。

2. 数据库应用现状

随着网络与信息技术的突飞猛进，信息出现了爆炸式增长。地理信息系统及科考应用的工程数据库要求在1 TB以上；通信业的数据库要求1 GB以上；电子商务、产业信息化等企业级应用，尤其用到数据仓库技术的一类数据库，要求的支撑数据库也有几百、几千兆字节容量，数据库的并行访问需求大大提高，系统要求支持多用户协同工作。面对海量数据，企业级应用的性能愈来愈依赖数据库的性能，企业海量数据库优化的重要性日益突现。

在数据量愈来愈大，并行访问的用户愈来愈多的情况下，经济有效地提高数据库系统的吞吐量和减少事务的响应时间成为数据库系统发展的关键问题。在企业级的实际解决方案中，在很大程度上应用合理与否决定数据库性能。要做到应用合理，语句是重点。是面向结果语言，用户只要学会抽象的语句，具体内部实现由厂商实现并改进。然而具体应用是否合理却是工程人员的事情，而且应用合理与否对性能影响明显。

3. 海量数据查询工作原理

一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。DBMS(数据库管理系统)处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如MS SQLServer)的高版本中都是采用“基于语法的查询优化器”和“基于开销的查询优化器”。“基于语法的查询优化器”为获得对SQL查询的应答结果创建一个过程计划，但是它选择的特定计划取决于查询的确切语法及查询中的子句顺序。无论数据库中记录的数目或组合是否随时间变化而更改，基于语法的查询优化器每次都执行同样的计划。与基于开销的查询优化器不同，它不查看或维护数据库的统计记录。“基于开销的查询优化器”在备选计划中选择应答SQL查询的计划^[1]。选择是基于对执行特殊计划的开销估算(I/O操作数、CPU秒数，等等)而做出的。它通过记录表或索引中记录的数目和构成的统计数字估算这些开销。与基于语法的查询优化器不同，它不依赖于查询的确切语法或查询中的子句顺序。虽然现在的数据库产品在查询优化方面已经做得越来越好，但由用户提交的SQL语句是系统优化的基础，很难设想一个原本糟糕的查询语句经过系统的优化之后会变得高效，因此用户所写语句的优劣至关重要^[2]。“基于开销的查询优化器”的优化方法我们暂不讨论，下面重点说明“基于语法的查询优化器”的解决方案。

4. 优化策略

4.1. 合理使用索引

索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：

1) 在经常进行连接，但是没有指定为外键的列上建立索引。

2) 在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。

3) 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没有必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

4) 如果待排序的列有多个，可以在这些列上建立复合索引(compound index)。

5) 不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

6) 对查询型的表，建立多个索引会大大提高查询速度，对更新型的表，如果索引过多，会增大开销。

4.2. 避免或简化排序

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：

1) 索引中不包括一个或几个待排序的列；

2) group by或order by子句中列的次序与索引的次序不一样；

3) 排序的列来自不同的表。

为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表(尽管有时可能影响表的规范化，但相对于效率的提高是值得的)。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

4.3. 消除对大型表行数据的顺序存取

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：

SELECT*

FROM Orders WHERE (customer_num = 104 AND order_num > 1001) OR order_num = 1008

虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：

SELECT * FROM orders WHERE customer_num = 104 AND order_num > 1001 UNION SELECT * FROM orders WHERE order_num = 1008

这样就能利用索引路径处理查询。

4.4. 避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

4.5. 避免困难的正规表达式

某些关键字的应用是正确的，技术上叫正规表达式，但有时搭配不当会非常耗费时间，特别是在大型数据表中体现的尤为突出，我们把这种正规表达式称为困难的正规表达式。

1) 支持通配符的CHARINDEX和LIKE关键字。例如：

SELECT * FROM table1 WHERE user_id LIKE '98___'

即使在user_id字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM table1 WHERE user_id > '98000'在执行查询时就会利用索引来查询，显然会大大提高速度。

比如查找用户名包含有“c”的所有用户，可以用 

SELECT * FROM table1 WHERE user_name LIKE '%c%'

下面是完成上面功能的另一种写法：

SELECT * FROM table1 WHERE CHARINDEX ('c',user_name) > 0

这种方法理论上比上一种方法多了一个判断语句，即>0，但这个判断过程是最快的，我想信80%以上的运算都是花在查找字符串及其它的运算上。用这种方法也有好处，那就是对“%”、“|”等在不能直接用LIKE查找到的字符中可以直接在这CHARINDEX中运用，如下：

SELECT * FROM table1 WHERE CHARINDEX ('%',user_name) > 0 2) 少使用“*”。例如语句：

SELECT COUNT(*) FROM table1

这时用“*”和一个实际的列名得到的都是一个行数的结果，但是用“*”会统计所有列，显然要比用一个实际的列名效率慢。同样，尽管很多开发人员都习惯采用“SELECT * FROM TBL”的模式进行查询，但是为了提高系统的效率，如果你只需要其中某几个字段的值的话，最好把这几个字段直接写出来。

3) 尽量不要在WHERE子句中对字段使用函数或参与表达式计算，这样会导致无法使用索引进行全表扫描。

4) 不要使用NOT。查询时可以在WHERE子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

WHERE NOT (col = 'VALID')

NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：

SELECT * FROM table1 WHERE user_id<>3000；

对这个查询，可以改写为不使用NOT：

SELECT * FROM table1 WHERE user_id < 3000 OR user_id > 3000；

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许对user_id列使用索引，而第一种查询则不能使用索引。

5) IN和EXISTS。EXISTS要远比IN的效率高，里面关系到full table scan和range scan。同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT(不能使用索引而降低速度)，但是NOT EXISTS要比NOT IN查询效率更高。

6) 慎用游标。在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

7) 在海量查询时尽量少用格式转换。

8) IN、OR子句常会使工作表的索引失效。如果不产生大量重复值，可以考虑把子句拆开，拆开的子句中应该包含索引。

4.6. 使用临时表加速查询

把表的一个子集进行排序并创建临时表，有时能加速查询。有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：

SELECT cust.name, rcvbles.balance FROM cust, rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance > 0 AND cust.postcode > '98000'

ORDER BY cust.name

如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：

SELECT cust.name, rcvbles.balance FROM cust, rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance > 0 ORDER BY cust.name INTO TEMP cust_with_balance

然后以下面的方式在临时表中查询：

SELECT * FROM cust_with_balance WHERE postcode > '98000'

临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。

注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

4.7. 用排序来取代非顺序存取

非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。

5. 实例分析

下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括3个表，模式如下所示：

1) part表

2) vendor表

3) parven表

下面的查询将在这些表上定期运行，并产生关于所有零件数量的报表：

SELECT part_desc，vendor_name，part_amount FROM part，vendor，parven WHERE part.part_num = parven.part_num

          AND parven.vendor_num = vendor.vendor_num ORDER BY part.part_num

如果不建立索引，上述查询代码的开销将十分巨大。为此，我们在零件号和厂商号上建立索引。索引的建立避免了在嵌套中反复扫描。关于表与索引的统计信息如下：

看起来是个相对简单的3表连接，但是其查询开销是很大的。通过查看系统表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照物理顺序存放的。parven表没有特定的存放次序。这些表的大小说明从缓冲页中非顺序存取的成功率很小。此语句的优化查询规划是：首先从part中顺序读取400页，然后再对parven表非顺序存取1万次，每次2页(一个索引页、一个数据页)，总计2万个磁盘页，最后对vendor表非顺序存取1.5万次，合3万个磁盘页。可以看出在这个索引好的连接上花费的磁盘存取为5.04万次。

实际上，我们可以通过使用临时表分3个步骤来提高查询效率：

1) 从parven表中按vendor_num的次序读数据：

SELECT part_num, vendor_num, price FROM parven ORDER BY vendor_num INTO temp pv_by_vn

这个语句顺序读parven(50页)，写一个临时表(50页)，并排序。假定排序的开销为200页，总共是300页。

2) 把临时表和vendor表连接，把结果输出到一个临时表，并按part_num排序：

SELECT pv_by_vn, *vendor.vendor_num FROM pv_by_vn, vendor WHERE pv_by_vn.vendor_num = vendor.vendor_ num ORDER BY pv_by_vn.part_num INTO TMP pvvn_by_pn DROP TABLE pv_by_vn

这个查询读取pv_by_vn(50页)，它通过索引存取vendor表1.5万次，但由于按vendor_num次序排列，实际上只是通过索引顺序地读vendor表(40 + 2 = 42页)，输出的表每页约95行，共160页。写并存取这些页引发5 × 160 = 800次的读写，索引共读写892页。

3) 把输出和part连接得到最后的结果：

SELECT pvvn_by_pn.*, part.part_desc FROM pvvn_by_pn, part WHERE pvvn_by_pn.part_num = part.part_num DROP TABLE pvvn_by_pn

这样，查询顺序地读pvvn_by_pn(160页)，通过索引读part表1.5万次，由于建有索引，所以实际上进行1772次磁盘读写，优化比例为30:1。笔者在Informix Dynamic Sever上做同样的实验，发现在时间耗费上的优化比例为5:1(如果增加数据量，比例可能会更大)。

6. 总结

20%的代码用去了80%的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。对于数据库应用程序来说，重点在于SQL的执行效率，所谓优化的重点环节即WHERE子句利用了索引，不可优化即发生了全表扫描或额外开销。经验显示，数据库性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能降低问题常常是由这些方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免全表搜索的发生。其实SQL的查询性能优化是一个复杂的过程，上述这些只是在应用层面的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

参考文献 (References)

[1]       汪照东. Oracle 11g数据库管理与优化宝典[M]. 北京: 电子工业出版社, 2008.

[2]       谭怀远. 让Oracle跑得更快2——基于海量数据的数据库设计与优化[M]. 北京: 电子工业出版社, 2011.