SQL主键设计原理与高可用实战：从索引机制到分布式ID选型

1. 什么是SQL主键：不只是“唯一标识”，而是数据库的骨骼系统

你刚接触SQL时，可能被教过：“主键就是让每一行数据都有个身份证号”。这话没错，但太轻飘了。在我带过的三十多个数据库项目里，真正把主键当“骨骼系统”来设计的团队，上线后三年内没出过一次因主键引发的数据一致性事故；而把主键当成“加个NOT NULL和UNIQUE就完事”的项目，平均每六个月就要紧急修复一次外键断裂、重复插入或索引膨胀问题。

主键不是贴在表上的装饰性约束，它是整个关系型数据库运行逻辑的物理锚点。它直接决定查询引擎怎么走索引、优化器怎么生成执行计划、事务系统怎么锁定记录、复制机制怎么同步变更。举个最直白的例子：当你执行SELECT * FROM orders WHERE order_id = 'a1b2c3'，数据库不是在整张表里一行行翻找，而是通过主键索引树（B+Tree）最多3次磁盘I/O就定位到目标页——这个效率差，是毫秒级响应和秒级卡顿的根本分水岭。

更关键的是，主键定义了“这行数据到底是谁”。在分布式场景下，一个UUID主键能让你在新加坡、法兰克福、圣保罗三地同时写入订单而不冲突；而一个自增INT主键，一旦跨库分片，立刻面临ID重复、路由错乱、全局排序失效三大死穴。我亲眼见过一家电商公司因主键设计失误，在大促期间出现17%的订单状态不一致，根源就是用MySQL的AUTO_INCREMENT做分库主键，结果不同分片生成了相同ID。

所以别再把它当成入门概念应付考试。主键选型，本质上是在为未来三年的数据规模、业务复杂度、运维成本和故障恢复能力提前投票。它不炫技，但一招定生死。接下来我会用真实生产环境里的血泪经验，拆解每一个技术决策背后的重量——不是告诉你“怎么写语法”，而是告诉你“为什么必须这样写”。

2. 主键核心原理与架构设计：从B+Tree索引到事务隔离的底层联动

2.1 主键即索引：为什么删掉主键等于废掉半张表的性能

很多人以为“主键约束”和“主键索引”是两回事。错。在PostgreSQL、MySQL（InnoDB）、SQL Server等主流引擎中，主键约束的实现完全依赖于主键索引。当你执行CREATE TABLE t (id SERIAL PRIMARY KEY)，数据库实际做了三件事：

1. 创建id列并设置NOT NULL和唯一性校验逻辑；
2. 自动创建一个名为t_pkey的唯一B+Tree索引；
3. 将该索引标记为“聚簇索引”（Clustered Index）——这意味着表数据本身按主键顺序物理存储。

这个“聚簇”特性是性能命脉。假设orders表有1000万行，主键是order_id UUID。当执行SELECT * FROM orders WHERE order_id = 'xxx'时，B+Tree索引先定位到叶子节点，该节点直接包含整行数据（因为数据按主键排序存放），一次I/O完成读取。但如果主键是created_at时间戳，而你常查customer_id，问题就来了：customer_id没有索引，数据库只能全表扫描；即使你后来给customer_id建了二级索引，查询时需先查二级索引拿到created_at值，再回表查聚簇索引——两次I/O变三次，延迟翻倍。

提示：PostgreSQL虽不强制聚簇索引（数据物理顺序可与主键分离），但主键索引仍承担90%以上的WHERE/JOIN/ORDER BY加速任务。删除主键=删除默认最优索引路径，后续所有查询性能将断崖式下跌。

2.2 主键如何参与事务与并发控制：MVCC下的行锁粒度真相

主键还深度绑定数据库的并发控制机制。以PostgreSQL的MVCC（多版本并发控制）为例：当你执行UPDATE orders SET status='shipped' WHERE order_id = 123，数据库不会锁整张表，而是精确锁定主键值为123的那行数据对应的索引项。这个锁基于主键索引的B+Tree结构实现——锁住索引页中的特定槽位（slot），其他事务仍可并发更新order_id=456的行。

