MySQL通配符全解析：LIKE使用技巧、索引失效与性能优化实战

MySQL里的通配符说起这个很多人第一反应就是LIKE加%好像它天生就是用来“糊弄”模糊查询的。实际上在真实的业务开发里通配符用得深一点能帮你省不少事用得糙一点却可能把整张表的数据拖垮。我做了十多年数据相关的活MySQL里因为通配符用错导致的慢查询、数据漏查、甚至误更新误删除的案例见过太多今天就把这些年踩过的坑和用顺手的技巧一次性说清楚。这篇文章面向的是在日常开发中已经会写SELECT * FROM t WHERE name LIKE %张三%这类查询的人还有对那些想搞清楚“为什么我加了通配符之后查询就慢得离谱”的运维和开发同学。文章会覆盖通配符的基础语义、搭配场景、转义规则、性能影响、正则扩展这几个层面最后附上我积累的排错经验和速查表你可以直接照着抄作业。1. 通配符到底是什么能解决什么问题1.1%百分号的匹配语义%表示匹配任意数量的字符包括零个字符。这句定义看起来简单实际上很多人对它存在一个理解偏差它不只是匹配“字符串的一部分”而是匹配“任意位置出现的模式”。比如LIKE %abc%会命中所有包含abc的记录不管abc出现在开头、中间还是结尾。我在实际开发里常用它做搜索输入框的过滤条件。比如订单号检索用户输入了SO2024后台拼接的 SQL 就是SELECT * FROM orders WHERE order_no LIKE %SO2024%;这样用户不用输入完整的订单号也能查出来。但要注意的是这种写法一旦数据量上来问题就暴露了。这里先留个悬念后面的性能章节我会详细说为什么。1.2_下划线匹配单个字符_这个符号看起来简单但它的作用很容易被误解。它匹配且仅匹配一个字符而且这个字符可以是任意值。举个例子SELECT * FROM employees WHERE emp_name LIKE 张_;这条语句只会命中姓名为“张”字开头的两个字的人比如“张伟”“张霞”但不会匹配“张大伟”。张_等价于“张”加任意一个字符总共正好两个字符。如果你想匹配“张三丰”这种三个字的就得写成张__两个下划线代表两个任意字符。这个语义在数据清洗和格式匹配场景里特别有用。我之前处理过一批手机号数据需要找出中间四位被屏蔽的记录比如138****1234这样的格式直接LIKE %****%会误伤好数据。正确做法是利用下划线和百分号组合SELECT * FROM user_phones WHERE phone LIKE 138____1234;这里四个下划线精确占位既能识别脱敏格式又不会把别的号码卷进来。1.3 通配符拼接与取反查询通配符不只能单独用还能组合进字符串拼接逻辑里。比如在存储过程或动态 SQL 中常常需要把传入的参数拼进LIKE语句SET kw CONCAT(%, 测试, %); SELECT * FROM article WHERE title LIKE kw;这种方式比直接写死%测试%更灵活尤其适合分页查询中筛选条件动态变化的场景。再说取反——NOT LIKE和LIKE是配套的但很多人忽略了一点NOT LIKE不会排除NULL值。也就是说如果某行某字段是NULL无论你用NOT LIKE %xx%怎么过滤这一行都不会出现在结果里。这个特性在数据统计时很容易造成漏数处理时必须额外加上OR field IS NULL之类的显式条件。2. 实战场景拆解从商品检索到编号规则匹配2.1 场景一商品名称模糊搜索电商后台的商品搜索是最典型的通配符应用。用户输入“华为手机”前端传参到后端后端可能同时匹配商品名称、品牌、卖点等多个字段SELECT sku_id, sku_name, brand_name FROM product_sku WHERE sku_name LIKE %华为% OR brand_name LIKE %华为% OR selling_point LIKE %华为% ORDER BY sales_volume DESC LIMIT 20;这里有个细节brand_name如果已经是确定性的枚举字段完全没必要用LIKE用就能命中索引。把通配符用在不该用的字段上是新手最常见的性能浪费。2.2 场景二用户名开放搜索很多系统的用户在“找人”功能里支持输入关键字模糊匹配。这种情况下如果直接LIKE %关键字%去扫用户表几百万用户量级的表会直接卡住。我的做法是先判断输入长度短的用前缀匹配长的再做全文检索或者干脆引入搜索引擎而不是死磕 MySQL。-- 短关键字前缀匹配能走索引 SELECT * FROM users WHERE username LIKE alice%; -- 长关键字谨慎使用全模糊尽量限流 SELECT * FROM users WHERE username LIKE %alice% LIMIT 50;2.3 场景三订单号、车牌号等编码规则匹配编码规则匹配是通配符的进阶用法。比如要查所有 2024 年 3 月生成的、并且第 4 位是字母 A 的订单号直接组合多个通配符即可SELECT * FROM orders WHERE order_no LIKE 202403_A%;这里下划线精确占住第 7 位字符假设订单号规则是年月日_类型编号百分号匹配剩余部分。这种查询在数据稽核和业务对账时特别常用也比单纯用SUBSTRING(order_no, 7, 1) A更直观而且能用得上索引前提是别在开头放%。3. 通配符的隐藏规则与转义处理3.1 转义符ESCAPE的正确用法当你要搜索的内容本身包含%或_时直接写LIKE %50%%大概率会把所有包含50的记录全捞出来而你想找的其实是“50%折扣”这种字面量。正确做法是声明一个转义字符SELECT * FROM promotion WHERE title LIKE %50\%% ESCAPE \;这条语句里\%表示字面意义的百分号最后的%才是通配符。同理要匹配下划线写作\_。MySQL 默认允许用反斜杠作为转义符但在某些框架或驱动环境下反斜杠本身也可能被转义导致 SQL 行为诡异。这时候建议显式写ESCAPE !这类自定义字符降低冲突概率SELECT * FROM promotion WHERE title LIKE %50!%% ESCAPE !;我实际维护过一个优惠券系统活动名称里经常带%当时一批开发同学没写ESCAPE导致活动筛选漏数据后来统一改成显式转义才消停。这不是理论问题是真实事故。3.2 排序规则对大小写匹配的影响MySQL 里LIKE是否区分大小写取决于字段的 collation排序规则。默认的utf8mb4_general_ci和utf8mb4_0900_ai_ci中的ci表示case insensitive也就是不区分大小写。因此SELECT * FROM user WHERE nickname LIKE abc%;能同时命中abc、ABC、Abc。如果你需要精确区分大小写要么把字段的 collation 改成utf8mb4_bin要么用LIKE BINARY强制二进制度比较SELECT * FROM user WHERE nickname LIKE BINARY abc%;这一点在账号校验类业务里很容易踩坑。用户明明注册的是Alice结果别人搜alice也能搜出来有时候反而不符合产品预期。3.3 NULL 与空字符串的边界处理通配符不匹配NULL但很多人以为LIKE %%能匹配所有记录。实际上%%只能匹配空字符串和非NULL字段。测试下来SELECT NULL LIKE %%; -- 结果是 NULL不是 1 SELECT LIKE %%; -- 结果是 1所以如果你写了一条更新语句UPDATE t SET status x WHERE code LIKE %%;你以为会更新所有行实际上code为NULL的那些行根本不会更新。这种逻辑错误在数据订正时常发生正确的写法应该是UPDATE t SET status x WHERE code IS NOT NULL;排查问题时我一般先确认字段是否允许NULL这比盯着通配符本身更重要。4. 性能影响为什么前导通配符这么慢4.1 索引失效的原理这是通配符最核心的实战问题。WHERE name LIKE abc%这种写法如果name字段上有 BTree 索引MySQL 优化器可以利用索引做 range 扫描查询效率很高。但一旦写成WHERE name LIKE %abc%因为目标字符串可能在任意位置出现索引的有序性就失去了意义优化器只能老老实实全表扫描。你用生活类比来理解字典按首字母顺序编排你可以快速翻到所有“张”开头的人名但你没法快速找到所有第二个字是“三”的人名只能从头到尾翻一遍。这就解释了为什么同样的表前缀查询毫秒级返回换成%关键字%就秒级甚至更慢。大数据量场景比如千万行以上全表扫描的代价极其昂贵。4.2 慢查询排查与优化遇到LIKE %xxx%导致的慢查询我通常按以下顺序处理判断业务是否能接受前缀查询能则把%移到后面。如果必须包含中间模糊匹配且数据量不大百万以内可考虑改用LOCATE或INSTR函数判断是否存在SELECT * FROM article WHERE LOCATE(关键词, title) 0;这两个函数虽然也是扫描但某些复杂场景下比LIKE拼接更可控。注意它们同样无法走索引。数据量大且对模糊检索有强需求时方案从 MySQL 本身转移到全文索引。MySQL 自带FULLTEXT INDEX支持MATCH ... AGAINST语法针对中文得用 ngram 插件ALTER TABLE article ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram; SELECT * FROM article WHERE MATCH(title) AGAINST(关键词 IN NATURAL LANGUAGE MODE);如果全文索引也不满足就引外部搜索引擎比如 Elasticsearch。早期硬扛后期迁移的例子我见过太多不如一开始就规划好。4.3 配合EXPLAIN判断是否走索引排查慢查询时我最常用的手段是EXPLAIN。看到type列是ALLrows估算很高基本就断定是全表扫描。强制使用索引还可以用FORCE INDEX临时验证但真正的解决思路应该是改写 SQL。EXPLAIN SELECT * FROM employees WHERE emp_name LIKE %小明%; -- 如果 type 为 ALL说明没走索引另外还有一个冷门技巧如果只需要判断某条件是否存在可以用EXISTS配合LIKE字面常量缩短结果集例如SELECT EXISTS(SELECT 1 FROM employees WHERE emp_name LIKE %小明%);这种写法比COUNT(*)高效因为到第一条命中就返回了。5. 正则通配符 REGEXP更高级的模糊匹配5.1 元字符基础LIKE只能表达简单的模糊关系当匹配规则复杂时需要动用正则表达式。MySQL 的REGEXP操作符支持常见的正则语法.匹配任意单个字符^匹配开头$匹配结尾[abc]匹配括号内任一字符[^abc]匹配不在括号内的任意字符*、、?分别匹配前一个字符的零次或多次、一次或多次、零次或一次{n}精确匹配 n 次|表示或逻辑举个例子想查找所有名字里带“张”或“李”的员工SELECT * FROM employees WHERE emp_name REGEXP ^[张李];这会命中所有以“张”或“李”开头的名字。如果你想确认某个手机号是否符合 1 开头、第二位是 3/5/7/8/9 的规则SELECT phone FROM user_phones WHERE phone REGEXP ^1[35789][0-9]{9}$;5.2 LIKE 与 REGEXP 的选择LIKE和REGEXP不是替代关系各有用武之地。LIKE简单直观而且前缀模式能走索引REGEXP表达能力强但基本不可能走索引查询性能更差所以尽量只在小数据量或辅助筛选场景下用。我还见过一个坑REGEXP匹配子串时如果没写^和$它只需要部分命中就返回true。很多人误以为它和LIKE一样是全文精确匹配结果查出完全不相干的数据。比如SELECT MySQL REGEXP SQL; -- 1因为包含 SQL这种行为和正则引擎的“部分匹配”特性有关写查询时一定要清楚逻辑边界。5.3 正则实际案例从日志中筛选异常 ID我处理过一批应用日志要找出所有报错信息中包含数字日期格式的异常请求SELECT request_id, message FROM app_log WHERE message REGEXP 2024-[0-9]{2}-[0-9]{2} .*ERROR.*;正则的好处是能把复杂规则压缩成一行比写十几条LIKE OR干净得多。代价是慢所以这条查询一般只用在离线分析库不会挂在面向用户的实时接口里。6. 常见问题与排查技巧实录6.1 常见问题速查表下面这张表是我长期维护数据库时积累的问题清单直接收藏即可。问题现象可能原因解决方案LIKE %xx%查询极慢前导通配符导致索引失效改前缀匹配、加全文索引、或引入搜索引擎LIKE %50%%结果过多%被当成了通配符使用ESCAPE \转义NOT LIKE排除不了NULL行NULL与任何比较都返回NULL显式加IS NULL条件大小写不敏感导致误匹配字段 collation 为_ci使用COLLATE utf8mb4_bin或BINARY匹配_字面量失败下划线默认是单字符通配符使用\_转义REGEXP匹配结果出乎意料正则部分匹配而非完全匹配用^...$框定边界UPDATE时行数少于预期WHERE里有通配符未命中先SELECT验证再UPDATE6.2 排查流程脚本遇到通配符相关问题我一般会按下面这个流程跑一遍先列出当前表结构和索引情况SHOW CREATE TABLE table_name; SHOW INDEX FROM table_name;查看 SQL 的执行计划EXPLAIN SELECT ... ;如果走了全表扫描考虑改写成前缀匹配或用函数改写用EXPLAIN再验证一次。在测试环境用同样数据规模复制问题再在生产环境小范围试运行避免直接怼线上。6.3 独家避坑技巧有几个很少有人提但极其实用的技巧首先LIKE拼接时要留意 SQL 注入风险。动态构建LIKE条件时一定要把%和_作为参数传递而不是直接拼进字符串尤其要预处理传参。-- 参数化写法 WHERE name LIKE CONCAT(%, ?, %)其次导入大量数据前先搞清楚哪些字段会用于模糊查询提前设计索引策略。我遇到过很多项目上线后才发现报表页全是LIKE %...%再补索引已无济于事只得改架构成本极高。最后IN和LIKE不要混用。想匹配多个前缀用几条OR LIKE组合而不是试图写IN (%a%, %b%)后者语义不对。7. 实操手记一次线上问题修复全流程这里记录一个完整案例。有一回线上反馈商品管理页的搜索接口响应时间到了 8 秒现场EXPLAIN一看核心 SQL 是SELECT product_id, product_name FROM product_sku WHERE product_name LIKE %手机% ORDER BY update_time DESC;表是一千二百万行的商品表product_name上有普通索引但%手机%让索引直接报废typeALLrows估算一百多万排序也走 filesort。我的处理分三步走第一步立刻把接口改成优先走前缀匹配WHERE product_name LIKE 手机% OR product_name LIKE %手机先给用户返回精确前缀和尾缀结果同时异步触发后台任务重建搜索数据缓解当前压力。第二步为搜索的核心场景新增全文索引并把查询改为全文检索ALTER TABLE product_sku ADD FULLTEXT INDEX ft_name (product_name) WITH PARSER ngram;第三步上线前用旧数据压测确认新查询计划走MATCH ... AGAINST后接口耗时降到 200 毫秒以下才放量。这次修复给我最大的启发是通配符的选型必须在设计阶段就参与讨论不要等数据膨胀后才考虑优化否则你会付出几倍的代价。8. 最后分享两个小技巧我个人在实际操作中越来越喜欢用LOCATE和REGEXP_LIKEMySQL 8.0 提供来替代部分LIKE场景。比如要检查某个字段是否包含关键字LOCATE返回位置写WHERE LOCATE(a, col) 0语义更明确不容易跟%搞混。MySQL 8.0 还有REGEXP_INSTR、REGEXP_SUBSTR这类函数处理文本抽取场景比拼接多个LIKE干净得多。另外一个小技巧写完任何带通配符的 SQL先跑一遍SELECT COUNT(*)看看命中的行数是否符合预期再执行真正的UPDATE或DELETE。宁可多花两秒确认也别在凌晨被电话叫醒去处理一条误删数据的善后工作。这是用事故换来的经验。