正则表达式是处理文本的一把利器，在SQL里通过REGEXP（MySQL）或~（PostgreSQL）操作符，可以轻松实现诸如“查找所有包含连续三个数字的邮箱名”或者“筛选出以特定前缀开头的日志行”。但大多数时候，这类查询的性能并不理想——执行计划里往往只能看到全表扫描。根本问题在于，常规的B-Tree索引是按列的完整值或最左前缀排序的，而正则匹配是一种任意的模式匹配，索引无法直接定位到满足模式的行。不过，这并不意味着我们只能忍受慢查询，通过合理设计函数索引、生成列或调整匹配策略，仍然可以为正则查询铺一条索引高速路。
正则查询为什么默认不走索引？
先来看一个典型的场景。假设有一张用户表users，其中email字段上有普通索引。执行下面的MySQL语句：
SELECT * FROM users WHERE email REGEXP '^[a-z]{3}[0-9]+@';
使用EXPLAIN分析后会发现，type列为ALL，即全表扫描。原因在于REGEXP需要评估每一行的email值是否匹配给定的模式，而B-Tree索引无法提供这种“按模式过滤”的能力。索引只能加速等值、前缀匹配或范围扫描，对于复杂的正则表达式，数据库引擎无法将其自动转换为索引查找操作。
PostgreSQL中类似，使用email ~ '^[a-z]{3}[0-9]+@'同样会走Seq Scan。除非模式的最左侧部分是固定的前缀（例如^abc），并且数据库优化器足够智能，可能会将前缀部分提取出来用作索引条件，但这属于特例，且多数正则模式并不满足这个条件。
函数索引：把正则的“不变部分”固化下来
虽然正则表达式本身无法被索引，但如果在查询中经常使用某个固定的正则模式，或者模式中有一部分是可以预先计算的，我们就可以利用函数索引（也叫表达式索引）来优化。函数索引存储的不是原始的列值，而是对列值应用某个函数或表达式后的结果。当查询中的WHERE条件包含相同的表达式时，优化器就有可能使用该索引。
以PostgreSQL为例，假设我们想频繁查询邮箱名称中是否包含连续三个数字。可以创建一个基于substring或自定义函数的索引。但更实用的场景是：正则表达式始终用于判断一个列是否满足某种二值条件（是或否），我们可以创建一个布尔类型的函数索引。
-- 创建一个函数，返回邮箱是否包含连续三个数字
CREATE OR REPLACE FUNCTION has_three_digits(email TEXT)
RETURNS BOOLEAN AS $$
BEGIN
RETURN email ~ 'd{3}';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 基于函数创建索引
CREATE INDEX idx_email_has_three_digits ON users (has_three_digits(email));
查询时，必须使用完全相同的函数调用才能触发索引：
SELECT * FROM users WHERE has_three_digits(email) IS TRUE;
这样，PostgreSQL就可以扫描该函数索引，而不是全表扫描。注意，函数必须被标记为IMMUTABLE，即对于相同的输入总是返回相同的结果，否则无法用于索引。
在MySQL 8.0中，虽然没有函数索引，但可以使用生成列（Generated Column）来达到类似效果。生成列可以存储基于其他列计算出的值，并能为之建立普通索引。
ALTER TABLE users
ADD COLUMN email_has_three_digits TINYINT
GENERATED ALWAYS AS (email REGEXP '[0-9]{3}') STORED;
CREATE INDEX idx_email_digits ON users(email_has_three_digits);
然后查询时直接使用生成列：
SELECT * FROM users WHERE email_has_three_digits = 1;
这样，正则匹配的结果被提前计算并持久化，查询就变成了对整数值的等值查找，完美利用索引。不过要注意，STORED生成列会占用磁盘空间，并且在插入和更新时会稍微影响性能，需要根据实际场景权衡。
利用前缀匹配 + 正则替换策略
很多时候，正则表达式中最消耗性能的是中间或末尾的通配符，而开头部分则相对固定。如果能够把固定前缀提取出来单独做索引过滤，再对剩余少量行做正则校验，整体性能会大幅提升。这种思路依赖数据库优化器的能力，但在某些数据库中需要手动干预。
比如要查找所有以user_开头，后面跟着2到4位数字的邮箱地址。直接写email REGEXP '^user_[0-9]{2,4}@'会导致全表扫描。但我们可以利用LIKE前缀查询能走索引的特点，先用前缀筛选，再叠加正则条件：
SELECT * FROM users
WHERE email LIKE 'user_%'
AND email REGEXP '^user_[0-9]{2,4}@';
这时如果email列上有索引，LIKE 'user_%'会执行range扫描，返回所有以user_开头的行。这一步已经从全表缩小到了少量数据，随后的REGEXP只在这些行上进行评估，整体查询速度就会快很多。
在PostgreSQL中，类似的写法同样有效：
SELECT * FROM users
WHERE email LIKE 'user_%'
AND email ~ '^user_[0-9]{2,4}@';
这也是一种用索引辅助正则查询的经典方法，适用于模式起始部分固定的情况。如果起始部分也多变，则考虑其他方案。
对正则结果使用全文索引
对于复杂的文本搜索需求，如果正则表达式主要用于单词或词干的匹配，完全可以考虑用全文索引代替正则。全文索引专为文本搜索设计，支持布尔模式、自然语言模式等，并且能够使用倒排索引，性能远胜正则表达式的全表扫描。
在MySQL中，为email字段建立全文索引并没有多大意义，因为邮箱地址不是自然语言。但对于日志表、文章内容表等场景，如果经常需要查找包含特定关键字的记录，使用MATCH ... AGAINST配合全文索引无疑是更优的选择。但如果确实需要正则表达式的灵活性，全文索引就无法覆盖了。
PostgreSQL的pg_trgm扩展提供了一种折中方案。它通过三元组（Trigram）索引来加速LIKE和正则表达式的相似度搜索。启用扩展后，可以为文本列创建GIN或GiST索引：
CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);
此时，像WHERE email ~ 'abc[0-9]+'这样的正则查询，可能会使用该三元组索引进行过滤。不过它的加速效果依赖于模式中固定三元组的数量，如果模式太短或全是通配符，索引效果会打折扣。但相较于全表扫描，很多时候仍能带来数量级的提升。
执行计划对比：有索引 vs 无索引
理论讲完，我们来实际操作，对比一下优化前后的执行计划差异。这里以PostgreSQL为例，创建一张包含10万行随机数据的大表：
CREATE TABLE test_data (
id SERIAL PRIMARY KEY,
content TEXT
);
INSERT INTO test_data (content)
SELECT md5(random()::text) || ' fixed_suffix ' || md5(random()::text)
FROM generate_series(1, 100000);
假设需求是查找所有以fixed_suffix结尾且前面部分包含至少两个数字的行。直接写正则查询：
EXPLAIN ANALYZE SELECT * FROM test_data WHERE content ~ 'd{2}.*fixed_suffix$';
输出显示Seq Scan on test_data，执行时间可能达到数百毫秒。此时我们创建函数索引：
CREATE OR REPLACE FUNCTION match_custom_pattern(t TEXT)
RETURNS BOOLEAN AS $$
BEGIN
RETURN t ~ 'd{2}.*fixed_suffix$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE INDEX idx_custom_pattern ON test_data (match_custom_pattern(content));
然后重新执行查询（注意使用函数调用）：
EXPLAIN ANALYZE SELECT * FROM test_data WHERE match_custom_pattern(content) IS TRUE;
此时计划变为Bitmap Heap Scan on test_data，利用idx_custom_pattern索引，执行时间可能降至几毫秒。这就是函数索引带来的直接收益。
注意事项与局限性
函数索引和生成列虽然好用，但并非万能。首先，它们都是针对特定正则模式的优化方案，如果查询中的正则表达式经常变化，就无法为每种模式都建立索引。此时需要评估能否将多种模式统一为一种固定模式，或者接受全表扫描的现实。
其次，函数索引可能会增加写操作成本，因为每次插入或更新都要重新计算函数值并更新索引。对于写密集型表，需要仔细测试性能影响。
另外，MySQL的生成列如果定义为VIRTUAL而不是STORED，虽然不占用额外存储，但无法直接创建普通索引，只能在5.7及以上版本配合使用虚拟列的二级索引，但有些场景不支持。稳妥起见，使用STORED仍是最通用的做法。
最后，正则表达式本身的书写也影响性能。尽量避免使用捕获组和复杂的回溯，采用非贪婪匹配时注意其可能带来的回溯开销。合理使用字符类（如[0-9]）而非通配符，也能让优化器更容易估算行数。
实战建议：三步优化法则
当你的SQL正则查询变慢时，可以按照以下步骤进行诊断和优化：
- 第一步：尝试提取固定前缀。检查正则表达式是否以固定字符串开头，如果是，加上一个
LIKE 'prefix%'条件，利用已有索引缩小扫描范围。 - 第二步：评估模式是否固定。如果正则模式是业务逻辑中固定不变的（比如验证手机号、邮箱格式），毫不犹豫地使用生成列（MySQL）或函数索引（PostgreSQL）将计算结果持久化并加索引。
- 第三步：考虑替代方案。对于复杂的文本搜索，测试全文索引或三元组索引是否能满足需求。哪怕无法完全替代正则，也可以先用全文索引过滤出候选集，再用正则精确匹配，组合使用往往效果更佳。
通过这三步，绝大多数正则查询都能找到合适的索引加速方案，让模糊匹配不再成为性能瓶颈。