5000 万数据，如何毫秒级查手机尾号？

最近在准备面试，刷题的时候遇到一个很有意思的问题："有个用户表，5000 万条数据，现在要根据手机号后 4 位进行查询，你会怎么做？"

我第一反应是 WHERE phone_number LIKE '%1234'，然后面试官的笑容就逐渐凝固了。他说："兄弟，这可是 5000 万条数据，不是 5000 条..."

后来我仔细研究了这个问题，发现这道题真的很经典，涉及到索引原理、架构设计、空间时间权衡等多个知识点。今天就来聊聊这个问题的几种解法，以及它们背后的思考逻辑。

为什么 LIKE '%1234' 不行？

咱们先说说为什么这个最直观的写法会被面试官否定。

数据库的 B+树索引，其实就像新华字典的拼音目录。如果你知道一个字的拼音是 "zi"，可以很快通过目录定位到它。但如果我让你找所有拼音以 "i" 结尾的字，你就只能从第一页翻到最后一页了。

LIKE '%1234' 这种前面带通配符的查询，数据库根本不知道从哪开始查，只能老老实实把 5000 万条记录挨个检查一遍。

我当时用 EXPLAIN 分析了一下，type 显示的是 ALL，也就是全表扫描，预计要扫描所有 5000 万行数据。

EXPLAIN SELECT * FROM users WHERE phone_number LIKE '%1234';
-- type: ALL (全表扫描)
-- rows: 50000000 (预计扫描5000万行)

性能表现：

• 查询时间：30 秒 ~ 几分钟
• CPU 占用：100%
• 对并发查询的影响：灾难性

这种查询在我本地跑了 30 多秒才出结果，而且 CPU 直接飙到 100%。如果在生产环境，几个这样的查询并发进来，数据库基本就挂了。

所以面试官说得对，这个方案只适合数据量特别小的表，几万条数据还凑合，但到了百万千万级别就是灾难。

方案一：用空间换时间

既然后缀不好查，那我们就创造一个让它"好查"的条件。最简单的思路就是把手机号的后 4 位单独存一份。

我给表加了一个 phone_suffix 字段，专门存后 4 位。具体做法是先 ALTER TABLE 加字段，然后用 UPDATE 把存量数据都填充进去，最后在这个字段上建个索引。

-- 1. 新增后缀字段
ALTERTABLEusersADDCOLUMN phone_suffix CHAR(4) NOTNULLDEFAULT'';
-- 2. 填充存量数据
UPDATEusersSET phone_suffix = RIGHT(phone_number, 4);
-- 3. 创建索引
CREATEINDEX idx_phone_suffix ONusers(phone_suffix);

这样查询就变成了 WHERE phone_suffix = '1234'，这可是等值查询，数据库最擅长的操作。我再用 EXPLAIN 看了下，type 变成了 ref，预计只需要扫描几千行（因为手机号后 4 位重复的概率大概是万分之一）。

-- 优化后的查询
SELECT * FROM users WHERE phone_suffix = '1234';

-- EXPLAIN 结果
-- type: ref (索引查询)
-- rows: 约5000 (假设后4位碰撞率为1/10000)
-- Extra: Using index condition

实测下来，查询时间直接从 30 秒降到了 5 毫秒，提升了 6000 倍！这就是索引的威力。

但这个方案也有问题。首先是占用了额外的存储空间，虽然每条记录只多了 4 个字节，但 5000 万条数据加起来也有 200MB。更麻烦的是数据一致性问题，每次插入或更新手机号时，都要记得同步更新这个后缀字段。

我在代码里是这么处理的，插入用户时自动计算后缀，更新手机号时一并更新两个字段。如果用事务包起来，能保证原子性。

// 更新手机号时同步更新后缀
@Transactional
public void updatePhone(Long userId, String newPhone) {
    String suffix = newPhone.substring(newPhone.length() - 4);
    userMapper.updatePhoneAndSuffix(userId, newPhone, suffix);
}

这个方案的另一个局限是只能查固定长度的后缀。如果哪天产品经理说要支持查后 5 位、后 6 位，就得再加字段了。

方案二：把手机号倒过来存

为了解决灵活性问题，我想到了一个巧妙的办法：把手机号倒序存储。

比如 13812345678 存进去的时候，我再存一个反转后的版本 87654321831。然后在这个反转字段上建索引。

-- 1. 新增反转字段
ALTERTABLEusersADDCOLUMN reversed_phone CHAR(11) NOTNULLDEFAULT'';
-- 2. 填充数据
UPDATEusersSET reversed_phone = REVERSE(phone_number);
-- 3. 创建索引
CREATEINDEX idx_reversed_phone ONusers(reversed_phone);

查询的时候，我也把要查的后缀反转一下。原本是 LIKE '%5678'，现在变成 WHERE reversed_phone LIKE '8765%'。你看，后缀匹配瞬间变成了前缀匹配，数据库又可以愉快地用索引了。

-- 原需求：查手机号后4位为 '5678'
-- 优化查询：
SELECT * FROM users
WHERE reversed_phone LIKE '8765%';

-- 查后5位为 '45678'：
WHERE reversed_phone LIKE '87654%';

-- 查后3位为 '678'：
WHERE reversed_phone LIKE '876%';

这个方案比方案一灵活多了。想查后 4 位就用 LIKE '4321%'，想查后 5 位就用 LIKE '54321%'，非常方便。当然代价是存储开销更大了，每条记录多了 11 个字节。

性能方面，前缀匹配虽然没有等值查询那么快，但也能达到 10 毫秒以内，完全够用。

