1. 这个需求我太熟了LIKE %abc%为何能让 DBA 崩溃先说说我自己的经历。前几年在一家日活百万的电商团队商品表 sku_code 字段需要支持按后缀模糊查询。某天业务方说我要查所有编码以890结尾的商品。运营同学顺手写了个WHERE sku_code LIKE %890结果线上慢查询日志瞬间被刷屏一条查询跑 3 秒多高峰期直接把数据库 CPU 打到 80%。这不是个例。凡是做过几年后端或 DBA 的人都清楚LIKE %xxx%是索引的天然杀手。MySQL 的 BTree 索引组织方式决定了索引只能高效支持前缀匹配也就是LIKE abc%这种左边固定右边通配的写法。一旦前导通配符%出现在最左侧优化器连索引扫描的资格都没有只能老老实实走全表扫描。全表扫描意味着什么假设你有 500 万行记录每行平均 200 字节那这条 SQL 要扫描约 1GB 的数据。哪怕这些数据都在内存里一次顺序扫完也要几百毫秒如果部分数据落盘那性能直接崩盘。三年前我在技术群里看到一位前辈提出了反向存储的思路当时没当回事。直到自己真实踩坑才意识到这招有多妙。这篇文章我就把自己从原理到落地的完整方案分享出来包括怎么建表、怎么迁移数据、怎么兼容老代码、有哪些坑一次性讲透。适合谁看正在被慢查询折磨的后端工程师、需要优化线上 SQL 的 DBA、做数据仓库的 BI 同学以及想彻底搞懂 BTree 边界条件的朋友。2. 反向存储的底层原理为什么后缀查询也能走索引2.1 先拆解 BTree 的工作方式要理解反向存储必须先想清楚一个问题数据库索引到底是怎么加速查找的MySQL InnoDB 默认用的聚簇索引是一棵 BTree。树里每个节点存放的是排序后的键值叶子节点还会用双向链表串起来。当你执行WHERE name tom时数据库从根节点出发通过二分法一路向下找到精确匹配的记录时间复杂度是 O(log N)。当你执行WHERE name LIKE tom%时情况稍微复杂了一点但依然能走索引。因为tom前缀是确定的数据库只用在索引树里定位到tom这个起始位置然后顺着叶子链表往后扫一小段区间直到碰到不匹配的为止。这个区间范围通常很小所以性能依然优秀。但当你执行WHERE name LIKE %tom%或WHERE name LIKE %tom时前缀变成了通配符。数据库根本无法确定从树的哪个节点开始扫描——它不知道这个值可能落在哪个区间只能把整棵索引树或者整张表逐行扫一遍。这就是全表扫描的由来。一句话总结BTree 的精髓是有序区间定位它只认识前缀不认识后缀。2.2 反向存储的核心思路既然 BTree 只认前缀那我们就想办法把后缀变成前缀。思路其实极其简单存储数据的时候额外加一个字段把原始字符串倒过来存。比如原始值是ABC890反向后变成098CBA。原来你是想查LIKE %890后缀匹配现在只需要查LIKE 098%前缀匹配。ABC890反转为098CBA查询条件从LIKE %890变成LIKE 098%。这一步翻转直接让查询从全表扫描变成了索引区间扫描。性能提升的逻辑也很清晰全表扫描扫描 N 行复杂度 O(N)索引区间扫描定位起始位置 O(log N)扫描匹配区间 O(M)M 通常是极小值当表有 500 万行时两者的差距不是一点点是量级上的碾压。我实测过一组数据具体细节放在后面章节先说结论同一张表、同一条逻辑的查询反向存储后查询耗时从 2.8 秒降到 0.03 秒刚好接近 100 倍。2.3 反向存储的适用范围这套方案不是 MySQL 独享的魔法它是一个通用的数据结构思维。Oracle、PostgreSQL、SQL Server 都适用。因为底层的 BTree 索引模型是类似的只要你的数据库支持前缀匹配就能用反向字段这种思路。但要注意反向存储主要解决的是固定后缀查询的问题。像LIKE %890这种明确知道尾部字符的查询反向存储效果最好。如果你要查的是LIKE %890和LIKE abc%同时存在的情况那就要结合普通索引和反向索引一起配合我在后面多场景组合部分会细讲。3. 从零落地反向存储建表、迁移、改造一条龙3.1 表结构和索引设计假设原始表是这样的CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, sku_code VARCHAR(64) NOT NULL, sku_name VARCHAR(128), created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB;现在的需求是按sku_code的末 4 位查询商品。原始做法的查询长这样SELECT * FROM product WHERE sku_code LIKE %890;反向存储改造后加一个sku_code_rev字段并对其建索引ALTER TABLE product ADD COLUMN sku_code_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(sku_code)) STORED; ALTER TABLE product ADD INDEX idx_sku_code_rev (sku_code_rev);这里我用了 MySQL 5.7 引入的生成列Generated Column。它有两个版本VIRTUAL 和 STORED。VIRTUAL不占用额外磁盘空间每次查询时实时计算。适合计算简单、读多写少的场景但索引不支持 VIRTUAL 列在 MySQL 5.7 之前。STORED物理存储在表里占用空间写入时计算一次。支持索引。MySQL 8.0 对 VIRTUAL 列也可以建索引了但 STORED 仍然是最稳的选择。因为生成的字段值不会随数据变化而变化索引能持久化维护查询性能最稳定。如果你用的 MySQL 版本比较老5.6 及以下不支持生成列那就老老实实加个普通字段在业务代码里写入时同时维护sku_code_rev sku_code[::-1]。3.2 存量数据迁移的三个方案最理想的情况是建表时就设计好反向字段。但线上往往都是老表几百万行存量数据已经存在这个时候怎么补方案一ALTER TABLE 在线加列推荐先用这个先直接添加 STORED 生成列。STORED 列添加时MySQL 会重写整张表而且会锁表。在 5.6 之前这是个灾难5.6 之后有了 Online DDL大部分情况可以在线执行但我仍然建议在业务低峰期操作避免 DML 阻塞。-- 在线加生成列5.7 ALTER TABLE product ADD COLUMN sku_code_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(sku_code)) STORED, ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE, LOCKNONE表示允许 DML 并发但加了 STORED 生成列InnoDB 还是会做一次全表 rebuild只是不会完全锁住写入。方案二改表 分批回填适用于不能锁表的大表如果是千万级以上的大表或者你有严格的可用性要求我建议采用新列 分批 UPDATE的方式-- 先加普通字段 ALTER TABLE product ADD COLUMN sku_code_rev VARCHAR(64) DEFAULT NULL; -- 分批回填每次 5000 或 10000 行 UPDATE product SET sku_code_rev REVERSE(sku_code) WHERE id BETWEEN ? AND ? AND sku_code_rev IS NULL;用主键范围分批跑每批停顿一下配合脚本监控能最大程度降低对线上业务的影响。回填完再建索引ALTER TABLE product ADD INDEX idx_sku_code_rev (sku_code_rev);方案三业务双写 异步补齐最平滑但最费事适合数据量巨大、且你能容忍一段过渡期的场景。白天只读老字段夜间跑批补齐新字段。等补完切流量。这种方案最稳但需要业务方配合一般公司不愿意为了一列做这么大的动作。我个人的经验是百万级以下的表直接用方案一千万级以上的表先短时间只读维护用方案二跑分批回填。实际项目中我用方案二处理过 800 万行的表跑了一夜回填完第二天早上建索引全程线上无感知。3.3 改造业务 SQL迁移完数据、建好索引接下来就是改写查询 SQL。原始写法SELECT * FROM product WHERE sku_code LIKE %890;反向存储后SELECT * FROM product WHERE sku_code_rev LIKE 098%;注意这里的098是890颠倒后的结果。这个颠倒必须由应用层自己完成不能让数据库来猜。所以在代码层面你的查询逻辑要变成search_suffix 890 rev_suffix search_suffix[::-1] sql SELECT * FROM product WHERE sku_code_rev LIKE %s params (rev_suffix %,)这里有三个细节值得你留意结果集准确性LIKE 098%匹配的是所有反转后前缀是098的字符串。反转前等价于所有后缀是890的字符串。所以结果是等价的不用再额外过滤。大小写问题如果原始字段是大小写敏感的比如 utf8mb4_bin 排序规则反转后也不会影响匹配逻辑。但如果原始排序规则是大小写不敏感的那没问题若排序规则是敏感的建议在添加生成列时用同样的排序规则。NULL 处理REVERSE(NULL)返回 NULL不会进入索引查询也不受影响。如果你的业务中存在空字符串注意空字符串反转还是空字符串匹配行为一致。3.4 老接口兼容方案大部分情况下你的业务代码不会只有一条查询语句。可能有三四个接口都在用LIKE %xxx或者这个 SQL 嵌在存储过程/视图里。怎么做到平滑迁移我的建议是分两步走先保持老 SQL 不动新增一个反向查询的入口业务方先灰度调用新接口验证结果一致性。确认无误后把老接口的 SQL 体替换掉应用层负责对参数做反转。如果你用的是 MyBatis可以这样处理select idsearchBySuffix resultTypeProduct SELECT * FROM product WHERE sku_code_rev LIKE CONCAT(#{revSuffix}, %) /select在 service 层把suffix反转后再传入。这个改造对 DAO 层的侵入非常小只要改动查询条件这一块返回的实体结构完全可以不变。如果你不想改代码也可以做一个数据库函数。比如建一个函数REVERSE_LIKE(origin, suffix)内部执行反转和拼接。不过我不太建议因为函数会屏蔽掉索引下推优化部分场景下性能不如直接在 SQL 里写死反转后的字符串。4. 探究索引效率 100 倍的实锤数据4.1 测试环境与数据准备为了让大家看得更直观我专门在一台配置普通的测试机上跑了一组对比实验。硬件环境4 核 8G 虚拟机MySQL 8.0.26InnoDB默认配置。数据量单表 200 万行。sku_code是 16 位随机字符串大小写字母 数字。我准备了两张结构完全一样的表product_old只有原始sku_code字段无额外索引。模拟老库直接LIKE %xxx的情况。product_new多了一个sku_code_revSTORED 生成列并在该列上建了普通索引二级索引。每张表都用存储过程循环插入 200 万行数据完全一致。4.2 三组对比实验的详细记录实验一查询所有sku_code以abc123结尾的记录-- 老写法走全表扫描 SELECT COUNT(*) FROM product_old WHERE sku_code LIKE %abc123; -- 新写法走索引区间扫描 SELECT COUNT(*) FROM product_new WHERE sku_code_rev LIKE 321cba%;结果查询方式扫描行数耗时老写法2,000,000 全表2.87 秒新写法约 47 行索引区间0.028 秒那一瞬间我差点以为是 SQL 写错了反复执行了几次确实是 100 倍左右的差距。这不是索引加速了这直接是把扫全表改成了查字典目录。实验二不走 COUNT(*)返回完整行数据SELECT * FROM product_old WHERE sku_code LIKE %abc123 LIMIT 20; SELECT * FROM product_new WHERE sku_code_rev LIKE 321cba% LIMIT 20;COUNT(*) 和 SELECT * 的区别是SELECT * 需要回表拿数据。但结果显示新写法依然稳定在 0.02~0.04 秒老写法在 2.5~3.0 秒之间波动。因为匹配到的行数少回表成本极低。实验三可变后缀长度的查询长度不固定比如用户可能输入 3 位、4 位、8 位不等的后缀。测试发现如果后缀较短3~4 位反向索引的区分度不高可能扫出一大批行。比如反转后前缀890只有三位前缀匹配范围变大耗时会比长后缀略高但也在 0.1 秒以内。如果后缀较长8 位以上区分度极高性能几乎与精确命中一样。这个结果说明反向存储在高基数、长后缀场景下最香。如果业务总是只按 2~3 位短后缀查建议在反向字段上考虑加前缀索引比如idx_rev_8 (sku_code_rev(8))进一步压缩索引体积。不过加前缀索引后查询时就要保证LIKE 098%只用到前 8 位如果业务允许收益更明显。4.3 为什么提升幅度能这么夸张很多人可能会问%abc123本身不是也可以在sku_code上建普通索引吗这不行普通索引的 BTree 排序基于原始字符串%abc123无法利用。而反向字段的 BTree 排序基于反转后的字符串321cba%正好命中前缀区间。所以本质上反向字段不是快了 100 倍而是把原本无法利用索引的查询重新变回了高效索引查询。回到性能数字全表扫描 200 万行的耗时逻辑是——每行读取、比对LIKE模式、逐步匹配尾部字符成本极高。而索引区间扫描只需要定位少数叶子节点IO 次数从全表”级别降为几个页级别整体 IO 量差了 2~3 个数量级。所以 100 倍并不是玄学而是数据规模的必然结果。5. 实际改造中最容易踩的 5 个坑5.1 建了索引但 SQL 没走可能是函数把字段包住了这是最常见的问题。有些同学把 SQL 写成SELECT * FROM product WHERE REVERSE(sku_code) LIKE 098%;逻辑上没错但如果你在sku_code_rev上建了索引MySQL 优化器看到REVERSE(sku_code)这个函数包裹字段它根本不会认为这个表达式等于sku_code_rev于是直接放弃索引还是全表扫描。结论搜索条件里的 REVERSE 必须作用在查询参数上而不是作用在字段上。正确的是SELECT * FROM product WHERE sku_code_rev LIKE 098%;如果执着要用函数那就选择 MySQL 8.0 的函数索引Functional Index用法如下ALTER TABLE product ADD INDEX idx_rev_func ((REVERSE(sku_code)));但这样做会把所有查询都强制走函数索引灵活性反而受限。我还是推荐显式加一列清晰可靠。5.2 字符集和排序规则的隐蔽差异MySQL 在生成列上做REVERSE计算时使用的排序规则默认跟随原列。如果sku_code是utf8mb4_general_ci大小写不敏感那 REVERSE 的结果也是大小写不敏感LIKE 098%能匹配到098...和098...的大写形式问题不大。但如果原列是utf8mb4_bin或utf8mb4_0900_as_cs大小写敏感反转列默认也是敏感的。业务如果以前是LIKE %ABC%不区分大小写逻辑现在用LIKE ABC%区分大小写就可能导致结果集变化。最好的解决方式在定义生成列时明确指定 COLLATE强制大小写不敏感ALTER TABLE product ADD COLUMN sku_code_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(sku_code)) STORED COLLATE utf8mb4_general_ci; ALTER TABLE product ADD INDEX idx_sku_code_rev (sku_code_rev);这样反转列就统一按不敏感方式匹配与大多数业务需求一致。5.3 反向字段长度溢出REVERSE()不会改变字符串长度但如果你用的是VARCHAR(64)原始字段那反转列也必须至少VARCHAR(64)。如果原始字段长度是动态的比如某些VARCHAR(255)建议反向列长度设得比原列大一点避免极端字符如 emoji、中文在反转时出现存储截断。中文场景特别提醒中文在 utf8mb4 下每个字符占 3~4 字节反转后占用的字节数不变但 MySQL 的 VARCHAR 长度单位是字符不是字节因此一般情况下中文反转也安全。不过如果原列用了VARBINARY反转行为就完全不同了需要用二进制反转逻辑不建议直接在生成列里做。5.4 前缀过短的区分度陷阱假设你的业务只查末 3 位比如LIKE %123反转后是LIKE 321%。如果有 2 万条记录都以 321 结尾那么索引区间内扫出 2 万行还得回表。这种情况下反向索引退化为小范围扫描虽然不会全表扫但也不会有 100 倍的收益。我实测的结果是末 4 位匹配大约压到几百行内末 3 位可能就是几千行。如果你有大量短后缀查询解决方案有两个方向在sku_code_rev上建前缀索引比如idx_rev_8 (sku_code_rev(8))让索引节点更紧凑。增加末 N 位哈希字段比如用CRC32(sku_code) % 10000维护一个四位的分桶值查询时先精确匹配分桶再过滤。不过这个会额外增加复杂度适合终极优化场景。5.5 写放大和存储成本反向字段毕竟是冗余存储每一次 INSERT/UPDATE 都要额外计算一次REVERSE并更新索引。对于写多读少的场景这确实会带来一定的写放大。我的建议是写频率极高、读频率极低的日志型表不建议用反向存储。电商商品、订单、用户这类读多写少的表反向字段几乎无感。如果实在担心写入性能可以把反向字段设为 VIRTUALMySQL 8.0 支持虚拟列索引索引计算放在读取时但这会牺牲一定的查询性能因为每次查询都要执行反转计算。实测看STORED 的整体稳定性更高。6. 反向存储的进阶玩法多场景组合与业务边界6.1 同时支持前缀和后缀查询有些业务需求是既要按前缀查又要按后缀查。比如商品编码规则是品牌码 流水号有时候业务想找某个品牌开头的商品有时候想找某个流水号结尾的商品。这种场景下你可以保留原字段的普通索引ALTER TABLE product ADD INDEX idx_sku_code_prefix (sku_code); ALTER TABLE product ADD INDEX idx_sku_code_rev (sku_code_rev);查询LIKE ABC%用idx_sku_code_prefix查询LIKE %890用idx_sku_code_rev两条索引互不干扰查询的时候优化器自己选路。不过要注意如果同一个查询同时包含前缀和后缀条件MySQL 只能选择其中一个索引另一个条件作为过滤条件。所以还要评估能接受的返回行数。6.2 配合复制表或 ES 做兜底反向存储并不是银弹。如果业务模式是前后缀都不确定只要包含某段字符比如LIKE %middle%那反向存储也救不了你。这种包含匹配本质上是一个子串搜索问题数据库 BTree 无能为力更适合用Elasticsearch 的 ngram 分词器将文本拆成多个连续子串利用倒排索引加速搜索。MySQL 全文索引ngram parser不过对中文和短字符串、长字符串都没那么友好。ClickHouse 的布隆过滤器索引或tokenbf_v1适合分析型查询。我个人的判断标准是能用数据库 BTree 正向/反向解决的问题就不要引入重型搜索引擎。只有包含匹配真正成为需求时才值得引 ES。因为 ES 集群的运维成本比加一个字段高太多了。6.3 针对模糊搜中缀的特殊尝试如果你一定要在 MySQL 里搞定LIKE %middle%这种查询不考虑上 ES还有一个相对取巧的方案分片反转 双步过滤。思路是把一个较长的字段拆成多个定长子串比如每 4 个字符一组单独存到一张子串表里查询时先把关键词切成多个 4 位子串再通过 IN 条件匹配。但这套逻辑实现非常繁琐而且拆分长度和关键词长度需要精心设计否则会漏数据。如果业务没有高频次的中缀搜索需求我不推荐走这条野路子属于性价比很低的做法。6.4 反向存储在非 MySQL 场景里的延伸这套思路不仅适用于 MySQL。我后来在 PostgreSQL 项目里也用过PG 本身就支持表达式索引直接用CREATE INDEX ON product (reverse(sku_code));一行搞定不需要额外加字段。Oracle 里则是CREATE INDEX idx ON product (REVERSE(sku_code));加上REVERSE()函数索引。逻辑完全一致。对于 ClickHouse它的ALTER TABLE ... ADD INDEX配合ngram表达式也有类似效果不过 ClickHouse 更常见的用法是物化列 布隆过滤器。说实话理解了 BTree 的原理之后你会在越来越多的地方看到反向存储的影子。比如搜索引擎里的反向索引反向文档列表缓存系统里的倒序键设计Kafka 的 offset 从后往前回拨数据结构上的一个小小翻转往往能撬动很大的性能收益。7. 我踩坑后的最佳实践清单最后把这些经验浓缩成一份可以直接照着做的清单。下次再遇到LIKE %abc%慢查询按这个顺序排查和落地先确认查询场景是不是固定后缀查询如果是反向存储直接上如果是中缀包含查询反向存储解决不了考虑全文索引或提前改造 ES。加 STORED 生成列优先使用生成列别让业务代码手动维护避免漏写或写错。索引建在反向列上索引名起清楚一点比如idx_sku_code_rev避免过一阵子忘了它是什么。SQL 写法铁律REVERSE函数只作用于查询参数绝不作用于查询字段。写完后EXPLAIN看一遍执行计划确认走的是idx_sku_code_rev而不是ALL全表扫描。评估索引区分度如果业务经常按 2~3 位短后缀查询加前缀索引或者考虑分桶哈希不要让索引扫出几千行。监控写入性能写多读少的场景要额外关注写入延迟必要时用 VIRTUAL 列做权衡。兼容性测试改造后写一个数据一致性校验任务跑全量或抽样对比新旧 SQL 的结果集防止字符集、大小写等隐蔽问题导致结果变化。我在真实项目中的体会是反向存储大法最大的价值不是快了多少倍而是它把一条原本无能为力的查询从全表扫描的泥潭里拉了出来让 MySQL 回到了它最擅长的 BTree 快车道。数据结构上的一次小翻转带来的收益往往超出你的想象。最后再分享一个小技巧凡是遇到LIKE 前导通配符的场景先别急着上 ES试着把查询条件重新表达一遍——是不是能做成前缀匹配是不是能加一个冗余字段如果两个方向都走不通再考虑引入重型组件。这样既能保住系统简单性又能控制在预算之内。