分表之后如何进行模糊查询的方案

方案选择图片（来自文章：分库分表下的分页查询：底层逻辑、全场景坑点与生产级最优解-腾讯云开发者社区-腾讯云）：

一、问题背景

电商平台用户表按user_id分库分表（16库×16表），分表后单表数据量从1亿降至40万，写入性能提升显著。但新问题出现：

• 模糊搜索失效：用户管理后台需要根据昵称模糊查询（如%张三%），但数据分散在多个库表
• 分页排序混乱：查询SELECT * FROM user WHERE age > 18 ORDER BY create_time DESC LIMIT 10000, 10，因各分片排序规则独立，结果集错乱

二、为什么分库分表后，分页查询会成为难题

2.1 单库单表分页的底层逻辑

MySQL单库单表的分页查询，标准语法为：

SELECT * FROM t_order ORDER BY id LIMIT offset, size;

其底层执行逻辑为：

1. 扫描满足条件的前offset + size行数据
2. 丢弃前offset行，返回剩余的size行结果

该逻辑在单表中可以保证结果的准确性，因为所有数据都在同一个物理表中，排序是全局有序的。即使是深分页（offset过大），最多也只是单表扫描性能下降，不会出现结果错乱的问题。

2.2 分库分表的核心分片模型

本文仅讨论对分页影响最大的水平分片，主流分片规则分为两类：

分片类型	原理	优点	缺点
哈希分片	按分片键的哈希值取模，如t_order_${order_id % 2}	数据分布均匀，无热点问题	无法预判数据所在分片范围
范围分片	按分片键的数值/时间范围划分，如按月份分片	可精准定位数据，范围查询性能优异	易出现数据热点问题

2.3 分库分表分页的核心矛盾

分库分表后，数据分散在多个独立的物理分片上，每个分片仅存储全局数据的一部分，没有任何一个分片持有全局有序的完整数据集，这就是分页查询的核心矛盾。

示例说明

假设有2个分片表，采用哈希分片，分片键为id，分片规则为id % 2：

• t_order_0（分片0）存储id为偶数的数据：2、4、6、8、10、12
• t_order_1（分片1）存储id为奇数的数据：1、3、5、7、9、11

业务需求：按id升序分页，取第2页，每页2条数据（offset=2，size=2），正确结果应为3、4。

错误做法：直接在每个分片执行单表分页SQL：

SELECT * FROM t_order_0 ORDER BY id LIMIT 2,2;  -- 返回：6、8
SELECT * FROM t_order_1 ORDER BY id LIMIT 2,2;  -- 返回：5、7

合并排序后取前2条得到5、6，与正确结果3、4完全不符！

这就是绝大多数开发者落地分库分表时踩的第一个坑：直接把单表分页逻辑照搬到分库分表中，导致分页结果完全错误。

---

三、技术方案：三种解决分库问题的思路

方案一：异构索引（Elasticsearch与MySQL数据同步）

通过Elasticsearch实现全文检索，MySQL仅作分片存储。

1. 数据同步

• 实时同步：使用Canal监听MySQL binlog，实时写入Elasticsearch
• 批量同步：通过DataX每日全量同步（补偿机制）

2. 索引设计

PUT /user_index
{
  "mappings": {
    "properties": {
      "nickname": { "type": "text", "analyzer": "ik_max_word" },
      "user_id": { "type": "keyword" },
      "age": { "type": "integer" },
      "create_time": { "type": "date" }
    }
  }
}

3. 查询示例

POST /user_index/_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "nickname": "张三" }},
        { "range": { "age": { "gt": 18 }} }
      ]
    }
  },
  "sort": [ { "create_time": "desc" } ],
  "from": 10000,
  "size": 10
}

ES二级索引法（全局索引法）

该方案是解决大数据量、支持跳页、深分页场景的唯一成熟生产级方案。

底层逻辑：

1. 通过Binlog同步组件（Canal/Maxwell）将分库分表中的分页查询关键字段（排序字段、查询条件、主键ID、分片键）实时同步到Elasticsearch
2. 分页查询时，先在ES中执行分页查询，获取符合条件的主键ID列表与分片键
3. 通过主键ID+分片键，精准路由到对应分片，批量查询完整数据
4. 组装数据后返回给前端

优点：

• 支持复杂查询、高并发搜索
• ES天生支持分布式分页，深分页性能优异
• 支持跳页与复杂条件查询

缺点：

• 数据同步延迟（通常1~3秒）
• 维护成本高

---

方案二：中间件方案（ShardingSphere聚合结果）

核心思想：通过ShardingSphere代理层合并分片结果。

整体查询流程

1. 代理层向所有分片发送SELECT * FROM user WHERE age > 18
2. 各分片返回局部排序结果
3. 代理层在内存中全局排序，截取10000~10010条数据

子方案 2.1：全局视野法（二次排序法）

底层逻辑：