但如果主键设计不当，锁范围会灾难性扩大。比如用(customer_id, created_at)作复合主键，而你执行UPDATE orders SET status='shipped' WHERE customer_id = 1001（漏掉created_at），数据库无法利用主键索引精确定位，被迫升级为页级锁甚至表级锁。我曾处理过一个案例：某金融系统因复合主键缺失查询条件，单次更新导致23个并发事务排队等待，TPS从1200暴跌至87。

更隐蔽的是幻读（Phantom Read）问题。在REPEATABLE READ隔离级别下，主键索引的间隙锁（Gap Lock）会阻止其他事务在索引间隙插入新行。例如主键是SERIAL，当前最大ID为100，事务A执行SELECT * FROM orders WHERE id > 90 FOR UPDATE，则ID=91~100之间的间隙被锁，事务B插入ID=95会阻塞。这个机制保障了可重复读，但也意味着主键值越稀疏（如跳号ID），间隙锁覆盖范围越大，死锁风险越高。

2.3 主键与外键的强耦合：为什么“引用不存在的主键”会直接拒绝写入

外键（Foreign Key）不是独立存在的约束，它本质是对主键索引的只读引用。当定义orders.customer_id REFERENCES customers.customer_id时，数据库会在orders表上创建一个隐式索引（除非你手动建），并在每次插入/更新orders时，实时查询customers表的主键索引验证customer_id是否存在。

这个验证过程有严格性能要求：如果customers.customer_id没有主键或唯一索引，PostgreSQL会直接报错there is no unique constraint matching given keys for referenced table "customers"。这不是语法限制，而是工程必然——没有索引的验证需要全表扫描，单次插入耗时从0.2ms飙升至200ms，系统根本不可用。

实操中常见陷阱：有人为customers表建了UNIQUE INDEX ON customers(email)，却忘记设主键，然后在外键中引用email。表面可行，但email可能为空（NULL允许重复），且业务上邮箱可能变更，导致外键指向失效。真正的解法永远是：外键必须引用被引用表的主键，且该主键必须是稳定、不可变的标识符。

3. 主键类型实战选型：自然键、代理键、复合键的血泪对比

3.1 自然键（Natural Key）：业务意义清晰，但脆弱得像玻璃

自然键是直接从业务数据中提取的唯一标识，比如身份证号、邮箱、手机号、ISBN书号。它的优势一目了然：无需额外存储、业务人员一眼看懂、报表中天然可读。我在做政务系统时，用身份证号作citizens表主键，导出Excel给街道办核对时，工作人员直接说“张三的ID是110101199003072315”，比看id=847291高效十倍。

但代价极其沉重。先看三个真实故障：

• 变更灾难：某教育平台用学生学号（格式：年级+班级+序号）作主键。年级升迁时需批量更新students.id，结果外键关联的grades、attendance、courses三张表全部级联失败，因ON UPDATE CASCADE触发链过长超时。最终人工修复耗时17小时。
• 隐私雷区：GDPR合规审计发现，users表主键为邮箱，而日志系统未脱敏记录主键值，导致数万条明文邮箱泄露。整改方案只能重建表，停机4小时。
• 唯一性崩塌：电商系统用商品SKU（如IPHONE15-PRO-256GB-BLACK）作主键，但供应商提供SKU时大小写不统一（iphone15-pro...vsiPhone15-Pro...），数据库默认区分大小写，导致同一商品存成两条记录，库存扣减错乱。

注意：自然键唯一性依赖业务规则，而数据库只保证技术层面唯一。UNIQUE(email)无法防止alice@example.com和ALICE@EXAMPLE.COM被当作不同值（除非你建函数索引CREATE UNIQUE INDEX idx_email_lower ON users (LOWER(email))）。

3.2 代理键（Surrogate Key）：无业务含义，却是系统稳定的压舱石

