企业官网建设流程全解析

跟数据打交道久了，多少都会碰到这种尴尬：某个SQL写法，明明几个月前刚用过，真到要用的时候死活想不起完整语句，只能现场翻文档、翻笔记，甚至翻聊天记录。我自己的解决办法很笨——从某个时间点开始，凡是工作中验证过、确实好用的PostgreSQL SQL，我都按场景攒起来，遇到一次记一次。这份PostgreSQL常用SQL合集就是这么攒出来的，今天先放出第一版。

合集不按语法分类，而是按“什么场景下我会翻它”来组织。这样有两类人最适合参考：一类是刚接触PostgreSQL、需要一份能直接抄的常用语句清单的开发；另一类是天天跟数据库打交道，但不想为“查个锁、看个表大小”重复查资料的维护人员。后续看情况，我会持续往里面补新场景，这篇就先从使用频率最高的查数、性能、维护、运维、统计这几块开始。

1. 攒这份合集的原则：按场景收录，不搞官方文档式排版

1.1 为什么我不推荐按系统表、语法维度整理

网上已经有很多PostgreSQL手册、语法速查表，但真到用的时候你会发现，纯粹按“SELECT怎么写、INSERT怎么写”来整理，遇到实际问题照样找不到答案。原因很简单：我们工作时面临的都是复合问题，比如“订单表太大，想快速看哪个用户最近下单多”，这个问题拆开是JOIN、聚合、排序、时间过滤的组合，散落在不同语法章节里，临时拼装要花时间，还容易拼错。

所以我整理合集的第一个原则是以问题为入口。今天因为“删除重复数据”翻了资料，就把去重方案记下来；明天因为“业务一直报锁等待”去查了锁视图，就把查锁SQL记下来。下次同样的问题出现，直接复制这条记录，改表名就能用。相比手册式排版，这种记录法可能不够系统，但胜在快，而且每条都是我实际跑过的。

1.2 每条SQL的收录格式

为了让合集真正能在日后“无脑复现”，我给自己定了收录格式，每条至少包含三块：

• 使用场景：这行SQL解决什么问题，比如“查出占用空间最大的前20张表”。
• 可直接运行的SQL：统一用标量/公共表表达式写清楚，表名尽量用业务无关的通用词，比如orders、users，方便复制后替换。
• 注意事项：哪个版本不支持、有没有权限要求、哪些情况不能用。

第三块最重要。很多SQL写法正确，但在特定环境下会翻车，这些坑不写成备注，过几个月再看很容易忘。

2. 高频查数：联表、聚合与去重的一线写法

2.1 JOIN 的写法，以及一个容易踩的坑

日常统计里最常用的还是JOIN。我一般用显式 JOIN 而不是逗号拼接表，条件写进ON里。举个例子，查最近7天下单用户的信息：

SELECT o.order_no, c.customer_name, o.total_amount FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at >= CURRENT_DATE - INTERVAL '7 days' ORDER BY o.total_amount DESC;

有人习惯把过滤条件也塞进ON，觉得早过滤早好，这里要分情况。内连接（INNER JOIN）里，过滤条件放ON后面和放WHERE后面，结果一样，执行器一般也能优化到相同的计划；但外连接（LEFT JOIN / RIGHT JOIN）就不一样了。LEFT JOIN场景下，右表条件如果写在ON里，只是决定右表匹配不到时是否补NULL；写在WHERE里，则会直接过滤掉左表未匹配的行，相当于把外连接变成了内连接。这个差异很经典，遇到“明明用了LEFT JOIN却少了数据”的怪问题，先检查右表条件放哪了。

2.2 GROUP BY 和 HAVING：分组后过滤别用WHERE

统计类SQL的另一半是聚合。按月/按状态汇总这类需求，基本逃不开GROUP BY。

SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS total_amount, AVG(total_amount) AS avg_amount FROM orders WHERE created_at >= date_trunc('month', CURRENT_DATE) GROUP BY status HAVING COUNT(*) > 100 ORDER BY total_amount DESC;

