企业官网建设流程全解析

玩MySQL这些年，用通配符（wildcard）做模糊查询是最常见不过的需求，但也是被问得最多、最容易出幺蛾子的地方。一个刚入行的开发可能第一天就会写LIKE '%关键词%'，但很多人连通配符有几种、各自在什么场景下才安全靠谱、为什么某个查询跑着跑着就慢成蜗牛都没彻底搞明白。今天就把这块掰开揉碎讲一遍，从基础用法到原理分析，再到压箱底的排查经验，一次说清。

这个内容适合人群很广：刚学SQL的学生、写业务代码的初级中高级开发、需要分析线上慢SQL的DBA，只要你跟数据库打过交道，这章绝对有值得收藏的地方。核心就一句话：通配符能解决模糊匹配问题，但前提是你得知道它怎么工作，以及用什么姿势用才不会埋雷。

1. 通配符究竟是什么：一次搞懂四类字符

1.1 先分清通配符与正则的边界

很多新手会把MySQL里的通配符跟正则表达式搞混，实际上它们是两套完全不同的匹配机制。通配符用于LIKE、NOT LIKE操作符，配合%、_、[]、[^]这几个符号使用。而正则表达式是独立的表格驱动机制，通过REGEXP或RLIKE操作符工作，语法复杂得多，表达能力也强得多。

举个直观例子，你想查找名字含有“张”的用户：

SELECT * FROM user WHERE name LIKE '%张%';

如果换成正则就是：

SELECT * FROM user WHERE name REGEXP '张';

两种写法结果可能一样，但底层逻辑不同。通配符的核心是三个基础符号%、_、ESCAPE组合，而正则更像一门微型语言。日常业务里90%的场景不需要上正则，用通配符就够了，正则性能更差且写起来更容易出错。

1.2 三类基本通配符的行为差异对照

我把通配符的核心组合整理成一个表，平时碰到不确定的场景直接对着查。

先说%，这是最常用的，但也是最容易乱用的。%放在开头，意味着查询必须扫描全表每一行的这个字段，没有任何优化余地（后面会详细讲索引的事）。_其实很实用，它严格匹配一个字符，比如你要查三个字的名字最后一个是“伟”，可以写'_伟'，这样两个字的“张伟”就不会被查出来。[]和[^]在很多其他数据库（如SQL Server）里用得很欢，但在MySQL里它们并不能直接用于LIKE操作，这是非常多人踩坑的地方。

1.3 MySQL里[]的坑：为什么总是不生效

如果之前用过SQL Server，到了MySQL上写LIKE '[张王]三'以为能查张三王三，结果返回空集，这不是你的语法有问题，而是MySQL的LIKE根本不支持[]这种字符集合语法。MySQL的LIKE只认三个通配符：%、_和用于转义的ESCAPE字符。这一点太容易被跨数据库经验的人忽视。

如果要在MySQL里实现“张三或王三”这种多字符匹配，有两个替代方案：一个是直接用正则REGEXP '^[张王]三$'，另一个是写多个条件用OR连接。我从实操角度建议，能不用正则在数据量大的时候就别用，因为正则无法走索引，多个OR条件如果每个都走得上索引，优化器还有机会选索引合并，性能反而更好。

2. 通配符的工作原理：匹配的执行路径与取舍

2.1 LIKE匹配究竟在数据库里怎么执行

在讨论通配符能不能走索引之前，得先建立一层底层认知：MySQL的LIKE本质上是一个逐字符的比较过程。

当执行SELECT * FROM user WHERE name LIKE '%张%'时，服务器从存储引擎取出一行，把name字段值取出来，然后在内部实现中，对字符串每个位置尝试用“张”去匹配，没有命中就移动一位，直到字符串末尾。这个过程的复杂度是 O(n*m)，n是字段长度，m是模式串长度（含通配符状态机）。如果字段是短字符串，看不出来什么，但字段一旦变长，比如存了一段文本、一篇博客正文，这速度就感人了。

这里有一个重要的点：带%开头的模糊查询不能使用B+树索引，因为B+树索引是按完整值排序存储的。想象一下字典，你能快速查所有“张”开头的词，因为它们在字典里是连续排在一起的。但“包含张”的词在字典里并没有聚在一起，索引就失效了。这是通配符使用里最核心的原理约束。

2.2 前置通配符与后置通配符：性能差异究竟有多大