代理键是数据库自动生成的、与业务无关的唯一标识，典型如SERIAL、BIGSERIAL、UUID。它牺牲了业务可读性，换来了工程鲁棒性。我的经验是：只要表生命周期超过6个月，或关联表超过3张，必须用代理键。

• SERIAL（PostgreSQL） /AUTO_INCREMENT（MySQL）：最轻量，4字节INT，索引体积小，CPU缓存友好。适合单库单表场景。但分库分表时ID重复，且易被爬虫枚举（订单ID从1开始递增，暴露业务量）。
• BIGSERIAL：8字节，支持2^63行，避免INT溢出。某物流系统用SERIAL，第3年ID达21亿，SERIAL上限21.47亿，紧急扩容停机2小时。
• UUID v4：16字节，全球唯一，完美解决分布式ID冲突。但体积大导致索引膨胀——1000万行表，SERIAL主键索引约120MB，UUID索引达480MB，内存缓存命中率下降，JOIN性能降35%。我们用pgcrypto.gen_random_uuid()，而非uuid_generate_v4()（后者依赖C扩展，部署复杂）。

关键技巧：UUID并非银弹。我们给高频查询表（如orders）用BIGSERIAL，给分布式写入表（如user_sessions）用UUID，再通过CREATE INDEX CONCURRENTLY ON orders USING btree (uuid_col)建辅助索引平衡读写。

3.3 复合主键（Composite Key）：精准建模关系，但复杂度指数级上升

复合主键由两个及以上列组成，典型用于关联表（Junction Table）。例如course_enrollments(student_id, course_id)，天然表达“学生选课”这一多对多关系，无需额外ID列。

但它带来三重负担：

1. 外键引用爆炸：enrollments表被grades、attendance、certificates三张表引用，每张表都要定义(student_id, course_id)外键，建表语句冗长，ORM映射配置复杂。
2. 索引体积失控：(student_id, course_id)双INT主键索引，体积是单INT的2.3倍（含指针开销）。某在线教育平台enrollments表2亿行，主键索引占磁盘42GB，备份窗口超8小时。
3. 查询陷阱：SELECT * FROM enrollments WHERE student_id = 1001无法使用主键索引（缺少course_id），必须建额外索引，否则全表扫描。

我的折中方案：对纯关联表，仍用复合主键，但强制添加单列代理键+唯一约束：

CREATE TABLE course_enrollments ( id BIGSERIAL PRIMARY KEY, -- 代理键，简化外键引用 student_id INT NOT NULL, course_id INT NOT NULL, enrolled_at TIMESTAMP DEFAULT NOW(), UNIQUE (student_id, course_id) -- 保证业务唯一性 ); -- 外键引用id，而非复合列 ALTER TABLE grades ADD COLUMN enrollment_id BIGINT REFERENCES course_enrollments(id);

既保留业务语义，又规避复合键缺陷。

4. 全流程实操：从零搭建高可用主键体系（PostgreSQL实战）

4.1 环境准备与安全基线：为什么跳过这步，后面全是坑

别急着写CREATE TABLE。先确认你的PostgreSQL实例已启用关键安全与性能参数。我见过太多团队因忽略此步，在上线后遭遇惨痛教训：

• synchronous_commit = on（默认）：确保事务提交前WAL日志已刷盘，避免崩溃丢数据。但高并发写入时延迟略升，权衡后我们设为remote_write（等待备库接收日志）。
• shared_buffers = 25% of RAM：主键索引频繁访问，需足够共享内存缓存。16GB内存服务器设为4GB，SHOW shared_buffers;验证。
• work_mem = 64MB：复杂JOIN需内存排序，过小触发磁盘临时文件，性能骤降。

安装pgcrypto扩展（UUID必需）：

# 连接postgres超级用户 psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"

提示：生产环境禁用postgres默认用户。创建专用用户：

