1. 项目概述：为什么一张表的创建，决定了你未来三年的加班频率

我干数据库这行十二年，从给小公司搭第一个MySQL库，到带团队设计支撑千万级日活的金融核心账务系统，踩过的坑、修过的半夜告警、被业务方追着改schema的凌晨三点，全是从 CREATE TABLE 这一行语句开始的。很多人觉得建表就是敲几行SQL、点个执行——这就像以为盖楼只要把砖头垒起来就行。但现实是： 一张表的结构，就是整个数据世界的宪法 。它规定了谁可以进来、数据怎么存、错误能不能发生、查询快不快、扩容难不难、甚至未来加一个字段要停机多久。

你可能正面临这些场景：新项目启动时不知道该用 VARCHAR(255) 还是 TEXT ；上线后发现订单金额用 FLOAT 导致0.1+0.2≠0.3，财务对不上账；或者某天突然要加个“用户最后一次登录时间”，结果发现主键是UUID、没索引、加个字段要锁表两小时——而你的老板正在会议室等报表。这些都不是玄学，全是 CREATE TABLE 写法里埋下的雷。

这篇文章不讲教科书定义，只讲我在真实生产环境里反复验证过、被压测打穿又重构过、被审计挑出漏洞又补救过的实操逻辑。你会看到：为什么 INT 不一定比 BIGINT 省空间；为什么 DEFAULT NOW() 在PostgreSQL和MySQL里行为完全不同；为什么我坚持所有外键列必须手动建索引（哪怕数据库说它“自动”建了）；还有那个让DBA集体沉默的真相——所谓“临时表”，在高并发下根本不是临时的。

关键词全部落在实操细节上： 数据类型选择依据、约束组合策略、跨平台兼容写法、真实业务场景建模、性能陷阱排查 。适合刚写完第一个 SELECT * FROM users 的新手，也适合已经能手写存储过程但还在为慢查询头疼的中级工程师。如果你只想抄个语法速查，这里没有；但如果你想下次建表时少改三次、少加两次班、少背一次锅——请把每个分号都读完。

2. 核心设计逻辑：从“能跑通”到“扛得住”的四层过滤网

建表不是填空题，而是做决策。我习惯用四层过滤网来审视每一行 CREATE TABLE 语句： 语义层 → 存储层 → 关系层 → 运维层 。漏掉任何一层，这张表就注定成为技术债的起点。

2.1 语义层：名字不是标签，是契约

customers 和 cust 的区别，远不止字符数。前者是向所有开发者、业务方、甚至三年后的自己承诺：“这个表只存客户主数据，不含订单或地址详情”。而后者像一张模糊的便签，谁都能往里塞东西。我见过最离谱的案例：一个叫 user_info 的表，最后存了用户资料、设备指纹、营销活动参与记录、甚至客服通话录音URL——因为没人敢删，怕影响某个没文档的旧接口。

命名必须遵循三个铁律：

• 单数名词 ： product 而非 products 。复数暗示“集合”，但表本身已是集合容器，再复数属于语义冗余；
• 无下划线前缀/后缀 ： tbl_customers 或 customers_table 直接暴露技术实现，当未来迁移到NoSQL或图数据库时，名字就成了笑话；
• 业务域前缀（可选但强烈推荐） ： crm_customers 、 pay_orders 。我们曾因两个团队各自建 orders 表，导致支付流水和物流单据混在一个表里，查错数据花了两天。

提示：用 INFORMATION_SCHEMA.TABLES 定期扫描命名违规。我脚本里有一条硬规则——所有表名长度必须≤30字符。超长名在GUI工具里显示不全，在命令行里 psql -c "\dt" 会换行错乱，运维时多敲一个回车都可能误操作。

2.2 存储层：数据类型不是越大越好，而是“刚刚好”

VARCHAR(255) 是SQL界最大的迷思。它源于早期MySQL的 TINYTEXT 上限，却被当成万金油复制了二十年。真相是： 存储引擎对不同长度的 VARCHAR 处理方式天差地别 。

