MySQL优化含LIKE与排序的慢查询：索引创建方案咨询

先给你泼个冷水：你打算创建的联合索引ref_date (reference, date_created)对当前的慢查询几乎起不到优化作用。原因很简单：LIKE '%SOME_TEXT%'是后缀/中间模糊匹配，普通B-tree索引的有序性是基于字段前缀的，这种匹配方式根本没法利用reference字段的索引部分，连带后面的date_created也没法发挥联合索引的排序优势——数据库还是得全表扫描过滤数据，再做文件排序，性能自然上不去。

下面给你几个更针对性的优化方案，按实用性排序：

1. 全文索引（最推荐的通用方案） #

如果你的数据库支持全文索引（比如MySQL的FULLTEXT、PostgreSQL的tsvector），这是解决这类模糊查询最靠谱的方式：

• 针对reference字段创建全文索引：
  • MySQL：CREATE FULLTEXT INDEX idx_ref_fulltext ON my_table(reference) WITH PARSER ngram;（用ngram解析器支持部分匹配，适合短字符串/编号类字段）
  • PostgreSQL：先创建tsvector字段，再建GIN索引：ALTER TABLE my_table ADD COLUMN ref_tsv tsvector; UPDATE my_table SET ref_tsv = to_tsvector('simple', reference); CREATE INDEX idx_ref_tsv ON my_table USING GIN(ref_tsv);
• 修改查询语句适配全文索引：
  • MySQL：WHERE MATCH(reference) AGAINST('SOME_TEXT' IN BOOLEAN MODE)
  • PostgreSQL：WHERE ref_tsv @@ to_tsquery('simple', 'SOME_TEXT:*')
• 搭配排序：如果需要按date_created排序，可以创建包含date_created的覆盖索引（比如MySQL的FULLTEXT INDEX idx_ref_date_fulltext (reference) INCLUDE (date_created)，PostgreSQL的CREATE INDEX idx_ref_tsv_date ON my_table USING GIN(ref_tsv) INCLUDE (date_created)），这样数据库可以直接从索引里获取排序所需的字段，避免回表。

2. 反转字段+联合索引（适合固定格式的字符串） #

如果你的reference是类似编号、代码的固定格式字符串，没法用全文索引（或者数据库不支持），可以试试反转字段的思路：

• 新增一个reverse_reference字段，存储reference的反转值：

  ALTER TABLE my_table ADD COLUMN reverse_reference VARCHAR(20);
  UPDATE my_table SET reverse_reference = REVERSE(reference);
  

• 创建联合索引：CREATE INDEX idx_revref_date ON my_table(reverse_reference, date_created);
• 修改查询语句：把LIKE '%SOME_TEXT%'改成LIKE CONCAT(REVERSE('SOME_TEXT'), '%')，也就是变成前缀匹配：

  SELECT * FROM my_table WHERE reverse_reference LIKE CONCAT(REVERSE('SOME_TEXT'), '%') ORDER BY date_created;
  

  这时候联合索引就能完全发挥作用：先通过reverse_reference的前缀匹配快速过滤数据，索引里已经按date_created排序，不需要额外做文件排序，性能会大幅提升。

3. 调整查询逻辑（最优但依赖业务） #

如果业务允许，尽量把模糊匹配改成前缀匹配（LIKE 'SOME_TEXT%'），这时候你原本想建的联合索引(reference, date_created)就完全有效了：数据库可以通过reference的前缀索引快速定位数据，同时利用联合索引的有序性直接按date_created返回结果，不需要排序。但如果业务必须支持后缀或中间匹配，这个方法就不适用。

4. 数据库特定的索引类型 #

• PostgreSQL：可以用GIN/GIST索引配合pg_trgm插件，支持基于 trigram 的模糊匹配，对任意位置的模糊匹配都有不错的性能：

  CREATE EXTENSION pg_trgm;
  CREATE INDEX idx_ref_trgm ON my_table USING GIN(reference gin_trgm_ops);
  

  这样直接用WHERE reference LIKE '%SOME_TEXT%'就能用到索引，再搭配INCLUDE (date_created)来优化排序。

总结 #

• 如果是自然语言类的模糊搜索：优先用全文索引
• 如果是固定格式的短字符串：反转字段+联合索引是低成本方案
• 如果业务能调整查询方式：前缀匹配+联合索引是最优解
• 不要浪费时间在(reference, date_created)这个联合索引上，对当前查询没用。

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