表设计里的三个细节：逻辑删除的唯一约束、IP 地址存储、热点行更新
表结构设计的大方向通常不会出错，出问题的往往是细节：唯一索引看起来建了却没生效，IP 地址按网段查询少了三成数据，一个爆款商品的库存更新拖住了整个库。这三个问题都能在 MySQL 上几行 SQL 复现。
本文所有实验都在 MySQL 8.4.11 上完成，热点行压测使用 JDBC 直连，脚本与原始输出见文末配套实验。
一、先说结论
- 唯一索引里包含可空列，逻辑删除后的唯一约束会失效。 NULL 与任何值都不相等，两条
deleted_at IS NULL的记录可以同时插入成功。「未删除」要用一个确定的值表示。 - 两个可靠的写法：删除时把
deleted_id从 0 改为自身主键；或者用 MySQL 8.0.13 起支持的函数索引，只对未删除的行建立唯一约束。 - IP 地址用
VARBINARY(16)配合INET6_ATON存储，IPv4 和 IPv6 统一处理，范围查询正确。字符串存储时按BETWEEN查网段，实测漏掉了 32% 的数据。 - 热点行的吞吐被行锁串行化。 实测 64 个线程并发更新同一行，吞吐只有约 2,200 TPS，几乎每次更新都要等锁；分散到 1000 行时达到 16,420 TPS。
- 热点行的解法是减少对同一行的并发写：拆分成多个桶、在数据库之前合并请求、把扣减前移到缓存或队列。
二、逻辑删除与唯一约束
2.1 场景
用户开通服务后可以退订，退订后还可以再次开通。要求：同一个用户、同一个产品，同一时刻只能有一条有效记录；历史记录保留，不做物理删除。
2.2 一个常见的错误写法
CREATE TABLE service_record_a (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_code VARCHAR(32) NOT NULL,
deleted_at DATETIME NULL, -- NULL 表示未删除
UNIQUE KEY uk_user_product (user_id, product_code, deleted_at)
);
INSERT INTO service_record_a (user_id, product_code) VALUES (1001, 'VIP');
INSERT INTO service_record_a (user_id, product_code) VALUES (1001, 'VIP'); -- 没有报错+-----------------------------+-------------+
| test | active_rows |
+-----------------------------+-------------+
| A: two active rows allowed? | 2 |
+-----------------------------+-------------+MySQL 的唯一索引允许多个 NULL 值，这是 SQL 标准的行为。带着删除时间的唯一索引，只能防止「同一秒删除两次」，挡不住「两条未删除的记录」。
2.3 写法一：删除标记用确定的值
CREATE TABLE service_record_b (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_code VARCHAR(32) NOT NULL,
deleted_id BIGINT NOT NULL DEFAULT 0, -- 0 表示未删除
UNIQUE KEY uk_user_product (user_id, product_code, deleted_id)
);
-- 退订：把 deleted_id 改成这一行自己的主键，保证已删除的记录互不冲突
UPDATE service_record_b SET deleted_id = id
WHERE user_id = 1001 AND product_code = 'VIP' AND deleted_id = 0;实测开通、退订、再开通、再退订、再开通之后：
| id | deleted_id |
| 1 | 1 | 已删除
| 2 | 2 | 已删除
| 3 | 0 | 有效此时再插入一条有效记录会报 Duplicate entry '1001-VIP-0'。需要记录删除时间时，另加一列 deleted_at，但不放进唯一索引。
2.4 写法二：函数索引只约束有效记录
CREATE TABLE service_record_c (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_code VARCHAR(32) NOT NULL,
deleted_at DATETIME NULL,
UNIQUE KEY uk_active ((IF(deleted_at IS NULL, CONCAT(user_id, ':', product_code), NULL)))
);已删除的行，索引表达式的结果是 NULL，互不冲突；有效行的表达式结果是 1001:VIP，只能有一条。实测再插入有效记录时报错 Duplicate entry '1001:VIP' for key 'service_record_c.uk_active'。
两种写法的取舍：
写法一：deleted_id | 写法二：函数索引 | |
|---|---|---|
| 支持版本 | 所有版本 | MySQL 8.0.13 起 |
| 删除操作 | 需要写入自身主键 | 只写 deleted_at |
| 可读性 | 需要约定 0 的含义 | 约束写在索引定义里，意图清楚 |
| ORM 适配 | 容易，普通字段 | 部分 ORM 或迁移工具对函数索引支持不好 |
| 迁移到其他数据库 | 通用 | 需要改写（如 PostgreSQL 用部分索引） |
2.5 另外两种思路
- 有效记录与历史记录分表：主表只存有效记录，唯一索引就是
(user_id, product_code)；退订时在同一事务里把记录移到历史表。查询有效记录最简单，代价是删除要写两张表。 - 只保留一条记录，状态字段加流水表：主表每个用户每个产品只有一行，用状态字段表示开通或退订，操作历史写入流水表。适合状态频繁切换的场景。
无论哪种写法，唯一约束都必须落在数据库上。应用层「先查一下没有再插入」在并发下一定会出现重复。
三、IP 地址用什么类型存
3.1 MySQL 提供的转换函数
SELECT INET_ATON('192.0.2.235'); -- 3221226219（IPv4 转整数）
SELECT HEX(INET6_ATON('192.0.2.235')); -- C00002EB，4 字节
SELECT HEX(INET6_ATON('2001:db8::1')); -- 20010DB8000000000000000000000001，16 字节
SELECT INET6_NTOA(INET6_ATON('2001:0db8:0000::0001')); -- 2001:db8::1（顺便完成了规范化）INET6_ATON 对 IPv4 返回 4 字节，对 IPv6 返回 16 字节，所以一列 VARBINARY(16) 就能同时存两种地址。
3.2 三种存储方式对比
| 方式 | 类型 | IPv4 空间 | IPv6 | 范围查询 | 可读性 |
|---|---|---|---|---|---|
| 字符串 | VARCHAR(45) | 7—15 字节 | 支持，但同一地址有多种写法 | 结果可能错误 | 直接可读 |
| 整数 | INT UNSIGNED | 4 字节 | 不支持 | 正确 | 需要 INET_NTOA |
| 二进制 | VARBINARY(16) | 4 字节 | 支持，16 字节 | 正确 | 需要 INET6_NTOA |
3.3 字符串的范围查询为什么会错
实测 100 万个 10.0.0.0/8 内的 IPv4 地址（由公式生成、互不相同）分别存为字符串和二进制，查询 10.1.0.0/16 网段：
-- 二进制：3,916 行，正确
SELECT COUNT(*) FROM access_log_bin
WHERE ip BETWEEN INET6_ATON('10.1.0.0') AND INET6_ATON('10.1.255.255');
-- 字符串 BETWEEN：2,672 行，少了 1,244 行
SELECT COUNT(*) FROM access_log_str WHERE ip BETWEEN '10.1.0.0' AND '10.1.255.255';字符串按字符逐位比较，'10.1.100.1' 和 '10.1.99.1' 比到第 6 个字符时是 '1' < '9'，前者排在前面，与数值大小相反。LIKE '10.1.%' 能查对 /16 这种按点号切分的网段，但 /20、/28 这类网段就无法表达了。
空间上，同样 100 万行，两个表重建后，二进制列上的索引占 16.5MB，字符串列上的索引占 26.6MB。
3.4 建议
- 需要按网段查询、做黑白名单匹配、统计来源分布：用
VARBINARY(16)+INET6_ATON，查询时计算网段的起止地址。 - 只用于展示和审计日志、从不按范围查询：字符串也可以接受，写入前统一规范化格式。
- 注意 IPv4 映射地址：
::ffff:192.0.2.235与192.0.2.235转换后的字节不同，应用层入库前要统一成一种形式，可以用IS_IPV4_MAPPED()判断。 - Java 中可以用
InetAddress.getByName(ip).getAddress()得到同样的字节数组，直接作为参数写入。
四、热点行更新
4.1 问题是什么
大促时，某个爆款商品的库存行每秒被扣减几千次：
UPDATE sku_stock SET qty = qty - 1 WHERE sku_id = ? AND qty > 0;每条 UPDATE 都要持有这一行的排他锁直到事务提交，而提交要等 Redo 和 Binlog 刷盘。对同一行的更新，只能一个接一个地执行。
4.2 实测
1000 个 SKU，分别让所有线程更新同一行、分散更新 1000 行，每个线程执行 400 次更新，自动提交：
| 线程数 | 同一行 TPS | 同一行最大延迟 | 同一行锁等待次数 | 分散更新 TPS | 分散更新锁等待次数 |
|---|---|---|---|---|---|
| 1 | 1,793 | 3.1ms | 0 | 1,980 | 0 |
| 16 | 2,186 | 26.5ms | 6,399 / 6,400 | 12,144 | 404 |
| 64 | 2,216 | 51.7ms | 25,599 / 25,600 | 16,420 | 2,691 |
「同一行」这一列说明了一切：线程从 1 个加到 64 个，吞吐只增长了约四分之一，除第一次外每一次更新都经历了锁等待，最大延迟随并发上升。锁等待的线程还一直占着数据库连接，连接池很快会被耗尽，进而拖累其他业务。
4.3 缓解方式
拆分热点行（分桶）。把一个 SKU 的库存拆成 N 行，每次随机或轮询选一个桶扣减：
CREATE TABLE sku_stock_bucket (
sku_id BIGINT NOT NULL,
bucket_no TINYINT NOT NULL,
qty INT NOT NULL,
PRIMARY KEY (sku_id, bucket_no)
);
-- 扣减时随机选一个桶；某个桶扣完后换下一个桶重试
UPDATE sku_stock_bucket SET qty = qty - 1 WHERE sku_id = ? AND bucket_no = ? AND qty > 0;实测 64 个线程、每次随机选桶时，4 个桶 2,531 TPS，16 个桶 5,300 TPS，分别是单行的 1.1 倍和 2.4 倍：随机选桶仍会让多个线程撞在同一个桶上，桶数要明显多于并发线程数才有效果。代价是：某个桶扣完而其他桶还有库存时需要重试；总库存要汇总各桶；桶数越多，「最后几件」的扣减越容易反复失败。
在数据库之前合并请求。应用层把短时间内对同一 SKU 的扣减请求攒成一批，一次性 qty = qty - n，让数据库面对的写入次数变少。适合对单个请求的实时性要求不高的场景。
把扣减前移到缓存或队列。先在 Redis 中用原子操作扣减库存，成功后通过消息异步写入数据库；数据库只承担最终落库。这种做法的吞吐最高，但要处理缓存与数据库的一致性、消息丢失与重复，以及对账，见 订单、库存与数据一致性。
缩短事务。热点行所在的事务里不要做远程调用、不要更新其他行，扣减语句尽量是事务里的最后一步，让锁持有的时间只包含这一次更新和提交。
五、常见误区
- 「唯一索引里加上
deleted_at就能支持逻辑删除」：NULL 不参与唯一性比较，有效记录可以重复。 - 「应用层先查后插就能保证唯一」：并发请求会同时查到「不存在」，唯一约束必须由数据库保证。
- 「IP 用字符串存最方便」：展示方便，但范围查询结果可能错误，IPv6 还有多种写法。
- 「热点行加机器就能扛住」：同一行的更新由行锁串行化，增加应用实例只会让锁等待更严重。
- 「分桶越多越好」：桶越多，库存越分散，末尾库存的扣减越容易反复失败。
小结
这三个细节背后是同一个原则：让数据库的约束和数据结构真正表达业务规则。唯一约束要避开 NULL，IP 地址要用能正确排序的类型，热点写入要避免让所有请求争抢同一行。它们都能在上线前用几行 SQL 验证，值得在设计评审时顺手跑一遍。
配套实验
- codesphere-labs/storage/mysql-schema-design：逻辑删除的三种唯一约束写法、100 万个 IP 地址的网段查询与索引大小、热点行与分桶的吞吐和锁等待（验证记录）
参考资料