要保证分库分表分页结果的全局准确性，必须让每个分片都返回从第0行到offset+size行的全量数据，在应用层/中间件层完成全局排序后，再截取offset到offset+size的目标数据。

示例执行逻辑（offset=2，size=2）：

1. 每个分片执行SELECT * FROM t_order_${0..1} ORDER BY id LIMIT 0, 4;（offset+size=4）
2. 分片0返回：2、4、6、8；分片1返回：1、3、5、7
3. 应用层将两个分片的结果合并，按id全局排序得到：1、2、3、4、5、6、7、8
4. 截取offset=2开始的2条数据，得到正确结果：3、4

ShardingSphere-JDBC已经内置了该方案的完整实现，开发者无需手动编写合并排序逻辑。

子方案 2.2：流式归并排序优化

很多开发者担心全量数据加载到内存会导致OOM，ShardingSphere采用了流式归并排序优化：

• 每个分片的查询结果都是按排序字段有序的
• 采用优先级队列（PriorityQueue）实现多路归并排序，无需将所有数据加载到内存
• 仅需保存每个分片的当前游标
• 跳过前offset条数据时，仅移动游标，不加载全量数据到内存

性能优化建议

• 分页限制：禁止深度分页（如LIMIT 100000, 10），改为基于游标的分页
• 异步预取：首次查询缓存后续分页数据（牺牲实时性换性能）

适用场景

适用	不适用
后台管理系统的分页查询（限制最大翻页数≤100页）	无限制的深分页查询
小数据量、少分片的分库分表场景	C端用户的无限滚动列表
无法使用其他优化方案的兜底场景	-

---

方案三：业务折衷（限制搜索条件）

核心思想：通过业务规则约束查询条件，使查询落在单库内。

实现方案

• 时间范围限定：要求模糊搜索必须带时间条件（如create_time BETWEEN '2023-01-01' AND '2023-12-31'），确保查询路由到同一分片
• 分页规则：仅允许按主键（user_id）排序分页，避免跨库排序

子方案 3.1：游标分页法（滚动分页、禁止跳页法）

这是分库分表场景下，解决深分页问题的生产级最优方案，也是业内C端场景的通用标准方案。

底层逻辑：

摒弃传统的offset行号定位方式，采用基于值的游标定位：

1. 每次分页查询，返回结果的最后一条数据的排序字段值作为下一次查询的游标
2. 下一次查询时，通过WHERE 排序字段 > 上一次的游标值过滤数据，再执行LIMIT size
3. 每个分片仅需扫描符合条件的size条数据，无需扫描前offset行

核心前提：排序字段必须是全局唯一、单调递增/递减的，推荐使用：

• 主键ID
• 创建时间+全局唯一ID

禁止仅用非唯一的创建时间排序。

示例执行逻辑：

1. 第一页查询，size=2：

   SELECT * FROM t_order ORDER BY id ASC LIMIT 2;
   

   返回结果：1、2，最后一条id=2作为下一页游标

2. 第二页查询，游标id=2：

   SELECT * FROM t_order WHERE id > 2 ORDER BY id ASC LIMIT 2;
   

   分片0返回：4、6；分片1返回：3、5

3. 合并排序后得到：3、4、5、6，取前2条，得到正确结果3、4

优势：无论翻到第100页还是第1000页，每个分片的扫描行数始终是size条，性能完全稳定。

子方案 3.2：分片键精准路由法

这是分库分表分页查询的性能天花板方案。

底层逻辑：

若查询条件中携带了完整的分片键，ShardingSphere可直接通过分片算法计算出数据所在的唯一分片，将查询请求精准路由到该分片，后续的分页查询逻辑与单库单表完全一致。

示例：将订单表的分片键改为user_id，分片规则为user_id % 2。当用户查询自己的订单列表时，必然会携带user_id查询条件，ShardingSphere可直接路由到对应分片表，无需全分片扫描。

适用场景	不适用场景
C端用户查询自己的私有数据（个人订单、收藏、账单）	无法携带分片键的全量数据分页查询
所有能携带分片键的分页查询	后台管理系统的全平台订单查询

子方案 3.3：范围分片优化法

该方案是针对范围分片场景的专属优化。

底层逻辑：

若采用范围分片（如按id范围、时间范围分片），数据的分片范围是可预判的，分页查询时可先通过计算定位到offset所在的分片范围，仅需扫描相关分片。

示例：按id范围分为3个分片

• 分片0：id 1-10000
• 分片1：id 10001-20000
• 分片2：id 20001-30000

当需要查询offset=15000，size=10时，可直接定位到分片1和分片2，分片0无需扫描，减少了1/3的数据库压力。

适用场景	不适用场景
按时间范围分片的日志、报表、账单数据	哈希分片场景
数据冷热分离场景（冷数据分片无需扫描）	高并发写入易出现热点的业务
范围查询为主的业务场景	-

---