SQL

EXPLAIN 打印规划器准备采用的执行计划与代价；EXPLAIN ANALYZE 则真的跑一遍语句，把估算变成测到的行数与时间。它们回答的是两个不同问题：规划器认为应该怎样做，以及数据库实际怎样做。排障时先看前者理解意图，再用后者测量现实，别把 cost 当成秒数，也别在生产库上无准备地执行一条会写数据的 ANALYZE。 计划提供需要核对的估计与观测，而不是单凭行数差异就能完成的诊断。循环次数和提前停止也会制造表面差距；估计准确时，查询仍可能受到锁等待、溢写或大量必要工作的影响。它告诉你读了哪些表、用了什么索引、预计经过多少行，以及每个节点把多少工作交给下一个节点。把计划保存进工单，和请求参数、统计信息更新时间、数据库版本一起记录，几周后的回归才有可比基线。只贴一句“慢”没有证据；保存计划能帮助定位值得检验的假设，但不能自动解释所有请求延迟。 自上而下读形状 文本计划先打印父节点，再缩进打印它的子节点。向下读可以理解节点依赖哪些输入，向上看可以理解结果怎样汇合。执行可能按需发生：上层的 LIMIT 得到足够多的行便会停止索取，下层排序却可能需要读完全部输入才能交出第一行。缩进描述依赖关系，并不是所有节点通用的执行时间顺序。 cost 使用规划器的工作量单位，不是毫秒。父节点总成本包含子节点，不能把整棵树的数字直接求和。启动成本表示返回第一行前预计投入多少工作，总成本表示完成节点的估算。实际时间和实际行数在 loops 大于一时按每次执行取平均；判断重复工作要结合循环次数，同时避免把父节点包含的子节点时间再加一次。 阅读时先问三个问题。第一，计划是否走了预期的访问路径，例如选择性很高的条件是否用了索引。第二，估算行数和实际行数是否在同一个数量级，数量级错了通常比单个节点的 cost 更值得处理。第三，数据流在哪里膨胀或等待，宽大的中间结果会把后面的排序、连接和聚合一起拖慢。沿着这三个问题读，计划就从一堆术语变成了可验证的因果链。 追踪工作量与放大点 行数最多只是线索，不能直接证明瓶颈。连续读取的大表扫描可能比随机访问索引更便宜，一个每次很快却反复执行数万次的节点也可能主导延迟。先找估算与实际第一次发生明显偏离的地方，再追踪它怎样影响后续连接、排序与内存。嵌套循环并非天然错误：外表很小、内表有匹配索引时它可能最快；危险的是外表被严重低估后仍反复访问内表。 把计划格式化为 JSON 可以让工具比较节点，手工排障先用普通 EXPLAIN 确认访问路径，再按需执行带 ANALYZE、BUFFERS 的测量。缓冲命中、缓冲读取、临时文件读写能帮助定位数据访问与排序落盘。缓冲读取不等于真实磁盘读取，因为操作系统缓存也可能提供数据；看到 read 就宣布磁盘慢，证据还不够。VERBOSE 会显示更多输出列和别名，适合定位复杂视图。 用 ANALYZE 让行数和代价对齐 EXPLAIN ANALYZE 会真实执行查询，同时给出估算与实际行数、节点耗时和循环次数。两者出入很大，可能来自统计过时、分布倾斜或列间相关性；重新收集统计有时能改善，但不能保证修好所有偏差。写入实验放到隔离数据库，并设置语句超时。事务回滚只能撤销可回滚的数据库改动，不能撤销已经分配的序列值、函数或触发器对外部系统造成的不可回滚副作用。SELECT 也可能调用带副作用的函数，不能只根据语句开头判断安全。 可以按需提高相关列的统计目标，或者建立描述同一张表中相关列的扩展统计，但这些动作也要由测量验证。扩展统计不能修复所有估计，目前也不是跨表连接选择性的通用信息来源；应先确认它改变了真正有问题的估计，再归因于统计改进。索引收益仍要根据整个查询的工作量评估。若同一语句对小租户很快、对大租户很慢，要用应用实际的预备语句路径比较通用计划和定制计划。PostgreSQL 核心并没有通用的 SQL 提示语法，不要凭空加入提示来强行选择节点。每次只改变一个变量，把结果集、计划和延迟并排保存。 ...

