SQL数据库里用LIKE查找，怎么才能又快又准呢？

要解决SQL中LIKE查询既快又准的问题，我们需要从理解数据库如何工作开始，然后采取一系列有针对性的措施，核心思想是尽量减少数据库需要扫描的数据量,并帮助它用上更高效的查找方式。

理解“慢”的根本原因：全表扫描

想象一下，你的数据库表就像一本厚厚的电话簿，记录着所有用户的信息，当你执行一个像 SELECT * FROM users WHERE name LIKE '%张%' 这样的查询时，数据库并不知道名字里带“张”的人具体在哪一页，它唯一的办法就是从电话簿的第一页开始，一页一页地翻看每一个名字，检查是否包含“张”字，这就是所谓的“全表扫描”（Full Table Scan），当电话簿有几千页（表有数百万行）时,这个翻找过程会非常缓慢。

LIKE查询尤其慢在两种情况下：

1. 前导通配符：当模式以百分号 开头时，如 '%张' 或 '%张%'，这就像让你在电话簿里找“所有姓氏以‘张’结尾的人”，你根本无法利用按姓氏首字母排序的索引,只能从头到尾检查每一个名字。
2. 搜索的列没有索引：即使你的模式是 '张%'（查找姓“张”的人），姓名”这一列没有建立索引，数据库依然只能进行全表扫描，索引就像是电话簿最后的按电话号码或职业分类的快速索引表,能帮你快速定位。

如何实现“快”：给查询装上加速器

要让LIKE快起来，核心就是避免全表扫描,让数据库能用上索引。

1. 最有效的技巧：坚持使用前缀搜索 这是提升LIKE性能最简单、最显著的方法，尽可能让你的查询条件以具体字符开头,而不是通配符。

   • 慢：WHERE name LIKE '%小明' （不知道名字怎么开头）
   • 快：WHERE name LIKE '张%' （知道以“张”开头，可以利用索引） 只要条件不是以 或 _ 开头，数据库就有可能使用建立在name列上的索引（如果存在的话），快速定位到所有以“张”开头的记录，而不需要读取整张表，根据数据库专家Cary Millsap在《优化Oracle应用程序》中的观点,避免不必要的全表扫描是SQL优化最重要的原则之一。

2. 建立合适的索引：创建快速通道 如果经常需要根据某一列进行LIKE搜索,务必为该列创建索引。

   CREATE INDEX idx_users_name ON users (name);

   创建这个索引后，像 WHERE name LIKE '李%' 这样的查询速度会有质的飞跃，但请记住，这个索引对 WHERE name LIKE '%李' 是无效的。

3. 谨慎使用通配符，明确搜索范围

   • （百分号）代表任意多个字符（包括0个）。
   • _（下划线）代表一个任意字符。 通配符越“宽泛”，数据库需要比较的次数就越多。'张_' 比 '张%' 更精确，理论上会稍快一点，在业务允许的情况下,尽量让模式更具体。

如何实现“准”：确保结果正确无误

“准”的问题通常出在对通配符和字符串匹配规则的理解上。

1. 注意尾随空格：数据库中的字符串长度可能是固定的，或者存储时包含了尾随空格，一个name字段是CHAR(10)类型，存储了“张三 ”（后面有8个空格），你的查询WHERE name LIKE '张%'能匹配到，但WHERE name LIKE '张三'可能就匹配不到，因为'张三'不等于'张三 '，可以使用TRIM函数处理后再比较：WHERE TRIM(name) LIKE '张三'，但要注意，对列使用函数会使索引失效,需要权衡。

2. 转义特殊字符：如果你的搜索内容本身就包含或_，需要使用转义符，要查找包含“50%”折扣的商品：

   WHERE description LIKE '%50\%%' ESCAPE '\';

   这里的ESCAPE '\'指定了反斜杠为转义符，这样\%就表示普通的百分号字符，而不是通配符,忘记转义会导致结果数量远多于预期。

3. 考虑大小写敏感性：这取决于数据库的排序规则（Collation），在默认不区分大小写的数据库中，LIKE 'apple%'可能会找到“Apple”、“APPLE”等，如果你需要精确区分大小写，可能需要修改查询或列的排序规则，例如在MySQL中使用BINARY运算符：WHERE name LIKE BINARY 'Apple%'。

   

当LIKE无法满足时：考虑替代方案

最快的LIKE查询也不够快，或者业务需求更复杂,这时可以考虑其他方案。

1. 全文索引：这是为全文搜索设计的“终极武器”，如果你需要在大段文本（如文章内容、产品描述）中搜索词语，全文索引比LIKE高效几个数量级，它能理解词语的边界，支持相关性评分，并且避免了通配符带来的性能问题，在MySQL中可以使用MATCH ... AGAINST语法，数据库专家们普遍认为，对于真正的文本搜索需求,全文索引是比LIKE更专业和高效的选择。

2. 将%name%查询转化为范围查询：这是一个高级技巧，如果你必须进行LIKE '%abc%'这类查询，并且该列有索引，可以尝试用范围查询来“模拟”。WHERE name >= 'abc' AND name < 'abd' 会找出所有介于‘abc’（包含）和‘abd’（不包含）之间的字符串，这实际上包含了所有以‘abc’开头的字符串，虽然它不完全等同于%abc%，但在某些特定场景下（如已知目标字符串靠近开头）,结合其他条件可能会更快。

要实现LIKE查询的又快又准,你应该：

• 首要任务：避免使用前导通配符，尽量使用条件%的形式。
• 基础保障：为频繁用于搜索的列创建索引。
• 保证准确：理解空格、大小写和转义符对结果的影响。
• 超越LIKE：对于复杂的文本搜索,积极考虑使用更强大的全文索引功能。

通过结合这些策略，你就能在大多数场景下,让SQL中的LIKE查询既快速又返回精确的结果。