MySQL如何高效实现大表指定列的敏感词检测与标记？

核心性能瓶颈说明 #

现有方案的性能问题根源在于：全表扫描4亿条记录+单条SQL正则匹配2000个敏感词，单条记录匹配耗时呈线性增长，同时全量更新会触发长事务、锁表、磁盘IO打满等问题。 #

优化方案 #

一、最高优先级：增量扫描替代每日全量扫描 #

改动最小，收益最高，可直接将每日扫描量级从4亿降到单日新增量级：

• 在comments表新增last_scan_time datetime类型字段，默认值设为'1970-01-01 00:00:00'
• 每日任务仅扫描created_date > 上次任务执行时间的新增评论，已经标记过违规、以及历史扫描过的评论直接跳过
• 仅当敏感词库更新时，才单独触发全量重扫，不需要每日执行全量扫描

二、MySQL层面优化 #

如果暂时不想脱离MySQL实现匹配逻辑，可做如下调整：

• 分批更新替代单条全表UPDATE，避免长事务、锁表问题，示例代码如下：

select group_concat(word SEPARATOR '|') into @badwords from badwords;
set @start_id = 0, @batch_size = 5000;
-- 按id切片分批处理
while @start_id < (select max(id) from comments) do
    update comments 
    set status = 'F', badwords = 1 
    where id between @start_id and @start_id + @batch_size 
      and status != 'F' -- 已标记违规的直接跳过，避免重复计算
      and comment REGEXP @badwords;
    set @start_id = @start_id + @batch_size;
end while;

• 新增联合索引idx_status_created(status, created_date)，过滤无效扫描行
• 全量扫描历史数据时建议在只读从库执行，匹配结果再同步回主库，避免影响主库业务

三、算法层面优化：AC自动机替代正则匹配 #

MySQL原生REGEXP性能极低，2000个敏感词的正则匹配效率比专用多模式匹配算法低10~100倍：

• 用Go/Java/Python脚本把2000个敏感词构建为AC自动机，AC自动机匹配敏感词的时间复杂度为O(n)（n为评论内容长度），和敏感词数量无关
• 多线程分片读取comments表数据，比如把4亿条记录按id拆成20个分片，20个线程并行匹配，匹配完成后批量更新状态，全量4亿条扫描可压缩到小时级

四、长期架构优化 #

• 新增评论实时检测：新评论写入数据库前先做敏感词检测，直接标记状态，后续仅需在敏感词库更新时扫描对应时间段的评论即可
• 接入全文检索引擎：将评论数据同步到Elasticsearch，用ES的全文检索能力做敏感词匹配，性能比MySQL高2个数量级

内容的提问来源于stack exchange，提问作者MediaGiantDesign