“黑名单”这三个字看着简单，做起来却比想象中麻烦得多。最近我刚收拾完一个线上事故，某个直播间的风控接口被刷爆，后台一查，规则封禁的 IP 和账号都老老实实落在 MySQL 里，但查询走错了索引，本来应该毫秒级的判断接口被拖成了几百毫秒，最后把整个数据库连接数打满。这事让我很受触动，因为自己做过的“MySQL 黑名单”方案不止一次被业务方夸过得去，可细节上一放松，照样出大问题。这篇就把完整套路写出来。
这个内容适合正在做用户黑名单、IP 风控、内容关键词过滤的后端同学参考。不管黑名单量是几千条还是上百万条，只要用的是 MySQL，表结构、索引、过期策略、缓存衔接这套链路都可以直接抄作业。我尽量把踩过的坑也一并说出来，尤其是那些在本地开发环境根本不会暴露、一上生产就翻车的问题。
1. 黑名单系统设计思路与场景定位
1.1 先想清楚：你拦截的到底是什么
黑名单不是一个表就能套完所有业务的。做设计的第一步，是把拦截维度拆清楚，再决定存储结构。我见过太多团队上来就建一张blacklist表，里面塞满各种类型的数据，最后查询条件写得像拼凑出来的杂烩，索引也建得稀烂。
常见的黑名单维度至少有这些：
- 用户维度：用户 ID、手机号、邮箱、身份证号。用于封禁账号、限制下单、禁止发言。
- 网络维度：IP、设备指纹、MAC。用于防爬、防刷、限制异常登录。
- 内容维度：敏感词、违规图片指纹、恶意 URL 域名。用于评论审核、昵称过滤、消息拦截。
这些维度虽然都叫黑名单，但业务语意差得很远。用户 ID 是精确匹配，关键词可能需要片段匹配，IP 要处理 CIDR 网段还得分 v4/v6。如果全塞一张表，用字符串字段硬扛，实验时还挺爽，一旦数据量大或者并发上来，查询就会变成噩梦。所以我一般按维度拆表，user_block、ip_block、keyword_block各管各的，逻辑清晰，索引也好设计。毕竟 MySQL 最喜欢的就是业务层把数据分干净，再给它一个清晰的等值查询条件。
1.2 为什么用 MySQL 而不是纯 Redis
很多同学一听到黑名单就反射性想到 Redis，因为判断黑名单本质上是“查一个 key 存不存在”，Redis 的 set 结构做这事儿确实快。但实际项目往往没这么简单。
Redis 的硬伤是持久化和审计。线上封禁一个用户，业务方一定会问：谁封的？什么时候封的？理由是什么？到期了没有？Redis 虽然也能存这些字段，但真正要按封禁原因拉数据、统计封禁趋势、导出发送给风控团队的时候，Redis 的查询能力根本不够看。更别提 Redis 发生内存淘汰或者主从切换时，数据完整性不好保障。
MySQL 的定位不是一个纯高速缓存，而是黑名单的权威数据源。你把封禁记录当作一条有状态的业务数据，它天然需要事务、查询、统计、审计、备份恢复这些能力，这些都是关系库的看家本领。等到判断链路真的对延迟有苛刻要求，再加一层 Redis 做预热缓存，MySQL 负责兜底和回源，这样两边都不委屈。
1.3 整体架构比例
我做过的最稳的组合是 3 层：MySQL 存全量数据和审计；Redis 存热点拦截集合；应用本地再加一层短时缓存承接极端峰值。
第一层 MySQL 是唯一的真源，所有的封禁和解封都走这里。第二层 Redis 用 set 结构缓存“生效中”的黑名单 key，拦截判断绝大多数直接打 Redis，O(1) 完成。第三层本地缓存用于那种单机热点极高、又允许几秒内延迟感知的场景，比如同一个网关节点频繁收到同一批异常 IP 的请求，本地缓存能把 Redis 的 QPS 都省下来。
这套架构的好处是每层的职责非常单一，不会出现“把整个黑名单加载到内存里”的奇怪玩法。我在另一个项目里见过有人为了追求性能，启动时把整张表 load 到应用内存，结果每次封禁都要通知所有节点刷新，稍有不慎就是脏数据。MySQL 做底层，恰恰给了你一个安全的回源点，真出问题大不了慢一点，不至于错杀或者漏杀。
2. 核心表结构与索引设计
2.1 通用黑名单表应该怎么建
统一模板可以这样写，每个维度扩展自己的字段：
CREATE TABLE user_block ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL COMMENT '被封禁用户ID', block_type TINYINT NOT NULL DEFAULT 1 COMMENT '封禁类型：1-发言 2-登录 3-下单', reason VARCHAR(255) DEFAULT NULL COMMENT '封禁原因', source VARCHAR(64) DEFAULT NULL COMMENT '封禁来源：运营/系统', operator VARCHAR(64) DEFAULT NULL COMMENT '操作人', expire_at DATETIME DEFAULT NULL COMMENT '封禁到期时间，NULL为永久', status TINYINT NOT NULL DEFAULT 1 COMMENT '1生效 0失效', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_user_type (user_id, block_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;这张表的核心思路是把“封禁对象”和“封禁策略”分开。user_id 是对象，block_type 是策略，同一个用户可以因为发言违规被封，同时因为登录异常被封，两者互不干扰。如果只用一个 user_id 做主键，你反而失去这种灵活性。
我再解释几个容易被忽略的字段：
expire_at是到期时间，NULL 表示永久封禁。为什么要单独给一列？因为业务上“永久”和“限时”是两种完全不同的心智，字段空值比塞一个 9999-12-31 更直观，查询条件写expire_at IS NULL OR expire_at > NOW()也够符合直觉。source用来区分是运营后台手动封的还是系统规则自动封的。后面出问题时，这是追责和回滚的第一依据。status则是软删除和恢复的开关。你解封一个用户后，千万别物理 DELETE 这条记录，否则审计链就断了。更建议的做法是把 status 改成 0，保留历史，后续出纠纷还能捞出来看。
2.2 索引怎么加才不会翻车
索引设计是黑名单表最重要的一环，大多数线上事故都出在这。先看上面的表，我刻意放了UNIQUE KEY uk_user_type (user_id, block_type, status)，这个复合唯一索引有三个作用：
- 防止同一条封禁规则重复插入。业务方手滑点两下，或者接口被重试，都不会产生全等记录。
- 拦截查询全命中。判断某个用户是否被禁言，走
WHERE user_id = ? AND block_type = ? AND status = 1，直接在唯一索引上等值击中，回表次数几乎为零。 - 让 InnoDB 的二级索引紧凑，不会因为垃圾数据膨胀。
真正容易翻车的是expire_at。有人习惯给 expire_at 单独建索引，然后查询写WHERE expire_at > NOW()，看起来合理，实际上就是一个范围扫描加回表。黑名单表如果到了百万量级，这个查询慢得让你怀疑人生。
我的做法是区分场景。如果是业务判断“当前时间是否在封禁期内”，更应该直接写成：
WHERE user_id = ? AND block_type = ? AND status = 1 AND (expire_at IS NULL OR expire_at > NOW())其中 user_id、block_type、status 走了前面那个复合唯一索引，expire_at的条件只是在返回的少数几行里做过滤，根本不需要再建索引。至于“捞取所有今天过期的记录”这种维护型任务，量级不大，就让它扫描吧，反正一天一次。为高频业务判断服务的是前缀等值索引，不是范围索引，这个思路记住就不会跑偏。
2.3 IP 黑名单的存法
IP 黑名单是另一个重灾区。很多人把 IP 直接存 VARCHAR，然后查询WHERE ip = '192.168.1.1'，本地几百条数据毫无感觉。可一旦你是封禁一个 C 段，比如192.168.1.0/24，等于要判断目标 IP 落在某个网段里，VARCHAR 存起来就没法愉快地走索引了。
IPv4 有个标准做法：用INET_ATON()把 IP 转成整数存 BIGINT，查询时也用整数比较。比如：
-- 用整数存起始和结束 CREATE TABLE ip_block ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ip_start BIGINT UNSIGNED NOT NULL COMMENT '起始IP整数', ip_end BIGINT UNSIGNED NOT NULL COMMENT '结束IP整数', ... KEY idx_ip_range (ip_start, ip_end) );判断某个 IP 是否被封禁，就可以在应用层先把用户请求的 IPINET_ATON转成整数，然后：
SELECT id FROM ip_block WHERE ip_start <= ? AND ip_end >= ? LIMIT 1;这个查询没法像等值那样完美命中索引，但在合理的数据量和网段数量下，配合范围索引和覆盖索引，性能是可以控制的。比字符串 LIKE 或者WHERE INET_NTOA(ip_int)这种写法靠谱一万倍。
IPv6 就更麻烦一点，官方原生的INET6_ATON返回的是二进制，更适合VARBINARY(16)存储。如果业务里主要是 v4 和 v6 混用，我建议干脆表里加一个ip_version字段，分开处理，别在一个字段里辣眼睛。
3. 黑名单业务的落地实现与优化
3.1 添加封禁的 SQL 写法
写入封禁的时候，最容易踩的坑是重复插入。前端的防重提交只是第一道防线，后端接口被脚本重放的时候还得靠数据库本身约束。
我常用的写法是INSERT ... ON DUPLICATE KEY UPDATE：
INSERT INTO user_block (user_id, block_type, reason, source, operator, expire_at, status) VALUES (10001, 1, '刷屏禁言30天', 'system', 'system', NOW() + INTERVAL 30 DAY, 1) ON DUPLICATE KEY UPDATE reason = VALUES(reason), source = VALUES(source), expire_at = VALUES(expire_at), status = 1, updated_at = NOW();这个写法的意义在于：如果同一条封禁规则已经存在，这次操作就自动更新原因和到期时间，并且把 status 重新置为 1。要是之前已经解封了，这次重新封禁就能把记录捞活，不用新增一行。
有同学会问我为什么不用INSERT IGNORE。INSERT IGNORE遇到唯一键冲突直接吞掉，副作用是它也会吞掉其他异常，比如字段长度超长、非法值，排错的时候特别难受。所以我建议用ON DUPLICATE KEY UPDATE，至少能让你明确知道这次操作到底影响了几行。
3.2 高效拦截查询
业务中最核心的判断语句，要保证极端简单。拿“检查用户 10001 是否被禁言”举例：
SELECT EXISTS( SELECT 1 FROM user_block WHERE user_id = 10001 AND block_type = 1 AND status = 1 AND (expire_at IS NULL OR expire_at > NOW()) ) AS blocked;这种写法的特点是：查询结果只有 0 或 1，传输数据量少；能用上复合索引的等值部分，InnoDB 在二级索引上扫几行就能返回，效率很高。应用层拿这个EXISTS结果直接做布尔判断，不用再把一条完整的封禁记录拉出来解析。
另一个容易出问题的地方是 JOIN。很多人把黑名单判断写进主业务 SQL，用JOIN user_block b ON b.user_id = u.id，然后过滤掉被拉黑的用户。这在低并发下看起来没什么问题，一旦主表是大结果集，JOIN 会让优化器做一大堆半连接判断，扩散到整个查询计划。我更推荐的方式是把黑名单判断独立成一个接口调用，或者在主查询里改用NOT EXISTS加子查询，让优化器能更精确地选择驱动表。
不过也不是说 JOIN 完全不能用。如果你的主查询本来就是以小结果集为驱动，比如只查最近 20 条订单，那 JOIN 一把黑名单表也没有不可承受的成本。核心原则是别拿大表和黑名单全量做笛卡尔式碰撞。
3.3 过期与解封处理
黑名单过期需要单独处理，不能全依赖 SQL 里的expire_at > NOW()硬扛。因为你那条封禁记录放在表里，会一直占据索引空间，过期之后如果不处理，表会越堆越大，唯一索引里存量垃圾过多还会影响写入性能。
我的建议是搞一个定时任务，每天或者每几个小时扫一次。这个任务做两件事：
- 把
expire_at < NOW()且status = 1的记录批量更新为status = 0。 - 如果业务上允许物理清理，再把这些过期记录转存入历史表，主表只保留最近一段时间的有效和近期待清理数据。
转存历史表挺重要，我甚至建议把过期记录同步写到一张独立的历史黑名单表，并加上原 ID 字段。这样既有审计证据，又不影响在线表的索引密度。数据量到了千万级以后，这种“在线表 + 历史表”的模式会让你运维起来舒服很多。
3.4 高并发下的缓存与持久层配合
虽然 MySQL 能扛住一部分判断压力，但纯靠它抗高并发属于用错了工具。黑名单的特点是读多写少，而且热点集中，比如某个恶意 IP 在短时间内反复请求，每次请求都穿过 MySQL 查询一遍，确实浪费。
我常用的设计是：MySQL 作为数据源，Redis 作为第一层读缓存。封禁和解封时，先写 MySQL，再把操作同步到 Redis。拦截时，应用先查 Redis 的 set，如果 key 存在直接拦截；如果 key 不存在，提供一个容忍时间窗口内的 MySQL 回源逻辑。
Redis 的数据结构不需要太复杂，直接搞几个 set，例如blacklist:user:12345、blacklist:ip:192.168.1.1，每个集合只是存在与否。判断时直接SISMEMBER，效率高到可怕。但要注意，千万别把 Redis 当成永久存储，Redis 宕机或者崩溃之后，启动时一定记得从 MySQL 重新构建缓存，这段逻辑需要定时任务或者懒加载机制兜底。
我还建议应用层做一层短时本地缓存，TTL 设置在 5 到 10 秒。这种做法的目的不是为了减少 Redis 的请求，而是为了让单个节点在遭遇极端流量洪峰时，不因为 Redis 抖动而全量打到 MySQL。本地缓存带来的副作用是解封后生效有延迟，但黑名单业务一般都能接受几秒的延迟，收益明显大于风险。
4. 常见问题排查与避坑实录
4.1 索引失效的常见场景
我先说最坑的一点：对索引列做函数运算。很多同学写判断 IP 的时候，记得存成了整数，但查询时写：
WHERE INET_NTOA(ip_int) = '192.168.1.1'这样一写，索引直接废掉，因为索引中存的是整数，而查询要在每一行的 ip_int 上执行函数转成字符串，再和右边比较。这等于强迫 MySQL 做全表扫描，没有任何优化余地。正确姿势是应用层把 IP 转成整数再进 SQL。
另一个常见问题是隐式类型转换。如果user_block.user_id是 BIGINT，而你的业务代码里把它当字符串拼进 SQL，比如WHERE user_id = '12345'，MySQL 大多数时候还能隐式转换，但如果反过来，字段是 VARCHAR，条件传了整数，索引就直接失效了。我排查慢查询时，第一步永远是用EXPLAIN看type列，如果是ALL，八成就是类型或者函数问题。
4.2 字符集、大小写和空白
黑名单匹配最容易翻车的是字符串边界不一致，尤其是手机号、邮箱、用户名这类。MySQL 在 utf8mb4 和默认排序规则下，很多字符串等值比较是不区分大小写的，比如'abc@example.com'和'ABC@example.com'会被当成同一个。这对于封禁邮箱来说可能是好事，但对封禁用户名来说，可能会误伤本来不该封禁的记录。
解决方式是明确业务需求后设置字段的排序规则。如果希望大小写敏感，建表时指定COLLATE utf8mb4_bin；如果希望统一忽略大小写，那应用层写入时先做归一化，比如一律转小写，查询时也一律转小写。两边的处理要完全一致，否则就会出现“查询查不到，但库里明明有”的诡异现场。
空白字符同样是个大坑。运营从 Excel 复制一串手机号过来，很可能会带上换行符或者看不见的空格，12345678901和12345678901看起来一样，存进 MySQL 里则是四条不同的记录。我的建议是写一个应用层的清洗器，入库前做 trim、去全角空格、去零宽字符，黑名单数据尤其不能脏，一脏就封不住人。
4.3 批量导入和事务一致性
黑名单初始化和大促前临时加名单是高频操作。手工一条一条 INSERT 不现实，一般会用到批量导入。我踩过的坑是：大批量同时插入时，没有分批提交事务，导致 InnoDB 的 undo log 和 redo log 暴涨，直接把磁盘 IO 打满。
靠谱的做法是每 500 到 1000 条提交一次事务。另外可以用LOAD DATA INFILE，对 MySQL 来说这是导入文本数据的最快路径，比循环 INSERT 在性能上强了不止一个量级。但用LOAD DATA之前，一定要先清洗文本文件的编码、空行、分隔符，尤其不能有 BOM 头，否则第一列数据会带一个看不见的字符。
还有个隐蔽的坑：批量导入触发了唯一索引冲突。你以为是脏数据，想跳过，于是用INSERT IGNORE，结果 MySQL 连异常都一起吞了。我更推荐把导入数据先加载进一个临时表，再通过INSERT ... SELECT ... WHERE NOT EXISTS把合规数据筛到正式表，最后把冲突的数据导出来给业务方核对。虽然步骤多一点，但不会出那种“明明导了却说库里没有”的死无对证。
4.4 误封、误伤和应急恢复
黑名单业务最敏感的永远不是技术，而是误伤。把正常用户当成机器人封了，投诉立刻就来。我做过一个风控项目，规则没写好，把某个省的大量正常用户给封了。这时候如果要手动解封几千个用户，一条条跑 SQL 不现实，效率太低。
我的应急方案是预先做好“名单回滚”能力。每一条封禁记录都带source和created_at，批量解封直接采用：
UPDATE user_block SET status = 0, updated_at = NOW() WHERE source = 'auto_rule_20240115' AND status = 1;这种按来源批量解封的方式特别管用，只要封禁的时候标记了来源，解封就不需要枚举 ID。如果连来源都没有，那只能捞时间窗口内创建的所有记录去筛，效率就低很多了。所以说建表的时候给source字段留一个位置，不是多此一举，而是给自己留一条快速止血的路。
基于这个经历，我强烈建议所有黑名单方案都加一个“白名单优先”的校验逻辑。也就是在拦截判断之前，先查一个白名单集合，如果命中了，直接放行。这是一个保险丝设计，当规则产生误伤人潮时，运营只要把 VIP 用户白名单加进去，就能先保命，再慢慢修规则。这个设计我可以说是最值得抄走的一条经验。
最后再说几句实在的
黑名单本质上是“宁可错杀”和“宁可放过”之间的博弈，MySQL 只是负责把这个博弈落地成一行行可审计的记录。我从一开始只会在 Redis 里塞 key，到现在认认真真给黑名单表设计唯一索引、做历史表归档、接多层缓存，中间踩过的坑全是生产环境教我的。每次上线新的封禁规则，我都建议先小范围灰度，同时准备好回滚脚本，别等到事故出来再拍脑袋。
如果你现在的项目里黑名单还是用一个简单的表、几个幼稚的查询去硬顶，这个周末不妨参照上面的方案重构一下。即使数据量还没那么大，把索引和缓存设计先对齐，后续扩展就能省掉一次伤筋动骨。希望这篇能帮你把 MySQL 黑名单做成那个真正让人放心的底层底盘。
