MySQL模糊查询优化技巧

LIKE查询慢的本质是索引失效：以%开头导致B+树无法利用有序性

LIKE查询为什么慢？索引失效的根源

当查询条件以 LIKE '%abc' 形式开头时，会出现性能瓶颈。其根本原因在于，MySQL的B+树索引依赖于数据的有序排列，而前导通配符 % 破坏了这种有序性。数据库引擎无法在索引树上定位有效的起点，最终只能进行全表扫描。即使相关字段已创建索引，此时索引也无法生效。这是由B+树索引结构决定的限制。

因此，优化的核心方向是明确的：要么避免使用前导通配符，要么采用支持前缀匹配的其他索引机制。

哪些LIKE查询能使用索引？最左前缀匹配原则

B+树索引能否生效，取决于查询模式是否符合“最左前缀匹配”原则。简单来说，查询条件必须有一个明确的、固定的开头：

• WHERE name LIKE 'abc%' → 可以高效利用索引。其执行逻辑类似于 name >= 'abc' AND name < 'abd' 的范围查询。
• WHERE name LIKE 'ab%c' → 无法有效利用索引。因为中间的 % 使得“c”之前的部分无法形成确定的查询边界。
• WHERE name LIKE '%abc' → 典型的全表扫描场景，索引完全失效。
• WHERE name LIKE 'a%b%c' → 同样失效，非连续的前缀使索引无法定位。

需要注意的是，LIKE 操作是否区分大小写取决于字段的排序规则。例如，utf8mb4_0900_as_cs 规则区分大小写，而 utf8mb4_0900_ai_ci 则不区分。但这只影响匹配结果，不改变索引使用的基本规则。

必须使用前导%怎么办？全文索引与倒排索引思路

如果业务场景确实无法避免前导%，例如需要搜索标题中任意位置包含“MySQL数据库教程”的文章，那么 LIKE '%...%' 就成为刚性需求。此时应考虑更专业的解决方案：

• 针对短文本：使用MySQL内置的 FULLTEXT 全文索引，配合 MATCH ... AGAINST 语句。它支持自然语言和布尔搜索模式，效率远高于 LIKE。但需注意，它通常不支持单字符或纯通配符搜索。
• 针对长文本或高精度匹配：可采用预处理和倒排索引的思路。例如，将文本内容按一定长度拆分成所有可能的子串，存入关联表并建立索引。查询时直接搜索子串索引即可。
• 反转字符串技巧：对于特定如 LIKE '%abc' 的需求，可以通过生成列和反转函数实现。原理是将字符串倒过来存储，使查询结尾变为查询前缀：

  ALTER TABLE t ADD COLUMN name_rev VARCHAR(100) AS (REVERSE(name)) STORED;
  CREATE INDEX idx_name_rev ON t(name_rev);

  之后，查询 '%abc' 就等价于查询反转后的列 name_rev LIKE 'cba%'，从而可以利用前缀索引。

其他容易被忽略的优化细节

很多时候，优化尝试看似合理却依然失败，问题往往出在细节上：

• 字段类型陷阱：CHAR 类型字段会自动用空格填充到定义长度。这可能导致 LIKE 'abc ' 这类查询产生意想不到的匹配结果。对于需要模糊查询的字段，建议使用 VARCHAR 类型。
• 排序规则的影响：排序规则不仅决定大小写敏感，还影响等价性判断。例如在 utf8mb4_unicode_ci 规则下，LIKE 'cafe' 可能会匹配到“café”。如果对此不了解，当查询结果与预期不符时，容易误以为是索引问题。而在 _bin 这类二进制排序规则下，则是严格区分大小写的。
• 正确解读执行计划：使用 EXPLAIN 分析查询时，关键要看 type 字段。只有显示为 range 或 ref 时，才表示索引被有效使用。如果 type 是 ALL，那就是全表扫描。
• 函数操作导致索引失效：在字段上使用函数会直接导致索引失效，例如 WHERE UPPER(name) LIKE 'ABC%'。正确的做法是，如果需要进行大小写不敏感的搜索，应该通过指定排序规则来实现：WHERE name LIKE 'abc%' COLLATE utf8mb4_0900_ai_ci。

总之，模糊查询没有通用的“银弹”。最关键的一步是在设计之初明确业务需求：究竟是“开头匹配”、“中间包含”还是“结尾匹配”？明确了这一点，才能选择正确的索引策略。否则，盲目添加索引只会增加数据库的维护负担，对性能提升没有帮助。