参数化查询通过不同的接口参数传递数据库指令与实际取值。数据库把指令解释为 SQL，再把各个参数用于需要值的位置。这种边界既适用于 SQLite 这样的嵌入式引擎，也适用于客户端连接服务端的数据库；它不依赖一次网络交互，更不要求应用显式管理命名的服务端语句。重点在真实调用如何传值，而不是有没有某个名为“预编译”的包装函数。 坏掉的样子 字符串拼接会拆掉这条边界。姓名中的一个引号可能提前结束原本的字面量，后面的文本就有机会变成条件或注释。仅仅禁用分号无法修复问题，因为攻击者可以在不追加第二条语句的情况下，改变同一条查询的含义。同样，一个接口即使拒绝一次执行多条语句，也仍然需要正确绑定参数，不能把这个限制当作注入防护已经完成的证据。 绑定可以防止值变成 SQL 语法，但所选运算符仍会按自身规则解释这个值。对于 LIKE，百分号匹配一段字符，下划线匹配一个字符。因此，一个经过绑定的搜索串，仍可能比用户期望的普通文本搜索匹配更多记录。这属于模式语义的问题，不说明绑定失效，却说明“输入不会执行成 SQL”还不等于“搜索符合产品要求”。 另外一些错误也不会造成注入，却会给出错误结果。把逗号分隔的文本交给一个 IN 占位符，它仍是一个标量，不会自动展开成列表。把列名当作值绑定，得到的仍是文本，不是列引用。因此，类型检查、允许的查询结构与业务权限，都需要在绑定之外独立成立。 快速验证 直接运行下面的完整 Python 脚本，只需要标准库 sqlite3。它创建内存数据库作为样本，完成断言后关闭连接。记录中的运行环境为 Python 3.12.14 与 SQLite 3.53.1。故意保留的脆弱查询仅用于比较三个虚构用户的结果，不连接任何真实业务数据： import sqlite3 with sqlite3.connect(':memory:') as db: db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)') db.executemany('INSERT INTO users VALUES (?, ?)', [(1, 'Alice'), (2, 'A_1'), (3, 'Ab1')]) payload = "' OR 1=1 --" # Deliberately vulnerable comparison, only against this in-memory fixture. unsafe = "SELECT id FROM users WHERE name='" + payload + "' ORDER BY id" assert sorted(db.execute(unsafe).fetchall()) == [(1,), (2,), (3,)] assert db.execute('SELECT id FROM users WHERE name=?', (payload,)).fetchall() == [] print('concatenated: 3 rows; bound: 0 rows') for value in ("O'Reilly", r'a\b', 'x; DROP TABLE users; --'): assert db.execute('SELECT ?', (value,)).fetchone() == (value,) assert db.execute('SELECT count(*) FROM users').fetchone() == (3,) print('quote, backslash, semicolon: literal round trips') assert db.execute('SELECT id FROM users WHERE name LIKE ? ORDER BY id', ('A_1',)).fetchall() == [(2,), (3,)] literal = 'A_1' escaped = literal.replace('!', '!!').replace('%', '!%').replace('_', '!_') assert db.execute("SELECT id FROM users WHERE name LIKE ? ESCAPE '!' ORDER BY id", (escaped,)).fetchall() == [(2,)] assert db.execute('SELECT id FROM users WHERE name=?', (literal,)).fetchall() == [(2,)] print('bound LIKE: 2 rows; escaped LIKE and equality: 1 row') assert db.execute('SELECT id FROM users WHERE id IN (?)', ('1,3',)).fetchall() == [] assert db.execute('SELECT id FROM users WHERE id IN (?,?) ORDER BY id', (1, 3)).fetchall() == [(1,), (3,)] assert db.execute('SELECT ? FROM users LIMIT 1', ('name',)).fetchone() == ('name',) assert db.execute('SELECT id FROM users ORDER BY id LIMIT ? OFFSET ?', (1, 1)).fetchall() == [(2,)] assert db.execute('SELECT id FROM users ORDER BY id LIMIT ?', (-1,)).fetchall() == [(1,), (2,), (3,)] assert db.execute('SELECT id FROM users ORDER BY id LIMIT ?', (0,)).fetchall() == [] print('IN needs separate values; bound column is a literal; LIMIT/OFFSET bind') print('negative LIMIT removes the cap; zero LIMIT returns no rows') db.close() 拼接查询返回三个编号，绑定查询面对相同输入则没有任何匹配。恶意输入中的注释还会删掉原先追加的排序部分，因此断言先对脆弱查询的结果排序，再比较编号集合；实验没有依赖未声明顺序的查询恰巧按插入顺序返回。这也展示了注入会改变原指令的多个部分，不只是多匹配一行那么简单。 ...