很多优化文档都会反复强调一个规则：LIKE '%xxx'和LIKE '%xxx%'不能走索引，而LIKE 'xxx%'可以走索引。但很多人不理解为什么，我来拆开说明。

B+树索引能够加速的范围查询依赖一个先决条件：比较的字符前缀是已知的定值。因为索引键是按照字典序排列的，当你给定一个固定前缀如'张'时，引擎可以二分定位到第一个“张”的位置，然后顺序扫描所有“张”开头的叶子节点。这跟人查字典是一样的道理——你不翻到“张”那几页，而是想找所有中间带“张”的字，那就必须从头到尾翻一遍字典。

实际压测过一个两千万行的用户表，name字段建了普通索引，查询条件如下：

SELECT * FROM user WHERE name LIKE '张%' LIMIT 20; -- 耗时约 0.02s，走 index range scan SELECT * FROM user WHERE name LIKE '%张%' LIMIT 20; -- 耗时约 8.7s，全表扫描，扫了两千万行

差距是几百倍。很多人会误以为数据量不大没关系，但等数据涨上来再发现这个问题就痛苦了。所以只要业务允许，尽量把通配符往后放。

2.3 转义字符：当你要匹配百分号和下划线本身

通配符本身也是合法字符，总有一些场景你需要匹配真实的%或_。比如商品标题里有“50%off”这种字符串，直接写LIKE '%50%off%'，MySQL会把第二个%也当通配符解析，搜出来的结果就不是你要的。

这个时候需要用ESCAPE关键字指定一个转义字符：

SELECT * FROM product WHERE title LIKE '%50\%%' ESCAPE '\';

上面这条SQL的意思是：匹配任意位置、包含“50%”字符串的行。这里\%表示字面意义的百分号字符。默认情况下MySQL的转义字符就是反斜杠\，但为了避免在程序语言中转义嵌套时把SQL搞乱，我更喜欢显式写成ESCAPE '!'这种冷门字符：

SELECT * FROM product WHERE title LIKE '%50!%%' ESCAPE '!';

自己搭过项目的人都懂，Java、Python字符串里处理\会双倍转义，很容易看得眼睛花。换成一个不太可能出现在业务数据里的字符做转义符，SQL可读性直接上一个台阶。

3. 实操过程全记录：从业务需求到SQL落地

3.1 场景建模与初步方案

这章节带着一个实际案例跑一遍完整过程，假设这是一个电商后台系统的商品搜索需求，目前在做一个模拟项目X的商品管理模块，要求支持“商品名称模糊搜索，同时过滤品牌ID和状态”。

SELECT id, title, brand_id, status, price FROM product WHERE title LIKE '%无线耳机%' AND brand_id = 12 AND status = 1 ORDER BY id DESC LIMIT 50;

这段SQL初看没毛病，但实际落地有个隐患：MySQL优化器面对这个查询时，大概率先做brand_id = 12的过滤（走索引），再回头对结果集里的每一行做title LIKE判断。如果品牌12下的商品行数很多，比如几万行，每行都要做一次全字段通配符匹配，性能依然不够理想。这就需要在设计阶段多一步：评估该品牌下的商品数据量。

3.2 为了让模糊查询真正走索引的落地改造

如果搜索词是固定前缀，比如用户习惯输入“无线耳机”开头的型号编码，可以直接让SQL写成：

SELECT id, title, brand_id, status, price FROM product WHERE title LIKE '无线耳机%' AND brand_id = 12 AND status = 1 ORDER BY id DESC LIMIT 50;

只要title上有索引，这条查询就能走Range Scan。但如果是任意词中点搜索，比如输入“耳机”想匹配“无线耳机低音炮”，前缀未知——就不能单纯靠索引解决。

我在这里分享一个经过实际项目检验的方案：冗余字段 + 前缀索引。把商品名称反转后存储到一个新列title_reverse中，比如“无线耳机低音炮”存成“炮音低机耳线无”。查询后缀匹配时，把查询词也反转，变成前缀匹配：

SELECT id, title, ... FROM product WHERE title_reverse LIKE CONCAT(REVERSE('低音炮'), '%')

这个改写把“后缀模糊查询”完美变成了“前缀精确范围查询”，可以正常走索引。注意，反转列需要单独建索引，同时写入时需要同步维护该字段，这是典型的空间换时间。这个方法在真实项目里跑过，三千万行数据，”%低音炮“后缀模糊查找从全表扫描4秒降到了索引扫描0.01秒级别。虽然牺牲了一部分写入性能和存储，但换回的业务体验是实打实的。

