使用LIKE的数据库查询异常缓慢的原因与优化方案咨询

1. 为什么LIKE查询比=查询慢这么多？ #

• 索引完全失效：普通B树索引是按字段前缀排序存储的，=查询可以直接通过索引快速定位匹配行，时间复杂度接近O(logN)。但你的查询SELECT * FROM work_item_summary WHERE manager LIKE '%manager' ORDER BY created;是后缀模糊匹配，这种以%开头的模式无法触发B树索引，数据库只能执行全表扫描——遍历3000多万条记录逐一判断是否符合条件，这一步的耗时就会指数级上升。
• 额外排序开销叠加：查询包含ORDER BY created，全表扫描得到的符合条件的行是无序的，数据库需要对这些行进行排序。如果结果集较大，内存无法容纳全部数据，还会触发磁盘排序（filesort），进一步拉长耗时。而=查询因为能利用索引定位，甚至可以通过联合索引（比如(manager, created)）直接拿到有序结果，完全避免了额外排序的开销。

2. 通用模糊查询的优化方案 #

因为你需要支持任意列的任意模式查询，普通索引方案确实不可行，以下是几种可行的优化方向：

数据库层面优化 #

• Trigram索引（推荐）：比如PostgreSQL的pg_trgm扩展、MySQL的ngram全文解析器。Trigram索引通过将字段内容拆分为三元字符组构建索引，能高效支持任意位置的模糊查询（包括%xxx、xxx%、%xxx%），性能远优于全表扫描，且不需要针对每个字段的特定模式单独建索引，3000万条数据的场景下，只要服务器资源足够，效果比较理想。
• 字段反转+前缀索引：如果主要是后缀模糊查询（比如%manager），可以新增一个反转字段（比如manager_reversed），存储原字段的反转值（比如reganam），然后给这个反转字段建普通B树索引。查询时用LIKE 'reganam%'匹配原字段的%manager，就能利用索引加速。但这种方案只针对后缀匹配，无法覆盖中间或前缀模糊的场景。
• 全文索引：数据库内置的全文索引（比如MySQL的FULLTEXT、PostgreSQL的tsvector）适合词级别的模糊搜索，但对任意字符模式的匹配支持有限，更适合自然语言类的搜索场景。

外部系统优化 #

• 引入专业搜索引擎：如果需要支持复杂的多字段模糊查询、分词搜索、高亮等功能，建议将数据同步到Elasticsearch、Solr这类专业搜索引擎中。搜索引擎基于倒排索引设计，天生适合海量数据的全文检索和模糊匹配，性能和灵活性都远优于数据库的原生查询。
• 内存数据库加速：如果预算充足，可以将高频查询的数据同步到内存数据库（比如MemSQL），内存级别的查询速度能大幅降低耗时，但成本较高，且需要维护数据同步机制。

临时优化手段 #

• 限制结果集大小：如果业务允许，通过LIMIT减少需要排序和返回的数据量，能显著降低耗时。
• 分区表优化：如果数据有明确的分区维度（比如created字段按时间分区），可以在查询时先过滤分区范围，减少扫描的数据量，但这只对包含分区键的查询有效，无法覆盖任意列的场景。

不存在绝对的“最优方案”，需要根据你的业务场景（查询频率、模式类型、资源预算）选择最适合的方式。

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