分库分表后的跨库查询难题破解 🧩

一、开篇故事：分散的图书馆 📚

想象你管理着4个图书馆，书籍分散存储：

困境1：跨馆找书（跨库JOIN）

读者："我要所有张三写的书及其出版社信息。"

问题：
  - 作者信息在1号馆
  - 出版社信息在2号馆
  - 书籍信息在3号馆

传统方案：
  → 去1号馆查张三的ID
  → 去3号馆查张三的书
  → 去2号馆查出版社
  → 手动拼接数据 😰

困境2：跨馆排序（分页问题）

读者："给我价格最贵的前10本书。"

问题：
  - 1号馆最贵：100元
  - 2号馆最贵：200元
  - 3号馆最贵：150元
  - 4号馆最贵：180元

错误方案：
  → 每馆取最贵的10本
  → 合并40本，再排序取前10 ❌
  
正确方案：
  → 每馆取最贵的10本
  → 合并后排序
  → 但如果要第100-110名呢？
  → 每馆要取110本！😱

这就是分库分表后的跨库查询难题！

---

二、问题1：跨分片JOIN 🔗

2.1 问题描述

-- 单库时（没问题）
SELECT u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = '北京';

-- 分库后（无法执行！）
-- users表在db1
-- orders表在db2
-- ❌ 跨库JOIN不支持！

---

2.2 解决方案1：应用层JOIN ⭐⭐⭐⭐

原理： 分别查询，应用层关联

@Service
public class UserOrderService {
    
    public List<UserOrderVO> getUserOrders(String city) {
        // 步骤1：查询用户
        List<User> users = userService.findByCity(city);
        // 例如：查到100个北京用户
        
        // 步骤2：提取用户ID
        List<Long> userIds = users.stream()
            .map(User::getId)
            .collect(Collectors.toList());
        
        // 步骤3：查询订单（按用户ID）
        List<Order> orders = orderService.findByUserIds(userIds);
        
        // 步骤4：应用层JOIN（关联数据）
        Map<Long, User> userMap = users.stream()
            .collect(Collectors.toMap(User::getId, u -> u));
        
        return orders.stream()
            .map(order -> {
                User user = userMap.get(order.getUserId());
                return new UserOrderVO(user.getName(), 
                                       order.getOrderNo(), 
                                       order.getAmount());
            })
            .collect(Collectors.toList());
    }
}

优点：

✅ 灵活，任何JOIN都能实现
✅ 可以优化查询（批量查询）
✅ 可以使用缓存

缺点：

❌ 代码复杂
❌ 需要两次查询
❌ 内存占用大（大数据集）

---

2.3 解决方案2：数据冗余 ⭐⭐⭐⭐⭐

原理： 在表中冗余关联字段

-- 原始设计（需要JOIN）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2)
);

-- 优化设计（冗余字段，不需要JOIN）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    user_name VARCHAR(50),      -- 冗余
    user_phone VARCHAR(20),     -- 冗余
    user_city VARCHAR(50),      -- 冗余
    amount DECIMAL(10,2)
);

-- 查询时不需要JOIN
SELECT user_name, order_no, amount
FROM orders
WHERE user_city = '北京';  -- 直接查，不需要JOIN ✅

数据同步：

// 用户信息变更时，同步更新订单表
@Service
public class UserService {
    
    @Transactional
    public void updateUser(User user) {
        // 1. 更新用户表
        userMapper.updateById(user);
        
        // 2. 同步更新订单表的冗余字段
        orderMapper.updateUserInfo(
            user.getId(), 
            user.getName(), 
            user.getPhone(),
            user.getCity()
        );
    }
}

优点：

✅ 查询性能最好（无需JOIN）
✅ 实现简单
✅ 适合读多写少场景

缺点：

❌ 数据冗余，占用空间
❌ 更新时需要同步多张表
❌ 可能出现数据不一致

---

2.4 解决方案3：全局表（字典表） ⭐⭐⭐

原理： 小表在每个库都复制一份