3.3 转义场景的完整参数推演

汇报一个真实遇到的业务边界：优惠券系统里，券码格式统一是ABC-123-50%，用户输入框里要么输完整码，要么输部分码，其中百分号是真实字符。需求要求支持“跟用户输入的串做模糊匹配”。最开始有同事写：

SELECT * FROM coupon WHERE code LIKE '%' || '50%' || '%';

这段看起来能匹配“50%”，但仔细推演会发现匹配范围过宽，因为没转义第一个%和第二个%前的%。准确写法应该是：

SELECT * FROM coupon WHERE code LIKE '%50!%%' ESCAPE '!';

推演过程如下：

• %— 匹配任意前缀
• 50— 字面匹配字符“50”
• !%— 转义后的字面百分号
• %— 匹配任意后缀

这条SQL才可以精准命中所有券码中带有“50%”字样的行。如果用户输入的另一段内容里还带下划线，同理需要转义_，转义符统一下来就是!_。这种事单独写一教我建议所有新人都听一遍，否则线上数据被误查了都不知道怎么排查。

3.4 大小写与排序规则对匹配结果的影响

有个刚接手项目的人来问为何MySQL查询“apple”搜不到小写“Apple”的数据。这背后和通配符不是一回事，但会直接影响匹配结果：取决于字段的排序规则（collation）。

MySQL的utf8mb4_general_ci、utf8mb4_unicode_ci这类以_ci结尾的排序规则不区分大小写，所以LIKE 'apple%'能匹配 “Apple”。如果是utf8mb4_bin或utf8mb4_0900_as_cs之类的二进制/大小写敏感规则，LIKE 'apple'就严格区分大小写了。

对通配符匹配来说，一样的规则。也就是说，同样的SQL在不同的库、不同的表结构下可能结果完全不同。排查匹配结果不符合预期时，第一个要看的不是代码逻辑，而是字段的COLLATION属性。常见的坑是建表时用了默认排序规则，后来某张表又单独指定了二进制规则，业务查询在两张表上行为不一致，排查半天最后发现是SHOW CREATE TABLE里多了一行collation配置。

4. 通配符之外的模糊查询方案：全文索引与函数

4.1 什么时候该放弃LIKE，转用全文索引

LIKE '%词%'在前文已经明确无法走正常索引，数据量大时性能极差。其实MySQL对这类痛点有一个内置的解决方案：全文索引（FULLTEXT INDEX）。很多开发者对这个功能不太熟悉，特别是用了多年MySQL都只在用InnoDB默认存储引擎的情况下，以为全文索引是第三方搜索引擎才有的能力。

实际上MySQL自5.6以后InnoDB就支持中文全文索引了，只是中文分词效果没有第三方搜索引擎好。它解决的问题是：“在一大段文本里搜索某个词”，比如文章表、日志表、描述字段，这恰好是LIKE的弱项。

建全文索引：

ALTER TABLE article ADD FULLTEXT INDEX ft_title_body (title, body);

查询时用MATCH ... AGAINST：

SELECT id, title FROM article WHERE MATCH(title, body) AGAINST('数据库优化技巧' IN NATURAL LANGUAGE MODE);

全文索引的原理是倒排索引，把“词”映射到包含它的“文档ID”，查询时不需要扫全表，响应时间随数据量增长的变化也不大。但它对中文分词支持有限，该不该上全文索引需要评估业务。一个折中方案是把需要搜索的文本截断到前几十个字，单独存一列并建全文索引，这样分词的准确度更高，实现成本也不会太大。

4.2 LIKE与INSTR、LOCATE的横向对比

每次讲到模糊查询，总会有人问：LIKE '%str%'和INSTR(field, 'str') > 0哪个性能更好？实际结果是这两个方案底层都是全表扫描，性能差距可以忽略不计。但二者有个行为差异值得注意：LIKE '%str%'讲究通配符语义，空字符串处理比较特殊，空字符串会匹配所有行；而INSTR更接近程序员的子串思维。

SELECT * FROM product WHERE INSTR(title, '无线') > 0;

写起来比LIKE直观。还有一个LOCATE函数，效果类似，只是参数顺序调换：