CREATE ROLE db_admin WITH LOGIN PASSWORD 'StrongPass!2024'; GRANT ALL PRIVILEGES ON DATABASE pk_tutorial TO db_admin;

4.2 核心表创建：代理键、UUID、复合键的混合战术

我们构建一个电商核心模型：customers（客户）、products（商品）、orders（订单）、order_items（订单明细）。策略如下：

• customers：BIGSERIAL代理键，兼顾性能与容量；
• products：UUID主键，因商品需多渠道同步（APP、小程序、POS机）；
• orders：BIGSERIAL，订单ID需连续便于财务对账；
• order_items：复合主键(order_id, product_id)，天然防重复添加同一商品。

-- 1. customers表：代理键 + 业务唯一约束 CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, -- 代理键，主索引 email TEXT NOT NULL, phone VARCHAR(20), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- 业务唯一性保障（邮箱/手机二选一） CONSTRAINT uk_customer_email UNIQUE (email), CONSTRAINT uk_customer_phone UNIQUE (phone) ); -- 2. products表：UUID主键，全局唯一 CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 自动生成UUID sku TEXT NOT NULL, name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, -- 业务唯一约束 CONSTRAINT uk_product_sku UNIQUE (sku) ); -- 3. orders表：BIGSERIAL + 外键引用 CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE, order_date DATE NOT NULL DEFAULT CURRENT_DATE, status VARCHAR(20) NOT NULL DEFAULT 'pending', total_amount NUMERIC(12,2) NOT NULL, -- 为高频查询加速 INDEX idx_orders_customer_date (customer_id, order_date) ); -- 4. order_items表：复合主键 + 外键 CREATE TABLE order_items ( order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id UUID NOT NULL REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), unit_price NUMERIC(10,2) NOT NULL, -- 复合主键，天然唯一 PRIMARY KEY (order_id, product_id), -- 防止同一订单多次添加同商品 CONSTRAINT chk_quantity_positive CHECK (quantity > 0) );

关键细节解析：

• ON DELETE CASCADE：删除客户时自动清理其订单，避免孤儿记录。但慎用！财务系统需保留历史订单，此时改用ON DELETE RESTRICT。
• INDEX idx_orders_customer_date：非主键索引，但customer_id是外键，order_date是常用查询条件，组合索引大幅提升SELECT * FROM orders WHERE customer_id=123 AND order_date>'2024-01-01'性能。
• CHECK (quantity > 0)：在数据库层强制业务规则，比应用层校验更可靠。

4.3 数据填充与冲突处理：INSERT ... ON CONFLICT的工业级用法

真实业务中，重复插入是常态（如支付回调重试、前端重复提交）。INSERT ... ON CONFLICT（PostgreSQL特有，MySQL用INSERT IGNORE或ON DUPLICATE KEY UPDATE）是救命稻草。

模拟场景：用户注册时，邮箱可能已存在，需更新最后登录时间而非报错。

-- 插入客户，邮箱冲突时更新last_login INSERT INTO customers (email, phone, created_at) VALUES ('alice@example.com', '13800138000', NOW()) ON CONFLICT (email) DO UPDATE SET phone = EXCLUDED.phone, last_login = NOW() RETURNING id, email;

EXCLUDED是PostgreSQL关键字，代表本次插入被拒绝的行。ON CONFLICT (email)指定冲突检测列（必须是唯一索引或主键列）。

高级技巧：UPSERT with JOIN订单创建时需关联客户ID，但客户可能尚未注册（如游客下单）。用CTE（Common Table Expression）原子化处理：

WITH new_customer AS ( INSERT INTO customers (email, created_at) VALUES ('guest@example.com', NOW()) ON CONFLICT (email) DO NOTHING RETURNING id ), customer_id AS ( SELECT id FROM new_customer UNION ALL SELECT id FROM customers WHERE email = 'guest@example.com' LIMIT 1 ) INSERT INTO orders (customer_id, order_date, total_amount) SELECT id, NOW(), 99.99 FROM customer_id;

