锐客网

  1. 首页 > it技术 > >

TDSQL PostgreSQL 执行计划详解

SQL

在详解 PostgreSQL 执行计划之前，需要先知道执行计划由来。我们 PostgreSQL 数据库任何查询都会经过语法和语义解析，生成查询表达式树，也就是常用查询数，解析器它会去解析我们的语法，分析器会把语法对应对象进行展开，通过重写器对规则进行重写，最后生成查询数。
根据查询树执行器经过查询再进行预处理，找出最小代价路径，最终创建出计划树。再把查询计划交由执行器进行执行。最终执行完成它会把结果返回给前端应用。这些操作都是在每个连接对应 Backend 进程去进行处理。执行器在执行时，会去访问共享内存，内存没有数据，则从磁盘读取。最终将查询的结果缓存在数据库中，逐步输出给用户进程。
进程会涉及到例如 Work memory、temp buffer 等进程级内存，可以通过我们的 Explain 命令来查看执行计划，对不合理的资源进行调整，提高 SQL 执行效率。在 SQL 前面加上 Explain，就可以直接看到执行计划。不管是在 pgadmin 还是其它工具都可以简单进行查看，也可以通过我们的 psql 去查看。
我们的执行计划有几个特点，首先查询规划是以规划为节点的树形结构，以查询的一些路径作为树形结构，树最底层节点是扫描节点，它是去扫描表中原始行数。不同表也有不同扫描类型，比如顺序扫描或索引扫描、位图索引扫描。也有非表列源，比如说 Values 子句。还有查询，可能需要关联、聚合、排序以便操作，同时也会在扫描节点上增加节点进行操作提示以及消耗。Expain 输出总是以每个树节点显示一行，内容是基本节点类型和执行节点的消耗评估。可能会出现同级别节点，从汇总行节点缩进显示其它属性。第一行一般都是我们汇总的消耗，这个值是越小越好。
在看一个执行计划，我们创建一个测试表，插入 1 万条数据做分析后，可以看到它的执行计划，这个执行计划很简单，全面扫描它只有一行。执行计划我们从左到右去看，先是评估开始的消耗，这里因为没有别的步骤，所以这个步骤是从 0 开始，然后是一个总消耗评估。
Rows 是输出的行数，它是一个评估结果；然后是每一行的平均字节数，这是一个评估结果，这个评估结果依赖于 pg_stats 和 pg_statistic 统计信息。
那么我们怎么去看执行计划呢？就是上级节点的消耗，其中包含了其子节点的消耗，这个消耗值反映在规划器评估这个操作需要的代价。一般这个消耗不包括将数据传输到客户端，只是在数据库后台的执行代价。评估的行数不是执行和扫描节点查询的节点数量，而是返回的数量。同时消耗它不是一个秒的，它是我们规划器的一个参数。Cost 是描述一个执行计划代价是多少，而不是具体时间。
【TDSQL PostgreSQL 执行计划详解】代价评估的一些基准值一般会关注哪几个参数呢？seq_page_cost，即扫描一个块需要的消耗，我们默认为是 1，而随机扫描 random_page 我们默认为是 4，这个在优化的环节需要进行优化，比如说现在使用 SSD，随机页的访问效率肯定比其它的磁盘更快，而这里值就可以改为 1。另外就是 cpu_tuple_cost，我们 CPU 去扫描一个块里具体行数，一行大概 0.01 的消耗。索引是 cpu_index_tuple_cost，0.005 的消耗。
举个例子，新建 Test 表有一万行，它分配了 94 个页。而根据刚刚执行计划可以大概估算消耗：磁盘页乘顺序扫描的 Cost，加上扫描行数。这个值就是 94 个页乘以 1，加上 1 万行乘以 0.01 的消耗就是 194。
那什么时候去更新 pg_class 以及 pg_stat_user_tables 的统计信息？它分为两个部分，一部分主要还是通过 analyze 以及部分 DDL 语句去触发更新统计信息。所以执行计划准确与否和统计信息也很有关联。这里加上条件，比如说 Where Id 小于 1000，会去增加一个筛选条件。这样扫描的同时它会去增加损耗，比如扫描的行数不变，但是增加了 CPU 的计算比较时间，就变为 219。
执行计划最底层是表的扫描，而扫描又分为两种方式，全表扫描以及索引扫描。全表扫描顾名思义去整个表上扫描。就算是有些表加了索引，它也不一定会走索引扫描，如果说满足条件的数据集比较大，索引扫描代价比全表扫描更大，它就会走全表扫描。如前面所说，扫描全表，这个时候重新扫描，会先走索引，再走对应的块，这个代价会比走全表扫描更慢。
另一个问题是索引扫描 Index Scan。在上面的测试表对查询列建一个索引，举例查询条件是小于 1000 这个值，cost 减少还不够直观，如果条件是小于 10 之类小数据量查询，索引效果更好，直接走 Index Scan。但如果查询条件筛选率不够高，查询会先走索引扫描，再重新扫描行，扫描后他会去判断每一个行的条件，Cost 可能相应就变更高。在优化的时候，尤其要去关注这一点，一定要关注索引的筛选率。
索引扫描里还有一个 Index Only Scan，也就是投影列、查询条件都在索引里面，它就会走一个 Index Only Scan，不会再去读其它具体的行值，扫描完索引之后就返回，效率非常高。
还有一种扫描方式是位图扫描，在 PG 里没有位图索引，但是它是有位图扫描的，一般是在 on、and 或 in 子句里面去走。举个例子，上面查询 ID 小于 1000，同时 ID 要大于 9000，这时候它会先做两次索引扫描。扫描时它不会去读具体数据，会先去做一个 Bitmap Scan，之后我们的条件是 Or，会先做一个聚集后再去做 Check，看一下具体实现方式。它是先去启动时间两个 Bitmap Scan 总和，因为是具体扫描会有扫描时间，所以这个组合会花费大量时间。同时 Index Scan 输出的是 Tuple，先扫描索引块，得到对应 ctid 再去扫描具体数据。如果一次只读一条索引项然后去判断行是否满足条件，一个 PAGE 可以多次访问。而 Bitmap Scan 会去输出所有满足条件的索引项，然后组合到一起做 or 等操作，最后才交给上一个节点 Bitmap Heap Scan 去扫描具体数据，由于会先去根据索引扫描的物理数据进行排序，一次性将块中满足条件索引项数据取出来。这样可以说一个块，一次扫描就扫描完了，可以想象这个效率是非常高的。
在底层的数据扫描完之后会去做表连接。连接方式一般在两表关联的时候才有连接可能。一般简单说自然选择、左连接、右连接等等。但具体的到数据库的执行计划里一般主要有 hash join、nested loop、merge join。
Hash Join，它是以 Hash 方式来进行表连接，首先它确定是两个表里的大小，使用小表去建立 Hash map，去扫描大表比较 Hash 值获取最终查询结果。我们示例中建立另外一张表 Test1，并建一个索引进行两张表关联查询，当他们的 T1 的 ID 小于 10，它 Info 相等，做一个关联查询。首先开始的时候，因为两个表大小一样，一张有索引，一张没有，会优先选择有索引的表去做一个 Hash 桶，另外一张表进行一个循环比较 Hash 值。如果说变一下条件把 Test1 表删除一部分数据，优化器会以 Test1 去做一个 Hash 表，Test 表在上面去做驱动。
做一个简单梳理。Hash 连接是在做大数据连接时非常有用的方式，就是在两个大表进行 join。那么这里也是为什么 PG 在和 MySQL 比的时候，说它的分析能力要强一点的原因，因为我们的 Hash join 支持非常好。另外现在 MySQL 已经支持 Hash 了，但是还不是那么完善。
Hash 它有个问题，如果 Hash 的小表也比较大，Hash 表的结果非常大，你的内存放不下，这时就可能会写到你的磁盘中去，就会导致性能急剧下降。在这个时候就要提高 work_mem。hash join 的时间消耗是什么？我们的外层 Cost 请求，加上内层一个请求就可以了。
另外一个连接方式就是 Nested Loop 循环扫描，在这个扫描上写了两个循环去扫描。一般在优化的时候，特别是用 PG 数据库，要去重点看 Nested Loop 是不是合理。那么什么时候用 Nested Loop 呢？就是小表和大表进行关联的时候，小表作为驱动表，那大表作为下面的内层表会比较合理。
首先它会确定一个驱动表，另外是一个内层的表，驱动表每一行与它里面那张表进行一个查询，一个嵌套循环查询比较，代价非常高。就比如每次都是外层的表，乘以外面的条件消耗，这一看就比较大了。
像这种情况，每次扫描时，外层的表每次在驱动时它会去扫描层内层的表，这样效率非常低。而如果内层的表它结果集是相对固定的，那么就可以扫描一次把它做一个物化，下次再循环比较的时就不用再去查询里面的表，类似于 Hash join。Hash join 是做什么的呢？它前面也是一个 Loop，只是把内存的表建立一个 Hash 表，这样去扫描就会快很多。Materialize 就是这么一个优化的方向，这个也依赖于我们的 work_mem。
最后一种连接方式叫 Merge join，主要针对于数据量不是特别大的情况下，而且两个表如果结构相似，做好排序，这时反而会比散列连接会好一点。示例中原来是走了一个 Nested Loop，我们把索引删除，它就去走了 Merge join。一般对于这种数值比较效率还可以，因为排序数值效率是高一点。如果是字符串一类，走 Merge join 效率会更低。
看一下具体的实现，它是先将两个表进行一个排序。Id 1 等于 1 先比较完后，再去比较 Id 等于 2 时，就不会再去比较 Id1 等于 1 的位置块，会直接从另外一张表的 2 开始去比较。
做一个简单比较，Hash join 是将一个小表做为一个内存表做 Hash 运算，将列数据根据 hash 值放到 Hash 行列表中，再从另外一张表去抽取记录做 Hash 运算找到匹配的值，一般是小表做 Hash 表。
Nested Loop 是一张表读取数据，访问另外一张表做匹配。Nested Loop 在关联表比较小的时候效率最高。小表做驱动，比如这个表只有百来行，而大表很大，循环 100 次查询，大表会进行索引扫描，相对会快很多。
Merge join 如果数据做好了排序，而且是数字类型排序，Merge join 可能反而比 Hash 要快。但一般来说如果数据量比较大，Hash 基本会比 Merge join 更快。
另外是关联相关参数一般以 Enable 开头。刚刚那几种连接 Nested Loop、Merge join、Hash join、Bitmap Scan 都是可以去控制的，参数可以是 session 级别控制。
查看执行计划首先是看扫描方式和连接方式，不论再怎么复杂，都是通过这两个进行组合。一般是看它在扫描和关联是不是合理的。这两个判断之后，再去看它的条件是不是合理，或需不需要改写。有了执行计划之外，在看具体执行时间，就要加上 Explain Analyze 来看具体执行时间。这里有一个不一样的点，在这里有了一个实际执行时间，这个时间是真实时间。可以很精确知道每一步花费时间。
在 Analyze 之外，还有一些其它参数，可以通过\H Explain 的方式去查看详细的语法，有 verbos 显示具体执行日志，还有 Cost 消耗、Settings 显示特殊设置，buffers 内存的一些分配情况。wal、Timing 时间，Summary，format 输出的格式 TXT 或者 xml、json。如果加上，它的显示信息会多很多。主要是 buffers 比较有用，显示说你申请了多少，现在多少磁盘块是要命中，多少是进行读取的。在第二次查询的时候，它的磁盘读取会变少，第一次读取是 94 块，第二次 50 块块。
除了上述内容，还有一个日志参数。我们的 log_planner_stats 可以输出你的执行计划到日志文件中，Oracle 的执行计划是从表里去看，而我们 PG 是没有的。那么怎么办？可以通过一些参数去控制，导到日志里来。就目前这个日志它是输入到运行日志里的，没有单独去进行记录。当然这个也是我们优化的一个方向。
通过设置这些参数，把这里日志打印出来，显示出执行计划，语法分析、语义分析、重写，这几个阶段它会显示出来。如果开启了执行计划状态，会把这些进行打印。
最后看执行计划之外，从执行计划去反推 SQL 优化方向。从最底层一个扫描去入手，要尽量走索引扫描。另外索引扫描这里有很多方式，就是看它是否是合理索引，要看类型是不是选择合理的。比如数字类型、字符串类型，我们选用 gin 索引，还是一些 btree 索引。PG 默认是 btree 索引，但 btree 索引不是所有类型和操作符都会适用。另外还需要减少不必要的索引、避免单条 SQL 插入，要单条变为批量进行插入。
前面说执行计划表连接类型是不是正确合理，另外要从 SQL 本身进行入手，我们目的是为了减少它的消耗。如果 SQL 语句比较复杂，而扫描类型已经无法改动，那这时只能去改写 SQL 语句，尽量减少嵌套，减少子查询。还可以通过物化视图临时表，去做 SQL 拆分。
尽量把 in 语法用 Exits 方式做连接。另外还要注意一些类型的转换失真，在扫描时，如果它可以走索引扫描，结果走了全面扫描，可能是转换失真了，比如说一个 in 类型，结果输入是一个字符串类型，它有可能会转换失败，只能走全面扫描，不能索引。
另外从数据库参数来入手，就需要精确的统计信息，我们在生成执行计划时，可能 autovacuum 没有去执行，也可能统计信息落后，那么执行计划就是错误的。这时候就要对应表作为一个 analyze。
最后就是干涉执行计划，干涉执行计划有两种方式，除了前面的 enable 几个参数，我们的 pg_hint_plan 插件也可以做一些 Hint 控制。还有一些新参数调整，例如调整 work_mem、temp_buffers、shared_buffers 等参数。还有一些连接池的使用，我们操作系统参数、硬件的性能参数调整等等。
其实往往数据库优化，除了这些以外，还有我们去看操作系统的一些硬件性能，比如 CPU 是不是 Performance 模式，磁盘调度方式是不是最优的，网卡 Bond 模式等其他参数。

    推荐阅读


    上一篇：TDSQL-C PostgreSQL 版的高可用特性

    下一篇：如何使用|如何使用 gh-pages 部署 vue 应用