一、postgre数据库SQL优化：相关视图介绍

积跬步，慕至千里

已于 2022-10-28 15:35:59 修改

阅读量2.3k

点赞数 1

分类专栏： SQL学习库文章标签： 1024程序员节

于 2022-10-24 22:00:00 首次发布

本文链接：https://blog.csdn.net/weixin_42521211/article/details/127493680

版权

SQL学习库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

一、前言

关于sql优化，面试或工作中，都是高频遇到的问题。自己也时常被难住，基于此，特将看到或用到的一些sql优化信息进行整理，以便有问题时，自己也可以方便复现或者解决问题。

二、sql优化目标思考出发点

为何优化sql优化，无非就是想要快速获取查询数据的结果，对应到一些专业术语：高性能、高效。那判断高效的标准：在满足业务需求的情况下，执行sql语句耗时最少或者尽可能少。

sql优化的目标是让 sql程序执行时间尽可能短，那如何发现需要优化的sql呢？可以从以下三点出发考虑[1]：

紧急，重大事故（逆推法，从现象找原因）：生产环境慢查询导致系统卡顿（数据库服务器CPU使用率飙升，负载飙升），前端表现为大批用户反馈数据请求超时。
居安思危：日常排查慢查询 pg_stat_statement 。
前瞻性：查看索引使用情况，为必要的表字段添加索引。（大胆猜测，小心求证）

针对上述考虑，进一步需要明确的优化问题，可以从以下问题进一步明确：

怎么判断是否需要优化（怎么查看SQL是否有问题）？阻塞、锁？（pg_stat_activity系统内置活动视图）
如何统计慢查询？（pg_stat_statement 插件）
大表vs慢查询？
如何分析慢查询？（explain）

三、sql优化相关帮助视图介绍

3.1 pg_stat_activity系统内置活动视图

pg_stat_activity视图将为每一个服务器进程显示一行，显示与该进程当前活动相关的信息。理论上能捕抓到每个实时的SQL，运行结束可能就会消失，也就是说实际上有些运行时间短的SQL人为可能会看不到。该视图可以实时查询当前在执行的SQL，包括状态，比如是否阻塞、等待锁等。

3.1.1 视图主要字段

. wait_event_type：后端正在等待的事件类型，如果不存在则为 NULL。可能的值有：

LWLock：后端正在等待一个轻量级锁。每一个这样的锁保护着共享内存中的一个特殊数据结构。wait_event将含有一个标识该轻量级锁目的的名称（一些锁具有特定的名称，其他是一组具有类似目的的锁中的一部分）。
Lock：后端正在等待一个重量级锁。重量级锁，也称为锁管理器锁或者简单锁，主要保护 SQL 可见的对象，例如表。不过，它们也被用于确保特定内部操作的互斥，例如关系扩展。wait_event将标识等待的锁的类型。
BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。如果另一个进程持有一个最终从要访问的缓冲区中读取数据的打开的游标，缓冲区pin 等待可能会被拖延。
Activity：服务器进程处于闲置状态。这被用于在其主处理循环中等待活动的系统进程。wait_event将标识特定的等待点。
Extension：服务器进程正在一个扩展模块中等待活动。这一个分类被用于要跟踪自定义等待点的模块。
Client：服务器进程正在一个套接字上等待来自用户应用的某种活动，并且该服务器预期某种与其内部处理无关的事情发生。wait_event将标识特定的等待点。
IPC：服务器进程正在等待来自服务器中另一个进程的某种活动。wait_event将标识特定的等待点。
Timeout：服务器进程正在等待一次超时发生。wait_event将标识特定的等待点。
IO：服务器进程正在等待一次IO完成。wait_event将标识特定的等待点。

state：这个后端的当前总体状态。可能的值是：

active：后端正在执行一个查询。
idle：后端正在等待一个新的客户端命令。
idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。
idle in transaction (aborted)：这个状态与idle in transaction相似，不过在该事务中的一个语句导致了一个错误。
fastpath function call：后端正在执行一个 fast-path 函数。
disabled：如果在这个后端中track_activities被禁用，则报告这个状态。

3.1.2 常用sql

select * from pg_stat_activity;

-- 查找非空闲的SQL
select * from pg_stat_activity where state <>'idle';
-- 按照开始时间进行排序
select *,now()-query_start from pg_stat_activity where state <>'idle' ORDER BY query_start asc ;

-- kill SQL，pg_terminate_backend(PID);
SELECT pg_terminate_backend(15322);

阻塞问题：如果是非异常的阻塞，可以直接kill掉，使用pg_terminate_backend。

注意：

不是说SQL执行时间长或者是在等待锁就是有问题的，需要看具体的SQL，有些DDL就是需要加锁的，因此其他相关的操作会等待锁是正常的。
不正常距离：有很多终端列表查询的SQL，且运行时间都比较长。

其他字段的详细介绍可以进一步参考：[2] 监控数据库活动。

3.2 pg_stat_statement SQL执行统计视图

