先贴代码, 回头讲解。未完待续
规划
实现分库分表、读写分离。
准备两张表：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;@Configurationpublic class ShardingJdbcConfig {@Beanpublic 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 {@Overridepublic 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 {@Overridepublic 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;@Slf4jpublic class KeyGenerate implements KeyGenerator {private static AtomicLong atomicInteger = new AtomicLong(1);@Overridepublic Number generateKey() {long nextId = atomicInteger.addAndGet(1);log.info("nextId:{}", nextId);return nextId;}}
