企业官网建设流程全解析

开头：

高性能高并发项目里被慢查询干过的同学，一定对这条 SQL 不陌生：WHERE col LIKE '%abc'。明明就多了一个前置百分号，索引就像失灵了一样，数据量一上来从几百毫秒一路涨到几秒甚至几十秒。今天聊聊我实际优化过的一种方案——“反向存储大法”，或者叫反转索引。思路不算复杂，就是把存储和查询两侧的字符串都做一次镜像反转，把LIKE '%abc'变成LIKE 'cba%'，让 B+Tree 索引重新“愿意”工作，实测在千万级数据下做到了接近百倍的响应提升。这篇东西适合天天跟 MySQL 打交道的后端开发、DBA、数据运维同学，也适合那种每次提性能优化都被“加索引”搪塞、想搞明白索引到底为什么失效的入门者。我会把从原理、落地 SQL、EXPLAIN 对比到踩坑边界全部写透，尽量让你看完能直接照着改。

1. 从一条 3 秒的慢查询说起：LIKE '%abc' 到底死在哪一个环节

先还原一个我实际遇到过的场景。

业务方要按订单号后几位查单，比如搜“最后几位是 20241012 的订单”，需求提得很朴素——WHERE order_no LIKE '%20241012'。本地测试环境几十万行数据跑起来毫无感觉，两三百毫秒还能忍。等上了生产，某个核心流水表到了千万级、日志表到了上亿级之后，这条查询直接出现在慢查询 TOP 榜上。用户每点一次搜索，背后就是在全表做一次字符串扫描，别提多酸爽。

问题来了：为什么LIKE 'abc%'能走索引，LIKE '%abc'就非得全表扫？

1.1 B+Tree 索引的本质：一本按字母序排好的词典

InnoDB 的 B+Tree 二级索引，本质就是一个排好序的结构。排好序意味着什么？意味着数据库可以从根节点开始二分查找，快速定位到某个“起点”，然后顺序往下扫，直到遇到不满足范围条件的数据为止。这是索引拯救查询性能的根基。

打个比方：你手里有一本按英文字母顺序编排的词典，想找所有以le开头的单词，可以直接翻到词典 L 区，从le这一页往后翻，基本是 O(log n + m) 的代价，m 是命中条数。但如果你要找所有以le结尾的单词，词典的字母序编排帮不上任何忙——因为同一个结尾可以出现在词典完全不同的角落，你只能把整本词典一页页翻完，挨个看最后一个字母是不是le。又慢又绝望，但这就是逻辑上的必然。

数据库也一样。二级索引页按前缀排序，LIKE 'abc%'能明确给出一个扫描起点：先命中abc这个键值，然后往右扫到所有超出abc前缀范围的行停止。而LIKE '%abc'没有一个可定位的起点，优化器只能遍历整棵索引树或者堆表。

1.2 优化器为什么不给面子：EXPLAIN 里的真相

这种慢查询，直接EXPLAIN看执行计划，输出往往非常“感人”：

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | rows | ... | Extra | +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | t_log | ALL | NULL | NULL | 12478910| ... | Using where| +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

type=ALL是全表扫描，rows=12478910是优化器预估要扫的行数。百万、千万级数据量下，单条查询就是一次全表 IO 风暴，冷缓存场景下慢得尤其明显。

这里有个无数人踩过的误区：觉得“只要加个索引就能救”。可问题是，针对col本身建索引，LIKE '%abc'依然没法用上。因为 B+Tree 的有序性只对前缀有效，你查后缀相当于在已经排好序的数据里干一件需要全局遍历的事。索引在，但优化器判断使用索引甚至比全表扫更亏，因为扫描二级索引之后还得回表。所以真正的解法不是“再建一个同样的索引”，而是改变数据的组织方式，让“后缀查询”变成“前缀查询”。这就是下文的思路来源。

2. 反向列加查询改写：把后缀查询硬生生变成前缀查询

“反向存储大法”的核心一句话可以概括：存储时把字符串反着写，查询时把条件反着拼，让数据库能用前缀索引去支持“原后缀匹配”。

原查询是WHERE col LIKE '%abc'。如果我们在表里额外维护一列col_rev，让col_rev = REVERSE(col)，那存储层的数据面貌就变了：原本order_no='20241012'的行，order_no_rev='2101404202'。

