MySQL里用Like做全文检索到底是咋实现的，有啥讲究和坑

MySQL里的LIKE操作符，说白了就是一种模式匹配，它并不是我们常说的那种“全文检索”，真正的全文检索（FULLTEXT）用的是完全不同的技术，比如倒排索引，而LIKE的实现方式其实非常“朴素”甚至可以说“粗暴”。

LIKE是怎么实现的？

它的核心就是逐字符扫描，想象一下，你有一本很厚的书（这就是你的数据表），你要在里面找所有包含“苹果”这个词的句子，LIKE的做法不是先去看书后面的关键词索引，而是从第一页第一行第一个字开始，一个字一个字地往后看，检查从当前位置开始的字符是不是“苹”，如果是，再看下一个字是不是“果”，如果匹配上了，就记下这个位置；如果中间有任何一个字对不上,就立刻从当前行的下一个字重新开始这个匹配过程。

在数据库里,这个过程是这样的：

1. 当你执行一条带LIKE的SQL语句（SELECT * FROM products WHERE name LIKE '%苹果%';），MySQL首先会（如果可能）利用索引，一旦LIKE模式的开头是通配符或_，绝大多数情况下，它就无法使用建立在name字段上的普通B-Tree索引了，因为索引是按照字段的完整内容排序的，你从中间开始匹配,索引就失去了快速定位的能力。
2. 既然用不上索引，MySQL就只能进行全表扫描，也就是把表中每一行数据都捞出来,逐行地对目标字段进行上面提到的那种逐字符比较。
3. 这个比较过程本身是由MySQL的底层字符串比较函数完成的，它会严格按照你指定的模式（比如'%苹果%'）去匹配每一行的数据。

总结一下LIKE的实现：它是一种基于全表扫描的、逐个字符进行匹配的简单模式匹配操作。

使用LIKE的讲究

正因为它的实现方式比较“笨”，所以在使用时就有很多讲究,主要是为了性能。

1. 通配符的位置是性能关键,这是最重要的一条讲究。

   • 最坏情况：LIKE '%关键字%'，模式以开头，这会导致索引完全失效（如果该字段有索引的话），必须进行全表扫描，对于数据量大的表,这是性能杀手。
   • 次坏情况：LIKE '%关键字'，模式以结尾，查询所有以“关键字”结尾的记录，同样无法使用索引,需要全表扫描。
   • 较好情况：LIKE '关键字%'，模式以具体字符开头，后面跟，这种情况下，如果该字段建立了普通索引，MySQL是有可能利用这个索引进行范围扫描的，因为它知道索引是按照字母顺序排的，所有以“关”字开头的条目在索引里是挨着的，它可以快速定位到第一个“关”字开头的记录，然后顺序扫描索引直到条件不满足为止,这比全表扫描快得多。
   • 使用_通配符，下划线_代表匹配一个任意字符，和一样，如果它出现在模式的开头（如LIKE '_关键字'）,也会导致索引失效。

2. 注意转义字符，如果你的查询内容本身就包含或_，你需要使用转义字符，默认是反斜杠\，所以查询“20%折扣”应该写成 LIKE '%20\%折扣%'，你也可以用ESCAPE关键字自定义转义符，比如LIKE '%20#%折扣%' ESCAPE '#'。

   

3. 区分大小写，这取决于你的数据库、表或字段的字符集和校对规则，如果校对规则是大小写敏感的（如utf8mb4_bin或xxx_cs后缀），那么LIKE 'Apple'和LIKE 'apple'的结果是不同的，如果是不敏感的（如utf8mb4_general_ci或xxx_ci后缀），它们就是一样的，这一点很容易被忽略,导致查询结果不符合预期。

LIKE的“坑”

1. 最大的坑：性能陷阱，对于百万级、千万级甚至更多数据的表，滥用LIKE '%...%'是非常危险的，它会让数据库服务器CPU飙升，IO（磁盘读写）压力巨大，导致整个系统响应变慢，甚至拖垮其他正常查询,这可能是LIKE带来的最严重的生产问题。

2. 语义局限，不是真正的全文检索。

   

   • 无法分词：LIKE只会机械地匹配字符序列，比如你搜LIKE '%苹果手机%'，它找不到“苹果的品牌手机很好用”，因为中间多了“的品牌”几个字，而真正的全文检索（FULLTEXT）通常支持分词，可以分别找到包含“苹果”和“手机”的文档,然后按相关性排序。
   • 无法计算相关性：LIKE只有“匹配”和“不匹配”两种结果，不会告诉你匹配的程度有多高，全文检索则可以给结果打分,把最相关的结果排在最前面。
   • 功能单一：不支持布尔搜索（AND, OR, NOT）、同义词扩展等高级功能。

3. 模糊性的两面性，有时候过于模糊反而会返回大量不相关的结果，你需要精心设计模式来过滤,这增加了查询的复杂性。

什么时候该用LIKE，什么时候不该用？

• 适合用LIKE的场景：

  • 数据量小,对性能要求不高的内部管理系统。
  • 确切的前缀匹配，且该字段有索引，例如根据用户名前缀快速查找（LIKE '张%'）。
  • 查询模式非常简单，且全文检索的“大炮”显得过于笨重时。

• 不适合用LIKE，应考虑真正全文检索（如MySQL的FULLTEXT）或专业搜索引擎（如Elasticsearch）的场景：

  • 需要对大文本字段（如文章内容、产品描述）进行灵活的搜索。
  • 需要搜索多个关键词并计算相关性排名。
  • 应用的核心功能就是强大的搜索,且数据量庞大。

总结来源依据综合了MySQL官方文档中关于字符串函数（LIKE操作符）、索引优化（如何避免全表扫描）以及字符集和校对规则的说明，并结合了常见的数据库性能优化实践和全文检索技术的基本原理,核心观点是LIKE的实现机制决定了它在模糊匹配时的性能特征和功能局限。