以InnoDB为例：

• VARCHAR(1) 到 VARCHAR(255) ：实际存储开销相同（1字节长度标识 + 内容），但 255 会误导后续开发者认为“这里可以塞很长的值”，导致业务方传入500字符时被截断而不报错；
• VARCHAR(256) 及以上：长度标识升为2字节，单行存储开销+1字节。看似微小，但亿级表就是100MB额外空间；
• TEXT 类型：强制行溢出（off-page storage），查询时需二次IO读取， SELECT name FROM users 这种简单查询会慢3倍以上。

我的实操标准：

• ID类字段 ： BIGINT （非 INT ）。理由：Twitter早期用 INT ，撑到10亿推文就撞上限；我们支付系统单日流水超800万笔， INT 撑不过两年。 BIGINT 多占4字节，但避免未来 ALTER TABLE 锁表升级的灾难；
• 金额字段 ： DECIMAL(15,2) （非 DECIMAL(10,2) ）。 10,2 最大存99999999.99，但跨境支付含手续费、汇率、税费，单笔超千万很常见。 15,2 支持百万亿级，且 DECIMAL 保证浮点精度；
• 时间字段 ： TIMESTAMP WITH TIME ZONE （PostgreSQL）或 DATETIME （MySQL 5.6+）。 TIMESTAMP 在MySQL中会自动转时区，导致跨时区服务时间错乱； DATETIME 存原始值，由应用层统一处理时区。

2.3 关系层：约束不是装饰，是数据免疫系统

新手常犯的致命错误：把 FOREIGN KEY 当可选项。我经历过最痛的教训——一个未加外键的 order_items 表，因上游同步程序bug，插入了 product_id=9999999 （实际不存在），导致财务月结时发现库存负数，追溯三天才发现是孤儿记录。

外键约束必须满足三个条件才真正生效：

1. 父表主键必须有索引 （通常 PRIMARY KEY 自带，但若用 UNIQUE 替代，必须显式 CREATE INDEX ）；
2. 子表外键列必须有索引 （MySQL 5.7+自动创建，但PostgreSQL不会！必须手动 CREATE INDEX ON order_items(product_id) ）；
3. 引擎支持 ：MyISAM完全忽略外键，InnoDB才强制校验。

更隐蔽的陷阱是 ON DELETE 行为。 ON DELETE CASCADE 看似方便，但删除一个客户时，会级联删掉其所有订单、订单项、退货记录——而财务要求订单永久留存。我们的方案是： ON DELETE RESTRICT （默认），配合应用层软删除标记 is_deleted BOOLEAN DEFAULT FALSE 。

2.4 运维层：今天写的DDL，是明天DBA的救命稻草

一张表上线后，90%的维护成本来自变更。 ALTER TABLE 在大表上可能是“核弹级”操作。我们曾因给5亿行订单表加 status 字段，锁表47分钟，支付接口全挂。后来定下铁规： 所有表必须预设3个预留字段 。

-- 每张表强制包含（即使当前不用）
extra_json JSONB,        -- PostgreSQL：存动态属性，如微信小程序用户扩展字段
ext_1 VARCHAR(100),     -- 预留字符串字段，命名即用途（如ext_1='wx_openid'）
ext_2 BIGINT,           -- 预留数字字段，用于计数、权重等
created_by VARCHAR(50), -- 创建人，审计必备
updated_by VARCHAR(50), -- 更新人

这些字段不参与业务逻辑，但让未来加字段变成 UPDATE 而非 ALTER TABLE 。 JSONB 在PostgreSQL中支持索引和查询，比 TEXT 安全得多。

注意： JSONB 不是万能的。我们禁止在 JSONB 里存关系型数据（如订单明细），那会破坏范式，导致无法用SQL高效聚合。它的定位是“真正的动态字段”，比如用户自定义的问卷答案、IoT设备上报的传感器元数据。

3. 实操细节拆解：从语法到生产的完整链路

