企业官网建设流程全解析

先交代一下背景。我之前接手过一个线上订单查询系统，业务方提了一个需求：按订单号的最后 6 位模糊查询订单。当时第一版 SQL 写出来大概是这样的：

SELECT * FROM orders WHERE order_no LIKE '%123456';

表面看没啥问题，数据量小的时候也确实很快。等表里数据量跑到 500 万行以后，这条 SQL 直接变成了数据库里的头号慢查询，高峰期能把 CPU 打到 90% 以上，最后只能靠限流保命。我当时的第一反应和大家一样：加索引呗。结果加完普通索引一看 EXPLAIN，type 还是 ALL，索引根本没生效。

后来才搞明白，LIKE 左侧带 % 的模糊查询，B+ 树索引天生就无能为力。直到我试了“反向存储大法”，把这条 SQL 的响应时间从 2 秒多降到了 20 毫秒以内，说效率提升 100 倍一点都不夸张。这篇文章就把这套方案的原理、实操步骤和踩过的坑完整复盘一遍。

1. 为什么 % 在左边，索引就“罢工”了

1.1 B+ 树索引本质上是个“前缀匹配器”

要理解反向存储为什么有效，先得搞清楚一件事：MySQL 的 InnoDB 索引底层是 B+ 树，数据按索引列的值从小到大排序，叶子节点串成了有序链表。这种数据结构决定了它的检索方式是“从根节点开始，按值逐层二分查找”，它最擅长的事就是“从前往后的精确或范围匹配”。

举个例子，WHERE order_no LIKE '2023%'是前缀匹配。优化器可以从索引树上定位到第一个以2023开头的键值，然后沿着叶子链表一直往后扫，直到不满足前缀条件为止。这是一个标准的范围扫描，type=range，性能非常稳定。

但WHERE order_no LIKE '%123456'就完全不一样了。%在最前面，意味着目标字符串可能从任意位置开始，优化器根本不知道应该从索引树的哪个节点开始查。它唯一的办法就是：把整个表所有行的 order_no 全部取出来，从第一个字符到最后一个字符逐位比对，也就是全表扫描。

打个比方，B+ 树索引像一本按姓氏拼音排序的电话簿。你想找“所有姓张的人”，翻到 Z 开头那一块就行，非常快。但如果你想找“名字里最后一个字是‘伟’的人”，这本按姓氏排序的电话簿就帮不上忙了，你只能一页一页从头翻到尾。

1.2 一次全表扫描的成本到底有多高

很多人觉得全表扫描也没啥，不就多花点时间吗。但真实生产环境里，这个“时间”是会被放大成事故的。

假设一张表有 500 万行，每行数据加索引页算下来平均 300 字节，全表数据量大概 1.5GB 左右。如果 buffer pool 足够大，数据全在内存里，那一次顺序扫描可能也就一两秒。但问题是，生产环境的大表往往远超 buffer pool 容量，或者查询条件复杂导致扫描过程中要随机回表，这就变成了大量磁盘随机 IO。我见过最夸张的情况，一条LIKE '%关键字'在千万级表上跑了 8 秒，直接把连接池打满。

还有一个容易被忽略的问题：全表扫描不仅仅是慢，它还会产生大量的行锁和一致性读开销。在 RR 隔离级别下，长查询会让 undo log 的清理滞后，进一步拖垮整个实例。所以这类 SQL 不是“忍忍就过去”的问题，而是必须处理掉。

1.3 中间匹配能不能靠普通索引救？

有人会问，那我建一个覆盖索引，把所有字段都塞进去，是不是就不回表了？能缓解，但解决不了根本问题。覆盖索引只是省去了回表这一步，但优化器依然要遍历整棵索引树的所有叶子节点，做字符串匹配。索引树再小，也是几百万个叶子节点，全扫一遍消耗依然巨大。

还有的人尝试把%abc拆成LIKE 'abc%' OR LIKE '%abc'，再 UNION 一下。这个方案能解决一部分需求变体，但%abc这个分支本身照样是全表扫描，治标不治本。

所以问题的关键不是“索引覆盖了哪些列”，而是“查询模式符不符合 B+ 树的字典序规则”。后缀匹配天然违背这个规则，必须从数据存储形态上想办法，这就是反向存储的切入点。

2. 反向存储大法：把后缀变成前缀

2.1 方案原理与一个粗浅类比

