MySQL中REGEXP匹配标题单词的高性能替代方案咨询

针对REGEXP查询性能差、无法利用title索引的问题，这里提供几个可行的替代方案，按适用场景排序：

1. 全文索引（最优大数据量方案） #

这是处理多单词匹配最高效的方式，直接利用数据库的全文索引能力：
首先给title列创建全文索引：

CREATE FULLTEXT INDEX idx_title_fulltext ON table_name(title);

然后用布尔模式的MATCH AGAINST查询，默认会匹配任意指定单词：

SELECT id, title FROM table_name 
WHERE MATCH(title) AGAINST('Human Resources Director' IN BOOLEAN MODE);

这个方案完全利用全文索引，查询速度远优于REGEXP，适合数据量较大的场景。注意不同数据库的语法细节略有差异：比如PostgreSQL需要先把title转换成tsvector类型再建索引，核心逻辑都是通过预构建的全文索引快速匹配单词。

2. 带单词边界的LIKE组合查询 #

如果暂时无法创建全文索引，可以用LIKE组合来模拟单词边界匹配，部分场景能用到title的索引：

SELECT id, title FROM table_name 
WHERE title LIKE 'Human %' 
   OR title LIKE '% Human %' 
   OR title LIKE '% Human'
   OR title LIKE 'Resources %' 
   OR title LIKE '% Resources %' 
   OR title LIKE '% Resources'
   OR title LIKE 'Director %' 
   OR title LIKE '% Director %' 
   OR title LIKE '% Director';

其中'Human %'这种前缀匹配可以直接用到title的索引，中间和结尾的匹配虽然无法利用索引，但整体性能比REGEXP要好。如果数据库区分大小写，需要统一转换大小写的话，可以考虑给LOWER(title)创建函数索引，再用LOWER(title) LIKE 'human %'来查询。

3. 预分词关联表方案（高频查询专用） #

如果业务中这类单词匹配查询非常频繁，可以提前做分词处理：

1. 创建一个关联表存储拆分后的单词：

CREATE TABLE title_words (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_id INT NOT NULL,
    word VARCHAR(100) NOT NULL,
    FOREIGN KEY (table_id) REFERENCES table_name(id)
);

2. 给word列创建索引：

CREATE INDEX idx_title_words_word ON title_words(word);

3. 新增/更新原表title时，同步拆分单词到关联表（可以用触发器或业务代码实现）。
4. 查询时通过关联表匹配：

SELECT DISTINCT tn.id, tn.title 
FROM table_name tn
JOIN title_words tw ON tn.id = tw.table_id
WHERE tw.word IN ('Human', 'Resources', 'Director');

这个方案查询效率最高，因为直接走word列的索引，但需要额外维护分词逻辑，适合对查询性能要求极高的场景。

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