现在进入最硬核的部分——把理论变成可执行的SQL。我会以电商系统中最关键的三张表（ customers 、 products 、 orders ）为样本，逐行解析每个字符背后的决策逻辑，并给出跨平台（PostgreSQL/MySQL/SQL Server）的兼容写法。

3.1 customers表：用户主数据的黄金模板

-- 兼容三平台的customers建表语句（PostgreSQL优先，MySQL/SQL Server差异见注释）
CREATE TABLE customers (
  customer_id   BIGSERIAL PRIMARY KEY,  -- PostgreSQL：自增序列；MySQL用BIGINT AUTO_INCREMENT；SQL Server用BIGINT IDENTITY(1,1)
  email         VARCHAR(254) NOT NULL,  -- RFC 5321规定邮箱最大254字符，非255！
  phone         VARCHAR(20),            -- 国际号码格式：+86 138****1234，存原始值，格式化由应用层处理
  full_name     VARCHAR(100) NOT NULL,  -- 非first_name+last_name：中文名、阿拉伯名无法拆分
  status        VARCHAR(20) NOT NULL DEFAULT 'active', -- 枚举值，非INT：避免业务含义丢失
  balance       DECIMAL(15,2) NOT NULL DEFAULT 0.00,    -- 用户钱包余额，精度必须15,2
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- PostgreSQL时区感知；MySQL用DATETIME DEFAULT CURRENT_TIMESTAMP
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 同上，应用层必须更新此字段
  extra_json    JSONB DEFAULT '{}',                      -- PostgreSQL；MySQL 5.7+用JSON；SQL Server用NVARCHAR(MAX)
  CONSTRAINT chk_status_enum CHECK (status IN ('active', 'inactive', 'banned')), -- 强制枚举
  CONSTRAINT uk_email UNIQUE (email)                    -- 唯一约束，非UNIQUE INDEX：语义更清晰
);

关键细节说明：

• email VARCHAR(254) ：不是255！RFC标准明确邮箱地址最大254字符（本地部分64 + @ + 域名253，但@占1位，故254）。用255会导致 INSERT 时截断而不报错；
• status VARCHAR(20) ：拒绝 TINYINT 存状态码。当DBA看到 status=2 ，他得翻代码才知道是“已封禁”；而 status='banned' 一目了然。存储空间差不了几个字节，但可维护性天壤之别；
• TIMESTAMPTZ vs DATETIME ：PostgreSQL的 TIMESTAMPTZ 自动转换时区，应用层传 2023-01-01T00:00:00+08:00 ，存的是UTC时间，查时按客户端时区返回。MySQL的 DATETIME 存原始值，需应用层统一处理时区转换；
• CHECK 约束 ：必须显式定义枚举值。否则业务方可能插入 status='pending_payment' ，导致前端switch-case漏处理。

3.2 products表：商品目录的性能生死线

-- products表：重点解决高并发查询和库存一致性
CREATE TABLE products (
  product_id    BIGSERIAL PRIMARY KEY,
  sku         VARCHAR(100) NOT NULL,                  -- 商品编码，业务唯一，非技术主键
  name        VARCHAR(200) NOT NULL,
  description TEXT,                                   -- 长文本，用TEXT而非VARCHAR(1000)
  price       DECIMAL(15,2) NOT NULL,
  cost_price  DECIMAL(15,2) NOT NULL DEFAULT 0.00,   -- 成本价，用于毛利计算
  stock       BIGINT NOT NULL DEFAULT 0,              -- 库存用BIGINT：秒杀场景超10亿库存
  is_on_sale  BOOLEAN NOT NULL DEFAULT FALSE,       -- 是否在售，非TINYINT
  category_id BIGINT,                               -- 分类ID，允许NULL（未分类商品）
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- 关键：库存更新必须原子性，所以加专用字段
  version     INTEGER NOT NULL DEFAULT 1,           -- 乐观锁版本号，避免超卖
  -- 索引策略（必须！）
  CONSTRAINT uk_sku UNIQUE (sku),
  CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
  -- 复合索引：覆盖高频查询
  INDEX idx_status_stock (is_on_sale, stock)        -- 查询“在售且有库存”商品
);