HAVING 和 WHERE 的执行时机不一样：WHERE先过滤行，再分组聚合；HAVING在分组之后过滤聚合结果。所以“统计金额超过XX的状态”这种条件，必须放HAVING，放WHERE里会直接报错，因为聚合函数不能出现在WHERE中。顺带说一句，COUNT(*) 和 COUNT(1) 在PostgreSQL里没有实质性能差异，但COUNT(distinct col)在大表上很重，是典型的慢SQL源头，能提前去重缩小范围就尽量缩小。

2.3 删除重复数据：ctid 方案的边界

去重是日常维护的高频需求。假设业务表因为重复导入产生了完全相同的行，且表没有可用的业务唯一键，我以前最常用的是ctid方案：

DELETE FROM target_table t USING target_table t2 WHERE t.business_key = t2.business_key AND t.ctid < t2.ctid;

ctid是PostgreSQL里的物理行标识，同一行在表里位置越靠前ctid值越小。这条SQL等于说：同一业务键下有多行时，保留物理位置最靠前的那一行，其余全删。

有几点必须提醒。第一，ctid在分区表中只是在分区内唯一，不是全局唯一，对分区表直接跑全表去重会误删，得逐分区执行。第二，ctid会被VACUUM重写，所以不要在表刚做过清理、物理位置变化后，依赖ctid做跨事务记录。第三，这种全表USING删除在数据量大时压力不小，建议先按业务键分组统计一下重复量，再决定要不要分批删。

3. 排查慢查询：先看执行计划再谈优化

3.1 EXPLAIN ANALYZE 的正确打开方式

遇到一条SQL慢，我的第一反应永远是看执行计划，而不是凭感觉加索引。只看EXPLAIN不带ANALYZE，只能看到估算；加上ANALYZE才会真正执行语句并输出实际数据。

EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_no, o.total_amount FROM orders o WHERE o.customer_id = 12345 AND o.created_at >= CURRENT_DATE - INTERVAL '30 days' ORDER BY o.created_at DESC;

看执行计划时，我最关注三个点：

• 有没有出现Seq Scan，且被扫表的过滤性其实很高；
• 计划里预测的rows和实际rows差距是否过大；
• 排序、哈希操作是否把内存耗爆了。

BUFFERS选项会显示实际命中的buffer页数，对判断“是否走索引后还要回表大量读取”很有帮助。实际行数与估算行数严重不符的情况，通常指向统计信息过期。

3.2 统计信息过期与 autovacuum

执行计划里rows和actual rows差一个数量级，最常见的根源是表的统计信息没跟上。PostgreSQL靠统计信息估算行数，而统计信息由autovacuum自动采集，如果表更新频繁、大批量删除之后又没及时触发analyze，估算就会失真。

SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders';

看到这些字段全是0或者dead元组数量飙升，说明表很长时间没被正常分析了。可以先手动跑ANALYZE orders; 再看计划是否恢复。长事务、大量UPDATE/DELETE后deadtuple堆积，也容易让SQL走错计划，这个因素比想象中常见。

3.3 用 pg_stat_statements 抓最拖库的语句

单条SQL排查完，还要站在全局看哪些语句消耗最高。PostgreSQL自带的统计扩展pg_stat_statements非常好用，前提是先在配置里开启并创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

然后直接查：

SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

需要注意版本差异：PostgreSQL 13以前，执行耗时字段叫total_time；13及以后拆成了total_exec_time和total_plan_time。如果库比较老，字段名会不一样，先DESCRIBE一下或者查一下视图结构再跑。另外，这个视图默认按总量排序，如果想找“单次平均最慢”的，改成按mean_exec_time排。

4. 索引使用：别只会建B-tree，这几个场景容易选错

4.1 索引类型选型

PostgreSQL自带多种索引类型，日常最常用的就几种，选错类型往往不是不能用，而是性能上限低。

绝大多数场景用默认btree就够了，不必追求花哨。真正出问题的是没有创建合适的btree索引，或者错误地把GIN当成万能索引用。

4.2 联合索引的字段顺序决定效果

联合索引是最容易“建了等于没建”的地方。假设高频查询是“某个客户的订单，按下单时间倒序”，最佳索引一般是：

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

字段顺序的原则是：等值条件放前面，范围条件放后面。customer_id = ?是等值过滤，created_at是范围排序，所以customer_id在前。如果反过来建，查询只有created_at条件时也许能用上索引，但要是查询给定customer_id同时排序，这个联合索引反而帮不上忙，因为前面字段就卡住了。