INSERT … ON CONFLICT 让 PostgreSQL 在真正写入的语句内部处理插入冲突。它用明确的冲突策略和实际索引或约束，替代脆弱的“先查键是否存在，再决定插入”流程。但它不会把所有插入错误都变成更新成功，也不会替应用决定新旧数据应该如何合并。使用前，需要先确定唯一键和业务动作。 冲突目标定义处理范围 (name) 这样的目标，要求 PostgreSQL 推断匹配的唯一索引。表上必须实际存在适用的唯一性规则；写出 ON CONFLICT 并不会自动建立约束。如果键涉及表达式或部分索引，索引表达式和谓词也会影响匹配。目标没有匹配到索引时会报错，而不是悄悄改用另一种插入策略。可空业务键尤其需要注意：普通唯一索引把空值视为彼此不同，多次写入空键不一定冲突。若业务要求这些行相互冲突，应选择非空键，或明确采用符合需求的 NULLS NOT DISTINCT 语义。 DO UPDATE 必须指定目标，其裁决依据只能是受支持的非延迟唯一约束或唯一索引，排除约束不能作为 DO UPDATE 的冲突裁决依据。DO NOTHING 的处理范围更广，包括受支持的排除约束，也可以省略目标来处理适用的冲突规则。但这些能力都不会让外键、非空或检查约束失效，不能把它当成通用的错误忽略机制。 原子的插入或更新行为，解决的是并发争用同一键时的协调。语句仍可能等待其他事务持有的相关行或索引冲突，正常的事务错误也仍可能发生。原子性描述结果如何被协调，不保证完全无锁、不阻塞，也不意味着调用方永远不用处理事务级重试。 明确写出合并规则 目标表名或别名代表已有行，EXCLUDED 代表原本准备插入的行，其中也反映 BEFORE INSERT 触发器的相关影响。赋值为 EXCLUDED.total 是替换旧值；赋值为已有 total 加 EXCLUDED.total 是累加一次贡献。虽然语法相近，两者回答的业务问题不同，不能把其中一种默认当成“正确的 upsert”。 计数器直接在更新表达式中使用已有行，可以避免客户端先读旧值，再算一个可能过时的替换值。带版本的文档则可以用 WHERE 拒绝较旧版本。这个条件在找到并锁定冲突行后求值，因此，即使最终没有更新，它也可能与其他写入者争用锁。条件跳过不等于完全没有执行成本或并发影响。 ...