这段代码确保：若邮箱不存在则新建客户，存在则复用，整个过程事务安全，无竞态条件。

4.4 查询优化实战：EXPLAIN ANALYZE解读主键索引效能

主键设计是否合理，EXPLAIN ANALYZE说了算。执行以下查询并分析：

EXPLAIN ANALYZE SELECT o.id, o.order_date, c.email, p.name FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE o.order_date >= '2024-07-01' AND c.email LIKE 'a%';

关键看输出：

• Index Scan using orders_pkey on orders o：主键索引被正确使用（好）；
• Index Scan using customers_pkey on customers c：c.id走主键索引（好）；
• Hash Join：order_items和products用哈希连接，因product_id是UUID主键，B+Tree索引不适合范围JOIN；
• Rows Removed by Filter: 12000：c.email LIKE 'a%'未走索引，全表扫描过滤（坏！需建函数索引）。

立即修复：

CREATE INDEX CONCURRENTLY idx_customers_email_prefix ON customers (email) WHERE email IS NOT NULL; -- 或更优：函数索引支持前缀查询 CREATE INDEX CONCURRENTLY idx_customers_email_lower ON customers (LOWER(email));

注意：CONCURRENTLY避免锁表，但建索引时间更长，且不能在事务块中执行。

5. 常见故障排查与避坑指南：十年踩坑总结的21条军规

5.1 主键相关故障速查表

5.2 必须遵守的21条主键军规（来自血泪教训）

1. 永远不要用TEXT或VARCHAR作主键：索引体积大、比较慢、易受字符集影响。某项目用VARCHAR(255)存API密钥作主键，索引膨胀至1.2GB，JOIN耗时从5ms升至800ms。
2. 复合主键列数≤2：三列以上复合键，外键引用、索引维护、ORM映射复杂度指数上升。我们规定：order_items用(order_id, product_id)，但order_shipments必须用代理键。
3. SERIAL序列必须监控：SELECT last_value FROM your_table_id_seq;每日巡检，剩余空间<10%时预警。用BIGSERIAL替代SERIAL，成本几乎为零。
4. UUID必须用v4，禁用v1：v1含时间戳和MAC地址，可被反向推算生成时间及机器信息，安全风险极高。
5. 主键列禁止UPDATE：即使ON UPDATE CASCADE可用，也禁用。某金融系统更新客户ID，导致12张关联表级联更新，锁表18分钟。
6. 外键必须建索引：PostgreSQL不自动为外键列建索引，DELETE父记录时会锁子表全表。CREATE INDEX idx_orders_customer_id ON orders(customer_id);是上线必检项。
7. NULL检查要双重保险：主键列设NOT NULL，但应用层插入前仍需校验。我们用JDBC的PreparedStatement.setNull()会静默失败，改用setObject(val, Types.VARCHAR)并判空。
8. 测试环境必须用生产数据量：本地用100行测试SERIAL没问题，生产1000万行时VACUUM频率、索引深度、缓冲区命中率全不同。我们用pg_dump --data-only --table=orders | head -1000000 > sample.sql生成百万级测试数据。
9. 主键命名统一用id：customer_id、order_id是外键列名，主键列名一律id。避免customer_no、cust_id等混乱命名。
10. 禁止在主键上用函数索引：CREATE INDEX idx ON t ((lower(id)))无效，主键必须是原始值。
11. ON DELETE SET NULL慎用：SET NULL需外键列允许NULL，但主键引用列不可NULL，逻辑矛盾。一律用CASCADE或RESTRICT。
12. 分区表主键必须含分区键：如按order_date分区，则主键必须是(id, order_date)，否则无法确定数据归属分区。
13. COPY导入时禁用主键检查：大数据量导入先SET session_replication_role = 'replica';，导入完SET session_replication_role = 'origin';，再VACUUM。
14. 主键索引禁止REINDEX CONCURRENTLY：会阻塞DML，用CREATE INDEX CONCURRENTLY建新索引，再DROP INDEX CONCURRENTLY旧索引。
15. pg_stat_all_indexes定期分析：SELECT * FROM pg_stat_all_indexes WHERE idx_scan < 100 AND schemaname='public';找出未使用的索引删除。
16. 主键变更必须停机：ALTER TABLE t DROP CONSTRAINT t_pkey; ALTER TABLE t ADD PRIMARY KEY (new_id);会锁表，安排在凌晨低峰期。
17. UUID生成必须用gen_random_uuid()：uuid_generate_v4()需CREATE EXTENSION，且某些云数据库不支持。
18. 禁止用CURRENT_TIMESTAMP作主键：精度不足（微秒级可能重复），且业务语义错误。
19. SERIAL初始值设为10000：避免ID=1,2,3等敏感值被恶意探测，CREATE SEQUENCE t_id_seq START 10000;。
20. 主键字段禁止DEFAULT值：SERIAL已隐含默认，显式DEFAULT nextval()冗余且易错。
21. 所有主键操作必须写进部署脚本：CREATE TABLE、ADD PRIMARY KEY、CREATE INDEX全部纳入Ansible或Flyway脚本，禁止手工执行。

