碰过 MySQL 的兄弟多半都有过这种经历数据量一上来一条WHERE name LIKE %abc的查询直接把慢日志打爆DBA 深夜打电话喊你起来看监控。原因其实一句话就能说清——%放在最前面索引就用不上了只能全表扫描。今天要聊的“反向存储大法”是我在生产环境反复用过的优化套路核心逻辑很简单把“以 abc 结尾”的模糊查询倒过来存一份镜像数据再把查询条件改成“以 cba 开头”让索引从完全失效变成完全命中。这套方案特别适合那些大量“按尾缀匹配”的业务场景比如按订单号后缀、按邮箱域名后缀、按文件路径结尾查数据。数据量小的时候无所谓一旦到了百万条以上优化前后可能就是 4 秒和 40 毫秒的区别。这篇文章我会从原理讲到建表、迁移、查询改造最后再把坑挨个说一遍你拿去就能直接用。1. 慢查询现场LIKE %abc 为什么不走索引1.1 从一条慢 SQL 说起前阵子帮朋友看一个电商后台的搜索接口数据量不到 1000 万原本按订单号精确查询一直很稳响应基本在 300 毫秒左右。结果新需求上线之后要支持“查以某个后缀结尾”的订单号代码里很自然地写成了WHERE order_no LIKE %20230815。上线当天还没事第二天数据一多接口直接冲到 4 秒慢日志里刷屏的全是这一条。为什么会崩不是 MySQL 突然变笨了而是这种写法把索引废掉了。你可能会想1000 万行不算多啊加个普通索引不就完事了问题恰恰在于这个LIKE模式的第一个字符就是%MySQL 的查询优化器看到这种条件压根不知道从索引的哪个位置开始找于是只能选择把整张表从头到尾扫一遍。全表扫描的成本和数据行数强相关行数越多扫得越久慢是必然的。很多开发遇到这条慢 SQL第一反应是改索引、改缓存其实都没挠到痒处。要搞清楚为什么索引在这里失效得回到 B 树索引的定位原理上去看。1.2 B 树索引的匹配原则把 B 树索引想象成一本按拼音排序的电话簿。如果你想找“王”开头的名字翻到 W 那一页顺着字母序往下翻很快就能定位但如果你想找“以军结尾”的名字那完蛋了因为你根本不知道这些名字分布在电话簿的哪一页只能一页一页从头翻到尾没有任何捷径。MySQL 的 B 树索引跟电话簿是同一个逻辑。索引里的数据按照索引列的值排序排序的起点就是字符串的第一个字符。所谓“快速定位”本质上是利用有序性做二分查找锁定一个范围。但这套机制有个天然的边界它只能处理“开头确定”的匹配。用LIKE abc%这种前缀匹配时优化器可以从abc这个前缀开始扫一直扫到abd之前结束范围清清楚楚。而LIKE %abc是后缀匹配优化器想用索引也不知道从哪一段开始。更不用说LIKE %abc%这种前后都带通配符的写法等于把字符串中间的一段掏出来做切片匹配有序性彻底作废。所以判断一条 LIKE 能不能走索引本质就一句话能不能把匹配条件变成“从左边界开始的一段前缀”。1.3 用 EXPLAIN 快速确认索引失效口头说“索引失效”不够得用工具验证。MySQL 里最直接的工具就是EXPLAIN。假设你的表结构长这样CREATE TABLE users ( id BIGINT PRIMARY KEY, email VARCHAR(255) NOT NULL, KEY idx_email (email) ) ENGINE InnoDB;执行这条查询EXPLAIN SELECT id, email FROM users WHERE email LIKE %qq.com \G如果你用的是 MySQL 5.7 或 8.0执行计划里会看到类似的关键信息type: ALL key: NULL rows: 9854800 Extra: Using where这几个字段每一格都是警报。typeALL表示全表扫描是所有执行方式里最慢的一种keyNULL说明优化器最终一个索引都没选rows接近整表行数代表它预计要扫近千万行Extra里的Using where表示它扫到每一行之后还要再拿LIKE条件做过滤。四行信息连起来读就是一句判决书索引毫无参与感这条 SQL 全凭蛮力硬扫。反过来如果一条 SQL 能用到索引type会变成range或者refkey会显示具体的索引名rows会大幅下降。所以优化前先跑一把 EXPLAIN既是诊断也是给后面优化效果留个对比基线。这条经验我反复用线上排查慢查询时十次里面有八次都能靠它快速定位到问题根源。2. 反向存储大法把后缀查询改成前缀查询2.1 核心思路多存一个“倒着写”的镜像列既然 B 树只认前缀匹配后缀匹配完全没法定位那就想办法把后缀匹配翻译成前缀匹配。翻译的思路很朴素给字符串做一次“镜像反转”比如原始字符串是abcqq.com反转之后就变成moc.qqcba。我们把反转后的值单独存一列这一列就是原始列的“镜像列”。查询的时候原本要找的是“以 qq.com 结尾”的邮箱现在换成在镜像列里找“以 moc.qq 开头”的值。仔细想想一个字符串如果以qq.com结尾那么反转之后必然以moc.qq开头反过来也成立两边的匹配逻辑在数学上完全等价。这里用到的反转工具是 MySQL 内置的REVERSE()函数它不需要你自己写任何复杂逻辑。函数返回的是字符顺序完全倒排后的新字符串简单可靠。关键点是REVERSE()是按字符集来反转字符的常见的 utf8mb4 下面英文字母、数字、中文都能正确处理不会因为多字节编码把字符串搞乱。2.2 查询改造从 %abc 到 cba% 的推导过程改造成 SQL 非常直白。原来的查询是SELECT id, email FROM users WHERE email LIKE %qq.com;改造之后变成SELECT id, email FROM users WHERE email_rev LIKE moc.qq%;这里有个特别容易踩错的点反转的是被搜索的“常量”不是列。常量qq.com反转之后是moc.qq所以查询模板应该是REVERSE(qq.com) || %也就是moc.qq%。你要是手痒写成email_rev LIKE REVERSE(email) ...那就等于对每一行的镜像列再做一次函数运算索引照样废掉性能还不如不改。一般生产里我习惯把这个转换封装成一个 SQL 模板或者存储函数避免业务同学手写反转结果。比如封装一个suffix_like(column_rev, constant)的函数内部自动处理反转逻辑调用方只需要传原始常量就不会把abc和cba搞混。2.3 收益估算100 倍是怎么算出来的标题里的“100 倍”不是拍脑袋咱们来做个简单的 IO 模型估算。假设users表有 1000 万行每行数据平均 500 字节那全表数据差不多 5GB。InnoDB 默认一个数据页是 16KB5GB 要被拆成大约 32 万个数据页。全表扫描意味着这条查询要把这 32 万个页从头到尾读一遍哪怕只查出来几百行结果也必须付出这个完整的读取代价。反过来走索引范围查找的成本就低太多了。B 树的从根节点到叶子节点一般就三四层定位到一个起始位置只需要读几个索引页。假设匹配到的数据有 1000 行索引叶子页加对应数据页撑死了也就几十个页的读盘量。32 万页对比几十页足足相差四个数量级就算扣掉缓存命中、随机读损耗这些因素用户能感知到的延迟差距也稳稳超过 100 倍。不过我得把丑话说在前面。如果查询模式命中率特别高比如整张表有一半以上的行都满足条件那优化器反而可能觉得索引不如全扫划算。这一点后面讲边界时还会再提先记住结论反向存储大法的收益取决于“模式本身能圈定一个很小的结果集”。3. 落地实操建表、迁移、查询全流程3.1 生成列别把镜像列做成手工同步说到“多存一列”很多人的第一反应是应用层写数据的时候顺手多写一个字段。这么做不是不行但特别容易埋雷漏一处更新线上就会冒出一批查不到的脏数据而且排查起来极其痛苦。最稳的做法是用 MySQL 5.7 开始支持的生成列Generated Column让数据库自动维护镜像值。生成列分为VIRTUAL和STORED两种我强烈建议生产环境用STORED。STORED意味着列值真实落盘写入和更新时由 InnoDB 自动计算你完全不用在应用层操心VIRTUAL则是在查询时临时计算虽然省磁盘但索引优化和范围扫描在某些版本里不如STORED来得干脆。一份可以直接抄的建表语句长这样CREATE TABLE users ( id BIGINT PRIMARY KEY, email VARCHAR(255) NOT NULL, email_rev VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, KEY idx_email_rev (email_rev) ) ENGINE InnoDB;执行完这条语句以后你往email里写什么值email_rev都会自动同步成反转结果不需要额外的触发器也不需要应用层配合。3.2 存量数据回填与在线变更如果你是在一张已经上线、数据量很大的老表上做优化没有机会走新建表的流程那就得用ALTER TABLE来加生成列。MySQL 会遍历全表把存量数据逐行算好镜像值并落盘这个过程会持有表锁业务高峰期跑ALTER基本等于自杀。我的经验是放在凌晨低峰期执行或者直接用gh-ost、pt-online-schema-change这类在线变更工具把锁表影响降到最低。在线变更的大致流程是先建立一张影子表在影子表上加好生成列和索引然后同步增量数据最后切换表名。这类工具的原理不复杂但对运行环境有一些要求权限、复制延迟、磁盘空间都得提前确认。如果你一次性变更的数据量特别大建议先找个测试环境压一遍计算出大致耗时再排上线窗口。如果连在线工具都用不了还有一个土办法分页分批更新。先ALTER TABLE加一个普通字段email_rev然后在应用层起个批处理任务每次取 1000 条主键区间算出REVERSE(email)回写提交事务再处理下一批。分段提交的好处是不至于产生一个巨型的 undo log也不会长时间占住锁对线上影响小很多。但这种方式有个致命前提应用层每次写email的时候必须记得同步email_rev只要漏一处数据一致性就崩了。所以能用生成列就用生成列别给自己和同事留定时炸弹。3.3 查询改写与执行计划验证结构改造完成之后把业务查询切到新索引上。原来的慢查询SELECT id, email FROM users WHERE email LIKE %qq.com;新写法SELECT id, email FROM users WHERE email_rev LIKE moc.qq%;查询结果里要展示的仍然是email原字段email_rev只是过滤条件不需要体现在业务结果里。因此接口的返回值完全不用改只换一条 SQL 就够了。改完先别急着上线用EXPLAIN验证一把EXPLAIN SELECT id, email FROM users WHERE email_rev LIKE moc.qq% \G理想情况下执行计划应该变成这样type: range key: idx_email_rev rows: 351 Extra: Using index condition看到type从ALL变成rangekey从NULL变成具体索引名rows从千万降到几百基本就可以放心了。这里还能再优化一层如果这个查询只需要id和email_rev就把索引改成覆盖索引(email_rev, id)执行计划的Extra会变成Using index连回表都省掉性能又会上一个小台阶。4. 进阶从“后缀匹配”扩展到“任意子串匹配”4.1 反转子串的数学原理为什么只对“尾部”有效反向存储大法被误解最多的地方就是有人以为它能解决所有LIKE通配符问题。必须说清楚它只对“后缀匹配”也就是LIKE %abc有效。如果你的查询是LIKE %abc%这种任意位置子串匹配反转之后依然会得到LIKE %cba%模式前后都带%索引照样是废的。原因也很直白。把字符串abcdef倒过来得到fedcba你想找中间那段cd倒过来之后变成dc位置仍然在正中间。反转操作只是把“尾部对齐”变成长“头部对齐”并没有把“中间匹配”变成“前缀匹配”。任何宣传“反转大法包治百病”的说法基本都是在误导。遇到任意子串匹配应该去考虑全文索引或倒排表反向存储帮不上忙。4.2 双镜像列方案同时搞定开头和结尾如果业务同时需要“以 abc 开头”和“以 abc 结尾”两类匹配那就别只加一列镜像直接加两列。一列保持原始值走LIKE abc%另一列存反转值走LIKE REVERSE(abc) || %。两条查询各自走各自的索引互不干扰完全可以把两种需求都优化到位。不过这里有一个执行计划的坑如果一条 SQL 同时带了“开头条件”和“结尾条件”比如email LIKE a% AND email LIKE %b优化器通常只会选择其中一个索引做范围扫描然后用另一个条件在回表阶段过滤。在数据分布不均匀的情况下优化器可能选错索引导致性能打折。遇到这种情况可以先跑一下 EXPLAIN看看它实际选的是哪条索引必要时用FORCE INDEX强制指定或者把两个条件拆到两个查询里再在应用层合并结果。4.3 逻辑等价性校验与常见误用反向存储之所以敢说“结果集完全一致”核心在于“反转整个字符串”和“反转匹配模式”是同步发生的。原字符串尾部固定的一段反转后正好变成镜像列头部固定的一段两端对齐映射关系严密不需要额外加长度条件去修正。真正容易翻车的是人肉写反转结果。比如常量是abc反转之后是cba有人图省事直接写LIKE abc%那找的就变成“以 cba 开头”的原始镜像结果自然全不对。所以我在团队里落地这套方案时明确要求所有查询必须通过一个统一封装的反转函数去生成模式串绝不手写硬编码。另一个常见误用是把整个列也反转一遍写成REVERSE(email_rev) LIKE %abc%这样又会退回到全表扫描把前面的优化全白费。记住反转只发生在常量那一侧列一侧保持原样。5. 避坑指南反向存储的 5 个典型坑5.1 忘了同步镜像列脏数据事故复盘我见过一次比较惨的线上事故就是因为镜像列没有同步。某个历史脚本直接UPDATE email更新了一批用户的邮箱却漏掉了email_rev结果这一批用户用邮箱后缀查询再也查不到了排查了两个小时才定位到是脏数据。如果你用的是生成列这个坑天然不存在因为数据库会自动维护。但如果你因为兼容性或者其他原因用了普通列就必须把所有写入、修改、批量订正、存储过程全部纳入同步范围。我的建议是让应用层写接口统一走一个 DAO 方法原始值和镜像值在一个事务里同时更新不允许任何绕过这个方法的裸 SQL 存在。没有生成列的条件时同步逻辑越收敛出事故的概率越低。5.2 排序规则与大小写不一致MySQL 的LIKE行为受排序规则collation影响。默认的utf8mb4_general_ci或者utf8mb4_0900_ai_ci都是大小写不敏感的比较所以email_rev LIKE moc.qq%不会因为邮箱里出现大写字母而漏数据和原查询的行为一致。但如果你在加镜像列的时候不小心把列定义成了COLLATE utf8mb4_bin那镜像列的比较就会变成大小写敏感结果和原始查询立刻产生差异。排序规则不一致是隐藏最深的坑表面看数据没问题实际一跑线上业务就漏结果。所以加列的时候原列用什么排序规则镜像列就照着抄一遍别自作聪明去改。5.3 前缀索引长度选不对照样退化有人为了省点索引空间把镜像列建成了前缀索引比如KEY idx (email_rev(10))。这个操作会带来一个隐患如果业务里的搜索常量反转之后超过 10 个字符比如查LIKE %abcdefghijk镜像列里的范围扫描只能定位到前 10 个字符第 11 个字符之后的信息完全没法通过索引区分MySQL 只能扩大扫描范围再回表过滤性能迅速退化。我给的建议很直接后端模糊查询做不了“预计常量长度可控”的假设干脆建全列索引。如果列特别长非要省空间那就让前缀长度比业务里可能出现的最长搜索模式还要长并且每次上线前都用 EXPLAIN 确认type仍然是range别让优化器悄悄退化成别的方案。5.4 回表开销与覆盖索引补救索引命中不代表查询一定快。WHERE email_rev LIKE moc.qq%能通过索引找到一批主键但接下来每一行都要回表去读原始数据页才能返回email字段。如果命中的是几万行回表开销同样不可小觑。优化办法是让索引“覆盖”查询所需的全部字段。比如某些报表场景只需要id和email_rev那你直接把索引建成(email_rev, id)查询就能完全从索引页拿到结果不需要回表。覆盖索引是这种优化方案落地后的最后一道加速手段很多时候能从“能用”升级成“飞快”。5.5 中文、emoji 与多字节字符场景REVERSE()在 utf8mb4 字符集下按字符反转中文和 emoji 基本不会反转成乱码。但这一层安全是有前提的连接 MySQL 时的字符集设置必须一致最好统一SET NAMES utf8mb4否则服务端和客户端字符集不一致可能导致反转结果乱码。另外一些组合字符或者带声调的拼音序列反转后从视觉上会跟原字符顺序不一致但 MySQL 的LIKE比较最终走的是排序规则结果影响通常可控。测试阶段务必拿几条包含中文和特殊符号的真实数据跑一遍结果集校验别只盯着纯英文数据测试。多字节字符场景下最好在预发环境把线上数据抽样比一轮再上线。6. 什么场景才值得用反向存储6.1 先 EXPLAIN再决定改不改我见过太多人把反向存储当万能钥匙见 LIKE 就反转这其实很危险。做任何优化之前先把原始查询的EXPLAIN拿出来看一眼确认它确实走的是全表扫描确认慢的原因真的在索引上。有些查询之所以慢是因为连接条件写错、网络延迟高、或者别的复杂子查询拖慢了整体这时候优化 LIKE 完全在打空枪。另外还要算一下匹配占比。反向存储之所以能快靠的是“小结果集 索引定位”。如果模式本身命中一大半数据比如在一张全是英文单词的表里做LIKE %e那走索引反而不如全表扫描省事优化器甚至会自动放弃索引。这类场景改不改都没有意义先诊断再动手才是正确的打开方式。6.2 命中率很高时索引未必更快命中率高的情况下全表扫描往往是有它的优势的。顺序读硬盘上的数据页不像索引那样需要来回跳转加上 InnoDB 的预读机制大数据块的连续读取效率其实很高。而索引虽然能快速定位但最终还是要回表读取大量行随机 IO 比顺序 IO 慢得多。所以反向存储大法的最佳应用范围是条件能圈出整体数据里很小的一部分比如按订单号尾部匹配某几个字符、按邮箱域名后缀匹配少量账户、按路径尾部匹配特定目录。命中行少索引的价值才能全部发挥出来。命中行一多收益就急剧下降甚至变成负优化。6.3 替代方案全文索引与倒排表如果你的需求是“任意子串匹配”比如搜索商品名里包含“手机壳”的所有商品反向存储确实救不了你。这时候应该考虑 MySQL 的全文索引搭配 ngram 分词插件可以比较高效地处理中文子串搜索。更大规模的场景就该用外部的倒排索引比如 Elasticsearch把文本拆成词条用分词结果建立倒排表才能真正支持任意位置的模糊匹配。反向存储的价值在于它成本极低不需要引入额外的中间件也不需要数据同步链路纯粹靠数据库自带功能就能解决一大类痛点。但终归技术方案要对症下药分清“后缀匹配”和“子串匹配”才不会把简单问题复杂化也不会拿一个救不了你的方案硬套。每次上线前我都会让测试同事把改造前后的结果集求一次差集确认差集为空再放流量。这条习惯帮我躲过至少三次排序规则不一致和一次常量反转写错造成的事故。反向存储大法本质上只是把 B 树的边界条件重新排列了一遍它并不神秘但每一步都得踩在准确的等价关系上才算真正落地。