SQL databases
关系型数据库在系统设计里真正替你做的三件事：用约束守住数据、用 join 和 materialized view 回答没预先设计过的查询、用连接和副本扛住读。讲清外键索引在 PostgreSQL 与 InnoDB 的差别、REFRESH MATERIALIZED VIEW CONCURRENTLY 的前提、N+1 查询的算例和四种修法、max_connections 与连接池，以及面试答法。
这一章回答三个问题：关系型数据库替你守住了哪些规则、一个没提前设计过的查询它怎么回答、读压力上来时先扩什么。 什么时候该选 SQL 而不是 NoSQL，见 SQL 还是 NoSQL；索引结构和 EXPLAIN 见 Indexes；隔离级别见 Transactions；慢查询的排查流程见 SQL 调优。
SQL（关系型）database 是一组有预定义关系的数据集合，通常以 tables 的形式组织（columns + rows）。Table 存放实体信息；column 表示某类数据；row 表示某个对象或实体的一组相关值。
每一行通常有唯一标识（primary key），多表之间通过 foreign keys 建立关系。数据可以用多种方式访问，不需要重组 tables。SQL databases 通常提供 ACID 事务。
有约束的设计问题
一个 B2B 订货平台用 PostgreSQL：
customers5 万行，orders3,000 万行，order_items1.2 亿行。- 订单列表页每页 50 单，要显示客户名和商品数。
- 运营后台每天看「按客户、按月的销售额」，查询要扫近一年的数据。
- 应用有 40 个实例，每个实例的连接池默认开 20 个连接。
规模数字是本章的假设。下面四节分别对应：数据完整性、列表页的 N+1、报表的 materialized view、连接数。
1. 约束：让数据库拒绝坏数据
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
order_no text NOT NULL UNIQUE,
status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
total_cents bigint NOT NULL CHECK (total_cents >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (customer_id); -- PostgreSQL 不会替你建
REFERENCES保证不会出现指向不存在客户的订单；UNIQUE (order_no)挡住重复提交；CHECK挡住负金额和拼错的状态。应用有几个版本同时在线时，这些规则仍然只有一份。- 外键列的索引，两个数据库行为不同。 PostgreSQL 文档写明声明外键不会自动在引用列上建索引，而删除或更新被引用的行时要扫描引用表，所以通常要手动建。InnoDB 则要求外键列有索引，没有就自动建一个。从 MySQL 迁到 PostgreSQL 忘了这一步，删一个客户就会全表扫 3,000 万行订单。
2. N+1：列表页多发了 50 条 SQL
N+1 query problem 指 data access layer 额外执行 N 条 SQL 查询，去获取本该在主查询里一次拿到的数据。这在 GraphQL 与 ORM 中很常见：
orders = Order.objects.order_by('-created_at')[:50] # 1 条
for o in orders:
print(o.customer.name) # 每单再查 1 条，共 50 条
算一遍。 假设应用和数据库在同一可用区，一次往返约 0.5 ms，每条主键查询本身不到 0.1 ms（都是假设值）。51 条查询约 51 × 0.6 ≈ 30 ms，而一条 join 约 1 ms。如果数据库在另一个区域、往返 50 ms，同一页就要 2.5 秒以上。N+1 的代价主要是往返次数，不是 SQL 本身慢，所以在开发机上几乎看不出来。
四种修法：
| 修法 | 写法 | 查询条数 | 适合 |
|---|---|---|---|
| JOIN | SELECT o.*, c.name FROM orders o JOIN customers c ON c.id = o.customer_id ... | 1 | 一对一、多对一 |
| 批量 IN | 先取 50 单，再 SELECT * FROM customers WHERE id = ANY($1) | 2 | 一对多，避免 join 让父行重复 |
| ORM 预加载 | Django select_related（生成 join）/ prefetch_related（文档写明对每个关系单独查一次，在 Python 里拼接） | 1 或 2 | 用 ORM 的项目 |
| DataLoader | GraphQL resolver 里 loader.load(id)；文档写明同一个事件循环 tick 内的所有 load 会合并成一次批量调用 | 每层 1 | GraphQL，resolver 各自取数 |
order_items 的商品数不要在应用里一单一单地数，用 GROUP BY order_id 一次聚合，或在订单表上维护一个 item_count 冗余列（这属于 denormalization，写入时要一起更新）。
3. Materialized view：把重复的重查询算好存起来
Materialized view 是根据 query 预计算并存储的结果集。因为数据预先计算好，查询 materialized view 比直接查询 base table 更快，尤其在 query 频繁或复杂时差异很大。
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT customer_id, date_trunc('month', created_at) AS month,
sum(total_cents) AS revenue_cents, count(*) AS orders
FROM orders WHERE status = 'paid'
GROUP BY 1, 2;
CREATE UNIQUE INDEX ON monthly_sales (customer_id, month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales; -- 定时任务执行
PostgreSQL 文档里有几条必须知道的规则：
REFRESH MATERIALIZED VIEW是整体替换内容，不会自动增量更新，也不会在 base table 变化时自动刷新，刷新频率由你的定时任务决定。- 不加
CONCURRENTLY时，影响很多行的刷新更省资源、更快，但会挡住读这个视图的查询。 - 加
CONCURRENTLY不挡读，但要求视图上至少有一个只用列名、覆盖所有行的UNIQUE索引（不能是表达式索引，不能带WHERE），视图必须已经填充过数据；同一个视图同一时刻只能有一个 REFRESH 在跑。
MySQL 的 CREATE VIEW 只有普通视图，没有 materialized 选项；同样的需求通常用一张汇总表加定时任务维护。无论哪种，报表看到的都是上次刷新时的数据，页面上要标「数据截至几点」。
4. 连接和读扩展
PostgreSQL 的 max_connections 文档写的默认值通常是 100。开头的场景里 40 个实例 × 20 个连接 = 800 个连接，默认配置下大部分会连不上；而把 max_connections 调到 800 也不是答案，因为每个连接在服务器上都占资源，且 work_mem 这类内存按每个排序或哈希操作计算，连接越多，峰值内存越难估计。常见做法：
- 应用侧连接池调小，只保留真正并发执行 SQL 所需的数量。
- 中间加一层连接池代理（如 PgBouncer 的 transaction pooling），让几百个客户端连接复用几十个服务器连接。
- 读多写少时加只读副本，把报表和列表查询导过去；「刚下的单要立刻看到」的请求仍走 primary，细节见 Database Replication。
- 单个 primary 写不下，再考虑 Federation（按业务拆库）或 Sharding（按 key 拆同一张表）。
常见翻车
| 翻车 | 用户看到什么 | 修法 |
|---|---|---|
| PostgreSQL 外键列没建索引 | 删除一个客户，请求卡几十秒并持有锁 | 在引用列上建索引 |
| 列表页 N+1 | 本地很快，上线后跨区域部署时列表页 2 秒+ | JOIN、批量 IN、ORM 预加载或 DataLoader；在测试里断言查询条数 |
不加 CONCURRENTLY 刷新大视图 | 报表页在刷新期间全部卡住 | 建唯一索引后用 CONCURRENTLY，或刷新到新视图再切换 |
| 刷新间隔比报表需求长 | 运营看到昨天的数字当成今天的 | 页面显示数据时间；刷新频率按需求定 |
| 实例扩容后连接数暴涨 | 新实例启动时报 too many clients，发布失败 | 连接池按并发需求配置，加连接池代理 |
| 用读副本服务「我刚提交的数据」 | 用户提交后刷新页面看不到 | 读自己写的请求走 primary 或等副本追上 |
Advantages
- 约束、外键和事务让数据在多个应用版本、多个写入方之间保持一致
- SQL 可以临时写任意 join 和聚合，不需要提前为每个查询设计存储结构
- 工具链成熟：备份、复制、审计、BI 工具都能直接用
Disadvantages
- schema 变更要走 migration，大表加列、加索引要考虑锁和耗时
- 写入集中在一个 primary 上，水平扩展要靠拆库或分片，跨库 join 和事务变复杂
- 每个索引、约束都增加写入成本
- 连接是服务器端资源，连接数不能随应用实例无限增长
面试时这样回答
- 说清数据库替你守什么。 主键、外键、唯一、
CHECK约束，以及哪条业务规则要在一个事务里完成。 - 列出热路径查询。 列表页用 JOIN 或批量查询避免 N+1，并估算往返次数乘以 RTT。
- 重查询预计算。 报表用 materialized view 或汇总表，说出刷新方式、刷新频率和用户看到的数据时间。
- 说读扩展的顺序。 连接池 → 只读副本 → 按业务拆库 → 分片，每一步说一个代价。
- 说一个故障。 例如 PostgreSQL 外键没索引导致删除时全表扫描加锁，或实例扩容把连接数打满。
相关章节：SQL 还是 NoSQL、NoSQL databases、ACID vs BASE、Indexes、SQL 调优、Normalization vs Denormalization、Database Replication、Database Federation。
Examples
一手证据
- PostgreSQL：Constraints（外键不会自动建索引）
- MySQL：FOREIGN KEY Constraints（外键列自动建索引）
- PostgreSQL：REFRESH MATERIALIZED VIEW
- PostgreSQL：Materialized Views
- MySQL：CREATE VIEW
- PostgreSQL：Connections and Authentication（max_connections）
- PostgreSQL：Resource Consumption（work_mem）
- Django：QuerySet API（select_related、prefetch_related）
- graphql/dataloader
- PgBouncer：Features（session / transaction / statement pooling）
N+1 算例的往返时间是假设值，用来说明量级；实际数字用 EXPLAIN ANALYZE 和应用侧的查询计数测出来。