查询时我们对关键字也做反转：REVERSE('20241012')='2101404202'。于是后缀匹配order_no LIKE '%20241012'就等价于前缀匹配order_no_rev LIKE '2101404202%'——注意，这下前置百分号跑到屁股后面去了，索引可以正常从2101404202这个键值开始向右范围扫描。一前一后，世界完全不一样。

2.1 从加列到回填：一套能直接抄的 DDL 流程

以 MySQL 为例，完整操作分四步。

第一步，加反向列。字符集、排序规则必须与原列保持一致，否则可能出现大小写敏感不一致之类的诡异问题。

ALTER TABLE t_order ADD COLUMN order_no_rev VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci AFTER order_no;

第二步，回填存量数据。生产环境表如果很大，不要一条 UPDATE 梭哈，锁表时间会爆炸。要分批回填，比如每次只处理一个 id 区段，或者按主键范围循环处理。

-- 小表可以直接一次性回填 UPDATE t_order SET order_no_rev = REVERSE(order_no) WHERE order_no_rev IS NULL; -- 大表务必分批，例如： UPDATE t_order SET order_no_rev = REVERSE(order_no) WHERE id BETWEEN ? AND ? AND order_no_rev IS NULL;

第三步，建索引。

ALTER TABLE t_order ADD INDEX idx_order_no_rev (order_no_rev);

第四步，改写查询。

-- 优化前 SELECT * FROM t_order WHERE order_no LIKE '%20241012'; -- 优化后 SELECT * FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE('20241012'), '%');

有必要多说一句：优化后的 SQL，仍然建议把原条件带上作为二次校验，类似AND order_no LIKE '%20241012'。为什么？反转函数在绝大多数常规字符串上没问题，但在多字节字符、特殊排序规则、隐藏字符等极端场景下，你不敢 100% 赌它和原 LIKE 完全等价。带上原条件，索引扫描先用反向列把扫描范围缩小到极小，然后原条件做精确过滤。这一招在灰度阶段尤其关键，稳妥第一。

2.2 生成列和函数索引：让数据库自己维护反向值

手工维护一个冗余列最大的痛点，是应用层写入时容易漏。今天新增了一个写入入口忘了写order_no_rev，明天查不到数据，后天线上告警，长期下去反向列就成了脏数据源。所以我在正式项目里更推荐用生成列（Generated Column）让数据库自己算。

MySQL 5.7 及以上，可以建一个存储生成列：

ALTER TABLE t_order ADD COLUMN order_no_rev VARCHAR(64) GENERATED ALWAYS AS (REVERSE(order_no)) STORED; ALTER TABLE t_order ADD INDEX idx_order_no_rev (order_no_rev);

STORED生成列会把反转结果物理落盘，读的时候不用现场计算；写入时数据库自动维护，应用层完全不用关心。这条路径的好处是业务代码改动量最小，新数据天然一致，存量一把回填完就持续稳定。

MySQL 8.0.13 以上，甚至可以直接建函数索引，连冗余列都省了：

ALTER TABLE t_order ADD INDEX idx_order_no_rev ((REVERSE(order_no)));

查询时写成WHERE REVERSE(order_no) LIKE CONCAT(REVERSE('20241012'), '%')。函数索引本质上和生成列索引是同一个思路，优化器能识别表达式并走索引。但我要提个醒：函数索引的优化器识别能力在不同版本之间有差异，生产环境务必先看EXPLAIN确认走了range或ref，别想当然。

那到底选冗余列、生成列还是函数索引？我的习惯是：MySQL 5.7 用STORED生成列，MySQL 8.0 优先函数索引但必须用 EXPLAIN 验证；如果团队里有不少新人，冗余列 + 应用双写反而最直观，配合定期校验脚本也能控住脏数据。没有银弹，按你的运维精力选。

3. 同样的搜索条件，EXPLAIN 前后的差别：9.8 秒到 15 毫秒

方案讲得再好，没有实测数据就是耍流氓。下面是我在测试环境里跑过的一组对比，机器配置是 8 核 16G、普通 SSD、MySQL 8.0.28，表里压了 1200 万行订单数据，order_no长度在 16 到 24 位之间混合分布，查询目标是“找 rear 号段后缀为某固定值”的记录。

优化前 SQL：

