PostgreSQL 优化器入门
文章平均质量分 78
数据库内核专家带你轻松掌握数据库核心技能
优惠券已抵扣
余额抵扣
还需支付
¥39.90
¥99.00
购买须知?
本专栏为图文内容,最终完结不会低于15篇文章。
订阅专栏,享有专栏所有文章阅读权限。
本专栏为虚拟商品,基于网络商品和虚拟商品的性质和特征,专栏一经购买无正当理由不予退款,不支持升级,敬请谅解。
shujiezhang
这个作者很懒,什么都没留下…
展开
-
《PostgreSQL技术内幕:事务处理深度探索》上市了!
新的个人博客主页:zhangshujie.cn京东地址当当地址淘宝地址为什么写这本书?近年来,我一直从事与PostgreSQL相关的数据库内核开发工作,主攻方向是SQL引擎中的查询优化,对事务的了解仅停留在理论层面,虽然对事务的性质、隔离级别也能讲一番大道理,但终究华而不实,于探究事务的本质无益。所谓“纸上得来终觉浅,绝知此事要躬行”,在完成了《PostgreSQL技术内幕:查询优化深度探索》一书之后,我就开始了事务的探索之旅。探索的过程并非一帆风顺。事务的理论已经颇为复杂,在形成源..原创 2021-08-04 23:52:10 · 1235 阅读 · 0 评论 -
开篇词:翻过数据库优化器这座山峰
大家好,我是张树杰,是一名数据库内核开发者。我在 2018 年 6 月出版了《PostgreSQL 技术内幕:查询优化深度探索》一书,这本书对 PostgreSQL 优化器的源代码进行了详尽的分析,但也有一些朋友向我抱怨:“你只顾自己源码分析得 High,考虑过我们的感受吗?”是的,除了 PostgreSQL 的内核开发者,广阔天地间还有更多 PostgreSQL 的使用者以及其他数据库使用者。如...原创 2020-09-22 12:15:19 · 1010 阅读 · 0 评论 -
导读:逻辑优化篇
这部分课程致力于让读者达到对数据库优化器理解的第二个层次:详细了解。愿上层楼骋远目,勿在浮沙筑高台,在开始学习第二个层次的内容之前,让我们先来复习一下第一个层次的内容。为了使对优化器分析的过程更为形象生动,接下来我们跟着小明、大明和牛二哥一起来探讨一下 PostgreSQL 查询优化器的一些基础知识。对这块内容已经了如指掌的朋友可以跳过导读,直接开始后面内容的学习。查询优化器的基本原理小明与...原创 2020-09-22 12:15:17 · 1609 阅读 · 0 评论 -
导读:物理优化篇
通过大明和小明的对话,相信读者朋友对逻辑优化的部分已经有了一个简单的了解,逻辑优化也叫基于规则的优化,这种优化方式比较呆板、不够灵活,就是按照定义好的规则硬性地进行等价变换,于是就催生了新的优化方法——物理优化,物理优化又叫基于代价的优化,今天我们再次跟着小明、大明和牛二哥一起来探讨一下 PostgreSQL 优化器是怎么计算路径代价的,又是怎么筛选路径的。对这些内容已经了如指掌的朋友可以跳过这个...原创 2020-09-22 12:15:15 · 1057 阅读 · 0 评论 -
第01课:SQL 语句的历程
通常,数据库就如同一个黑匣子,我们输入 SQL 语句给它,它负责反馈执行结果给我们。尤其是 SQL 又是一种入门极简单的语言,使用者只要能看懂英语就能熟练写出增删改查的简单语句,对于刚入门的用户来说肯定感觉是极好的。在数据量比较少时,用户对数据库操作得比较随意,数据库也不会提出反对意见。但是随着数据量的增加,若是再胡乱地拼凑几个查询,并期望数据库给出一个完美的结果,那只能一首《凉凉》送给你——数...原创 2020-09-22 12:15:14 · 655 阅读 · 0 评论 -
第02课:解读执行计划
SQL 语句在转换成查询树之后,就会进入优化器。优化器通过对查询树进行逻辑优化和物理优化后挑选出一个最优的执行计划,这个执行计划就会交给执行器来执行。因此,在使用 PostgreSQL 数据库的过程中,如果你在执行一个 SQL 语句时期望优化器给你带来的是卖家秀,结果实际上收到一个买家秀,那二话不说就先要看看这个 SQL 语句到底产生了一个什么样的执行计划,是不是因为优化器一时发昏选了一个比较“傻...原创 2020-09-22 12:15:12 · 563 阅读 · 0 评论 -
第03课:调整执行计划
通过 EXPLAIN 查看了具体的执行计划之后,我们还能尝试影响优化器,让优化器生成我们想要的执行计划。不过,PostgreSQL 没有像 Oracle 那样通过在 SQL 语句中增加 HINT 信息的方式来影响执行计划的生成。但它也提供了一系列的 GUC 参数,比如在前面的课程中曾使用 enable_material 参数来尝试禁用 material,而且确实达到了禁用的效果。PostgreSQ...原创 2020-09-22 12:15:10 · 372 阅读 · 0 评论 -
第04课:谓词下推
所谓“出名要趁早”,谓词下推就是基于这一原则(这里所说的谓词就是常用的那些约束条件)。如果一个谓词在执行计划中即使处在不同的位置也不改变执行结果,那么我们就尽量把它保持在下层,因为它有“过滤”的作用。在下层结点把数据过滤掉,有助于降低上层结点的计算量。当然对于一些比较执着的谓词,SQL 的书写者把它安排在了上层,我们在生成执行计划的时候就可以考虑是否能把它推下去。这就需要进行甄别,哪些谓词是可以推...原创 2020-09-22 12:15:08 · 1092 阅读 · 0 评论 -
第05课:连接顺序交换规则
没有规矩,不成方圆。我们在编写 SQL 语句的时候,通常都会绞尽脑汁地想写出一个性能比较好的语句。对于如何安排表之间的连接顺序,也都是经过深思熟虑的。但是人脑毕竟不是电脑,我们在安排表之间的连接顺序时,能够考虑到的因素也有限。而且,数据库内的数据情况瞬息万变,在不同的时间节点,同样的连接顺序可能需要消耗不同数量的资源。因此,对表的连接顺序进行重新排列是一个非常重要的优化过程。请看下面示例中的 S...原创 2020-09-22 12:15:06 · 636 阅读 · 0 评论 -
第06课:子连接提升
PostgreSQL 数据库区分了子查询和子连接,我们先来看一下子查询和子连接的区别。子连接和子查询的区别PostgreSQL 数据库基于子查询所在的位置和作用的不同,将子查询细分成了两类,一类称为子连接(SubLink),另一类称为子查询(SubQuery)。那么如何来区分子查询和子连接呢?通常而言,如果它是以“表”的方式存在的,那么就称为子查询,例如下面的示例语句中是以一个表的方式存在的:...原创 2020-09-22 12:15:04 · 868 阅读 · 0 评论 -
第07课:子查询提升
子查询和子连接不同,它不是“表达式”,它是“表”。因此,优化器把子查询当做表来对待,从而针对子查询产生的是一个扫描路径,也就是 SubQueryScan。SIMPLE 子查询的提升如果把子查询看做一个表,我们可以叫它“子查询表”,这个子查询表和其他表在做连接(Join)操作。子查询如果被提升,会转换成子查询中的表直接与与上层的表做连接操作。查询优化模块对表之间的连接操作的优化做了很多工作,因此...原创 2020-09-22 12:15:03 · 477 阅读 · 0 评论 -
第08课:消除外连接
外连接是查询优化中的搅屎棍,在查询优化器的实现过程中,很多时间都消耗在和外连接做斗争。我们从《第4课:谓词下推》和《第5课:连接顺序交换规则》可以看出,外连接的出现对提高优化器的实现难度“功不可没”。例如,对约束条件进行下推(谓词下推)时,如果连接操作是外连接,那么有些约束条件下推就可能会受到阻碍。在连接顺序交换时,内连接的表之间的连接顺序交换比较灵活,而外连接不能随意地交换连接表的顺序。因此,如...原创 2020-09-22 12:15:01 · 305 阅读 · 0 评论 -
第09课:等价推理
在 SQL 语句中,经常会有 A=B 这样的约束条件,它的操作符是等值操作符。我们将这种等值约束条件称为“等价条件”,而基于多个等价条件进行推理而获得的等价属性的集合就是“等价类”。含有常量的等价推理假如等价约束条件中的一端是常量,这种等价的推理就会显得更有意义。假如有两个约束条件 A=B 和 B=5,从谓词下推的角度来看,A=B 肯定只能作为一个连接条件。只有在连接操作做完之后,A=B 这样...原创 2020-09-22 12:14:59 · 623 阅读 · 0 评论 -
第10课:表达式的规范化
我们写一个查询语句,语句中会有投影和约束条件,这些都需要在执行器中进行计算之后才能获得它们的值,这些就是我们所说的表达式,比如:SELECT a, b+c FROM TEST_A WHERE d > 0;这样的一个语句中我们说有 3 个表达式:* 对 a 进行求值的表达式* 对 b+c 进行求值的表达式* 对 d > 0 进行求值的表达式执行器通过 Seq Scan o...原创 2020-09-22 12:14:58 · 351 阅读 · 0 评论 -
第11课:逻辑优化汇总
目前已经比较着重地介绍过子连接和子查询的提升、表达式的规范化、外连接的消除、等价推理、连接顺序交换、谓词下推等逻辑优化方法,实际上,PostgreSQL 还做了很多逻辑优化,我们把这些优化在这一课做一个汇总。Having 子句的优化在 Having 子句中,有些约束条件是可以转变为过滤条件的。这里对 Having 子句中的约束条件进行了拆分,从下面的示例可以看出,c > 0 这个约束条件...原创 2020-09-22 12:14:56 · 570 阅读 · 0 评论 -
第12课:统计信息
从这一节课开始就进入了物理优化的部分。所谓物理优化就是通过计算代价的方式来对多种可能的方法进行筛选,优胜劣汰。那么什么是代价呢?代价就是一个执行计划在执行过程中所带来的消耗。既然是数据库,数据都保存在磁盘上,那么就免不了读取磁盘带来的消耗,这种消耗可以称为 IO 代价。在 SQL 语句中需要执行各种表之间做逻辑运算,看到“运算”两个字就很容易想到 CPU,因为 CPU 是中央处理单元,所以执行计划...原创 2020-09-22 12:14:55 · 272 阅读 · 0 评论 -
第13课:表达式提取
如果有这样一条 SQL 语句:SELECT * FROM STUDENT, COURSE WHERE (sno =1 AND cname=’math’) OR (sno = 2 AND cname = ‘physics’),在对谓词做表达式预处理的时候,我们对这种类型的约束条件无从下手。我们知道 PostgreSQL 的约束条件的顶层是合取范式(或者说是“与”的方式),但这里的顶层是一个析取...原创 2020-09-22 12:14:53 · 263 阅读 · 0 评论 -
第14课:选择率
通过统计信息,代价估算系统就可以了解一个表有多少行数据、用了多少个数据页面、某个值出现的频率等,然后根据这些信息计算出一个约束条件能过滤掉多少数据,这种约束条件过滤出的数据占总数据量的比例称为“选择率”。选择率 = 筛选之后所剩的元组数量 / 筛选之前的元组数量选择率与随机 IO 的关系获得了统计信息之后,在代价估算的时候就可以利用这些统计信息进行计算,比如可以借用统计信息计算约束条件的选...原创 2020-09-22 12:14:51 · 1051 阅读 · 0 评论 -
第15课:参数化路径
在谓词下推的过程中,对于 …FROM A JOIN B ON A.a = B.b 这种类型的约束条件肯定是不能下推的。原因是 A.a = B.b 这样的约束条件既引用了 LHS 表的列属性,又引用了 RHS 表的列属性,必须在获得两个表的元组之后才能应用这样的约束条件。现在假如在 B.b 属性上有一个索引 B_b_index,但没有能够匹配索引的约束条件,索引扫描路径或者不会被采纳,或者采纳为对整...原创 2020-09-22 12:14:49 · 982 阅读 · 0 评论 -
第16课:选择最优执行计划
PostgreSQL 数据库分别采用了动态规划方法和遗传算法来选择最优的执行计划。动态规划方法需要遍历全部的解空间(有优化),它一定能够获得最优解,因此是我们首选的方法。遗传算法则只能尝试从局部最优解向全局最优解不断逼近,但由于遗传代际的数量的限制,最终可能产生的是局部最优解。这种方法在表比较多的时候被采用,因为在表比较多的时候,动态规划的解空间快速地膨胀,可能会导致查询性能的下降,遗传算法的复杂...原创 2020-09-22 12:14:48 · 591 阅读 · 0 评论 -
第17课:扫描代价计算
接下来我们会分别介绍 3 种物理路径的代价计算方法,这样读者就能跟着这种计算的过程了解代价计算的流程。需要注意的是读者朋友们可以不用过度关注计算的准确性,而是把注意力集中在 PostgreSQL 代价模型的思想上。由于 SQL 是描述性语言,所以它只需要告诉我们执行结果,它不关心执行的过程。假如有很多可以获得执行结果的手段,那么我们当然是想获得一个效率最高的——也就是说条条大路通罗马,但我们一定...原创 2020-09-22 12:14:46 · 395 阅读 · 0 评论 -
第18课:连接代价和 Non-SPJ 代价
在介绍具体的代价计算之前,我们先来认识一下启动代价和整体代价。启动代价和整体代价我们在前面的课程已经简要介绍了启动代价,那为什么要区分启动代价和整体代价呢?我们通过一个示例来说明。postgres=# CREATE INDEX TEST_A_A_IDX ON TEST_A(a);CREATE INDEXpostgres=# INSERT INTO TEST_A SELECT GENER...原创 2020-09-22 12:14:45 · 319 阅读 · 0 评论 -
第19课:扫描计划路径的执行
选择执行计划之后,这个执行计划就可以开始执行了。PostgreSQL 数据库的执行器是一个流水线式的火山模型,“流水线”的意思是说它每次只处理一条元组,“火山模型”的意思是它是一个“拉取”式的模型,也就是对执行计划进行后序遍历。为了方便说明,我们来看一个例子。假设有 2 个表,他们的数据分别如下:TEST_A 表:ABCD11112222TES...原创 2020-09-22 12:14:43 · 160 阅读 · 0 评论 -
第20课:连接路径的执行说明
连接路径中包括嵌套循环连接、哈希连接和归并连接,本文主要介绍嵌套循环连接和哈希连接的一些特点。假设有 2 个表,他们的数据分别如下:TEST_A 表:ABCD11112222TEST_B 表:ABCD22223333嵌套循环连接的流程我们假设这两个表做连接的 SQL 语句生成了一个嵌套循环连接...原创 2020-09-22 12:14:42 · 208 阅读 · 0 评论 -
第21课:聚集与分组的执行说明
PostgreSQL 中除了 SPJ(SELECT、PROJECT、JOIN) 之外,还有大量的聚合和分组操作,比如下面示例中的语句,其中有聚集操作 avg(a) 和分组操作 GROUP BY b。顾名思义,聚集操作就是一个合并的过程,而分组操作则是对数据的归类。postgres=# EXPLAIN SELECT avg(a) FROM TEST_A GROUP BY b; ...原创 2020-09-22 12:14:40 · 511 阅读 · 0 评论 -
第22课:Greenplum 的执行计划
Greenplum 是脱胎于 PostgreSQL 的分布式数据库。既然是分布式数据库,原来 PostgreSQL 生成的基于单机数据库的执行计划显然就无法使用了。因此,本章要分析的是 Greenplum 如何改进 PostgreSQL 的执行计划,使其能够在分布式数据库中执行。数据分布不同了Greenplum 中有多个结点,最主要的是划分成了 Master 结点和 Segment 结点。每个...原创 2020-09-22 12:14:38 · 826 阅读 · 0 评论