1. 引言
先来理解几个概念：
- 「LogicTable」：数据分片的逻辑表，对于水平拆分的数据库(表)，同一类表的总称。例如订单信息表拆分为2张表,分别是
t_order_0、t_order_1，他们的逻辑表名为t_order。 - 「ActualTable」：在分片的数据库中真实存在的物理表。即上个示例中的
t_order_0、t_order_1。 - 「DataNode」：数据分片的最小单元。由数据源名称和数据表组成，例：
test_msg0.t_order_0。配置时默认各个分片数据库的表结构均相同，直接配置逻辑表和真实表对应关系即可。 - 「ShardingColumn」：分片字段。用于将数据库(表)水平拆分的关键字段。SQL中如果无分片字段，将执行全路由，性能较差。Sharding-JDBC支持多分片字段。
- 「ShardingAlgorithm」：分片算法。Sharding-JDBC通过分片算法将数据分片，支持通过等号、BETWEEN和IN分片。分片算法目前需要业务方开发者自行实现，可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法，如range，hash和tag等。
2. SpringBoot整合Sharding-Jdbc
SpringBoot整合Sharding-Jdbc分为两种方式
方式一：原生配置方式，自己需要实现接口
- 分库算法类需要实现
SingleKeyDatabaseShardingAlgorithm<T>接口 - 分表算法类需要实现
SingleKeyTableShardingAlgorithm<T>接口
方式二：通过配置文件形式配置
2.1 案例分析
案例需求：t_order 需要拆分成t_order_0和 t_order _1
SpringBoot整合Sharding-Jdbc之前，先来定义数据库表结构：
CREATE TABLE `t_order_0` ( `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; CREATE TABLE `t_order_1` ( `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoD
2.2 原生配置方式
1.添加maven依赖
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.3.RELEASE</version> <relativePath /> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <!-- jpa --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!-- 引入shardingjdbc依赖信息 --> <dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>2.0.3</version> </dependency> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-self-id-generator</artifactId> <version>1.4.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.12</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>
2.application配置
###数据库访问连接 spring: jdbc: db0: className: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/%s?characterEncoding=utf-8 username: root password: 123456 jpa: database: mysql show-sql: true hibernate: ## 自己建表 ddl-auto: none application: name: sharding-jdbc-first
3.分表算法
DataSourceConfig:
// 数据源相关配置信息 @Configuration public class DataSourceConfig { @Value("${spring.jdbc.db0.className}") private String className; @Value("${spring.jdbc.db0.url}") private String url; @Value("${spring.jdbc.db0.username}") private String username; @Value("${spring.jdbc.db0.password}") private String password; @Bean public IdGenerator getIdGenerator() { return new CommonSelfIdGenerator(); } @Bean public DataSource getDataSource() { return buildDataSource(); } private DataSource buildDataSource() { // 1.设置分库映射 Map<String, DataSource> dataSourceMap = new HashMap<>(2); dataSourceMap.put("ds_0", createDataSource("ds_0")); // dataSourceMap.put("ds_1", createDataSource("ds_1")); // 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库 // 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库， // 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据 DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0"); // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表 TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1")) .dataSourceRule(rule).build(); // 3.具体的分库分表策略 ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule) .tableRules(Arrays.asList(orderTableRule)) // 根据userid分片字段 .tableShardingStrategy(new TableShardingStrategy("user_id", new TableShardingAlgorithm())).build(); // 创建数据源 DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule); return dataSource; } private DataSource createDataSource(String dataSourceName) { // 使用druid连接数据库 DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setDriverClassName(className); druidDataSource.setUrl(String.format(url, dataSourceName)); druidDataSource.setUsername(username); druidDataSource.setPassword(password); return druidDataSource; } }
TableShardingAlgorithm:
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> { // sql 中关键字 匹配符为 =的时候，表的路由函数 public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) { for (String tableName : availableTargetNames) { if (tableName.endsWith(shardingValue.getValue() % 2 + "")) { return tableName; } } throw new IllegalArgumentException(); } @Override public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) { return null; } @Override public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) { return null; } }
4.分库算法
分库算法类需要实现SingleKeyDatabaseShardingAlgorithm<T>接口:
DataSourceConfig:
// 数据源相关配置信息 @Configuration public class DataSourceConfig { @Value("${spring.jdbc.db0.className}") private String className; @Value("${spring.jdbc.db0.url}") private String url; @Value("${spring.jdbc.db0.username}") private String username; @Value("${spring.jdbc.db0.password}") private String password; @Bean public IdGenerator getIdGenerator() { return new CommonSelfIdGenerator(); } @Bean public DataSource getDataSource() { return buildDataSource(); } private DataSource buildDataSource() { // 1.设置分库映射 Map<String, DataSource> dataSourceMap = new HashMap<>(2); dataSourceMap.put("ds_0", createDataSource("ds_0")); dataSourceMap.put("ds_1", createDataSource("ds_1")); // 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库 // 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库， // 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据 DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0"); // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表 TableRule orderTableRule = TableRule.builder("t_order").dataSourceRule(rule).build(); // 3.具体的分库分表策略 ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule) .tableRules(Arrays.asList(orderTableRule)) .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DatabaseShardingAlgorithm())) .build(); // 创建数据源 DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule); return dataSource; } private DataSource createDataSource(String dataSourceName) { // 使用druid连接数据库 DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setDriverClassName(className); druidDataSource.setUrl(String.format(url, dataSourceName)); druidDataSource.setUsername(username); druidDataSource.setPassword(password); return druidDataSource; } }
DatabaseShardingAlgorithm:
public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> { @Override public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) { for (String tableName : databases) { System.out.println("tableName:" + tableName + ",----" + shardingValue.getValue()); if (tableName.endsWith(shardingValue.getValue() % 2 + "")) { return tableName; } } throw new IllegalArgumentException(); } @Override public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) { return null; } @Override public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) { return null; } }
2.3 配置文件方式
1.添加maven依赖：
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.4.RELEASE</version> <relativePath /> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.0.0.M3</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.9</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> </dependencies>
2.application配置：
spring: jpa: show-sql: true hibernate: ddl-auto: none database-platform: org.hibernate.dialect.MySQL5InnoDBDialect sharding: jdbc: ####ds1 datasource: names: ds1 ds1: password: root type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/ds_1 username: root config: sharding: tables: t_order: table-strategy: inline: #### 根据userid 进行分片 sharding-column: user_id algorithm-expression: ds_1.t_order_$->{user_id % 2} actual-data-nodes: ds1.t_order_$->{0..1} props: sql: ### 开启分片日志 show: true