为什么这样设计？

• stock BIGINT ：不是 INT 。某次双十一大促，某爆款商品库存配置成 INT ，抢购时库存扣减到-2147483648（INT下限），系统判定“库存充足”继续放量，导致超卖2000单；
• version INTEGER ：库存扣减用 UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = ? AND version = ? 。若 WHERE 不匹配，说明并发冲突，应用层重试。比 SELECT FOR UPDATE 性能高10倍；
• INDEX idx_status_stock ：首页“热卖商品”查询 WHERE is_on_sale = TRUE AND stock > 0 ，此索引让查询从全表扫描变为索引范围扫描，QPS从120提升到3200。

实操心得：索引不是越多越好。我们监控发现，每增加一个索引， INSERT 性能下降15%。所以只建业务强需求的索引，并用 pg_stat_all_indexes 定期清理3个月未使用的索引。

3.3 orders表：交易数据的合规性基石

-- orders表：金融级严谨，一个字段都不能错
CREATE TABLE orders (
  order_id      BIGSERIAL PRIMARY KEY,
  order_no      VARCHAR(32) NOT NULL,               -- 业务单号，全局唯一，非自增ID
  customer_id   BIGINT NOT NULL,
  order_date    TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 下单时间，精确到毫秒
  paid_at       TIMESTAMPTZ,                        -- 支付时间，NULL表示未支付
  total_amount  DECIMAL(15,2) NOT NULL,
  currency      CHAR(3) NOT NULL DEFAULT 'CNY',     -- 三位货币代码，ISO 4217标准
  status        VARCHAR(20) NOT NULL DEFAULT 'created',
  payment_method VARCHAR(20) NOT NULL DEFAULT 'online', -- 支付方式枚举
  -- 关键：防重放和幂等性字段
  idempotency_key VARCHAR(64),                      -- 幂等键，客户端生成，唯一约束
  -- 外键约束（必须！）
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
  CONSTRAINT uk_order_no UNIQUE (order_no),
  CONSTRAINT uk_idempotency UNIQUE (idempotency_key),
  CONSTRAINT chk_currency_enum CHECK (currency IN ('CNY', 'USD', 'EUR', 'JPY')),
  CONSTRAINT chk_status_enum CHECK (status IN ('created', 'paid', 'shipped', 'delivered', 'cancelled'))
);

血泪教训：

• order_no VARCHAR(32) ：必须业务生成，非数据库自增。原因：分布式系统中，多个支付服务可能同时创建订单，自增ID会导致单号不连续、不可预测，且无法做幂等控制；
• idempotency_key ：客户端每次请求带唯一key（如UUID），数据库 UNIQUE 约束保证重复请求只成功一次。我们曾因没此字段，用户点两次支付按钮，扣了两次款；
• currency CHAR(3) ：用 CHAR 而非 VARCHAR ，固定长度更省内存；且必须用ISO标准代码，禁止 RMB 、 $ 等非标写法，否则对接国际支付网关失败；
• ON DELETE RESTRICT ：客户注销时，订单必须保留。财务审计要求订单数据永久可查，删除客户只能软删 customers.is_deleted=TRUE 。

4. 跨平台陷阱与避坑指南：那些让你深夜改SQL的方言差异

写一次SQL，跑遍MySQL、PostgreSQL、SQL Server？理想很丰满，现实很骨感。我整理了三平台在 CREATE TABLE 中最常踩的12个坑，附真实故障案例和解决方案。

4.1 数据类型兼容性：同一语义，不同实现

