SQL 正则表达式与索引结合优化

mysql的regexp无法走索引，必须全表扫描；唯一能走索引的正则类操作是like ‘abc%’前缀匹配；fulltext不支持正则；大数据量下应预提取可索引特征列替代正则查询。

MySQL 的 REGEXP 无法走索引，别硬扛

MySQL 原生 REGEXP（包括 RLIKE）在绝大多数情况下不会使用 B+ 树索引，哪怕字段上有 INDEX 或 FULLTEXT。这不是配置问题，是引擎限制——它必须全表扫描逐行匹配。

常见错误现象：EXPLAIN 显示 type: ALL、rows 等于全表行数、查询慢到超时；但开发者仍试图加 HINT 或调整 collation 挣扎。

• 唯一能触发索引的“正则类”操作，仅限前缀匹配：LIKE 'abc%'（注意不是 '%abc' 或 '%abc%'）
• FULLTEXT 索引只支持 MATCH ... AGAINST，不支持正则语法，也不能写 REGEXP '.*pattern.*'
• 如果必须用正则且数据量大，提前在应用层或 etl 阶段把可索引特征提取为新列（比如 domain_part、status_code），然后用等值或范围查询代替

postgresql 的 ~ 和 ~* 在某些条件下可走索引

PostgreSQL 支持为正则表达式构建函数索引，但前提是正则本身是「固定前缀」或能被 pg_trgm 扩展转化为相似度查询。它不像 MySQL 那样完全放弃索引，但也不是无脑生效。

使用场景：模糊查邮箱域名、日志行首模式、用户昵称关键词匹配（非任意位置）。

• 启用 pg_trgm：CREATE EXTENSION if NOT EXISTS pg_trgm;
• 建 gin 索引：CREATE INDEX idx_users_email_trgm ON users using GIN (email gin_trgm_ops);
• 查询时用 ILIKE 或 % 操作符才能命中索引，~* 表达式只有在简单字面量（如 email ~* '^admin@'）且有函数索引时才可能走索引
• 避免写 ~ '.*keyword.*' —— 这会退化为顺序扫描，pg_trgm 对 .* 无感知

clickhouse 的 match() 和 extract() 能利用跳数索引

ClickHouse 不走传统 B+ 树，而是靠跳数索引（skip）和列式压缩特性加速正则。但前提是正则足够“具体”，能让引擎跳过大量数据块。

性能影响明显：一个宽松的 match(url, 'https?://.*') 几乎没跳过效果；而 match(host, '^api.[a-z]+.com$') 可能跳掉 90% 的块。

• 确保字段类型是 String，LowCardinality(String) 会破坏正则索引能力
• 建表时显式声明跳数索引：INDEX idx_host host TYPE ngrambf_v1(4, 256, 2, 0) GRANULARITY 4
• extract() 比 match() 更重，只在真需要捕获子组时用；否则优先用 position() + substring() 组合替代
• 避免在 WHERE 中嵌套多个 match()，它们无法合并跳过逻辑，会逐个检查

正则 + 索引的真正可行路径：预计算 + 约束条件下推

没有银弹。所有号称“SQL 正则走索引”的方案，本质都是把正则逻辑拆解成可索引的原子条件，再靠优化器合并执行。

容易被忽略的点：正则的语义复杂度越高，预计算列的维护成本就越大；而索引本身会拖慢写入，尤其高频更新场景。

• 例如匹配手机号：phone REGEXP '^1[3-9]d{9}$' → 提前加一列 is_mobile Boolean，用触发器或应用层写入时计算
• 再比如日志级别过滤：log_line ~ 'Error|FATAL' → 单独抽离 log_level String 列，用 enum 或字典映射
• 千万别在 WHERE 里写 REGEXP 同时又依赖其他索引字段排序，优化器大概率放弃全部索引，改用临时文件排序

正则和索引天生不亲。想快，就得承认它不适合在线查询，要么降级为离线预处理，要么换工具——比如把匹配逻辑下推到向量化处理引擎（Doris、Trino）或专用文本引擎（elasticsearch）里做。