如何提高SQL模糊查询的效率_使用后缀匹配或全文索引优化

顺亿

2026-09-07 • 👁 0人浏览过

LIKE '%关键词%' 一定慢是因为B+树索引仅支持最左前缀匹配，无确定前缀起点，优化器被迫全表扫描，EXPLAIN显示type: ALL、key: NULL、rows≈总行数。后缀匹配（

LIKE '关键词%'

）能走索引，全文索引适合任意位置匹配——但两者适用场景完全不同，不能混用。为什么

LIKE '%关键词%'

一定慢？数据库的 B+ 树索引是按值从左到右排序存储的，

LIKE '%关键词%'

没有确定的前缀起点，优化器无法跳转到索引某一段，只能全扫描。哪怕字段有索引，

EXPLAIN

的

type

字段也会显示

ALL

或

index

，

rows

值接近总行数。常见错误现象：百万级表查一个

LIKE '%张%'

耗时 3~8 秒，而同样数据量的

LIKE '张%'

只要 20ms 加了索引却没生效，

key

列在

EXPLAIN

中仍为

NULL

查询只返回几条结果，但

rows

显示扫描了上百万行改用

LIKE '关键词%'

的实操要点这是最轻量、兼容性最好的优化方式，但要求业务能接受“仅匹配开头”的限制。前端输入框需配合提示：例如用户输“张”，自动补全建议“张三”“张伟”，避免让用户手动加

%

后端 SQL 必须写成

WHERE name LIKE CONCAT(?, '%')

，而不是拼接

'%' + ? + '%'

如果字段允许为空，

IS NULL

条件不能和

LIKE

写在同一层

OR

中，否则可能让索引失效索引列顺序很重要：若查询条件还有

status = 1

，建议建联合索引

(status, name)

，而非单列

name

什么时候必须上全文索引？当业务明确需要“包含关键词”“前后都模糊”“支持同义词或拼音”时，

LIKE

已无解，必须换引擎。 SQL Server 用

CREATE FULLTEXT INDEX

，查询改用

CONTAINS()

或

FREETEXT()

，不是

LIKE

MySQL 5.6+ 的

MATCH ... AGAINST

对中文分词支持弱，生产环境慎用；更推荐 Elasticsearch，尤其已有日志/搜索基建的项目注意数据同步延迟：全文索引不是实时的，Elasticsearch 默认 1 秒刷新，SQL Server 全文索引也有爬网间隔不要在小表（容易被忽略的细节很多人试过

LIKE '关键词%'

还是慢，或者全文索引查不到数据，问题往往不在方案本身：

COLLATION

不匹配会导致索引失效，比如字段是

utf8mb4_0900_as_cs

，而连接字符集是

utf8mb4_general_ci

MySQL 中

INSTR()

和

LOCATE()

函数即使字段有索引，也无法利用索引加速，它们仍是全表扫描 SQL Server 的全文索引默认不索引停用词（如“的”“了”），搜这些词会直接返回空，且不会报错 MyBatis 的

标签拼接

LIKE

时，如果参数为空，可能生成

LIKE '%%'

，等于强制全表扫

相关文章