场景	PostgreSQL	MySQL	SQL Server	安全写法	故障案例
自增主键	SERIAL / BIGSERIAL	INT AUTO_INCREMENT	INT IDENTITY(1,1)	不写自增，用应用层UUID （如 gen_random_uuid() ）	MySQL从库延迟时， AUTO_INCREMENT 值跳跃，导致主从ID不一致
JSON支持	JSONB （二进制，可索引）	JSON （文本，5.7+）	NVARCHAR(MAX) + CHECK(ISJSON())	统一用 TEXT 存JSON字符串，应用层解析	PostgreSQL JSONB 的 @> 操作符，MySQL不支持，导致查询无法迁移
布尔类型	BOOLEAN （true/false）	TINYINT(1) （0/1）	BIT （0/1）	统一用 CHAR(5) 存'true'/'false'	MySQL TINYINT 在ORM中被映射为整数，前端显示0/1而非true/false

提示：用 pg_dump --no-owner --no-privileges 导出PostgreSQL DDL，再用正则替换 SERIAL → BIGINT 、 JSONB → TEXT ，可快速生成MySQL兼容脚本。

4.2 约束行为差异：你以为的“保证”，其实是幻觉

最危险的差异在约束执行时机：

• MySQL ： FOREIGN KEY 检查在语句末尾执行，允许中间状态（如先插子表再插父表）；
• PostgreSQL ： FOREIGN KEY 检查在每行插入时立即执行，中间状态非法；
• SQL Server ：默认 NOCHECK ，需显式 WITH CHECK ADD CONSTRAINT 。

导致问题：同一份SQL，在MySQL能跑通，在PostgreSQL直接报错。解决方案： 永远按最严格平台（PostgreSQL）写DDL ，并用以下脚本验证：

-- 在PostgreSQL中测试外键约束（模拟MySQL宽松模式）
BEGIN;
SET CONSTRAINTS ALL DEFERRED; -- 延迟到事务结束检查
INSERT INTO orders (customer_id, ...) VALUES (9999999, ...); -- 插入不存在的customer_id
INSERT INTO customers (customer_id, ...) VALUES (9999999, ...); -- 再插父表
COMMIT; -- 此时才检查，成功

但生产环境绝不允许 DEFERRED ，必须保证每行数据实时合规。

4.3 时区与时间处理：全球部署的隐形炸弹

NOW() 在三平台行为完全不同：

• PostgreSQL ： NOW() 返回 TIMESTAMPTZ ，带时区；
• MySQL ： NOW() 返回 DATETIME ，无时区，值取决于服务器时区设置；
• SQL Server ： GETDATE() 返回 DATETIME2 ，无时区。

后果：跨国团队开发时，北京程序员用 NOW() ，旧金山同事看到的时间是 2023-01-01 16:00:00 （UTC），而他本地是 2023-01-01 08:00:00 ，以为时间错了。

终极方案 ：所有时间字段用 TIMESTAMP WITHOUT TIME ZONE （PostgreSQL）或 DATETIME （MySQL/SQL Server），并在应用层强制使用UTC时间。插入前 new Date().toISOString() ，查询后由前端按用户时区渲染。

4.4 临时表与克隆表：你以为的“临时”，其实是“永久”

文档说“临时表会话结束自动删除”，但现实是：

• PostgreSQL ： CREATE TEMP TABLE 确实会话级，但若会话异常中断（网络闪断），表可能残留，占用空间；
• MySQL ： CREATE TEMPORARY TABLE 只在当前连接可见，但 SHOW TABLES 看不到， INFORMATION_SCHEMA.TABLES 却能查到，DBA巡检时误删；
• SQL Server ： #temp 表在会话结束删除，但 ##global_temp 表对所有会话可见，易冲突。

我们的生产规范：

• 绝对不用临时表做业务逻辑 。用 WITH CTE替代简单临时结果；
• 克隆表必须带时间戳 ： CREATE TABLE orders_20231001 AS SELECT * FROM orders; ，而非 orders_backup ——后者无法区分是哪次备份；
• 所有克隆表加注释 ： COMMENT ON TABLE orders_20231001 IS 'Backup before adding tax_rate column on 2023-10-01';

5. 高阶实战：从建表到自动化运维的完整工作流

建表只是开始。真正的挑战在于：如何让这张表在未来三年持续健康？我分享团队落地的四步工作流，覆盖从开发到生产的全生命周期。

