sql 如何根据月份查询数据总数_PostgreSQL如何查询IO消耗最高的SQL及优化

一、安装pg_stat_statements插件

此插件用于统计数据库的资源消耗，分析SQL语句

安装流程：

cd /*/postgresql-11.1/contrib/pg_stat_statementsmakemake install

二、加载pg_stat_statements模块

--编辑postgresql.conf文件vi $PGDATA/postgresql.conf--修改配置shared_preload_libraries='pg_stat_statements'track_io_timing = on用于跟踪IO消耗的时间track_activity_query_size = 1024设置单条SQL的最长长度，超过被截断显示(可选)

三、配置pg_stat_statements采样参数

--在postgresql.conf文件添加pg_stat_statements.max = 10000           # 在pg_stat_statements中最多保留多少条统计信息  pg_stat_statements.track = all           # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)    pg_stat_statements.track_utility = off   # 是否跟踪非DML语句 (例如DDL，DCL)， on表示跟踪, off表示不跟踪    pg_stat_statements.save = on             # 重启后是否保留统计信息

修改完postgresql.conf文件后，重启数据库restart

pg_ctl —D $PGDATA restart

四、创建pg_stat_statements extension

postgres=# create extension pg_stat_statements;CREATE EXTENSIONpostgres=#

五、分析SQL

可以通过查询pg_stat_statements视图，获取统计信息

postgres=# select * from pg_stat_statements; userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time--------+------+---------+-------+-------+------------+----------+----------+-----------+-------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------(0 rows)

1.字段分析

字段名	类型	引用	描述
userid	oid	pg_authid.oid	执行该语句的用户的 OID
dbid	oid	pg_database.oid	在其中执行该语句的数据库的 OID
queryid	bigint		内部哈希码，从语句的解析树计算得来
query	text		语句的文本形式
calls	bigint		被执行的次数
total_time	double precision		在该语句中花费的总时间，以毫秒计
min_time	double precision		在该语句中花费的最小时间，以毫秒计
max_time	double precision		在该语句中花费的最大时间，以毫秒计
mean_time	double precision		在该语句中花费的平均时间，以毫秒计
stddev_time	double precision		在该语句中花费时间的总体标准偏差，以毫秒计
rows	bigint		该语句检索或影响的行总数
shared_blks_hit	bigint		该语句造成的共享块缓冲命中总数
shared_blks_read	bigint		该语句读取的共享块的总数
shared_blks_read	bigint		该语句弄脏的共享块的总数
shared_blks_written	bigint		该语句写入的共享块的总数
local_blks_hit	bigint		该语句造成的本地块缓冲命中总数
local_blks_read	bigint		该语句读取的本地块的总数
local_blks_dirtied	bigint		该语句弄脏的本地块的总数
local_blks_written	bigint		该语句写入的本地块的总数
temp_blks_read	bigint		该语句读取的临时块的总数
temp_blks_written	bigint		该语句写入的临时块的总数
blk_read_time	double precision		该语句花在读取块上的总时间，以毫秒计(如果track_io_timing被启用，否则为零)
blk_write_time	double precision		该语句花在写入块上的总时间，以毫秒计(如果track_io_timing被启用，否则为零)

2.如何获取不同情况的SQL

(1)最耗IO的SQL

单次消耗IO的前五条SQL，降序

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

总消耗IO的前五条SQL，降序

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

(2)最耗时的SQL

单次调用耗时前五条SQL，降序

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

总耗时前五条SQL，降序(最关注的)

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

(3)响应时间抖动最严重的SQL

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

(4)最耗共享内存的SQL

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

(5)最耗临时空间的SQL

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

六、重置统计信息

可以定期清理历史的统计信息，使用如下SQL

postgres=# select pg_stat_statements_reset(); pg_stat_statements_reset--------------------------(1 row)

七、优化方案

1.进行有针对的查询，避免使用select * ？

  如果是要使用count(*)计算，要使用计算行数的子查询

select count(*) from(selectidfrom userswhere preferred_language = 'zh_CN'and private_profile = True) as temp;

2.避免使用NOT IN

  避免使用IN或者NOT IN，因为这个操作会进行全表扫描。可以使用EXCEPT或NOT EXISTS来代替

3.模糊查询的优化可以参考《PostgreSQL模糊查询优化》这篇文章

4.添加适当的索引

  对表中经常进行查询、排序、分组的字段添加索引

(1)b-tree索引

b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。

postgres=# create index idx_t_btree_1 on t_btree using btree (id);

(2)hash索引

hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。hash索引特别适用于字段VALUE非常长(不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE)的情况。

如果只需要等值搜索，可以使用hash索引

postgres=# create index idx_t_hash_1 on t_hash using hash (info);

(3)gin倒排索引

当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。

--arr是数组类型postgres=# create index idx_t_gin1_1 on t_gin1 using gin (arr);

(4)gist索引

它是一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现。但Gist索引创建耗时较长，占用空间也比较大。

postgres=# create index idx_t_gist_1 on t_gist using gist (pos);

(5)sp-gist索引

SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，支持位置搜索。

postgres=# create index idx_t_spgist_1 on t_spgist using spgist (rg);

5.UNION ALL 比 UNION 快

UNION在进行表链接后会筛选掉重复的记录，UNION ALL不会去除重复记录UNION将会按照字段的顺序进行排序，UNION ALL只是简单的将两个结果合并后就返回