-- 字典表：省市区、商品分类等
CREATE TABLE regions (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

-- 每个数据库都有一份完整的regions表
db1.regions
db2.regions
db3.regions
db4.regions

-- 这样JOIN时，每个库都有字典表
SELECT p.name, r.name as region_name
FROM products p
JOIN regions r ON p.region_id = r.id;  -- ✅ 可以JOIN

ShardingSphere配置：

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          regions:
            actual-data-nodes: db${0..3}.regions
        broadcast-tables:
          - regions  # 配置为广播表

优点：

✅ 支持JOIN
✅ 数据一致
✅ 适合字典表、配置表

缺点：

❌ 只适合小表（< 10万行）
❌ 更新时需要更新所有库
❌ 占用空间

---

2.5 解决方案4：ER分片（父子表） ⭐⭐⭐

原理： 父子表存储在同一分片

// 用户和订单按user_id分片，存储在同一库
// user_id=100 → db0
//   users表：id=100的用户
//   orders表：user_id=100的所有订单

// 这样JOIN时在同一库，可以执行
SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 100;  -- ✅ 在同一个库，可以JOIN

ShardingSphere配置：

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          users:
            actual-data-nodes: db${0..3}.users
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: user-mod
          
          orders:
            actual-data-nodes: db${0..3}.orders
            database-strategy:
              standard:
                sharding-column: user_id  # 按user_id分片
                sharding-algorithm-name: user-mod  # 和users用同一个算法
        
        binding-tables:
          - users,orders  # 绑定表

优点：

✅ 支持父子表JOIN
✅ 查询性能好

缺点：

❌ 只适合一对多关系
❌ 多对多无法使用
❌ 限制较多

---

2.6 解决方案5：使用ES等搜索引擎 ⭐⭐⭐⭐

原理： 复杂查询交给ES

// MySQL：存储明细数据
// ES：存储查询数据（可以跨表聚合）

@Service
public class ProductSearchService {
    
    @Autowired
    private ElasticsearchClient esClient;
    
    // 复杂查询用ES
    public List<ProductVO> search(SearchDTO dto) {
        // ES中已经聚合了商品、用户、分类等信息
        SearchResponse<ProductDoc> response = esClient.search(s -> s
            .index("products")
            .query(q -> q
                .bool(b -> b
                    .must(m -> m.match(mt -> mt.field("userName").query(dto.getUserName())))
                    .must(m -> m.range(r -> r.field("price").gte(dto.getMinPrice())))
                )
            ),
            ProductDoc.class
        );
        
        return response.hits().hits().stream()
            .map(hit -> convert(hit.source()))
            .collect(Collectors.toList());
    }
}

数据同步：

// Canal监听MySQL binlog，实时同步到ES
@Component
public class CanalListener {
    
    @CanalEventListener
    public void onEvent(CanalEntry.Entry entry) {
        if (entry.getEntryType() == EntryType.ROWDATA) {
            // 解析binlog
            RowChange rowChange = RowChange.parseFrom(entry.getStoreValue());
            
            // 同步到ES
            for (RowData rowData : rowChange.getRowDatasList()) {
                if (rowChange.getEventType() == EventType.INSERT) {
                    esService.index(rowData);
                } else if (rowChange.getEventType() == EventType.UPDATE) {
                    esService.update(rowData);
                }
            }
        }
    }
}

优点：

✅ 支持复杂查询
✅ 支持全文搜索
✅ 性能好
✅ 功能强大

缺点：

❌ 引入新组件，复杂度高
❌ 数据同步延迟
❌ 运维成本高

---

三、问题2：跨分片分页 📄

3.1 问题描述

-- 单库时
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100, 10;

-- 分库后（4个库）
-- 需要查询：
-- db1: LIMIT 110
-- db2: LIMIT 110
-- db3: LIMIT 110
-- db4: LIMIT 110
-- 合并后排序，取100-110

问题：
  - 查了440条，只用10条
  - 深分页时更糟：LIMIT 10000, 10
  - 需要查4万条，只用10条！💀

---

3.2 解决方案1：禁止跳页查询 ⭐⭐⭐⭐⭐

原理： 只允许"下一页"，不允许"跳到第N页"

// ❌ 不允许
SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

// ✅ 允许（记录上次最大ID）
SELECT * FROM orders WHERE id > #{lastMaxId} ORDER BY id LIMIT 10;

@Service
public class OrderService {
    
    public PageResult<Order> getNextPage(Long lastMaxId, int pageSize) {
        // 每个分片查询
        List<Order> db1Orders = db1.selectOrders(lastMaxId, pageSize);
        List<Order> db2Orders = db2.selectOrders(lastMaxId, pageSize);
        List<Order> db3Orders = db3.selectOrders(lastMaxId, pageSize);
        List<Order> db4Orders = db4.selectOrders(lastMaxId, pageSize);
        
        // 合并排序
        List<Order> allOrders = new ArrayList<>();
        allOrders.addAll(db1Orders);
        allOrders.addAll(db2Orders);
        allOrders.addAll(db3Orders);
        allOrders.addAll(db4Orders);
        
        allOrders.sort(Comparator.comparing(Order::getId));
        
        // 取前pageSize条
        List<Order> result = allOrders.stream()
            .limit(pageSize)
            .collect(Collectors.toList());
        
        return new PageResult<>(result, getNextMaxId(result));
    }
}

优点：

✅ 性能好（每个分片只查pageSize条）
✅ 内存占用小
✅ 适合移动端（下拉加载更多）

缺点：

❌ 不能跳页
❌ 用户体验略差

---

3.3 解决方案2：二次查询优化 ⭐⭐⭐⭐

原理： 先查ID，再查明细

@Service
public class OrderService {
    
    public PageResult<Order> getPage(int page, int pageSize) {
        int offset = page * pageSize;
        int limit = pageSize;
        
        // 步骤1：每个分片只查ID（不查其他字段）
        // 索引覆盖，非常快
        List<Long> db1Ids = db1.selectIds(offset + limit);
        List<Long> db2Ids = db2.selectIds(offset + limit);
        List<Long> db3Ids = db3.selectIds(offset + limit);
        List<Long> db4Ids = db4.selectIds(offset + limit);
        
        // 步骤2：合并排序ID
        List<Long> allIds = new ArrayList<>();
        allIds.addAll(db1Ids);
        allIds.addAll(db2Ids);
        allIds.addAll(db3Ids);
        allIds.addAll(db4Ids);
        
        Collections.sort(allIds);
        
        // 步骤3：取需要的ID
        List<Long> targetIds = allIds.subList(offset, offset + limit);
        
        // 步骤4：根据ID查询完整数据（可能跨多个分片）
        List<Order> orders = orderService.findByIds(targetIds);
        
        return new PageResult<>(orders, allIds.size());
    }
}

优点：

✅ 第一次查询快（只查ID，索引覆盖）
✅ 减少数据传输
✅ 支持跳页

缺点：

❌ 需要两次查询
❌ 深分页仍然慢

---

3.4 解决方案3：使用ES分页 ⭐⭐⭐⭐⭐

原理： 分页查询交给ES

@Service
public class OrderSearchService {
    
    public PageResult<OrderDoc> searchOrders(SearchDTO dto, int page, int pageSize) {
        // ES天然支持分布式分页
        SearchResponse<OrderDoc> response = esClient.search(s -> s
            .index("orders")
            .query(q -> buildQuery(dto))
            .sort(so -> so.field(f -> f.field("createTime").order(SortOrder.Desc)))
            .from(page * pageSize)  // 直接支持分页
            .size(pageSize),
            OrderDoc.class
        );
        
        List<OrderDoc> orders = response.hits().hits().stream()
            .map(Hit::source)
            .collect(Collectors.toList());
        
        long total = response.hits().total().value();
        
        return new PageResult<>(orders, total);
    }
}

优点：

✅ ES天然支持分布式分页
✅ 性能好
✅ 支持复杂查询

缺点：

❌ 引入ES组件
❌ 数据同步延迟

---

3.5 解决方案4：业务限制 ⭐⭐⭐

原理： 限制用户只能查前N页

@Service
public class OrderService {
    
    private static final int MAX_PAGE = 100; // 最多查100页
    
    public PageResult<Order> getPage(int page, int pageSize) {
        if (page > MAX_PAGE) {
            throw new BusinessException("只能查询前100页，请使用搜索功能");
        }
        
        // 正常分页逻辑...
    }
}

优点：

✅ 实现简单
✅ 控制性能

缺点：

❌ 用户体验差
❌ 功能受限

---

四、方案对比总结 📊

4.1 跨分片JOIN方案对比

方案	复杂度	性能	适用场景	推荐度
应用层JOIN	⭐⭐⭐	⭐⭐⭐	任何场景	⭐⭐⭐⭐
数据冗余	⭐⭐	⭐⭐⭐⭐⭐	读多写少	⭐⭐⭐⭐⭐
全局表	⭐⭐	⭐⭐⭐⭐	字典表	⭐⭐⭐
ER分片	⭐⭐⭐⭐	⭐⭐⭐⭐	父子表	⭐⭐⭐
使用ES	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	复杂查询	⭐⭐⭐⭐⭐

4.2 跨分片分页方案对比

方案	性能	用户体验	适用场景	推荐度
禁止跳页	⭐⭐⭐⭐⭐	⭐⭐⭐	移动端	⭐⭐⭐⭐⭐
二次查询	⭐⭐⭐⭐	⭐⭐⭐⭐	浅分页	⭐⭐⭐⭐
使用ES	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	复杂查询	⭐⭐⭐⭐⭐
业务限制	⭐⭐⭐⭐⭐	⭐⭐	内部系统	⭐⭐⭐

---

五、实战案例：电商订单查询优化 💼

需求

用户查询自己的订单：
  - 按时间倒序
  - 支持分页
  - 需要显示商品名称

问题：
  - orders表分库分表（按user_id）
  - products表也分库分表（按product_id）
  - 跨表查询！

解决方案

方案1：数据冗余（推荐）

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    user_id BIGINT,
    product_id BIGINT,
    product_name VARCHAR(200),  -- 冗余商品名称
    product_image VARCHAR(500), -- 冗余商品图片
    amount DECIMAL(10,2),
    create_time DATETIME,
    INDEX idx_user_time (user_id, create_time)
);

