Dan Levy

测验：深入 PostgreSQL：第 2 部分

你是SQL硬核还是SQL软妹？

第2部分，共2部分。 返回第1部分

欢迎来到我的深度 PostgreSQL 测验第 2 部分！

本测验涵盖了常见与鲜为人知的 PostgreSQL 特性和坑点：JOIN、NULL、ANSI SQL、事务、内部机制、索引、数据抽样……以及更多！

祝你好运！ 🍀

1. 

热身：JOIN

哪个不是 ❌ 有效的 PostgreSQL JOIN 表达式？

你知道 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 子句过滤）

2. 

标识列

PostgreSQL (v10+) 中用于自增 IDENTITY 列的（SQL 标准）语法是什么？

虽然 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 实际上只是创建序列并设置默认值的简写，这可能在转储和恢复时导致问题。

3. 

ANSI SQL

PostgreSQL 文档将以下哪个子句列为对标准 SQL 的扩展？

PostgreSQL 文档将 RETURNING 子句列为对标准 SQL 的扩展。ILIKE 也是 PostgreSQL 的扩展。

不应把所有数组或 JSON 支持都归为非标准功能：SQL 在这些领域已有标准化能力，同时 PostgreSQL 也提供 jsonb 等自己的类型和运算符。

不同版本的 SQL 标准逐步引入了以下功能：

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

4. 

事务

除“取决于副作用”之外， 错误发生后会怎样？

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

当此处出现错误时：

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

若想部分提交：

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

5. 

SQL 内部原理

哪个 ON 条件可以让 PostgreSQL 使用 HASH JOIN？

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

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

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

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

6. 

索引

哪个索引更适合此查询？

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

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

注意： 对于 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 指出此解释中早期的错误！

7. 

引用 1

对于区分大小写的列名，哪种写法是正确的？

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

在 PostgreSQL 中：

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

最佳实践：

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

8. 

引用 2

如何指定包含句点的列名？

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

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

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

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

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

9. 

数据抽样

哪种语法可以**随机抽样 10%**的学生？

这道题很刁钻！大约有 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%，且在大表上可能很慢。

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

10. 

索引

这段咒语做什么？

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

这个技巧叫做 Partial Index。

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

只有当规划器能证明查询条件蕴含 status = 'active' 时，才会考虑这个索引。并不总是要求文本完全相同，但规划器的逻辑推导能力有限；参数化条件可能无法在规划时证明满足谓词。符合使用条件，也不保证最终选择该索引。

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

11. 

NULL 行为

这将返回多少行？

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

好，这有点儿棘手。关键是 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)

12. 

性能

EXPLAIN ANALYZE 对 UPDATE 语句有什么作用？

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

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

最佳实践：

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

做得好！你深入探讨了 PostgreSQL 的多个方面！🐘

希望你学到了新东西，或者至少拿到了可以炫耀的分数！🏆

想要生活更刺激？看看我的 测验合集吧，乐趣无穷*！

Loading comments...