PG数据库里那些难搞的关键SQL，怎么调才稳又快点

直接说正事，PG数据库，也就是PostgreSQL，性能调优这事儿，真不是背几个命令就完事的，它像个精密的机械表，你得懂它什么时候该紧一紧，什么时候该松一松，下面这些，就是那些经常让人头疼，但又必须搞定的关键SQL和调优思路,咱们用大白话聊清楚。

最要命的慢SQL：关联查询和聚合操作

当你发现一个查询好几张表，还带着GROUP BY、DISTINCT这种操作，速度突然慢下来了，别急着怪数据库，先看看执行计划，PG有个超级好用的命令叫EXPLAIN (ANALYZE, BUFFERS)，光用EXPLAIN是纸上谈兵，它只告诉你计划怎么走；加上ANALYZE，它才会真正执行一下，告诉你实际花了多少时间，这才是实打实的。BUFFERS能告诉你用了多少缓存,这对判断是不是内存不够导致的磁盘读写慢特别有用。

怎么调？

1. 加索引，但要加对地方：这是老生常谈，但很多人加错了，关联查询慢，优先看关联条件（ON后面的字段）和WHERE子句里的过滤条件有没有索引，别动不动就给所有字段都加索引，索引也占地方，写数据的时候还会变慢，一个复合索引（把两个或多个字段绑在一起建的索引）比一堆单字段索引管用得多，比如你经常按时间和用户ID查，建个(时间, 用户ID)的复合索引可能效果拔群。
2. 调整work_mem参数：这个参数专门管那些排序（ORDER BY）、哈希关联和聚合（GROUP BY）操作能用的内存大小，如果执行计划里出现了Disk: ... temp这样的字眼，就说明排序之类的东西在硬盘上进行了，那肯定慢，适当调大work_mem（比如从默认的4MB调到32MB或更高，看你的服务器内存大小），让这些操作在内存里完成，速度能飞起来，这个参数可以在会话级别单独设置，对某个特别耗资源的查询单独“开小灶”,不影响别人。
3. *避免`SELECT **：特别是表里有很多字段，或者有那种超大文本字段（比如TEXT`类型）的时候，你查的字段越少，需要搬运的数据量就越小，数据库干活自然就轻快,老老实实把需要的字段名写出来。

让人头疼的并发更新：锁等待和死锁

PG的默认隔离级别是“读已提交”，这本身没啥问题，但当很多人同时更新同一行或者一个范围内的数据时，锁的问题就来了，表现就是，你的应用感觉“卡住了”，某个请求半天没反应,可能就是它在等别人释放锁。

怎么调？

1. 让事务“短小精悍”：这是黄金法则，一个事务里别塞进去一大堆SQL操作，特别是那些耗时的查询，尽快完成更新，然后提交事务，把锁释放掉，别在事务里干一些无关的操作，比如更新完数据再去调个外部接口,这期间锁一直占着。
2. 搞清楚锁的类型：用SELECT * FROM pg_locks这样的语句可以查看当前数据库里的锁情况，你要重点关注的是RowExclusiveLock（行排他锁，更新、删除时会加）和ExclusiveLock（排他锁，比如CREATE INDEX时会加）的冲突，如果发现等待，找到是哪个SQL引起的,优化它或者调整业务逻辑。
3. 慎用SELECT ... FOR UPDATE：这个语句是“悲观锁”，意思是“我查到这行就是为了待会要改，你们谁也别动”，用不好很容易造成大量等待，如果业务允许，可以考虑用乐观锁，比如在表里加个version版本号字段，更新的时候带上前置条件WHERE id=? AND version=?，如果版本号变了，说明别人改过了，这次更新就失败重试,这样能减少锁竞争。

神出鬼没的慢查询：参数设置不合理

SQL本身写得没问题，索引也建了，但就是不稳定，时快时慢，这很可能跟PG的一些“内心戏”有关。

1. 随机采样random_page_cost：PG在计算执行计划时，会估算从磁盘读数据的成本，默认认为顺序读（比如全表扫描）的成本是1，而随机读（比如通过索引跳着读）的成本是4，这个比值在传统机械硬盘时代很合理，因为磁头跳来跳去确实慢，但现在很多服务器都用SSD固态硬盘了，随机读和顺序读的速度差距没那么大，如果你用的是SSD，可以尝试把这个参数调小，比如调到1.5或2.0，这会“鼓励”查询优化器更愿意使用索引扫描，而不是全表扫描，这个参数在postgresql.conf文件里改,改完要重启数据库生效。
2. 统计信息不准：PG靠统计信息来估算一个查询会返回多少行，从而选择最优计划，如果统计信息过时了，它可能就会选错计划，它以为某个条件只能查出10条数据，用了索引，结果实际有100万条，用索引反而慢，定期对表执行ANALYZE 表名;，或者打开autovacuum的自动分析功能，确保统计信息是新的，对于数据变化特别频繁的表,可能需要更频繁地手动分析。
3. 连接池问题：如果你的应用是短连接，频繁地创建和断开数据库连接，开销很大，考虑使用连接池，比如PG自带的pgbouncer，连接池帮你管理一批常驻的连接，应用来要连接的时候，直接从池子里分配一个现成的，用完了还回来，避免了反复建立连接的开销,这对提升整体并发性能非常关键。

终极武器：寻求数据库自身的“诊断报告”

PG有很多强大的扩展和视图,能帮你看到更深层的问题。

• pg_stat_statements扩展：这绝对是PG调优的“神器”，它像个黑匣子，记录下所有执行过的SQL及其耗时、调用次数等，你先要修改postgresql.conf，加上shared_preload_libraries = 'pg_stat_statements'，然后重启数据库，再执行CREATE EXTENSION pg_stat_statements;，之后，你就可以用SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;这样的查询，直接找出“最耗时”的TOP N SQL,精准打击。
• *`pgstat视图**：比如pg_stat_user_tables可以看表的扫描次数、索引使用次数；pg_statio_user_tables可以看缓存命中率，如果发现某个表的缓存命中率很低，说明它经常要从磁盘读，可能就需要考虑调整shared_buffers`（PG自己的缓存大小）或者优化相关查询。

总结一下核心思想：调优没有一劳永逸的银弹，它是个“观察-假设-验证”的循环，先用EXPLAIN ANALYZE和pg_stat_statements找到瓶颈，然后提出假设（加个索引会不会好？”），再测试验证，参数调整要谨慎，一次只改一个，观察效果，最重要的是，结合你的具体业务逻辑来思考，有时候改一小行业务代码,比在数据库层面折腾半天效果还好。