前言
原文地址:http://blog.jobbole.com/100349/
国内大佬翻译的文章,因为文章较长,不适合碎片化阅读,因此分为几篇文章来转载,满满的干货,外链在微信上不能显示,建议从第一篇文章开始看起
所有的现代数据库都在用基于成本的优化(即CBO)来优化查询。道理是针对每个运算设置一个成本,通过应用成本最低廉的一系列运算,来找到最佳的降低查询成本的方法。
为了理解成本优化器的原理,我觉得最好用个例子来『感受』一下这个任务背后的复杂性。这里我将给出联接 2 个表的 3 个方法,我们很快就能看到即便一个简单的联接查询对于优化器来说都是个噩梦。之后,我们会了解真正的优化器是怎么做的。
对于这些联接操作,我会专注于它们的时间复杂度,但是,数据库优化器计算的是它们的 CPU 成本、磁盘 I/O 成本、和内存需求。时间复杂度和 CPU 成本的区别是,时间成本是个近似值(给我这样的懒家伙准备的)。而 CPU 成本,我这里包括了所有的运算,比如:加法、条件判断、乘法、迭代……还有呢:
使用时间复杂度就容易多了(至少对我来说),用它我也能了解到 CBO 的概念。由于磁盘 I/O 是个重要的概念,我偶尔也会提到它。请牢记,大多数时候瓶颈在于磁盘 I/O 而不是 CPU 使用。
在研究 B+树的时候我们谈到了索引,要记住一点,索引都是已经排了序的。
仅供参考:还有其他类型的索引,比如位图索引,在 CPU、磁盘I/O、和内存方面与B+树索引的成本并不相同。
另外,很多现代数据库为了改善执行计划的成本,可以仅为当前查询动态地生成临时索引。
注:由于所有存取路径的真正问题是磁盘 I/O,我不会过多探讨时间复杂度。
如果你读过执行计划,一定看到过『全扫描』(或只是『扫描』)一词。简单的说全扫描就是数据库完整的读一个表或索引。就磁盘 I/O 而言,很明显全表扫描的成本比索引全扫描要高昂。
其他类型的扫描有索引范围扫描,比如当你使用谓词 ” WHERE AGE > 20 AND AGE < 40 ” 的时候它就会发生。
当然,你需要在 AGE 字段上有索引才能用到索引范围扫描。
在第一部分我们已经知道,范围查询的时间成本大约是 log(N)+M,这里 N 是索引的数据量,M 是范围内估测的行数。多亏有了统计我们才能知道 N 和 M 的值(注: M 是谓词 “ AGE > 20 AND AGE < 40 ” 的选择率)。另外范围扫描时,你不需要读取整个索引,因此在磁盘 I/O 方面没有全扫描那么昂贵。
如果你只需要从索引中取一个值你可以用唯一扫描。
多数情况下,如果数据库使用索引,它就必须查找与索引相关的行,这样就会用到根据 ROW ID 存取的方式。
例如,假如你运行:
SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28
如果 person 表的 age 列有索引,优化器会使用索引找到所有年龄为 28 的人,然后它会去表中读取相关的行,这是因为索引中只有 age 的信息而你要的是姓和名。
但是,假如你换个做法:
SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON
WHERE PERSON.AGE = TYPE_PERSON.AGE
PERSON 表的索引会用来联接 TYPE_PERSON 表,但是 PERSON 表不会根据行ID 存取,因为你并没有要求这个表内的信息。
虽然这个方法在少量存取时表现很好,这个运算的真正问题其实是磁盘 I/O。假如需要大量的根据行ID存取,数据库也许会选择全扫描。
我没有列举所有的存取路径,如果你感兴趣可以读一读 Oracle文档。其它数据库里也许叫法不同但背后的概念是一样的。
那么,我们知道如何获取数据了,那现在就把它们联接起来!
我要展现的是3个个常用联接运算符:合并联接(Merge join),哈希联接(Hash Join)和嵌套循环联接(Nested Loop Join)。但是在此之前,我需要引入新词汇了:内关系和外关系( inner relation and outer relation) 【译者注: “内关系和外关系” 这个说法来源不明,跟查询的“内联接(INNER JOIN) 、外联接(OUTER JOIN) ” 不是一个概念 。只查到百度百科词条:关系数据库 里提到“每个表格(有时被称为一个关系)……” 。 其他参考链接 “Merge Join” “Hash Join” “Nested Loop Join” 】 。 一个关系可以是:
当你联接两个关系时,联接算法对两个关系的处理是不同的。在本文剩余部分,我将假定:
比如, A JOIN B 是 A 和 B 的联接,这里 A 是外关系,B 是内关系。
多数情况下, A JOIN B 的成本跟 B JOIN A 的成本是不同的。
在这一部分,我还将假定外关系有 N 个元素,内关系有 M 个元素。要记住,真实的优化器通过统计知道 N 和 M 的值。
注:N 和 M 是关系的基数。
嵌套循环联接是最简单的。
道理如下:
下面是伪代码:
nested_loop_join(array outer, array inner)
for each row a in outer
for each row b in inner
if (match_join_condition(a,b))
write_result_in_output(a,b)
end if
end for
end for
由于这是个双迭代,时间复杂度是 O(N*M)。
在磁盘 I/O 方面, 针对 N 行外关系的每一行,内部循环需要从内关系读取 M 行。这个算法需要从磁盘读取 N+ N*M 行。但是,如果内关系足够小,你可以把它读入内存,那么就只剩下 M + N 次读取。这样修改之后,内关系必须是最小的,因为它有更大机会装入内存。
在CPU成本方面没有什么区别,但是在磁盘 I/O 方面,最好最好的,是每个关系只读取一次。
当然,内关系可以由索引代替,对磁盘 I/O 更有利。
由于这个算法非常简单,下面这个版本在内关系太大无法装入内存时,对磁盘 I/O 更加有利。道理如下:
可能的算法如下:
// improved version to reduce the disk I/O.
nested_loop_join_v2(file outer, file inner)
for each bunch ba in outer
// ba is now in memory
for each bunch bb in inner
// bb is now in memory
for each row a in ba
for each row b in bb
if (match_join_condition(a,b))
write_result_in_output(a,b)
end if
end for
end for
end for
end for
使用这个版本,时间复杂度没有变化,但是磁盘访问降低了:
哈希联接更复杂,不过在很多场合比嵌套循环联接成本低。
哈希联接的道理是:
在时间复杂度方面我需要做些假设来简化问题:
时间复杂度是 (M/X) * N + 创建哈希表的成本(M) + 哈希函数的成本 * N 。 如果哈希函数创建了足够小规模的哈希桶,那么复杂度就是 O(M+N)。
还有个哈希联接的版本,对内存有利但是对磁盘 I/O 不够有利。 这回是这样的:
3.合并联接
合并联接是唯一产生排序的联接算法。
注:这个简化的合并联接不区分内表或外表;两个表扮演同样的角色。但是真实的实现方式是不同的,比如当处理重复值时。、
1.(可选)排序联接运算:两个输入源都按照联接关键字排序。
2.合并联接运算:排序后的输入源合并到一起。
排序
我们已经谈到过合并排序,在这里合并排序是个很好的算法(但是并非最好的,如果内存足够用的话,还是哈希联接更好)。
然而有时数据集已经排序了,比如:
这部分与我们研究过的合并排序中的合并运算非常相似。不过这一次呢,我们不是从两个关系里挑选所有元素,而是只挑选相同的元素。道理如下:
因为两个关系都是已排序的,你不需要『回头去找』,所以这个方法是有效的。
该算法是个简化版,因为它没有处理两个序列中相同数据出现多次的情况(即多重匹配)。真实版本『仅仅』针对本例就更加复杂,所以我才选择简化版。
如果两个关系都已经排序,时间复杂度是 O(N+M)
如果两个关系需要排序,时间复杂度是对两个关系排序的成本:O(N*Log(N) + M*Log(M))
对于计算机极客,我给出下面这个可能的算法来处理多重匹配(注:对于这个算法我不保证100%正确):
mergeJoin(relation a, relation b)
relation output
integer a_key:=0;
integer b_key:=0;
while (a[a_key]!=null and b[b_key]!=null)
if (a[a_key] < b[b_key])
a_key++;
else if (a[a_key] > b[b_key])
b_key++;
else //Join predicate satisfied
write_result_in_output(a[a_key],b[b_key])
//We need to be careful when we increase the pointers
if (a[a_key+1] != b[b_key])
b_key++;
end if
if (b[b_key+1] != a[a_key])
a_key++;
end if
if (b[b_key+1] == a[a_key] && b[b_key] == a[a_key+1])
b_key++;
a_key++;
end if
end if
end while
如果有最好的,就没必要弄那么多种类型了。这个问题很难,因为很多因素都要考虑,比如:
想要更详细的信息,可以阅读DB2, ORACLE 或 SQL Server)的文档。
我们已经研究了 3 种类型的联接操作。
现在,比如说我们要联接 5 个表,来获得一个人的全部信息。一个人可以有:
换句话说,我们需要用下面的查询快速得到答案:
SELECT * from PERSON, MOBILES, MAILS,ADRESSES, BANK_ACCOUNTS
WHERE
PERSON.PERSON_ID = MOBILES.PERSON_ID
AND PERSON.PERSON_ID = MAILS.PERSON_ID
AND PERSON.PERSON_ID = ADRESSES.PERSON_ID
AND PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID
作为一个查询优化器,我必须找到处理数据最好的方法。但有 2 个问题:
那么下面就是我可能采取的方法:
在这个简单的例子中,我最后得到很多可能性。但现实世界的查询还会有其他关系运算符,像 OUTER JOIN, CROSS JOIN, GROUP BY, ORDER BY, PROJECTION, UNION, INTERSECT, DISTINCT … 这意味着更多的可能性。
那么,数据库是如何处理的呢?
动态规划,贪婪算法和启发式算法
关系型数据库会尝试我刚刚提到的多种方法,优化器真正的工作是在有限时间里找到一个好的解决方案。
多数时候,优化器找到的不是最佳的方案,而是一个『不错』的
对于小规模的查询,采取粗暴的方式是有可能的。但是为了让中等规模的查询也能采取粗暴的方式,我们有办法避免不必要的计算,这就是动态规划。
这几个字背后的理念是,很多执行计划是非常相似的。看看下图这几种计划:
它们都有相同的子树(A JOIN B),所以,不必在每个计划中计算这个子树的成本,计算一次,保存结果,当再遇到这个子树时重用。用更正规的说法,我们面对的是个重叠问题。为了避免对部分结果的重复计算,我们使用记忆法。
应用这一技术,我们不再有 (2*N)!/(N+1)! 的复杂度,而是“只有” 3^N。在之前 4 个JOIN 的例子里,这意味着将 336 次排序降为 81 次。如果是大一些的查询,比如 8 个 JOIN (其实也不是很大啦),就是将 57,657,600 次降为 6551 次。【译者注:这一小段漏掉了,感谢 nsos指出来。另外感谢 Clark Li 指出Dynamic Programing 应该翻译为动态规划。 】
对于计算机极客,下面是我在先前给你的教程里找到的一个算法。我不提供解释,所以仅在你已经了解动态规划或者精通算法的情况下阅读(我提醒过你哦):
procedure findbestplan(S)
if (bestplan[S].cost infinite)
return bestplan[S]
// else bestplan[S] has not been computed earlier, compute it now
if (S contains only 1 relation)
set bestplan[S].plan and bestplan[S].cost based on the best way
of accessing S /* Using selections on S and indices on S */
else for each non-empty subset S1 of S such that S1 != S
P1= findbestplan(S1)
P2= findbestplan(S - S1)
A = best algorithm for joining results of P1 and P2
cost = P1.cost + P2.cost + cost of A
if cost < bestplan[S].cost
bestplan[S].cost = cost
bestplan[S].plan = 『execute P1.plan; execute P2.plan;
join results of P1 and P2 using A』
return bestplan[S]
针对大规模查询,你也可以用动态规划方法,但是要附加额外的规则(或者称为启发式算法)来减少可能性。
但是,优化器面对一个非常大的查询,或者为了尽快找到答案(然而查询速度就快不起来了),会应用另一种算法,叫贪婪算法。
原理是按照一个规则(或启发)以渐进的方式制定查询计划。在这个规则下,贪婪算法逐步寻找最佳算法,先处理一条JOIN,接着每一步按照同样规则加一条新的JOIN。
我们来看个简单的例子。比如一个针对5张表(A,B,C,D,E)4次JOIN 的查询,为了简化我们把嵌套JOIN作为可能的联接方式,按照『使用最低成本的联接』规则。
因为我们是武断地从表 A 开始,我们可以把同样的算法用在 B,然后 C,然后 D, 然后 E。最后保留成本最低的执行计划。
顺便说一句,这个算法有个名字,叫『最近邻居算法』。
抛开细节不谈,只需一个良好的模型和一个 N*log(N) 复杂度的排序,问题就轻松解决了。这个算法的复杂度是 O(N*log(N)) ,对比一下完全动态规划的 O(3^N)。如果你有个20个联接的大型查询,这意味着 26 vs 3,486,784,401 ,天壤之别!
这个算法的问题是,我们做的假设是:找到 2 个表的最佳联接方法,保留这个联接结果,再联接下一个表,就能得到最低的成本。但是:
为了改善这一状况,你可以多次使用基于不同规则的贪婪算法,并保留最佳的执行计划。
[ 如果你已经受够了算法话题,就直接跳到下一部分。这部分对文章余下的内容不重要。]
很多计算机科学研究者热衷于寻找最佳的执行计划,他们经常为特定问题或模式探寻更好的解决方案,比如:
其他算法也在研究之中,就是为了替换在大型查询中的动态规划算法。贪婪算法属于一个叫做启发式算法的大家族,它根据一条规则(或启发),保存上一步找到的方法,『附加』到当前步骤来进一步搜寻解决方法。有些算法根据特定规则,一步步的应用规则但不总是保留上一步找到的最佳方法。它们统称启发式算法。
比如,基因算法就是一种:
循环次数越多,计划就越好。
这是魔术?不,这是自然法则:适者生存!
PostgreSQL 实现了基因算法,但我并没有发现它是不是默认使用这种算法的。
数据库中还使用了其它启发式算法,像『模拟退火算法(Simulated Annealing)』、『交互式改良算法(Iterative Improvement)』、『双阶段优化算法(Two-Phase Optimization)』…..不过,我不知道这些算法当前是否在企业级数据库应用了,还是仅仅用在研究型数据库。
如果想进一步了解,这篇研究文章介绍两个更多可能的算法《数据库查询优化中联接排序问题的算法综述》,你可以去阅读一下。
[ 这段不重要,可以跳过 ]
然而,所有上述罗里罗嗦的都非常理论化,我是个开发者而不是研究者,我喜欢具体的例子。
我们来看看 SQLite 优化器 是怎么工作的。这是个轻量化数据库,它使用一种简单优化器,基于带有附加规则的贪婪算法,来限制可能性的数量。
我们再看看另一个优化器是怎么工作的。IBM DB2 跟所有企业级数据库都类似,我讨论它是因为在切换到大数据之前,它是我最后真正使用的数据库。
看过官方文档后,我们了解到 DB2 优化器可以让你使用 7 种级别的优化:
DB2 的默认级别是 5,优化器使用下列特性: 【译者注:以下出现的一些概念我没有做考证,因为[ 这段不重要,可以跳过 ]】
默认的,DB2 对联接排列使用受启发式限制的动态规划算法。
其它情况 (GROUP BY, DISTINCT…) 由简单规则处理。
由于创建查询计划是耗时的,大多数据库把计划保存在查询计划缓存,来避免重复计算。这个话题比较大,因为数据库需要知道什么时候更新过时的计划。办法是设置一个上限,如果一个表的统计变化超过了上限,关于该表的查询计划就从缓存中清除。
在这个阶段,我们有了一个优化的执行计划,再编译为可执行代码。然后,如果有足够资源(内存,CPU),查询执行器就会执行它。计划中的操作符 (JOIN, SORT BY …) 可以顺序或并行执行,这取决于执行器。为了获得和写入数据,查询执行器与数据管理器交互,本文下一部分来讨论数据管理器。