测验：深入 PostgreSQL：第 2 部分

你知道 CROSS JOIN 吧？

JOIN ALL 让你想到另一个操作 UNION ALL 了吗？

这里的答案是 JOIN ALL ❌。

正确的 JOIN 类型有：

• INNER JOIN（默认 JOIN）
• LEFT JOIN（或 LEFT OUTER JOIN）
• RIGHT JOIN（或 RIGHT OUTER JOIN）
• FULL JOIN（或 FULL OUTER JOIN）
• CROSS JOIN（笛卡尔积，除非后面的 WHERE 子句过滤）

虽然 SERIAL 仍被广泛使用，但 SQL 标准 的方式是：

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

• id INT IDENTITY(1,1) 是 SQL Server 语法。
• id INT AUTO_INCREMENT 是 MySQL 语法。
• id INTEGER AUTO_INCREMENT PRIMARY KEY 是 MySQL 语法。

相较于 SERIAL，它有以下几个优势：

• 符合 SQL 标准
• 更好地处理序列所有权
• 对手动插入的语义更清晰
• 在复制环境下表现更佳

SERIAL 实际上只是创建序列并设置默认值的简写，这可能在转储和恢复时导致问题。

PostgreSQL 对 ANSI SQL 的扩展示例：

• RETURNING 子句。
• 用于不区分大小写匹配的 ILIKE。
• 数组类型及其操作。
• JSON/JSONB 类型。

原始 ANSI SQL 标准包含：

• WITH（公共表表达式）。
• 用于模式匹配的 LIKE。
• LATERAL 连接。
• 窗口函数。

当此处出现错误时：

• 整个事务会被回滚
• 没有任何更改被提交
• 事务被标记为失败

若想部分提交：

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

哈希连接只能在 ON 子句中使用等号（=）条件。

• = 是哈希连接的关键谓词形态
• > 和 >= 不是哈希连接的谓词
• HASH JOIN 不是 PostgreSQL 的合法语法

根据表大小、统计信息、索引和内存设置，规划器仍可能选择嵌套循环或合并连接。

对我来说，令人惊讶的是即使查询文本中的列顺序与索引定义不匹配，多列索引仍然可以被使用。

注意： 对于 B‑tree 索引，首列非常重要。因为此查询按 grade_level 和 last_name 过滤，首列为 grade_level、随后为 last_name 的索引最合适。

SQL 能选取的行越少，性能就越好。

例如，如果你有一个 task 表，并且知道 status 的选择性高于 created_at，可以为类似 status = 'done' AND created_at > '2024-01-01' 的查询创建以 status 为首列的索引。真正决定性能的不是 WHERE 子句中文本的条件顺序，而是索引列的顺序以及谓词的形态。

B+Tree 索引可用于：

• 首列或最左前缀列。
• =, >, <, BETWEEN, IN 等操作符，具体取决于查询形态。
• 前缀匹配，如 LIKE 'prefix%'。

感谢 u/mwdb2 指出此解释中早期的错误！

在 PostgreSQL 中：

• 双引号 (") 用于标识符（表名、列名）
• 单引号 (') 用于字符串字面量

最佳实践：

• 避免在名称中使用混合大小写或空格
• 使用 snake_case 命名标识符
• 仅在绝对必要时才使用带引号的标识符

PostgreSQL 使用双引号来引用标识符：

• 双引号 "..." 用于标识符（列名、表名）
• 单引号 '...' 用于字符串字面量
• 形式 first.name 会被解释为 table_name.column_name

如果不加引号，列名中的句点会被当作模式/表分隔符！此外，所有未加引号的标识符默认会被转为小写。

为了减少意外，最好使用 snake_case 并避免使用特殊字符。

这道题很刁钻！大约有 6 个陷阱，例如：

1. 高层描述 + 语法碎片，考察你如何拼凑不完整的信息。
2. 需要熟悉 TABLESAMPLE 语法，因为没有一个 TABLESAMPLE 选项使用了正确的写法！（阴险！我知道！继续，加油，我相信你!!! ❤️）
3. ROW_NUMBER() OVER (ORDER BY RAND()) 和 ORDER BY RANDOM() > 10 只是红鲱鱼。它们看起来像是可行的，但其实不对。RAND() 并不存在，且 RANDOM() > 10 永远为假，因为 RANDOM() 的取值范围是 0.0-1.0。 另外，ORDER BY RANDOM() > 10 甚至会在布尔值上排序，而不是在随机数上。> 10 看起来像是百分比，但其实不是。
4. BERNOULLI 是合法的，但 SAMPLETABLE 不是。
5. WHERE RANDOM() >= 0.1 是个陷阱！RANDOM() 返回 0.0 <= x < 1.0，所以 >= 0.1 会返回约 90% 的行，而不是 10%。

PostgreSQL 支持两种抽样方法：

• BERNOULLI：每行等概率抽取
• SYSTEM：块级抽样（更快但随机性稍差）

想了解更多抽样技巧，请阅读来自 Render 的文章。

示例：

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);

-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

还有一点值得一提，TABLESAMPLE 已被 报告 说随机性不佳。如果需要更随机的固定大小抽样，可以考虑使用 ORDER BY RANDOM() LIMIT 10。但要注意，这会返回 10 行，而不是 10%，且在大表上可能很慢。

市面上也有一些第三方扩展提供更好的随机抽样方法，但超出本题范围 😅

这个技巧叫做 Partial Index。

当通常只查询一部分行时，它们有助于减少磁盘使用并加速查询。

当查询中出现 status = 'active' 条件时，Postgres 才会使用该索引。

提醒: Postgres 不支持像其他 RDBMS 那样的 “query hints”。查询规划器会自行决定何时使用合适的索引。

好，这有点儿棘手。关键是 x = NULL 永远不会匹配任何行，它总是返回一行，计数为 0。

因为 NULL 是未知值，不能与任何东西比较，甚至不能与它自身比较。

检查 NULL 值的正确方式是使用 IS NULL。

示例：

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)

postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

EXPLAIN ANALYZE 实际上会执行查询！在分析写操作时要小心，尤其是 UPDATE、DELETE 和 INSERT 语句。每次运行 EXPLAIN ANALYZE 后可能需要执行清理操作，以获得可重复的结果。

最佳实践：

• 对 UPDATE/DELETE/INSERT 使用不带 ANALYZE 的 EXPLAIN
• 将修改包装在事务中：

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;