postgres之sql性能分析知识整理

postgres之sql性能分析知识整理

上一篇已经整理完数据库并行控制中关于事务，锁，相关的知识点，这次就开始学习学习一下关于如何分析sql的执行快慢了。

前言

一个数据由远程应用请求到相应快慢其实是有多方面原因，一个是代码的业务逻辑效率影响的慢，一个是网络传输数据慢，一个是数据库查询数据慢，也可能是因为服务器性能而导致慢等多方原因。现在就是入手怎么学习认识数据库有些数据查询为什么慢，慢在哪里的知识，然后根据原因进行调优一下性能。

一、一条postgres sql的查询sql是如何工作的。

1.1一条sql执行的主要步骤。

其实在写完一条sql语句的时候，数据库系统底层做了许多的工作，主要就概括为5大步骤：
1.解析器解析sql语句。
2.分析与重写sql语句。
3.规划器生成计划。
4.执行计划。

1.2解析

解析器会将字符串sql查询语句转换成解析树。

1.3解析与重写

在生成解析树后，优化器就开始尝试对其进行分析然后做不同的转换与优化生成查询树。

1.4查询计划

规划器根据优化后的解析树生成了计划树，一个计划树包含了多个计划节点。每个计划节点可以看成是一个执行任务，当前这个任务里包含了它估算的执行成本，当前任务的总执行成本，扫描行数，宽度。每一个计划节点可能有多个子节点，当一个计划节点存在子节点它就先递归执行完子节点后将结果返回给父节点父节点才能执行。

1.5执行器执行。

最后执行器就根据计划树，由叶子子节点开始执行回到跟节点。

二、 查看与分析计划树。

如前面所说的一样，了解了整个sql的执行是根据计划说来工作的，所以我们就看一下计划树是长成什么样子，然后根据计划树分析最后进行优化执行语句。

2.1EXPLAIN关键字

在一句要查询的sql前面添加一个expalain关键字就可以观看该sql的执行计划了。

（1）估计启动成本。这是输出阶段可以开始之前花费的时间，例如，全表扫描节点中进行扫描过滤出id，估计启动成本就是0.00。
（2）估计总成本。这是在计划节点运行完成的假设下陈述的，即检索所有可用行，列如，全表扫描节点中进行扫描过滤出id的总成本为14.00
（3）此计划节点输出的估计行数。同样，假设节点运行完成的时刻
（4）此计划节点输出的行的估计平均宽度（以字节为单位）。
正如上面所说的都是因为没有执行前做的分析计划，所以都是通过某个参照数据进行对整个运行的sql进行估算。

2.2成本估算的参照数据是什么

seq_page_cost( floating point)
设置规划器对作为一系列顺序提取的一部分的磁盘页面提取的成本的估计。默认值为 1.0。通过设置同名的表空间参数，可以为特定表空间中的表和索引覆盖此值（请参阅ALTER TABLESPACE）。

random_page_cost( floating point)
设置规划器对非顺序获取的磁盘页面的成本的估计。默认值为 4.0。通过设置同名的表空间参数，可以为特定表空间中的表和索引覆盖此值（请参阅ALTER TABLESPACE）。

相对于减少这个值seq_page_cost会导致系统更喜欢索引扫描；提高它会使索引扫描看起来相对更昂贵。您可以同时提高或降低这两个值，以更改磁盘 I/O 成本相对于 CPU 成本的重要性，这由以下参数描述。

对机械磁盘存储的随机访问通常比四倍顺序访问昂贵得多。但是，使用较低的默认值 (4.0)，因为对磁盘的大多数随机访问（例如索引读取）都假定在缓存中。默认值可以被认为是将随机访问建模为比顺序慢 40 倍，同时期望缓存 90% 的随机读取。

如果您认为 90% 的缓存率对于您的工作负载是不正确的假设，您可以增加 random_page_cost 以更好地反映随机存储读取的真实成本。相应地，如果您的数据很可能完全在缓存中，例如当数据库小于服务器总内存时，则可以适当降低 random_page_cost 。相对于顺序的随机读取成本较低的存储，例如固态驱动器，也可以使用较低的 random_page_cost 值更好地建模。

cpu_tuple_cost( floating point)
设置规划器对查询期间处理每一行的成本的估计。默认值为 0.01。

