查询优化器的原理

第15章优化SQL语句

第15章优化SQL语句
15.1 理解查询优化器的工作原理

了解查询优化器的工作原理，并学会编写更优化的SQL语句，对于数据库系统的稳定运行及提升性能有重要意义。本章将介绍PostgreSQL查询优化器的原理，并指导用户编写更优化的SQL语句。
PostgreSQL主要进行了两个阶段的优化，即逻辑优化和物理优化。
● 逻辑优化：在关系代数的理论基础上对查询树的节点进行重组，从而生成一个没有冗余的查询树，以提高查询的效率。
● 物理优化：从查询的物理成本上进行优化，通过对各种基本信息进行分析后，选择成本相对低的查询路径。

15.1 理解查询优化器的工作原理

15.1.1 SQL语句执行过程

应用程序在与PostgreSQL服务器创建连接后，将SQL查询语句发送到PostgreSQL服务器。PostgreSQL服务器收到SQL查询语句后，会进行如下操作（见图15-1）。

- （1）解析器对SQL语句进行语法检查和语义检查，并生成查询树，然后把查询树作为输入交给重写器（rewrite system）。
- （2）重写器根据存储在系统表（system catalogs）中的规则修改查询树。先把视图重写为对应的基础表，然后把重写后的查询树交给优化器（planner/optimizer）。
- （ 3 ） 优 化 器根 据查询 树产 生 执 行 计 划 ， 然后 交 给执 行器（executor）。
- （4）执行器执行查询计划树并返回查询结果。

重写器和优化器处理的都是查询树，都属于查询优化的范围。优化器包括对SPJ的优化和非SPJ优化。
● SPJ优化：基于选择（SELECT）、投影（PROJECT）、连接（JOIN）3种基本操作的查询优化。
● 非SPJ优化：在SPJ基础上，对分组、集合、排序等操作的查询优化。

15.1.2 了解查询树

查询优化的对象是查询树。什么是查询树？它是一个SQL语句的内部表现形式，组成该语句的每个独立部分都是分别存储的。
设置如下配置参数，可以在服务器日志中查看到详细的查询树内容：

set debug_print_parse=on;     --查看重写前的查询树
select * from test
set debug_print_rewriten=on;  --查看重写后的查询树


eg:
=192.168.205.142 port=47026",,,,,,,,,"","client backend",,0
2025-10-29 11:22:57.334 CST,"postgres","testdb",6875,"192.168.61.22:52429",690187f1.1adb,6,"SELECT",2025-10-29 11:20:17 CST,4/292650,0,LOG,00000,"parse tree:","   {QUERY 
   :commandType 1 
   :querySource 0 
   :canSetTag true 
   :utilityStmt <> 
   :resultRelation 0 
   :hasAggs false 
   :hasWindowFuncs false 
   :hasTargetSRFs false 
   :hasSubLinks false 
   :hasDistinctOn false 
   :hasRecursive false 
   :hasModifyingCTE false 
   :hasForUpdate false 
   :hasRowSecurity false 
   :isReturn false 
   :cteList <> 
   :rtable (
      {RANGETBLENTRY 
      :alias <> 
      :eref 
         {ALIAS 
         :aliasname test 
         :colnames (""id"" ""name"")
         }
      :rtekind 0 
      :relid 16538 
      :relkind r 
      :rellockmode 1 
      :tablesample <> 
      :perminfoindex 1 
      :lateral false 
      :inh true 
      :inFromCl true 
      :securityQuals <>
      }
   )
   :rteperminfos (
      {RTEPERMISSIONINFO 
      :relid 16538 
      :inh true 
      :requiredPerms 2 
      :checkAsUser 0 
      :selectedCols (b 8 9)
      :insertedCols (b)
      :updatedCols (b)
      }
   )
   :jointree 
      {FROMEXPR 
      :fromlist (
         {RANGETBLREF 
         :rtindex 1
         }
      )
      :quals <>
      }
   :mergeActionList <> 
   :mergeUseOuterJoin false 
   :targetList (
      {TARGETENTRY 
      :expr 
         {VAR 
         :varno 1 
         :varattno 1 
         :vartype 23 
         :vartypmod -1 
         :varcollid 0 
         :varnullingrels (b)
         :varlevelsup 0 
         :varnosyn 1 
         :varattnosyn 1 
         :location 7
         }
      :resno 1 
      :resname id 
      :ressortgroupref 0 
      :resorigtbl 16538 
      :resorigcol 1 
      :resjunk false
      }
      {TARGETENTRY 
      :expr 
         {VAR 
         :varno 1 
         :varattno 2 
         :vartype 1043 
         :vartypmod 104 
         :varcollid 100 
         :varnullingrels (b)
         :varlevelsup 0 
         :varnosyn 1 
         :varattnosyn 2 
         :location 7
         }
      :resno 2 
      :resname name 
      :ressortgroupref 0 
      :resorigtbl 16538 
      :resorigcol 2 
      :resjunk false
      }
   )
   :override 0 
   :onConflict <> 
   :returningList <> 
   :groupClause <> 
   :groupDistinct false 
   :groupingSets <> 
   :havingQual <> 
   :windowClause <> 
   :distinctClause <> 
   :sortClause <> 
   :limitOffset <> 
   :limitCount <> 
   :limitOption 0 
   :rowMarks <> 
   :setOperations <> 
   :constraintDeps <> 
   :withCheckOptions <> 
   :stmt_location 0 
   :stmt_len 0
   }
