先贴代码, 回头讲解。未完待续
规划
实现分库分表、读写分离。
准备两张表:svc_order, svc_order_item
结构如下,可以想象成是未实施分库分表之前的数据库结构:
CREATE DATABASE IF NOT EXISTS DATABASE;
USE DATABASE;
CREATE TABLE IF NOT EXISTS `svc_order` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
一定要对原有结构做到心中有数,重复表述一下:
我们原有一个数据库叫 database
, 里面有两张表,分别是 svc_order
订单表和 svc_order_item
订单明细表,每个表都有一个主键ID 、用户ID(user_id) 和 名称。
计划:
svc_order 表按照 user_id 分库,按照主键 order_id 分片
svc_order_item 表按照 user_id 分库,按照主键 order_item_id 分片
原有库分成两个库,使用 user_id 对 2 取模,例如 user_id = 2 的存入 0 库,user_id = 3 的存入 1 库。
原有表分成两个表,svc_order
订单表使用 order_id 对 2 取模,svc_order_item
表使用 order_item_id 对 2 取模,例如 order_id = 2 的存入 0 表,order_id = 3 的存入 1 表。
读写分离设置为 1 个写库,2 个读库。
为了便于理解,我们定义常用名词:
逻辑表:指的就是未实施分表之前的表名。例如
svc_order
就是逻辑表名。虚拟表、物理表:指的是分表后真实存在的表名。上面我们说到存到 0 表、 1 表就是虚拟表,为了便于理解,我们把逻辑表名加在前面,比如
svc_order
的两个虚表我们可以命名为svc_order_0
、svc_order_1
顺便说明一下数据库名,我们原有 database
, 由于要分成两个库,我们可以命名为 database_0
、database_1
,由于,需要读写分离(1带2),相当于又多了四个数据库,至此,我们的数据库应该有 write_database_0
,read_0_database_0
,read_1_database_0
,write_database_1
,read_0_database_1
,read_1_database_1
数据库
init.sql
DROP DATABASE IF EXISTS write_database_0;
DROP DATABASE IF EXISTS read_0_database_0;
DROP DATABASE IF EXISTS read_1_database_0;
DROP DATABASE IF EXISTS write_database_1;
DROP DATABASE IF EXISTS read_0_database_1;
DROP DATABASE IF EXISTS read_1_database_1;
CREATE DATABASE IF NOT EXISTS write_database_0;
USE write_database_0;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS read_1_database_0;
USE read_1_database_0;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS read_0_database_0;
USE read_0_database_0;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS write_database_1;
USE write_database_1;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS read_0_database_1;
USE read_0_database_1;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS read_1_database_1;
USE read_1_database_1;
CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
依赖
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.5.4.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>
配置
ShardingJdbcConfig.java
package com.junbaor.sharding.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.MasterSlaveDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.slave.MasterSlaveLoadBalanceStrategyType;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.config.ShardingPropertiesConstant;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@Configuration
public class ShardingJdbcConfig {
@Bean
public DataSource buildDataSource() throws SQLException {
/*读写分离 start */
DataSource writeDatabase0 = createDataSource("write_database_0");
Map<String, DataSource> slave0DataBase = new HashMap<>(2);
slave0DataBase.put("read0_database_0", createDataSource("read0_database_0"));
slave0DataBase.put("read1_database_0", createDataSource("read1_database_0"));
DataSource database0 = MasterSlaveDataSourceFactory.createDataSource("write_database_0", "write_database_0", writeDatabase0, slave0DataBase, MasterSlaveLoadBalanceStrategyType.ROUND_ROBIN);
DataSource writeDatabase1 = createDataSource("write_database_1");
Map<String, DataSource> slave1DataBase = new HashMap<>(2);
slave1DataBase.put("read0_database_1", createDataSource("read0_database_1"));
slave1DataBase.put("read1_database_1", createDataSource("read1_database_1"));
DataSource database1 = MasterSlaveDataSourceFactory.createDataSource("write_database_1", "write_database_1", writeDatabase1, slave1DataBase, MasterSlaveLoadBalanceStrategyType.ROUND_ROBIN);
/*读写分离 end */
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
dataSourceMap.put("database_0", database0);
dataSourceMap.put("database_1", database1);
/*分库分表 start*/
DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, "database_0");
TableRule orderTableRule = TableRule.builder("svc_order")
.actualTables(Arrays.asList("svc_order_0", "svc_order_1"))
.generateKeyColumn("order_id", KeyGenerate.class)
.dataSourceRule(dataSourceRule)
.build();
TableRule orderItemTableRule = TableRule.builder("svc_order_item")
.actualTables(Arrays.asList("svc_order_item_0", "svc_order_item_1"))
.generateKeyColumn("order_item_id",KeyGenerate.class)
.dataSourceRule(dataSourceRule)
.build();
BindingTableRule bindOrderAndOrderItem = new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule));
ShardingRule shardingRule = ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule, orderItemTableRule))
.databaseShardingStrategy(new DatabaseShardingStrategy(Arrays.asList("user_id"), new DbSharding()))
.tableShardingStrategy(new TableShardingStrategy(Arrays.asList("order_id"), new TableSharding()))
.bindingTableRules(Arrays.asList(bindOrderAndOrderItem))
.build();
/*分库分表 end*/
Properties properties = new Properties();
properties.setProperty(ShardingPropertiesConstant.SQL_SHOW.getKey(), "true");
DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule, properties);
return dataSource;
}
private static DataSource createDataSource(final String dataSourceName) {
DruidDataSource result = new DruidDataSource();
result.setUrl(String.format("jdbc:mysql://127.0.0.1:3306/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false", dataSourceName));
result.setUsername("root");
result.setPassword("root");
return result;
}
}
分片工具
AppUtils.java
package com.junbaor.sharding.util;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
public class AppUtils {
public static Collection<String> sharding(Collection<String> availableTargetNames, Collection<ShardingValue<?>> shardingValues) {
long shardingvalue = 0;
Iterator<ShardingValue<?>> iterator = shardingValues.iterator();
if (iterator.hasNext()) {
shardingvalue = ((Long) iterator.next().getValue()).intValue();
}
for (String tableName : availableTargetNames) {
if (tableName.endsWith((shardingvalue % 2) + "")) {
return Arrays.asList(tableName);
}
}
return null;
}
}
分库算法
DbSharding.java
package com.junbaor.sharding.config;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.MultipleKeysDatabaseShardingAlgorithm;
import com.junbaor.sharding.util.AppUtils;
import java.util.Collection;
/**
* 利用多分片键接口实现单键分片算法
*/
public class DbSharding implements MultipleKeysDatabaseShardingAlgorithm {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue<?>> shardingValues) {
Collection<String> sharding = AppUtils.sharding(availableTargetNames, shardingValues);
if (sharding != null) {
return sharding;
}
return null;
}
}
分表算法
TableSharding.java
package com.junbaor.sharding.config;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.MultipleKeysTableShardingAlgorithm;
import com.junbaor.sharding.util.AppUtils;
import java.util.Collection;
/**
* 利用多分片键接口实现单键分片算法
*/
public class TableSharding implements MultipleKeysTableShardingAlgorithm {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue<?>> shardingValues) {
Collection<String> sharding = AppUtils.sharding(availableTargetNames, shardingValues);
if (sharding != null) {
return sharding;
}
return null;
}
}
主键生成器
KeyGenerate.java 仅供单机测试
package com.junbaor.sharding.config;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import lombok.extern.slf4j.Slf4j;
import java.util.concurrent.atomic.AtomicLong;
@Slf4j
public class KeyGenerate implements KeyGenerator {
private static AtomicLong atomicInteger = new AtomicLong(1);
@Override
public Number generateKey() {
long nextId = atomicInteger.addAndGet(1);
log.info("nextId:{}", nextId);
return nextId;
}
}
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。