企业官网建设流程全解析

凌晨两点，一条慢查询报警把值班群炸醒：一张 3800 万行的订单表，后台同事为了查“近 30 天尾号 8888 的订单”，写了一条WHERE order_no LIKE '%8888'，跑了 12.8 秒还没出结果。这条 SQL 在业务上完全合理，但数据库的索引在这里帮不上任何忙——LIKE '%abc'这种后缀模糊匹配，天生和 B+Tree 索引八字不合。后来我用“反向存储大法”做了改造，同一查询从 12 秒降到 0.09 秒，索引效率直接跨了两个数量级。

文章适合谁读？凡是线上有过LIKE '%xxx'慢查询经历的后端、DBA、架构师，或者正在为模糊查询优化发愁的同学，这篇都值得看完。我会把原理、落地步骤、踩坑边界一次讲透。

1. 同样叫模糊查询，为什么 abc% 走索引，%abc 就全表扫

1.1 从B+Tree的排序逻辑说起

先说个前提：MySQL 的 InnoDB 索引用的是 B+Tree，底层叶子节点按索引列的值有序排列。这个“有序”是所有索引优化的基础。你可以把 B+Tree 想象成一本按拼音排好序的《新华字典》——你想查“zhang”这个音节，绝不会从第一页开始翻，而是直接翻到字典中部偏后的位置，再二分定位。索引树的查找逻辑也一样，数据库根据你给的关键字，在树里一层层缩小范围，直到找到目标区间。

关键在于：字符串的“有序”是从第一个字符开始比的。'abc' < 'abd' < 'abe'，MySQL 比较两个字符串时，是从左往右逐字符比对的。这听起来像废话，但所有模糊查询能不能用上索引，全都是围绕这一条展开的。

1.2 模糊查询的三种姿势与索引的关系

我们平时写LIKE，其实有三种形态，它们的命运完全不同：

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 之后引入了 ICP（Index 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。核心就是“骗过”B+Tree 的排序规则，让无迹可寻的后缀变成有据可依的前缀。

打个比方：你有一堆书，按书名首字母排好序（正排索引），想找“书名以 XX 结尾”的书得一本一本翻；但如果你把所有书名倒着写一遍再排序，结尾词就变成了“倒序书名的开头词”，查起来自然就是二分定位了。

2.2 一个订单尾号查询的完整改造示例

拿开头那个订单表举例。原始 SQL：

SELECT 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);

改造第四步，改写 SQL：

SELECT 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 Column）：

CREATE 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) ) ENGINE=InnoDB;

生成列的意思是：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 自己决定扫哪一段。跑完之后写个对账 SQL：

SELECT 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 DDL，CREATE 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是range，key是你新建的索引，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 比全表顺序扫描贵得多。所以优化器大概率直接选 type=ALL，你的索引白建了。

正确做法是：建索引之前先测一下匹配结果的比例。你可以先跑一条统计 SQL 看命中量，如果命中量超过全表的 10%，反向存储的收益基本为零，甚至可能更慢。这不是索引没用，而是这个查询场景本身就不适合走索引。

4.3 大小写、排序规则与字符集细节

反向存储对字符集和排序规则比较敏感，很容易在细节上翻车。

第一，REVERSE()到底按字节反转还是按字符反转？在 MySQL 的 UTF-8 字符集下，REVERSE()是按字符反转的，不是简单的字节倒序。比如REVERSE('中国')得到'国中'，这是符合预期的。但你必须在建列的时候就确定字符集，后续别改，否则可能出现排序错乱。

第二，排序规则（collation）决定了LIKE匹配是否区分大小写。如果列用的是utf8mb4_unicode_ci，LIKE '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 最后的一点判断力

把全文看下来，你会发现“反向存储大法”其实没什么高深的技术含量，它玩的不过是 B+Tree 最基础的排序性质。真正体现功力的，是在方案落地前后保持清醒：

• LIKE '%abc'慢，先 explain，确认到底是 index full scan 还是 ALL，别凭感觉优化。
• 反向存储只解决后缀匹配，不要对包含匹配抱有幻想。
• 新增反转列，优先生成列，其次应用层双写，触发器是最后的手段。
• 数据分布决定索引收益，低选择性场景果断放弃索引方案。
• 线上改造永远是增量演进，先解决眼前最痛的查询，再逐步评估是否引入更强的搜索架构。

我在实际项目里的体会是：这类索引优化方案，难点从来不在“想出来”，而在“守住边界”。反向列一旦加上去，就是一个长期存在的数据资产，后续所有读写逻辑都要为它负起责任。但只要想清楚、落地稳，它确实能用一行LIKE的改写，换回几个数量级的查询提速，这笔账怎么算都值。