前几天在帮同事排查一条慢查询一条模糊搜索的SQL把接口拖到了1.2秒数据量才不到200万行。一看语句WHERE customer_name LIKE %某某%典型的通配符前置写法直接把索引废掉了。后来改成前缀匹配加覆盖索引耗时降到几十毫秒。其实MySQL里的通配符很简单就是%和_两个符号的事但能把它们用好的人真不多。很多人写模糊查询就是随手一个LIKE %关键词%完全不考虑匹配规则、索引利用、特殊字符转义和边界条件。这篇文章我不打算复述文档而是结合实际排查经验把通配符的用法、原理、性能陷阱和常见坑一次讲透适合刚接触SQL的初学者也适合写了好几年SQL但从来没深究过这几个符号的开发者。1. 通配符的基础语法与匹配规则1.1 两个核心符号% 与 _先明确最基础的东西。MySQL的LIKE操作符支持两个通配符这是所有模糊匹配的起点。%表示任意长度的字符序列包括0个字符。所以LIKE 张%能匹配“张”、“张三”、“张三丰”也能匹配“张”。注意最后这个情况%可以代表空字符串这意味着LIKE 张%是能匹配到恰好等于“张”的数据行的很多人在写“以某字符开头”的查询时会下意识以为还要把全等的情况单独写一遍其实完全不需要。_表示恰好一个字符。一个下划线占一个位置两个下划线占两个位置。LIKE 张_匹配“张三”、“张四”但不匹配“张”和“张三丰”。如果要匹配第二个字符是某个特定值的场景比如查所有姓“张”且名字第二个字是“三”的人可以写LIKE 张三%也可以用LIKE _三%表达“第二个字是三”这个位置语义。这两个符号可以任意组合LIKE _a%b的含义是第二个字符是a后面有任意内容最后以b结尾。核心的匹配逻辑是从左到右逐字符比对%会贪心地向右扩展_则严格占用一个字符位。1.2 大小写与排序规则的影响通配符比较是否区分大小写不取决于LIKE本身而取决于字段的排序规则Collation。MySQL里字符串字段都有字符集和排序规则比如最常见的utf8mb4_general_ci结尾的ci是 case insensitive也就是大小写不敏感。在这种排序规则下LIKE abc%能匹配到 “ABC”、“Abc”、“abc”。如果字段用的是utf8mb4_bin或utf8mb4_0900_ai_ci之外的二进制排序规则比较就是大小写敏感的。如果你在排查“为什么我查小写查不到大写”之类的问题先去看字段的排序规则而不是怀疑通配符写错了。这在实际业务里经常埋坑。比如用户名、邮箱这类字段注册时用户可能输入大写查询时你按小写匹配排序规则不敏感就没事敏感就出问题。我处理过一个线上故障用户在A环境查得到记录在B环境查不到最后定位就是两个库表的排序规则不同一个utf8mb4_unicode_ci一个utf8mb4_bin。1.3 空格、NULL 与隐式转换的边界LIKE匹配不会忽略字符串末尾的空格这是需要注意的。如果一个字段值是张三 带尾部空格那么LIKE 张%能匹配到因为%把空格也吃掉了但LIKE 张三匹配不到它因为全等比较会区分尾部空格。这里有个更隐蔽的坑对于CHAR类型的字段MySQL 在存储时会自动去掉尾部空格所以CHAR字段上LIKE 张%和LIKE 张三%的行为可能和VARCHAR字段不一样。建议所有模糊查询字段都用VARCHAR别用CHAR省得给自己找麻烦。还有一个坑是 NULL。任何与 NULL 进行的 LIKE 比较结果都是 NULL也就是不匹配。WHERE name LIKE %abc%不会返回 name 为 NULL 的行WHERE name NOT LIKE %abc%同样不会返回 NULL 行。很多人写“不包含某个关键词”的查询时会漏掉 NULL 的记录需要额外加OR name IS NULL才行。另外注意隐式转换。如果对数值字段用 LIKE比如WHERE phone LIKE %123%MySQL 会把字段隐式转换成字符串再匹配这个转换可能导致索引失效和全表扫描而且行为有时反直觉。我曾经见过有人对id这种整型字段写WHERE id LIKE 12%看起来是查id以12开头的记录实际上MySQL做了全表扫描更可怕的是id字段为123和123.0的情况表现还不一样。数值匹配就用数值范围别用通配符。2. 通配符与索引走索引的秘密2.1 前缀匹配为什么能走索引这是通配符使用的核心性能知识点。B树索引的本质是按索引列的值排序存储所以查询能够利用索引的前提是能够从根节点开始根据排序好的键值快速定位。LIKE abc%能走索引是因为优化器能把它转换成一个范围查询。逻辑是匹配所有以“abc”开头的字符串等价于 abc AND abd假设字段序和字符序一致这是一个典型的范围扫描。你可以在 MySQL 中执行EXPLAIN SELECT * FROM t WHERE col LIKE abc%如果满足条件type显示rangekey显示实际使用的索引。这里有一个实用的细节前缀匹配中如果字符串有明确的长度比如LIKE abc%索引的范围边界就是[abc, abd)如果写LIKE abc本身MySQL 甚至会把它优化成等值访问ref。所以能写有限前缀就不要加多余的%。2.2 为什么 % 放到开头就没救了LIKE %abc%和LIKE %abc走不了索引这个结论几乎所有人背过但理解为什么才能真正记住。B树索引只支持“从左到右”的匹配定位。一旦%出现在模式的最左侧意味着索引的第一个字符是不确定的优化器不知道应该从哪棵子树的哪个节点开始扫描只能把整棵索引树遍历一遍或者干脆回表遍历全表数据做逐行的模式匹配。LIKE %abc理论上如果索引是倒序的就能利用但MySQL的标准B树索引不支持倒序匹配。所以现实情况是只要模式以%开头这个查询就基本告别索引了。2.3 实在绕不开 % 开头怎么补救业务场景里总是有“包含某关键词”的需求比如搜索商品名包含“手机”的所有记录这天然就是LIKE %手机%。不可能把所有查询都改成前缀匹配但有几条优化路径可以走。第一条是覆盖索引。如果查询只需要返回id和keyword这两个字段而这两个字段恰好都在某个索引里MySQL 可以不回表直接扫描索引虽然还是全索引扫描但比全表扫描快很多。比如SELECT id, product_name FROM products WHERE product_name LIKE %手机%在有(product_name, id)联合索引的情况下性能会比SELECT *好不少。第二条是全文索引适合大文本和长内容的搜索。MySQL 5.7 之后支持中文全文索引通过WITH PARSER ngram对中文文本做内置分词。全文索引比LIKE %关键词%快几个数量级但要注意全文索引的语义和 LIKE 不完全一样它是基于分词匹配的可能有召回率的问题同时维护全文索引也有额外的存储开销。第三条是反向列存储适合“以某词结尾”的固定场景。把字符串反转一下存储比如存REVERSE(product_name)查询后缀LIKE %手机就变成前缀匹配LIKE 机手%索引就可以用了。这种做法对字段的写入逻辑有侵入通常适用于后缀匹配场景特别集中、且写入低频的业务。第四条是外部搜索引擎。如果模糊搜索的体量真到了千万级、亿级LIKE不管怎么优化都顶不住这时候应该上ES或者专业的搜索组件MySQL 里只保留精确匹配和范围查询。这个决策不是技术炫技而是数据量到了那个量级关系型数据库的模糊匹配能力确实有物理天花板。3. 特殊字符与转义处理3.1 搜 % 或 _ 时通配符变成了普通字符通配符本身也是合法字符那么问题来了用户搜索的关键词里如果真的包含%或者_怎么办比如商品型号叫A_100用户搜索“A_100”你用LIKE %A_100%去匹配结果会把 “AA100”、“AB100”、“A_100”全查出来因为_被当成了“任意一个字符”。解决办法是转义。MySQL 的 LIKE 默认用反斜杠\作为转义符所以LIKE %A\_100%会把_变成普通字符。同理LIKE %50\%%匹配包含“50%”这个字符串的记录。这里有一个容易踩的雷如果代码里用了参数绑定或者ORM框架像 MyBatis 的%${keyword}%这种写法或者 Python 里直接把用户输入拼进SQL用户输入的反斜杠和通配符会一起进入 SQL造成转义失效甚至SQL注入风险。处理用户输入中的通配符应该在应用程序层先把\、%、_全部转义掉再做参数绑定。3.2 换一个转义符ESCAPE 的用法有些场景下反斜杠在字符串里已经被业务层转义过一次再叠加 LIKE 的转义会搞得乌烟瘴气。比如在 Java 字符串里写LIKE %A\\_100%才能表达一个反斜杠要是业务逻辑再复杂点代码就完全没法读了。这时候可以用ESCAPE自定义一个转义字符。比如SELECT * FROM products WHERE model LIKE %A$_100% ESCAPE $;这样$成了当前 LIKE 表达式里的转义符只有$_会被识别为转义后的普通_SQL 字符串本身不需要再写反斜杠代码可读性高很多。选转义符时尽量挑业务数据里几乎不出现的字符$、#、!都是常见选择注意别选%和_本身。3.3 反斜杠的双重转义连环坑MySQL 的字符串字面量中反斜杠本身就是转义符。比如a\nb里的\n会被解释成换行符。所以当你写出LIKE %A\_100%时MySQL 先解析字符串字面量\_中反斜杠后跟下划线在字符串解析时反斜杠保留下划线保留等于说字符串里还是一个反斜杠加下划线然后再交给 LIKE 匹配引擎于是_被转义。这么理解的话就清楚了对于%和_字符串解析阶段不改变它们真正起作用的是 LIKE 引擎的反斜杠。但如果你的数据里本身就有普通反斜杠比如 Windows 路径C:\temp\file想搜它就得把反斜杠层层转义写成WHERE file_path LIKE %C:\\\\temp\\\\file%这条 SQL 在 MySQL 字符串解析阶段\\变成单个反斜杠到了 LIKE 匹配阶段单个反斜杠会把后面的字符转义所以需要两个反斜杠才能表达一个原义反斜杠。很多人在这一步彻底晕掉。我的建议是能用 ESCAPE 自定义转义符的地方坚决不用默认反斜杠代码里少一半诡异符号。4. 进阶比较LIKE、REGEXP 与字符串函数4.1 REGEXP正则匹配要什么自行车MySQL 支持正则匹配关键字是REGEXP或RLIKE两者等价。正则表达式的表达能力远强于%和_。匹配“以A开头第二位是数字以xyz结尾”的字符串LIKE 写不出来REGEXP 一行搞定SELECT * FROM users WHERE username REGEXP ^A[0-9].*xyz$;但正则的代价是性能更差。MySQL 的 REGEXP 在数据量稍大时基本不可能走索引而且正则引擎本身有复杂度上限某些写法在超大文本上可能触发灾难性的回溯。我的经验是字段值很短、数据量很小、需要复杂模式匹配时用 REGEXP数据量大、模式相对固定时优先考虑 LIKE 加前缀或者干脆上全文索引。REGEXP 还有一个地方容易翻车MySQL 8.0 之前和之后的正则引擎不同8.0 用 ICU 库一些字符类写法的行为有差异跨版本应用要格外小心。4.2 字符串函数有时更好用模糊匹配不只有 LIKE 一条路。LOCATE、POSITION、INSTR函数可以判断一个子串是否在字段中出现语义上等价于LIKE %substring%。SELECT * FROM products WHERE LOCATE(手机, product_name) 0; SELECT * FROM products WHERE INSTR(product_name, 手机) 0;这些函数的可读性更好语义也更明确但性能上和%关键字%一样都走不了索引。它们主要的好处是避免通配符转义问题因为子串内容不需要考虑%和_被特殊处理。前缀匹配还有一个函数是LEFT(col, n)配合可以表达LIKE abc%同样不一定能利用索引反而写法更啰嗦不如直接用 LIKE 前缀。4.3 多个通配符条件的组合策略实际开发里模糊搜索往往有多个条件。比如搜索商品用户可能同时选“分类电子产品”和“名称含手机”。这时要注意联合索引的列顺序等值条件放在前面范围/模糊条件放在后面。比如建索引(category_id, product_name)查询WHERE category_id 10 AND product_name LIKE %手机%时MySQL 通过category_id定位到一个较小的范围再在这个范围内做模糊匹配效率远高于全表扫。如果是多个 LIKE 条件组合比如name LIKE %A% AND desc LIKE %B%基本上没有优化空间只能靠扫描。这时候可以给每个条件加一个布尔值辅助列写入时预先计算好查询时直接 1空间换时间在特定业务里很管用。还有一个容易忽略的点像LIKE 张% AND age 30这种优化器可以选择先走name索引过滤前缀再回表过滤age。但如果你写age 30 AND name LIKE 张%优化器可能会根据统计信息选择先过滤age还是先过滤name最终计划不一定符合直觉。遇到查询慢老老实实EXPLAIN看真实执行计划别靠猜。5. 实战场景与问题排查实录5.1 场景一用户搜索关键词中的通配符处理假设有一个搜索接口前端把用户输入的关键词传给后端后端拼SQLSELECT * FROM products WHERE product_name LIKE % #{keyword} %如果用户输入的是%那这条SQL会匹配所有商品如果输入_它会匹配大量只有一个字符差异的产品如果输入50%off很可能什么都查不到或者查出一堆错误结果。正确做法是在Java/Python/Go代码里对keyword做预处理函数 escapeLike(String input) { 先将 \ 替换为 \\接着将 % 替换为 \%最后将 _ 替换为 \_ return input; }然后SQL改成SELECT * FROM products WHERE product_name LIKE CONCAT(%, #{keyword}, %) ESCAPE \\或者干脆用ESCAPE $自定义转义符代码里就不用双重反斜杠了。这一步是模糊搜索上线前必须检查的很多搜索接口的BUG都出在用户输入恰好等于通配符上面。5.2 场景二中文模糊搜索与全文索引业务里有中文名称搜索比如查“华为手机”你当然可以LIKE %华为%但如果表里有一百万条商品数据这个查询的耗时基本稳定在几百毫秒以上。后来我们在这个表上建了全文索引ALTER TABLE products ADD FULLTEXT INDEX ft_product_name (product_name) WITH PARSER ngram;查询改写为SELECT * FROM products WHERE MATCH(product_name) AGAINST(华为手机 IN BOOLEAN MODE);全文索引配合BOOLEAN MODE支持必须包含、-不能包含等操作语义比 LIKE 更丰富。比如AGAINST(华为 -苹果 IN BOOLEAN MODE)表示必须包含“华为”且不包含“苹果”。注意 ngram 默认分词粒度是2单字搜索可能匹配不到中文全文索引的分词行为需要熟悉一下。5.3 场景三模糊查询慢查询定位一条模糊查询慢了先看EXPLAIN结果重点关注三列列名含义判断标准type访问类型从好到差依次是const、eq_ref、ref、range、index、ALL看到ALL就要警惕key实际用到的索引为NULL说明没走索引rows预估扫描行数大表上超过十万行基本就是慢查询的苗条常见情况是 typeALLrows 好几百万SQL 写法是LIKE %关键词%。优化的顺序建议是先看能否改成前缀匹配不行就上全文索引再不行就上外部搜索。如果数据量只有几十万行覆盖索引和全文索引已经能扛住大部分场景。5.4 常见问题速查表问题现象可能原因解决方案LIKE %abc%查不到以abc开头的记录逻辑没写错但存在大小写敏感排序规则确认字段 collation必要时使用LOWER(col)或改用utf8mb4_unicode_ci查询包含%的记录结果全乱了通配符未转义代码中先转义再拼参数或用ESCAPE模糊匹配结果缺了NULL记录NULL 不参与 LIKE 比较追加OR col IS NULL整数区间用 LIKE 查询很慢隐式转换导致索引失效改用BETWEEN或、200万行表LIKE %xxx%几百毫秒无法利用索引全表扫描改前缀、全文索引、外部搜索用户输入_搜出了不相关数据下划线被当作单字符匹配转义或自定义 escape5.5 动态SQL拼接的边界很多框架允许动态拼接 LIKE 条件比如 Java 的 MyBatis 里if testkeyword ! null and keyword ! AND product_name LIKE CONCAT(%, #{keyword}, %) /if这里用#{keyword}参数绑定是安全的不会SQL注入。但如果你为了图方便用${keyword}直接拼进去那就是把裸字符串塞进了SQL既有注入风险又有通配符乱入风险。实际操作里MyBatis 的CONCAT(%, #{keyword}, %)写法要注意如果keyword本身是%它匹配的是全部记录逻辑上要自己兜底。分页场景还有一个细节LIMIT配合LIKEMySQL 是在扫描完所有匹配行之后再取偏移量的。LIKE %关键字% LIMIT 200000, 20这种深分页代价一样很大因为 MySQL 得先计算前200000行。优化可以用游标分页或者记录上一页的最后一条id改写成WHERE id 上一页最大id LIMIT 20能显著减少扫描量。6. 写在最后的一些体会通配符这东西看着简单实际上每个细节都能牵扯出一串问题。我见过太多线上事故归根结底就是没搞懂%的位置对索引的影响或者没处理用户输入的通配符字符。写SQL的时候先问三个问题这个模糊查询能改前缀匹配吗用户输入里的通配符要不要转义排序规则是不是我预期的那套三个问题想清楚了绝大多数坑都能绕开。如果让我给一个最实用的建议那就是不要在业务代码里手工拼接 LIKE 语句所有动态模糊查询都走参数绑定同时对入参做严格的通配符转义。等这几步都做完了再去想索引和性能优化的事。顺序反过来早晚要出事。