大家好，我是锋哥。最近不少粉丝问锋哥MySQL中like模糊查询如何优化?今天锋哥来总结下，大家可以参考。

最近锋哥也开始收一些Java学员，有意向可以找锋哥。

在数据库应用中，LIKE 是一种常用的模糊查询方法，它允许我们在查询中使用通配符进行部分匹配。虽然 LIKE 查询在许多情况下非常方便，但当数据量增大时，使用 LIKE 查询会导致性能问题，尤其是当查询中使用了前置通配符（%abc）时，MySQL无法利用索引，从而导致全表扫描。为了提高性能，我们需要对 LIKE 查询进行优化。

1. LIKE查询的工作原理

MySQL的 LIKE 查询通过对字段内容进行匹配来查找符合条件的记录。常用的通配符有：

• %

• ：匹配零个或多个字符。

• _

• ：匹配一个字符。

例如，查询字段 name 中包含“张”字的所有记录：

SELECT*FROM users WHERE name LIKE'%张%';

如果查询条件是 %张%，MySQL将无法使用索引，因为查询必须扫描每一行来查找匹配。这个查询将变得非常慢，尤其是在表的记录非常多时。

2. LIKE模糊查询的性能瓶颈

全表扫描

当使用 LIKE 查询时，如果在搜索模式中使用了前置通配符（如 %abc），MySQL无法利用索引。这意味着它必须对表中的每一行数据进行扫描，从而显著影响查询速度。

无法利用索引

如果查询模式是前置通配符的形式（如 %abc），MySQL就无法利用索引进行加速，性能会变得非常差。即使字段上有索引，MySQL也无法使用它，因为索引只能用来提高基于前缀的匹配（比如 abc%）。

3. 优化LIKE查询的方法

1. 使用前缀匹配（避免前置通配符）

最有效的优化方法之一是避免使用前置通配符。例如：

SELECT*FROM users WHERE name LIKE'张%';

这种情况下，MySQL可以利用索引进行快速查找，避免全表扫描。如果可以确定查询条件的模式尽量避免前缀是 % 的模糊查询。

2. 使用全文索引（FULLTEXT）

对于大量文本数据的模糊匹配查询，可以考虑使用MySQL的全文索引。全文索引允许对文本字段进行更高效的搜索，适合大数据量的模糊查询。

步骤：

1. 在表中创建全文索引：

2. ALTERTABLE users ADD FULLTEXT(name);

3. 使用 MATCH ... AGAINST 来替代 LIKE：

4. SELECT*FROM users WHEREMATCH(name) AGAINST ('张'INBOOLEAN MODE);

5. 这样可以显著提高查询效率，尤其是对于包含大量文本的字段。

3. 使用正则表达式（REGEXP）

在一些特定情况下，可以考虑使用正则表达式来优化查询。正则表达式在某些情况下比 LIKE 更高效，尤其是对于复杂模式匹配。MySQL支持 REGEXP 运算符，它允许使用正则表达式来进行模式匹配。

例如：

SELECT*FROM users WHERE name REGEXP '^张';

然而，REGEXP 在一些查询中仍然可能不如 LIKE 快，因此在使用时需要谨慎评估性能。

4. 使用 IN 和 OR 来减少查询范围

当查询条件可以分解成多个离散的值时，可以使用 IN 或 OR 进行优化，避免模糊匹配。例如，如果我们知道名字中有特定的几个值，可以使用 IN：

SELECT*FROM users WHERE name IN ('张三', '张伟', '张杰');

这能显著减少扫描的记录数量，从而提高查询效率。

5. 使用索引优化查询

为了优化基于 LIKE 的查询，可以确保查询列有索引，特别是对于查询条件的字段。通过建立索引，可以加速基于前缀匹配的查询。例如：

CREATE INDEX idx_name ON users(name);

对于前缀查询，创建索引后可以显著提高查询性能。但是，记住只有像 name LIKE '张%' 这样的前缀查询才能有效使用索引。

6. 分表或分库

对于数据量极大的表，可以通过分表或分库的方式将数据划分为多个子集，避免单一表的性能瓶颈。例如，可以根据 name 字段的首字母进行分表，或者基于其他逻辑进行拆分。这样可以减少每次查询扫描的表的大小，从而提高查询效率。

4. 示例：优化前置通配符的LIKE查询

假设我们有一个包含用户信息的表 users，并且我们需要查询名字中包含“张”字的所有用户。原始查询：

SELECT*FROM users WHERE name LIKE'%张%';

优化方案1：避免使用前置通配符

我们将查询修改为避免前置通配符的形式：

SELECT*FROM users WHERE name LIKE'张%';

优化方案2：使用全文索引

我们为 name 字段添加全文索引，并使用 MATCH ... AGAINST 进行查询：

ALTERTABLE users ADD FULLTEXT(name);

查询语句：

SELECT*FROM users WHEREMATCH(name) AGAINST ('张'INBOOLEAN MODE);

优化方案3：使用正则表达式

如果需要更灵活的模式匹配，可以使用正则表达式：

SELECT*FROM users WHERE name REGEXP '张';

5. 总结

• 避免前置通配符

• ：尽量使用 LIKE '张%' 这样的前缀匹配，避免 %张% 这样的查询。

• 使用全文索引

• ：对于大量文本数据，可以通过创建全文索引并使用 MATCH 来加速查询。

• 使用正则表达式

• ：对于复杂的模式匹配，考虑使用 REGEXP，但要注意其性能影响。

• 索引优化

• ：确保查询列有合适的索引，尤其是对于前缀匹配的情况。

• 分表或分库

• ：对于大数据量的情况，可以通过分表或分库来优化性能。

通过上述方法的优化，能够显著提高 MySQL 中 LIKE 模糊查询的性能，尤其在大数据量的应用场景下，能够提升查询效率，降低查询时间。

原文来源：https://mp.weixin.qq.com/s/hyg6rYsUXPYiHjvzw9sO9g