PostgreSQL常用SQL速查：执行计划与锁等待排查

PG 常用 SQL 这个话题看着基础但真到了项目里手边没一份能直接抄的语句清单效率会掉一大截。我维护过几个数据量从几百万到上亿行的库踩过的坑大多不是不会写 SQL而是记不清某个系统视图叫什么、某个参数该怎么查、某个报错到底该看哪张表。所以我把日常高频用到的语句按场景重新梳理了一遍从最普通的一条 SELECT 到执行计划拆解、锁等待排查尽量做到拿过来改个表名就能跑。这篇内容适合刚上手 PG 的后端同学也适合用惯了别的数据库、想快速把思路切到 PG 上的老手读完至少能对什么场景该翻哪类 SQL有个清晰印象而不是每次遇到问题都去搜索引擎里翻半天。1. PG 常用 SQL 的整体思路与场景拆解1.1 为什么不建议死记语法而要按场景归档SQL 语法本身不多SELECT、INSERT、UPDATE、DELETE 加上 DDL一页纸能写完。但真正让人卡住的从来不是语法而是我知道要做这件事但不知道 PG 里对应的入口在哪。比如想看看哪条 SQL 最耗 CPU得去pg_stat_statements想看看现在有没有锁等待得去pg_locks和pg_stat_activity对着看想确认某张表是不是膨胀了得算死元组比例。这些都不是语法问题是知识索引问题。我的做法是按使用频率和紧急程度把常用 SQL 分成三层。第一层是天天要写的条件查询、聚合、分页、UPSERT、批量导入这层追求的是顺手和不出错。第二层是改表结构、加索引、调字段类型时才用的 DDL这层追求的是知道代价在哪别在业务高峰期执行。第三层是出问题时才翻的排查语句这层追求的是准确因为排查时你只有一两次机会查错了方向会浪费大量时间。这三层的记忆方式完全不同第一层靠肌肉记忆第二层靠查文档加验证第三层靠提前准备好脚本。1.2 三类 SQL 的使用边界与常见误用第一层查询类 SQL 最容易出的问题是能跑但慢。很多人写分页直接LIMIT 20 OFFSET 100000小表没事大表上就是灾难因为 PG 要扫描并丢弃前十万行。这类语句必须知道它的边界在哪超过一定偏移量就得换成基于游标或者基于索引键的分页。第二层 DDL 的边界在于锁。ALTER TABLE ... ADD COLUMN在 PG 11 之后加带默认值的列已经很快了但改字段类型、加约束、建索引默认都会拿强锁大表上执行可能直接把业务堵死。我见过有人在千万级表上直接跑ALTER TABLE ... ALTER COLUMN ... TYPE结果锁等了十几分钟连接池全满。正确的做法是用CREATE INDEX CONCURRENTLY、分批次回填、或者干脆走新列 双写 切换的路子。第三层排查语句的边界在于别在从库上查主库的状态。有些系统视图在只读副本上是空的或者滞后的查之前先确认pg_is_in_recovery()的返回值。另外排查语句本身也会消耗资源EXPLAIN ANALYZE会真实执行语句在写操作上千万别随手加 ANALYZE。1.3 一份适合自己的 SQL 速查清单该怎么建我自己的习惯是建一个pg_cheatsheet.sql文件按注释分块每块前面写清楚什么时候用。比如查表膨胀这块下面放两条一条算死元组比例一条算表实际占用空间。用的时候直接搜注释关键字比搜语法快得多。清单里我建议至少覆盖这几类连接与库表信息、查询与分页、聚合与窗口、写入与冲突处理、DDL 与索引、执行计划、锁与活动会话、空间与膨胀、权限、备份导出。每一类不用多三到五条高频的就够多了反而记不住。下面几个章节我就按这个思路展开把我实际用得最多的语句和背后的判断逻辑都写出来。2. 日常查询与数据操作的常用 SQL2.1 条件查询里的几个高频写法与坑最基础的查询没什么好说的真正值得记的是几个 PG 特有的写法。模糊匹配上LIKE对大小写敏感ILIKE不敏感很多人从别的库迁过来会习惯性写LOWER(col) LIKE %x%其实用ILIKE更直观不过两者都没法走普通 B-tree 索引数据量大时得考虑pg_trgm扩展加 GIN 索引。-- 大小写不敏感匹配 SELECT id, name FROM users WHERE name ILIKE %keyword%; -- 判断 NULL 用 IS NULL别用 NULL SELECT id FROM orders WHERE paid_at IS NULL; -- NULL 安全的等值比较 SELECT id FROM orders WHERE shipped_at IS NOT DISTINCT FROM NULL;IS NOT DISTINCT FROM这个写法知道的人不多它在做两个字段是否相等且都能为 NULL的判断时非常有用比如数据比对场景用会把 NULL 行漏掉。分页是另一个高频踩坑点。深分页必须换成基于键的分页-- 不推荐深偏移 SELECT * FROM logs ORDER BY id LIMIT 20 OFFSET 500000; -- 推荐基于上一页最后一行的 id SELECT * FROM logs WHERE id 500000 ORDER BY id LIMIT 20;这里的前提是id有索引且单调如果排序字段不唯一需要带上第二排序键做游标比如(created_at, id) (2024-01-01, 123)这种元组比较PG 对行值比较支持得很好可以直接写在 WHERE 里。还有一个很实用的写法是DISTINCT ON它能在每个分组里取第一条比窗口函数写起来短-- 每个用户最新的一笔订单 SELECT DISTINCT ON (user_id) user_id, id, amount, created_at FROM orders ORDER BY user_id, created_at DESC;注意DISTINCT ON后面的列必须和 ORDER BY 的前缀一致否则结果不确定这是它唯一的硬约束。2.2 聚合、分组与窗口函数的实战用法聚合里最容易被忽略的是FILTER子句它能在一次扫描里算出多个口径不用写多个子查询SELECT count(*) AS total, count(*) FILTER (WHERE status paid) AS paid_cnt, count(*) FILTER (WHERE status refunded) AS refunded_cnt, sum(amount) FILTER (WHERE status paid) AS paid_amount FROM orders WHERE created_at now() - interval 7 days;这个写法在做报表和看板时特别划算因为表只扫一遍。相比CASE WHEN包在聚合里FILTER语义更清楚执行计划也更干净。窗口函数我日常用得最多的三个row_number()做分组排序取 TopNlag()/lead()做同比环比sum() OVER做累计。取每个分类下销量前三SELECT * FROM ( SELECT category_id, product_id, sales, row_number() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM product_stats ) t WHERE rn 3;这里有个实操细节如果sales有并列且你希望并列的都保留就得用rank()或dense_rank()row_number()会强行给每一行不同编号并列的会被截掉。这个区别在排行榜类需求里非常关键选错了业务方会来问为什么少了一条。累计求和的标准写法是sum(x) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)不写ROWS子句时默认是RANGE模式遇到相同排序键会一次算完整个组结果和逐行累加不一样。这一点我在做日活累计曲线时被坑过一次数据对不上最后就是补了ROWS子句解决的。2.3 增删改与批量写入的核心套路写入这块PG 的INSERT ... ON CONFLICT是必会的也就是常说的 UPSERTINSERT INTO user_points (user_id, points, updated_at) VALUES (1001, 10, now()) ON CONFLICT (user_id) DO UPDATE SET points user_points.points EXCLUDED.points, updated_at now();这里有两个细节值得说。第一冲突目标必须是唯一索引或主键写列名或者写约束名都行但列上必须真的有唯一约束否则会直接报错。第二EXCLUDED指的是这次准备插入的那行做累加时用它和原表列运算语义最清楚。如果只想在冲突时什么都不做用DO NOTHING但要注意它会静默丢弃业务上如果依赖插入结果判断得配合RETURNING。RETURNING是 PG 很好用的一个特性INSERT、UPDATE、DELETE 都能带DELETE FROM sessions WHERE expire_at now() RETURNING id, user_id;做批量清理时直接拿到删掉的主键省一次查询。批量插入时用它拿自增 ID 也很常见不过要注意大批量RETURNING会占内存几万行以上建议分批。批量写入还有两个实操建议。一是用COPY代替多值 INSERT 做初始化导入速度差一个数量级二是多值 INSERT 每批控制在 500 到 1000 行太多会撞上参数上限太少网络往返浪费。更新时如果涉及大范围数据记得加LIMIT或者分批按主键区间推进避免一个事务锁住整张表。3. 表结构管理与 DDL 常用语句3.1 建表时的类型选择与默认值设计建表看着简单但类型选错了后面改起来很痛苦。主键我一般用bigint generated by default as identity比serial更符合标准也比 UUID 在主键索引上更省空间。UUID 适合需要客户端生成 ID 或者跨库合并的场景但要清楚它的索引体积和随机写入带来的页分裂问题高并发插入时影响不小。时间字段统一用timestamptz别用不带时区的timestamp。这个坑太经典了前期用timestamp存本地时间后面接海外业务或者跨时区报表数据全乱。timestamptz内部存 UTC展示时按会话时区转换语义清晰。CREATE TABLE orders ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, amount numeric(12,2) NOT NULL DEFAULT 0, status text NOT NULL DEFAULT created, payload jsonb NOT NULL DEFAULT {}::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() );金额用numeric不用float这是常识但每年都有人踩。状态字段用text加CHECK约束比枚举类型灵活改枚举值要ALTER TYPE加删除很麻烦text加约束改起来只是换个约束。JSON 字段用jsonb不用json前者支持索引且写入时会做规范化查询效率高很多。建表时还有个小技巧把created_at和updated_at的默认值都设上updated_at再配一个触发器自动更新省得每个 UPDATE 语句都手写一遍时间。触发器写法不复杂但对写入性能有轻微影响超高频写入的表要权衡一下。3.2 字段变更与索引维护的实际操作加字段是最常见的变更。PG 11 之后ADD COLUMN ... DEFAULT 常量是元数据操作不会重写整表秒级完成。但如果默认值是易变函数比如now()PG 会走重写路径大表上要小心。想让时间字段带默认值又不重写可以先加空列再设默认值再分批回填。ALTER TABLE orders ADD COLUMN channel text; ALTER TABLE orders ALTER COLUMN channel SET DEFAULT web; UPDATE orders SET channel web WHERE channel IS NULL; -- 分批执行改字段类型是最危险的操作之一会拿ACCESS EXCLUSIVE锁并重写表。千万级以上的表我的做法是新加一列、双写、回填、切换读、删旧列整个过程业务无感代价是代码要改两处。如果非要直接改至少确认几件事表的行数、磁盘剩余空间是否够两倍、是否有长事务在跑、变更窗口内能否接受短暂不可写。索引维护上CREATE INDEX CONCURRENTLY是必须记住的它不阻塞写入代价是执行慢、可能失败留下无效索引。失败的索引状态是invalid用pg_index能查出来处理方式是DROP INDEX CONCURRENTLY后重建。-- 查无效索引 SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name FROM pg_index WHERE NOT indisvalid; -- 并发建索引 CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC);复合索引的字段顺序有讲究等值条件放前面范围条件放后面排序字段跟着范围条件走。上面这个索引对WHERE user_id ? ORDER BY created_at DESC正好对WHERE created_at ?单独用就不行因为前导列没用上。这种最左前缀的规则和 B-tree 结构直接相关理解了就不用死记。4. 性能分析与慢查询排查 SQL4.1 EXPLAIN 执行计划的正确读法EXPLAIN是排查第一入口但很多人只看总耗时忽略了每行的actual time和rows。我读计划的顺序是先看最内层节点的rows估算和实际差多少差一个数量级说明统计信息不准再看有没有Seq Scan出现在大表上最后看loops是不是很高高 loops 意味着这个节点被重复执行了很多次常见于嵌套循环。EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 10;BUFFERS这个选项一定要加它能告诉你读了多少块、命中多少缓存。如果read远大于hit说明数据不在内存里慢是正常的加索引也未必有用可能要考虑预热或者调整缓存。VERBOSE会展开输出列排查投影开销时有用。几个典型信号出现Nested Loop且内层表很大通常是连接字段缺索引出现Sort且Sort Method: external merge Disk说明排序内存不够可以调work_mem或者优化排序字段出现Rows Removed by Filter很大说明索引选择性不好或者条件写法导致索引失效。还有一点EXPLAIN ANALYZE会真实执行写语句要包在事务里回滚BEGIN; EXPLAIN ANALYZE UPDATE orders SET status done WHERE id 1; ROLLBACK;不这么做测试环境的数据就被改掉了我见过不止一次。4.2 系统视图与锁等待排查慢查询定位主要靠pg_stat_statements扩展它按归一化后的 SQL 聚合统计能看到总耗时、调用次数、平均耗时、共享块读写。开启方式是在shared_preload_libraries里加上它然后重启之后CREATE EXTENSION pg_stat_statements。SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;我一般先按total_exec_time排序找总耗时大户再按mean_exec_time加calls 100找单次慢且频繁的语句。这两个维度结合基本能锁定优化目标。锁等待排查是线上事故的必修课。当前活动会话看pg_stat_activity关注state、wait_event_type、query_startSELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS duration, left(query, 120) AS query FROM pg_stat_activity WHERE state idle ORDER BY duration DESC;wait_event_type Lock基本就是锁等待再去pg_locks里找阻塞链。pg_blocking_pids(pid)这个函数很好用直接返回阻塞某个会话的进程号SELECT pid, pg_blocking_pids(pid) AS blockers, left(query, 100) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;找到源头后评估能不能pg_cancel_backend温和取消当前语句或者pg_terminate_backend断连接。处理前一定要看清楚那条 SQL 在干什么别把正在做大事务回滚的会话杀了可能引发更长恢复。5. 权限、备份与日常运维 SQL5.1 用户权限与 schema 管理PG 的权限模型是库、schema、表三级加上角色继承刚接触容易绕晕。我通常按一个应用一个角色来建角色只给必要的库和 schema 权限不给超级用户。CREATE ROLE app_user LOGIN PASSWORD change_me; GRANT CONNECT ON DATABASE appdb TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user; -- 让以后新建的表也自动带上权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;ALTER DEFAULT PRIVILEGES这条特别容易被漏掉漏了之后运维新建一张表应用就报权限不足还得手动补。注意它只对执行这条语句的角色之后创建的对象生效所以要在建表用的那个角色下执行。查权限用information_schema或者has_table_privilege函数SELECT has_table_privilege(app_user, public.orders, SELECT);排查为什么这个用户查不了时先确认它属于哪些角色再看这些角色在目标对象上有没有权限pg_roles和pg_auth_members一查就清楚。另外别忘了search_path同名表在不同 schema 下权限可能不一样SHOW search_path看一眼能省很多时间。5.2 数据导出、备份与恢复的常用手段逻辑备份用pg_dump整库、单表、单 schema 都支持。生产环境我一般用自定义格式-Fc压缩率高且恢复时能选择性还原pg_dump -h 127.0.0.1 -U app_user -d appdb -Fc -f appdb.dump pg_restore -h 127.0.0.1 -U app_user -d appdb_new -j 4 appdb.dump-j并行恢复能明显加快速度但只对-Fc或-Fd格式有效纯 SQL 文本没法并行。恢复前记得目标库要存在pg_restore不会自动建库。单表数据导出导入COPY比 INSERT 快得多COPY orders TO /tmp/orders.csv WITH (FORMAT csv, HEADER true); COPY orders FROM /tmp/orders.csv WITH (FORMAT csv, HEADER true);注意服务端的COPY要求文件在数据库服务器上且需要有相应文件权限客户端用\copy元命令则走本地文件。这个区别在容器环境里经常搞混报找不到文件时先想想文件到底在哪台机器上。备份策略上逻辑备份适合小库和跨版本迁移大库还是得靠物理备份加 WAL 归档做时间点恢复。不管用哪种恢复演练一定要定期做我见过备份脚本跑了两年从没验证过真出事时发现文件是空的。6. 常见问题与排查技巧实录6.1 高频报错速查表下面这张表是我这些年翻得最多的几个报错基本覆盖了日常八成问题。报错信息常见原因处理方向too many connections连接数超上限或连接泄漏查pg_stat_activity的 idle 连接检查连接池配置deadlock detected两个事务加锁顺序相反统一业务加锁顺序缩短事务could not serialize access可重复读或串行化隔离级别下冲突业务侧重试或降低隔离级别relation does not existsearch_path 不对或表在别的 schemaSHOW search_path确认 schemapermission denied for table角色缺权限或默认权限没设用has_table_privilege逐项确认invalid input syntax for type类型转换失败常见于空串转数字检查数据源用NULLIF或显式转换index row size exceeds maximum索引字段太长超出页大小换hash索引或缩短字段或改用表达式索引out of memory排序或哈希超出内存调work_mem或优化语句减少中间结果这张表建议直接存到自己的清单里遇到报错先对一遍比从头分析快得多。表里没有的再去查pg_stat_activity和日志。6.2 实操心得与几条避坑清单第一条心得是关于统计信息的。表数据变化超过一定比例后autovacuum才会触发ANALYZE中间这段时间执行计划可能很烂。大批量导入或者删数据后我会手动跑一次ANALYZE 表名几秒钟的事能避免很多昨天还快今天突然慢的困惑。查上次分析时间用pg_stat_user_tables的last_analyze字段。第二条是关于事务的。PG 里长事务的危害比很多人想象的大它会让VACUUM无法回收死元组表越来越大查询越来越慢形成恶性循环。查长事务SELECT pid, now() - xact_start AS xact_age, state, left(query, 100) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 10;xact_age超过几分钟就该关注了超过半小时基本可以判定有问题。常见的元凶是应用开启了事务但中间调了外部接口接口超时导致事务一直挂着。第三条是关于表膨胀的。pg_stat_user_tables里的n_dead_tup和n_live_tup能看出死元组比例比例高说明需要VACUUM但如果是VACUUM也压不下去的膨胀就得考虑VACUUM FULL或者在线重建。VACUUM FULL会拿排他锁并需要等量磁盘空间大表上一定要在维护窗口做能用在线工具就别硬来。第四条是关于 JSONB 查询的。jsonb字段上的-和-区别要分清前者返回 JSON 对象后者返回文本。建 GIN 索引后包含查询能走索引但-提取后再比较通常走不了得建表达式索引。这个细节在做配置类表查询时经常决定性能好坏。-- 能走 GIN 索引 SELECT * FROM orders WHERE payload {channel:web}; -- 走不了普通索引需要表达式索引 CREATE INDEX idx_orders_channel ON orders ((payload - channel));第五条算是个人习惯所有排查用的 SQL 我都放在一个固定文件里按会话、锁、空间、统计分块注释。真出事的时候人是慌的靠临场拼语句容易漏条件或者拼错视图名。提前准备好出事时复制粘贴改两个参数就能跑这个习惯帮我省下的时间比学任何高级技巧都多。