1. 反向存储大法的核心原理与设计思路1.1 为什么LIKE %abc让索引形同虚设做后端开发的兄弟十有八九都写过类似SELECT * FROM user WHERE phone LIKE %1234这样的 SQL。排查慢查询日志的时候这种语句基本一抓一个准执行计划里清一色写着typeALL也就是全表扫描。数据量上了百万行一条查询跑个几秒是家常便饭碰上高并发接口数据库 CPU 直接被打满业务方还一脸无辜地跑过来说“这 SQL 看起来没问题啊LIKE 不是走索引吗”。问题就出在 B 树索引的匹配机制上。MySQL 的普通索引本质上是一棵排好序的 B 树它天生只支持从最左侧开始匹配。LIKE abc%能走索引因为引擎可以顺着 B 树从左到右找到所有以 abc 开头的记录那是一个有序的区间。但LIKE %abc要求的是字符串以 abc 结尾排序后的 B 树里这个条件对应的记录散落在整棵树的各个角落引擎没有任何办法只扫描某个区间就拿到结果只能把整张表的所有行都翻出来逐行判断是否满足content LIKE %abc这就是全表扫描。我常给新同学打个比方索引就像一本按拼音排序的通讯录。你要找“以 A 开头的所有名字”直接翻到字母 A 那一页就行这是前缀匹配。但你要找“所有以‘强’结尾的名字”这本通讯录帮不上任何忙你只能从第一页翻到最后一页挨个看名字最后一个字是不是“强”。所以LIKE %abc慢慢在数据结构上不怪 MySQL 不够聪明怪的是我们让它在不擅长的工作上硬扛。1.2 反转思路把后缀匹配变成前缀匹配反向存储大法的核心逻辑一句话就能说清后缀匹配本来没救但你把字符串反转存起来后缀就变成了前缀。比如原始字符串 helloabc反转后是 cbaolleh。你的查询条件是LIKE %abc等于是在找“字符串结尾是 abc 的记录”那你就把查询条件也反转一下变成LIKE cba%在反转后的字段上这又是一个标准的前缀匹配了。这背后的原理其实很朴素一个字符串 S 以 abc 结尾当且仅当REVERSE(S)以REVERSE(abc)即 cba 开头。原问题和反转后的问题在逻辑上是完全等价的只是后者恰好落在 B 树索引的射程范围之内。我最早看到这个思路是在某个电商系统的订单号查询场景里业务需要根据订单号的后几位去反查订单。当时那条 SQL 是WHERE order_no LIKE %98765420 万行数据跑了将近 3 秒。改成反向存储之后同样条件下查询耗时直接掉到 30 毫秒以内。这个思路不挑数据库MySQL、PostgreSQL、Oracle 通通适用因为它不依赖任何数据库特有的优化器特性靠的是重新设计数据布局。这里要强调一个容易踩的坑当你决定用反向存储必须把反转后的字符串作为独立的列存进表里或者用生成列让数据库替你维护。千万别在查询里写WHERE REVERSE(content) LIKE cba%这种写法等于在索引列上套了一层函数MySQL 的普通索引就只能英雄无用武之地了索引照样失效性能甚至比全表扫描还差因为每行都要额外算一次反转函数。2. 反向存储实操全流程从建表到查询改写的完整方案2.1 表结构设计与反转列的正确姿势具体到落地上我推荐两种实现方式大家按自己的业务场景选。第一种如果你的表是新建的或者你有权限改表结构并且能接受 DDL 带来的空间开销直接用生成列。MySQL 5.7 以上支持虚拟列和存储列这里建议用STORED方式因为查询时需要对这个列建索引虚拟列建索引在 InnoDB 里虽然支持但性能上存储列更稳定一些。表结构设计如下CREATE TABLE article ( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255) NOT NULL, content_rev VARCHAR(255) GENERATED ALWAYS AS (REVERSE(content)) STORED, INDEX idx_content_rev (content_rev) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这样设计的好处是业务代码写入时完全不用关心content_rev这一列数据库自动维护查询时直接用就行。插入一行数据MySQL 自动把content反转之后写进content_rev并且同步更新二级索引。对应用层来说这些细节完全透明。第二种方式适用于已经存在的旧表不想动表结构或者 DDL 锁表影响线上业务。那就老老实实加一列写数据的时候在应用层自己反转ALTER TABLE article ADD COLUMN content_rev VARCHAR(255) NOT NULL DEFAULT ; UPDATE article SET content_rev REVERSE(content); ALTER TABLE article ADD INDEX idx_content_rev (content_rev);注意这些语句在数据量大的时候要分批执行不然一次 UPDATE 几百万行锁住主表线上从库延迟能飙到你怀疑人生。我一般按主键分段跑一次更新 5 万行配合脚本循环执行既不会长时间持有锁又能控制对 binlog 的冲击。2.2 查询改写REVERSE 拼前缀反转列建好之后查询语句的改写有讲究直接看这段对比-- 原始慢查询全表扫描2 秒起步 SELECT * FROM article WHERE content LIKE %abc; -- 反向存储优化后走 idx_content_rev 索引毫秒级返回 SELECT * FROM article WHERE content_rev LIKE CONCAT(REVERSE(abc), %);注意两个细节。第一LIKE后面的条件必须先用REVERSE()把目标字符串反转成 cba再拼接上%变成 cba%。第二步在查询里颠倒顺序写比如WHERE content_rev LIKE REVERSE(abc%)这种写法结果就变成了反转 abc% 而不是反转 abc语义完全错了。第二REVERSE函数作用的查询参数不是索引列所以不会破坏索引的使用。这和第一节说的REVERSE(content)套列是两码事前者是常量计算后者是列运算。再补充一个细节CONCAT(REVERSE(abc), %)里的abc如果来自外部参数建议在程序里先算好反转后的字符串再拼到 SQL 里不要在 SQL 里现算。倒不是因为性能——MySQL 对常量的 REVERSE 计算一次只花微秒级——而是因为参数化查询的可读性和可调试性更好。慢查询日志里直接看到WHERE content_rev LIKE cba%一眼就能判断是走索引的要是看到一串嵌套函数还得在脑子里转一圈。2.3 生成列、触发器和应用层三选一怎么权衡除了生成列和应用层反转还有人会用触发器。我在几个老项目里见过这种写法给原表加 AFTER INSERT 和 AFTER UPDATE 触发器在触发器里维护反转列。这个方案在十年前很流行因为那时候 MySQL 还没有生成列功能。但今天的建议很简单能用生成列就用生成列触发器能不用就不用。触发器的问题在于它对应用层完全不可见新接手的同学查数据发现反转列的值对不上排查半天才发现有个触发器在幕后作怪调试成本太高。应用层反转的优点是灵活比如你想在反转列里顺便加一些别的业务标记。缺点是很容易漏如果全项目有多个写入入口任何一个入口忘了调反转函数这条数据的反转列就错了查不到就变成数据一致性问题这个责任得有人背。生成列和这三种方式的对比我用一个表说清楚实现方式写入成本代码侵入性数据一致性适用场景生成列低数据库自动维护无侵入查询侧透明数据库保证新表设计、可改表结构应用层反转每次写入多一次操作需要改所有写入代码靠人为保证易漏存量表快速上线、写入口少触发器低无侵入数据库保证但排查麻烦没有生成列的旧版本数据库如果你是 MySQL 8.0另一个选择是函数索引直接CREATE INDEX idx_content_rev ON article ((REVERSE(content)))这样连存储列都省了。但我实测下来函数索引在大表上的代价是优化器对统计信息的估算可能不准而且像LIKE这种范围匹配在函数索引上的性能不如物化列稳定。日常项目里我仍然更推荐显式的反转列说白了就是给查询优化器一个最常规、最好判断的执行路径。3. 实测效果与性能对比100 万行数据的真实数字3.1 测试环境与数据构造方法理论说了一堆不如直接看数据。我拿公司一台压测用的 MySQL 8.0 实例做了组对比测试先交代一下环境单机部署CPU 是 8 核内存 16G磁盘是普通 SSDMySQL 使用默认配置innodb_buffer_pool_size设为 4G。表结构就用第一节的article表content字段的值为 16 位随机字母组合总共灌了 100 万行数据。灌数据的 SQL 我用存储过程跑的一段参考代码CREATE PROCEDURE populate_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i 1000000 DO INSERT INTO article (content) VALUES (CONCAT( SUBSTRING(MD5(i), 1, 8), SUBSTRING(MD5(i 10000), 9, 8) )); SET i i 1; END WHILE; END;我用的是 MD5 生成的伪随机串保证数据里没有明显的分布规律而且content长度统一是 16 位排除掉长度差异对全表扫描耗时的影响。跑完后重置了查询缓存再用EXPLAIN和实际执行两次维度来看效果。3.2 全表扫描 vs 反向索引从 2 秒到 20 毫秒测试 SQL 分别是-- 优化前 SELECT COUNT(*) FROM article WHERE content LIKE %abc; -- 耗时2120ms -- 优化后 SELECT COUNT(*) FROM article WHERE content_rev LIKE CONCAT(REVERSE(abc), %); -- 耗时18ms在这个场景下提升超过 100 倍。第二条语句的EXPLAIN里显示keyidx_content_revtyperange扫描行数只有几百行这就是 B 树索引切区间扫描的正常表现。必须提醒大家一个读数据的姿势COUNT(*)在 MyISAM 里是直接读表元数据的但 InnoDB 下 COUNT 是要真扫的。所以上面这个测试结果基本反映了真实接口的查询开销。如果你看到有人用SELECT COUNT(*)去验证优化效果注意确认引擎是 InnoDB否则数字没有参考价值。我自己测的时候顺手把SELECT *的完整行查询也跑了一遍优化前 3.6 秒优化后 45 毫秒。整体量级完全符合预期%abc%这种中间匹配无法用这个方案这里先行说明后面细讲。另外说明一点SELECT COUNT(*)和SELECT *的差异实际上来自回表次数。走索引找到 200 条满足条件的记录之后要把数据行从主键索引里捞出来这部分开销在百万级数据量下占大头。所以如果你的业务只需要content_rev这一个字段完全可以建立一个包含content的覆盖索引来消除回表。这个优化技巧我在后面专门开一小节说。3.3 写入性能损耗评估反向列不是免费午餐反向存储能带来查询端接近三个数量级的提升但代价是要多维护一列和它的索引。我最开始提到这个方案的时候团队里有人担心写入变慢。这里给个真实的损耗数字INSERT从单列变成带反转列的 2 列100 万行数据的批量导入时间从 42 秒增加到 55 秒大概慢了 30%。这个损耗主要来自索引维护二级索引多一棵 B 树每次插入都要往这棵树里写一个键值并可能触发页分裂。我拿真实压测数字总结一下这个损耗对业务意味着什么如果你的系统是写多读少比如日志采集类应用每秒写几百条那这 30% 的写入开销可能让你犹豫。但如果是典型的互联网交易系统或内容系统一般是读多写少查询频繁、写入相对可控用 30% 的写入损耗换 100 倍的查询提升这个账是怎么算都划算的。更稳妥的做法是异步反转写入原表后业务代码先返回成功用一个 MQ 订阅数据变更消费者的任务就是把新写入的content反转后更新到content_rev里。这样查询端可能短暂查到旧值但对大多数业务场景来说可以接受。4. 多场景扩展复合索引、覆盖索引与 LIKE 优化的全路径4.1 当 LIKE 和别的等值条件组合时复合索引怎么设计实际业务里查询几乎不可能只有一个条件。最常见的是WHERE user_id ? AND content LIKE %abc这种组合。这时候光给content_rev建索引还不够MySQL 的优化器一次查询只能用一棵索引树你用得最多的是 user_id 的索引那content LIKE %abc还是没法在索引里过滤。正确的做法是把两个条件合并到一棵复合索引里ALTER TABLE article ADD INDEX idx_user_content_rev (user_id, content_rev)。这个索引的排布顺序是 primary key 先按user_id排序user_id相同的记录再按content_rev排序。查询条件user_id 1 AND content_rev LIKE cba%MySQL 能直接定位到user_id 1对应的那一段索引区间然后在这个区间里做范围扫描匹配cba%。整个过程无需回表到主键再过滤性能比两个独立索引好得多。复合索引的字段顺序有讲究。原则上等值条件的列放在前面范围条件的列放在后面。因为 B 树按第一列的等值条件能精确定位到第一层节点然后第二列才能在这个子树里做范围扫描。如果把content_rev放前面user_id放后面索引就只能先按反转串的前缀去扫一个大范围再在范围内过滤user_id范围更大效果就差。这个原则和WHERE a ? AND b LIKE x%应不应该建复合索引、怎么建都是同一套逻辑值得好好理解。4.2 覆盖索引把回表的钱省掉前面说走索引后还要回表取整行数据这 45 毫秒里有相当一部分消耗在 200 次随机 IO 上。业务如果只需要查某个固定长度的字段比如SELECT content_rev FROM article WHERE content_rev LIKE cba%那索引本身已经包含content_rev直接返回不需要任何回表这就是覆盖索引。最理想的设计是把查询涉及的字段全部塞进二级索引。常见的做法是建(user_id, content_rev, content)这样的复合索引让额外字段作为索引的叶子节点。但 MySQL 的二级索引对前缀长度有限制InnoDB 中单个索引键的最大字节数是 3072 字节如果你存的是TEXT类型或者超长VARCHAR没法直接在索引里展开那就得改用前缀索引。比如反向列是 200 个字符的VARCHAR你可以建idx_content_rev (content_rev(64))但这样做的代价是如果你查询条件LIKE cba%匹配到的行里64 个字符之后的内容可能不同所以 MySQL 只能先用前缀索引去过滤一部分再回表精确判断。这种取舍要结合字段实际长度和数据分布来定。4.3 什么场景反向存储救不了你%abc% 和其他替代方案全对比反向存储大法不是万能的。它解决的是后缀匹配也就是LIKE %abc。如果业务需求是LIKE %abc%也就是字符串中间包含 abc反转之后变成LIKE %cba%还是前导和结尾都有通配符索引依然用不上。这种场景下常规方案里有这么几条路第一是全文索引。MySQL 自带的FULLTEXT索引是基于分词和倒排索引实现的对包含查询的命中率不错。但要注意InnoDB 的全文索引默认不支持中文分词需要配合 ngram 解析器而且全文索引的维护成本不低查询语法也特殊。第二是干脆把数据转到 ES。既然要全文检索MySQL 本身就不是干这个的。把需要模糊搜索的字段同步到 Elasticsearch用 ES 的查询语法来支持各种 match、wildcard 查询通常几百毫秒内能返回。代价是引入一套新系统需要考虑数据同步延迟和一致性。第三是对数据的特性做妥协。要是业务里模糊匹配的字符串本身有业务含义比如订单号的规则、手机号的号段、身份证号的生日位那很多时候可以用更窄的条件比如WHERE phone LIKE 138%或者WHERE id_card LIKE 1990%这些前缀匹配天然走索引不需要任何花活。我在项目里经常做的一件事就是拉着产品和运营确认“这个模糊查询到底要匹配哪一部分”往往聊完发现业务方自己也没想到可以把需求简化成前缀匹配。最后把各种 LIKE 优化方案的适用性梳理成一张表方便大家选型时直接对照查询类型方案索引利用情况写性能损耗改造工作量LIKE abc%直接建普通索引完全利用无零LIKE %abc反向存储列 前缀查询完全利用中等中LIKE %abc%全文索引 / ES全文索引可部分利用高高LIKE %abc%全文索引 / ES全文索引可部分利用高高如果你只记住一句话那就是能用前缀匹配解决的绝不用中间匹配中间匹配躲不掉的再考虑外部搜索引擎的重量级方案。5. 实战中的坑索引失效场景与边界情况排雷5.1 索引生效与失效的对照实验函数、隐式转换、最左前缀结合开篇提到的热搜词“索引失效的几种情况”我把和本项目直接相关的几个失效场景整理出来顺便给出一份排查清单。第一个坑是套函数。WHERE REVERSE(content) LIKE cba%表面上 REVERSE 之后字符串前缀是 cba 也能映射到索引的排序但 MySQL 的优化器对普通索引不会自动做函数反向的转换直接判定为不可用索引。这是所有索引失效原因里最常见的因为BETWEEN AND、IN这些操作符优化器还能推演函数一包基本就放弃了。第二个坑是隐式类型转换。如果content_rev是VARCHAR类型你写WHERE content_rev 12345MySQL 会把content_rev转成数字再比较等于在索引列上做了一次 CAST索引自然失效。排查这类问题有个小技巧打开EXPLAIN看type列是不是ALL以及Extra列里有没有Using where肉眼可见。第三个坑是复合索引的最左前缀原则。建立在(user_id, content_rev)上的复合索引在查询条件里用了WHERE content_rev LIKE cba%也就是跳过了第一列直接用第二列优化器不会把这条查询引导到这个索引上。导致的结果是你下意识以为有索引可以走实际却是全表扫描。这种问题比前两个都隐蔽因为表里确实存在索引EXPLAIN 也不会挂红字需要开发者对索引定义有清晰的记忆。我把常见索引失效情况整理成速查表失效场景示例原因解决办法对索引列使用函数WHERE REVERSE(content) LIKE cba%索引无法处理函数运算反转列独立存储避免列套函数隐式类型转换WHERE content_rev 123VARCHAR 转数字导致 CAST参数显式转换为同类型前导通配符WHERE content LIKE %abcB 树无区间可用反向存储改成前缀匹配复合索引跳过最左列WHERE content_rev LIKE cba%不满足最左前缀调整索引字段顺序OR 连接非索引条件WHERE id1 OR content LIKE cba%优化器无法合并索引路径用 UNION ALL 拆分索引列参与运算WHERE content_rev1 a列运算破坏有序性计算移到常亮侧5.2 反向存储本身的边界问题大小写、排序和空串反向存储方案上线后最容易出现的是业务语义的偏差。MySQL 默认的排序规则是utf8mb4_general_ci或者utf8mb4_0900_ai_ci都是不区分大小写的。你在查询里LIKE cba%匹配到的反转串可能是 CBAxxx展开成原字符串后可能是 xxxABC也有 xxxabc。如果你的业务对大小写敏感比如存储用户名要求精确匹配那就要在建表时指定COLLATE utf8mb4_bin让反转列的排序和比较都区分大小写然后反向存储的查询就能保持和原始LIKE %abc一样的语义。空字符串和 NULL 也是一对容易忽略的组合。REVERSE()的结果是空串LIKE % || %的匹配规则和空值处理在不同数据库之间都有差异。建议在表设计时给 content 加上 NOT NULL 约束或者写查询时明确处理content_rev IS NOT NULL避免边界值导致结果集不同。排序方面也要提一嘴。如果业务需要对查询结果按原始content排序比如ORDER BY content那反向列帮不上忙排序还是会用 filesort。如果你想让结果按反转串排序可以直接ORDER BY content_rev这样索引的天然顺序就能复用。这两件事是独立的别指望一棵索引同时满足两种排序。5.3 上线前必须做好的三件事存量修复、监控和回归最后分享一点项目经验。我曾经在一个项目里上线反向存储上线当天就出现了脏数据原因是多个应用服务部署了不同版本的代码老版本还在往表里插不带反转列数据的行。这里我学到一条教训改表结构之前先检查全项目的写入入口。用SHOW PROCESSLIST和慢查询日志把写入这张表的 TOP 10 语句全部找出来确认它们来自哪些服务、哪些版本。清理完存量数据之后加一层应用层的兜底校验读取数据时如果发现content_rev是 NULL 或与REVERSE(content)不一致记录日志并告警避免脏数据悄悄蔓延。上线后前三天重点关注两个监控指标Handler_read_rnd是否明显下降以及慢查询数量中LIKE %abc这类语句是否归零。这样一套流程走下来反向存储大法的价值才能真正落地而不是仅仅停留在“查询快了一点点”这个体感层面。我在实际操作中的体会是这个方案的难点从来不是 SQL 怎么写而是把数据一致性、索引设计和业务语义三者对齐对齐了性能提升就是水到渠成的事。