《ShardingSphere解读》05 数据分片（上）

05 数据分片如何实现分库、分表、分库分表以及强制路由上通过前面几篇的介绍相信你对 ShardingSphere 已经有了初步了解。从今天开始将通过案例分析逐步掌握 ShardingSphere 的各项核心功能首当其冲的就是分库分表机制。1. 单库单表系统我们先从单库单表系统说起。我们将默认使用 Spring Boot 集成 ShardingSphere 框架同时基于 MyBatis 实现对数据库的访问。1.1 导入开发框架在pom.xml中添加以下依赖dependency groupIdorg.apache.shardingsphere/groupId artifactIdsharding-jdbc-spring-boot-starter/artifactId /dependency dependency groupIdorg.mybatis.spring.boot/groupId artifactIdmybatis-spring-boot-starter/artifactId /dependency1.2 梳理业务场景我们考虑一个医疗健康领域的典型场景每个用户User拥有一份健康记录HealthRecord记录中包含用户的健康等级HealthLevel以及一系列待办的健康任务HealthTask。医生根据健康记录创建任务用户完成任务后获取积分积分影响健康等级最终更新健康记录形成一个正向的业务闭环。本场景聚焦四个核心业务对象User、HealthRecord、HealthLevel、HealthTask。它们的关系可用以下类图表示1.3 完成基础功能按照 MyBatis 的开发流程我们首先定义实体类代码略然后编写 Mapper 接口及对应的 XML 文件。接着配置数据源application-traditional.propertiesspring.datasource.driverClassNamecom.mysql.jdbc.Driver spring.datasource.urljdbc:mysql://localhost:3306/ds spring.datasource.usernameroot spring.datasource.passwordroot接下来创建 Repository 层和 Service 层组件。以HealthRecordService为例其核心逻辑如下Service public class HealthRecordServiceImpl implements HealthRecordService { Autowired private HealthRecordRepository healthRecordRepository; Autowired private HealthTaskRepository healthTaskRepository; Override public void processHealthRecords() throws SQLException { insertHealthRecords(); } private ListInteger insertHealthRecords() throws SQLException { ListInteger result new ArrayList(10); for (int i 1; i 10; i) { HealthRecord healthRecord insertHealthRecord(i); insertHealthTask(i, healthRecord); result.add(healthRecord.getRecordId()); } return result; } private HealthRecord insertHealthRecord(int i) throws SQLException { HealthRecord healthRecord new HealthRecord(); healthRecord.setUserId(i); healthRecord.setLevelId(i % 5); healthRecord.setRemark(Remark i); healthRecordRepository.addEntity(healthRecord); return healthRecord; } private void insertHealthTask(int i, HealthRecord healthRecord) throws SQLException { HealthTask healthTask new HealthTask(); healthTask.setRecordId(healthRecord.getRecordId()); healthTask.setUserId(i); healthTask.setTaskName(TaskName i); healthTaskRepository.addEntity(healthTask); } }最后通过单元测试如UserServiceTest验证数据能够正常插入单库单表系统构建完成。2. 系统改造如何实现分库在单库单表基础上进行分库分表改造只需调整 ShardingSphere 配置业务代码完全不用改动。下面我们以实现分库为目标进行配置。2.1 初始化数据源准备两个数据库ds0和ds1并在配置中声明spring.shardingsphere.datasource.namesds0,ds1 spring.shardingsphere.datasource.ds0.typecom.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds0.driver-class-namecom.mysql.jdbc.Driver spring.shardingsphere.datasource.ds0.urljdbc:mysql://localhost:3306/ds0 spring.shardingsphere.datasource.ds0.usernameroot spring.shardingsphere.datasource.ds0.passwordroot spring.shardingsphere.datasource.ds1.typecom.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds1.driver-class-namecom.mysql.jdbc.Driver spring.shardingsphere.datasource.ds1.urljdbc:mysql://localhost:3306/ds1 spring.shardingsphere.datasource.ds1.usernameroot spring.shardingsphere.datasource.ds1.passwordroot2.2 设置分片策略使用行表达式分片策略按user_id对 2 取模决定数据进入ds0还是ds1spring.shardingsphere.sharding.default-database-strategy.inline.sharding-columnuser_id spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expressionds$-{user_id % 2}2.3 设置绑定表和广播表绑定表Binding Table绑定表是指分片规则一致的一组主表和子表。例如health_record和health_task都包含record_id字段若按该字段分片它们可互为绑定表。配置绑定表后关联查询能避免笛卡尔积提升效率。下图对比了有无绑定表的区别因此我们在配置中添加绑定表spring.shardingsphere.sharding.binding-tableshealth_record,health_task广播表Broadcast Table广播表指所有分片数据源中都存在的表表结构和数据完全一致常用于字典表。本例中health_level即为广播表spring.shardingsphere.sharding.broadcast-tableshealth_level2.4 设置表分片规则配置TableRuleConfiguration包括实际数据节点和分布式主键生成器。health_record 表spring.shardingsphere.sharding.tables.health_record.actual-data-nodesds$-{0..1}.health_record spring.shardingsphere.sharding.tables.health_record.key-generator.columnrecord_id spring.shardingsphere.sharding.tables.health_record.key-generator.typeSNOWFLAKE spring.shardingsphere.sharding.tables.health_record.key-generator.props.worker.id33health_task 表spring.shardingsphere.sharding.tables.health_task.actual-data-nodesds$-{0..1}.health_task spring.shardingsphere.sharding.tables.health_task.key-generator.columntask_id spring.shardingsphere.sharding.tables.health_task.key-generator.typeSNOWFLAKE spring.shardingsphere.sharding.tables.health_task.key-generator.props.worker.id333. 验证分库效果重新执行单元测试数据将按user_id奇偶分布到ds0和ds1。下图展示了数据分布逻辑实际数据如下部分示例ds0.health_recordrecord_iduser_idlevel_idremark111Remark1333Remark3550Remark5772Remark7994Remark9ds1.health_recordrecord_iduser_idlevel_idremark222Remark2444Remark4661Remark6883Remark810100Remark10显然数据已按预期分库存储。4. 小结与思考本篇我们从零构建了一个单库单表的医疗健康系统然后通过 ShardingSphere 的配置体系实现了分库改造。我们学习了如何配置数据源、分片策略、绑定表、广播表以及表分片规则。分库是分片引擎中最基础的功能下一篇我们将继续探讨分表、分库分表以及强制路由等高级特性。思考题如何理解绑定表和广播表的含义和作用它们在实际生产中解决了哪些问题欢迎在评论区分享你的理解。