分库分表之实战-sharding-JDBC水平分库+分表后：查询与删除操作实战

前情摘要：

1、数据库性能优化 2、分库分表之优缺点分析 3、分库分表之数据库分片分类 4、分库分表之策略 5、分库分表技术栈讲解-Sharding-JDBC 6、分库分表下的 ID 冲突问题与雪花算法讲解 7、分库分表之实战-sharding-JDBC 8、分库分表之实战-sharding-JDBC广播表 9、分库分表之实战-sharding-JDBC水平分库+水平分表配置实战 10、分库分表之实战-sharding-JDBC绑定表配置实战

【亲测宝藏】发现一个让 AI 学习秒变轻松的神站！不用啃高数、不用怕编程，高中生都能看懂的人工智能教程来啦！

👉点击跳转，和 thousands of 小伙伴一起用快乐学习法征服 AI，说不定下一个开发出爆款 AI 程序的就是你！

水平分库+分表后：查询与删除操作实战

在水平分库+分表架构中，CRUD操作的路由逻辑直接影响系统性能与数据准确性。本文聚焦查询和删除操作，通过实战案例解析「有分片键」和「无分片键」场景下的路由规则，结合Sharding-Jdbc的实现原理，总结高效操作的最佳实践。

一、知识回顾：分库分表路由核心

先明确本文的分库分表规则（以订单表product_order为例）：

• 分库策略：按user_id取模（2个库：ds0、ds1）
• 分表策略：按id取模（每个库分2张表：t0、t1）
• 完整表名格式：ds{user_id % 2}.product_order_{id % 2}

核心概念：

• 分片键：决定数据路由的字段（本文中user_id为分库键，order_id为分表键，统称「分片键」）
• 标准路由：有明确分片键时，精准定位到目标库表（高效）
• 全库表路由：无分片键时，扫描所有库表（低效，需避免）

二、查询操作实战：有分片键 vs 无分片键

查询操作的路由逻辑是分库分表中最常接触的场景，也是性能优化的关键。

1. 有分片键的查询（标准路由）

当查询条件中包含分库键或分表键时，Sharding-Jdbc会通过「标准路由」精准定位目标库表，避免全扫描。

（1）= 条件查询（单分片键）

在DbTest中新增测试函数

@Test
    public void testPartitionKeyQuery(){
        List<ProductOrderDO> list = productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().eq("id", 1942559776326320130L));
    }

在这里插入图片描述

结果只会发出两条sql 路由过程：

• 计算分表：id=1942559776326320130 % 2 = 0 → 目标表为product_order_t0
• 分库键user_id未指定，需扫描所有分库的product_order_t0
• 实际执行：ds0.product_order_t0、ds1.product_order_t0各查1次

在DbTest中新增测试函数（同时包含分库键user_id和分表键order_id）：

@Test
    public void testTwoPartitionKeyQuery(){
        List<ProductOrderDO> list = productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().eq("id", 1942559776326320130L)
                .eq("user_id", 204402002533403893L));
    }

在这里插入图片描述

结果只会发出1条sql

路由过程：