SELECT * FROM t_order WHERE order_no LIKE '%20241012';

冷缓存 +innodb_buffer_pool_size未完全预热的情况下，我连续跑了 10 次取均值，单次耗时9.8 秒，EXPLAIN 显示type=ALL, rows≈12000000, Extra=Using where。全表扫一块约 1.8GB 的表文件，这个数字一点不夸张。

加完反向列和索引后，改写为：

SELECT * FROM t_order WHERE order_no_rev = CONCAT(REVERSE('20241012'), '%');

严格来说应该用LIKE CONCAT(REVERSE('20241012'), '%')，实际执行计划类型通常显示为range，走了idx_order_no_rev索引，预估扫描行数骤降到几百行，单次耗时均值15 毫秒左右。从 9.8 秒到 15 毫秒，正好是三个数量级，跟标题里“100 倍”的说法在方向上完全吻合。当然，真实倍数是由命中的数据量决定的，下面细说。

3.1 执行计划变化怎么看

改写前后的关键差异，看EXPLAIN几个核心字段就够了：

rows从千万级掉到百级，意味着扫描成本缩小了几个数量级。二级索引定位到2101404202...前缀对应的叶子节点，只在这些位置回表，IO 次数和 CPU 消耗自然都不是一个量级。

有一点要说明：很多人在这个阶段容易犯强迫症，看到Extra=Using index condition还不够，总想折腾成Using index做覆盖索引。如果你的查询列能被索引完全覆盖，那确实能省掉回表；但对于宽表来说，索引覆盖很多字段反而让索引体积变大、写入变慢，属于典型的过度优化。我的建议是：业务查询要取几列，就把这几列评估一下，常用且足够窄的再加到索引里，别为了图标好看把整张表塞进索引。

3.2 为什么有的场景提升不到 100 倍：命中行数才是底层变量

很多人把“100 倍”当成玄学，觉得是不是什么场景都能这么猛。其实加速比背后是一个非常朴素的公式：扫描行数的下降比例，约等于响应时间的下降比例上限。你原来全表扫 1200 万行，现在索引只扫 300 行，扫描量下降了 4 万倍，但回表、随机 IO、SQL 解析这些开销还在，所以实际响应时间不会等比例下降，但降两三个数量级很正常。

反过来，如果你的“后缀”是高频值，比如所有订单号都以2024结尾，那反向列索引扫出来的第一个键值就开始大范围命中，rows依然可能是几十万甚至几百万。索引确实“走”了，但回表代价摆在那里，响应时间还是慢。这种场景，反向存储救不了你，要考虑的是分区、汇总表或者换精确匹配。

所以判断一个查询适不适合做反转优化，先做一个小样本统计：

SELECT COUNT(*) AS cnt FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE('20241012'), '%');

这个cnt大致就是索引要扫的行数。如果cnt相对总行数很小，提升会很明显；如果cnt都占总量百分之十几了，别折腾了，这条路收益有限，直接换方案。

4. 不是银弹：五类边角场景会让反向列白建

我在多个项目里踩过不少坑，有些还挺隐蔽。这一章把它们全列出来，能避免的避免，不能避免的提前想好对策。

4.1 坑一：存量回填和一致性校验

前面说了生成列可以自动维护新数据，但存量数据回填这一步依然躲不掉。回填最容易出的问题，不是 SQL 写错，而是回填动作和生产写入并发执行，导致“回填完之后又插入了反向列 IS NULL 的新行”。所以大表回填一定要分批，并且建议回填完成后再跑一遍校准脚本：

-- 找反向列与反转原列不一致的数据 SELECT COUNT(*) FROM t_order WHERE order_no_rev <> REVERSE(order_no) OR (order_no IS NOT NULL AND order_no_rev IS NULL);

这个该校验脚本应该被放进定期巡检任务，而不是只跑一次。尤其是那些用了手工冗余列、没有走生成列的旧系统，脏数据全靠这个脚本兜底。

4.2 坑二：把“包含匹配 %abc%”也拿来反转

LIKE '%abc'和LIKE '%abc%'是两种完全不同的需求。前者是“以 abc 结尾”，反转成cba%之后索引能帮忙；后者是“包含 abc”，反转之后变成%cba%，前面还是有一个%，索引照样废掉。我见过不止一次，同事做了反向列之后开开心心把LIKE '%abc%'改成LIKE CONCAT('%', REVERSE('abc'), '%')，跑完 EXPLAIN 发现还是全表扫，跑回来质问我方案是不是没用。真不是没用，是问题从“后缀匹配”偷偷换成了“包含匹配”。

