3-陈线-PostgreSQL查询优化深度探索.pdf

1、PostgreSQL查询优化深度探索陈线陈线柯普瑞企业柯普瑞企业ITIT学院学院PostgreSQLPostgreSQL授权讲师授权讲师资深资深数据库数据库讲师讲师PostgreSQL查询优化器概述01.PostgreSQL数据库是世界上最先进的开源关系数据库系统，查询优化器是数据库重要的核心模块之一查询优化的常规方法02.PREPARE预备、EXPLAIN执行计划、INDEX索引、PARTITION分区、VIEW视图、MV物化视图等等查询优化的深度探索03.查询树、逻辑重写、逻辑分解、统计信息与选择率、扫描路径、动态规划&遗传算法、连接路径等等主要内容：01 PostgreSQL查询优化器概

2、述3PostgreSQL数据库系统查询优化模块词法分析语法分析语义分析其它模块事务处理并发控制故障恢复规则成本语法分析模块查询执行模块数据存取模块01-1 语法分析模块4源码文件位置：src/backend/parser一词法分析：scan.l二语法分析：gram.y三语义分析:parse_*.c01-2 查询优化模块5源码文件位置：src/backend/optimizer一基于规则的优化RBO：Rule-Based Optimizer逻辑优化二基于代价的优化CBO：Cost-Based Optimizer物理优化01-3 查询执行模块6源码文件位置：src/backend/executor

3、01-4 数据存取模块7源码文件位置：src/backend/access01-5 其他模块8一事务处理二并发控制三故障恢复源码文件位置：src/backend/access/transamsrc/backedn/storage/lmgr02 查询优化的常规方法9 查询优化的目标通过调整SQL语句或设计索引，提高查询性能、减少响应时间，以期系统更高效地运行。常规查询优化有下列主要技术：一使用EXPLAIN命令：在执行查询之前，先使用EXPLAIN关键字加上要执行的查询语句，这样会返回查询计划信息。根据该信息可以判断查询是否需要重写或者添加索引等操作。二创建合适的索引(index)：对于经常被查

4、询的列，应该为其创建索引。索引可以大大提高查询速度，特别是当表中数据量非常大时。三分区表（Partitioning）：将大型表按照某个条件进行水平切分成更小的子表，每个子表都有自己的存储空间。这样可以提高查询效率并减少不必要的IO开销。四视图（View）：如果有复杂的查询逻辑，可以将其定义为视图，然后直接从视图中获取结果。这样可以避免重复编写相同的查询逻辑。五统计信息收集与更新：PostgreSQL内置了统计信息收集器，可以收集表、索引和系统状态的统计信息。及时更新这些统计信息可以确保查询优化器选择最佳的执行路径。六配置参数调优：通过修改PostgreSQL的配置文件postgresql.co

5、nf，可以调整一些影响查询性能的参数值，比如shared_buffers、work_mem等。七使用物理外部表（External Table）：如果需要处理大量的数据，而无法完全放入内存中，可以使用物理外部表来连接到外部数据源，利用外部数据库的功能来处理数据。02-1 EXPLAIN1002-2 创建索引1102-3分区表（Partitioning）1202-4视图（View）和物化视图MV1302-5统计信息收集与更新1402-6 配置参数调优1502-7使用物理外部表（External Table）1603 PostgreSQL查询优化深度探索17源码中优化器的5个基本目录：plan:总入

6、口，调用逻辑优化与物理优化prep：逻辑优化path：路径物理优化geqo：遗传算法物理优化（表连接超过11）util：辅助工具模块，公共函数，供所有目录使用。03-1 目录结构图18Plan 模块为总调用模块，Prep 和 Path 被它调用。在执行中，从Plan模块入口，先调用 Prep 模块进行预处理，再调用Path模块进行优化。Path模块中有开关，指示是 否启用遗传算法进行优化，如果启用，且连接的表超过 ll，就调用 geqo 目录中的遗传算法进行优化。util 模块为辅助工具模块，提供其他模块使用的工具