顺便提一句，PostgreSQL的btree索引支持反向扫描，所以即使索引创建时不写DESC，单列排序也能利用。但联合索引里方向写清楚，能让执行器少做反向扫描，排序更稳定。

4.3 线上建索引的安全姿势

表一旦上了生产，直接CREATE INDEX会锁住表，期间写入全部卡住，影响太大。低峰期也尽量用并发模式：

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

CONCURRENTLY的意思是不长时间持锁，索引构建过程允许读写继续，代价是耗时更长、不能放在事务块里执行。还有个坑：如果建到一半失败，PostgreSQL会留下一个无效索引，占用空间且后续自动维护可能报错。所以要养成建完之后查一下索引状态的习惯：

SELECT indexrelid, indrelid, indisvalid FROM pg_index WHERE indrelid = 'orders'::regclass;

indisvalid为false就说明这个索引是坏的，需要手动DROP。

5. 数据维护脚本：批量更新、迁移、复制的高效姿势

5.1 批量更新：一条UPDATE关联，别用循环

用程序逐条UPDATE一批数据是最常见的性能灾难，尤其在量过万之后。PostgreSQL支持UPDATE ... FROM的关联更新语法，一次把整批数据交进去：

UPDATE users u SET balance = s.balance, updated_at = NOW() FROM stage_users s WHERE u.id = s.user_id AND u.balance IS DISTINCT FROM s.balance;

我习惯把最后一行写成IS DISTINCT FROM，这跟!=不同，两边都是NULL不会判定为相等冲突，能精准找到“真的变了”的行。更重要的是，这个条件会跳过值没有变化的行，避免产生空的更新事务和大量WAL日志。对超大表做全量更新时，建议再按主键ID范围分批执行，比如每批5万行，把单事务时间控制住，不然旧版本堆积会让后续VACUUM压力巨大。

5.2 建表复制与导入导出

快速复制一张表的结构，很多人第一反应是CREATE TABLE new AS SELECT ...，但这样建出来的表不包含索引、约束、默认值。如果想完整复制结构而不复制数据，应该用LIKE：

CREATE TABLE orders_copy (LIKE orders INCLUDING ALL);

INCLUDING ALL会带上存储参数、约束、索引、默认值等，在建测试表、备库临时表时特别省事。

导入导出方面，容易搞混的是COPY和\copy。COPY在数据库服务端执行，文件路径是服务器本地路径，通常需要超级用户权限；\copy是psql的客户端命令，文件路径相对你本地机器，普通用户也能用。日常操作大多用\copy：

-- 导出为CSV \copy orders TO '/tmp/orders_2025.csv' WITH CSV HEADER; -- 从CSV导入 \copy orders FROM '/tmp/orders_2025.csv' WITH CSV HEADER;

CSV带HEADER导入时，PostgreSQL会自动跳过首行。如果字段里有逗号、引号，默认CSV模式就能处理，不用特意处理转义。

5.3 数据质量校验的几条实用SQL

维护时经常要快速判断字段是否有脏数据，这种“找异常”的SQL也属于合集里反复被翻的类型。比如邮箱、电话这类格式字段：

SELECT id, email FROM users WHERE email IS NOT NULL AND email !~ '@';

这里用了操作符!~，就是“不匹配正则”的意思。类似地，找出某个业务键出现次数过多，可以在应用层接收前先排查：

SELECT business_key, COUNT(*) FROM orders GROUP BY business_key HAVING COUNT(*) > 5 ORDER BY COUNT(*) DESC;

这类校验SQL不需要写得很复杂，重点是把“大概情况”摸出来，再决定要不要修。

6. 运维现场救命的SQL：连接、锁、表膨胀一眼看清

6.1 看当前连接都在干什么

系统一旦告警“连接数满了”，第一步不是重启，而是看连接状态分布：

SELECT pid, usename, state, wait_event_type, wait_event, query, query_start FROM pg_stat_activity WHERE datname = current_database() ORDER BY query_start;

