我在MySQL数据库中有一个注释表，需要扫描"；脏话"以便将包含这些单词的评论标记为适度。
我们用来执行此操作的查询每天通过cron运行一次，但现在已经超过24小时，无法完成标记可疑评论的扫描。
数据分为两个表：
create table comments (id bigint(20) not null primary key auto_increment,
comment varchar(512),
author_id bigint(20) not null,
created_date datetime,
status varchar(1) not null default 'A',
badwords smallint(1) not null default 0);
create table badwords (word varchar(255) not null primary key);
每天我们有一个cron，它使用以下查询执行并触发坏字扫描：
select group_concat(word SEPARATOR '|') into @badwords from badwords;
update comments a set a.status = 'F', a.badwords = 1 where a.comment REGEXP @badwords;
这个设置运行了一段时间，但随着时间的推移，它匹配的速度越来越慢(因为注释表中添加了更多的行)，因此这个查询的运行时间现在已经超过了24小时(这是完全不可接受的)。评论表现在有超过4亿行，坏单词有大约2000个单词
有没有一种更快的方法可以根据任何人找到的坏词列表找到坏条目？
我已经尝试了各种方法，比如连接、游标等，但还没有想出一种更快的方法来扫描这些数据中的坏词。
感谢您的帮助。
使用FULLTEXT索引。每天动态构造查询。查询应该相当快。
SELECT ...
FROM tbl
WHERE MATCH(txt_column)
AGAINST(
'dam sheet friggin' -- This part is dynamically built
IN BOOLEAN MODE)
使用这种方法，您甚至不需要将搜索限制为表中的"新"条目。事实上，这可能会减慢查询速度。此外，不要做"；块化"；正如我在另一个答案中所讨论的。
这种(和其他)方法有很多问题——
- o vs o vs 0 vs。。。作为一种在搜索中隐藏脏话的方法
- FULLTEXT取决于"；单词"；；因此，添加相邻的字母或插入空格将从FULLTEXT中隐藏一个坏单词
更改查询，以便只扫描自上次扫描以来创建的注释。或者在最近25个小时内，以避免由于边缘条件而错过一些评论。
这样，您的运行时间将是恒定的*(而且很短)，而不是随着数据库的大小而增长。
如果你真的必须在坏词列表扩展时重新扫描旧的评论，你可以每月花24个多小时在上面一次。或者，以增加复杂性为代价，每月的测试只能针对新的坏词进行检查。(但要衡量运行时受正则表达式大小影响的程度；使用一个好的正则表达式引擎，可能不会有什么不同。)
(*)几乎是不变的，因为随着业务的增长，24小时内会添加更多的评论。但这比每天扫描所有东西要好得多。
您的REGEXP方法相当不错。(是的，中间词等，存在问题。)
在表中有一个时间戳列。它指示何时添加或修改注释。索引该列。
现在，与其做日常工作，不如做一份持续运转的工作。(这避免了自己被绊倒和更快地捕捉到坏词。)作业在表中循环，在最后一次看到的时间戳处继续。这样，它只检查最近添加/修改的项目。
当你添加到坏词列表中时，启动第二个类似的作业，扫描整个表格一次，然后退出。与此同时，另一项工作是捕捉最近的东西，并检查其中的新旧坏词。
当运行这样一个长列表时，请将其分组。这将有助于避免干扰其他线程。看见http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks了解如何有效地将一个大表分解为1000行块。
因此，我的问题的解决方案是从上面的建议中得出的，再加上我最终对REGEX引擎在MySQL中与其他REGEX引擎的性能进行分析的数据。最后，我发现MySQL REGEX引擎与其他引擎相比实际上相当慢。
我测试了MySQL的regex吞吐量与bash和PHP的对比。
出乎意料的是，我发现PHP拥有最快的正则表达式引擎。
测试尽可能直接——在所有场景中，我都从数据库加载了所有regex模式，并从注释表中选择了100万行，然后"；定时的"；扫描它们以查找所有种类的单词(如上所述)，然后根据需要针对数据库发布更新语句所需的时间。
MySQL总是负责选择或更新数据。
因此，对于BASH测试，我使用mysql命令行将结果返回到SH脚本，然后使用SH进行评估，然后将更新查询发送回mysql。
我使用PHP遵循了相同类型的设置——除了我使用PDO来处理"；选择"；和STDIO来处理更新查询。
php wordvalidation | mysql ....
在我的比较中，PHP在处理和更新1mm行方面优于所有其他解决方案，实现了不到2分钟的运行时间，同时执行了我问题中描述的所有必要的比较。(单线程CLI脚本)。
一旦我下了这个决心，其他的事情就迎刃而解了。
我设置了一个每2分钟运行一次的cron脚本来检查新记录(使用Alexis的建议)。它跟踪它扫描的最后一个id，并在每次生成时使用它来查找新记录。这样可以确保几乎立即扫描和标记新记录(与以前的方法相比有了很大改进)。
我设置了第二个cron脚本，它也每2分钟运行一次，但以线性方式扫描旧记录，并跟踪每天重置回0的索引，这样它就可以重新开始它的过程。这种方法保证了整个列表被定期扫描以寻找新的"数据"；坏关键字"；。这可能是对所需内容的过度处理，但在目前的设置下，每天可以扫描整个数据库，时间大约比以前少1/2
感谢您的评论和帮助Alexis和Rick James。