• 分库：user_id=204402002533403893 % 2 = 1 → 目标库ds1
• 分表：id=1942559776326320130 % 2 = 0 → 目标表product_order_t0`
• 实际执行：仅查询 ds1.product_order_t0（1次操作，最优路由）

（2）IN 条件查询（多分片键）

在DbTest中新增测试函数（分表键id用IN查询）：

@Test
    public void testPartitionKeyInQuery(){
        List<ProductOrderDO> list = productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().in("id", Arrays.asList(1942559776326320130L, 1942559779014868995L))
        );
    }

执行结果

在这里插入图片描述

路由过程：

• order_id=1942559776326320130 % 2 = 0 → 表product_order_t0
• order_id=1942559779014868995 % 2 = 1 → 表product_order_t1
• 分库键user_id未指定，需扫描所有分库的t0和t1
• 实际执行：ds0.t0、ds0.t1、ds1.t0、ds1.t1（4次操作，按分表键拆分后路由）

2. 无分片键的查询（全库表路由）

当查询条件中不包含任何分片键时，Sharding-Jdbc会执行「全库表路由」，即扫描所有分库分表。

在DbTest中新增测试函数（仅按非分片键out_trade_no查询）：

 @Test
    public void testNoPartitionKeyQuery(){
        List<ProductOrderDO> list = productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().eq("out_trade_no", "70bd5220-1c9c-4e3e-b418-85481f60")
        );
    }

在这里插入图片描述

路由过程：

• 无user_id和id，无法定位分库分表
• 实际执行：查询所有4张表（ds0.t0、ds0.t1、ds1.t0、ds1.t1），并合并结果

问题与风险：

• 性能损耗：全表扫描次数 = 分库数 × 分表数（本例4次，分表越多损耗越大）
• 数据一致性：若表中存在重复数据（如不同分片有相同out_trade_no=70bd5220-1c9c-4e3e-b418-85481f60的记录），需业务层保证逻辑唯一

三、删除操作实战

删除操作的路由逻辑与查询基本一致，但需额外注意数据安全（尤其是无分片键时）。

1. 有分片键的删除（标准路由）

（1）= 条件删除

在DbTest中新增测试函数（按分库键+分表键删除）：

@Test
    public void testTwoPartitionKeyDelete(){
        productOrderMapper.delete(new QueryWrapper<ProductOrderDO>().eq("id", 1942559776326320130L)
                .eq("user_id", 204402002533403893L));
    }

执行结果

在这里插入图片描述

路由过程：

• 定位到ds1.product_order_t0（1次删除，精准高效）

（2）IN 条件删除

在DbTest中新增测试函数（按分表键批量删除）：

@Test
    public void testPartitionKeyInDelete(){
        productOrderMapper.delete(new QueryWrapper<ProductOrderDO>().in("id", Arrays.asList(1942559776326320130L, 1942559779014868995L))
        );
    }

执行结果

在这里插入图片描述

路由过程：

• 分表：1942559776326320130→t0、1942559779014868995→t1，但无分库键user_id
• 实际执行：删除ds0.t0、ds0.t1、ds1.t0、ds1.t1中符合条件的记录（4次操作）

2. 无分片键的删除（全库表路由）

在DbTest中新增测试函数：

@Test
    public void testNoPartitionKeyDelete(){
        productOrderMapper.delete(new QueryWrapper<ProductOrderDO>().eq("out_trade_no", "70bd5220-1c9c-4e3e-b418-85481f60")
        );
    }

执行结果

在这里插入图片描述

路由过程：

• 扫描所有4张表，执行删除操作
• 风险提示：无分片键的删除可能导致「误删全量数据」，建议先通过查询验证数据范围，再执行删除（可加LIMIT限制条数）

四、路由原理：Sharding-Jdbc如何决定路由规则？

Sharding-Jdbc的路由逻辑基于「分片键提取→分片算法计算→目标库表定位」三步：

1. 提取分片键：从SQL的WHERE条件中解析分库键（如user_id）和分表键（如order_id）
2. 计算分片：调用配置的分片算法（如取模、哈希），得到目标库索引和表索引
3. 生成路由：若有完整分片键，生成「标准路由」；若缺失，生成「全库表路由」

五、最佳实践与避坑指南

1. 优先使用「有分片键」的操作

• 所有查询和删除尽量携带分库键或分表键（至少携带一个），避免全库表扫描

2. 谨慎使用IN条件

• IN中的值不宜过多（建议≤10个），否则会触发多表路由，性能接近全扫描

3. 无分片键删除的安全操作

• 必须执行时，先通过SELECT COUNT(*)预估数据量，确认后再删除
• 加LIMIT限制单次删除条数：DELETE FROM product_order WHERE status=3 LIMIT 1000;

4. 结合索引优化查询

• 分表的分片键必须建索引（如id），避免单表内全扫描
• 非分片键查询需配合分表键使用，例：WHERE user_id=1 AND out_trade_no=1（先定位库，再查订单号）

5. 注意事务一致性

• 跨分片删除会触发分布式事务，建议通过「分片键+本地事务」避免（如按user_id批量删除）

六、总结

水平分库分表后的查询和删除操作，核心是对分片键的依赖程度：

• 「有分片键」→ 标准路由，高效精准，推荐使用
• 「无分片键」→ 全库表路由，低效且有风险，谨慎使用

实际开发中，应通过业务设计强制分片键的使用（如API接口必须传入user_id或order_id），同时结合监控工具（如Sharding-Jdbc的SQL日志）跟踪路由情况，及时优化不合理的操作。

觉得有用请点赞收藏！ 如果有相关问题，欢迎评论区留言讨论~