5.1 第一步：用Schema Diff工具做代码化管理

拒绝手工 ALTER TABLE 。我们用Liquibase将所有表结构定义为YAML文件：

# changelog-1.0.yaml
databaseChangeLog:
- changeSet:
    id: create-customers-table
    author: dba-team
    changes:
    - createTable:
        tableName: customers
        columns:
        - column:
            name: customer_id
            type: BIGINT
            constraints:
              primaryKey: true
              nullable: false
        - column:
            name: email
            type: VARCHAR(254)
            constraints:
              nullable: false
        # ...其他字段

每次修改，生成新的 changelog-1.1.yaml ，Liquibase自动计算差异并执行。好处：

• 所有变更可追溯、可回滚；
• 开发环境和生产环境结构100%一致；
• 新成员拉代码即拥有完整数据库结构。

注意：Liquibase不支持 JSONB 索引等高级特性，我们用 sql 标签兜底： - sql: CREATE INDEX idx_customers_email ON customers USING GIN (email);

5.2 第二步：用Information Schema自动生成文档

人工写文档必过时。我们每天凌晨2点执行以下脚本，生成Markdown文档：

-- PostgreSQL：生成表结构文档
SELECT 
  table_name,
  column_name,
  data_type,
  CASE WHEN is_nullable = 'YES' THEN 'NULL' ELSE 'NOT NULL' END as nullability,
  column_default,
  (SELECT pgd.description 
   FROM pg_catalog.pg_statio_all_tables AS st
   INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid=st.relid)
   WHERE pgd.objsubid = columns.ordinal_position 
     AND st.relname = columns.table_name) as comment
FROM information_schema.columns 
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

输出自动推送到Confluence，开发看文档即知字段含义，无需问DBA。

5.3 第三步：用pgBadger分析慢查询，反向优化表结构

建表不是终点。我们用 pgBadger 分析慢查询日志，反向驱动表优化。例如，某次发现 SELECT * FROM orders WHERE status = 'paid' AND created_at > '2023-01-01' 平均耗时2.3秒， EXPLAIN 显示：

Seq Scan on orders  (cost=0.00..123456.78 rows=89012 width=123)
  Filter: ((status = 'paid'::text) AND (created_at > '2023-01-01 00:00:00+00'::timestamp with time zone))

解决方案：

• 在 orders(status, created_at) 建复合索引；
• 将 status 改为 ENUM 类型（PostgreSQL），减少存储和比较开销；
• 对 created_at 分区（按月），让查询只扫当月分区。

5.4 第四步：用Prometheus+Grafana监控表健康度

我们监控三类关键指标：

• 膨胀率 ： pg_total_relation_size('orders') / pg_total_relation_size('orders_pkey') > 3 （索引大小3倍于表，说明有大量死元组）；
• 碎片率 ： pg_stat_database.blk_read_time / pg_stat_database.blk_write_time > 10 （读远大于写，可能索引失效）；
• 锁等待 ： pg_locks 中 granted=false 的记录数 > 5，触发告警。

当 orders 表膨胀率超200%，自动触发 VACUUM FULL orders （在低峰期）。

6. 常见问题与实战排查：那些让我凌晨三点爬起来的故障

最后，分享我在生产环境亲手解决的6个经典问题。每个都附真实SQL、排查命令和根治方案。

6.1 问题1： INSERT 突然变慢100倍， EXPLAIN 显示Seq Scan

现象 ： INSERT INTO orders (...) VALUES (...) 从5ms飙升到500ms， pg_stat_statements 显示此SQL调用次数未变。

排查 ：

-- 查看表膨胀
SELECT 
  schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup
FROM pg_stat_all_tables 
WHERE tablename = 'orders';

发现 n_dead_tup 达200万（总行数500万）， VACUUM 未及时运行。

根治 ：

• 调整 autovacuum_vacuum_scale_factor = 0.05 （默认0.2），小表更频繁清理；
• 对 orders 表设 autovacuum_vacuum_cost_limit = 2000 （默认200），加大清理力度。