“包含匹配”本质是子串搜索，靠 B+Tree 解决不了。利落一点，上全文索引、ngram 分词，或者干脆把数据同步到 Elasticsearch / ClickHouse 这类专门干检索的引擎。别在 MySQL 里硬扛。

4.3 坑三：REVERSE 不是万能安全函数

在 utf8mb4 下，MySQL 的REVERSE()是按字符反转而不是按字节反转，常规中文、英文、数字问题不大。但要注意几类数据：

• 含有 emoji 等多字节组合字符时，反转结果的排序规则表现可能跟你预期不一致；
• 大小写不敏感排序规则下，原列和反向列的 collation 必须一致，否则查询时比较规则不同，可能出现该命中没命中的情况；
• 如果列定义里带特殊字符集，比如utf8mb4_bin和utf8mb4_general_ci混着用，结果全凭运气。

落地时最省心的做法：反向列必须显式复制原列的CHARACTER SET和COLLATE，不要手滑用默认值。

4.4 坑四：搜索关键字本身含通配符

用户搜索的内容如果包含%或_字符，比如搜索单号里本身就带百分号，直接拼CONCAT(REVERSE('%abc'), '%')会把通配符也当模糊条件解析掉，结果查询结果完全错乱。这时候需要ESCAPE关键字，标准的写法类似：

SELECT * FROM t_order WHERE order_no_rev LIKE CONCAT(REVERSE('abc'), '%') AND order_no LIKE '%abc' ESCAPE '!';

反转列这边的斜杠转义也建议显式处理：

WHERE order_no_rev LIKE CONCAT(REPLACE(REVERSE('abc'), '%', '\\%'), '%');

反正一句话：凡是用户输入直接进入 LIKE 的，先做转义永远没错。

4.5 坑五：过度使用反向列导致写入放大

一个人口众多的业务表如果搞了三五个反向列，每个列都要额外索引，写入放大和存储膨胀会很可观。反向列最优解只用在那些“后缀检索频率极高、结果行数占比很小”的字段上。用了它，就不要再朝三暮四建一堆相关前缀索引，索引不是越多越好，DBA 看到一张表挂着二三十个索引才是最痛苦的。

5. 反向存储、全文索引、ES：三种检索需求的选型决策

最后给一张选型对照表，纯属我个人在多个项目里沉淀下来的经验，供参考。

有同学可能注意到，MapReduce 里常见的“倒排序索引”，思想和反向存储也是同一路子：数据正向放不好查，就换一个维度重新排列，让查询能用“有序跳过”代替“全局遍历”。倒排索引把文档 ID 按词条重排，反向列把字符串按尾部重排，底层都是空间换时间。

5.1 关于 MySQL 全文索引的一个小提醒

如果只是少量文本字段做包含匹配，MySQL 自带全文索引能凑合，InnoDB 支持中文环境下的 ngram 解析器。但千万别把全文索引当 ES 平替用。数据量过了千万级、并发检索复杂了，MySQL 全文索引在分词质量、相关性排序、并发度上都会露怯。到那一步，该上 ES 就上 ES，反向列解决不了所有问题，全文索引也只是一个阶段方案。

5.2 我的落地建议：生成列优先，灰度验证不能省

基于上面这些经验，我给自己项目的定级规则是这样的：

• 第一优先级：MySQL 5.7 用STORED生成列自动维护反向值；MySQL 8.0 优先函数索引并 EXPLAIN 验证。
• 第二优先级：存量做分批回填，线上灰度前跑一遍<> REVERSE(col)校验脚本。
• 第三优先级：监控慢查询日志和索引使用情况，观察一个月，用实际数据决定是否保留该优化策略。

这也是我想最后反复强调的：反向存储大法不复杂，复杂的是你要分清楚业务到底是“后缀匹配”“前缀匹配”还是“包含匹配”，以及你到底愿不愿意为查询性能付出存储和写入的额外代价。想清楚了再动手，动手了就要一路看 EXPLAIN 看到底。这套方法我用了很多年，没有一次掉链子，希望你也能在这条路上少踩几个坑。