窗口分区决定哪些行属于同一组，窗口帧决定其中哪些行参与当前行的某个帧敏感计算。把两种边界混淆，可能让逐笔累计变成重复显示的小计，也可能让 last_value 返回当前值，而报表原本需要的是分区最后一行的值。应先说明计算要回答什么，再选择排序和帧范围。 默认范围取决于是否排序 PostgreSQL 的默认帧是 RANGE：从分区开头到当前行的最后一个并列行。窗口 ORDER BY 中全部表达式都相等的行，属于同一个并列组。因此，有排序时的默认累计和，会包含当前行的全部同值伙伴，即使其中某些行在最终展示结果里排在后面，也已经进入当前帧。 如果窗口没有 ORDER BY，所有行都是并列行，默认帧就覆盖整个分区。因此，sum(amount) OVER (PARTITION BY account) 会重复显示账户总额；添加排序后，计算就变成有顺序的累计结果。“默认总是整个组”和“默认每次恰好多加一行”都不能同时描述这两种情况，不能把其中一种当成通用规则。 窗口顺序与最终输出顺序是分开的。外层 ORDER BY 可以改变展示排列，而不改变帧。反过来，在 RANGE 窗口内部加入唯一标识，可能把原来的并列组拆开，改变计算结果；这不只是让页面看起来稳定，而是修改了语义，应当按需求审阅。 按行、按值域和按并列组 ROWS 计算窗口排序中的位置。从前两行到当前行，最多包含三行；靠近分区起点时还会更少。它不表示三天或三分钟。如果多个事件时间戳相同，而业务需要确定的逐事件顺序，就应加入合适的唯一标识，明确同一时刻的事件谁先谁后。 RANGE 使用排序值和并列边界。指定偏移量时，PostgreSQL 要求只有一个排序表达式，并使用兼容的偏移类型，例如时间戳可以配合 interval。基于时间值域的帧按时间差纳入事件，忙时可能包含很多行，闲时可能很少。GROUPS 则按并列组计数，让帧沿不同排序值对应的组移动，同时保留组内全部并列行。这三者表达的是不同的计量单位。 first_value、last_value、nth_value，以及作为窗口函数使用的聚合，都依赖帧范围。排名和 lag、lead 的定义则基于分区顺序，并非相同规则；缩小帧不会让 lag 忘记分区中的前一行。解释某个窗口函数前，应查看该函数的语义，不能把“只看到帧内的行”套用到所有函数上。 帧成员范围不会覆盖函数本身的空值规则。PostgreSQL 18 的 last_value 及相关取值函数采用 RESPECT NULLS，末行为空时结果也为空；sum 等聚合通常忽略空输入。应先确定报表要末行取值、最后一个非空值，还是数值最大值。这些需求不能仅靠把帧延长到分区末尾就互相替代，改窗口之前要把空值案例写入验收条件。 ...

