mysql通配符是进行模糊查询时的重要工具，常用的通配符包括百分号%和下划线_，其中%可以匹配任意长度的字符，下划线只能匹配单个字符。在实际业务场景中，很多查询需求都需要用到通配符，比如搜索用户名包含特定字符的记录、匹配特定格式的手机号等。
mysql通配符的基本使用方式
最常用的通配符查询是通过LIKE关键字实现的，以下是几个基础的使用示例：
-- 匹配用户名以admin开头的记录 SELECT * FROM user WHERE username LIKE 'admin%'; -- 匹配用户名包含test的记录 SELECT * FROM user WHERE username LIKE '%test%'; -- 匹配用户名长度为5且以a开头的记录 SELECT * FROM user WHERE username LIKE 'a____';
通配符查询效率低下的核心原因
通配符查询效率问题主要和mysql的索引使用规则有关，当通配符出现在匹配字符串的开头时，mysql无法利用B+树索引的有序性快速定位数据，只能进行全表扫描，数据量越大查询速度越慢。
- 当通配符在开头时，比如
LIKE '%test'，索引完全失效，全表扫描 - 当通配符在结尾时，比如
LIKE 'test%'，如果对应字段有索引，可以正常使用索引 - 下划线通配符和下划线在开头时，同样会导致索引失效
提高mysql通配符查询效率的实用技巧
1. 避免通配符放在匹配字符串开头
这是最直接的优化方式，尽量将通配符放在匹配字符串的末尾，让查询能够利用索引。如果业务必须支持开头匹配，可以考虑调整业务需求，或者采用其他方案。
2. 合理设计索引
对经常使用通配符查询的字段建立合适的索引，优先建立前缀索引，减少索引占用的存储空间，同时提升查询效率。以下是创建前缀索引的示例：
-- 对username字段的前10个字符创建前缀索引 CREATE INDEX idx_username_prefix ON user(username(10));
前缀索引的长度需要根据实际数据的区分度来调整，区分度越高，索引的效果越好。
3. 使用覆盖索引减少回表
如果查询的字段都包含在索引中，mysql可以直接从索引中获取数据，不需要回表查询主键对应的完整记录，能大幅提升查询速度。比如只需要查询用户id和用户名时，可以建立包含这两个字段的联合索引：
-- 创建包含id和username的联合索引 CREATE INDEX idx_id_username ON user(id, username);
4. 用其他方案替代通配符查询
如果通配符查询的需求非常复杂，比如需要全文搜索，可以使用mysql的全文索引替代LIKE通配符查询，全文索引的查询效率远高于模糊匹配。以下是全文索引的使用示例：
-- 创建全文索引
CREATE FULLTEXT INDEX idx_username_fulltext ON user(username);
-- 使用全文索引查询包含test的用户名
SELECT * FROM user WHERE MATCH(username) AGAINST('test');
另外如果数据量极大，也可以考虑使用Elasticsearch等专业的搜索引擎来处理模糊查询需求，减轻mysql的压力。
5. 控制查询返回的数据量
即使查询效率已经优化，也不要一次性返回过多的数据，配合LIMIT关键字限制返回的记录数，减少数据传输和处理的开销。
-- 限制返回10条记录 SELECT * FROM user WHERE username LIKE 'test%' LIMIT 10;
不同查询场景的效率对比
以下是几种常见通配符查询场景的效率对比：
| 查询场景 | 索引使用情况 | 效率等级 |
|---|---|---|
| LIKE 'test%' | 可使用索引 | 高 |
| LIKE '%test' | 索引失效，全表扫描 | 低 |
| LIKE '%test%' | 索引失效，全表扫描 | 低 |
| 使用全文索引查询 | 使用全文索引 | 中高 |
注意事项
优化通配符查询时需要结合实际的业务场景和数据量来选择方案，不是所有的通配符查询都需要优化，如果表中的数据量很小，全表扫描的开销也可以接受。同时索引也不是越多越好，过多的索引会影响插入和更新的效率，需要权衡利弊。