开头高性能高并发项目里被慢查询干过的同学一定对这条 SQL 不陌生WHERE col LIKE %abc。明明就多了一个前置百分号索引就像失灵了一样数据量一上来从几百毫秒一路涨到几秒甚至几十秒。今天聊聊我实际优化过的一种方案——“反向存储大法”或者叫反转索引。思路不算复杂就是把存储和查询两侧的字符串都做一次镜像反转把LIKE %abc变成LIKE cba%让 BTree 索引重新“愿意”工作实测在千万级数据下做到了接近百倍的响应提升。这篇东西适合天天跟 MySQL 打交道的后端开发、DBA、数据运维同学也适合那种每次提性能优化都被“加索引”搪塞、想搞明白索引到底为什么失效的入门者。我会把从原理、落地 SQL、EXPLAIN 对比到踩坑边界全部写透尽量让你看完能直接照着改。1. 从一条 3 秒的慢查询说起LIKE %abc 到底死在哪一个环节先还原一个我实际遇到过的场景。业务方要按订单号后几位查单比如搜“最后几位是 20241012 的订单”需求提得很朴素——WHERE order_no LIKE %20241012。本地测试环境几十万行数据跑起来毫无感觉两三百毫秒还能忍。等上了生产某个核心流水表到了千万级、日志表到了上亿级之后这条查询直接出现在慢查询 TOP 榜上。用户每点一次搜索背后就是在全表做一次字符串扫描别提多酸爽。问题来了为什么LIKE abc%能走索引LIKE %abc就非得全表扫1.1 BTree 索引的本质一本按字母序排好的词典InnoDB 的 BTree 二级索引本质就是一个排好序的结构。排好序意味着什么意味着数据库可以从根节点开始二分查找快速定位到某个“起点”然后顺序往下扫直到遇到不满足范围条件的数据为止。这是索引拯救查询性能的根基。打个比方你手里有一本按英文字母顺序编排的词典想找所有以le开头的单词可以直接翻到词典 L 区从le这一页往后翻基本是 O(log n m) 的代价m 是命中条数。但如果你要找所有以le结尾的单词词典的字母序编排帮不上任何忙——因为同一个结尾可以出现在词典完全不同的角落你只能把整本词典一页页翻完挨个看最后一个字母是不是le。又慢又绝望但这就是逻辑上的必然。数据库也一样。二级索引页按前缀排序LIKE abc%能明确给出一个扫描起点先命中abc这个键值然后往右扫到所有超出abc前缀范围的行停止。而LIKE %abc没有一个可定位的起点优化器只能遍历整棵索引树或者堆表。1.2 优化器为什么不给面子EXPLAIN 里的真相这种慢查询直接EXPLAIN看执行计划输出往往非常“感人”----------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | rows | ... | Extra | ----------------------------------------------------------------------------------------- | 1 | SIMPLE | t_log | ALL | NULL | NULL | 12478910| ... | Using where| -----------------------------------------------------------------------------------------typeALL是全表扫描rows12478910是优化器预估要扫的行数。百万、千万级数据量下单条查询就是一次全表 IO 风暴冷缓存场景下慢得尤其明显。这里有个无数人踩过的误区觉得“只要加个索引就能救”。可问题是针对col本身建索引LIKE %abc依然没法用上。因为 BTree 的有序性只对前缀有效你查后缀相当于在已经排好序的数据里干一件需要全局遍历的事。索引在但优化器判断使用索引甚至比全表扫更亏因为扫描二级索引之后还得回表。所以真正的解法不是“再建一个同样的索引”而是改变数据的组织方式让“后缀查询”变成“前缀查询”。这就是下文的思路来源。2. 反向列加查询改写把后缀查询硬生生变成前缀查询“反向存储大法”的核心一句话可以概括存储时把字符串反着写查询时把条件反着拼让数据库能用前缀索引去支持“原后缀匹配”。原查询是WHERE col LIKE %abc。如果我们在表里额外维护一列col_rev让col_rev REVERSE(col)那存储层的数据面貌就变了原本order_no20241012的行order_no_rev2101404202。查询时我们对关键字也做反转REVERSE(20241012)2101404202。于是后缀匹配order_no LIKE %20241012就等价于前缀匹配order_no_rev LIKE 2101404202%——注意这下前置百分号跑到屁股后面去了索引可以正常从2101404202这个键值开始向右范围扫描。一前一后世界完全不一样。2.1 从加列到回填一套能直接抄的 DDL 流程以 MySQL 为例完整操作分四步。第一步加反向列。字符集、排序规则必须与原列保持一致否则可能出现大小写敏感不一致之类的诡异问题。ALTER TABLE t_order ADD COLUMN order_no_rev VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci AFTER order_no;第二步回填存量数据。生产环境表如果很大不要一条 UPDATE 梭哈锁表时间会爆炸。要分批回填比如每次只处理一个 id 区段或者按主键范围循环处理。-- 小表可以直接一次性回填 UPDATE t_order SET order_no_rev REVERSE(order_no) WHERE order_no_rev IS NULL; -- 大表务必分批例如 UPDATE t_order SET order_no_rev REVERSE(order_no) WHERE id BETWEEN ? AND ? AND order_no_rev IS NULL;第三步建索引。ALTER TABLE t_order ADD INDEX idx_order_no_rev (order_no_rev);第四步改写查询。-- 优化前 SELECT * FROM t_order WHERE order_no LIKE %20241012; -- 优化后 SELECT * FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE(20241012), %);有必要多说一句优化后的 SQL仍然建议把原条件带上作为二次校验类似AND order_no LIKE %20241012。为什么反转函数在绝大多数常规字符串上没问题但在多字节字符、特殊排序规则、隐藏字符等极端场景下你不敢 100% 赌它和原 LIKE 完全等价。带上原条件索引扫描先用反向列把扫描范围缩小到极小然后原条件做精确过滤。这一招在灰度阶段尤其关键稳妥第一。2.2 生成列和函数索引让数据库自己维护反向值手工维护一个冗余列最大的痛点是应用层写入时容易漏。今天新增了一个写入入口忘了写order_no_rev明天查不到数据后天线上告警长期下去反向列就成了脏数据源。所以我在正式项目里更推荐用生成列Generated Column让数据库自己算。MySQL 5.7 及以上可以建一个存储生成列ALTER TABLE t_order ADD COLUMN order_no_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(order_no)) STORED; ALTER TABLE t_order ADD INDEX idx_order_no_rev (order_no_rev);STORED生成列会把反转结果物理落盘读的时候不用现场计算写入时数据库自动维护应用层完全不用关心。这条路径的好处是业务代码改动量最小新数据天然一致存量一把回填完就持续稳定。MySQL 8.0.13 以上甚至可以直接建函数索引连冗余列都省了ALTER TABLE t_order ADD INDEX idx_order_no_rev ((REVERSE(order_no)));查询时写成WHERE REVERSE(order_no) LIKE CONCAT(REVERSE(20241012), %)。函数索引本质上和生成列索引是同一个思路优化器能识别表达式并走索引。但我要提个醒函数索引的优化器识别能力在不同版本之间有差异生产环境务必先看EXPLAIN确认走了range或ref别想当然。那到底选冗余列、生成列还是函数索引我的习惯是MySQL 5.7 用STORED生成列MySQL 8.0 优先函数索引但必须用 EXPLAIN 验证如果团队里有不少新人冗余列 应用双写反而最直观配合定期校验脚本也能控住脏数据。没有银弹按你的运维精力选。3. 同样的搜索条件EXPLAIN 前后的差别9.8 秒到 15 毫秒方案讲得再好没有实测数据就是耍流氓。下面是我在测试环境里跑过的一组对比机器配置是 8 核 16G、普通 SSD、MySQL 8.0.28表里压了 1200 万行订单数据order_no长度在 16 到 24 位之间混合分布查询目标是“找 rear 号段后缀为某固定值”的记录。优化前 SQLSELECT * FROM t_order WHERE order_no LIKE %20241012;冷缓存 innodb_buffer_pool_size未完全预热的情况下我连续跑了 10 次取均值单次耗时9.8 秒EXPLAIN 显示typeALL, rows≈12000000, ExtraUsing where。全表扫一块约 1.8GB 的表文件这个数字一点不夸张。加完反向列和索引后改写为SELECT * FROM t_order WHERE order_no_rev CONCAT(REVERSE(20241012), %);严格来说应该用LIKE CONCAT(REVERSE(20241012), %)实际执行计划类型通常显示为range走了idx_order_no_rev索引预估扫描行数骤降到几百行单次耗时均值15 毫秒左右。从 9.8 秒到 15 毫秒正好是三个数量级跟标题里“100 倍”的说法在方向上完全吻合。当然真实倍数是由命中的数据量决定的下面细说。3.1 执行计划变化怎么看改写前后的关键差异看EXPLAIN几个核心字段就够了字段优化前优化后typeALLrange / refpossible_keysNULLidx_order_no_revkeyNULLidx_order_no_revrows12000000约 200~500ExtraUsing whereUsing index conditionrows从千万级掉到百级意味着扫描成本缩小了几个数量级。二级索引定位到2101404202...前缀对应的叶子节点只在这些位置回表IO 次数和 CPU 消耗自然都不是一个量级。有一点要说明很多人在这个阶段容易犯强迫症看到ExtraUsing index condition还不够总想折腾成Using index做覆盖索引。如果你的查询列能被索引完全覆盖那确实能省掉回表但对于宽表来说索引覆盖很多字段反而让索引体积变大、写入变慢属于典型的过度优化。我的建议是业务查询要取几列就把这几列评估一下常用且足够窄的再加到索引里别为了图标好看把整张表塞进索引。3.2 为什么有的场景提升不到 100 倍命中行数才是底层变量很多人把“100 倍”当成玄学觉得是不是什么场景都能这么猛。其实加速比背后是一个非常朴素的公式扫描行数的下降比例约等于响应时间的下降比例上限。你原来全表扫 1200 万行现在索引只扫 300 行扫描量下降了 4 万倍但回表、随机 IO、SQL 解析这些开销还在所以实际响应时间不会等比例下降但降两三个数量级很正常。反过来如果你的“后缀”是高频值比如所有订单号都以2024结尾那反向列索引扫出来的第一个键值就开始大范围命中rows依然可能是几十万甚至几百万。索引确实“走”了但回表代价摆在那里响应时间还是慢。这种场景反向存储救不了你要考虑的是分区、汇总表或者换精确匹配。所以判断一个查询适不适合做反转优化先做一个小样本统计SELECT COUNT(*) AS cnt FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE(20241012), %);这个cnt大致就是索引要扫的行数。如果cnt相对总行数很小提升会很明显如果cnt都占总量百分之十几了别折腾了这条路收益有限直接换方案。4. 不是银弹五类边角场景会让反向列白建我在多个项目里踩过不少坑有些还挺隐蔽。这一章把它们全列出来能避免的避免不能避免的提前想好对策。4.1 坑一存量回填和一致性校验前面说了生成列可以自动维护新数据但存量数据回填这一步依然躲不掉。回填最容易出的问题不是 SQL 写错而是回填动作和生产写入并发执行导致“回填完之后又插入了反向列 IS NULL 的新行”。所以大表回填一定要分批并且建议回填完成后再跑一遍校准脚本-- 找反向列与反转原列不一致的数据 SELECT COUNT(*) FROM t_order WHERE order_no_rev REVERSE(order_no) OR (order_no IS NOT NULL AND order_no_rev IS NULL);这个该校验脚本应该被放进定期巡检任务而不是只跑一次。尤其是那些用了手工冗余列、没有走生成列的旧系统脏数据全靠这个脚本兜底。4.2 坑二把“包含匹配 %abc%”也拿来反转LIKE %abc和LIKE %abc%是两种完全不同的需求。前者是“以 abc 结尾”反转成cba%之后索引能帮忙后者是“包含 abc”反转之后变成%cba%前面还是有一个%索引照样废掉。我见过不止一次同事做了反向列之后开开心心把LIKE %abc%改成LIKE CONCAT(%, REVERSE(abc), %)跑完 EXPLAIN 发现还是全表扫跑回来质问我方案是不是没用。真不是没用是问题从“后缀匹配”偷偷换成了“包含匹配”。“包含匹配”本质是子串搜索靠 BTree 解决不了。利落一点上全文索引、ngram 分词或者干脆把数据同步到 Elasticsearch / ClickHouse 这类专门干检索的引擎。别在 MySQL 里硬扛。4.3 坑三REVERSE 不是万能安全函数在 utf8mb4 下MySQL 的REVERSE()是按字符反转而不是按字节反转常规中文、英文、数字问题不大。但要注意几类数据含有 emoji 等多字节组合字符时反转结果的排序规则表现可能跟你预期不一致大小写不敏感排序规则下原列和反向列的 collation 必须一致否则查询时比较规则不同可能出现该命中没命中的情况如果列定义里带特殊字符集比如utf8mb4_bin和utf8mb4_general_ci混着用结果全凭运气。落地时最省心的做法反向列必须显式复制原列的CHARACTER SET和COLLATE不要手滑用默认值。4.4 坑四搜索关键字本身含通配符用户搜索的内容如果包含%或_字符比如搜索单号里本身就带百分号直接拼CONCAT(REVERSE(%abc), %)会把通配符也当模糊条件解析掉结果查询结果完全错乱。这时候需要ESCAPE关键字标准的写法类似SELECT * FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE(abc), %) AND order_no LIKE %abc ESCAPE !;反转列这边的斜杠转义也建议显式处理WHERE order_no_rev LIKE CONCAT(REPLACE(REVERSE(abc), %, \\%), %);反正一句话凡是用户输入直接进入 LIKE 的先做转义永远没错。4.5 坑五过度使用反向列导致写入放大一个人口众多的业务表如果搞了三五个反向列每个列都要额外索引写入放大和存储膨胀会很可观。反向列最优解只用在那些“后缀检索频率极高、结果行数占比很小”的字段上。用了它就不要再朝三暮四建一堆相关前缀索引索引不是越多越好DBA 看到一张表挂着二三十个索引才是最痛苦的。5. 反向存储、全文索引、ES三种检索需求的选型决策最后给一张选型对照表纯属我个人在多个项目里沉淀下来的经验供参考。检索需求推荐方案原因固定前缀匹配abc%普通 BTree 前缀索引索引天然支持无需反转固定后缀匹配%abc反向列 / 函数索引将后缀查询转成前缀查询收益最大包含子串匹配%abc%MySQL 全文索引ngram或外部检索引擎BTree 无法高效支持子串检索海量大文本内容检索Elasticsearch / 列式存储倒排索引与分词能力远超 MySQL低频筛选 总行数较小直接 LIKE 全表扫不值得为低频查询引入额外存储和复杂度有同学可能注意到MapReduce 里常见的“倒排序索引”思想和反向存储也是同一路子数据正向放不好查就换一个维度重新排列让查询能用“有序跳过”代替“全局遍历”。倒排索引把文档 ID 按词条重排反向列把字符串按尾部重排底层都是空间换时间。5.1 关于 MySQL 全文索引的一个小提醒如果只是少量文本字段做包含匹配MySQL 自带全文索引能凑合InnoDB 支持中文环境下的 ngram 解析器。但千万别把全文索引当 ES 平替用。数据量过了千万级、并发检索复杂了MySQL 全文索引在分词质量、相关性排序、并发度上都会露怯。到那一步该上 ES 就上 ES反向列解决不了所有问题全文索引也只是一个阶段方案。5.2 我的落地建议生成列优先灰度验证不能省基于上面这些经验我给自己项目的定级规则是这样的第一优先级MySQL 5.7 用STORED生成列自动维护反向值MySQL 8.0 优先函数索引并 EXPLAIN 验证。第二优先级存量做分批回填线上灰度前跑一遍 REVERSE(col)校验脚本。第三优先级监控慢查询日志和索引使用情况观察一个月用实际数据决定是否保留该优化策略。这也是我想最后反复强调的反向存储大法不复杂复杂的是你要分清楚业务到底是“后缀匹配”“前缀匹配”还是“包含匹配”以及你到底愿不愿意为查询性能付出存储和写入的额外代价。想清楚了再动手动手了就要一路看 EXPLAIN 看到底。这套方法我用了很多年没有一次掉链子希望你也能在这条路上少踩几个坑。