",,,,,"select * from test",,,"Navicat","client backend",,2052796746736852713

日志显示重写前和重写后的查询树内容是一样的。
先创建myview视图，再查看执行“SELECT*FROM myview”语句的查询树内容，代码如下：

15.1.3 了解逻辑优化

逻辑优化的基本理论来源于关系代数。PostgreSQL 数据库属于关系型数据库，关系型数据库查询语言的基础就是关系代数，因此，对
查询语言的优化就可以通过使用关系代数的运算来进行。
逻辑优化阶段，使用关系代数中的并、交、差、积、除、选择、投影、连接、半连接等一系列运算方法对查询树进行等价变换，使得SQL语句采用查询树的执行效率更高。
逻辑优化的顺序如下所述。

（1）对子查询进行优化。
（2）对WHERE、HAVING、ON等条件表达优化及等价谓词重写。
（3）对外连接进行优化。

15.1.4 逻辑优化：对子查询进行优化

一般来说，子查询是通过把它转换成表连接的方法进行优化的。
1.子查询优化的步骤
对子查询进行优化大致分为两个步骤。
（1）对子查询进行上提（即尽可能地把子查询上提到父查询中，与父查询进行合并），这样可以减少查询的层次，减少嵌套查询，使得查询节点尽可能地在叶子节点完成选择操作。
（2）把选择出来的少量结果进行表间的连接操作，从而将表连接的操作数量降到最低，提高查询性能。

15.1.5 逻辑优化：条件表达式优化及等价谓词重写优化

基于关系代数中的并、交、差等运算规则，用户可以对查询树的条件表达式进行优化。
例如，下述SQL查询语句的WHERE表达式是“employee.deptid=10AND employee.deptid=department.deptid”，逻辑查询优化会把它优化为
“employee.deptid=10 AND department.deptid=10”，代码如下：

在以下的查询语句中，含有谓词 BETWEEN，逻辑查询优化会把“employee.empid BETWEEN 0 AND 100”替换为“（empid＞=0） AND（empid＜=100）”，代码如下：

15.1.6 逻辑优化：外连接优化

外连接包括左外连接、右外连接、全外连接等多种连接方式。把外连接转化为内连接，可以使得表的连接顺序更随意，提高查询效率。
在以下的查询语句中，执行计划显示还存在Hash Left Join节点，说明左外连接没有被优化为内连接，代码如下：

