基于成本的数据库查询优化浅析

基于成本的数据库查询优化浅析
作者：崔育礼尹长青
来源：《电脑知识与技术》2008年第34期
摘要：主要介绍基于成本的数据库查询优化的一些基本概念，针对多表连接的三种方法：嵌套循环链接、归并连接和混合连接进行分析和阐述，并成本估算，列出估算公式。

关键词：数据库系统；成本；存取路径；连接顺序；连接方法；NLJ；MJ；Hybrid Join
中图分类号：TP311文献标识码：A文章编号：1009-3044(2008)34-1540-02
On Cost Based Database Query Optimization
CUI Yu-li, YIN Chang-qing
(School of Software Engineering, Tongji University, Shanghai 201804, China)
Abstract: Mainly presents some basic concept of database query optimization, and analyze the three multi-table join methods: nested loop join, merge join and hybrid join，give the cost of each join methods.
Key words: DBMS; cost; access path; join sequence; join methods; NLJ; MJ;Hybrid Join
1 引言
目前，数据库系统是管理信息系统的核心。

从大多数管理信息系统的数据库操作来看，查询操作在各种数据库操作中所占据的比重最大，因而提高查询的效率，优化数据库查询便成了提高数据库管理系统乃至管理信息系统的关键所在。

举例来说，如果数据量累积到一定程度，比如一个银行的账户数据库表信息累积到上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。

如果采用比全表扫描更好的查询策略，往往可以使查询时间大大缩短，由此可见查询优化技术的重要性。

数据库查询优化器是RDBMS的一个重要组成部分。

对于基于成本的优化，数据库查询优化器的任务是，通过产生可供选择的执行计划，找到最低估算成本的执行计划，来优化一条SQL语句。

它在SQL语句性能表现上扮演了至关重要的角色。

本文就基于多表连接的三种方法简单估算一下成本开销，来说明基于成本的数据库查询优化。

2 成本含义
成本表示优化器对执行语句所用时间的最优估计。

当一条SQL语句被输入RDBMS服务器，它将会被解析并提交给数据库查询优化器。

查询优化器将会对其进行查询重写和表达式评估，以产生可供选择的执行计划。

对于每个待选的执行计划，数据库优化器根据相应的统计量进行估计每个执行计划的成本，成本估计最小的执行计划将被选取用来执行SQL语句。

在实际的数据库产品(如Oracle、Sybase、DB2等)的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表所得到的信息来估计不同的执行计划的成本，然后选择一个较优的执行计划。

成本主要由CPU Cost 和I/O Cost两部分组成，具体图示如图1。

其中：Base cost 是CPU初始化花费的时间，是确定的。

Page cost 是缓冲池中定位请求页所花费时间。

Scan cost 是扫描每条记录的时间。

Row cost 是复制记录到线程私有存储空间所花费的时间。

I/O cost 是从硬盘中读取所需页面所花费的时间。

3 存取路径
那么什么是存取路径呢？当将 SQL 语句提交给 DBMS 执行时，DBMS 会“实时”地为动态SQL 语句设计出存取路径。

对分析者而言，在执行每条 SQL 语句之前，无法检查 DBMS 为这些语句所选择的存取路径。

当DBMS优化器为每条 SQL 语句创建优化的存取路径时，可以挑选各种不同的技术。

查询优化器将主要执行两个步骤来最终决定选择哪个存取路径：1) 列举出可能的存取路径；2) 对列举出的存取路径成本评估，选择出最快的一个存取路径。

影响存取路径的因素主要有以下几个方面：连接顺序, 连接方法, 存取方法等，本文就具体针对表连接做个简单的分析。

4 连接的理解
多张数据表查询时就要用到连接操作，连接表的顺序及连接的算法将极大地影响这数据库查询的速度和效率。

数据库优化器提供了一系列技术来用于连接表数据。

当在 FROM 子句中引用多个数据库表（或指定了 JOIN 子句）时，SQL 会请求DBMS执行连接操作。

那么DBMS优化器是如何做这件事？每个多表查询会分解成数个单独的存取路径。

为完成此连接操作， DBMS优化器首先选择一个连接顺序，DBMS优化器先根据它认为是最优的连接方式来进行选择其中的两张表并创建一条经过优化的存取路径，然后，优化器继续连接其它表，直到优化完整条查询。

其次，在连接表时，优化器将必须确定要使用的最佳连接算法。

连接算法（或连接方法）定义了组合表的基本过程。

每种连接方法的运行方式各不相同，但可得出相同的结果。

然而，连接方法的选用会极大地影响到连接性能。

每种连接方法通常都涉及一些特定的基本步骤。

通常，首先确定先处理哪个表。

