凌晨两点一条慢查询报警把值班群炸醒一张 3800 万行的订单表后台同事为了查“近 30 天尾号 8888 的订单”写了一条WHERE order_no LIKE %8888跑了 12.8 秒还没出结果。这条 SQL 在业务上完全合理但数据库的索引在这里帮不上任何忙——LIKE %abc这种后缀模糊匹配天生和 BTree 索引八字不合。后来我用“反向存储大法”做了改造同一查询从 12 秒降到 0.09 秒索引效率直接跨了两个数量级。文章适合谁读凡是线上有过LIKE %xxx慢查询经历的后端、DBA、架构师或者正在为模糊查询优化发愁的同学这篇都值得看完。我会把原理、落地步骤、踩坑边界一次讲透。1. 同样叫模糊查询为什么 abc% 走索引%abc 就全表扫1.1 从BTree的排序逻辑说起先说个前提MySQL 的 InnoDB 索引用的是 BTree底层叶子节点按索引列的值有序排列。这个“有序”是所有索引优化的基础。你可以把 BTree 想象成一本按拼音排好序的《新华字典》——你想查“zhang”这个音节绝不会从第一页开始翻而是直接翻到字典中部偏后的位置再二分定位。索引树的查找逻辑也一样数据库根据你给的关键字在树里一层层缩小范围直到找到目标区间。关键在于字符串的“有序”是从第一个字符开始比的。abc abd abeMySQL 比较两个字符串时是从左往右逐字符比对的。这听起来像废话但所有模糊查询能不能用上索引全都是围绕这一条展开的。1.2 模糊查询的三种姿势与索引的关系我们平时写LIKE其实有三种形态它们的命运完全不同LIKE 模式能否用索引范围扫描原因LIKE abc%能走 range有确定的左边界可以从abc开始顺序扫LIKE %abc不能通常全表扫前缀未知无法在索引树里定位起点LIKE %abc%不能通常全表扫前后都未知比后缀匹配更麻烦LIKE abc%为什么能走索引因为优化器可以计算出匹配区间下界就是abc本身上界是比abc大一点点的那个字符串相当于abd或者编码空间里的下一个值。数据库跳到abc所在的叶子节点然后顺着链表往后扫直到扫出区间为止。这个过程叫 range scan扫描的行数只跟匹配结果数量成正比。LIKE %abc就麻烦了。你要匹配的是字符串的结尾但索引排序只对开头有感知。数据库想用索引就得回答一个问题第一个以abc结尾的字符串存在索引树的哪个位置答案是无解。它只能从索引的第一个叶子节点开始把每一条记录的完整值取出来再在内存里用%abc去比对。这就是全索引扫描量级是 O(n)表一千万行就是扫一千万次慢是必然的。1.3 为什么不能说“完全不能走索引”有些同学会反驳我执行EXPLAIN看LIKE %abc明明key字段有值啊怎么叫不能走索引这里要区分清楚key有值不代表是高效的范围扫描更可能是index full scan全索引扫描。MySQL 5.6 之后引入了 ICPIndex Condition Pushdown如果查询列都包含在索引里数据库可以在索引层直接过滤掉不符合%abc的记录减少回表次数。你看到key字段有值Extra 里写着Using index condition就是这个情况。但注意ICP 只是减少了回表并没有减少索引的遍历量。它依然要把整个索引树从头到尾过一遍复杂度还是 O(n)。数据量小的时候无所谓表上了千万行照样卡死。所以说得更准确一点LIKE %abc无法做范围定位最多做全覆盖扫描本质问题在于“找不到搜索起点”。2. 反向存储把字符串转个身让数据库“找得到头”2.1 核心理念后缀匹配变成前缀匹配反向存储的思路特别简单一句话就能说清把字符串倒过来存到另一列里查询时也把关键字倒过来%abc就变成了cba%。比如用户表里有一列mobile存手机号13800138000你新增一列mobile_rev存反转后的000831000831。业务想查“尾号 8000 的用户”原 SQL 是SELECT * FROM user_tab WHERE mobile LIKE %8000;改完之后变成SELECT * FROM user_tab WHERE mobile_rev LIKE 0008%;你品一下%8000是后缀匹配索引没法定点但0008%是前缀匹配数据库可以像查字典一样直接跳到0008开头的区间做一个漂亮的 range scan。核心就是“骗过”BTree 的排序规则让无迹可寻的后缀变成有据可依的前缀。打个比方你有一堆书按书名首字母排好序正排索引想找“书名以 XX 结尾”的书得一本一本翻但如果你把所有书名倒着写一遍再排序结尾词就变成了“倒序书名的开头词”查起来自然就是二分定位了。2.2 一个订单尾号查询的完整改造示例拿开头那个订单表举例。原始 SQLSELECT id, order_no, amount, create_time FROM order_tab WHERE order_no LIKE %8888 AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 50;这是典型的“尾号查询”运营想看最近一个月里尾号是 8888 的订单。表有 3800 万行order_no上有一个普通索引idx_order_no但这个索引形同虚设。改造第一步加反转列ALTER TABLE order_tab ADD COLUMN order_no_rev VARCHAR(64) NOT NULL DEFAULT ;改造第二步回填数据低峰期分批执行UPDATE order_tab SET order_no_rev REVERSE(order_no) WHERE id BETWEEN 1 AND 50000;改造第三步在反转列上建索引CREATE INDEX idx_order_no_rev ON order_tab(order_no_rev);改造第四步改写 SQLSELECT id, order_no, amount, create_time FROM order_tab WHERE order_no_rev LIKE 8888% AND create_time 2024-05-01 ORDER BY create_time DESC LIMIT 50;改造前后执行计划对比-- 改造前 EXPLAIN SELECT ... WHERE order_no LIKE %8888 ... -- type: ALL -- rows: 38500000 -- Extra: Using where -- 改造后 EXPLAIN SELECT ... WHERE order_no_rev LIKE 8888% ... -- type: range -- key: idx_order_no_rev -- rows: 2400 -- Extra: Using index condition改造前后扫描行数从 3800 万降到 2400 左右查询时间从 12.8 秒掉到 0.09 秒。所谓“100 倍”其实是保守说法高选择性场景下跨两三个数量级都很正常。2.3 关于“100倍提升”的正确理解方式我要泼一点冷水不是所有LIKE %abc都能提升 100 倍标题里的“100 倍”是有前提的。前提一匹配结果占全表比例足够低。尾号 8888 的订单大约占 1/10000选择性极高索引收益巨大。前提二唯一目标是把“后缀匹配”换成“前缀匹配”。如果业务模式是%abc%反向存储救不了。前提三你建的索引确实被优化器选中了。如果选择性低比如LIKE %a能匹配出全表 30% 的行优化器反而会弃用任何索引直接全表扫。所以更准确的说法是反向存储把“不可能走索引”变成“可能走索引”最终收益取决于匹配结果的选择性。我们在线上验证任何优化方案都不要只看标题结论一定根据实际数据分布 explain 一遍。3. 线上落地从数据迁移到SQL改写的一整套动作3.1 加列还是用生成列确定要做反向存储后第一个问题就是反转列怎么加谁来维护如果你用的是 MySQL 5.7 及以上版本最省心的方式是生成列Generated ColumnCREATE TABLE order_tab ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, order_no_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(order_no)) STORED, amount DECIMAL(12,2), create_time DATETIME, PRIMARY KEY (id), INDEX idx_order_no_rev (order_no_rev) ) ENGINEInnoDB;生成列的意思是order_no_rev的值由数据库自动根据order_no计算出来任何插入或更新order_no的操作order_no_rev都会同步刷新应用层零感知。这是我最推荐的方案因为它从根本上避免了“业务只写正列、忘了写反列”的脏数据问题。不过生成列加索引有一个前提建表时就要把列定义好。如果是要在现有表上改造MySQL 5.7 不支持直接ALTER TABLE ... ADD COLUMN GENERATED ALWAYS时顺带回填历史数据你得走一遍“加列→回填→加索引”的流程。好在 5.7 的在线 DDL 在大部分场景下不会锁写但夜间低峰操作还是稳妥。如果你用的是 MySQL 8.0.13 以上的版本还有一个更简洁的玩法——函数索引CREATE INDEX idx_order_no_rev ON order_tab ((REVERSE(order_no)));直接在索引定义里写表达式查询时直接写LIKE 8888%就行MySQL 会自动识别到可以用这个函数索引。但函数索引对REVERSE这类确定性内置函数的支持在不同版本里有细微差别线上最好先小版本验证一下再全量铺开。如果是老库、老表又不想改表结构那就老老实实加一个普通列回填数据后靠应用层做双写维护。方案之间没有绝对优劣核心判断标准是你能不能让新列的数据长期保持和原列一致。3.2 数据回填不锁表的节奏历史数据回填是线上改造最容易翻车的一步尤其大表。我见过有人直接跑一条UPDATE order_tab SET order_no_rev REVERSE(order_no)然后整个表被锁到死业务全线报警。这种操作本质上是在用全表扫描做逐行更新单条 UPDATE 持有行锁和间隙锁的范围会无限扩大。正确节奏是分批做-- 分批函数示例每批处理5万行 UPDATE order_tab SET order_no_rev REVERSE(order_no) WHERE order_no_rev AND order_no ! AND id BETWEEN ?start AND ?end;循环跑每次提交一批批与批之间留几秒间隔。时间段选择凌晨低峰期同时监控threads_running和锁等待指标。常见问题如果order_no本身长度不一REVERSE(order_no)耗时并不长但表有几千万行的时候真正的时间花在扫描和 IO 上。所以每次更新务必带上id范围用主键定位切片而不是让 MySQL 自己决定扫哪一段。跑完之后写个对账 SQLSELECT COUNT(*) FROM order_tab WHERE order_no_rev AND order_no ! ;结果必须为 0才算回填干净。3.3 建索引的版本与参数选择回填完成再加索引CREATE INDEX idx_order_no_rev ON order_tab(order_no_rev);线上大表建索引要注意两个点。第一MySQL 5.6 开始 InnoDB 支持 online DDLCREATE INDEX默认不会阻塞 DML但 ALGORITHM 会因版本和表结构不同有差异如果你还在 5.5 老版本建索引期间表会被锁住务必在维护窗口操作。第二索引不是越多越好order_no本身已经有一个idx_order_no现在又加一个idx_order_no_rev等于每次插入/更新都要多维护一棵索引树写入性能会有可感知的损耗。如果这张表的写入频率很高要考虑索引成本。更精细的做法是替换掉旧索引如果业务里order_no LIKE %xxx后缀和order_no xxx精确都会用到两个索引都留着如果业务全是后缀查询那原来的idx_order_no还留着支持精确匹配idx_order_no_rev只服务后缀查询——两个都要精确匹配还是走普通索引最快。3.4 SQL改写与EXPLAIN验证改写 SQL 时有一个非常关键、也非常容易踩的坑不能对索引列再做函数。正常写法是SELECT ... WHERE order_no_rev LIKE CONCAT(REVERSE(8888), %);这里的REVERSE(8888)是对常量做函数MySQL 在执行前就把常量算好了等价于WHERE order_no_rev LIKE 8888%索引不受影响。错误的写法是-- 千万别这么写 SELECT ... WHERE REVERSE(order_no_rev) LIKE %8888;这就是把函数套在索引列上索引直接失效又变回全表扫描。优化后查询的核心原则永远不变让索引列以最原始、未加工的状态出现在比较符左边。改完以后用EXPLAIN验证一次mysql EXPLAIN SELECT id, order_no, amount FROM order_tab WHERE order_no_rev LIKE 8888%\G *************************** 1. row *************************** type: range key: idx_order_no_rev key_len: 194 rows: 2400 Extra: Using index condition确认type是rangekey是你新建的索引rows明显变小就可以放心上线了。如果type还是ALL回去检查是不是 SQL 写成了REVERSE(order_no_rev) LIKE。3.5 写入链路的三种维护方案对比反转列的写入维护是长期工程我整理了三种常见方案供你按场景取舍方案优点缺点适用场景生成列数据库自动维护无脏数据建表/改表约束多部分版本限制新表或允许重建表场景应用层双写灵活可控性高多入口容易漏写需要代码评审兜底老表快速改造触发器不侵入应用规则统一隐式逻辑难排查复制链路有额外开销不推荐生产使用个人建议能用生成列就用生成列用不了生成列就应用层双写 定期对账脚本兜底。触发器我做过几次后来全部拆掉了——线上排查问题的时候最怕的就是“不知道哪个触发器偷偷改了数据”。4. 边界与踩坑反向存储救不了的场景4.1 包含匹配 %abc% 救不了很多同学做完第一版改造后会问那LIKE %8888%订单号里包含 8888能不能也用反向存储答案是不行。LIKE %8888%反转后变成LIKE %8888%前后都有%依然没有确定的前缀反向存储等于白做。这背后的本质是反向存储只能解决一端固定的模式解决不了两端都不固定的包含匹配。如果你确实需要包含搜索方向得换成全文索引、ngram 分词或者外部搜索引擎这些我在下一节展开。所以接到一个模糊查询需求先问清楚查询模式是前缀、后缀还是包含只优化拿得准的场景别硬套方案。4.2 低选择性时索引反而更慢反向存储建好索引后还有一个优化器“不领情”的时候。假设业务查的是LIKE %a——任何以字母 a 结尾的字符串反转后是LIKE a%这个前缀能匹配出一大堆数据占总行数的 20% 甚至更多。这个时候 MySQL 优化器会怎么选它会算一笔账走a%索引要回表拿 20% 的数据回表意味着随机 IO一千万行里取两百万行随机 IO 比全表顺序扫描贵得多。所以优化器大概率直接选 typeALL你的索引白建了。正确做法是建索引之前先测一下匹配结果的比例。你可以先跑一条统计 SQL 看命中量如果命中量超过全表的 10%反向存储的收益基本为零甚至可能更慢。这不是索引没用而是这个查询场景本身就不适合走索引。4.3 大小写、排序规则与字符集细节反向存储对字符集和排序规则比较敏感很容易在细节上翻车。第一REVERSE()到底按字节反转还是按字符反转在 MySQL 的 UTF-8 字符集下REVERSE()是按字符反转的不是简单的字节倒序。比如REVERSE(中国)得到国中这是符合预期的。但你必须在建列的时候就确定字符集后续别改否则可能出现排序错乱。第二排序规则collation决定了LIKE匹配是否区分大小写。如果列用的是utf8mb4_unicode_ciLIKE ab%和LIKE AB%效果一样反转列也应该跟原列保持同一个 collation否则可能出现“正列能查到、反列查不到”的情况。如果原列是大小写敏感的二进制排序反转列就必须同样敏感。第三中文场景下反转后的字符串按编码排序没问题但如果你原列内容包含表情符号4 字节字符字符集必须用utf8mb4别用老的utf8或者utf8mb3否则REVERSE会出现截断或乱码。4.4 数据一致性忘了维护反转列就出大事反向存储最大的隐性成本是数据一致性。线上系统经常有多个入口写入同一张表主业务接口、后台脚本、运营工具、数据订正任务……只要任何一个入口只更新了正列、忘了更新反转列后续所有依赖反转列的查询都会漏数据。我处理过一起真实事故运营通过一个内部 Excel 导入工具批量修改了订单备注工具只 UPDATE 了remark列反转列remark_rev没动。结果用户端按“备注尾词”搜索的内容全部对不上排查了两小时才发现是导入脚本漏了双写。预防手段就三条一是能自动维护生成列就不要手动维护二是所有写入入口代码评审时把反转列列入常规检查项三是在数据链路里加一条对账任务每天随机抽一批数据比对REVERSE(正列) 反转列发现问题第一时间告警。对账成本不高但能拦住绝大部分漏写事故。5. 进阶玩法和其他优化手段的组合拳5.1 配合复合索引处理多条件查询线上真实的查询很少只有单条件更常见的是WHERE status 1 AND order_no LIKE %8888。这种场景反向存储依然有效但索引要配合复合索引来建。假设高频查询是“状态为已支付 订单尾号 8888”你可以建复合索引CREATE INDEX idx_status_rev ON order_tab(status, order_no_rev);查询改成SELECT ... FROM order_tab WHERE status 1 AND order_no_rev LIKE 8888%;这样 MySQL 先是定位status 1的索引区间再在区间内用order_no_rev LIKE 8888%做范围扫描。这比单独两个索引更高效因为避免了回表之后再过滤另一个字段。注意复合索引的字段顺序要遵循最左前缀原则——等值条件的字段放前面范围条件的字段放后面也就是status在前、order_no_rev在后。顺序放反了索引就废掉了。5.2 MySQL全文索引与ngram如果业务真的是包含匹配比如LIKE %8888%反向存储解决不了那就得换工具。MySQL 从 5.7.6 开始支持 ngram 全文索引解析器可以对中文和字符串做分词配合FULLTEXT索引处理包含搜索。建索引ALTER TABLE order_tab ADD FULLTEXT INDEX ft_order_no (order_no) WITH PARSER ngram;查询SELECT ... FROM order_tab WHERE MATCH(order_no) AGAINST (8888 IN NATURAL LANGUAGE MODE);注意全文索引的匹配逻辑和LIKE不完全一样它基于分词8888这种短串能不能分词、分词后能不能命中受ngram_token_size参数影响。而且全文索引在数据量小的时候性能并不突出它擅长的是中等规模数据下的包含搜索不是万能药。真要处理海量数据的复杂模糊搜索还是得靠外部搜索引擎。5.3 数据量大到极致倒排索引是终极方案反转列的本质是给数据多加了一重“面向检索的视图”。这个思路再往前走一步就是倒排索引——不是把单个字符串反转而是把长文本拆成词项每个词项指向包含它的文档列表。你在关系数据库里用LIKE做包含搜索本质上是在做“正排扫描”而搜索引擎从单词 → 文档列表反向映射这才是大规模模糊搜索的终极解法。如果一张表的模糊查询已经多到压垮主库或者查询模式覆盖了前缀、后缀、包含、多关键词组合我建议在架构层面引入搜索引擎。反转存储可以作为低成本过渡方案不要把它当成永久架构。判断标准很简单模糊查询的流量占比超过 10%或者单表数据量超过 5000 万且包含匹配需求明确就应该考虑搜索引擎了。5.4 最后的一点判断力把全文看下来你会发现“反向存储大法”其实没什么高深的技术含量它玩的不过是 BTree 最基础的排序性质。真正体现功力的是在方案落地前后保持清醒LIKE %abc慢先 explain确认到底是 index full scan 还是 ALL别凭感觉优化。反向存储只解决后缀匹配不要对包含匹配抱有幻想。新增反转列优先生成列其次应用层双写触发器是最后的手段。数据分布决定索引收益低选择性场景果断放弃索引方案。线上改造永远是增量演进先解决眼前最痛的查询再逐步评估是否引入更强的搜索架构。我在实际项目里的体会是这类索引优化方案难点从来不在“想出来”而在“守住边界”。反向列一旦加上去就是一个长期存在的数据资产后续所有读写逻辑都要为它负起责任。但只要想清楚、落地稳它确实能用一行LIKE的改写换回几个数量级的查询提速这笔账怎么算都值。