如何为文本列的子串创建索引 优化含斜杠的SQL过滤查询性能

你当前使用的 NOT LIKE "%/%" 之所以慢，是因为前置通配符完全无法命中普通B树索引的前缀匹配规则，必然触发全表逐行扫描。针对“匹配任意位置固定子串存在性”的需求，完全不需要额外维护冗余列，也不用受限于只能匹配字符串前缀的方案，有两种低侵入的优化路径：

现代数据库场景（支持表达式索引：MySQL 8.0+、PostgreSQL、SQL Server 2017+等） #

直接对“是否包含目标子串”的判断逻辑建函数索引，零侵入不修改原有业务逻辑：

• 建索引的SQL示例：

  CREATE INDEX idx_text_col_has_slash ON table_name( (INSTR(text_col, '/') > 0) );
  

  这里的INSTR(text_col, '/') > 0是固定布尔表达式，只要文本列任意位置出现/就返回真，完全覆盖子串出现在全字符串任意位置的场景。
• 调整查询写法即可命中索引：
  原来的NOT LIKE "%/%"可以替换为等价的、能匹配上述索引的条件：

  WHERE NOT (INSTR(text_col, '/') > 0)
  

  之前接触到的LEFT()类方案本质是仅对字符串开头的前缀内容建索引，根本无法覆盖子串在任意位置的匹配需求，用返回子串位置的函数做表达式索引就没有这个限制。

老旧数据库场景（不支持表达式索引：如MySQL 5.x） #

可以用自动生成的虚拟列替代手动维护的标记列，同样不需要编写任何业务同步逻辑：

• 新增自动计算的虚拟列，并直接为其创建索引：

  ALTER TABLE table_name 
  ADD COLUMN has_slash TINYINT(1) 
  GENERATED ALWAYS AS (INSTR(text_col, '/') > 0) VIRTUAL,
  ADD INDEX idx_has_slash(has_slash);
  

  这个列的值完全由数据库根据text_col的内容自动计算更新，不需要写触发器、也不需要业务代码手动赋值，维护成本和纯函数索引几乎没有区别。
• 查询时直接使用生成列做过滤即可：

  WHERE has_slash = 0
  

实用注意事项 #

• 这类单固定字符的存在性判断，完全不需要启用全文索引，后者的索引维护和查询开销远高于上面的方案，属于典型的过度设计。
• 后续如果需要匹配其他固定子串的存在性，只需要把表达式里的/替换成目标子串即可，方案通用性很强。

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