先交代一下背景。我之前接手过一个线上订单查询系统业务方提了一个需求按订单号的最后 6 位模糊查询订单。当时第一版 SQL 写出来大概是这样的SELECT * FROM orders WHERE order_no LIKE %123456;表面看没啥问题数据量小的时候也确实很快。等表里数据量跑到 500 万行以后这条 SQL 直接变成了数据库里的头号慢查询高峰期能把 CPU 打到 90% 以上最后只能靠限流保命。我当时的第一反应和大家一样加索引呗。结果加完普通索引一看 EXPLAINtype 还是 ALL索引根本没生效。后来才搞明白LIKE 左侧带 % 的模糊查询B 树索引天生就无能为力。直到我试了“反向存储大法”把这条 SQL 的响应时间从 2 秒多降到了 20 毫秒以内说效率提升 100 倍一点都不夸张。这篇文章就把这套方案的原理、实操步骤和踩过的坑完整复盘一遍。1. 为什么 % 在左边索引就“罢工”了1.1 B 树索引本质上是个“前缀匹配器”要理解反向存储为什么有效先得搞清楚一件事MySQL 的 InnoDB 索引底层是 B 树数据按索引列的值从小到大排序叶子节点串成了有序链表。这种数据结构决定了它的检索方式是“从根节点开始按值逐层二分查找”它最擅长的事就是“从前往后的精确或范围匹配”。举个例子WHERE order_no LIKE 2023%是前缀匹配。优化器可以从索引树上定位到第一个以2023开头的键值然后沿着叶子链表一直往后扫直到不满足前缀条件为止。这是一个标准的范围扫描typerange性能非常稳定。但WHERE order_no LIKE %123456就完全不一样了。%在最前面意味着目标字符串可能从任意位置开始优化器根本不知道应该从索引树的哪个节点开始查。它唯一的办法就是把整个表所有行的 order_no 全部取出来从第一个字符到最后一个字符逐位比对也就是全表扫描。打个比方B 树索引像一本按姓氏拼音排序的电话簿。你想找“所有姓张的人”翻到 Z 开头那一块就行非常快。但如果你想找“名字里最后一个字是‘伟’的人”这本按姓氏排序的电话簿就帮不上忙了你只能一页一页从头翻到尾。1.2 一次全表扫描的成本到底有多高很多人觉得全表扫描也没啥不就多花点时间吗。但真实生产环境里这个“时间”是会被放大成事故的。假设一张表有 500 万行每行数据加索引页算下来平均 300 字节全表数据量大概 1.5GB 左右。如果 buffer pool 足够大数据全在内存里那一次顺序扫描可能也就一两秒。但问题是生产环境的大表往往远超 buffer pool 容量或者查询条件复杂导致扫描过程中要随机回表这就变成了大量磁盘随机 IO。我见过最夸张的情况一条LIKE %关键字在千万级表上跑了 8 秒直接把连接池打满。还有一个容易被忽略的问题全表扫描不仅仅是慢它还会产生大量的行锁和一致性读开销。在 RR 隔离级别下长查询会让 undo log 的清理滞后进一步拖垮整个实例。所以这类 SQL 不是“忍忍就过去”的问题而是必须处理掉。1.3 中间匹配能不能靠普通索引救有人会问那我建一个覆盖索引把所有字段都塞进去是不是就不回表了能缓解但解决不了根本问题。覆盖索引只是省去了回表这一步但优化器依然要遍历整棵索引树的所有叶子节点做字符串匹配。索引树再小也是几百万个叶子节点全扫一遍消耗依然巨大。还有的人尝试把%abc拆成LIKE abc% OR LIKE %abc再 UNION 一下。这个方案能解决一部分需求变体但%abc这个分支本身照样是全表扫描治标不治本。所以问题的关键不是“索引覆盖了哪些列”而是“查询模式符不符合 B 树的字典序规则”。后缀匹配天然违背这个规则必须从数据存储形态上想办法这就是反向存储的切入点。2. 反向存储大法把后缀变成前缀2.1 方案原理与一个粗浅类比“反向存储大法”的英文做法其实叫 Reverse Index思路非常简单建表的时候额外增加一列专门存放原字段的反转字符串。比如订单号ABC123456反转后就是654321CBA。而查询条件LIKE %123456经过反转就变成了LIKE 654321%。左模糊瞬间变成了右模糊后缀匹配瞬间变成了前缀匹配。前缀匹配是 B 树的舒适区索引就能正常生效了。还是用电话簿来类比。普通索引是按名字拼音正向排序的电话簿找“名字以 X 结尾”的人很难但如果另外再整理一本“把每个名字倒过来排”的电话簿比如“伟张”排到 W 区“建国李”排到 G 区那你想找“名字最后一个字是伟”的人直接在倒序电话簿里查“伟”开头的区域就行又快又准。第二种做法如果业务场景里其实要查的是“后缀等于某个固定值”而不是模糊匹配那反转列还能支持等值查询。比如查所有qq.com结尾的邮箱反转列直接等于REVERSE(qq.com)走的是typeref比 range 还快。2.2 能提升多少从 O(n) 到 O(log n)这个方案的本质是把时间复杂度从 O(n) 降到了 O(log n m)其中 n 是全表行数m 是最终命中的结果集行数。改造前的全表扫描每次查询都要扫描 n 行n 等于 500 万就是 500 万次字符串比较。改造后的索引范围扫描在 B 树上二分查找定位起始位置大约log2(500万)≈23次比较然后沿着链表顺序读取 m 行m 通常是个位数到几十。注意最终命中的结果集大小 m 必须远小于全表行数这个方案收益才大。反过来讲如果一条LIKE %abc能匹配全表 50% 的行那就算走了索引回表代价也很高优化器可能依然选择全表扫描。所以这个方案适合的场景是查询选择性好结果集小但原表数据量巨大。2.3 适用场景判断不是所有LIKE %abc都适合用反向存储动手之前先对照一下适合快递单号后几位查询、邮箱后缀查询、手机号后几位查询、用户名末尾关键字搜索、URL 路径末段匹配这些场景后缀信息有明确的业务含义而且选择性通常不错。不适合LIKE %abc%这种中间模糊匹配反转之后还是%cba%照样全表扫描。不适合后缀本身没什么区分度比如查LIKE %ing一匹配就是几十万行走索引还不如全扫。还有一点要想清楚这个方案本质上是空间换时间多一列反转数据就意味着额外存储和写入开销只适合读多写少的业务。如果一张表每秒写入上千次还非得加反转列可能需要重新评估成本。3. 实操从建表到查询改写的完整改造3.1 用生成列自动维护反转数据反向存储最怕的一件事就是数据不一致。你在应用层往 A 列写原值再往 B 列写反转值万一哪天代码漏了、事务没提交完整、或者批量数据导入的时候忘了反转整张表的索引就废了。所以在 MySQL 里我强烈建议用生成列Generated Column让数据库自己负责反转应用层插入数据的时候根本感知不到这一列的存在。从 MySQL 5.7 开始就支持这个功能。新表的话直接这样建CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(255) NOT NULL, email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, PRIMARY KEY (id), KEY idx_email_reverse (email_reverse) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;老表加列也很简单ALTER TABLE users ADD COLUMN email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, ADD INDEX idx_email_reverse (email_reverse);这里用了STORED关键词意思是反转后的值真实存储在磁盘上。MySQL 也支持VIRTUAL生成列不占行内存储空间但查询时往往需要额外计算优化器对 VIRTUAL 列索引的利用在一些复杂 SQL 里会表现得比较保守。我的经验是既然目的是建索引加速查询就用 STORED多占点磁盘换查询稳定性。注意一个细节索引建好后插入数据时不需要也不应该往email_reverse里显式写值。一旦写了MySQL 会报错。生成列的值完全由表达式REVERSE(email)决定数据一致性从底层就保证了。3.2 查询改写与参数绑定改造后的业务查询就是把原来LIKE %原串改成LIKE REVERSE(原串) || %。以“查所有 qq.com 结尾的邮箱”为例原始 SQLSELECT id, email FROM users WHERE email LIKE %qq.com;改写后SELECT id, email FROM users WHERE email_reverse LIKE REVERSE(qq.com) || %;拆解一下REVERSE(qq.com)的结果是moc.qq而abcqq.com这个邮箱反转后是moc.qqcba确实以moc.qq开头。匹配逻辑完全等价。如果业务语义是“邮箱后缀等于 qq.com”那更简单直接转等值匹配SELECT id, email FROM users WHERE email_reverse REVERSE(qq.com);等值匹配走的是typeref用普通索引就行性能比范围查询还要好。如果用的是 MyBatis 这类 ORM注意参数不要拼字符串用占位符传参。尤其要提醒同行朋友反转这个操作最好在代码里做不要把REVERSE()函数写在 SQL 的 ON 条件里否则索引列被函数一包又会演变成全表扫描。正确的 Java 侧写法是String keyword qq.com; String reverseKeyword new StringBuilder(keyword).reverse().toString(); ListUser users userMapper.queryByEmailReverse(reverseKeyword %);MyBatis 映射文件里对应的 SQLselect idqueryByEmailReverse resultTypeUser SELECT id, email FROM users WHERE email_reverse LIKE CONCAT(#{keyword}, %) /select别小看这个 CONCAT 和占位符的配合很多人习惯在 XML 里写LIKE %${keyword}%一旦 keyword 是用户传入的这叫 SQL 注入生产环境一定要避开。3.3 EXPLAIN 验证改造前后的差距改造完以后不要拍脑袋说“快了”用 EXPLAIN 把前后对比摆出来。改造前EXPLAIN SELECT id, email FROM users WHERE email LIKE %qq.com\G输出大致是id: 1 select_type: SIMPLE table: users type: ALL possible_keys: NULL key: NULL rows: 1000000 filtered: 5.00 Extra: Using where关键信息就是type: ALL和rows: 1000000这是一条典型的全表扫描 SQL。改造后EXPLAIN SELECT id, email FROM users WHERE email_reverse LIKE moc.qq%\G输出大致是id: 1 select_type: SIMPLE table: users type: range possible_keys: idx_email_reverse key: idx_email_reverse rows: 120 filtered: 100.00 Extra: Using index conditiontype从 ALL 变成 range扫描行数从 100 万变成 120 行这就是量级上的差距。在真实压测里这条查询的 P99 从 1800ms 将到了 18ms100 倍没有水分。3.4 存量数据如何平滑迁移如果是新表直接走 DDL 就行了。但生产环境大概率是已有上千万行数据的存量表这时加生成列和索引必须考虑在线变更。一个常见误区是直接在生产库执行ALTER TABLE ... ADD COLUMN ...。MySQL 8.0 对ADD COLUMN支持ALGORITHMINSTANT的场景非常有限加了STORED GENERATED COLUMN之后InnoDB 通常需要重建表这在千万级表上可能意味着几分钟甚至十几分钟的业务不可用还会产生主从延迟。我建议大表场景用pt-osc或gh-ost做在线表结构变更pt-online-schema-change \ --alter ADD COLUMN email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, ADD INDEX idx_email_reverse(email_reverse) \ Dtest,tusers \ --execute如果公司的数据库运维平台内置了无锁 DDL 能力直接用平台的变更工单会比自己在客户端敲 DDL 安全得多。还有一个小技巧如果业务上你只需要固定长度的后缀比如只查“订单号最后 6 位”反转列可以只存REVERSE(RIGHT(order_no, 6))。这样生成列的长度短索引页能装下更多键值IO 更少还能避免反转全字段带来的存储膨胀。4. 进阶多维优化与替代方案4.1 反转列其他条件的联合索引设计实际业务里的查询很少只有单字段条件更多是组合查询。比如“按邮箱后缀 用户状态 创建时间”筛选用户。反转列建好以后不要单独只建一个索引可以结合其他高频查询条件建联合索引ALTER TABLE users ADD INDEX idx_rev_status_time (email_reverse, status, created_at);联合索引设计的时候要遵循最左前缀原则email_reverse放在最左边因为它是等值或范围模糊匹配之后可以跟着status等值条件再往后是排序字段created_at。这样查询走索引的同时排序也能由索引直接提供避免 filesort。反过来要注意别一上来就把五六列全塞进联合索引。索引列越多写入越慢索引页占用越大。一般情况下列数控制在 3 列以内比较稳妥。4.2 MySQL 8.0 函数索引能不能替代生成列MySQL 8.0.13 开始支持函数索引写法是ALTER TABLE users ADD INDEX idx_email_reverse ((REVERSE(email)));从功能上看函数索引确实可以达到和“生成列索引”类似的效果。但有一个很关键的差异函数索引能否被用到完全取决于优化器能不能把你的 SQL 自动改写为表达式匹配。如果你写WHERE email LIKE %qq.comMySQL 优化器并不会自动把它解读成REVERSE(email) LIKE moc.qq%它没有这个智商。你依然要手动改写 SQL改成WHERE REVERSE(email) LIKE moc.qq%这时候函数索引才能生效。所以函数索引和生成列方案在实际使用体验上差距不大都需要应用层改写查询。但生成列方案更直观EXPLAIN 里能看到明确的列名和索引名排查问题的时候心智负担更小。函数索引的优势是省掉一列存储适合不太想动表结构的场景。4.3 固定后缀查询直接转等值匹配我在项目里发现很多需求嘴上说的是“模糊查询”实际业务含义是“后缀等于某一类固定值”。比如查所有 VIP 用户的手机号段WHERE phone LIKE %8888其实业务方真正想要的是“尾号等于 8888 的那批用户”这种情况下用反转列做等值查询是最优解SELECT id, name FROM users WHERE phone_reverse REVERSE(8888);等值查询走的是typeref比 range 还要稳定而且优化器对等值查询的成本估算更准误判走全表的概率更小。所以以后接到类似需求先追问一句“这个后缀是固定的还是任意的”如果是固定的能转等值就转等值能省很多事。4.4 反向存储都搞不定的场景怎么办如果查询场景是LIKE %keyword%即关键字可能出现在字符串任意位置反转字段也没用因为反转之后还是%drowyek%。这种情况下可以考虑MySQL 全文索引适合英文或分词后的文本用 ngram 分词器可以支持中文但维护成本、精确度都不如专业搜索引擎。外部搜索引擎组件比如 ES把需要检索的字段做倒排索引模糊查询的吞吐量会高很多。数仓方案如果检索的数据是分析场景而不是在线交易场景可以同步到 ClickHouse 这类列式存储用LIKE扫描性能也远比 MySQL 好。一句话总结普通索引管前缀反转列管后缀中间模糊上搜索引擎别指望一个方案通吃所有场景。5. 常见坑与排查实录5.1 REVERSE 之后的排序不是原来的字典序反转列只能用来加速匹配和过滤千万不要在反转列上做 ORDER BY。比如你想查尾号 123456 的订单并按订单号从大到小排序写了SELECT * FROM orders WHERE order_no_reverse LIKE 654321% ORDER BY order_no_reverse DESC;这个结果看起来像是有序的实际上反转串的字典序和原串字典序没有任何直接对应关系。原串的ABC999反转后是999CBA原串的ABD000反转后是000DBA反转后000DBA排前面但原串ABD000其实是更大的那个排序逻辑全乱了。正确做法是先用反转列把结果集缩小再回表按原列排序或者把排序字段一并加入联合索引末端。5.2 多字节字符反转的意外MySQL 的REVERSE()函数是按字符反转的不是按字节反转所以对中文、日文这些多字节字符基本安全。比如REVERSE(你好世界)会得到界世好你不会出现半个汉字乱码。但这里有一个非常隐蔽的坑emoji 和组合字符。比如家庭组合 emoji‍‍在底层由多个码点组成REVERSE()会把码点序列反过来视觉上可能变成一个拼错的奇怪字符系列。如果你的业务字段里包含这类字符反转列存的“反转值”未必符合人的直观理解匹配业务规则时会出现偏差。另外一个更常见的坑是大小写。utf8mb4_unicode_ci排序规则下索引匹配不区分大小写REVERSE(ABC)和REVERSE(abc)在索引上会被视为同一个值本身没问题。但如果业务对大小写敏感还是要注意排序规则和索引定义的匹配。5.3 优化器不走索引怎么办有些情况下反转列和索引都建好了EXPLAIN一看还是typeALL。常见原因有统计信息不准确。生成列刚建立、索引刚添加的时候MySQL 的统计信息可能滞后先执行一遍ANALYZE TABLE users;刷新一下。查询选择性太差。优化器估算出匹配结果超过全表的 20% 时会认为走索引回表反而更慢主动放弃索引。这种情况不是优化器笨是业务查询确实不适合这个方案。隐式类型转换。反转列是 VARCHAR查询参数是数值或字符集不一致导致索引列上发生隐式转换索引失效。排查这类问题我的习惯是先跑一遍EXPLAIN FORMATJSON看cost和rows的估算值再用FORCE INDEX做一次对照试验。如果FORCE INDEX之后性能明显更好就去排查统计信息如果差不多说明这个查询本身就不该硬走索引。5.4 大表加列还是得走在线 DDL前面提过ALTER TABLE对生成列的兼容性问题这里再重点强调一遍。不要因为本地测试小表加列快就以为线上千万级表也能瞬间完成。任何涉及STORED GENERATED COLUMN的 DDL都必须先做预案观察表大小和当前主从延迟优先使用在线变更工具变更窗口选择低峰期变更完成后立刻验证EXPLAIN和索引统计信息。我在一个 2000 万行的日志表上直接跑ALTER TABLE加反转列结果重建表花了 17 分钟主从延迟飙到 200 多秒还好是低峰期没有酿成事故。从那以后凡是上千万行的表我宁可多花十分钟写 gh-ost 工单也绝不手敲 ALTER。5.5 缓存与批量导入的回填问题生成列可以保证新数据自动计算但如果是用LOAD DATA、INSERT ... SELECT批量导数据要注意 MySQL 会不会为生成列重新计算。实测下来这些批量操作都会自动触发生成列表达式求值不用额外回填。但如果之前用过应用层双写方案业务代码里自己写反转列历史数据里大量是错的切到生成列方案之后需要先写脚本校验。校验逻辑很简单按主键分批扫比对email_reverse和REVERSE(email)是否一致不一致的直接UPDATE覆盖。举一个实际踩过的坑有一回我们用 DataX 从数仓同步数据到 MySQL目标表有生成列源表没有反转字段。DataX 的任务配置里如果显式列举了列名却漏掉了反转列插入的时候 MySQL 会因为“生成列不能显式插入”而报错。解决方法是任务配置里只写原字段让生成列自算。5.6 唯一约束与反转列如果原字段本身有唯一性要求比如邮箱不能重复反转列同样可以建唯一索引ALTER TABLE users ADD UNIQUE KEY uk_email_reverse (email_reverse);反函数的唯一性完全等价的如果一个字段值唯一那么它的反转值也唯一。这个唯一索引还能顺手成为等值查询的索引一箭双雕。但注意联合唯一索引要慎重设计别让业务无关的列进唯一键。最后再分享一点个人体会。反向存储这套方案看起来不复杂但真正落地的时候至少要在三件事上有清晰认知一是查询模式到底是什么前缀、后缀还是包含匹配决定了能不能用这招二是数据一致性由谁保证生成列是首选应用层双写只能是过渡三是性能验证必须量化EXPLAIN 的 type 和 rows 比“感觉快了不少”可靠得多。我后来还遇到过需求方想用反转列同时支持“中间某段字符串匹配”试了一圈发现根本不现实。每个方案都有边界搞清楚边界在哪比急着动手更重要。如果你现在也被LIKE %abc这种慢查询折磨不妨先翻翻表结构看看能不能加一列反转值。别看这个思路简单生产环境里能救不少人的命。