六安市网站建设_网站建设公司_响应式开发_seo优化

关注我们,设为星标,每天7:30不见不散,每日java干货分享

你接到了一个运营需求：“帮我导出所有Gmail 邮箱的用户” 或者 “查一下所有尾号是 1234的手机号”。
直觉写法：

SELECT * FROM users WHERE email LIKE '%@gmail.com'; SELECT * FROM users WHERE phone LIKE '%1234';

结果：
只要数据量上了百万级，这两条 SQL 就会变成“慢查询”。因为%在最左边，B+ 树索引无法从左向右匹配，只能被迫全表扫描 (Full Table Scan)。
解决方案：
既然数据库只擅长“从左往右”查，那我们就把数据**“倒着存”**，强行让它变成前缀匹配！

---

1. 核心原理：把“后缀”变成“前缀”

B+ 树索引是按照字典序（从左到右）排列的。

• • 正序：apple,banana,cherry...

• • 我们要查以e结尾的单词，索引帮不上忙，因为e在单词末尾。

反向存储的思想：
如果我们把字符串翻转存入数据库：

• •elppa(apple)

• •ananab(banana)

• •yrrehc(cherry)

现在，我们要查以e结尾的单词，就变成了查以e开头的反向单词。
即：LIKE 'e%'。这时，索引就生效了！

---

2. 实战演练：MySQL 5.7+ 的优雅实现

在旧版本中，我们需要在应用层（Java/Python）先把字符串反转，再存入一个物理列reverse_email。这很麻烦，还需要维护数据一致性。

MySQL 5.7+ 引入的“虚拟生成列” (Generated Columns)让这一切变得极其优雅。我们不需要修改业务代码插入逻辑，全靠数据库自动完成。

步骤 A：原始表结构

CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100), phone VARCHAR(20) -- 索引加在 email 上也没用，因为我们要查后缀 );

步骤 B：添加虚拟列并建立索引

我们创建一个虚拟列email_reverse，它的值永远等于REVERSE(email)，并给它加索引。

-- 1. 添加虚拟列 (Virtual Column) -- 这一步不占存储空间，只是定义计算逻辑 ALTER TABLE users ADD COLUMN email_reverse VARCHAR(100) GENERATED ALWAYS AS (REVERSE(email)) VIRTUAL; -- 2. 给虚拟列加索引 (索引是占物理空间的) CREATE INDEX idx_email_reverse ON users(email_reverse);

步骤 C：修改查询 SQL

现在，当我们需要查@gmail.com时，我们在 SQL 里把关键词也反转一下。

优化前 (全表扫描 🐢):

SELECT * FROM users WHERE email LIKE '%@gmail.com';

优化后 (走索引 🚀):

-- 搜索词从 '@gmail.com' 变成 'moc.liamg@' SELECT * FROM users WHERE email_reverse LIKE 'moc.liamg@%';

Explain 效果：type从ALL变成了range，key使用了idx_email_reverse。查询速度瞬间从秒级变成毫秒级。

---

3. 四大实战场景

这个技巧专注于解决“左模糊匹配” (Suffix Match)的痛点。

场景一：手机号/身份证号后几位搜索

需求：客服系统，用户来电验证身份，只报出了手机号最后 4 位 “8888”。

• •传统：LIKE '%8888'(慢)

• •优化：存一个reverse_phone虚拟列。

• •查询：reverse_phone LIKE '8888%'(快)

场景二：邮箱域名统计/筛选

需求：找出所有使用公司企业邮箱 (@company.com) 的注册用户。

• •优化：对邮箱进行反向索引。这比使用全文检索 (Full Text Search) 更轻量，且实时性更好。

场景三：文件扩展名检索

需求：网盘系统，用户想筛选所有.jpg或.docx文件。

• •文件名：2024_report.docx

• •反向名：xcod.troper_4202

• •查询：reverse_name LIKE 'xcod.%'

场景四：车牌号限行查询

需求：交通系统，需要找出所有尾号是1或6的车辆进行限行抓拍。

• • 车牌号的尾号在字符串最后，反向存储后，尾号变成了“头号”，查询效率极高。

---

4. 局限性与避坑

虽然“反向存储”很好用，但它不是万能的。

1. 1.只解决“后缀”查询：
   它只能解决LIKE '%abc'。
   如果你需要LIKE '%abc%'(中间包含)，反向存储也无能为力。中间包含的查询只能靠Elasticsearch (ES)或 MySQL 的全文索引 (Full-Text Index)。

2. 2.写操作开销：
   虽然虚拟列VIRTUAL不占数据行空间，但索引是实实在在的。每次插入或更新email，数据库都要计算反转值并更新 B+ 树索引，会稍微增加写操作的负担。

3. 3.SQL 可读性：
   开发人员维护 SQL 时，看到moc.liamg@可能会懵逼。建议在 DAO 层封装好工具类，或者在 SQL 注释里写清楚。

---

5. 总结

当遇到“查屁股”（后缀匹配）的需求时，不要直接上 ES，也不要忍受全表扫描。

MySQL 虚拟列 + REVERSE 函数 + 索引，就是性价比最高的解决方案。它用极小的成本，换来了极致的查询性能。

推荐阅读 点击标题可跳转

50个Java代码示例：全面掌握Lambda表达式与Stream API

16 个 Java 代码“痛点”大改造：“一般写法” VS “高级写法”终极对决，看完代码质量飙升！

为什么高级 Java 开发工程师喜爱用策略模式

精选Java代码片段：覆盖10个常见编程场景的更优写法

提升Java代码可靠性：5个异常处理最佳实践

为什么大佬的代码中几乎看不到 if-else，因为他们都用这个...

还在 Service 里疯狂注入其他 Service？你早就该用 Spring 的事件机制了

看完本文有收获？请转发分享给更多人

关注「java干货」加星标，提升java技能

❤️给个「推荐 」，是最大的支持❤️

.cls-1{fill:#001e36;}.cls-2{fill:#31a8ff;}

.cls-1{fill:#001e36;}.cls-2{fill:#31a8ff;}

.cls-1{fill:#001e36;}.cls-2{fill:#31a8ff;}