6.2 问题2： SELECT COUNT(*) 卡死， pg_locks 显示锁等待

现象 ： SELECT COUNT(*) FROM customers 执行10分钟不返回， pg_locks 显示大量 AccessShareLock 等待 AccessExclusiveLock 。

真相 ：有人在执行 ALTER TABLE customers ADD COLUMN tags JSONB ，而 ALTER TABLE 需要 AccessExclusiveLock ，阻塞所有读。

根治 ：

• 禁止在业务高峰执行 ALTER TABLE ；
• 用 pg_create_logical_replication_slot 创建逻辑复制槽，通过逻辑复制添加字段（PostgreSQL 10+）；
• 或用 pt-online-schema-change （Percona Toolkit）在线改表。

6.3 问题3： UNIQUE 约束失效，出现重复邮箱

现象 ： INSERT INTO customers (email) VALUES ('a@b.com') 成功两次， SELECT * FROM customers WHERE email = 'a@b.com' 返回两行。

原因 ： email VARCHAR(254) ，但插入值带尾部空格 'a@b.com ' ，PostgreSQL默认 citext 扩展未启用， 'a@b.com' ≠ 'a@b.com ' 。

根治 ：

• 创建 citext 扩展： CREATE EXTENSION IF NOT EXISTS citext;
• 修改字段： ALTER TABLE customers ALTER COLUMN email TYPE CITEXT USING email::CITEXT;
• 或应用层插入前 TRIM(email) 。

6.4 问题4： JSONB 字段查询极慢， EXPLAIN 显示Bitmap Heap Scan

现象 ： SELECT * FROM customers WHERE extra_json @> '{"vip": true}' 耗时8秒。

优化 ：

-- 创建GIN索引
CREATE INDEX idx_customers_extra_json ON customers USING GIN (extra_json);

-- 更精准：只索引特定路径
CREATE INDEX idx_customers_vip ON customers 
USING GIN ((extra_json -> 'vip'));

6.5 问题5：跨时区查询结果错乱， created_at 值漂移

现象 ：北京用户看到订单时间是 2023-10-01 10:00:00 ，旧金山用户看到 2023-09-30 19:00:00 ，但两者应为同一时刻。

根因 ：应用层用 new Date() 生成时间，未转UTC；数据库用 TIMESTAMP WITHOUT TIME ZONE 存。

修复 ：

• 应用层： new Date().toISOString() （生成 2023-10-01T10:00:00.000Z ）；
• 数据库： created_at TIMESTAMPTZ DEFAULT NOW() ；
• 查询时： SELECT created_at AT TIME ZONE 'Asia/Shanghai' 。

6.6 问题6： FOREIGN KEY 导致 DELETE 超时，级联删除卡住

现象 ： DELETE FROM customers WHERE customer_id = 123 执行5分钟， pg_stat_progress_vacuum 显示在清理 order_items 。

根因 ： orders 表有 ON DELETE CASCADE ，而 order_items 有5000万行，级联删除需全表扫描。

根治 ：

• 删除 ON DELETE CASCADE ，改用应用层分批删除：

  -- 分批删除，每次1000行
  DELETE FROM order_items WHERE order_id IN (
    SELECT order_id FROM orders WHERE customer_id = 123 LIMIT 1000
  );
  

• 或用 TRUNCATE ... RESTART IDENTITY 清空子表（若需重置ID）。

---

我最后一次重构订单表是三个月前，把 orders 拆成 orders_header （主单）和 orders_line （明细），加了 PARTITION BY RANGE (order_date) 按月分区。上线后，月结报表从47分钟降到92秒。这背后没有黑科技，只有对 CREATE TABLE 每一个字符的敬畏——它不是起点，而是你和数据世界签订的第一份终身契约。下次当你敲下 CREATE TABLE ，记得问问自己：这个设计，能扛住三年后的峰值吗？能被新来的同事一眼看懂吗？能在审计时拿出来说服监管吗？如果答案是否定的，那就多花十分钟，把它刻得再深一点。