模糊匹配能不能走索引
字符串模糊匹配分为两类：前缀匹配和包含匹配。前缀匹配可以借助有序索引快速定位到起始位置，包含匹配则因为不知道从哪里开始，无法利用普通索引，只能逐行扫描。需要包含匹配又要性能时，得换检索方案：全文索引适合按词检索，三元组索引适合中短文本的模糊包含，固定后缀的匹配还可以把字符串反转后按前缀查询。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL 12 及以上 |
|---|---|
| 前缀匹配 | 可以走普通索引，条件应从字符串开头开始 |
| 包含匹配 | 无法利用普通索引，退化为逐行扫描 |
| 转义符 | 模式里的特殊字符可用转义关键字指定转义 |
| 替代方案 | 全文索引、三元组索引、反转存储列 |
典型使用场景
- 按姓名前缀搜索：适合走索引的前缀匹配
- 商品标题包含关键词搜索：需要全文检索或三元组索引
- 固定后缀查找：文件扩展名、编号尾号
- 搜索条件里带用户输入的百分号或下划线，必须先转义
- 评估把模糊查询改造成可索引形式的收益
可索引与替代方案
可索引与替代方案
-- 可以走索引：前缀匹配
SELECT * FROM users WHERE name LIKE 'wang%';
-- 无法走索引：前置通配
SELECT * FROM users WHERE name LIKE '%wang%';
-- 替代一：MySQL 全文索引，按词检索
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
SELECT * FROM articles
WHERE MATCH(content) AGAINST('mysql' IN BOOLEAN MODE);
-- 替代二：PostgreSQL 三元组索引（需扩展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_content_trgm ON articles USING GIN (content gin_trgm_ops);
SELECT * FROM articles WHERE content LIKE '%mysql%';
-- 替代三：固定后缀搜索，建反转列后按前缀匹配
-- SELECT * FROM files WHERE name_reversed LIKE REVERSE('%xlsx');方案对照
| 匹配方式 | 能否用普通索引 | 适用方案 |
|---|---|---|
| 前缀匹配 | 可以 | 普通索引 |
| 包含匹配 | 不可以 | 全文索引或三元组索引 |
| 固定后缀 | 不可以 | 反转列加前缀匹配 |
| 带转义的模式 | 视前缀而定 | 先用转义关键字处理字面字符 |
| 大数据量自由文本 | 不适用 | 外部搜索引擎 |
数据里含百分号或下划线时，模式含义会改变：这些字符在匹配模式里是通配符，查字面值必须用转义关键字指定转义符，否则连前缀匹配也用不上索引。
三元组索引的建立有前提：文本不能太短，索引体积会明显增大，写入成本也随之上升。上线前用真实数据评估索引大小与写入影响。