重点关注state字段：active是正在执行SQL；idle是空闲连接；idle in transaction表示连接开了事务什么都没做，这种最坑，可能锁着资源不放；idle in transaction (aborted)更危险，事务里出错了还没回滚。排查时先统计每种state的数量，要是idle in transaction大量堆积，多半是应用层事务没有正确关闭。

6.2 查锁与快速解除阻塞

真正常见的锁问题，表现为某个简单的UPDATE卡住不动。用下面这条能直接看到谁在等谁：

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY (blocked.pg_blocked_pid()) WHERE blocked.pid <> blocking.pid;

pg_blocked_pid()是PostgreSQL提供的函数，可以找出当前PID被哪些PID阻塞。查到源头PID后，先尝试用pg_cancel_backend取消它正在执行的SQL：

SELECT pg_cancel_backend(12345);

如果取消不掉，再用pg_terminate_backend终止连接：

SELECT pg_terminate_backend(12345);

终止连接会把事务回滚，是更强的操作，生产环境用之前最好确认一下。另外，可以提前给会话设置lock_timeout，避免一条SQL无脑等待锁到天荒地老：

SET lock_timeout = '5s';

6.3 表大小、死元组、膨胀率体检

表膨胀和死元组是PostgreSQL运维里绕不开的话题。定期跑一条SQL，就能把所有用户表按“健康度”排序：

SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup > 0 THEN round(n_dead_tup * 100.0 / n_live_tup, 2) ELSE 0 END AS dead_pct, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, last_autovacuum FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 30;

dead_pct超过20甚至30，而且last_autovacuum很久没更新，说明自动清理没跟上。可以先手动VACUUM：

VACUUM (ANALYZE) orders;

这里特别提醒：不要顺手就上VACUUM FULL。VACUUM FULL会重写整张表并锁表，对在线服务影响很大，应该是低峰期最后手段，不是日常保养操作。

7. 窗口函数在统计报表里的四个高频场景

7.1 分组Top N

“每个部门工资最高的前三个人”这类需求，在PostgreSQL里最推荐的写法是窗口函数：

SELECT dept_id, employee_name, salary FROM ( SELECT dept_id, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn <= 3;

ROW_NUMBER、RANK、DENSE_RANK的区别要分清：ROW_NUMBER对同值行会随机分配不同序号，不会出现并列；RANK同值并列，后面序号会跳；DENSE_RANK同值并列但序号不跳。业务要求“前三名”且有并列时，通常用DENSE_RANK更合理，用ROW_NUMBER会把并列的人挤掉。

7.2 环比、同比与差值

窗口函数里的LAG可以拿到上一行的值，做环比特别方便：

SELECT dt, amount, LAG(amount, 1) OVER (ORDER BY dt) AS prev_amount, amount - LAG(amount, 1) OVER (ORDER BY dt) AS diff, ROUND( (amount - LAG(amount, 1) OVER (ORDER BY dt)) * 100.0 / NULLIF(LAG(amount, 1) OVER (ORDER BY dt), 0), 2 ) AS pct_change FROM daily_sales ORDER BY dt;

LAG的第二个参数是偏移量，1表示上一行；LEAD同理是拿下一行。计算比例时用NULLIF避免除零错误，这也是一个容易被忽略的小细节。

7.3 累计值与移动平均

业务报表常要展示“截止当前累计”和“最近N天平均”，窗口函数比子查询简洁得多：

SELECT dt, amount, SUM(amount) OVER (ORDER BY dt) AS running_total, AVG(amount) OVER ( ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7d FROM daily_sales ORDER BY dt;

SUM配合ORDER BY，不加ROWS范围时默认统计到“当前行往前所有行”，正好是累计；移动平均要显式写ROWS/ RANGE框架，指定窗口边界。实际跑大表时，如果只需要最近30天，建议先在外层用时间条件过滤，别让窗口函数扫全表。

这版合集先更新到这里，核心都是这几年在一线踩坑之后、确实“每次想不起来还得翻”的场景。后面我会按节奏补JSON与JSONB操作、全文检索、分区表维护、逻辑复制这几块，遇到好用的SQL就直接回填。你如果手里也有那种“不值钱但离了真不方便”的常用语句，欢迎一起往里加，这种实用型合集，靠一个人攒还是慢了点。