在以下的查询语句中，左外连接被优化为内连接。需要注意的是，该查询语句与上面实例中的查询语句的语义不是等同的。执行计划显示，已经没有Hash Left Join节点，即左外连接已经被优化为内连接（Hash Join），代码如下：

15.1.7 了解物理优化

逻辑优化是指在不改变语义的基础上改变查询树的节点位置和结构，从而提升SQL语句的执行效率。
物理优化是指在查询树上选择最优的查询路径，该查询路径中的物理访问代价最少，从而提升SQL语句的执行效率，如图15-2所示。

1.单表的最优查询路径

在进行物理优化时，需要找出最优查询路径。由于单表在查询树上就是叶子节点，而且PostgreSQL 采用的动态规划算法会最先访问叶子节点，再由叶子节点向上层查找访问路径，所以，对单表进行优化实际上只涉及对该表的扫描方式的选择。
因为对每个表都可以进行顺序扫描，所以在评估单表的扫描方式时，默认都会评估顺序扫描表的代价。如果表中还存在一个或多个索引，则需要比较各个索引扫描的代价。
执行以下简单的查询语句，其中empid字段已经创建了索引，代码如下：

select * from emp where empid>1001;

在物理优化时，先比较扫描employee表的两种方式（顺序扫描表和索引扫描表）的扫描代价，并选择其中一种代价较小的扫描方式作为该表的物理查询方式。数据的离散性、查询的命中行数及总行数、表的大小等诸多因素都会影响到物理查询方式，因此，虽然empid字段已经创建了索引，却不一定会使用索引扫描。

执行EXPLAIN命令后可以看出，优化器对这条查询语句最终选择了顺序扫描表方式，代码如下：

2.两个表的最优查询路径

如果查询需要连接两个表，则用户需要考虑怎样连接使得查询效率最高。PostgreSQL 有以下3种可能的连接策略。

（1）嵌套循环连接（Nested Loop）

嵌套循环连接会对外表进行扫描，对找到的每一行都需要在内表进行一次扫描，匹配是否满足连接条件。如果内表可以使用索引扫描，那么扫描速度会加快。
这种策略通常会很耗时，但是在有些场景下，它是唯一的选择。例如，下述查询语句需要对两个表做笛卡儿积运算，因此只能选择嵌套循环连接策略，代码如下：

（2）归并连接（Merge Semi Join）

在进行归并连接前，内表和外表分别对关联字段进行排序，然后对两个表进行同步向前（forward）扫描，匹配是否满足连接条件。
与嵌套循环连接相比，归并连接更有吸引力，因为每个表都只需要扫描一次。当表在排序时，可以通过排序步骤来完成。如果关联字段上有索引，那么使用索引对表进行扫描。

在下述查询语句中，两个表都采用了归并连接的策略，代码如下：

此时禁用归并连接，再查看执行计划，该语句采用了Hash连接，代码如下：

（3）Hash连接（Hash Join）

Hash连接首先用关联字段作为Hash关键字，对内表进行扫描，并创建 Hash表；然后扫描外表，对扫描到的每个行，计算关联字段的Hash值，并使用该Hash值快速定位Hash表中的匹配行。

使用emp_order_insurance表和insurance表完成与上述实例中类似的查询语句，代码如下：

从上述查询语句可以看出，使用的是 Hash 连接策略，这是由于emp_order_insurance 表和insurance表都是小表，Hash连接对于小表的连接其查询效率更高。

3.超过两个表的最优查询路径
如果连接的表个数多于两个，那么在物理优化查询树时，会先将某两个表进行连接，再连接其他表节点或连接另外两表连接后的节点。这种连接方式可以有多种，那么就会有多种可用的查询路径。理论上，查询优化器会检查每种可用的查询路径，最终选择代价最小的路径。但是，每当多检查计算一种可能的查询路径的代价，就会消耗更多的时间和内存空间。

posted @ 2026-05-18 10:40 数据库小白（专注）阅读(19) 评论(0) 收藏举报

刷新页面返回顶部

数据库小白（专注）