老周上周被一条SQL搞得没脾气客户表三千多万行按手机尾号找客户条件写的是WHERE phone LIKE %6688一次查询跑了二十九秒慢查询日志每天都被它霸榜。他在phone上明明建了索引可执行计划里type还是ALL全表扫描一点面子不给。这真不是MySQL不给力是LIKE %abc这种写法本身就踩在BTree索引的命门上。后来我们换了个思路——把数据反着存同样的业务查询从二十九秒降到了零点几秒。这个方案不少团队叫它“反向存储大法”我自己的项目里前前后后用了不下五次每次效果都是数量级提升。今天不绕弯子把这个方法从原理、实操到坑点一次讲透适合被后缀模糊查询折磨过的后端开发、DBA和数据仓库同学。1. 先看懂病根LIKE %abc 为什么必然走不上索引1.1 BTree 的排序规则决定了前缀匹配的天然优势MySQL里InnoDB引擎的索引默认是BTree结构叶子节点上的数据按键值有序排列。这个“有序”是核心——搜索的时候可以二分定位、范围扫描。打个比方索引就像一本按拼音排序的通讯录。你找“张”开头的名字直接翻到Z那一摞顺着往后走就行。但如果你要查“最后一个字是‘飞’的人”这本按拼音排的通讯录就帮不上忙了只能一页一页全翻完。对应到SQL里LIKE abc%查以abc开头的字符串索引能从abc这个位置开始到abc开头的最大字符串结束这叫范围扫描range scan。LIKE %abc查以abc结尾的字符串索引不知道从哪里开始扫优化器唯一的选择是遍历整棵索引树或者聚簇索引逐行判断是否符合条件也就是全索引扫描或全表扫描。注意这里有一个很容易误会的点LIKE %abc并不算什么高级陷阱它从第一天起就是全表扫描的命运。MySQL优化器再聪明面对一个没有起始位置的查询条件也变不出定位的魔法。1.2 一张执行计划看懂 ALL 和 range 的差距你可以在本地复现一下CREATE TABLE users ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, email VARCHAR(255) DEFAULT NULL, KEY idx_phone (phone) ) ENGINEInnoDB;插入个几十万条数据再跑EXPLAIN SELECT * FROM users WHERE phone LIKE 138%; EXPLAIN SELECT * FROM users WHERE phone LIKE %6688;第一条执行计划的type字段大概率是rangekey显示idx_phonerows只有满足条件的少量行。第二条执行计划的type基本是ALLrows接近全表行数。优化器告诉我们的信息很明确全表扫描没有索引可用。有人会问那我只查phone这一列是不是走覆盖索引扫描就不会太差确实是覆盖索引扫描但覆盖的是整棵索引树而不是你想要的匹配区间本质上和全表扫没有量级差别尤其是行数一大照样把IO打满。1.3 别被“索引失效”这四个字带偏网上很多文章把这类问题统一归结为“索引失效”这个说法容易让人误解好像索引坏了、需要重建。实际上索引结构好端端的问题出在查询条件无法映射到索引的有序顺序上。真正失效的是“查询谓词与索引组织方式之间的匹配关系”。理解这一点特别重要因为后面讲反向存储时你会发现我们的目标不是修复索引而是重新设计数据的摆放方式让谓词重新贴合索引结构。2. 反向存储大法把后缀匹配变成前缀匹配2.1 核心思路一句话数据反转查询条件跟着反转既然BTree只擅长前缀匹配而业务要的偏偏是后缀匹配那我们就“打不过就加入”——存储的时候把值反转过来。假设原值是abc反转后存储为cba。查询条件LIKE %abc也跟着反转变成LIKE cba%。一个是后缀匹配一个是前缀匹配语义完全等价但后者命中索引。用真实的字段举例手机尾号查询WHERE phone LIKE %6688存储时 phone_rev 8866...整串反转查询变成WHERE phone_rev LIKE 8866%。邮箱域名查询WHERE email LIKE %gmail.com存储时 email_rev moc.liamg...查询变成WHERE email_rev LIKE moc.liamg%。这就是整个方案的全部奥义。没有任何巧妙的魔法就是把数据倒过来放让索引的有序性重新发挥作用。2.2 为什么能提升一百倍全表扫描范围扫描的 IO 差异标题里说“100倍”不是空口喊出来的。你要理解这个量级差异得看IO层面的对比。假设一张三千万行的表每行数据加主键加其他字段平均1KB全表扫描意味着InnoDB要把聚簇索引的叶子节点全部读一遍物理上大概要读几十GB的数据。就算SSD再快这也需要几十秒的IO时间。而走索引范围扫描呢匹配手机尾号%6688可能只有几百行或几千行InnoDB先走二级索引定位到反转后前缀匹配的叶子节点区间再回表读取对应的聚簇索引记录涉及的IO次数可能只有几十次数据量几MB。一个几十GB一个几MB中间差的就是几个数量级。如果你匹配的是%6688这种尾号命中比例大约万分之一那么耗时降两个数量级是常态。我在生产环境看到过从29秒降到0.3秒的真实案例也看到过从12秒降到80毫秒的案例基本都符合这个量级规律。2.3 一个关键技巧反转后的匹配可以做成等值匹配这里有一个多数人忽略的细节。如果业务查询的后缀是一个固定完整的字符串比如“查所有以gmail.com结尾的邮箱”反转后在索引列上其实可以写等值条件之外的“前缀等值边界”但在SQL里需要用LIKE加通配符因为email_rev moc.liamg 只匹配反转后恰好完全等于该值的行而实际行反转后通常还带用户名部分所以应该这样构造SELECT * FROM users WHERE email_rev LIKE CONCAT(moc.liamg, %);这样能利用索引范围扫描而且因为前缀长度足够长扫描区间很小。如果业务只查“邮箱域名完全等于某个域名”反转列也可以配合等值查询做更精确的索引下推比如把域名单独拆一列存储这属于另一个优化话题后面会提到。3. 实操改造从建表到上线的完整链路3.1 生成列方案让 MySQL 自己维护反转值MySQL 5.7 开始支持生成列Generated Column可以在建表或ALTER TABLE时定义一列值由其他列自动计算生成。配合索引可以让数据库自己维护反转值从源头避免应用层双写不一致的问题。ALTER TABLE users ADD COLUMN phone_rev VARCHAR(20) GENERATED ALWAYS AS (REVERSE(phone)) STORED, ADD INDEX idx_phone_rev (phone_rev);这里有两个选择STORED和VIRTUAL。STORED把反转值物理存储在磁盘上查询时直接读代价是多占一份存储空间VIRTUAL不占实际存储查询时临时计算但InnoDB也支持在VIRTUAL列上建立二级索引前提是索引不能是覆盖索引的替代物具体限制要看版本。我的经验是在数据量大且查询频繁的表上优先选STORED用空间换查询性能如果只是偶尔跑分析用VIRTUAL更省空间。插入和更新时不需要额外写任何代码MySQL自动维护这一列INSERT INTO users (phone) VALUES (13812346688); SELECT phone, phone_rev FROM users WHERE id LAST_INSERT_ID(); -- 结果phone13812346688phone_rev88664321313.2 应用层双写方案老业务改造的兼容路线生成列爽是爽但要求你的MySQL版本在5.7以上且如果原表数据量特别大ALTER TABLE加生成列同样要小心锁表。一些老团队会采用更保守的方案在应用层维护反转列。具体步骤老表加一个普通列比如phone_rev VARCHAR(20)建索引。写一个回填脚本分批更新历史数据UPDATE users SET phone_rev REVERSE(phone) WHERE phone_rev IS NULL AND id BETWEEN 0 AND 100000;分批是为了避免一次性UPDATE锁大量行、产生超大事务导致主从复制延迟甚至磁盘瞬时写满。应用层写代码时所有INSERT和UPDATE都同时写phone_rev REVERSE(phone)。查询代码统一封装一个方法比如buildReverseLikeSuffix(suffix)内部返回CONCAT(REVERSE(suffix), %)。应用层双写的坑在于很容易漏改某一条写入路径。漏改一次反转列就和原值对不上查出来的数据就错。所以能用生成列就用生成列能少写代码就少写代码这是我踩过坑之后的肺腑之言。3.3 查询语句与执行计划验证无论选哪种方案查询统一写成这样-- 改造前全表扫描 SELECT * FROM users WHERE phone LIKE %6688; -- 改造后索引范围扫描 SELECT * FROM users WHERE phone_rev LIKE 8866%;验证是否真的走索引千万别省这一步EXPLAIN SELECT * FROM users WHERE phone_rev LIKE 8866%;执行计划里type应该是rangekey是idx_phone_revrows远小于全表行数。如果看到type还是ALL说明哪里写错了最常见的原因就是把函数套在了索引列上——比如WHERE REVERSE(phone_rev) LIKE ...这等于又把索引废掉了。3.4 事务一致性三种写入方案怎么选我把写入逻辑分成三种方案适用于不同团队和演进阶段方案优点缺点适用场景生成列STORED数据库自动维护无法漏写版本要求5.7占存储空间新表、中小规模表生成列VIRTUAL 二级索引省空间查询性能略低于STORED大表但查询不频繁应用层双写不依赖MySQL新特性易漏写、代码侵入大老库、跨库迁移过渡期如果担心历史数据回填时的一致性建议先加列、再分批回填、最后再切查询SQL回填期间新旧SQL并存一段时间对账无误后再下线老查询。4. 边界条件反向存储不是万能钥匙4.1 适用场景盘点后缀匹配、尾号查询反向存储本质上是把“后缀匹配”转化为“前缀匹配”所以最合适的就是后缀匹配业务手机尾号找客户银行卡号后几位、订单号尾号查询邮箱域名后缀筛选文件扩展名搜索商品编码末位匹配这类场景通常基数不高、查询条件固定、匹配模式简单反向存储的改造收益最大代码也最容易统一。4.2 不适用场景包含匹配、模糊语义复杂有两个场景不要碰反向存储第一LIKE %abc%这种包含匹配。反转之后还是包含匹配中间有字符都只是变成LIKE %cba%后缀条件并没有变成前缀索引依旧使不上劲。这种需求请换思路全文索引、ES或者数据仓库的分词方案更合适。第二业务需求其实带有复杂的语义判断。比如“查询地址描述中包含‘花园小区’这种模糊语义”这不是简单的字符串后缀问题哪怕反转了也没用。别把反向存储硬套在不合适的场景里做技术选型先看清楚查询谓词的形状。4.3 备选方案对比全文索引、ES、以及其他数据库的写法遇到后缀匹配/包含匹配时不是只有反向存储这一条路对比一下更安心方案对索引的支持适用场景代价反向存储 BTREE索引后缀匹配变前缀匹配支持range有明确后缀匹配需求侵入写入逻辑生成列方案可降低侵入MySQL FULLTEXT索引按分词匹配%abc%可用布尔模式含中文分词的内容搜索分词质量有限数据量大了性能一般PostgreSQL pg_trgm GIN索引三字符组匹配支持%abc%、%abcPG环境下的模糊搜索索引膨胀明显写入变慢Elasticsearchinverted index通配符、正则查询复杂搜索、全文检索场景引入额外组件数据同步成本高如果你的业务跑在PostgreSQL上其实可以考虑pg_trgm扩展它处理LIKE %abc的效果比反向存储更优雅但如果你在MySQL上反向存储依然是性价比最高的方案。4.4 一个反例用反向列做排序的误区有些文章会延展说反向存储还能优化“倒序排序”比如ORDER BY phone DESC想走索引MySQL本来就支持索引倒序扫描从8.0开始还支持降序索引完全不必靠反转列来实现。反转列只服务“后缀匹配”这一件事别为了其他需求硬造反转列会导致代码理解成本上升、维护变复杂。5. 实测中的坑与进阶技巧5.1 坑一在字段上套 REVERSE() 导致索引继续失效我第一次在项目里推广这个方案时有个同事写出来的查询是SELECT * FROM users WHERE REVERSE(phone) LIKE 8866%;他把反转逻辑写在了字段上而不是条件参数上。结果phone列本身没有反转存储查询时MySQL必须先对每一行的phone做REVERSE计算才能判断是否匹配索引完全无法使用。这一步属于典型的“在索引列上使用函数导致索引失效”。正确的是把反转体现在数据存储和查询参数两边SELECT * FROM users WHERE phone_rev LIKE CONCAT(REVERSE(6688), %);这个REVERSE(6688)在参数上MySQL在执行时只需要计算一次然后拿去索引里执行范围扫描不影响索引使用。5.2 坑二大表回填导致主从延迟与锁竞争前面提过回填要分批这个不能只是说说。我见过一次生产事故某团队夜里跑一次性UPDATE回填全表的反转列结果整个表被锁了半小时业务写入全部阻塞从库延迟从几秒飙到几十分钟。后来我给的方案是-- 每次只处理5000行 UPDATE users SET phone_rev REVERSE(phone) WHERE id IN ( SELECT id FROM users WHERE phone_rev IS NULL ORDER BY id LIMIT 5000 );加一个WHERE phone_rev IS NULL的条件保证重复执行可以续跑再配合低峰期分批执行对主库的影响基本可控。如果表实在太大还可以考虑pt-osc这类在线改表工具原理是建影子表逐步同步最后切换能显著降低对业务的影响。5.3 坑三中文、emoji 等特殊字符的反转边界MySQL的REVERSE()函数在处理纯ASCII字符时没有任何问题但遇到中文、emoji这类多字节字符要小心。MySQL 8.0的utf8mb4字符集下普通汉字反转基本正常但某些特殊Unicode序列比如emoji的ZWJ序列、带组合标记的字符是按字节或者按字符颠倒可能导致显示乱码或者语义变化。如果你要反转的数据包含大量非英文内容建议先在测试环境验证SELECT REVERSE(中文), REVERSE(); -- 视版本和字符集不同结果可能不一样如果确认有问题就不要硬用SQL函数可以改为在应用层用语言内建的反转逻辑比如Golang的[]rune反转Java的反转工具类处理多字节字符更可靠。5.4 进阶技巧MySQL 8.0 的函数索引MySQL 8.0.13 开始支持函数索引Functional Index语法更简洁ALTER TABLE users ADD INDEX idx_email_rev ((REVERSE(email)));这个方案不需要额外定义生成列索引直接建立在函数表达式上。但你写查询的时候表达式必须严格匹配索引定义才能走索引。拿LIKE前缀匹配做实验的话需要验证优化器是否能正确利用函数索引做范围扫描。我的建议是如果用了函数索引执行计划验证比生成列方案更关键一切以EXPLAIN结果为准。5.5 进阶技巧双字段冗余组合一次解决“前缀匹配 后缀匹配”有些业务同时需要“开头是什么”和“结尾是什么”的条件比如查“手机号以138开头、尾号是6688”。光有反转列还不够最好把两个列都建上索引SELECT * FROM users WHERE phone LIKE 138% AND phone_rev LIKE 8866%;MySQL优化器在有多个可用索引时会评估哪个选择性更高可能选择只走其中一个索引再回表过滤也可能做索引合并Index Merge Intersection。三千万行数据下这个查询通常能控制在几十毫秒以内比没有索引时动辄几十秒好太多。收尾的一点实际感受这套方法在项目里用了很多次最大的体会是“数据库索引优化”并不只是建索引、选字段类型这些台面上的功夫更多时候要敢对业务存储结构动刀子。反转列看起来有点“土”但它直接吃透了BTree的物理特性和查询模式的匹配关系效果比任何参数调优都来得直接。如果让我给一个最小落地清单先确认查询是纯后缀匹配再选定生成列还是应用层双写回填脚本分批跑最后用EXPLAIN验证typerange。这一套走完慢查询基本就退出历史舞台了。另外多说一句代码写久了就会发现很多性能问题不一定要靠上中间件、引入新组件来解决先把手头的数据结构用明白往往就有惊喜。