本篇内容主要讲解“怎么使用PostgreSQL的pg_stat_statements”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用PostgreSQL的pg_stat_statements”吧!

pg_stat_statements提供了一种跟踪所有SQL语句的手段。

进入pg_stat_statements目录,执行make/make install

[pg12@localhostpg_stat_statements]$lsexpectedpg_stat_statements--1.4--1.5.sqlpg_stat_statements.controlMakefilepg_stat_statements--1.4.sqlpg_stat_statements.opg_stat_statements--1.0--1.1.sqlpg_stat_statements--1.5--1.6.sqlpg_stat_statements.sopg_stat_statements--1.1--1.2.sqlpg_stat_statements--1.6--1.7.sqlpg_stat_statements--unpackaged--1.0.sqlpg_stat_statements--1.2--1.3.sqlpg_stat_statements.csqlpg_stat_statements--1.3--1.4.sqlpg_stat_statements.conf[pg12@localhostpg_stat_statements]$makemake-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend'[pg12@localhostpg_stat_statements]$makeinstallmake-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/install-c-m755pg_stat_statements.so'/appdb/xdb/pg12beta1/lib/postgresql/pg_stat_statements.so'/usr/bin/install-c-m644./pg_stat_statements.control'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m644./pg_stat_statements--1.4.sql./pg_stat_statements--1.6--1.7.sql./pg_stat_statements--1.5--1.6.sql./pg_stat_statements--1.4--1.5.sql./pg_stat_statements--1.3--1.4.sql./pg_stat_statements--1.2--1.3.sql./pg_stat_statements--1.1--1.2.sql./pg_stat_statements--1.0--1.1.sql./pg_stat_statements--unpackaged--1.0.sql'/appdb/xdb/pg12beta1/share/postgresql/extension/'[pg12@localhostpg_stat_statements]$

使用前需在配置文件中添加预加载共享链接库,否则会报错:

testdb=#createextensionpg_stat_statements;CREATEEXTENSIONtestdb=#select*frompg_stat_statements;psql:ERROR:pg_stat_statementsmustbeloadedviashared_preload_libraries

在postgresql.conf文件中修改shared_preload_libraries参数:

[pg12@localhostpg12db1]$grep'shared_preload'postgresql.confshared_preload_libraries='pg_stat_statements'#(changerequiresrestart)

注意:修改该参数后需要重启数据库实例.

下面是简单的使用:

testdb=#\xExpandeddisplayison.testdb=#select*frompg_stat_statements;-[RECORD1]-------+---------------------------------userid|10dbid|16384queryid|6343734953611294604query|select*frompg_stat_statementscalls|2total_time|0.618297min_time|0.303231max_time|0.315066mean_time|0.3091485stddev_time|0.00591750000000002rows|2shared_blks_hit|0shared_blks_read|0shared_blks_dirtied|0shared_blks_written|0local_blks_hit|0local_blks_read|0local_blks_dirtied|0local_blks_written|0temp_blks_read|0temp_blks_written|0blk_read_time|0blk_write_time|0-[RECORD2]-------+---------------------------------userid|10dbid|16384queryid|-2568307067200732111query|select*fromt1calls|1total_time|876.219199min_time|876.219199max_time|876.219199mean_time|876.219199stddev_time|0rows|1100000shared_blks_hit|0shared_blks_read|5946shared_blks_dirtied|0shared_blks_written|0local_blks_hit|0local_blks_read|0local_blks_dirtied|0local_blks_written|0temp_blks_read|0temp_blks_written|0blk_read_time|0blk_write_time|0

查询汇总统计信息

testdb=#SELECTquery,(total_time/1000/60)astotal,(total_time/calls)asavgFROMpg_stat_statementsORDERBY1DESCLIMIT100;query|total|avg-------------------------------------+------------------------+---------------------SELECT+|1.5928199999999998e-05|0.318564(total_time/$1/$2)astotal,+||(total_time/calls)asavg,+||query+||FROMpg_stat_statements+||ORDERBY1DESC+||LIMIT$3||SELECT+|4.9146500000000005e-06|0.294879(total_time/$1/$2)astotal,+||(total_time/calls)asavg+||FROMpg_stat_statements+||ORDERBY1DESC+||LIMIT$3||select*fromt1|0.014603653316666666|876.219199select*frompg_stat_statements|1.5696116666666667e-05|0.31392233333333336(4rows)...testdb=#testdb=#SELECTtestdb-#query,testdb-#round((total_time/1000/60)::numeric,4)astotal,testdb-#round((total_time/calls)::numeric,4)asavgtestdb-#FROMpg_stat_statementstestdb-#WHEREquerylike'%t1%'testdb-#ORDERBY1DESCtestdb-#LIMIT100;query|total|avg--------------------------------+--------+----------select*fromt1whereid=$1|0.0022|129.9684select*fromt1whereid<$1|0.0020|121.3334select*fromt1|0.0146|876.2192(3rows)

查询条件中id = xx,实际值被替换为占位符($x).

到此，相信大家对“怎么使用PostgreSQL的pg_stat_statements”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！