“黑名单”这三个字看着简单做起来却比想象中麻烦得多。最近我刚收拾完一个线上事故某个直播间的风控接口被刷爆后台一查规则封禁的 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拦截判断绝大多数直接打 RedisO(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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张表的核心思路是把“封禁对象”和“封禁策略”分开。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 UPDATEINSERT 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 12345MySQL 大多数时候还能隐式转换但如果反过来字段是 VARCHAR条件传了整数索引就直接失效了。我排查慢查询时第一步永远是用EXPLAIN看type列如果是ALL八成就是类型或者函数问题。4.2 字符集、大小写和空白黑名单匹配最容易翻车的是字符串边界不一致尤其是手机号、邮箱、用户名这类。MySQL 在 utf8mb4 和默认排序规则下很多字符串等值比较是不区分大小写的比如abcexample.com和ABCexample.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 黑名单做成那个真正让人放心的底层底盘。