-- 查询时不需要JOIN products表
SELECT order_no, product_name, product_image, amount, create_time
FROM orders
WHERE user_id = #{userId}
ORDER BY create_time DESC
LIMIT #{offset}, #{limit};

方案2：禁止跳页

@Service
public class OrderService {
    
    public PageResult<Order> getMyOrders(Long userId, Long lastOrderId, int pageSize) {
        // 只允许"下一页"，记录上次最后一个订单ID
        List<Order> orders = orderMapper.selectByUserIdAndLastId(
            userId, lastOrderId, pageSize
        );
        
        return new PageResult<>(orders, getLastOrderId(orders));
    }
}

<!-- MyBatis Mapper -->
<select id="selectByUserIdAndLastId" resultType="Order">
    SELECT * FROM orders
    WHERE user_id = #{userId}
    <if test="lastOrderId != null">
        AND id &lt; #{lastOrderId}
    </if>
    ORDER BY id DESC
    LIMIT #{pageSize}
</select>

---

六、面试高频问题 🎤

Q1: 分库分表后如何解决跨库JOIN？

答：

1. 应用层JOIN：分别查询，应用层关联
2. 数据冗余：在表中冗余关联字段（推荐）
3. 全局表：小表在每个库都复制
4. ER分片：父子表存储在同一分片
5. 使用ES：复杂查询交给搜索引擎