SELECT * FROM product WHERE LOCATE('无线', title) > 0;

真正建议使用INSTR/LOCATE场景是：匹配关键词里本身带着%或_，用LIKE还得写转义，用INSTR就不用管，直接当普通字符串处理。虽然性能没优势，但代码可风险角度看更“无脑”。

4.3 反向冗余列与生成列：数据量绕不开时的终极方案

数据量超过千万，全文索引也救不了业务（中文分词不准、性能不稳定），这时候往往得从数据建模层面动刀。

第一种方案是上面提到的反向冗余列：

ALTER TABLE product ADD COLUMN search_text VARCHAR(500) GENERATED ALWAYS AS (REVERSE(title)) STORED, ADD INDEX idx_search_text(search_text);

生成列的好处是MySQL自动维护，不需要业务侧额外更新。但生成列不能选择中文分词，只能做字符级别的反转，能够解决的只是后缀匹配问题，比如“模糊匹配结尾某个型号、订单号、统一码”。这类需求业界也经常有。

第二种方案引入外部搜索引擎，比如Elasticsearch。说这个并不是要推倒MySQL，而是明确划界：当数据量处于千万到亿级、搜索条件多变、需要组合多个模糊字段时，MySQL再怎么优化也绕不开扫描成本，外部搜索引擎的倒排索引 + 分片并行真正解决问题。很多公司把MySQL里的全文索引拆到ES，就是为了让核心数据库专注事务，而搜索类型的查询走专门组件。

我在实际项目里见过很多团队，业务数据量刚到千万就急着上ES，结果引入大量运维复杂度，实际效果还不如把搜索条件合理建模成多个精确字段再加反向冗余列。所以方案的选择需要真实压测，别拍脑袋。

5. 通配符常见坑与排查速查表

5.1 LIKE拼SQL时程序语言的转义叠加

这是最普遍的一个隐藏Bug：业务代码里传参、拼接SQL、数据库预处理，每一层都可能做字符串转义。常见这样的报错，明明手动执行SQL没问题，代码跑出来却查不到数据。

典型场景：Java JDBC里使用PreparedStatement，SQL写LIKE ?，参数传%50\%%。应用层做转义时，参数传到数据库实际可能是%50\\%%，结果匹配的逻辑完全不同。我建议全链路统一用参数化查询，不要在SQL里手动拼接用户输入，否则既要提防SQL注入，又要提防转义错乱。参数化查询时，只转义当次查询需要的通配符即可，其余交给驱动层处理。

几个注意点浓缩成清单：

• 局域网内部SQL客户端手动测试时，反斜杠不能默认为转义符，必要时显式ESCAPE '!'
• 业务传参尽量用参数占位符（?）而不是字符串拼接
• 若必须拼接，先对用户输入统一过滤%、_、\三个字符，再拼接LIKE

5.2 下划线和百分号引发的匹配失真

假设数据里有这样一条记录：order_no = 'SN_20241201_001'。查询要求匹配某个前缀'SN_'，有人写了LIKE 'SN_%'，结果发现查出来很多条不相关记录，因为下划线匹配单字符，SN_20241201_001和SNX20241201_001会被同时命中。

正确的写法必须转义：

SELECT * FROM orders WHERE order_no LIKE 'SN!_%' ESCAPE '!';

这其实也是个提醒：任何订单号、编号、批次号这类数据里带下划线的场景，必须全局约定好转义方案，否则后面每个搜索都会踩雷。建议在团队内部沉淀一条规范：所有实险LIKE匹配，只要数据样本包含_或%其中之一，就必须在匹配模式中显式转义。

5.3 NULL字段与通配符匹配的边界

MySQL中NULL是一个特殊存在。LIKE比较操作符遇到NULL会返回NULL，而NULL在WHERE条件中被视为不成立。所以：

SELECT * FROM user WHERE nickname LIKE '%张%';

如果nickname字段是NULL，这行的匹配结果不是“不匹配”，而是“未知”，因此该行不会被返回。这个行为经常导致新人在统计数据时困惑：为什么总数对不上，差了NULL的行数。

如果业务上确实想统计所有无昵称用户，用nickname IS NULL而不是LIKE。同理，使用NOT LIKE时，NULL行同样会被排除在外，需要额外条件补上。

这是个边角知识，但线上数据清洗时是最容易被忽略的“少一行”。