“反向存储大法”的英文做法其实叫 Reverse Index，思路非常简单：建表的时候，额外增加一列，专门存放原字段的反转字符串。比如订单号ABC123456，反转后就是654321CBA。而查询条件LIKE '%123456'，经过反转就变成了LIKE '654321%'。

左模糊瞬间变成了右模糊，后缀匹配瞬间变成了前缀匹配。前缀匹配是 B+ 树的舒适区，索引就能正常生效了。

还是用电话簿来类比。普通索引是按名字拼音正向排序的电话簿，找“名字以 X 结尾”的人很难；但如果另外再整理一本“把每个名字倒过来排”的电话簿，比如“伟张”排到 W 区，“建国李”排到 G 区，那你想找“名字最后一个字是伟”的人，直接在倒序电话簿里查“伟”开头的区域就行，又快又准。

第二种做法：如果业务场景里其实要查的是“后缀等于某个固定值”而不是模糊匹配，那反转列还能支持等值查询。比如查所有@qq.com结尾的邮箱，反转列直接等于REVERSE('@qq.com')，走的是type=ref，比 range 还快。

2.2 能提升多少：从 O(n) 到 O(log n)

这个方案的本质，是把时间复杂度从 O(n) 降到了 O(log n + m)，其中 n 是全表行数，m 是最终命中的结果集行数。

• 改造前的全表扫描：每次查询都要扫描 n 行，n 等于 500 万，就是 500 万次字符串比较。
• 改造后的索引范围扫描：在 B+ 树上二分查找定位起始位置，大约log2(500万)≈23次比较，然后沿着链表顺序读取 m 行，m 通常是个位数到几十。

注意，最终命中的结果集大小 m 必须远小于全表行数，这个方案收益才大。反过来讲，如果一条LIKE '%abc'能匹配全表 50% 的行，那就算走了索引，回表代价也很高，优化器可能依然选择全表扫描。所以这个方案适合的场景是：查询选择性好，结果集小，但原表数据量巨大。

2.3 适用场景判断

不是所有LIKE '%abc'都适合用反向存储，动手之前先对照一下：

• 适合：快递单号后几位查询、邮箱后缀查询、手机号后几位查询、用户名末尾关键字搜索、URL 路径末段匹配，这些场景后缀信息有明确的业务含义，而且选择性通常不错。
• 不适合：LIKE '%abc%'这种中间模糊匹配，反转之后还是%cba%，照样全表扫描。
• 不适合：后缀本身没什么区分度，比如查LIKE '%ing'，一匹配就是几十万行，走索引还不如全扫。

还有一点要想清楚：这个方案本质上是空间换时间，多一列反转数据就意味着额外存储和写入开销，只适合读多写少的业务。如果一张表每秒写入上千次，还非得加反转列，可能需要重新评估成本。

3. 实操：从建表到查询改写的完整改造

3.1 用生成列自动维护反转数据

反向存储最怕的一件事就是数据不一致。你在应用层往 A 列写原值，再往 B 列写反转值，万一哪天代码漏了、事务没提交完整、或者批量数据导入的时候忘了反转，整张表的索引就废了。

所以在 MySQL 里我强烈建议用生成列（Generated Column），让数据库自己负责反转，应用层插入数据的时候根本感知不到这一列的存在。从 MySQL 5.7 开始就支持这个功能。

新表的话直接这样建：

CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(255) NOT NULL, email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, PRIMARY KEY (id), KEY idx_email_reverse (email_reverse) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

老表加列也很简单：

ALTER TABLE users ADD COLUMN email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, ADD INDEX idx_email_reverse (email_reverse);

这里用了STORED关键词，意思是反转后的值真实存储在磁盘上。MySQL 也支持VIRTUAL生成列，不占行内存储空间，但查询时往往需要额外计算，优化器对 VIRTUAL 列索引的利用在一些复杂 SQL 里会表现得比较保守。我的经验是：既然目的是建索引加速查询，就用 STORED，多占点磁盘，换查询稳定性。

注意一个细节：索引建好后，插入数据时不需要也不应该往email_reverse里显式写值。一旦写了，MySQL 会报错。生成列的值完全由表达式REVERSE(email)决定，数据一致性从底层就保证了。

3.2 查询改写与参数绑定

改造后的业务查询，就是把原来LIKE '%原串'改成LIKE REVERSE('原串') || '%'。

以“查所有 @qq.com 结尾的邮箱”为例，原始 SQL：

