前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >PostgreSQL - SQL调优方案

PostgreSQL - SQL调优方案

作者头像
雨临Lewis
发布2022-03-08 16:34:09
2K0
发布2022-03-08 16:34:09
举报
文章被收录于专栏:雨临Lewis的博客

查询执行很长时间的SQL(慢SQL)

可以通过查询系统表来找到目前处于活跃状态的SQL:

代码语言:javascript
复制
SELECT * FROM pg_stat_activity WHERE datname='数据库名' 
and client_addr = '发起查询的IP地址' order by state_change desc;

有个更好的办法,是安装扩展pg_stat_statements,此处需要PostgreSql支持,部分版本需要编译安装:

代码语言:javascript
复制
CREATE extension pg_stat_statements;
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();

等待一段时间后就可以查询慢SQL:

代码语言:javascript
复制
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

查询使用Buffer次数最多的SQL:

代码语言:javascript
复制
SELECT * FROM pg_stat_statements ORDER BY shared_blks_hit+shared_blks_read DESC LIMIT 5;

分析执行计划

explain可以看到sql的执行计划(但不会去执行这条sql),explain analyze或者explain analyse则可以看到真正执行sql时的执行计划。

对于已经能够确定其性能很慢的sql不建议使用explain analyze,除非你想慢慢等它执行完再看到对应的执行计划。

PostgreSQL的执行计划会显示出这条SQL的预估成本cost,需要扫描的数据行数量rows,扫描方式(是否使用索引等),循环次数loops等。执行计划中会使用缩减符和->来表示执行时每一步的先后顺序,缩减最大的就是最早执行的SQL片段。

cost就是执行对应的SQL片段时所需要的预估成本,包含启动成本和结束成本。不同的扫描方式其启动成本不一定一样,每一步的cost都会包含上一步的成本。

width表示扫描的数据行宽度,width=0表示只获取行的位置,没有读取数据;开始读取数据后其值会大于0。

扫描方式常见的有:

  • Seq Scan:全表扫描
  • Index Scan,Bitmap Index Scan,Bitmap Heap Scan:索引扫描
  • Subquery Scan:子查询
  • Nested Loop:表连接查询,内表(一般是带索引的大表)被外表(也叫“驱动表”,一般为小表:相对其它表为小表,且记录数的绝对值也较小,不要求有索引)驱动,就是拿小表的数据根据连接条件去大表里进行连接查询
  • Hash Join:建立哈希表,由于Hash的特点只能用于等值连接(=),会将表连接的两个表数据放进内存中,需要消耗大量内存
  • Merge Join:等值或非等值连接(>,<,>=,<=,但是不包含!=,也即<>),需要对连接表进行排序,在非等值连接时,Merge Join比Hash Join更有效
  • Sort:排序
  • Unique:DISTINCT,UNION操作
  • Limit:LIMIT,OFFSET操作
  • Aggregate:count,sum,avg,stddev等聚合函数
  • Group:GROUP BY分组操作

通过分析执行计划中的成本,以及扫描方式来决定下一步怎么对SQL进行优化,下面是一些常见的调优方案。

优化表连接

主要分为两个方向:

  1. 尽量减少连接(外连接或内连接)其他表的次数
  2. 优化表连接的条件,尽可能确保连接条件足够充分

以上都是为了尽可能减少中间表的数据量,通过执行计划就可以很明显看到表连接的cost大幅降低。

另外,在能使用inner join时尽量不要使用left join,inner join可以过滤掉不少不必要的数据,从而减少中间表的数据量。

使用CTE进行预查询

公用表表达式(Common Table Expression,简称CTE),对于一个很长很复杂的sql,可以用CTE把一部分sql片段预先查询出来,该sql片段查询的结果可以被整个sql所使用。类似于在代码中抽出一个公共的方法逻辑,方便被其他方法所使用。

CTE不仅提高了可读性,还可以非常有效地提高一条复杂长sql的查询效率,多个CTE之间可以用,分隔。语法是with <表名> as (),如果被CTE定义的表名被调用两次以上,则优化器会自动将预查询的数据放入一个TEMP表中,如果只被调用一次则不会。

但不是所有数据库都有实现这个功能,PostgreSQL和SQL SERVER都有提供。样例如下:

代码语言:javascript
复制
with pre as (
    select trim(both '{}' from ch.path) as "preLabelKey",id from cnt_codelist_book_h ch limit 2
), pre2 as (
    select regexp_split_to_table(trim(both '{}' from ch.path), '}[^}]*{') as "pre2LabelKey",id from cnt_codelist_book_h ch limit 2
)
select distinct book.path,pre."preLabelKey",pre2."pre2LabelKey" from cnt_codelist_book_h book
inner join pre on pre.id = book.id
inner join pre2 on pre2.id = book.id;

--查询结果如下:
path	preLabelKey	pre2LabelKey
{lbl.codelist} > {lbl.common.version}	lbl.codelist} > {lbl.common.version	lbl.codelist
{lbl.codelist} > {lbl.common.version}	lbl.codelist} > {lbl.common.version	lbl.common.version

注:上述sql中由于别名存在大小写,因此要用双引号包括起来,否则会报错。另外这里仅仅演示语法,因此写的很随意,不喜勿怪。

优化索引

这个就不详说了,不外乎对查询条件建立索引,注意使用联合索引时的字段顺序,不过PostgreSQL对于联合索引似乎会自动优化查询时的字段顺序。

参考链接

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022-03-032,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 查询执行很长时间的SQL(慢SQL)
  • 分析执行计划
  • 优化表连接
  • 使用CTE进行预查询
  • 优化索引
  • 参考链接
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档