MySQL 使用 like “%x“，索引一定会失效吗?

代码中の快捷键

于 2025-01-15 22:51:07 发布

阅读量662

点赞数 11

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/Amsssssssssss/article/details/145169859

版权

MySQL 使用 `LIKE "%x"`：索引一定会失效吗？

在MySQL数据库中，查询优化是一个至关重要的主题。对于包含LIKE条件的查询，尤其是以通配符（如%）开头的模式匹配查询，很多人认为这样的查询会导致索引失效。但事实真的如此吗？在这篇博客中，我们将用通俗易懂的语言详细探讨这个问题，并提供一些实用的建议来帮助你更好地理解和优化这类查询。

1. 索引基础

首先，我们需要了解一点关于MySQL索引的基础知识。索引是用于加速数据库查询的一种数据结构。通过创建索引，可以显著减少需要扫描的数据量，从而提高查询性能。然而，并不是所有的查询都能有效地利用索引，特别是当查询条件复杂或使用了某些特定的操作符时。

2. `LIKE`操作符与索引

LIKE操作符用于模式匹配查询。其基本语法如下：

sql

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

如果pattern不以%开头（例如'abc%'），那么MySQL可以使用索引来快速定位到满足条件的记录。
如果pattern以%开头（例如'%abc'或'%abc%'），则通常认为MySQL无法有效使用索引，因为在这种情况下，它必须检查表中的每一行来确定是否匹配给定的模式。

3. `LIKE "%x"`是否会令索引失效？

答案并不是绝对的“会”。虽然以%开头的LIKE查询确实限制了索引的有效性，但在某些情况下，MySQL仍然能够部分地利用索引，或者通过其他手段优化查询性能。

情况一：完全模糊搜索

当你执行如下查询时：

sql

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

在这种情况下，MySQL确实难以直接利用索引来加速查询，因为它需要检查表中的每个值看是否以john结尾。这种情况下，索引通常被认为是“失效”的。

情况二：前缀+模糊搜索

如果你的查询模式是以某个固定字符串开始并以%结束（例如LIKE 'john%'），MySQL可以非常高效地使用索引来加速查询。这是因为索引可以帮助MySQL快速定位到所有以john开头的记录，而不需要遍历整个表。

sql

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

在这个例子中，索引将大大加快查询速度。

情况三：覆盖索引

即使你的查询使用了以%开头的模式，如果查询只涉及索引列本身，MySQL有时仍能利用索引来满足查询需求。这种情况称为覆盖索引。例如：

sql

SELECT name FROM users WHERE name LIKE '%john';

如果name列上有索引，并且查询只请求name列的数据，MySQL可以直接从索引中读取结果，而不需要访问表数据。

情况四：全文索引

对于复杂的文本搜索需求，考虑使用MySQL的全文索引（Full-Text Index）。全文索引专门设计用来支持高效的文本搜索，包括但不限于LIKE查询。不过，请注意全文索引适用于特定类型的查询，且配置和维护相对复杂。

4. 实践建议

为了最大化查询性能，这里有一些实用的建议：

避免不必要的全表扫描：尽量减少使用以%开头的LIKE查询。如果可能，尝试重构你的查询逻辑，使得至少一部分模式是可以精确匹配的。例如，将'%john'改为'john%'。
使用覆盖索引：如果你的查询只需要从索引中获取信息，而不需要回表查询（即访问实际数据行），那么即使使用了LIKE "%x"，MySQL仍然可能使用索引来满足查询需求。
考虑全文索引：对于复杂的文本搜索需求，考虑使用专门的全文搜索引擎，如Elasticsearch或Solr。这些工具提供了比MySQL更强大的文本搜索功能，并且在处理大规模数据集时表现更好。
测试和监控：不要仅仅依赖理论知识来做决定。实际测试不同的查询策略，并使用MySQL提供的性能监控工具（如EXPLAIN语句）来评估它们的效果。通过实验找出最适合你应用的解决方案。

5. 示例分析

让我们通过一个具体的例子来深入理解上述概念。

假设我们有一个名为users的表，其中包含用户的姓名和其他信息。该表上有一个针对name列的普通B树索引。

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    INDEX idx_name (name)
);

INSERT INTO users (name) VALUES ('john'), ('doe'), ('johnson'), ('smith');

查询示例

前缀搜索：
sql
```
SELECT * FROM users WHERE name LIKE 'john%';
```
这个查询可以利用索引，因为模式以固定字符串开始。
后缀搜索：
sql
```
SELECT * FROM users WHERE name LIKE '%john';
```
这个查询无法有效利用索引，因为模式以%开头。
覆盖索引：
sql
```
SELECT name FROM users WHERE name LIKE '%john';
```
如果查询只需要索引列的数据，MySQL可以直接从索引中读取结果。
全文索引：对于更复杂的文本搜索需求，可以考虑使用全文索引。例如：
sql
```
ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('john');
```