SELECT id, email FROM users WHERE email LIKE '%@qq.com';

改写后：

SELECT id, email FROM users WHERE email_reverse LIKE REVERSE('@qq.com') || '%';

拆解一下：REVERSE('@qq.com')的结果是moc.qq@，而abc@qq.com这个邮箱反转后是moc.qq@cba，确实以moc.qq@开头。匹配逻辑完全等价。

如果业务语义是“邮箱后缀等于 @qq.com”，那更简单，直接转等值匹配：

SELECT id, email FROM users WHERE email_reverse = REVERSE('@qq.com');

等值匹配走的是type=ref，用普通索引就行，性能比范围查询还要好。

如果用的是 MyBatis 这类 ORM，注意参数不要拼字符串，用占位符传参。尤其要提醒同行朋友：反转这个操作最好在代码里做，不要把REVERSE()函数写在 SQL 的 ON 条件里，否则索引列被函数一包，又会演变成全表扫描。

正确的 Java 侧写法是：

String keyword = "@qq.com"; String reverseKeyword = new StringBuilder(keyword).reverse().toString(); List<User> users = userMapper.queryByEmailReverse(reverseKeyword + "%");

MyBatis 映射文件里对应的 SQL：

<select id="queryByEmailReverse" resultType="User"> SELECT id, email FROM users WHERE email_reverse LIKE CONCAT(#{keyword}, '%') </select>

别小看这个 CONCAT 和占位符的配合，很多人习惯在 XML 里写LIKE '%${keyword}%'，一旦 keyword 是用户传入的，这叫 SQL 注入，生产环境一定要避开。

3.3 EXPLAIN 验证：改造前后的差距

改造完以后，不要拍脑袋说“快了”，用 EXPLAIN 把前后对比摆出来。

改造前：

EXPLAIN SELECT id, email FROM users WHERE email LIKE '%@qq.com'\G

输出大致是：

id: 1 select_type: SIMPLE table: users type: ALL possible_keys: NULL key: NULL rows: 1000000 filtered: 5.00 Extra: Using where

关键信息就是type: ALL和rows: 1000000，这是一条典型的全表扫描 SQL。

改造后：

EXPLAIN SELECT id, email FROM users WHERE email_reverse LIKE 'moc.qq@%'\G

输出大致是：

id: 1 select_type: SIMPLE table: users type: range possible_keys: idx_email_reverse key: idx_email_reverse rows: 120 filtered: 100.00 Extra: Using index condition

type从 ALL 变成 range，扫描行数从 100 万变成 120 行，这就是量级上的差距。在真实压测里，这条查询的 P99 从 1800ms 将到了 18ms，100 倍没有水分。

3.4 存量数据如何平滑迁移

如果是新表，直接走 DDL 就行了。但生产环境大概率是已有上千万行数据的存量表，这时加生成列和索引必须考虑在线变更。

一个常见误区是直接在生产库执行ALTER TABLE ... ADD COLUMN ...。MySQL 8.0 对ADD COLUMN支持ALGORITHM=INSTANT的场景非常有限，加了STORED GENERATED COLUMN之后，InnoDB 通常需要重建表，这在千万级表上可能意味着几分钟甚至十几分钟的业务不可用，还会产生主从延迟。

我建议大表场景用pt-osc或gh-ost做在线表结构变更：

pt-online-schema-change \ --alter "ADD COLUMN email_reverse VARCHAR(255) GENERATED ALWAYS AS (REVERSE(email)) STORED, ADD INDEX idx_email_reverse(email_reverse)" \ D=test,t=users \ --execute

如果公司的数据库运维平台内置了无锁 DDL 能力，直接用平台的变更工单，会比自己在客户端敲 DDL 安全得多。

还有一个小技巧：如果业务上你只需要固定长度的后缀，比如只查“订单号最后 6 位”，反转列可以只存REVERSE(RIGHT(order_no, 6))。这样生成列的长度短，索引页能装下更多键值，IO 更少，还能避免反转全字段带来的存储膨胀。

4. 进阶：多维优化与替代方案

4.1 反转列+其他条件的联合索引设计

实际业务里的查询很少只有单字段条件，更多是组合查询。比如“按邮箱后缀 + 用户状态 + 创建时间”筛选用户。反转列建好以后，不要单独只建一个索引，可以结合其他高频查询条件建联合索引：

ALTER TABLE users ADD INDEX idx_rev_status_time (email_reverse, status, created_at);

联合索引设计的时候要遵循最左前缀原则：email_reverse放在最左边，因为它是等值或范围模糊匹配，之后可以跟着status等值条件，再往后是排序字段created_at。这样查询走索引的同时，排序也能由索引直接提供，避免 filesort。

反过来要注意，别一上来就把五六列全塞进联合索引。索引列越多，写入越慢，索引页占用越大。一般情况下列数控制在 3 列以内比较稳妥。

4.2 MySQL 8.0 函数索引能不能替代生成列？

MySQL 8.0.13 开始支持函数索引，写法是：

ALTER TABLE users ADD INDEX idx_email_reverse ((REVERSE(email)));

从功能上看，函数索引确实可以达到和“生成列+索引”类似的效果。但有一个很关键的差异：函数索引能否被用到，完全取决于优化器能不能把你的 SQL 自动改写为表达式匹配。

如果你写WHERE email LIKE '%@qq.com'，MySQL 优化器并不会自动把它解读成REVERSE(email) LIKE 'moc.qq@%'，它没有这个智商。你依然要手动改写 SQL，改成WHERE REVERSE(email) LIKE 'moc.qq@%'，这时候函数索引才能生效。

所以函数索引和生成列方案，在实际使用体验上差距不大，都需要应用层改写查询。但生成列方案更直观，EXPLAIN 里能看到明确的列名和索引名，排查问题的时候心智负担更小。函数索引的优势是省掉一列存储，适合不太想动表结构的场景。

4.3 固定后缀查询直接转等值匹配

我在项目里发现很多需求嘴上说的是“模糊查询”，实际业务含义是“后缀等于某一类固定值”。比如：

• 查所有 VIP 用户的手机号段：WHERE phone LIKE '%8888'
• 其实业务方真正想要的是“尾号等于 8888 的那批用户”

这种情况下，用反转列做等值查询是最优解：

SELECT id, name FROM users WHERE phone_reverse = REVERSE('8888');

等值查询走的是type=ref，比 range 还要稳定，而且优化器对等值查询的成本估算更准，误判走全表的概率更小。所以以后接到类似需求，先追问一句：“这个后缀是固定的还是任意的？”如果是固定的，能转等值就转等值，能省很多事。

4.4 反向存储都搞不定的场景怎么办

如果查询场景是LIKE '%keyword%'，即关键字可能出现在字符串任意位置，反转字段也没用，因为反转之后还是%drowyek%。这种情况下可以考虑：

• MySQL 全文索引：适合英文或分词后的文本，用 ngram 分词器可以支持中文，但维护成本、精确度都不如专业搜索引擎。
• 外部搜索引擎组件：比如 ES，把需要检索的字段做倒排索引，模糊查询的吞吐量会高很多。
• 数仓方案：如果检索的数据是分析场景而不是在线交易场景，可以同步到 ClickHouse 这类列式存储，用LIKE扫描性能也远比 MySQL 好。

一句话总结：普通索引管前缀，反转列管后缀，中间模糊上搜索引擎，别指望一个方案通吃所有场景。

5. 常见坑与排查实录

5.1 REVERSE 之后的排序不是原来的字典序

反转列只能用来加速匹配和过滤，千万不要在反转列上做 ORDER BY。比如你想查尾号 123456 的订单，并按订单号从大到小排序，写了：

SELECT * FROM orders WHERE order_no_reverse LIKE '654321%' ORDER BY order_no_reverse DESC;

这个结果看起来像是有序的，实际上反转串的字典序和原串字典序没有任何直接对应关系。原串的ABC999反转后是999CBA，原串的ABD000反转后是000DBA，反转后000DBA排前面，但原串ABD000其实是更大的那个，排序逻辑全乱了。

正确做法是：先用反转列把结果集缩小，再回表按原列排序，或者把排序字段一并加入联合索引末端。

5.2 多字节字符反转的意外

MySQL 的REVERSE()函数是按字符反转的，不是按字节反转，所以对中文、日文这些多字节字符基本安全。比如REVERSE('你好世界')会得到界世好你，不会出现半个汉字乱码。

但这里有一个非常隐蔽的坑：emoji 和组合字符。比如家庭组合 emoji👨‍👩‍👧在底层由多个码点组成，REVERSE()会把码点序列反过来，视觉上可能变成一个拼错的奇怪字符系列。如果你的业务字段里包含这类字符，反转列存的“反转值”未必符合人的直观理解，匹配业务规则时会出现偏差。

另外一个更常见的坑是大小写。utf8mb4_unicode_ci排序规则下，索引匹配不区分大小写，REVERSE('ABC')和REVERSE('abc')在索引上会被视为同一个值，本身没问题。但如果业务对大小写敏感，还是要注意排序规则和索引定义的匹配。

5.3 优化器不走索引怎么办

有些情况下，反转列和索引都建好了，EXPLAIN一看还是type=ALL。常见原因有：

• 统计信息不准确。生成列刚建立、索引刚添加的时候，MySQL 的统计信息可能滞后，先执行一遍ANALYZE TABLE users;刷新一下。
• 查询选择性太差。优化器估算出匹配结果超过全表的 20% 时，会认为走索引回表反而更慢，主动放弃索引。这种情况不是优化器笨，是业务查询确实不适合这个方案。
• 隐式类型转换。反转列是 VARCHAR，查询参数是数值或字符集不一致，导致索引列上发生隐式转换，索引失效。

排查这类问题，我的习惯是先跑一遍EXPLAIN FORMAT=JSON，看cost和rows的估算值，再用FORCE INDEX做一次对照试验。如果FORCE INDEX之后性能明显更好，就去排查统计信息；如果差不多，说明这个查询本身就不该硬走索引。

5.4 大表加列还是得走在线 DDL

前面提过ALTER TABLE对生成列的兼容性问题，这里再重点强调一遍。不要因为本地测试小表加列快，就以为线上千万级表也能瞬间完成。任何涉及STORED GENERATED COLUMN的 DDL，都必须先做预案：

• 观察表大小和当前主从延迟；
• 优先使用在线变更工具；
• 变更窗口选择低峰期；
• 变更完成后，立刻验证EXPLAIN和索引统计信息。

我在一个 2000 万行的日志表上直接跑ALTER TABLE加反转列，结果重建表花了 17 分钟，主从延迟飙到 200 多秒，还好是低峰期，没有酿成事故。从那以后，凡是上千万行的表，我宁可多花十分钟写 gh-ost 工单，也绝不手敲 ALTER。

5.5 缓存与批量导入的回填问题

生成列可以保证新数据自动计算，但如果是用LOAD DATA、INSERT ... SELECT批量导数据，要注意 MySQL 会不会为生成列重新计算。实测下来，这些批量操作都会自动触发生成列表达式求值，不用额外回填。

但如果之前用过应用层双写方案（业务代码里自己写反转列），历史数据里大量是错的，切到生成列方案之后，需要先写脚本校验。校验逻辑很简单：按主键分批扫，比对email_reverse和REVERSE(email)是否一致，不一致的直接UPDATE覆盖。

举一个实际踩过的坑：有一回我们用 DataX 从数仓同步数据到 MySQL，目标表有生成列，源表没有反转字段。DataX 的任务配置里如果显式列举了列名，却漏掉了反转列，插入的时候 MySQL 会因为“生成列不能显式插入”而报错。解决方法是任务配置里只写原字段，让生成列自算。

5.6 唯一约束与反转列

如果原字段本身有唯一性要求，比如邮箱不能重复，反转列同样可以建唯一索引：

ALTER TABLE users ADD UNIQUE KEY uk_email_reverse (email_reverse);

反函数的唯一性完全等价的：如果一个字段值唯一，那么它的反转值也唯一。这个唯一索引还能顺手成为等值查询的索引，一箭双雕。但注意，联合唯一索引要慎重设计，别让业务无关的列进唯一键。

最后再分享一点个人体会。反向存储这套方案看起来不复杂，但真正落地的时候，至少要在三件事上有清晰认知：一是查询模式到底是什么，前缀、后缀还是包含匹配，决定了能不能用这招；二是数据一致性由谁保证，生成列是首选，应用层双写只能是过渡；三是性能验证必须量化，EXPLAIN 的 type 和 rows 比“感觉快了不少”可靠得多。

我后来还遇到过需求方想用反转列同时支持“中间某段字符串匹配”，试了一圈发现根本不现实。每个方案都有边界，搞清楚边界在哪，比急着动手更重要。如果你现在也被LIKE '%abc'这种慢查询折磨，不妨先翻翻表结构，看看能不能加一列反转值。别看这个思路简单，生产环境里能救不少人的命。