SQL LIKE语句与索引失效：查询优化实践指南

SQL查询优化过程中，LIKE语句一直是一个容易被忽视但影响明显的问题。很多开发人员发现，为字段建立了索引之后，普通等值查询速度很快，但使用LIKE进行模糊搜索时，查询性能却明显下降，甚至出现全表扫描。这种情况通常与LIKE匹配方式和索引使用规则有关。

理解SQL LIKE语句为什么导致索引失效，掌握不同场景下的优化方法，对于提升数据库查询效率、降低系统响应时间具有重要意义。

LIKE语句的基本原理

SQL中的LIKE用于实现字符串模糊匹配，常见形式包括：

SELECT * FROM user WHERE username LIKE '张%';

表示查询所有以“张”开头的用户名。

LIKE主要支持两个通配符：

• %：匹配任意数量的字符，包括空字符。

• _：匹配单个字符。

例如：

username LIKE '张%'

可以匹配“张三”“张伟”“张小明”等数据。

username LIKE '%张'

可以匹配“李张”“小张”等以“张”结尾的数据。

username LIKE '%张%'

可以匹配任何包含“张”的字符串。

虽然LIKE使用方便，但不同写法对于索引的影响完全不同。

为什么LIKE会导致索引失效

数据库索引通常采用B+树结构，索引中的数据按照一定顺序排列。对于范围查询或者前缀匹配，数据库可以利用索引快速定位数据范围。

例如：

SELECT * FROM employee WHERE name LIKE '王%';

数据库可以将其转换为类似：

name >= '王' AND name < '王uffff'

这样的范围查询，通过索引快速扫描符合条件的数据。

但是：

SELECT * FROM employee WHERE name LIKE '%王';

或者：

SELECT * FROM employee WHERE name LIKE '%王%';

由于查询条件前面存在不确定字符，数据库无法确定索引树中的起始位置，只能逐行判断字段内容，因此通常会放弃索引，执行全表扫描。

简单来说：

• 左侧固定，右侧模糊：可能使用索引。

• 左侧模糊，右侧任意：通常无法使用普通B+树索引。

常见导致索引失效的LIKE场景

1. 前置通配符查询

这是最常见的问题：

SELECT * FROM article 
WHERE title LIKE '%数据库%';

由于数据库不知道“数据库”出现在字符串的什么位置，因此必须扫描大量数据。

执行计划中通常会看到：

type: ALL

表示进行了全表扫描。

优化方式：

如果业务允许，尽量改为：

SELECT * FROM article 
WHERE title LIKE '数据库%';

利用前缀索引能力提高查询效率。

2. 模糊匹配字段选择不合理

很多系统会设计：

WHERE content LIKE '%关键词%'

用于搜索文章正文、商品描述等大字段。

这种方式随着数据量增长会越来越慢。

例如：

• 10万条数据可能还能接受。

• 100万条数据开始明显变慢。

• 千万级数据通常无法满足实时查询需求。

对于文本搜索场景，更适合使用：

• MySQL全文索引。

• Elasticsearch。

• OpenSearch。

• 专业搜索服务。

例如MySQL全文索引：

SELECT *
FROM article
WHERE MATCH(content) AGAINST('数据库优化');

相比LIKE扫描，可以利用倒排结构快速定位关键词。

3. 字段进行函数处理导致索引无法使用

虽然不是LIKE本身造成的问题，但经常一起出现：

SELECT *
FROM user
WHERE LOWER(username) LIKE 'tom%';

由于username字段经过LOWER函数处理，数据库无法直接使用原字段索引。

类似情况：

WHERE CONCAT(name,'test') LIKE 'abc%'

也会影响索引。

优化方式：

可以增加新的冗余字段：

username_lower

提前保存处理后的值，并建立索引。

4. 隐式类型转换影响索引

例如字段：

phone VARCHAR(20)

查询：

SELECT *
FROM user
WHERE phone LIKE 13800138000;

部分数据库可能发生类型转换，导致执行计划变化。

正确写法：

SELECT *
FROM user
WHERE phone LIKE '13800138000%';

保持查询条件类型与字段类型一致。

LIKE查询优化实践方法

方法一：优先使用前缀匹配

如果业务需求允许，应优先采用：

LIKE 'keyword%'

而不是：

LIKE '%keyword%'

例如：

用户搜索：

“张”

查询：

WHERE username LIKE '张%'

通常比：

WHERE username LIKE '%张%'

效率高很多。

方法二：建立合适的索引

对于前缀查询：

CREATE INDEX idx_username 
ON user(username);

执行：

SELECT *
FROM user
WHERE username LIKE 'admin%';

可以有效利用索引。

但是：

LIKE '%admin%'

即使存在idx_username，也很可能无法发挥作用。

方法三：使用覆盖索引减少回表

如果查询只需要少量字段：

SELECT id, username
FROM user
WHERE username LIKE 'admin%';

可以建立覆盖索引：

CREATE INDEX idx_user_cover
ON user(username,id);

数据库可以直接从索引获取结果，减少访问数据页次数。

方法四：使用全文搜索替代LIKE搜索

对于以下需求：

• 商品关键词搜索。

• 新闻标题搜索。

• 文档内容搜索。

• 用户输入任意关键词搜索。

不要依赖：

LIKE '%关键词%'

建议采用：

MySQL：

FULLTEXT INDEX

或者：

Elasticsearch倒排索引。

搜索引擎可以针对分词、相关度排序、高亮显示等需求进行优化。

方法五：合理拆分搜索字段

例如：

用户表：

nickname
real_name
email
mobile

如果所有字段都使用：

WHERE nickname LIKE '%abc%'
OR real_name LIKE '%abc%'
OR email LIKE '%abc%'

查询压力会非常大。

可以根据业务建立：

• 搜索专用表。

• 搜索关键词表。

• 数据同步索引库。

将事务数据库和搜索系统分离。

如何判断LIKE是否使用索引

不要仅凭SQL语句判断，应该查看执行计划。

MySQL可以使用：

EXPLAIN
SELECT *
FROM user
WHERE username LIKE 'admin%';

重点关注：

type字段

常见值：

• ALL：全表扫描。

• index：扫描整个索引。

• range：范围扫描，通常较好。

• ref：高效索引查询。

key字段

查看实际使用的索引：

key: idx_username

如果：

key: NULL

说明没有使用索引。

rows字段

表示预计扫描的数据行数。

如果：

rows: 1000000

说明查询成本较高。

不同数据库中的LIKE差异

MySQL

MySQL对于：

LIKE 'abc%'

通常可以使用普通索引。

对于：

LIKE '%abc'

通常无法使用B+树索引。

PostgreSQL

PostgreSQL提供更多优化方式，例如：

• pg_trgm扩展。

• GIN索引。

• GiST索引。

可以支持部分包含匹配查询。

例如：

CREATE INDEX idx_name_trgm
ON users