MySQL中如何为反转varchar字段创建最优索引？

你提到的通过反转文本建立索引优化后缀模糊查询的思路是完全可落地的，不同数据库有对应的实现方式：

MySQL 实现方案 #

MySQL 5.7及以上版本可以通过生成列（Generated Column） 实现反转索引：

1. 为需要优化的字段新增自动维护的反转生成列，示例如下（以表article的title字段为例）：

   -- 可选STORED（持久化存储）或VIRTUAL（实时计算，不占存储，仅索引持久化）
   ALTER TABLE article ADD COLUMN title_reverse VARCHAR(255) 
   GENERATED ALWAYS AS (REVERSE(title)) STORED;
   

2. 为生成列创建索引：

   CREATE INDEX idx_title_reverse ON article(title_reverse);
   

3. 查询时改写原有后缀模糊查询条件即可触发索引：
   原来的 WHERE title LIKE "%foo" 改写为 WHERE title_reverse LIKE CONCAT(REVERSE("foo"), "%")，查询效率和前缀模糊查询基本一致。

其他主流数据库实现方案 #

• PostgreSQL：不需要新增冗余列，直接创建表达式索引即可，语法：

  CREATE INDEX idx_title_reverse ON article(REVERSE(title));
  

  查询时直接使用 WHERE REVERSE(title) LIKE CONCAT(REVERSE('foo'), '%') 即可触发索引。
• Oracle：支持函数索引，语法和PostgreSQL一致，创建后改写查询条件即可生效。
• SQL Server：可先创建计算列，再为计算列创建索引，逻辑和MySQL生成列方案一致。

注意事项 #

反转索引仅能优化%后缀格式的后缀模糊查询，如果你需要优化%关键词%这类前后都带通配符的模糊查询，该方案不适用，建议改用数据库原生全文索引或专门的全文搜索引擎。

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