GROUPING SETS 让一条 SQL 同时生成多种聚合层级。销售报表可以一起返回地区与商品明细汇总、地区小计和总计。结果中混合了不同种类的行，因此，识别每行属于哪个层级，与算对金额同样重要；如果下游把所有结果行都当成普通明细，即使数据库计算正确，报表仍可能出错。 声明报表需要的层级 GROUPING SETS ((region,product),(region),()) 声明三种分组。第一种为每个实际出现的地区与商品组合输出一行，第二种为每个地区输出一行，空集合则汇总全部选中的输入行。这是在声明结果语义，具体执行方式由优化器选择。它可以在不同层级共享工作，但语法本身不保证固定的物理扫描次数，也不会消除排序和内存成本。 ROLLUP(region,product) 是相同前缀集合的简写，并包含空集合。交换两列顺序，会改变小计维度。CUBE(region,product) 还包括只按商品汇总的层级，覆盖维度的全部子集。六个独立维度对应六十四种分组集合，不是只输出六十四行；每种集合还可能包含很多不同分组，所以结果规模可能远大于层级数。 所有层级都汇总 FROM 和 WHERE 选出的总体。如果过滤条件只保留某个月的销售，总计也只代表该月。如果不同层级需要不同输入总体，直接对一个共享输入声明这些集合，可能并不符合需求。比较另一张报表的总额之前，要先确认日期、业务状态等筛选范围一致，不能只对照列名。 用 GROUPING 标记层级 某个分组集合没有使用的维度列，会在输出中填入 SQL NULL。但原始数据本来就可能含有 NULL，因此仅靠哪些列为空，无法可靠判断这是小计还是普通分组。GROUPING(region,product) 返回位掩码，说明当前集合缺少了哪些分组表达式；最右边的参数对应最低有效位。 在这里，零表示两个维度都参与分组，一表示省略商品，三表示两个维度都省略。真实空地区的小计与全表总计，可能同时显示空地区、空商品，但掩码不同。应把这个标记纳入输出契约，只有层级明确表示跨地区汇总时，才生成“所有地区”等展示标签。 表达式是否参与分组，与表达式的值是否非空，是不同问题。GROUPING 报告前者，其参数必须匹配查询中的分组表达式，并不是测试任意值是否为空的普通函数。向下游提供数值层级或其他无歧义标识，比把每个空值都替换为“小计”更可靠，也能保留真实缺失维度的含义。 坏掉的样子 把结果中所有 total 再相加，会让同一笔销售在每个层级重复计入。需要进一步汇总时，应先选择一个层级，或直接取已经算好的总计。平均值和去重计数更不能随意相加：没有按样本数加权的地区平均值，通常不能直接平均成整体平均值；重叠集合的去重数量也不能简单求和。聚合层级不是彼此独立的新增交易。 空分组集合在没有输入行时仍形成一个分组，但后续 HAVING 条件可以把这行过滤掉；没有这类过滤时，其中 count 为零，sum 为 NULL。因此，金额为空不等于查询没有返回总计行。应明确报表如何展示空总体，而不是把空值自动当作执行失败。还要避免无意重复声明分组集合；重复层级可能产生重复聚合行，并不代表多了一份独立证据。 快速验证 把下面内容保存为 grouping.sql，在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f grouping.sql。数据特意包含一个真实空地区，脚本用事务包住临时对象，末尾回滚清理。 ...

给 PostgreSQL 现有表添加外键，既是 schema 变更，也是对历史数据作出声明。新写入可以立即受到检查，而旧行仍可能保留失效引用。规划迁移或排查完整性错误时，应分别考虑约束是否执行、历史数据是否验证，以及检查发生在什么时候，不能把这三个状态混成“外键已经存在”。 执行约束不等于验证历史数据 普通启用的外键要求适用的非空引用能匹配父键。被引用列需要符合条件的唯一性，例如适当的非延迟主键、唯一约束或非部分唯一索引。子列如果没有另外声明 NOT NULL，仍然允许空值；外键本身不会把所有关系变成必填。复合引用默认使用 MATCH SIMPLE，只要一个引用列为空就不要求匹配；MATCH FULL 允许全部为空，却拒绝部分为空、部分非空的组合。 ADD CONSTRAINT … NOT VALID 跳过对现有行的初次扫描，但仍会检查新建或改变的引用，因此可以在调查旧数据时阻止新增孤儿引用。NOT VALID 并不表示规则被禁用。与此同时，数据库还不能假定所有历史行都满足该约束，迁移也不能仅凭添加语句成功就宣告数据已经完整。 VALIDATE CONSTRAINT 扫描现有数据，检查成功后才把约束标为已验证。如果仍有历史孤儿行，验证会失败，约束继续处于未验证状态。应依据权威业务关系修复数据，再次验证。为了让检查变绿而编造父记录或删除子记录，即使得到结构一致的数据库，也可能破坏真实业务事实；修复依据不能只来自外键错误本身。 把部署阶段分开处理 添加外键即使使用 NOT VALID，也会锁定引用表和被引用表，不能把它描述成完全无锁的部署方式。其价值是在添加阶段持有锁时，避免执行可能很长的历史数据扫描。VALIDATE 使用允许普通数据修改的较弱锁，但仍消耗资源，并与其他 schema 操作进行协调，所以也需要观察执行耗时和实际争用。 如果迁移目标是缩短第一个锁窗口，应先提交短暂的添加阶段，再把验证作为后续操作执行。把两步包在同一个长事务中，会让早先取得的锁一直保留到事务结束，可能抵消分阶段的收益。下面示例故意把步骤放在一个事务里，只是为了最后回滚清理整个测试环境，并不是生产锁窗口的部署模板。 PostgreSQL 不会自动给子表引用列建立索引。父键的唯一性已有索引，但更新或删除父行时，仍可能需要查找子表引用。应先检查子表已有索引，再在实际负载需要时补上适合的访问路径。声明了外键，不代表优化器一定消除关联，也不保证大范围级联删除会很快。 什么时候延迟检查 DEFERRABLE 外键可以把检查推迟到事务结束。INITIALLY DEFERRED 表示初始就采用延迟模式；SET CONSTRAINTS 可以把适用的约束切回立即检查，并检查此前尚未确认的工作。这使同一事务中先插子行、再补父行的顺序成为可能，适合需要在最终提交点保证完整性的批量操作。 延迟并不允许提交未解决的失效引用，它改变的是检查边界，而不是最终不变量。NO ACTION 可以配合延迟检查，RESTRICT 的删除限制不能延迟。级联动作也不会仅因为约束可延迟就一并推后。应一起选择动作与检查模式，不能把 DEFERRABLE 理解为临时忽略完整性而无需满足结束条件的许可。 ...