方案三：用数据库的新特性

如果你用的是 MySQL 8.0 或者 PostgreSQL，其实还有个更优雅的办法：函数索引。

函数索引可以直接在某个表达式上建索引，不需要新增字段。比如我可以这样：

-- 方式一：基于 REVERSE 函数
CREATE INDEX idx_phone_reverse_func ON users((REVERSE(phone_number)));

然后查询时用相同的表达式：

 --查询（必须使用相同表达式）
SELECT * FROM users WHERE REVERSE(phone_number) LIKE '8765%';

数据库会自动识别出这个表达式匹配索引定义，直接使用函数索引。整个过程应用层完全无感知，表结构也保持干净。

但这个方案有两个坑。第一是数据库版本要求，MySQL 5.7 就不支持。第二是 SQL 写法必须严格匹配索引表达式，稍微改一下就可能用不上索引。比如你写成 SUBSTRING(REVERSE(phone_number), 1, 4) = '8765'，虽然逻辑一样，但索引就失效了。

所以用这个方案的话，最好在团队里约定好查询语句的标准写法，避免踩坑。

方案四：引入搜索引擎

前面几个方案解决的都是单一查询场景。但如果查询需求变复杂了呢？比如产品经理说，我要同时根据手机尾号、姓名、地址进行模糊搜索，还要支持拼音搜索、多字段组合查询。

这时候关系型数据库就力不从心了，该轮到 Elasticsearch 登场了。

ES 的核心是倒排索引。简单说，它会把每个词条和包含这个词条的文档 ID 建立映射关系。查询 "1234" 时，ES 能瞬间从倒排索引中找到所有包含它的用户。

不过引入 ES 意味着你要多维护一套存储系统。数据怎么从 MySQL 同步到 ES？常见的做法是用 Canal 监听 MySQL 的 binlog，然后实时同步到 ES。这样查询走 ES，写入走 MySQL，各司其职。

应用层 ──写──> MySQL (主存储)
  │              │
  │              │ binlog
读(ES) <─同步─ Canal

ES Mapping 设计

{
  "mappings": {
    "properties": {
      "phone_number": {
        "type": "keyword"
      },
      "phone_suffix": {
        "type": "keyword"// 后缀单独分词
      },
      "name": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      }
    }
  }
}

查询的示例：

// 单纯查手机尾号
{
"query": {
    "term": {
      "phone_suffix": "1234"
    }
  }
}

// 组合查询：姓名包含"张三" + 手机尾号1234
{
"query": {
    "bool": {
      "must": [
        { "match": { "name": "张三" }},
        { "term": { "phone_suffix": "1234" }}
      ]
    }
  }
}

但这套架构的复杂度可不是开玩笑的。你要考虑数据同步的延迟（一般 1-2 秒），要处理同步失败的情况，还要维护 ES 集群的稳定性。所以我建议，只有在查询场景特别复杂、数据量特别大的时候，才考虑引入 ES。

单纯为了查个手机尾号就上 ES，有点杀鸡用牛刀的意思。

到底该选哪个方案？

说了这么多方案，那实际工作中应该怎么选呢？我总结了一个简单的思路。

数据量级	查询场景	推荐方案	说明
< 100 万	模糊查询（LIKE '%...'）	直接 LIKE + 分页	数据量小，性能影响可控，配合分页即可满足需求。
100 万 ~ 5000 万	查询固定长度后缀（如后 4 位）	方案二：冗余字段法（最优）	新增冗余字段存储后缀，查询时直接匹配，性能最佳。
	灵活查询不同长度后缀	方案三：字段反转法	将字段反转后建索引，支持灵活后缀匹配，避免全表扫描。
	追求表结构简洁 + MySQL 8.0+	方案四：函数索引法	利用 MySQL 8.0 的函数索引功能，简洁但需规范 SQL 写法。
> 5000 万	单一查询场景（如固定后缀）	分库分表 + 方案二	按尾号哈希分片，每片内使用冗余字段法，提升并发与查询效率。
	复杂搜索场景（多条件、模糊）	方案五：MySQL + ES	使用 Elasticsearch 处理复杂搜索，MySQL 负责精确查询，分工明确。
特殊场景	实时性要求极高	Redis 位图 / 布隆过滤器	用内存数据结构预过滤，减少数据库压力，提升响应速度。
	数据更新频繁	避免使用 ES	ES 同步成本高，适合读多写少场景，频繁更新建议用 MySQL + 冗余字段等方案。
	数据更新频繁	避免使用 ES	ES 同步成本高，适合读多写少场景，频繁更新建议用 MySQL + 冗余字段等方案。
还有个容易被忽略的点是监控。优化完之后，一定要观察慢查询日志、索引命中率、响应时间的 P95 和 P99 值，确保优化真的有效。我之前就遇到过一次，以为优化完了，结果发现 P99 还是很高，最后发现是有些边界条件没考虑到。

总结

这道题看起来简单，但其实考察的知识点很多。从索引原理到空间时间权衡，从单机优化到分布式架构，每一层都能聊得很深。

面试的时候，如果你能从 LIKE 的问题出发，一步步推导出冗余字段、字段反转、函数索引的方案，再提到数据一致性的保障、分库分表的可能性，最后聊到 ES 的适用场景和架构设计，面试官肯定会对你刮目相看。

当然，最重要的是理解每个方案背后的思考逻辑和权衡取舍。没有银弹，只有最适合当前场景的方案。技术选型不是单纯比较性能高低，而是要综合考虑实现成本、维护成本、团队能力、业务发展阶段等多个因素。