MySQL LIKE ‘%abc‘ 慢查询优化：反向存储与索引原理实战

1. 为什么 LIKE %abc 会拖垮整个查询索引原理与失效真相先说一个我上个月真实遇到的场景某张业务表里存了约两千万条设备唯一码用户在前端输入一段尾号来查询设备SQL 长这样SELECT * FROM device_code WHERE uniq_code LIKE %8XK3Q;第一次跑这个查询的时候我盯着数据库监控看了半天CPU 直接飙到 80%单条查询耗时稳定在 3.5 秒左右。加了索引也没用因为这条 SQL 用的不是不是范围而是LIKE %xxx——前缀是通配符百分号。这里必须先搞清楚 B 树索引的扫描逻辑。MySQL 的普通 BTREE 索引本质上是一种按照字符串从左到右逐字符排序的数据结构。索引的查找入口是从根节点开始按排序后的键值逐步二分定位。当你用LIKE abc%查询时优化器能明确知道搜索的起点是abc这个前缀区域再往后扫到abd之前的节点就可以停止所以前缀匹配可以走索引。而LIKE %abc的情况完全反过来数据库无法确定起始位置因为目标字符串可能出现在任意位置索引的有序性在这里彻底失效优化器只能选择全表扫描。用个生活化类比——手机通讯录是按姓氏拼音排好序的。你想找“姓王的人”翻到 wang 那一页就行这就是前缀匹配。但如果你想找“名字最后一个字是强的人”你没法定位只能从头到尾把通讯录翻一遍。LIKE %abc就是那个“从头翻到尾”的操作。可能有人会问既然字符串居中和结尾的匹配都需要全表扫那把索引建成覆盖索引覆盖查询所需列是不是就快了答案是否定的。覆盖索引只是让数据库不需要回表但扫描本身依然是全量扫描两千万行的聚簇索引逐行读取再快也快不到哪去。真正的问题不是回表而是扫描范围无法缩小。这也是为什么网上总有“MySQL 里 LIKE 百分号开头索引会失效”的说法。更准确地说不是索引失效而是这种查询模式从设计上就无法利用索引的有序性。我们需要的不是吐槽而是一个能从根本上改变查询形态的方案——后端后缀匹配变成前端前缀匹配。2. 反向存储大法的核心逻辑把后缀查询“翻译”成前缀查询思路其实特别简单既然数据库最好走前缀匹配那我们就把字符串反过来存。设备码A8XK3Q反转存储后变成Q3KX8A。原来的查询条件是uniq_code LIKE %8XK3Q反转后的条件就是rev_code LIKE Q3KX8A%。后缀查前缀百分号挪到了右边索引就能正常工作了。这里最关键的转变在于反转操作把“无法利用索引的非前缀匹配问题”转换成了“完全符合最左前缀原则的索引可用场景”。等于绕过了 B 树的限制用存储空间换查询性能。来看具体 SQL 写法-- 原查询慢 SELECT * FROM device_code WHERE uniq_code LIKE %8XK3Q; -- 优化后快 SELECT * FROM device_code WHERE rev_code LIKE Q3KX8A%; -- 等价写法 SELECT * FROM device_code WHERE rev_code LIKE CONCAT(REVERSE(8XK3Q), %);第二个条件里的REVERSE(8XK3Q)会先把用户输入反转成Q3KX8A再加上%结果就是一个标准的可走索引的前缀 LIKE。为什么标题里说能提升“100 倍”因为在两千万行的表上走索引的前缀匹配只需要扫描索引树上的几十个索引条目加上回表也不过几十次磁盘 I/O而全表扫描要逐个读两千万行聚簇索引页。扫描行数从千万级降到几十级耗时差异就是两到三个数量级。不过这里有两个直接成本必须坦诚交代第一存储成本增加。你需要额外加一列存反转值并且为这一列建索引。如果原字段是 VARCHAR(32)反转到 32 字节加上二级索引的冗余存储表会变大写入会变慢。所以这个方案适合“查询多、写入可以接受一定放大”的场景不适合写入极其频繁的日志流水表。第二查询结果需要反转展示。如果业务上需要把rev_code原样显示给用户那么 SELECT 时要么从原字段取要么在查询后再做一次REVERSE()。一般来说我们只把rev_code当成检索列展示仍然走原字段避免额外函数开销。还需要注意反转后的列不能随便用函数包一层再查比如WHERE REVERSE(uniq_code) LIKE abc%这会让索引失效——因为数据库需要对每一行的uniq_code先做反转运算再比较依然全表扫描。记住反转的动作必须在写入时完成查询时只能直接给反转字符串作为前缀条件不要在查询条件里对列套函数。3. 落地实操生成列、触发器与历史数据迁移的完整方案光说思路没有用真正要落地的是怎么把反转列加进现有表里不丢数据、不阻塞线上业务。我实际用过三种方式按推荐排序讲一下。3.1 首选方案STORED 生成列虚拟列落库如果你的 MySQL 在 5.7 及以上版本直接加一列生成列是最干净的做法不需要应用层改任何写逻辑数据库自动维护。建表时这样定义CREATE TABLE device_code ( id BIGINT PRIMARY KEY AUTO_INCREMENT, uniq_code VARCHAR(32) NOT NULL, rev_code VARCHAR(32) GENERATED ALWAYS AS (REVERSE(uniq_code)) STORED, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_rev_code (rev_code) );对已有表则执行ALTER TABLE device_code ADD COLUMN rev_code VARCHAR(32) GENERATED ALWAYS AS (REVERSE(uniq_code)) STORED, ADD INDEX idx_rev_code (rev_code);STORED 关键字表示反转值真实落盘而不是虚拟计算。这样建索引后WHERE rev_code LIKE xxx%可以直接命中索引而且生成列是数据库层维护的写入时多一次计算但不需要应用层配合也没有一致性风险。这里有一个关键细节REVERSE(uniq_code) 有长度上限风险。如果原字段是 VARBINARY 或者包含多字节字符集REVERSE 不会影响字符内容它是按字符反转的但生成列的长度定义必须跟原字段一致或更长否则生成列会因为溢出报错。实践中我一般直接定义同样的 VARCHAR 长度或者干脆用VARCHAR(255)这种兜底长度。3.2 备选方案触发器同步维护有些老环境还在 MySQL 5.6或者你不想动表结构中的生成列那就用触发器。建两个触发器DELIMITER // CREATE TRIGGER trg_device_code_before_insert BEFORE INSERT ON device_code FOR EACH ROW BEGIN SET NEW.rev_code REVERSE(NEW.uniq_code); END// CREATE TRIGGER trg_device_code_before_update BEFORE UPDATE ON device_code FOR EACH ROW BEGIN SET NEW.rev_code REVERSE(NEW.uniq_code); END// DELIMITER ;触发器的优点是灵活可以在反转之外再塞一些处理逻辑比如去空格、统一大小写缺点是触发器的执行属于事务内操作写入性能会有一定损失而且排查问题时比生成列多一层“隐性逻辑”新接手的人看表结构完全不知道 rev_code 是怎么来的。所以触发器更适合过渡场景长期维护还是生成列更稳。3.3 应用层双写最不建议但有人会这么干有些团队不想动数据库就在应用侧写代码时同时写原字段和反转字段。我见过不少踩坑案例典型问题是某个历史接口漏了写 rev_code结果该字段一半有值一半是 NULL查询结果直接缺失。应用层双写听起来简单实际上只要有一个业务入口漏改数据就烂了。反转列的一致性必须由数据库层来保证这是我在项目里反复强调的原则。除非你能保证所有代码路径都经过同一个 DAO 层否则别走这条路。3.4 历史数据迁移存量数据怎么刷如果线上已经有几百万甚至几千万行历史数据加完列之后 rev_code 是空的生成列在建表时也会立即回填但触发器方案下的普通列是空的需要批量回刷。我的做法是分段 UPDATE防止一次性 UPDATE 锁表太久导致业务阻塞-- 分批回刷每批 10000 行循环执行直到影响行数为 0 UPDATE device_code SET rev_code REVERSE(uniq_code) WHERE rev_code IS NULL AND id BETWEEN 1 AND 10000;如果你用的是 STORED 生成列ALTER 时会自动重建表并计算所有反转值这个动作可能很慢两千万行可能要几分钟到几十分钟需要评估业务低峰期操作。MySQL 8.0 的 ALGORITHMINPLACE 也不能完全避免生成列计算的开销唯一办法是提前在测试环境评估锁持有时长。3.5 查询侧改造接口层怎么适配改造完表结构应用层的查询也要跟着改。原来的 DAO 是SELECT * FROM device_code WHERE uniq_code LIKE CONCAT(%, #{tail})改成SELECT * FROM device_code WHERE rev_code LIKE CONCAT(REVERSE(#{tail}), %)注意两点一是REVERSE()是 MySQL 函数如果在数据库里做反转一定要确保传入参数是应用层拼接好的目标字符串二是为了索引可用绝对不要写WHERE rev_code LIKE CONCAT(%, REVERSE(...))这等于又变回后缀匹配所有功夫白费。4. 实测验证两千万行数据下从 3.5 秒到 0.03 秒的完整对比正好拿我实际压测的一组数据说话。测试环境为 MySQL 8.0.288 核 16GInnoDB设备表两千万行uniq_code 为 8~12 位随机大写字母数字串。优化前和优化后的关键差异如下指标优化前LIKE %8XK3Q优化后rev_code LIKE Q3KX8A%扫描方式全表扫描type ALL索引范围扫描type range扫描行数~20,000,000 行4 行耗时3.482s0.028s返回行数4 行4 行额外开销无存储空间增加约 15%写入耗时增加约 8%实测结果用 EXPLAIN 看最直观。优化前执行计划是typeALL, keyNULL, rows20000000, ExtraUsing where优化后变成typerange, keyidx_rev_code, rows4, ExtraUsing index condition耗时从 3.5 秒降到 0.028 秒误差范围内就是 100 倍以上。注意优化后 Extra 里带着Using index condition说明 ICP索引条件下推也生效了MySQL 在索引层就先过滤掉不符合前缀的数据只有命中的几行才回表取整行数据效率自然有质的变化。为了证明这不是我挑出来的极端个例我连续跑了 50 个随机尾号查询优化前平均耗时 3.1 秒优化后平均耗时 0.031 秒基本稳定在百倍差距。另外还测了下写入性能原表每秒插入约 8000 行加生成列和索引后降为每秒 7300 行左右8% 的写入损耗换 100 倍的查询收益这个性价比完全值得。这里额外提醒一句如果你用的是 MySQL 5.7某些版本对生成列加索引还存在一个历史坑——在生成列上建索引时优化器可能偶尔不会选择这个索引表现就是 EXPLAIN 仍然显示全表扫描。解决办法是ANALYZE TABLE重新统计索引基数或者升级到 8.0。我在一个 5.7.26 的客户环境就遇到过一次ANALYZE 之后恢复正常。5. 反向存储的适用边界什么字段适合反转、什么字段别碰反向存储的效果厉害但这个方案绝不适用于所有模糊匹配场景。我甚至可以说如果无脑对所有 LIKE % 查询都上反转列最后大概率出现一种新的慢查询形态甚至把原始字段的长度优势都丢掉了。根据实际经验适用场景有三个突出特征第一查询目标是一个完整标识符的尾部片段。比如优惠码尾号、邀请码后 6 位、设备序列号后 5 位、手机号后 4 位、订单号尾部拼接的校验位。这类字段本身是短字符串、等长或近似等长反转后依然是短字符串存储成本可控。第二最重要查询方输入的不是任意子串而是“后缀”本身。也就是说用户明确知道他要查的是“结尾是 X 的记录”而不是“中间某个位置出现了 X 的记录”。如果业务需求是“只要字符串里包含 abc 就查出来”反转存储完全帮不上忙——包含中间任意位置的字符反转后还是中间任意位置B 树依然无从下口。第三并发查询量高、对响应时间敏感。如果日均查询量只有几十次3.5 秒让人后台跑批无所谓那就没必要折腾。正是因为查询频繁、每次几秒会打爆数据库连接才值得上一套反转列方案。反过来下面这些场景我个人坚决不碰自然语言内容模糊搜索。比如文章标题、简介里的LIKE %python%。这种是典型的全文检索需求用 MySQL 自带 FULLTEXT 索引配合 ngram 解析器都比反转列合适更不用说上 Elasticsearch。原因很简单文本里的关键词不会只出现在结尾反转列没法解决中间出现的问题。字段本身较长。VARCHAR(1000) 的长文本反转索引体积巨大写入成本高得离谱收益又不明显。排序依赖原字段语义。反转列虽然对应原始字符串但按反转列排序结果基本是乱序的。如果业务上需要ORDER BY后端功能码排序别直接把 rev_code 拿来排否则得联合原字段先反转再排序那又是全表函数运算。写入远多于查询的流水表。每行多算一次 REVERSE 写一个二级索引长跑下来 B 树维护成本会放大写入延迟。我见过有人在日志表上做反转列结果写入吞吐下降 30%得不偿失。还有一个容易被忽略的边界已经存在类似紧耦合业务的地方。比如上游系统可能用 uniq_code 做外键关联或者别的团队已经在同步读取这张表。加列本身不破坏现有场景但如果你为了查询效率改变了原字段的语义比如把原字段直接替换成反转字段下游所有按原字段聚合分析的 SQL 全会出错。稳妥的做法是保留原字段新列只作为检索专用不要试图用 rev_code 取代 uniq_code。6. 那几个我差点没扛过去的坑并发双写与边界条件方案跑通不难真正考验人的是边界条件和脏数据场景。我在上线过程中踩过几个硬坑列出来供大家提前避雷。6.1 大小写与 CHARSETREVERSE 不是全能的REVERSE 函数按字符反转不会改变大小写。如果你的查询需要忽略大小写匹配比如设备码里既有大写又有小写直接反转是没用的因为LIKE %abc配LIKE cba%在默认的utf8mb4_general_ci排序规则下不区分大小写反转后倒是能匹配上但你会同时召回ABC、abc、Abc等变体——结果一样但索引基数会被这些小写变体撑大可收缩性变差。最好的做法是写入反转时统一 UPPERREVERSE(UPPER(uniq_code))查询时也将输入先UPPER再反转从源头规范化。6.2 NULL 与空字符串的坑如果原字段允许 NULLREVERSE(NULL) 结果是 NULL索引当然不会收录 NULL。查询时如果REVERSE(#{tail})为 NULL整条查询回空业务上可能把 NULL 误判成不存在。我在实现生成列时专门加了 COALESCE 处理rev_code VARCHAR(32) GENERATED ALWAYS AS ( CASE WHEN uniq_code IS NULL OR uniq_code THEN NULL ELSE REVERSE(UPPER(uniq_code)) END ) STORED但注意如果生成列出现 NULLWHERE rev_code LIKE CONCAT(REVERSE(), %)会查不到任何数据因为LIKE NULL恒为 NULL在 WHERE 中被过滤掉。所以查询层一定要对输入做空字符串拦截提前返回空结果集别把空查询发给数据库。6.3 多字节字符反转后长度溢出的坑中文、日文等多字节字符在utf8mb4下REVERSE 反转后总长度不变但 MySQL 内部存储字节数可能比你预估的长。定义生成列时用VARCHAR(255)这种宽松长度能给未来字段增长留出空间。这个坑容易出现在字段长度严格等于原字段定义时——原字段 VARCHAR(32)反转列也 VARCHAR(32)如果原字段的实际值恰好占满 32 个字符反转列定义一样没问题但如果未来业务把长度加长到 64 而忘了改生成列插入超长数据时生成列计算会报 1406 错误最好在一开始就预留长度。6.4 并发双写触发器方案会在高并发下死锁吗中间有一次切换到触发器方案压测高并发 INSERT 时出现了锁等待。原因是触发器在 BEFORE INSERT 阶段执行 SET 操作相当于每个事务内多了一次对表元数据的隐式依赖并发量大了之后 InnoDB 的行锁竞争会加剧。后来我把触发器逻辑简化成只做SET NEW.rev_code REVERSE(NEW.uniq_code)一行退化成生成列等价的逻辑死锁就消失了。如果你的触发器里有 IF 分支、读其他表等复杂逻辑并发场景下锁范围会被迅速放大千万别这么做。6.5 一个真正锦上添花的小技巧前缀后缀同时查实际需求里还有一种更刁钻的形态“既能按前缀查也能按后缀查最好都能走索引”。我的处理方式是同时维护两个检索字段——原字段走正向前缀索引rev_code 走反向前缀索引查询时分别查两个字段再用 UNION 合并结果去重。例如查所有包含abc的目标可以分别跑WHERE uniq_code LIKE abc%和WHERE rev_code LIKE cba%UNION 之后把中间态去掉了得到的结果范围覆盖“开头是 abc”和“结尾是 abc”两类速度依然远快于全表 LIKE %abc%。6.6 收尾技巧用一条 SQL 统一校验数据一致性上线后别急着认为万事大吉最好定期核对 rev_code 和 uniq_code 是否真的互为反转防止历史脏数据或人为 UPDATE 破坏。一条 SQL 就够SELECT COUNT(*) AS broken_count FROM device_code WHERE rev_code REVERSE(UPPER(uniq_code));平时在监控报表里每跑一次几秒钟查处问题防止一边优化查询一边数据悄悄变质。这一套操作下来那个最初让 CPU 冲到 80% 的查询已经稳稳压在 30 毫秒以内后面新的业务功能又提出了“前缀后缀混合查询”也顺手用上面的 UNION 方案解决了。我个人这几年做数据库优化的体会是真正性价比高的手段往往不是上昂贵的外置组件而是像反转存储这种看起来笨、但完全贴合索引原理的基础操作。如果你的业务里还有大表上LIKE %xxx的低效查询别急着上 ES先在测试环境把这个方案跑一遍大概率能替你省下不少时间。