窗口函数让报表保留每条读数，同时附加相关行的信息。一条传感器读数可以展示前值、变化量和在该传感器总量中的占比，而不必把整个传感器压缩成一行结果。能否正确解释这些附加列，取决于计算前是否明确了输入总体、分区和顺序，不能只看函数名称就认定答案符合业务。 给相邻关系明确的含义 PARTITION BY 用来划分相互独立的序列。按 sensor 分区的 lag，只比较同一传感器的值；漏掉分区条件，可能把某个传感器的第一条读数与另一个传感器的最后一条相比较。OVER 内部的 ORDER BY 决定谁是前一行。可以使用稳定的序列键；时间戳可能重复时，则应使用时间戳加唯一标识，明确同一时刻的先后关系。 lag 找的是这个有序总体中的前一行，不一定是前一分钟、前一天或上一个计划采集时刻。采集有缺口时，数据库不会自动补出缺失行。如果计算要求与前一个自然日比较，应明确建立日历关系，或先构造所需的时间序列，再做窗口计算。表里相邻的两行，并不能证明采样间隔固定。 在分区开头，lag 返回默认值，通常是 SQL NULL，用来表示没有前驱行。把它改成零，会改变首条变化量的含义，应有业务规则支持。PostgreSQL 的 lag、lead 也会保留空值语义：如果前一条读数本身为空，它不会自动越过这条记录，继续向前寻找更早的非空值。不存在前驱与前驱值为空，需要在业务解释中保持区别。 在明细旁保留总额与占比 聚合函数配合 OVER 使用时，会保留输入行，并为每行增加计算结果。sum(value) OVER (PARTITION BY sensor) 没有窗口排序，因此重复显示整个传感器分区的总额。如果未考虑默认帧就加上排序，总额可能变成累计额，随后每一行的占比分母都会改变，这不是无关紧要的展示调整。 需要小数占比时，应在除法之前转换为 numeric。整数相除可能截断小数，让较小贡献看起来全部为零。示例使用 NULLIF 把零分母转换成 SQL NULL，使无法定义的占比保持未定义状态。这个处理是一条明确规则；改为零、拒绝数据集或展示另一个状态，都是不同的业务选择，不能让算式偶然决定。 并非每个窗口函数都使用移动帧。聚合和 last_value 等函数依赖帧，而 lag、lead 在分区行之间移动。仅仅缩小 ROWS 范围，并不会重新定义 lag 所指向的前一条分区记录。应分别为每个问题选择函数和窗口，不能把一条帧公式机械地套在所有计算上。 ...