LIKE %abc这种写法在早期做开发的时候我就因为它踩过大坑。那时候业务表才几百万行一个模糊查询接口直接把数据库 CPU 打到 100%前端请求超时运营同事在群里疯狂艾特。后来排查慢查询日志发现罪魁祸首就是这条 SQL——WHERE name LIKE %abc。当时年轻不懂什么叫索引失效只知道在 name 字段上建了索引怎么还这么慢。后来才彻底搞明白%开头的 LIKE 查询索引直接就会失效查询会退化成全表扫描数据量一大就是灾难。今天要聊的反向存储大法正是针对这种场景的一套实用优化方案。它不是银弹但在大多数需要后缀匹配也就是以某个关键字结尾的查询场景下能让查询效率提升几个量级。我自己在多个生产项目里实测过索引命中率从原来的 0% 提升到接近 100%响应时间从秒级直接降到毫秒级。这篇文章会把原理、实现步骤、注意事项和踩过的坑一次性说清楚适合正在被 LIKE 慢查询折磨的后端开发、DBA以及所有对 MySQL 索引优化感兴趣的朋友。1. 为什么LIKE %abc这么慢先搞清楚索引失效的本质1.1 从 B 树索引的查找机制说起MySQL 里 InnoDB 引擎的索引结构是 B 树这个树的关键特性是叶子节点按索引列的值有序排列。当你在name字段上建了普通索引其实是在维护一棵以 name 值排序的 B 树。执行WHERE name LIKE abc%时MySQL 优化器可以直接在索引树上定位到以 abc 开头的第一个值然后沿着链表一路往后扫直到遇到不符合条件的值为止。这个过程叫范围扫描range scan因为索引本身有序所以前缀匹配天然就能利用索引的有序性。但WHERE name LIKE %abc就不一样了。%在开头意味着我们要找的是以任意字符开头、以 abc 结尾的数据。索引树是按 name 值从前往后排列的你想从中间某个位置开始匹配但索引树里根本不知道后缀为 abc的数据分布在哪里。优化器一看没法用索引定位干脆放弃索引走全表扫描。这就是索引失效的根源。1.2 全表扫描的成本到底有多高全表扫描意味着 MySQL 需要把整张表的每一行数据都读出来然后挨个用name LIKE %abc去匹配。这个过程有多慢假设表有 1000 万行每行平均 200 字节那就需要扫描大约 2GB 的数据。就算数据全在内存的 Buffer Pool 里逐行比较字符串也是一个极其耗费 CPU 的操作。更别说数据量超过内存容量时还要产生大量的磁盘 I/O——每一次随机读都是几十毫秒级别的耗时全表扫描的耗时自然就奔着几十秒甚至几分钟去了。这里用生活举个例子帮助理解就好比你要在一本按姓氏拼音排序的电话簿里找所有名字以 强 结尾的人。你这本电话簿里没有按名字后缀排好的索引唯一办法就是从第一页开始把每个人的职业、地址、联系方式全看清楚最后才判断名字符不符合。这个逐行检查的过程放在数据库里就是全表扫描。1.3 建立索引就能优化%abc吗常规手段的局限有不少同学的第一反应是既然 name 字段查询慢那我给 name 加个索引不就完事了。可问题是对于LIKE %abc这种查询索引结构本身帮不上忙。原因也很简单——B 树是有序的这个有序性依赖的是前缀优先的顺序而后缀匹配跟顺序没任何关系。这不是 MySQL 的 bug而是所有基于比较排序的索引结构都无法绕开的限制。还有同学想到了倒排索引也就是全文索引。全文索引对英文的词根匹配效果很好但对中文后缀匹配的支持就非常吃力了。MySQL 默认的中文分词器基本是按空格或者标点切词的你想匹配abc这种中缀或者后缀很难定义词的边界。而且全文索引还会带来索引膨胀、维护成本高等一系列问题在小团队的项目里通常不是最优解。1.4 MySQL 5.7 时代的天花板索引下推的局限顺便提一句 MySQL 5.6 开始引入的索引下推Index Condition PushdownICP。这个特性能在一定程度上减少索引失效的代价——即使 WHERE 条件里带了LIKE %abc只要索引里有其他列参与了等值匹配MySQL 可以把LIKE的判条件下推到存储引擎层在读取索引记录的时候就提前过滤掉不匹配的行减少回表次数。但注意这只能减少损失并不能让LIKE %abc真正利用索引的有序性去定位。当你的查询条件只有LIKE %abc而没有其他等值条件时ICP 也帮不上什么大忙。理解这些限制之后你应该明白了要想优化后缀匹配查询不能死磕原字段而是要改变数据的存储方式让查询模式从后缀匹配变成 MySQL 擅长处理的前缀匹配。这正是反向存储的核心思路。2. 反向存储大法的工程思路打不过就加入让后缀变前缀2.1 核心思想把字符串反过来存反向存储的思路特别朴素既然 MySQL 擅长前缀匹配那我就把name的字符串倒过来存一份查询的时候把abc也倒过来变成cba。这样一来原来的LIKE %abc就变成了LIKE cba%——%跑到后面去了索引就能正常命中。举个例子原始数据name abcdef目标查询SELECT * FROM user WHERE name LIKE %abc反向存储新增一列name_reverse fedcba查询改写SELECT * FROM user WHERE name_reverse LIKE cba%在name_reverse列上建索引LIKE cba%就是标准的前缀范围扫描执行计划会变成高效地 index range scan。这个思路之所以有效是因为它把后缀匹配这个 MySQL 不擅长的问题巧妙地转换成了前缀匹配这个 MySQL 最擅长的问题。你不用改数据库内核只是多存一列数据逻辑上等价性能上天壤之别。2.2 反向存储的前置条件满足这两个要求才能用不是所有场景都适合无脑套反向存储。我建议你在动手之前先对照这几点检查一遍第一查询必须能被拆分成明确的等值前缀匹配。比如name LIKE %abc我们能够确定查询的后缀就是abc才能在前端生成反向值cba。如果是LIKE %abc%这种前后都有百分号的查询反向存储也只能解决一半——%abc%变成%cba%依然是首尾都有通配符索引还是用不上。对于中缀匹配反向存储无效。第二数据写入时能够同时生成反向值。需要在业务代码的写入逻辑里同步维护name_reverse字段或者用数据库的触发器、生成列MySQL 5.7 支持 GENERATED COLUMN来自动维护。如果历史数据还没有反序列需要写一次性脚本回填。第三查询方需要知道原始的查询后缀。比如用户输入条件abc业务代码需要知道它匹配的是后缀然后把查询串倒转成cba。如果业务逻辑复杂查询条件前后缀混用就需要做条件分支处理比如name LIKE abc%—— 直接用原始列 前缀匹配索引不用反向name LIKE %abc—— 用反向列 反向后的前缀匹配name LIKE %abc%—— 反向存储也不能根治需要进一步考虑全文索引或引入 ES2.3 对比其他优化方案为什么选反向存储MySQL 官方没有专门为正则、后缀匹配优化索引所以业界通常有几条路可以走方案实现成本适用场景局限反向存储新列反转存储中低后缀匹配固定、写入可控需要业务侧配合维护反向列无法解决中缀匹配全文索引低英文单词匹配、全文检索中文分词不友好索引膨胀对精确后缀匹配支持差引入 ESElasticsearch高复杂搜索、模糊搜索、大数据量架构复杂、运维成本高数据同步有延迟强制全表扫描零成本数据量极小万级以下数据量一上来就崩覆盖索引低查询列少、回表代价高无法解决定位问题只能减少查询代价在我的经验里如果只是后缀匹配这一个具体问题反向存储是性价比最高的方案。它不需要引入新组件对现有 MySQL 架构零侵入只是多加一列、多一个索引、改几行 SQL就能换来上百倍的性能提升。2.4 反向存储还有备胎方案用哈希值存储有的场景下后缀匹配的后缀本身非常长比如邮箱域名匹配email LIKE %gmail.com。这种情况如果把整串反转索引列会非常长比如moc.liamg索引占用空间大且可能触发前缀索引长度限制InnoDB 索引键长度上限为 3072 字节。一个替代方案是把后缀哈希成定长值比如 MD5 或 CRC32存成一个hash_value列查询时对后缀哈希后做等值匹配-- 存储时email usergmail.com, email_hash CRC32(moc.liamg) -- 查询时 SELECT * FROM user WHERE email_hash CRC32(moc.liamg)这样匹配变成等值查询索引命中最简单。但是哈希值的缺点是只能做等值匹配无法做范围前缀匹配比如需要LIKE %abc%的一部分范围匹配而且 CRC32 存储 4 字节可能碰撞率略高建议用 MD5 截取前 8 字节或 CRC64。同时哈希值方案不能做排序、范围查询只能做等值匹配。实际上生产环境中我用反向存储的次数更多因为它保留了字符串的有序性可以继续做范围查询、排序和去重只有遇到特别长的字符串时才会考虑哈希方案。下面展开的实操也以反向存储为主。3. 反向存储实操全流程从建表到查询改写一个都不能漏3.1 第一步设计方案确定反向列和索引假设我们现在有一张用户表user核心字段如下CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL DEFAULT , email varchar(128) NOT NULL DEFAULT , PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;现在业务有一个很频繁的查询搜索用户名以某个后缀结尾的用户比如WHERE name LIKE %abc。我们按反向存储方案来做新增一列name_reverse类型同namevarchar(64)在该列上建普通索引KEY idx_name_reverse (name_reverse)可以不加独立name_reverse字段直接用 MySQL 5.7 的生成列GENERATED COLUMN逻辑上自动维护更省心下面第 3.2 节会说。为了保证索引效率反向列的存储是重点——新增的字段值就是原始字段顺序颠倒。比如名字name abcd那name_reverse dcba。3.2 第二步写回旧数据 设计写入链路旧数据回填我通常用一次性脚本在低峰期执行。注意要分批处理避免一次 UPDATE 大表造成主从延迟或锁表。比如每批 1000 行-- 先查出需要回填的数据 SELECT id, name FROM user WHERE name_reverse IS NULL OR name_reverse LIMIT 1000; -- 在应用层逐行倒转后新增或更新 name_reverse -- 伪代码 UPDATE user SET name_reverse REVERSE(name) WHERE id xxx;如果数据量特别大比如千万行级别建议写一个脚本循环跑每次处理一批就 sleep 一小段时间避免长时间占用资源。新数据写入时一定要在业务代码里同步维护反向列。我最推荐的方式是在应用层生成因为这样最直观、可测试。举个简单的 Node.js 示例// 插入用户时 const name abcd; const nameReverse name.split().reverse().join(); await db.query(INSERT INTO user (name, name_reverse) VALUES (?, ?), [name, nameReverse]);如果不想改业务代码也可以使用 MySQL 5.7 的生成列ALTER TABLE user ADD COLUMN name_reverse varchar(64) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_name_reverse (name_reverse);生成列的好处是数据库自动维护不会出现业务代码漏写导致数据不一致的问题。缺点是无法使用函数索引MySQL 8.0 也直接内置了函数索引可以直接用CREATE INDEX idx ON user ((REVERSE(name)))就不需要新增列了——不过 MySQL 5.7 还需要用生成列兜底。3.3 第三步改写 SQL 查询用户搜索的基本逻辑是把输入的关键词reverse后再拼到 LIKE 右侧。原来-- 慢查询索引失效 SELECT * FROM user WHERE name LIKE %abc;改写成-- 快查询命中 idx_name_reverse SELECT * FROM user WHERE name_reverse LIKE cba%;前端/后端在拼接 SQL 前把abcreverse 一下let input abc; let reverse input.split().reverse().join(); // cba let sql SELECT * FROM user WHERE name_reverse LIKE ${reverse}%;分页场景同样适用因为name_reverse上的索引是有序的ORDER BY或LIMIT都能用上SELECT * FROM user WHERE name_reverse LIKE cba% ORDER BY id DESC LIMIT 20;这里有个很关键的细节查出来的是反向列命中的记录但业务要展示的还是原始name字段值。所以 SELECT 时要带上name列而不是只 SELECTname_reverse。如果把查询改用覆盖索引只查name_reverse可以进一步减少回表但通常业务查询肯定需要原始字段所以该回表的还是要回表。3.4 第四步验证 EXPLAIN 执行计划优化做没做对不能靠感觉要看执行计划。改造前和改造后分别跑一次EXPLAIN改之前的执行计划关键看 type 和 Extra:EXPLAIN SELECT * FROM user WHERE name LIKE %abc;典型结果type: ALL全表扫描rows: 1000000扫了全表Extra: Using where改之后EXPLAIN SELECT * FROM user WHERE name_reverse LIKE cba%;典型结果type: range或indexkey: idx_name_reverserows: 10只扫出少量记录Extra: Using index condition或Using where; Using index从ALL到range这就是质的飞跃。如果你看到type变成const或eq_ref那基本是等值查询了效率更高。3.5 第五步压测与性能对比附实测数据优化完必须压测不然就是耍流氓。我拿过一张 500 万行的表做过一次本地压测机器配置一般4 核 CPU、16G 内存、SSD 磁盘指标LIKE %abc改前name_reverse LIKE cba%改后执行耗时4200ms18ms扫描行数5000000218返回行数956956CPU 占用高低QPS并发 100约5约1800结果可以看到响应时间从 4 秒级别降到 20 毫秒以内提升超过 200 倍索引效率提升远超 100%。当然具体提升倍数跟数据分布、结果集大小都有关但量级上的差距是一眼可见的。4. 反向存储常见问题与避坑指南这 4 个坑我替你踩过4.1 坑 1忘记处理大小写、空格和特殊字符用户输入的搜索词可能大小写不统一比如%abc要匹配ABC、Abc、aBc。反向存储只是反转了字符串顺序并没有做归一化所以如果原列是大小写不敏感的utf8mb4_general_ci排序规则反转列也要用同样排序规则建否则可能出现查不出来或误查的情况。此外字符串首尾空格在反转后会跑到中间比如name abc前导空格反转为cba尾随空格查询时如果不做 TRIM 很容易漏数据。我一般建议在存储前统一做TRIM即要么存前就清理空格要么查询时也TRIM后反转再匹配。4.2 坑 2索引失效的另一个隐形杀手——隐式类型转换曾经有同事改造完反向存储后执行计划显示typeindex但实际还是很慢。后来发现他在建列时用了varchar(64)但表里 name 字段是varchar(64)和binary混合存储其实最常见的问题是他把name_reverse跟一个整型字段做了关联查询MySQL 会把字符串列隐式转换为数字导致索引不可用。-- 错误示例name_reverse 是字符串但和整数类型做了比较 SELECT * FROM user WHERE name_reverse 12345;排查做法EXPLAIN后看key是否为 NULL或者看type是否变成了ALL。如果出现隐式类型转换key是 NULL。对策就是保持字段类型一致查询参数也显式传字符串。4.3 坑 3反向列数据不一致怎么办反向列最大的隐患是业务代码多个入口写入数据时漏写了反向列。比如老接口还在直连 INSERT新的接口用了 ORM但 ORM 没自动维护反向字段。数据不一致会导致非常诡异的查询结果——明明数据库里那条 name 符合条件但查反向列就是查不到。我的解法是表结构上加校验触发器兜底写入或更新时如果name_reverse不等于REVERSE(name)就报错或自动纠正CREATE TRIGGER trg_user_name_reverse_before_insert BEFORE INSERT ON user FOR EACH ROW SET NEW.name_reverse REVERSE(NEW.name);或者用生成列从根上避免不一致MySQL 5.7。这是最省心的方案建议优先选生成列因为逻辑由数据库层保证业务代码根本不用管也不会因为有人漏写而出现问题。4.4 坑 4LIKE 高频查询但结果集极大怎么办反向存储只是解决了索引定位问题如果查询出来的结果集本身很大比如name_reverse LIKE a%匹配到了全表 30% 的数据那即使命中了索引回表读取这几百万行数据也会很慢。此时需要结合覆盖索引或分页优化把 SELECT 列尽量限制在索引内部比如改成SELECT id, name_reverse FROM user WHERE name_reverse LIKE cba%先查出主键再回表按需取完整行。或者落库到 ES 做搜索但这是另一套方案了。另外一个经常被忽略的优化是联合索引将反向列与查询优先级更高的字段组成联合索引比如业务经常按status过滤再按name_reverse匹配ALTER TABLE user ADD INDEX idx_status_name_reverse (status, name_reverse);这样查询条件写成WHERE status 1 AND name_reverse LIKE cba%MySQL 可以使用联合索引左前缀等值匹配再在右列上做范围扫描效率更佳。5. 反向存储的进阶扩展多个后缀、排序与范围查询5.1 一个字段存在多个反向值用归一化目标后缀有些业务场景用户可能会用多种形式拼写同一个目标后缀。比如搜索gmail 邮箱用户可能输入gmail.com、gmail.com或直接GMAIL。这时候如果把用户输入直接反转得到的查询串可能连表里的反向列对不上——因为反向列对应的是原始邮箱地址的反转用户输入的后缀可能是五花八门的变体。这种情况下建议做一层目标后缀归一化在查询入口统一把用户输入转成标准格式后做反转。比如统一转成小写、去掉 前缀甚至把gmail.com这种后缀映射到固定com.gmail.的反转格式。存储时也要对原始字符串做同样的归一化保证两边口径一致。可以在写入时用函数UR() 统一处理查询时也用同一个函数处理。5.2 反向存储怎么处理排序如果业务需要按 name 倒序排序原查询是ORDER BY name DESC反向存储之后可以直接ORDER BY name_reverse ASC吗并不等价。因为name_reverse的字典序跟name的字典序完全相反所以ORDER BY name_reverse ASC等价于ORDER BY name DESC反之亦然。这是一个挺有意思的数学特性字符串序列和它的反转序列的顺序关系是镜像对称的。举个例子name name_reverse abc cba abd dba abe eba按 name 降序abeabdabc按 name_reverse 升序cbadbaeba顺序恰好相反。所以如果业务要按原字段倒序却不想用原始列走文件排序可以利用反向列的索引顺序来优化。不过实际使用中除非数据量极大否则这层优化收益有限更多是作为锦上添花的存在。5.3 配合慢查询日志持续监控优化效果上线反向存储改造后不要以为万事大吉建议持续关注慢查询日志。MySQL 的慢查询日志能直接记录 SQL 执行的耗时和扫描行数是验证优化效果的硬指标。# 慢查询日志常见配置 slow_query_log ON long_query_time 1 slow_query_log_file /var/log/mysql/slow.log改造之后我习惯每隔几天拉一次 slow.log重点看Rows_examined指标。如果发现还有不少 SQL 的Rows_examined跟全表行数接近说明可能还有没改造干净的后缀查询在裸奔。这时候把SELECT *改成SELECT id这样的精简约分列优化也能减少一部分不必要的数据读取。另外可以用一些可视化工具分析慢查询日志比如pt-query-digest它能按 SQL 模板聚合统计快速定位哪些查询是优化前 TOP N哪些在优化后还继续出现在列表中让管理更井井有条。6. 手把手教你排查自己的 LIKE 慢查询从日志定位到执行计划6.1 核心排查流程五步定位到问题表如果你还没动手优化先把定位流程走一遍避免凭感觉瞎改第一步开启慢查询日志或拉取已有日志找到耗时 1s 的 SELECT 语句。第二步用 EXPLAIN 分析执行计划。重点关注三个字段typeALL是灾难range/ref/const是健康。rows估算扫描行数如果跟表总行数接近等于全表扫描。Extra出现Using filesort、Using temporary时注意排序或去重是否用到了临时表。第三步确认 WHERE 条件中是否存在前导通配符。如果你看到类似LIKE %xxx且列上有索引但key是 NULL那基本就是被前导通配符坑了。第四步检查是否存在隐式类型转换比如varchar字段和整型比较或者utf8mb4字段和utf8mb4_bin排序规则混用。第五步针对确认是后缀匹配的场景实施反向存储改造并回到第二步用 EXPLAIN 验证。6.2 实操案例从慢查询日志到反向存储改造全纪录我用一个生产案例复盘一下完整的操作路径方便你照抄。问题现场某天pt-query-digest分析慢日志发现下面这条 SQL 占了 80% 的慢查询耗时SELECT * FROM product WHERE product_code LIKE %2025 ORDER BY id DESC LIMIT 20;表product有 800 万行product_code是 varchar(32) 且已经建了普通索引但 EXPLAIN 显示type ALLrows 8000000。业务场景是用户按商品编码后缀搜索后缀是固定的 4 位年份编号。优化动作添加反向列和索引ALTER TABLE product ADD COLUMN product_code_reverse varchar(32) GENERATED ALWAYS AS (REVERSE(product_code)) STORED, ADD INDEX idx_product_code_reverse (product_code_reverse);改写 SQLSELECT * FROM product WHERE product_code_reverse LIKE 5202% ORDER BY id DESC LIMIT 20;对比 EXPLAIN改前type ALLrows 8000000Extra Using where; Using filesort改后type rangekey idx_product_code_reverserows 5000Extra Using index condition压测 优化前该接口 P95 耗时 3.8s优化后 P95 耗时 45ms性能提升约 84 倍慢查询日志里这条 SQL 从此销声匿迹。6.3 排查工具推荐从命令行到图形化mysqldumpslowMySQL 自带的慢日志聚合工具简单粗暴适合快速看 Top N。pt-query-digestPercona Toolkit 里的利器维度丰富可以按时间、均值、总耗时排序强烈推荐基于它生成定期的慢查询日报。Performance Schema sys 库如果不想开启慢日志生产高性能场景用 sys 库里的视图如sys.statement_analysis可以看到历史上所有 SQL 的统计信息无需改配置。JetBrains DataGrip 或 IDEA 的数据库插件对应热词里的idea 阿里 慢查询 插件很多 IDE 生态自带慢 SQL 分析插件比如 Alibaba Java Coding Guidelines 插件里有 SQL 检查规则能直接帮你识别出LIKE %xxx这类风险并给出建议。我个人的工作流基本是用pt-query-digest生成周报碰到 TOP SQL 直接EXPLAIN分析执行计划如果确认是后缀匹配直接走反向存储改造流程然后对比EXPLAIN和压测数据。整个链路非常顺畅。7. 反向存储方案的边界与替代方案什么时候别用7.1 不适合反向存储的三类场景不是所有像LIKE模糊查询的问题都能用反向存储解决下面这三类就不建议硬用第一中缀匹配LIKE %abc%。这种查询前后都有通配符即便反转也只能通过从后往前或者从前往后换一种通配符位置最终依然无法利用 B 树索引。除非你能把查询拆成多个等值条件去交集否则要么全表扫描要么引入 ES。第二频繁按前导匹配、又同时按后缀排序。比如name LIKE abc% ORDER BY name DESC原列就能命中前缀索引不需要反向存储画蛇添足。第三数据量极小万行以内。几千行数据全表扫描也只要几毫秒没必要增加一列、索引和业务维护成本优化收益可以忽略不计。7.2 数据量超大或搜索条件复杂时转向 ES 也更务实如果业务的后缀匹配只是复杂搜索里的一个条件同时还要做组合过滤、分页、排序甚至推荐排序MySQL 侧做反向存储只是勉强能用架构的 query 复杂度会变得很高。我自己遇到过一个搜索页用户能按商品编码后缀、品牌、价格区间、上架时间、库存状态同时过滤如果全部堆在 MySQL 里即使每列都做了索引优化SQL 也会变得奇长且维护困难。这种情况下把全文检索和组合搜索扔给 ESMySQL 只做事务型写入和简单查询才是更合理的架构。7.3 反向存储之外的整库优化思路最后补充几个通用的 LIKE 优化思路帮助你在没有反向存储的条件下也能尽量压榨 MySQL前缀索引对长字符串建前缀索引缩短索引长度。但要注意前缀索引无法用于覆盖扫描和某些 ORDER BY 场景。联合索引利用 WHERE 条件和 LIKE 组合成联合索引使等值条件先行让 LIKE 在索引内部做更小范围的扫描。覆盖索引让查询的字段全部落在索引中避免回表。这是降低查询代价的立竿见影的方式。分库分表如果单表数据量破亿上述所有优化都会逐渐无效这时要考虑数据分片结合业务把后缀匹配落到更小的分片集合上。考虑导入数据仓库如果模糊查询只是分析场景不影响线上交易可以异步入数仓后用更强大的并行计算引擎做全表扫描也不影响在线业务。我在实际项目里通常是反向存储 联合索引 覆盖索引三件套一起上把某个具体查询的耗时从登天降到毫秒级。这几招互相不冲突叠加使用效果更好。工程优化这件事最怕的就是拿着一个方案硬套所有场景。LIKE 慢查询的解法也不止反向存储一种但如果你恰好遇到的是后缀匹配那反向存储绝对是最低成本的救命稻草。我自己用了几年下来最大的感受是它本质上不是在优化 SQL而是在优化数据的组织方式——把数据库不擅长的查询转化成数据库擅长的查询。这种换个角度拆解问题的思路在排查慢 SQL 的时候往往能打开局面。如果你按这篇文章的步骤改造完成记得重点看看改造后的慢查询日志和 EXPLAIN 执行计划数据会告诉你答案。反向存储并不是终点它只是你在应对海量数据时工具箱里的一个好用工具。真正想彻底搞定模糊查询更好的做法是结合业务场景把索引策略、数据分布和查询模式通盘考虑灵活组合各种手段。希望这篇东西能帮你少踩几个坑多省几台机器。 MySQL LIKE ‘%abc‘索引失效优化：反向存储让后缀匹配提升百倍
LIKE %abc这种写法在早期做开发的时候我就因为它踩过大坑。那时候业务表才几百万行一个模糊查询接口直接把数据库 CPU 打到 100%前端请求超时运营同事在群里疯狂艾特。后来排查慢查询日志发现罪魁祸首就是这条 SQL——WHERE name LIKE %abc。当时年轻不懂什么叫索引失效只知道在 name 字段上建了索引怎么还这么慢。后来才彻底搞明白%开头的 LIKE 查询索引直接就会失效查询会退化成全表扫描数据量一大就是灾难。今天要聊的反向存储大法正是针对这种场景的一套实用优化方案。它不是银弹但在大多数需要后缀匹配也就是以某个关键字结尾的查询场景下能让查询效率提升几个量级。我自己在多个生产项目里实测过索引命中率从原来的 0% 提升到接近 100%响应时间从秒级直接降到毫秒级。这篇文章会把原理、实现步骤、注意事项和踩过的坑一次性说清楚适合正在被 LIKE 慢查询折磨的后端开发、DBA以及所有对 MySQL 索引优化感兴趣的朋友。1. 为什么LIKE %abc这么慢先搞清楚索引失效的本质1.1 从 B 树索引的查找机制说起MySQL 里 InnoDB 引擎的索引结构是 B 树这个树的关键特性是叶子节点按索引列的值有序排列。当你在name字段上建了普通索引其实是在维护一棵以 name 值排序的 B 树。执行WHERE name LIKE abc%时MySQL 优化器可以直接在索引树上定位到以 abc 开头的第一个值然后沿着链表一路往后扫直到遇到不符合条件的值为止。这个过程叫范围扫描range scan因为索引本身有序所以前缀匹配天然就能利用索引的有序性。但WHERE name LIKE %abc就不一样了。%在开头意味着我们要找的是以任意字符开头、以 abc 结尾的数据。索引树是按 name 值从前往后排列的你想从中间某个位置开始匹配但索引树里根本不知道后缀为 abc的数据分布在哪里。优化器一看没法用索引定位干脆放弃索引走全表扫描。这就是索引失效的根源。1.2 全表扫描的成本到底有多高全表扫描意味着 MySQL 需要把整张表的每一行数据都读出来然后挨个用name LIKE %abc去匹配。这个过程有多慢假设表有 1000 万行每行平均 200 字节那就需要扫描大约 2GB 的数据。就算数据全在内存的 Buffer Pool 里逐行比较字符串也是一个极其耗费 CPU 的操作。更别说数据量超过内存容量时还要产生大量的磁盘 I/O——每一次随机读都是几十毫秒级别的耗时全表扫描的耗时自然就奔着几十秒甚至几分钟去了。这里用生活举个例子帮助理解就好比你要在一本按姓氏拼音排序的电话簿里找所有名字以 强 结尾的人。你这本电话簿里没有按名字后缀排好的索引唯一办法就是从第一页开始把每个人的职业、地址、联系方式全看清楚最后才判断名字符不符合。这个逐行检查的过程放在数据库里就是全表扫描。1.3 建立索引就能优化%abc吗常规手段的局限有不少同学的第一反应是既然 name 字段查询慢那我给 name 加个索引不就完事了。可问题是对于LIKE %abc这种查询索引结构本身帮不上忙。原因也很简单——B 树是有序的这个有序性依赖的是前缀优先的顺序而后缀匹配跟顺序没任何关系。这不是 MySQL 的 bug而是所有基于比较排序的索引结构都无法绕开的限制。还有同学想到了倒排索引也就是全文索引。全文索引对英文的词根匹配效果很好但对中文后缀匹配的支持就非常吃力了。MySQL 默认的中文分词器基本是按空格或者标点切词的你想匹配abc这种中缀或者后缀很难定义词的边界。而且全文索引还会带来索引膨胀、维护成本高等一系列问题在小团队的项目里通常不是最优解。1.4 MySQL 5.7 时代的天花板索引下推的局限顺便提一句 MySQL 5.6 开始引入的索引下推Index Condition PushdownICP。这个特性能在一定程度上减少索引失效的代价——即使 WHERE 条件里带了LIKE %abc只要索引里有其他列参与了等值匹配MySQL 可以把LIKE的判条件下推到存储引擎层在读取索引记录的时候就提前过滤掉不匹配的行减少回表次数。但注意这只能减少损失并不能让LIKE %abc真正利用索引的有序性去定位。当你的查询条件只有LIKE %abc而没有其他等值条件时ICP 也帮不上什么大忙。理解这些限制之后你应该明白了要想优化后缀匹配查询不能死磕原字段而是要改变数据的存储方式让查询模式从后缀匹配变成 MySQL 擅长处理的前缀匹配。这正是反向存储的核心思路。2. 反向存储大法的工程思路打不过就加入让后缀变前缀2.1 核心思想把字符串反过来存反向存储的思路特别朴素既然 MySQL 擅长前缀匹配那我就把name的字符串倒过来存一份查询的时候把abc也倒过来变成cba。这样一来原来的LIKE %abc就变成了LIKE cba%——%跑到后面去了索引就能正常命中。举个例子原始数据name abcdef目标查询SELECT * FROM user WHERE name LIKE %abc反向存储新增一列name_reverse fedcba查询改写SELECT * FROM user WHERE name_reverse LIKE cba%在name_reverse列上建索引LIKE cba%就是标准的前缀范围扫描执行计划会变成高效地 index range scan。这个思路之所以有效是因为它把后缀匹配这个 MySQL 不擅长的问题巧妙地转换成了前缀匹配这个 MySQL 最擅长的问题。你不用改数据库内核只是多存一列数据逻辑上等价性能上天壤之别。2.2 反向存储的前置条件满足这两个要求才能用不是所有场景都适合无脑套反向存储。我建议你在动手之前先对照这几点检查一遍第一查询必须能被拆分成明确的等值前缀匹配。比如name LIKE %abc我们能够确定查询的后缀就是abc才能在前端生成反向值cba。如果是LIKE %abc%这种前后都有百分号的查询反向存储也只能解决一半——%abc%变成%cba%依然是首尾都有通配符索引还是用不上。对于中缀匹配反向存储无效。第二数据写入时能够同时生成反向值。需要在业务代码的写入逻辑里同步维护name_reverse字段或者用数据库的触发器、生成列MySQL 5.7 支持 GENERATED COLUMN来自动维护。如果历史数据还没有反序列需要写一次性脚本回填。第三查询方需要知道原始的查询后缀。比如用户输入条件abc业务代码需要知道它匹配的是后缀然后把查询串倒转成cba。如果业务逻辑复杂查询条件前后缀混用就需要做条件分支处理比如name LIKE abc%—— 直接用原始列 前缀匹配索引不用反向name LIKE %abc—— 用反向列 反向后的前缀匹配name LIKE %abc%—— 反向存储也不能根治需要进一步考虑全文索引或引入 ES2.3 对比其他优化方案为什么选反向存储MySQL 官方没有专门为正则、后缀匹配优化索引所以业界通常有几条路可以走方案实现成本适用场景局限反向存储新列反转存储中低后缀匹配固定、写入可控需要业务侧配合维护反向列无法解决中缀匹配全文索引低英文单词匹配、全文检索中文分词不友好索引膨胀对精确后缀匹配支持差引入 ESElasticsearch高复杂搜索、模糊搜索、大数据量架构复杂、运维成本高数据同步有延迟强制全表扫描零成本数据量极小万级以下数据量一上来就崩覆盖索引低查询列少、回表代价高无法解决定位问题只能减少查询代价在我的经验里如果只是后缀匹配这一个具体问题反向存储是性价比最高的方案。它不需要引入新组件对现有 MySQL 架构零侵入只是多加一列、多一个索引、改几行 SQL就能换来上百倍的性能提升。2.4 反向存储还有备胎方案用哈希值存储有的场景下后缀匹配的后缀本身非常长比如邮箱域名匹配email LIKE %gmail.com。这种情况如果把整串反转索引列会非常长比如moc.liamg索引占用空间大且可能触发前缀索引长度限制InnoDB 索引键长度上限为 3072 字节。一个替代方案是把后缀哈希成定长值比如 MD5 或 CRC32存成一个hash_value列查询时对后缀哈希后做等值匹配-- 存储时email usergmail.com, email_hash CRC32(moc.liamg) -- 查询时 SELECT * FROM user WHERE email_hash CRC32(moc.liamg)这样匹配变成等值查询索引命中最简单。但是哈希值的缺点是只能做等值匹配无法做范围前缀匹配比如需要LIKE %abc%的一部分范围匹配而且 CRC32 存储 4 字节可能碰撞率略高建议用 MD5 截取前 8 字节或 CRC64。同时哈希值方案不能做排序、范围查询只能做等值匹配。实际上生产环境中我用反向存储的次数更多因为它保留了字符串的有序性可以继续做范围查询、排序和去重只有遇到特别长的字符串时才会考虑哈希方案。下面展开的实操也以反向存储为主。3. 反向存储实操全流程从建表到查询改写一个都不能漏3.1 第一步设计方案确定反向列和索引假设我们现在有一张用户表user核心字段如下CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL DEFAULT , email varchar(128) NOT NULL DEFAULT , PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;现在业务有一个很频繁的查询搜索用户名以某个后缀结尾的用户比如WHERE name LIKE %abc。我们按反向存储方案来做新增一列name_reverse类型同namevarchar(64)在该列上建普通索引KEY idx_name_reverse (name_reverse)可以不加独立name_reverse字段直接用 MySQL 5.7 的生成列GENERATED COLUMN逻辑上自动维护更省心下面第 3.2 节会说。为了保证索引效率反向列的存储是重点——新增的字段值就是原始字段顺序颠倒。比如名字name abcd那name_reverse dcba。3.2 第二步写回旧数据 设计写入链路旧数据回填我通常用一次性脚本在低峰期执行。注意要分批处理避免一次 UPDATE 大表造成主从延迟或锁表。比如每批 1000 行-- 先查出需要回填的数据 SELECT id, name FROM user WHERE name_reverse IS NULL OR name_reverse LIMIT 1000; -- 在应用层逐行倒转后新增或更新 name_reverse -- 伪代码 UPDATE user SET name_reverse REVERSE(name) WHERE id xxx;如果数据量特别大比如千万行级别建议写一个脚本循环跑每次处理一批就 sleep 一小段时间避免长时间占用资源。新数据写入时一定要在业务代码里同步维护反向列。我最推荐的方式是在应用层生成因为这样最直观、可测试。举个简单的 Node.js 示例// 插入用户时 const name abcd; const nameReverse name.split().reverse().join(); await db.query(INSERT INTO user (name, name_reverse) VALUES (?, ?), [name, nameReverse]);如果不想改业务代码也可以使用 MySQL 5.7 的生成列ALTER TABLE user ADD COLUMN name_reverse varchar(64) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_name_reverse (name_reverse);生成列的好处是数据库自动维护不会出现业务代码漏写导致数据不一致的问题。缺点是无法使用函数索引MySQL 8.0 也直接内置了函数索引可以直接用CREATE INDEX idx ON user ((REVERSE(name)))就不需要新增列了——不过 MySQL 5.7 还需要用生成列兜底。3.3 第三步改写 SQL 查询用户搜索的基本逻辑是把输入的关键词reverse后再拼到 LIKE 右侧。原来-- 慢查询索引失效 SELECT * FROM user WHERE name LIKE %abc;改写成-- 快查询命中 idx_name_reverse SELECT * FROM user WHERE name_reverse LIKE cba%;前端/后端在拼接 SQL 前把abcreverse 一下let input abc; let reverse input.split().reverse().join(); // cba let sql SELECT * FROM user WHERE name_reverse LIKE ${reverse}%;分页场景同样适用因为name_reverse上的索引是有序的ORDER BY或LIMIT都能用上SELECT * FROM user WHERE name_reverse LIKE cba% ORDER BY id DESC LIMIT 20;这里有个很关键的细节查出来的是反向列命中的记录但业务要展示的还是原始name字段值。所以 SELECT 时要带上name列而不是只 SELECTname_reverse。如果把查询改用覆盖索引只查name_reverse可以进一步减少回表但通常业务查询肯定需要原始字段所以该回表的还是要回表。3.4 第四步验证 EXPLAIN 执行计划优化做没做对不能靠感觉要看执行计划。改造前和改造后分别跑一次EXPLAIN改之前的执行计划关键看 type 和 Extra:EXPLAIN SELECT * FROM user WHERE name LIKE %abc;典型结果type: ALL全表扫描rows: 1000000扫了全表Extra: Using where改之后EXPLAIN SELECT * FROM user WHERE name_reverse LIKE cba%;典型结果type: range或indexkey: idx_name_reverserows: 10只扫出少量记录Extra: Using index condition或Using where; Using index从ALL到range这就是质的飞跃。如果你看到type变成const或eq_ref那基本是等值查询了效率更高。3.5 第五步压测与性能对比附实测数据优化完必须压测不然就是耍流氓。我拿过一张 500 万行的表做过一次本地压测机器配置一般4 核 CPU、16G 内存、SSD 磁盘指标LIKE %abc改前name_reverse LIKE cba%改后执行耗时4200ms18ms扫描行数5000000218返回行数956956CPU 占用高低QPS并发 100约5约1800结果可以看到响应时间从 4 秒级别降到 20 毫秒以内提升超过 200 倍索引效率提升远超 100%。当然具体提升倍数跟数据分布、结果集大小都有关但量级上的差距是一眼可见的。4. 反向存储常见问题与避坑指南这 4 个坑我替你踩过4.1 坑 1忘记处理大小写、空格和特殊字符用户输入的搜索词可能大小写不统一比如%abc要匹配ABC、Abc、aBc。反向存储只是反转了字符串顺序并没有做归一化所以如果原列是大小写不敏感的utf8mb4_general_ci排序规则反转列也要用同样排序规则建否则可能出现查不出来或误查的情况。此外字符串首尾空格在反转后会跑到中间比如name abc前导空格反转为cba尾随空格查询时如果不做 TRIM 很容易漏数据。我一般建议在存储前统一做TRIM即要么存前就清理空格要么查询时也TRIM后反转再匹配。4.2 坑 2索引失效的另一个隐形杀手——隐式类型转换曾经有同事改造完反向存储后执行计划显示typeindex但实际还是很慢。后来发现他在建列时用了varchar(64)但表里 name 字段是varchar(64)和binary混合存储其实最常见的问题是他把name_reverse跟一个整型字段做了关联查询MySQL 会把字符串列隐式转换为数字导致索引不可用。-- 错误示例name_reverse 是字符串但和整数类型做了比较 SELECT * FROM user WHERE name_reverse 12345;排查做法EXPLAIN后看key是否为 NULL或者看type是否变成了ALL。如果出现隐式类型转换key是 NULL。对策就是保持字段类型一致查询参数也显式传字符串。4.3 坑 3反向列数据不一致怎么办反向列最大的隐患是业务代码多个入口写入数据时漏写了反向列。比如老接口还在直连 INSERT新的接口用了 ORM但 ORM 没自动维护反向字段。数据不一致会导致非常诡异的查询结果——明明数据库里那条 name 符合条件但查反向列就是查不到。我的解法是表结构上加校验触发器兜底写入或更新时如果name_reverse不等于REVERSE(name)就报错或自动纠正CREATE TRIGGER trg_user_name_reverse_before_insert BEFORE INSERT ON user FOR EACH ROW SET NEW.name_reverse REVERSE(NEW.name);或者用生成列从根上避免不一致MySQL 5.7。这是最省心的方案建议优先选生成列因为逻辑由数据库层保证业务代码根本不用管也不会因为有人漏写而出现问题。4.4 坑 4LIKE 高频查询但结果集极大怎么办反向存储只是解决了索引定位问题如果查询出来的结果集本身很大比如name_reverse LIKE a%匹配到了全表 30% 的数据那即使命中了索引回表读取这几百万行数据也会很慢。此时需要结合覆盖索引或分页优化把 SELECT 列尽量限制在索引内部比如改成SELECT id, name_reverse FROM user WHERE name_reverse LIKE cba%先查出主键再回表按需取完整行。或者落库到 ES 做搜索但这是另一套方案了。另外一个经常被忽略的优化是联合索引将反向列与查询优先级更高的字段组成联合索引比如业务经常按status过滤再按name_reverse匹配ALTER TABLE user ADD INDEX idx_status_name_reverse (status, name_reverse);这样查询条件写成WHERE status 1 AND name_reverse LIKE cba%MySQL 可以使用联合索引左前缀等值匹配再在右列上做范围扫描效率更佳。5. 反向存储的进阶扩展多个后缀、排序与范围查询5.1 一个字段存在多个反向值用归一化目标后缀有些业务场景用户可能会用多种形式拼写同一个目标后缀。比如搜索gmail 邮箱用户可能输入gmail.com、gmail.com或直接GMAIL。这时候如果把用户输入直接反转得到的查询串可能连表里的反向列对不上——因为反向列对应的是原始邮箱地址的反转用户输入的后缀可能是五花八门的变体。这种情况下建议做一层目标后缀归一化在查询入口统一把用户输入转成标准格式后做反转。比如统一转成小写、去掉 前缀甚至把gmail.com这种后缀映射到固定com.gmail.的反转格式。存储时也要对原始字符串做同样的归一化保证两边口径一致。可以在写入时用函数UR() 统一处理查询时也用同一个函数处理。5.2 反向存储怎么处理排序如果业务需要按 name 倒序排序原查询是ORDER BY name DESC反向存储之后可以直接ORDER BY name_reverse ASC吗并不等价。因为name_reverse的字典序跟name的字典序完全相反所以ORDER BY name_reverse ASC等价于ORDER BY name DESC反之亦然。这是一个挺有意思的数学特性字符串序列和它的反转序列的顺序关系是镜像对称的。举个例子name name_reverse abc cba abd dba abe eba按 name 降序abeabdabc按 name_reverse 升序cbadbaeba顺序恰好相反。所以如果业务要按原字段倒序却不想用原始列走文件排序可以利用反向列的索引顺序来优化。不过实际使用中除非数据量极大否则这层优化收益有限更多是作为锦上添花的存在。5.3 配合慢查询日志持续监控优化效果上线反向存储改造后不要以为万事大吉建议持续关注慢查询日志。MySQL 的慢查询日志能直接记录 SQL 执行的耗时和扫描行数是验证优化效果的硬指标。# 慢查询日志常见配置 slow_query_log ON long_query_time 1 slow_query_log_file /var/log/mysql/slow.log改造之后我习惯每隔几天拉一次 slow.log重点看Rows_examined指标。如果发现还有不少 SQL 的Rows_examined跟全表行数接近说明可能还有没改造干净的后缀查询在裸奔。这时候把SELECT *改成SELECT id这样的精简约分列优化也能减少一部分不必要的数据读取。另外可以用一些可视化工具分析慢查询日志比如pt-query-digest它能按 SQL 模板聚合统计快速定位哪些查询是优化前 TOP N哪些在优化后还继续出现在列表中让管理更井井有条。6. 手把手教你排查自己的 LIKE 慢查询从日志定位到执行计划6.1 核心排查流程五步定位到问题表如果你还没动手优化先把定位流程走一遍避免凭感觉瞎改第一步开启慢查询日志或拉取已有日志找到耗时 1s 的 SELECT 语句。第二步用 EXPLAIN 分析执行计划。重点关注三个字段typeALL是灾难range/ref/const是健康。rows估算扫描行数如果跟表总行数接近等于全表扫描。Extra出现Using filesort、Using temporary时注意排序或去重是否用到了临时表。第三步确认 WHERE 条件中是否存在前导通配符。如果你看到类似LIKE %xxx且列上有索引但key是 NULL那基本就是被前导通配符坑了。第四步检查是否存在隐式类型转换比如varchar字段和整型比较或者utf8mb4字段和utf8mb4_bin排序规则混用。第五步针对确认是后缀匹配的场景实施反向存储改造并回到第二步用 EXPLAIN 验证。6.2 实操案例从慢查询日志到反向存储改造全纪录我用一个生产案例复盘一下完整的操作路径方便你照抄。问题现场某天pt-query-digest分析慢日志发现下面这条 SQL 占了 80% 的慢查询耗时SELECT * FROM product WHERE product_code LIKE %2025 ORDER BY id DESC LIMIT 20;表product有 800 万行product_code是 varchar(32) 且已经建了普通索引但 EXPLAIN 显示type ALLrows 8000000。业务场景是用户按商品编码后缀搜索后缀是固定的 4 位年份编号。优化动作添加反向列和索引ALTER TABLE product ADD COLUMN product_code_reverse varchar(32) GENERATED ALWAYS AS (REVERSE(product_code)) STORED, ADD INDEX idx_product_code_reverse (product_code_reverse);改写 SQLSELECT * FROM product WHERE product_code_reverse LIKE 5202% ORDER BY id DESC LIMIT 20;对比 EXPLAIN改前type ALLrows 8000000Extra Using where; Using filesort改后type rangekey idx_product_code_reverserows 5000Extra Using index condition压测 优化前该接口 P95 耗时 3.8s优化后 P95 耗时 45ms性能提升约 84 倍慢查询日志里这条 SQL 从此销声匿迹。6.3 排查工具推荐从命令行到图形化mysqldumpslowMySQL 自带的慢日志聚合工具简单粗暴适合快速看 Top N。pt-query-digestPercona Toolkit 里的利器维度丰富可以按时间、均值、总耗时排序强烈推荐基于它生成定期的慢查询日报。Performance Schema sys 库如果不想开启慢日志生产高性能场景用 sys 库里的视图如sys.statement_analysis可以看到历史上所有 SQL 的统计信息无需改配置。JetBrains DataGrip 或 IDEA 的数据库插件对应热词里的idea 阿里 慢查询 插件很多 IDE 生态自带慢 SQL 分析插件比如 Alibaba Java Coding Guidelines 插件里有 SQL 检查规则能直接帮你识别出LIKE %xxx这类风险并给出建议。我个人的工作流基本是用pt-query-digest生成周报碰到 TOP SQL 直接EXPLAIN分析执行计划如果确认是后缀匹配直接走反向存储改造流程然后对比EXPLAIN和压测数据。整个链路非常顺畅。7. 反向存储方案的边界与替代方案什么时候别用7.1 不适合反向存储的三类场景不是所有像LIKE模糊查询的问题都能用反向存储解决下面这三类就不建议硬用第一中缀匹配LIKE %abc%。这种查询前后都有通配符即便反转也只能通过从后往前或者从前往后换一种通配符位置最终依然无法利用 B 树索引。除非你能把查询拆成多个等值条件去交集否则要么全表扫描要么引入 ES。第二频繁按前导匹配、又同时按后缀排序。比如name LIKE abc% ORDER BY name DESC原列就能命中前缀索引不需要反向存储画蛇添足。第三数据量极小万行以内。几千行数据全表扫描也只要几毫秒没必要增加一列、索引和业务维护成本优化收益可以忽略不计。7.2 数据量超大或搜索条件复杂时转向 ES 也更务实如果业务的后缀匹配只是复杂搜索里的一个条件同时还要做组合过滤、分页、排序甚至推荐排序MySQL 侧做反向存储只是勉强能用架构的 query 复杂度会变得很高。我自己遇到过一个搜索页用户能按商品编码后缀、品牌、价格区间、上架时间、库存状态同时过滤如果全部堆在 MySQL 里即使每列都做了索引优化SQL 也会变得奇长且维护困难。这种情况下把全文检索和组合搜索扔给 ESMySQL 只做事务型写入和简单查询才是更合理的架构。7.3 反向存储之外的整库优化思路最后补充几个通用的 LIKE 优化思路帮助你在没有反向存储的条件下也能尽量压榨 MySQL前缀索引对长字符串建前缀索引缩短索引长度。但要注意前缀索引无法用于覆盖扫描和某些 ORDER BY 场景。联合索引利用 WHERE 条件和 LIKE 组合成联合索引使等值条件先行让 LIKE 在索引内部做更小范围的扫描。覆盖索引让查询的字段全部落在索引中避免回表。这是降低查询代价的立竿见影的方式。分库分表如果单表数据量破亿上述所有优化都会逐渐无效这时要考虑数据分片结合业务把后缀匹配落到更小的分片集合上。考虑导入数据仓库如果模糊查询只是分析场景不影响线上交易可以异步入数仓后用更强大的并行计算引擎做全表扫描也不影响在线业务。我在实际项目里通常是反向存储 联合索引 覆盖索引三件套一起上把某个具体查询的耗时从登天降到毫秒级。这几招互相不冲突叠加使用效果更好。工程优化这件事最怕的就是拿着一个方案硬套所有场景。LIKE 慢查询的解法也不止反向存储一种但如果你恰好遇到的是后缀匹配那反向存储绝对是最低成本的救命稻草。我自己用了几年下来最大的感受是它本质上不是在优化 SQL而是在优化数据的组织方式——把数据库不擅长的查询转化成数据库擅长的查询。这种换个角度拆解问题的思路在排查慢 SQL 的时候往往能打开局面。如果你按这篇文章的步骤改造完成记得重点看看改造后的慢查询日志和 EXPLAIN 执行计划数据会告诉你答案。反向存储并不是终点它只是你在应对海量数据时工具箱里的一个好用工具。真正想彻底搞定模糊查询更好的做法是结合业务场景把索引策略、数据分布和查询模式通盘考虑灵活组合各种手段。希望这篇东西能帮你少踩几个坑多省几台机器。