数据库资源分为多个维度、CPU、内存、IO 等，为能够从各个维度层面查找最消耗数据库资源的 SQL，您可以使用 pg_stat_statements 插件统计数据库的资源开销和分析 Top SQL。该统计视图的使用需要安装插件。

执行如下命令，在需要查询 TOP SQL 的数据库中，创建pg_stat_statements 插件。

CREATE EXTENSION pg_stat_statements;

3.2.1 视图输出内容介绍

通过查询 pg_stat_statements 视图，您可以得到数据库资源开销的统计信息。SQL 语句中的一些过滤条件在 pg_stat_statements 中会被替换成变量，可以减少重复显示的问题。

pg_stat_statements 视图包含了一些重要信息，例如：：

SQL 的调用次数，总耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描、返回或处理了多少行。
shared buffer 的使用情况：命中、未命中、产生脏块、驱逐脏块。
local buffer 的使用情况：命中、未命中、产生脏块、驱逐脏块。
temp buffer 的使用情况：读了多少脏块、驱逐脏块。
数据块的读写时间。

下表列出了 pg_stat_statements 输出内容中各参数的含义。
在这里插入图片描述

中文版(简版)

3.2.2 常见案例 Top SQL

--最耗IO SQL
--单次调用最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;
--总最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

--最耗时 SQL
--单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5; 
--总最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5; 

--响应时间抖动最严重 SQL
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5; 

--最耗共享内存 SQL
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
 
--最耗临时空间 SQL
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

注意：因为参数化的问题，这里的SQL的是不可直接执行的，我们需要根据SQL的一些信息，在IDE中找到对应的逻辑，打日志，抓到对应的SQL，进行分析。我们需要重点关注：调用次数，总耗时，平均执行时间，这3个指标，SQL执行时间长，那说明有优化的空间。

3.2.3 重置统计信息

pg_stat_statements是累积的统计，如果要查看某个时间段的统计，需要查询快照的信息，详情请参见《PostgreSQL AWR报告（for 阿里云ApsaraDB PgSQL）》。您也可以通过执行如下命令，来定期清理历史统计信息。

SELECT pg_stat_statements_reset();

3.3 pg_stat_user_tables视图显示访问特定表的统计信息

索引主要用于查询逻辑，一般用于大表。反过来，我们需要关注索引的使用情况。日常维护中观察大表（所占存储空间大、记录多、索引所占空间大的表）和索引的情况，考虑是否需要为关键字段加索引、删掉未曾使用过的索引。

3.3.1 视图输出信息介绍

pg_stat_all_tables视图将包含当前数据库中每个表的一行（包括TOAST表），显示访问特定表的统计信息。 pg_stat_user_tables和 pg_stat_sys_tables视图包含相同的信息，但是过滤只分别显示用户和系统表。
在这里插入图片描述
需要注意的数据：seq_scan，idx_scan，n_live_tup，n_dead_tup。

3.3.2 常用sql

判断表的大小

-- 问下pg怎么求大小？！
select * from pg_proc  where proname ~ 'size';
-- 按照页的数量进行排序
select * from pg_class where relnamespace = 2200 ORDER BY relpages desc;
-- 按照实体的大小进行排序
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'r' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'i' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'm' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;

关注表使用情况

-- seq_scan，idx_scan，n_live_tup，n_dead_tup
select * from pg_stat_user_tables limit 10;
select * from pg_stat_user_tables ORDER BY seq_scan desc;
select * from pg_stat_user_tables ORDER BY idx_scan desc;
select * from pg_stat_user_tables ORDER BY n_live_tup desc;
select * from pg_stat_user_tables ORDER BY n_dead_tup desc;
-- 死亡元组多的表可以考虑清一波
select 'VACUUM VERBOSE ANALYZE '|| relname || ';',* from pg_stat_user_tables ORDER BY n_dead_tup desc;

-- 排查：大表的全表扫描比索引扫描用得多的情况（考虑建索引）
select * from pg_stat_user_tables where seq_scan > idx_scan ORDER BY seq_scan desc;
select * from pg_stat_user_tables where n_live_tup > 10000 and seq_scan > idx_scan ORDER BY seq_scan desc;

3.4 pg_stat_user_indexes视图显示特定索引的统计

3.4.1 视图输出内容介绍

pg_stat_all_indexes视图将包含当前数据库中的每个索引行，显示访问特定索引的统计。 pg_stat_user_indexes和 pg_stat_sys_indexes视图包含相同的信息，但是过滤只是分别显示用户和系统索引。
在这里插入图片描述

3.4.2 常用sql

-- 查看某个表索引使用情况
select * from pg_stat_user_indexes limit 10;
select * from pg_stat_user_indexes where relname = 'test' ORDER BY idx_scan asc;

根据 pg_stat_user_tables 视图找到需要关注的表，然后根据 pg_stat_user_indexes 视图查看该表的索引使用情况。

-- -- 建议一个个分析，当然也可以合起来
select * from pg_stat_user_indexes where relname in (select relname from pg_class where relnamespace = 2200 and relkind = 'r' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc limit 10) ORDER BY relname,idx_scan asc;