cpu_index_tuple_cost( floating point)
设置规划器对索引扫描期间处理每个索引条目的成本的估计。默认值为 0.005。

cpu_operator_cost( floating point)
设置规划器对在查询期间执行的每个运算符或函数的处理成本的估计。默认值为 0.0025。

parallel_setup_cost( floating point)
设置计划者对启动并行工作进程的成本的估计。默认值为 1000。

parallel_tuple_cost( floating point)
设置计划器对将一个元组从并行工作进程转移到另一个进程的成本的估计。默认值为 0.1。

min_parallel_table_scan_size( integer)
设置为了考虑并行扫描而必须扫描的最小表数据量。对于并行顺序扫描，扫描的表数据量总是等于表的大小，但是当使用索引时，扫描的表数据量通常会更少。如果指定此值不带单位，则将其视为块，即BLCKSZ字节，通常为 8kB。默认值为 8 兆字节 ( 8MB)。

min_parallel_index_scan_size( integer)
设置为了考虑并行扫描而必须扫描的最小索引数据量。请注意，并行索引扫描通常不会触及整个索引；规划人员认为相关的扫描实际上会触及的页数。此参数还用于决定特定索引是否可以参与并行真空。见真空。如果指定此值不带单位，则将其视为块，即BLCKSZ字节，通常为 8kB。默认值为 512 KB ( 512kB)。

effective_cache_size( integer)
设置规划器对单个查询可用的磁盘缓存的有效大小的假设。这会被计入使用指数的成本估算中；较高的值使其更有可能使用索引扫描，较低的值使其更有可能使用顺序扫描。设置此参数时，您应该同时考虑PostgreSQL的共享缓冲区和将用于PostgreSQL数据文件的内核磁盘缓存部分，尽管某些数据可能存在于这两个位置。此外，还要考虑对不同表的预期并发查询数量，因为它们必须共享可用空间。该参数对PostgreSQL分配的共享内存大小没有影响，也不保留内核磁盘缓存；它仅用于估计目的。系统也不假设数据在查询之间保留在磁盘缓存中。如果指定此值不带单位，则将其视为块，即BLCKSZ字节，通常为 8kB。默认值为 4 GB ( 4GB)。（如果BLCKSZ不是 8kB，则默认值与其成比例。）

jit_above_cost( floating point)
设置查询成本，如果启用，则激活 JIT 编译（参见第 31 章）。执行JIT 需要花费计划时间，但可以加速查询执行。将此设置为-1禁用 JIT 编译。默认值为100000.

jit_inline_above_cost( floating point)
设置 JIT 编译尝试内联函数和运算符的查询成本。内联增加了计划时间，但可以提高执行速度。将此设置为小于没有意义jit_above_cost。将此设置为-1禁用内联。默认值为500000.

jit_optimize_above_cost( floating point)
设置查询成本，高于该成本 JIT 编译应用昂贵的优化。这种优化增加了计划时间，但可以提高执行速度。将此设置为小于没有意义，将其设置为大于jit_above_cost也不太可能有益jit_inline_above_cost。将此设置为-1禁用昂贵的优化。默认值为500000.

2.2EXPLAIN+ ANALYZE获取计划树

可以通过这两个字获取计划树的花销时间跟实际的执行时间的出入，大伙也喜欢看实际数据。

2.3获取更多的运行时统计信息

EXPLAIN有一个BUFFERS选项可用于ANALYZE获取更多运行时统计信息，UFFERS帮助提供的数字用于识别查询的哪些部分是 I/O 密集程度最高的。

三 、规划器使用的统计数据

统计的一个组成部分是每个表和索引中的条目总数，以及每个表和索引占用的磁盘块数。

于效率原因，reltuples并且relpages不会即时更新，因此它们通常包含有些过时的值。它们由VACUUM、ANALYZE和一些 DDL 命令更新，例如CREATE INDEX. 不扫描整个表（通常是这种情况）的VACUUMorANALYZE操作将reltuples根据它扫描的表的部分增量更新计数，从而得到一个近似值。在任何情况下，规划器都会缩放它找到的值pg_class以匹配当前的物理表大小，从而获得更接近的近似值。

总结

1.通过执行计划分析一个sql是为什么执行慢，然后慢在哪个子节点上，接着就针对这个子节点慢的原因进行优化，通过解决这个主要矛盾提高了sql的执行速度。