5.4 排查思路：一条慢SQL的死法追踪

聊个实战案例。某后台系统的订单列表页有一天突然响应变慢，查出来是搜索框支持“订单号模糊查询”，SQL长这样：

SELECT * FROM orders WHERE order_no LIKE '%' || #{input} || '%' ORDER BY create_time DESC LIMIT 20;

表面看没毛病，但数据量撑到500万行后，这条%xxx%必然全表扫描，除非把搜索拆成前缀匹配。当时排查过程可以整理成流程：

1. EXPLAIN SELECT ...看到type=ALL，说明全表扫
2. 确认 order_no 明明有索引，但%在首字符导致索引不能走
3. 深度理解业务后，发现订单号其实有明确规则，前12位是日期+渠道编码，后端可以解析出前缀
4. 把用户的输入通过正则拆出前缀部分，拼到LIKE后走LIKE '20241201A%'

改造后查询从3秒多降到20毫秒内。排查这类问题不要上来就加索引，因为%xxx%加了索引也没用，核心是想办法让匹配变成有界前缀，或者从应用层解析出固定前缀。

6. 关于索引优化与通配符配合的补充实战

6.1 组合索引与LIKE碰撞出的额外坑

有时候表上有复合索引(brand_id, title)，数据量大了之后发现用LIKE '%关键词%' AND brand_id=12的查询依旧缓慢。原因是复合索引最左前缀原则：brand_id相等条件下才能用title排序。但如果查询条件把 title 的模糊匹配放在中间，且通配符在前方，索引就废了。正确的做法是在能满足条件的情况下，把复合索引设计成(brand_id, title_prefix)这种形式，即 title 只存前几位，专门供前缀匹配使用，其他模糊部分靠冗余列解决。

实际优化时，一个复合索引里放两个字段还是三个字段，是需要反复测试的。做题跟做工程项目不一样，不能只看B+树原理，要结合业务查询的频率、写入吞吐、查询结果集大小综合考量。

6.2 慢查询日志与通配符的底层观测

遇到线上通配符查询的疑难杂症，最靠谱的工具其实是慢查询日志。MySQL把超过long_query_time阈值的SQL都记录到slow_log表或者文件中，里面包含Rows_examined字段，这个字段能直接反映查询扫描了多少行。

结合Rows_examined与返回行数对比，如果扫描行数远大于返回行数，基本可以断定索引失效或选择不佳。我平时排查模糊查询性能问题的标准动作：

1. 打开SET GLOBAL slow_query_log = ON
2. 临时设置SET GLOBAL long_query_time = 1
3. 让业务跑一段时间，抓出所有耗时超过1秒的语句
4. 逐条用EXPLAIN分析

观察通配符位置和可能的索引情况，实测下来95%的慢查询都能归因到同一个问题：通配符放在开头导致全索引/全表扫描。

7. 我在实际项目中用通配符的心得与建议

踩过这么多坑之后，我总结出的核心经验其实就三条，值得所有写SQL的人刻进脑子里。

第一，通配符尽可能往后放。能写LIKE '前缀%'就不写LIKE '%前缀%'，这不仅仅是性能问题，更是数据准确度问题——前缀匹配往往更贴近业务真实意图。你搜“无线耳机”，用户多半是想找“无线耳机”开头的商品，而不是所有中间带“无线耳机”四个字的垃圾结果。

第二，明确自己的数据规模再决定技术方案。数据量十万级，LIKE '%xxx%'随便用，加个缓存就能扛。数据量百万级，就要开始评估是否要把模糊搜索改成前缀匹配或者上全文索引。数据量千万级，直接考虑反向冗余列或者外部搜索引擎。不同量级用不同武器，是技术选型的基本盘。

第三，通配符转义要形成规范，不能临时抱佛脚。尤其涉及用户输入搜索的场景，项目一开始就应该约定好转义符，所有LIKE查询统一使用ESCAPE '!'，把%、_当普通字符时统一转义。这个规范越早定越省事，等代码写散再回头修补，成本高到不想动。

最后分享一个收尾的小技巧：每次写完带通配符的SQL，养成用EXPLAIN看的习惯。type那列如果是ALL，先想想能不能改成range。这习惯坚持半年，你对通配符性能的敏感度会远超大多数人，线上排查问题时也能更快定位，不慌不忙把SQL一讲，同事瞬间就觉得这人靠谱。