---
title: "6.4 数据库设计与优化"
description: "AI 交叉论证法、索引策略、行级安全、连接管理、性能诊断"
---
# 6.4 数据库设计与优化
> **本节目标**：学会用 AI 交叉论证法设计健壮的数据库，理解索引、安全、连接管理等关键概念，能在应用变慢时知道从哪里下手。
---
## 老王的微信读书笔记翻车记
老王做了一个"微信读书笔记"应用——用户可以看书、写笔记、划线、分享。他让 AI 设计了表结构，看着挺像回事：`users` 表、`books` 表、`notes` 表、`highlights` 表，关系也对，约束也有。老王觉得没问题，就直接上线了。
前 100 个用户，一切正常。页面秒开，体验流畅。
用户涨到 1000 时，偶尔有人反馈"加载有点慢"，老王没太在意——可能是用户网络不好吧。
用户涨到 5000 时，问题彻底爆发了：打开笔记列表要等 3 秒，搜索功能直接超时报错。老王一脸懵——数据才几万条，怎么就慢了？他以为是服务器不够强，想着要不要升级配置。
他找了个懂数据库的朋友看了一眼，朋友说了三个字："没索引。"
老王的 `notes` 表有 `user_id` 和 `book_id` 两个外键，但都没有索引。每次查"某个用户的笔记"，数据库要把整张表从头扫到尾——5000 个用户、每人 50 条笔记 = 25 万行全表扫描。就像在一本没有目录的 25 万页大书里找某个人写的内容，只能一页一页翻。
朋友帮他加了两个索引，总共花了不到一分钟。查询从 3 秒变成了 30 毫秒——快了 100 倍。
老王很震惊：就加了两行代码，性能提升这么大？
朋友说："数据库设计不只是画表、连外键。索引、安全、连接管理这些'看不见的东西'，才决定了你的应用能不能扛住真实用户。表结构设计得再漂亮，这些基础设施没做好，用户一多就崩。"
这一节，我们就来学这些"看不见但至关重要"的东西。
---
## AI 交叉论证法（炼蛊）
新手很难一眼看出 Schema 设计的好坏。你让 AI 设计了一套表结构，看着挺合理，但真的没问题吗？数据类型选对了吗？外键列有索引吗？约束完整吗？有没有性能隐患？
你自己可能看不出来，但另一个 AI 往往能发现问题。这就是"交叉论证法"的核心思路：
1. 让 **AI 1 号** 根据你的需求设计表结构
2. 把生成的代码发给 **AI 2 号**，让它以"资深 PostgreSQL 架构师"的身份审查
3. 根据 AI 2 号的反馈，让 AI 1 号修改
4. 重复 1-2 轮，得到健壮的设计
为什么这个方法有效？因为每个 AI 都有自己的"盲点"。AI 1 号设计表结构时，可能专注于业务逻辑的正确性，忽略了性能优化；AI 2 号以审查者的视角来看，更容易发现"这个外键没索引"、"这个列类型不对"之类的问题。
这就像写代码时的 Code Review——自己写的代码自己很难发现 bug，但同事一眼就能看出来。AI 之间的交叉审查也是同样的道理。
通常经过两轮"左右互搏"，你就能得到一个相当健壮的数据库设计。不需要你自己是数据库专家，只需要会"让 AI 互相挑毛病"。
---
## 索引：数据库的目录
老王的故事已经说明了索引的重要性。这里展开讲讲，因为索引是数据库性能优化中最重要、也是最容易被忽视的环节。
### 什么是索引，解决什么问题
没有索引的查询就像在一本没有目录的 500 页书里找某个关键词——只能从第一页翻到最后一页。这叫**全表扫描（Sequential Scan）**，数据越多越慢。100 行数据全表扫描感觉不到，10 万行就开始卡了，100 万行可能要等好几秒。
**索引（Index）** 就是给数据库建的"目录"。有了索引，数据库可以直接跳到目标位置，不需要逐行扫描。查询速度提升 100-1000 倍是常见的。
索引的原理并不复杂。想象一本字典：如果没有拼音索引，你要找"数据库"这个词，只能从第一页翻到最后一页。有了拼音索引，你先查"S"开头的部分，再找"shu"，直接定位到对应页码。数据库索引的工作方式类似——它维护一个排好序的"目录"，通过这个目录快速定位到数据所在的位置。
### 哪些列需要索引
索引不是免费的——它占用额外存储空间（通常是被索引数据的 10%-30%），而且每次插入、更新数据时都要同步更新索引，会拖慢写入速度。所以不是每个列都该加索引，只在"值得"的列上建：
| 必须加索引 | 原因 |
|-----------|------|
| 外键列（`user_id`、`post_id`） | JOIN 和级联删除都依赖它，不加会全表扫描 |
| WHERE 条件常用的列 | 比如 `status`、`email`、`phone`，这些列经常出现在查询条件里 |
| ORDER BY 排序列 | 比如 `created_at` 倒序，没有索引的排序需要把所有数据读出来再排 |
| 不需要加索引 | 原因 |
|-------------|------|
| 很少用于查询的列 | 比如 `bio`、`avatar`，几乎不会出现在 WHERE 或 ORDER BY 里 |
| 布尔值列（只有 true/false） | 区分度太低，索引帮不上忙——想象一本书的目录只有"奇数页"和"偶数页"两项，没什么用 |
| 数据量很小的表（< 1000 行） | 全表扫描也很快，加索引反而浪费空间和写入性能 |
::: danger PostgreSQL 不会自动给外键加索引
这是老王踩的坑，也是最常见的性能陷阱。很多人以为定义了外键，数据库就会自动建索引——**不会的**。MySQL 会自动给外键建索引，但 PostgreSQL 不会。你必须手动创建。
审查 AI 生成的 Schema 时，第一件事就是检查：每个外键列是否都有对应的索引。这一条规则能帮你避免 80% 的性能问题。
:::
### 不同类型的索引
大多数情况下，默认的 **B-tree 索引**就够了——B-tree 是数据库最通用的索引类型，你不需要理解它的内部结构，知道它适合绝大多数查询就够了。它适合等值查询（`WHERE email = 'xxx'`）和范围查询（`WHERE created_at > '2024-01-01'`）。当你说"给某个列加索引"而不指定类型时，数据库默认创建的就是 B-tree。
但有两种特殊场景值得了解：
**GIN 索引**：如果你的表有 `jsonb` 列（比如存用户标签、商品属性、AI 生成的结构化数据），普通的 B-tree 索引对 JSON 内部的查询无效。GIN（Generalized Inverted Index，通用倒排索引）专门为"在复杂数据结构里快速搜东西"的场景设计——比如从一堆 JSON 里找某个标签。
举个例子：你的 `users` 表有一个 `preferences` 列，类型是 `jsonb`，存着 `{"theme": "dark", "language": "zh", "tags": ["vip", "early-adopter"]}`。想查"所有 VIP 用户"，B-tree 索引帮不上忙，但 GIN 索引能把这个查询从秒级降到毫秒级。
**部分索引（Partial Index）**：如果你用了软删除（`deleted_at` 字段），大部分查询只关心未删除的数据。普通索引会把所有行都索引起来，包括已删除的。部分索引可以只索引 `deleted_at IS NULL` 的行——索引更小、更快，而且不会被已删除的数据"污染"。
这两种索引你不需要自己决定什么时候用——AI 在生成代码时通常会根据场景选择合适的索引类型。但当你看到 AI 生成了 GIN 或部分索引时，知道它在做什么、为什么这么做，就不会觉得困惑了。
---
## 行级安全（RLS）：防止数据泄露的最后一道墙
### 它解决什么问题
想象小红的外卖平台出了个 bug：某个 API 接口忘了加权限检查，任何用户都能看到其他人的订单——包括收货地址、手机号、消费记录。
如果权限控制只在代码里做，一个 bug 就能导致全部用户数据泄露。而代码里的 bug 是不可避免的——再优秀的程序员也会犯错，再严格的 Code Review 也有漏网之鱼。
**行级安全（Row-Level Security, RLS）** 是在数据库层面做权限控制。你定义一条规则："用户只能看到自己的订单"，数据库会自动过滤，**无论从哪个入口查询都生效**——API 接口、后台管理面板、数据迁移脚本，全部受约束。
这意味着即使代码有 bug，数据库也不会泄露其他用户的数据。RLS 就像银行金库的最后一道门——即使小偷突破了所有外围防线（代码层的权限检查），到了金库门前还是打不开（数据库层的 RLS）。
### 什么时候需要 RLS
**如果你用 Supabase，RLS 几乎是必须的。** 因为 Supabase 的客户端 SDK 直接连数据库（通过 PostgREST），前端代码可以直接发 SQL 查询。没有 RLS 的话，任何人都可以在浏览器控制台里修改查询条件，看到所有用户的数据——这等于裸奔。
**如果你用 Neon + 自己的后端 API，RLS 不是必须的。** 因为用户的请求先经过你的后端代码，你可以在 API 层做权限控制（比如"只返回当前登录用户的订单"）。但 RLS 作为额外的安全层仍然值得考虑——多一道防线总比少一道好，特别是当你的应用处理敏感数据（支付信息、个人隐私）时。
### 审查 RLS 策略的要点
如果 AI 生成了 RLS 策略，检查三件事：
- **是否真的启用了？** 光定义策略不够，还要 `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`，否则策略不生效——就像手机设了密码但没开启锁屏，密码形同虚设。这是最常见的遗漏——策略定义和启用是两个独立步骤，缺一不可
- **策略覆盖全了吗？** SELECT、INSERT、UPDATE、DELETE 各需要单独的策略，漏一个就是安全漏洞。比如你定义了"用户只能查看自己的订单"（SELECT 策略），但忘了定义 UPDATE 策略，那用户就能修改别人的订单
- **会不会拖慢查询？** RLS 策略里用到的列（比如 `user_id`）必须有索引，否则每次查询都要全表扫描来做权限过滤。这又回到了索引的重要性——没有索引的 RLS 策略，安全是安全了，但慢得让人受不了
---
## 连接管理：用户一多就崩溃的元凶
### 它解决什么问题
老王的应用又出问题了。这次不是慢，而是直接报错：`too many connections`。才 50 个人同时用，数据库就拒绝新连接了。
老王很困惑：50 个用户算什么？别人的应用几千几万用户都没问题，我这 50 个就崩了？
原因是：**每个数据库连接都要消耗服务器资源**。你用某些 App 时遇到过"服务繁忙，请稍后再试"吗？背后的原因之一就是连接被占满了。一个连接大约占用 1-3 MB 内存，还要维护 TCP 连接状态、认证信息等。免费套餐的数据库服务器资源有限，总内存可能就几百 MB，能支撑的连接数大约是 100-200 个。
问题出在老王的应用代码上：每次收到用户请求，就新建一个数据库连接；请求处理完，连接也不释放。50 个用户同时刷页面，每人触发几个请求，连接数瞬间就打满了。后面的请求全部排队等待，用户看到的就是页面一直转圈。
### 连接池：复用而不是新建
**连接池（Connection Pooler）** 解决这个问题。它的原理很简单：预先建好一批数据库连接（比如 10 个），放在一个"池子"里。应用需要连接时，从池子里借一个用；用完还回去，下一个请求复用同一个连接。
这就像共享单车：城市里不需要每个人都买一辆自行车，只需要在路边放一批共享单车，需要的人骑走，到了目的地还回来，下一个人接着用。100 个人可能只需要 20 辆车就够了，因为不是所有人同时在骑。
连接池也是一样。100 个并发请求可能只需要 10 个数据库连接——因为每个请求占用连接的时间很短（通常几毫秒到几十毫秒），大部分时间连接都是空闲的，可以被其他请求复用。
Neon 和 Supabase 都内置了连接池。部署时确保使用连接池地址（通常端口不同，比如 Supabase 直连是 5432，连接池是 6543）。6.0 节已经详细介绍了这一点。
### 事务不要"占着茅坑不拉屎"
事务执行期间会占用一个数据库连接，并且可能持有锁。如果事务里包含了调用外部 API、发送邮件等耗时操作，这个连接就被白白占着，其他请求只能排队。
想象你在超市自助结账，前面的人扫完商品后，站在机器前打了个电话才付款。后面排队的人只能干等。正确的做法是：扫完商品、付完款、拿走东西（数据库操作），然后到旁边去打电话（外部调用）。
原则：**事务里只放数据库操作**。外部调用放在事务提交之后。
---
## 数据安全基础
数据库里存的是你最宝贵的资产——用户数据。一旦泄露或损坏，后果可能是灾难性的。这一节讲三个最基本的安全措施。
### SQL 注入：ORM 已经帮你挡住了
SQL 注入是最经典的安全漏洞，已经存在了二十多年，至今仍然是 Web 应用被攻击的主要方式之一。
它的原理很简单：攻击者在输入框里填入恶意的 SQL 片段，如果后端代码直接把用户输入拼接到 SQL 语句里，这些恶意片段就会被当作 SQL 执行。比如在登录框的"用户名"里输入 `' OR 1=1 --`，如果代码没做防护，就能绕过密码验证登录任何账号。
好消息是：**使用 ORM（Drizzle/Prisma）就自动防住了**。ORM 内部使用**参数化查询**——用户输入的内容会被当作"数据"而不是"代码"处理，无论输入什么都不会被当作 SQL 执行。这就像把用户的输入装进一个密封的信封里交给数据库，数据库只会读信封里的内容，不会把它当作指令执行。
但如果审查时看到 AI 生成了手写 SQL（字符串拼接的那种，比如 `` `SELECT * FROM users WHERE name = '${userName}'` ``），要警惕——这种写法没有参数化保护，容易被 SQL 注入攻击。应该改成 ORM 的写法或参数化查询。
### 最小权限：别用管理员账号跑应用
这个道理很直觉：你不会把家里所有房间的钥匙都给快递员，只会给他大门的钥匙。你不会把银行卡密码告诉外卖小哥，只会付当次的餐费。数据库也一样。
应用连接数据库的账号，应该只有它需要的最小权限：能读数据、能写数据、能改数据，但不能删表、不能改表结构、不能创建新用户。管理员账号只在迁移和维护时使用。
为什么这很重要？因为应用代码是暴露在互联网上的，它是攻击者最容易接触到的入口。如果应用使用的数据库账号拥有管理员权限，一旦应用被攻破（比如通过某个未修复的漏洞），攻击者就能用这个账号做任何事——删除所有表、导出所有数据、甚至植入后门。
但如果应用账号只有读写权限，即使被攻破，攻击者也做不了太大的破坏——他能读到数据（这已经很糟了），但至少不能删表、不能改结构、不能影响数据库的正常运行。
### 备份是底线
数据是产品的灵魂。代码丢了可以重写，UI 丑了可以换皮，但如果用户数据丢了——用户的账号、订单、聊天记录、创作内容——你的产品就彻底完了。没有任何技术手段能从零恢复丢失的用户数据。
备份策略三要素：
- **自动备份**：Neon 和 Supabase 都提供自动备份，确认已开启。不要依赖手动备份——你一定会忘的
- **多地备份**：重要数据不要只存一个地方。云服务商的数据中心也可能出故障（虽然概率很低），鸡蛋不要放在一个篮子里
- **恢复演练**：这是最容易被忽视的一点。定期测试从备份恢复，确认备份真的能用。太多人做了备份但从没测试过，等到需要恢复时才发现备份文件损坏、格式不兼容、或者恢复流程根本跑不通。备份的价值不在于"有没有做"，而在于"能不能恢复"
---
## 性能诊断：查询慢了怎么办
你的应用上线后，某天突然有用户反馈"页面加载很慢"。不要慌，按这个顺序排查，大部分问题都能定位到：
**第一步：确认是不是数据库的问题。** 页面慢不一定是数据库慢。可能是前端渲染太重、可能是图片太大、可能是 API 服务器响应慢、也可能是用户的网络不好。在慢的接口上加日志，记录数据库查询耗时。如果查询本身只要 10ms 但接口要 3 秒，问题不在数据库，去别的地方找原因。
**第二步：检查有没有缺失索引。** 如果确认是数据库查询慢，第一个怀疑对象就是缺索引。PostgreSQL 有一个叫 `EXPLAIN ANALYZE` 的工具，相当于给查询做"X 光"——能看到数据库执行这条查询时用了哪些索引、扫描了多少行、每一步花了多少时间。如果看到 `Sequential Scan`（全表扫描），说明缺索引。把 `EXPLAIN ANALYZE` 的输出发给 AI，它能告诉你该在哪个列上加索引。
**第三步：检查 N+1 问题。** 如果一个页面发了几十上百次数据库查询（可以在浏览器开发者工具的 Network 面板里看到），大概率是 N+1。6.3 节已经详细讲过这个问题——改成 JOIN 或批量查询就能解决。
**第四步：检查连接和事务。** 连接数是否接近上限？有没有长时间未提交的事务在阻塞其他操作？这些信息可以在数据库平台的监控面板里看到。Neon 和 Supabase 都提供了直观的监控界面，能看到当前活跃连接数、最慢的查询、锁等待情况等。
大部分性能问题都能在前两步解决——要么不是数据库的问题，要么是缺索引。真正需要深入到第三步、第四步的情况并不多。
::: tip 加载 Supabase Postgres 最佳实践
如果你使用支持 Skills 的 AI 编码工具，可以加载 `supabase-postgres-best-practices` 这个 Skill。它包含 31 条经过生产验证的 PostgreSQL 优化规则，涵盖索引策略、连接管理、RLS 性能优化、分页模式等，AI 会自动在生成代码时应用这些最佳实践。
:::
---
::: tip 本节核心要点
- **AI 交叉论证**：让不同 AI 互相审查 Schema，2 轮迭代就能得到健壮设计
- **索引是性能关键**：外键列必须加索引，PostgreSQL 不会自动创建
- **RLS 防数据泄露**：数据库层面的权限控制，即使代码有 bug 也不会泄露数据
- **连接池防崩溃**：生产环境必须用连接池，否则几十个用户就能打满连接
- **备份是底线**：自动备份 + 恢复演练，缺一不可
:::