优化慢查询的方法:
首先,使用 explain select … 分析查询计划,找出耗时部分
0、提升数据库服务器硬件,如CPU、内存、磁盘I/O等;
1、调整Pg的配置参数,如shared_buffers、work_mem、effective_cache_size等;
2、拆分大表,将大表拆分成小表,减少每张表的数据量;
3、建立分区,并在查询语句where中使用分区字段,避免全表扫描;
4、拆分宽表为细表,减少查询字段数量;
5、建立索引并使用索引过滤数据;
*6、优化查询语句:如避免使用select *,使用select field1,field2…; 使用limit限制返回结果数量;
避免使用大量子查询和联合查询,考虑使用临时表;
7、及时删除不再使用的数据、更新后未被占用的索引,清理空间的任务应该定期地运行;
8、使用连接池,连接状态的建立和销毁在数据库中使用大量资源,连接的复用会使情况得到改善;
9、适当调整数据库连接池的大小,避免数据库中存在大量的并发连接;
10、优化事务隔离级别或调整锁等待时间,避免数据库中存在大量的锁争用;
11、避免数据库中存在大量的自动化任务,例如备份、清理等,适当调整任务执行时间或优化任务执行方式;
查看耗时较长的查询
SELECT
pid, now(), now()-query_start as query_duration, query
FROM
pg_stat_activity
WHERE datname = 'performdb'
AND pid != pg_backend_pid()
AND state != 'idle'
ORDER BY query_duration DESC;