Laravel 5.3大表正则查询性能优化求助

这个问题的核心是NOT REGEXP + 大量词汇会触发全表扫描——MySQL没法用普通索引来加速正则匹配，尤其是NOT开头的，120万条数据自然慢得离谱。给你几个从易到难、性能逐步提升的解决方案：

1. 改用MySQL全文索引（最直接的优化） #

REGEXP本质是做文本模糊匹配，而MySQL的全文索引就是专门为这类场景设计的，效率比正则高几个数量级。

首先给comment字段加全文索引：

ALTER TABLE table1 ADD FULLTEXT INDEX ft_comment (comment);

（如果你的MySQL是MyISAM引擎，直接就能用；InnoDB从5.6开始支持全文索引，Laravel5.3对应的MySQL版本完全兼容）

然后把你的查询改成用MATCH AGAINST替代NOT REGEXP：

// 把500个词汇放到数组里，避免正则的空格匹配问题
$badWords = ['good', 'bad', 'nice', ...];
// 拼接成空格分隔的字符串，适配BOOLEAN模式的全文搜索
$wordStr = implode(' ', $badWords);

$temp1 = $connection->table('table1')
    ->select('*')
    // 用NOT MATCH排除包含任意关键词的记录
    ->whereRaw('NOT MATCH(comment) AGAINST(? IN BOOLEAN MODE)', [$wordStr])
    ->paginate(30);

这里要注意：MySQL默认的全文索引有最小词长限制（比如InnoDB默认是3个字符），如果你的词汇里有短词，需要修改my.cnf里的innodb_ft_min_token_size参数，重启MySQL后重建索引。

2. 预计算标记字段（性能最优方案） #

如果这500个词汇不怎么变动，那最彻底的优化是把匹配结果提前计算好，不用每次查询都做文本扫描。

步骤如下：

1. 给表加一个标记字段：

ALTER TABLE table1 ADD COLUMN has_bad_word TINYINT(1) DEFAULT 0 COMMENT '是否包含违规词汇：0=否，1=是';

2. 批量更新现有数据（分批处理，避免锁死全表）：

$batchSize = 1000;
$total = $connection->table('table1')->count();
$totalBatches = ceil($total / $batchSize);
$regexp = 'good|bad|nice|...'; // 去掉多余空格的正则表达式

for ($i = 0; $i < $totalBatches; $i++) {
    $connection->table('table1')
        ->skip($i * $batchSize)
        ->take($batchSize)
        ->update([
            'has_bad_word' => DB::raw("CASE WHEN comment REGEXP '$regexp' THEN 1 ELSE 0 END")
        ]);
}

3. 给标记字段加索引：

CREATE INDEX idx_has_bad_word ON table1(has_bad_word);

4. 之后的查询就超级简单了：

$temp1 = $connection->table('table1')
    ->select('*')
    ->where('has_bad_word', 0)
    ->paginate(30);

别忘了在新增/更新数据时自动更新这个标记！比如在你的模型里加个事件：

protected static function boot()
{
    parent::boot();
    
    static::saved(function ($model) {
        $regexp = 'good|bad|nice|...';
        $model->has_bad_word = preg_match("/$regexp/", $model->comment) ? 1 : 0;
        // 用saveQuietly避免触发循环事件
        $model->saveQuietly();
    });
}

这个方案的查询速度基本是毫秒级的，因为直接走索引过滤，完全跳过了文本匹配的过程。

3. 用关联表维护词汇（适合词汇频繁更新的场景） #

如果这500个词汇需要经常添加/删除，那可以把它们放到一个独立的表中，用关联查询替代正则。

1. 建词汇表：

CREATE TABLE bad_words (
    id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(255) NOT NULL UNIQUE COMMENT '违规词汇'
);
-- 把你的500个词汇插入进去
INSERT INTO bad_words (word) VALUES ('good'), ('bad'), ('nice'), ...;

2. 用WHERE NOT EXISTS查询：

$temp1 = $connection->table('table1')
    ->select('*')
    ->whereNotExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('bad_words')
            ->whereRaw('table1.comment LIKE CONCAT("%", bad_words.word, "%")');
    })
    ->paginate(30);

可以给bad_words.word加个索引，提升匹配速度：

CREATE INDEX idx_bad_word ON bad_words(word);

这个方案的好处是词汇维护方便，不用改代码，直接操作数据库就行，但性能比全文索引和预计算字段差一些，因为LIKE %xxx%还是没法利用comment字段的索引。

4. 临时应急：优化正则表达式 #

如果上面的方案都暂时没法实施，先把你的正则优化一下——原来的'good | bad | nice'里有多余的空格，会匹配"good "（带空格的good），可能不是你想要的，改成'good|bad|nice'去掉空格，减少正则的匹配开销。不过这个只是杯水车薪，本质还是全表扫描，只能临时缓解。

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