5.3 一个真实故障的完整复盘：主键设计缺陷导致的雪崩

故障现象：某SaaS平台凌晨3点告警，orders表写入延迟从50ms飙升至12秒，API超时率92%。

根因分析：

• orders表主键为SERIAL，但分库分表中间件（ShardingSphere）未配置主键生成策略，各分片均用本地SERIAL，导致ID重复；
• 应用层捕获duplicate key异常后，进入无限重试循环；
• 重试请求持续涌入，数据库连接池耗尽，连锁拖垮customers、products表。

解决方案：

1. 紧急：修改中间件配置，主键生成策略切为UUID；
2. 临时：ALTER SEQUENCE orders_id_seq RESTART WITH 10000000;避免近期ID冲突；
3. 永久：orders表迁移至UUID主键，用pg_dump --inserts导出数据，sed替换INSERT INTO orders VALUES (123,为INSERT INTO orders VALUES ('a1b2...',，再导入；
4. 预防：所有新表主键默认UUID，SERIAL仅用于内部计数表。

这次故障让我们彻底放弃“主键只是技术细节”的幻想。现在每个新表设计评审，主键方案是第一个被拷问的问题。

6. 高级场景应对：分布式、分库分表、遗留系统迁移的主键策略

6.1 分布式系统：UUID不是唯一解，Snowflake才是工业标准

UUID v4虽全球唯一，但16字节体积大、无序导致索引碎片化。Twitter的Snowflake算法（64位整数）成为更优解：时间戳（41bit）+ 机器ID（10bit）+ 序列号（12bit），生成趋势递增ID，兼顾唯一性、有序性、紧凑性。

PostgreSQL无原生Snowflake支持，但我们用PL/pgSQL实现轻量版：

CREATE OR REPLACE FUNCTION snowflake_id() RETURNS BIGINT AS $$ DECLARE epoch BIGINT := 1609459200000; -- 2021-01-01 00:00:00 UTC in ms timestamp_ms BIGINT; machine_id INT := 1; -- 部署时配置 sequence INT := 0; last_timestamp_ms BIGINT := 0; BEGIN timestamp_ms := FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000); IF timestamp_ms = last_timestamp_ms THEN sequence := sequence + 1; ELSE sequence := 0; last_timestamp_ms := timestamp_ms; END IF; RETURN ((timestamp_ms - epoch) << 22) | (machine_id << 12) | (sequence); END; $$ LANGUAGE plpgsql; -- 使用 CREATE TABLE distributed_orders ( id BIGINT PRIMARY KEY DEFAULT snowflake_id(), ... );

优势：ID长度减半（8字节vs16字节），索引体积降60%，且趋势递增，B+Tree分裂更少，写入吞吐提升2.3倍。

6.2 分库分表：主键必须全局唯一，但索引要本地化

分库分表（如按customer_id % 4分4库）时，主键设计分三层：

1. 逻辑主键（Logical PK）：业务层可见的唯一ID，如order_id，必须全局唯一（用Snowflake或UUID）；
2. 物理主键（Physical PK）：数据库表的id SERIAL，仅在单库内唯一，用于加速本地查询；
3. 分片键（Sharding Key）：customer_id，决定数据路由。

-- 物理表结构（每个分片相同） CREATE TABLE orders_0 ( id SERIAL PRIMARY KEY, -- 物理主键，本地唯一 order_id BIGINT NOT NULL, -- 逻辑主键，全局唯一 customer_id BIGINT NOT NULL, ... INDEX idx_order_id (order_id), -- 逻辑主键索引，支持全局查询 INDEX idx_customer_id (customer_id) -- 分片键索引，支持路由 );

查询时：SELECT * FROM orders WHERE order_id = 123456789→ 中间件根据order_id路由到具体分片 → 在该分片查idx_order_id索引。

6.3 遗留系统迁移：双主键并行，零停机切换

老系统用VARCHAR(50)自然键（如CUST-2024-0001），新系统需BIGSERIAL。强行切换风险高，我们采用三阶段迁移：

阶段1：双键共存

ALTER TABLE customers ADD COLUMN new_id BIGSERIAL; UPDATE customers SET new_id = nextval('customers_new_id_seq') WHERE new_id IS NULL; ALTER TABLE customers ALTER COLUMN new_id SET NOT NULL; -- 新应用写`new_id`，老应用仍写`old_id`

阶段2：双向同步

• 写new_id时，触发器同步更新old_id生成逻辑（'CUST-' || EXTRACT(YEAR FROM NOW()) || '-' || LPAD(new_id::TEXT, 4, '0')）；
• 写old_id时，触发器解析并填充new_id（需幂等）。

阶段3：灰度切换

• 80%流量切新键，20%留老键；
• 监控new_id和old_id一致性（SELECT COUNT(*) FROM customers WHERE old_id != generate_old_id(new_id)）；
• 一致性达100%后，停用老键，删除old_id列。

整个过程72小时完成，零用户感知。

7. 性能压测与监控：主键设计的终极验证

主键设计是否达标，必须用真实数据压测。我们用pgbench模拟高并发场景：

# 准备数据：100万客户，1000万订单 pgbench -i -s 100 pk_tutorial # 压测：混合读写（90%查询，10%插入） pgbench -c 50 -j 4 -T 300 -P 10 \ -f "SELECT * FROM orders WHERE id = random()*10000000;" \ -f "INSERT INTO orders (customer_id, order_date, total_amount) VALUES (random()*1000000, NOW(), random()*1000);" \ pk_tutorial

关键指标阈值：

• tpmC（每分钟事务数）≥ 12000；
• latency average（平均延迟）≤ 15ms；
• pg_stat_database.blks_read/blks_hit缓存命中率 ≥ 99.5%；
• pg_stat_all_indexes.idx_scan主键索引扫描次数 ≥ 总查询数的95%。

生产监控SQL（加入Zabbix或Prometheus）：

-- 主键索引使用率 SELECT schemaname, tablename, indexname, idx_scan, (idx_scan * 100.0 / (SELECT SUM(idx_scan) FROM pg_stat_all_indexes)) AS pct_use FROM pg_stat_all_indexes WHERE indexname ~ '_pkey$' ORDER BY idx_scan DESC LIMIT 5; -- 索引膨胀预警 SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, bloat_ratio FROM ( SELECT schemaname, tablename, indexname, indexrelid, ROUND(100.0 * (pg_total_relation_size(indexrelid) - pg_relation_size(indexrelid)) / pg_total_relation_size(indexrelid), 2) AS bloat_ratio FROM pg_stat_all_indexes WHERE indexname ~ '_pkey$' ) t WHERE bloat_ratio > 30;

当主键索引bloat_ratio > 30%，说明索引页碎片严重，需`VACU