上一篇 <<<Sharding-Jdbc实现读写分离
下一篇 >>>Sharding-Jdbc与MyCat区别
分片算法
分片算法目前需要业务方开发者自行实现，目前支持通过等号（doEqualSharding）、BETWEEN（doBetweenSharding）和IN（doInSharding）分片。
未来Sharding-JDBC也将会实现常用分片算法，如range，hash和tag等。
分片查询底层原理
和Mycat的查询原理一样
a.非分片关键字查询会搜索所有的分库分表，结果归并后按照sql语句排序返回，如果未设置排序，则按分库随机返回结果
b.分片关键字查询会直接定位到对应的分库，执行相应的sql语句返回结果。
SpringBoot整合Sharding-Jdbc方式
1.原生配置方式，自己需要实现接口。
a.分库算法类需要实现SingleKeyDatabaseShardingAlgorithm<T>接口
b.分表算法类需要实现SingleKeyTableShardingAlgorithm<T>接口
1.1代码水平单库拆分多表
- 核心：分表算法类需要实现SingleKeyTableShardingAlgorithm<T>接口
创建db_0数据库
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=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
<dependencies>
<!-- jpa -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</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>
</dependencies>
###数据库访问连接
spring:
jdbc:
db0:
password: root
className: com.mysql.jdbc.Driver
#数据库名称由代码中植入
url: jdbc:mysql://10.211.55.26:3306/%s?characterEncoding=utf-8
username: root
jpa:
database: mysql
show-sql: true
hibernate:
## 自己建表
ddl-auto: none
application:
name: sharding-jdbc-first
/**
* 数据源相关配置信息
*/
@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() {
/**
* 设置数据库，多个库组个往里面添加
*/
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
dataSourceMap.put("ds_0", createDataSource("ds_0"));
// dataSourceMap.put("ds_1", createDataSource("ds_1"));
/**如果有多个数据库，则必须指定默认数据库*/
DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
/**数据分片的逻辑表(t_order)，对应水平拆分的真实存在的物理表(t_order_0和t_order_1)，同一类表的总称。*/
TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
.dataSourceRule(rule).build();
/**分片策略*/
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;
}
}
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
/**
* 同一个数据库中分表的策略
* @param availableTargetNames 分表的集合 t_order_0 和t_order_1
* @param shardingValue userid 分片字段值
* @return
*/
@Override
public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
for (String tableName : availableTargetNames) {
//tableName = t_order_0
// shardingValue.getValue()=2
// t_order_0 2%2=0
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;
}
}
1.25.代码水平拆分为多库
- 分库算法类需要实现SingleKeyDatabaseShardingAlgorithm<T>接口
和单库多表相比的代码改动点：
/**
* 数据源相关配置信息
*/
@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() {
/**
* 设置数据库，多个库组个往里面添加
*/
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
dataSourceMap.put("ds_0", createDataSource("ds_0"));
dataSourceMap.put("ds_1", createDataSource("ds_1"));
/**如果有多个数据库，则必须指定默认数据库*/
DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
/**数据分片的逻辑表(t_order),和物理表一致，则不需要实际物理表*/
TableRule orderTableRule = TableRule.builder("t_order")
.dataSourceRule(rule).build();
/**分片策略*/
ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
.tableRules(Arrays.asList(orderTableRule))
//根据userid分片字段
.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;
}
}
public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
@Override
public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) {
for (String database : databases) {
System.out.println("database:" + database + ",----" + shardingValue.getValue());
if (database.endsWith(shardingValue.getValue() % 2 + "")) {
return database;
}
}
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.通过配置文件形式配置。
案例比如：t_order 拆分程t_order_0 t_order _1
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<!--必须要用M3版本，用M2版本会有问题-->
<version>3.0.0.M3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
</dependencies>
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://10.211.55.26:3306/ds_0?characterEncoding=utf-8
username: root
config:
sharding:
tables:
#如果要对不同的表进行分片，则类似t_order写多个接口
t_order:
table-strategy:
inline:
#### 根据userid 进行分片
sharding-column: user_id
algorithm-expression: ds_0.t_order_$->{user_id % 2}
actual-data-nodes: ds1.t_order_$->{0..1}
props:
sql:
### 开启分片日志
show: true
推荐阅读：
<<<MySQL自带主从复制原理
<<<MyCat实现读写分离与动态数据源切换
<<<分表分库与分区的区别及拆分策略
<<<MyCat的分片查询原理
<<<Sharding-Jdbc实现读写分离
<<<Sharding-Jdbc与MyCat区别