Q2: 分库分表后如何实现分页？

答：

1. 禁止跳页：只允许"下一页"，记录lastId（推荐）
2. 二次查询：先查ID，再查明细
3. 使用ES：ES天然支持分布式分页
4. 业务限制：限制只能查前N页

Q3: 深分页为什么慢？

答： 因为每个分片都要查offset+limit条数据，然后合并排序。例如LIMIT 10000, 10，4个分片需要查4×10010=40040条，但只用10条，浪费严重。

Q4: 如何优化深分页？

答：

1. 改用游标分页：WHERE id > lastId LIMIT 10
2. 使用ES：ES的from+size或scroll API
3. 业务限制：不允许查太后面的页

Q5: 数据冗余如何保证一致性？

答：

1. 更新时同步：更新主表时同步更新冗余字段
2. 异步补偿：定时任务检查并修复不一致数据
3. 最终一致性：允许短暂不一致，通过MQ等保证最终一致

---

七、总结口诀 📝

分库分表难题多，
跨库JOIN和分页破。
应用层JOIN最灵活，
数据冗余性能好。

全局表适合字典，
ER分片父子表。
复杂查询用ES，
分布式查询不用愁。

分页问题更麻烦，
深分页性能差。
禁止跳页用lastId，
移动端体验佳。

二次查询先查ID，
ES分页最优雅。
业务限制简单粗，
只查前面几十页！

---

参考资料 📚

• ShardingSphere官方文档
• Elasticsearch官方文档
• 《分库分表实战》

---

下期预告： 147-MySQL的索引失效场景有哪些？🔍

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的跨库查询如丝般顺滑！ 🧩✨