称这个表为外表。

做出决定之后，对该外表执行一系列的操作，为连接做准备。

然后，将该表中的各行与第二个表（称之为内表）进行组合。

另外，还要对内表执行的一系列操作，这可以在连接发生之前进行，也可以连接发生时进行，或者在这两者时进行。

优化器知道每种方法的优缺点，知道采用哪种方法会怎样影响到性能。

根据系统目录中的当前统计，优化器还知道哪些表最适合做内表，哪些表最适合做外表。

以下是查询优化器所要考虑的一些基本规律：
1) 表越小，越有可能被选为外表。

这有助于减少必须再次访问内表的次数。

2) 如果选择谓词可以应用到某个表，则该表更适合于被选作外表
3) 如果可能对其中某个表做索引查找，则该表很适合于作为内表。

4) 在连接操作中，重复元素最少的表倾向于被选作外表。

当然，这些不是固定不变的规则。

最后，优化器将根据详细的代价估计来选择外表和内表。

4.1 连接顺序
通常，查询的连接顺序很大程度上决定了查询的执行性能，因为越早地过滤行，效率越高。

表与表之间的连接顺序满足交换律（t1■t2 = t2■t1）和结合律（（t1■t2）■t3 =t1■
（t2■t3）），假设有n个表，那么这n个表的连接顺序数目就是n！，可想而知，随着n的增大，连接顺序的个数将迅速增加，以致无法全部枚举所有连接顺序，一般只考虑左深树连接循序。

左深树连接顺序的简明特点就是每一个连接的右孩子节点都是一个单独的表，而不是由其他表连接后的结果。

4.2 连接方法
通常可以采用三类连接方法：嵌套循环连接（nested loop join）、归并连接（merge join）和混合连接（hybrid join）。

每种连接方法的运行方式各不相同。

DBMS优化器会基于一组统计，选择连接方法，以求可以达到最佳性能。

1) 嵌套循环连接（nested loop join，NLJ）。

要执行 NLJ，首先要确定一个驱动表(outer table)，另一个表为inner table，先在外表中确定符合条件的行，然后扫描内表来搜索匹配。

驱动表中的每一行与inner表中的相应的记录JOIN，在完成对内表的扫描之后，再在外表中确定另一符合条件的行。

然后，再扫描内表以查找匹配，如此反复，类似一个嵌套的循环。

该连接方法适用于驱动表的记录集比较小（
cost= I/O cost + CPU cost
= I/O cost + (inner access cost * outer cardinality)
2)归并连接（merge join，MJ），又叫排序归并连接（sort merge join，SMJ）。

用 MJ 时，需要按照连接谓词对要连接的表进行排序。

这意味着必须按照指定连接标准的列的顺序访问每个表。

这个顺序可以用排序或索引式访问来实现。

在确保对外表和内表正确排序之后，按照顺序读取每个表，然后匹配连接列。

在归并扫描连接中，每个表只扫描一遍。

图示如图3。

cost = I/O cost + cpu cost
= I/O cost + (inner access cost + outer access cost + qualified row * sort record size + workfile access cost)
3) 混合连接（hybrid join）。

■
混合连接主要有以下几个步骤：
1) 采用有效的单表存取方法存取外表。

（下转第1544页）
（上接第1541页）
2) 将外表的数据与内表索引的rid连接，拼成rid list。

3) 如果内表的索引没有很好的聚集，对中间表和rid list进行排序。

4) 根据rid list 预取内表。

5) 将内表与外表匹配的记录与外表记录连接。

Cost = I/O cost + cpu cost
= I/O cost + (optional sort cost + semi_join cost + workfile access cost)
5 结论
数据库性能直接影响整个应用系统的性能。

在数据库管理和开发过程中，优化设计可以提高数据库的性能，特别是大型数据库，优化过程更为重要，不仅可以提高查询相应速度，还可以减少对内存的需求。

oracle、db2等数据库系统都十分注重数据库查询优化技术，另外，基于成本估算的数据库查询优化技术已被大多数数据库系统所采用。

对数据库查询优化的概念有所了解之后，又着重分析了连接的三种方法，对其成本进行了估算，以利于写出较优的查询语句，提高数据库查询效率。

参考文献：
[1] Dennis S,Bonnet P.数据库性能调优：原理与技术[M].北京:电子工业出版社,2004.
[2] Silberschatz A,Korth H F,Sudarshan S.Database Systems Concepts[M].北京:机械工业出版社,2006.
[3] 萨师煊,王珊.数据库系统概论[M].北京:高等教育出版社,2002.
[4] ibm技术网站.数据库性能调优专题
[EB/OL]./developerworks/cn/db2/zones/performance/.。