百万级数据查询中LIKE运算符的替代方案（匹配结尾含*的字符串）

原查询性能差的原因 #

SELECT example_id FROM example_tbl WHERE example_id LIKE ‘%*%’ 之所以慢，是因为前缀通配符%导致数据库无法利用example_id列上的索引，只能对百万级数据做全表扫描，耗时自然极高。而且你的场景里星号只在末尾，这个语句还会额外匹配中间带星号的无效数据（虽你说明星号仅在末尾，但写法上存在冗余匹配）。

替代方案 #

• 直接匹配末尾字符（简单快速）
  利用RIGHT()函数直接取字段最后一个字符进行判断，语句更精准，性能比原LIKE语句更好：

  SELECT example_id FROM example_tbl WHERE RIGHT(example_id, 1) = '*'
  

  注：如果example_id列有索引，这个方法依然无法直接利用索引，但胜在写法简单，比通配符全匹配的扫描效率略高。

• 添加计算列+索引（最优方案，适合频繁查询）
  如果需要频繁执行这类查询，推荐给表添加一个存储计算结果的列，并建立索引，彻底解决性能问题：

  1. 添加计算列（以MySQL为例）：

     ALTER TABLE example_tbl ADD COLUMN has_trailing_star BOOLEAN GENERATED ALWAYS AS (RIGHT(example_id, 1) = '*') STORED;
     

  2. 给计算列创建索引：

     CREATE INDEX idx_has_star ON example_tbl(has_trailing_star);
     

  3. 后续查询直接使用索引列：

     SELECT example_id FROM example_tbl WHERE has_trailing_star = TRUE;
     

  这个方案会让查询直接走索引，百万级数据的查询速度能提升到毫秒级。

• 通过长度对比判断
  利用星号存在时字段长度会多1的特点，对比原长度和去掉星号后的长度：

  SELECT example_id FROM example_tbl WHERE LENGTH(example_id) != LENGTH(REPLACE(example_id, '*', ''))
  

  这个方法和RIGHT()函数性能接近，但写法不如前者直观。

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