Sharding-JDBC 分庫分表Config · SpringCloud微服務實戰

# Sharding-JDBC 分庫分表基于java bean 的方式 >>**導航** [TOC] ## 本節代碼地址 GitHub:[https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-db-table-config](https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-db-table-config) ***** 本節主要介紹Sharding-JDBC分庫分表，并提供代碼樣例，在分布式項目架構中，分庫分表也是常用的一種方案。 ## 項目技術棧 | 技術棧 | 版本 | | --- | --- | | jdk | 1.8.0_161 | | SpringBoot | 2.2.2.RELEASE | | Shardingsphere | 4.0.1 | | Mybatis-plus | 3.3.0 | | Hutool | 5.0.6 | | HikariCP | 2.7.9 | * SpringBoot 基于SpringBoot 2.2.2.RELEASE 構建我們的應用 * Shardingsphere 使用最新版的4.0.1來支撐讀寫分離 * Mybatis-plus來作為我們的持久層框架，介紹代碼的開發量 * Hutool作為java工具包 * HikariCP 項目中的連接池，性能要比Druid要高，目前SpringBoot 默認支持的數據連接池。 * lombok 使代碼簡介，介紹getter\setter\構造器的創建，需要在idea中安裝lombok插件 ## 1. 數據庫創建 ### 1.1 新建數據庫 mysql 數據庫的搭建可以**參考容器化章節**（docker run 一個） ### 1.2 執行腳本執行以下腳本將創建數據庫ds_0和ds_1，并分別在兩個庫里面創建`sys_user0`、`sys_user1`表 ~~~ CREATE DATABASE ds0; USE ds0; DROP TABLE IF EXISTS `sys_user0`; CREATE TABLE `sys_user0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; DROP TABLE IF EXISTS `sys_user1`; CREATE TABLE `sys_user1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; CREATE DATABASE ds1; USE ds1; DROP TABLE IF EXISTS `sys_user0`; CREATE TABLE `sys_user0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; DROP TABLE IF EXISTS `sys_user1`; CREATE TABLE `sys_user1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; ~~~ ## 2. 新建讀寫分離模塊shardingsphere-learning-sub-db-table-config 上面我們已經配置了公共的針對用戶表的基本操作，這些暫時已經夠我們演示用了，如果不夠，可以自定義自己的實現 ### 2.1 添加依賴這里我們將`shardingsphere-learning-common`的包依賴進來，方便我們操作用戶表的增刪改查 ~~~ <dependencies> <dependency> <groupId>com.yisu.fwcloud</groupId> <artifactId>shardingsphere-learning-common</artifactId> <version>${version}</version> </dependency> </dependencies> ~~~ ### 2.2 應用配置 >這里筆者將默認的端口修改為了8900，然后就是配置Sharding-JDBC的數據源，我們本節演示的Sharding-JDBC讀寫分離需要兩個庫（真實項目中需要兩個數據庫實例）。 * 將sql日志信息打印出來 ~~~ server.port=8900 #打印sql spring.shardingsphere.props.sql.show=true logging.level.com.yisu= debug spring.main.allow-bean-definition-overriding=true ~~~ ### 2.3 數據源配置這里我們通過java bean 的方式手動創建HikariDataSource 來設置多個數據源，其中主鍵生成部分筆者使用的是MybatisPlus的雪花算法，也可以使用注釋部分的，并且需要配置ShardingRuleConfiguration來配置需要分片的表、配置庫的分片規則、需要分庫分表的字段。 ~~~ /** * @author xuyisu * @description 數據源配置 * @date 2020/3/14 */ @Configuration public class DataSourceConfig{ /** * * @return * @throws SQLException */ @Bean public DataSource dataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration()); //相同表分片規則的組，如果表分片規則相同，則可以放在一個組里 shardingRuleConfig.getBindingTableGroups().add("sys_user"); //廣播表 // shardingRuleConfig.getBroadcastTables().add("t_config"); // 根據ID分庫一共分為2個庫 shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}")); // 根據ID分表一共分為2張表 shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", new ShardingTableAlgorithmConfig())); Properties properties = new Properties(); properties.setProperty("sql.show",Boolean.TRUE.toString()); return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties); } /** * 主鍵配生成配置-因用了mybatis-plus，可以不用配置 * @return */ // private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() { // KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "id"); // return result; // } TableRuleConfiguration getOrderTableRuleConfiguration() { TableRuleConfiguration result = new TableRuleConfiguration("sys_user", "ds${0..1}.sys_user${0..1}"); // result.setKeyGeneratorConfig(getKeyGeneratorConfiguration()); return result; } /** * 創建數據源的集合 * @return */ Map<String, DataSource> createDataSourceMap() { Map<String, DataSource> result = new HashMap<>(); result.put("ds0", createDataSource("ds0")); result.put("ds1", createDataSource("ds1")); return result; } /** * 創建數據庫方案 * @param dataSourceName * @return */ public static DataSource createDataSource(final String dataSourceName) { HikariDataSource result = new HikariDataSource(); result.setDriverClassName(com.mysql.jdbc.Driver.class.getName()); result.setJdbcUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", "localhost", "3306", dataSourceName)); result.setUsername("root"); result.setPassword("123456"); return result; } } ~~~ ### 2.4 分表配置因為我們在上面配置了id為分表的字段，因此按此字段取模分片即可。 ~~~ /** * @author xuyisu * @description 分表配置 * @date 2020/3/14 */ public class ShardingTableAlgorithmConfig implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) { for (String each : tableNames) { if (each.endsWith(shardingValue.getValue() % 2 + "")) { return each; } } throw new RuntimeException("沒有需要分的表"); } } ~~~ ### 2.5 新建啟動類 ~~~ /** * @description 啟動類 * @author xuyisu * @date '2020-03-22' */ @SpringBootApplication public class FwShardingsphereSubDbTableConfig{ public static void main(String[] args) { SpringApplication.run(FwShardingsphereSubDbTableConfig.class, args); } } ~~~ ### 2.6 新建單元測試新建單元測試，用于測試讀寫分離的功能 ~~~ @RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT) public class SysUserServiceImplSubDBbTableConfigTest{ @Autowired private SysUserService sysUserService; /** * 測試數量 */ @Test public void testSelect(){ int count = sysUserService.count(); System.out.println(count); } /** * 測試單個新增 */ @Test public void testInsert(){ SysUser sysUser=new SysUser(); sysUser.setAvatar("/home/avatar"); sysUser.setCreateTime(DateUtil.date()); sysUser.setCreateUser("sys"); sysUser.setDeleteFlag(0); sysUser.setDeptCode("depe"); sysUser.setDisableFlag(0); sysUser.setEmail("***@123.com"); sysUser.setPassword("123456"); sysUser.setPosCode("pos"); sysUser.setRealName("realname"); sysUser.setUpdateTime(DateUtil.date()); sysUser.setUserName("fwcloud"); sysUser.setUpdateUser("sys"); sysUser.setUserPhone("12345678912"); sysUserService.save(sysUser); } /** * 測試批量新增 */ @Test public void testBatch(){ List<SysUser> list=new ArrayList<>(); for (int i = 0; i <100 ; i++) { SysUser sysUser=new SysUser(); sysUser.setAvatar("/home/avatar"); sysUser.setCreateTime(DateUtil.date()); sysUser.setCreateUser("sys"+i); sysUser.setDeleteFlag(0); sysUser.setDeptCode("depe"+i); sysUser.setDisableFlag(0); sysUser.setEmail("***@123.com"+i); sysUser.setPassword("123456"); sysUser.setPosCode("pos"); sysUser.setRealName("realname"+i); sysUser.setUpdateTime(DateUtil.date()); sysUser.setUserName("fwcloud"+i); sysUser.setUpdateUser("sys"+i); sysUser.setUserPhone("12345678912"+i); list.add(sysUser); } boolean saveBatch = sysUserService.saveBatch(list); Assert.assertEquals(true,saveBatch); } } ~~~ #### 2.6.1 測試新增先配置數據庫的ip地址，變量是dbIp，值是ip地址，也可以不用變量直接用真實的ip地址 ![](https://img.kancloud.cn/0a/bf/0abf49f0daa8acbf3a08cc4d5cc4387b_1049x620.png) 運行testBatch方法，可以看到控制臺的日志如下，可以看到走的實例是ds0和ds1都有 ~~~ 2020-03-22 17:18:33 INFO main ShardingSphere-SQL Actual SQL: ds0 ::: INSERT INTO sys_user0 ( id, user_phone, update_user, update_time, avatar, user_name, delete_flag, real_name, password, create_time, disable_flag, pos_code, create_user, dept_code, email ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241655496025108482, 123456789122, sys2, 2020-03-22 17:18:32.641, /home/avatar, fwcloud2, 0, realname2, 123456, 2020-03-22 17:18:32.641, 0, pos, sys2, depe2, ***@123.com2] 。。。。 2020-03-22 17:18:34 INFO main ShardingSphere-SQL Actual SQL: ds0 ::: INSERT INTO sys_user0 ( id, user_phone, update_user, update_time, avatar, user_name, delete_flag, real_name, password, create_time, disable_flag, pos_code, create_user, dept_code, email ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241655497413423106, 1234567891266, sys66, 2020-03-22 17:18:32.641, /home/avatar, fwcloud66, 0, realname66, 123456, 2020-03-22 17:18:32.641, 0, pos, sys66, depe66, ***@123.com66] 。。。 ~~~ 一共批量插入了100條數據，可以看到分別落入兩個庫的表里面 ![](https://img.kancloud.cn/bc/c0/bcc0f384831ddc9fac66b973eefa4387_443x187.png) ![](https://img.kancloud.cn/e2/ad/e2adf993a33755984d7f545bbfa5fdaa_349x181.png) #### 2.6.2 測試查詢運行testSelect方法，可以看到控制臺的日志如下，因為沒有讀寫分離，所以會查詢所有實例和表，總數量100 ~~~ 2020-03-22 17:27:45 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-22 17:27:45 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Rule Type: sharding 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Logic SQL: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-22 17:27:46 INFO main ShardingSphere-SQL SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@7bfc36d5, tablesContext=TablesContext(tables=[Table(name=sys_user, alias=Optional.absent())], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=( 1 ), alias=Optional.absent(), derivedAggregationProjections=[], index=-1)], columnLabels=[COUNT( 1 )]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@46b55a0e, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@7abd75fc, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@31b67d61, containsSubquery=false) 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Actual SQL: ds0 ::: SELECT COUNT( 1 ) FROM sys_user0 WHERE delete_flag=0 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Actual SQL: ds0 ::: SELECT COUNT( 1 ) FROM sys_user1 WHERE delete_flag=0 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Actual SQL: ds1 ::: SELECT COUNT( 1 ) FROM sys_user0 WHERE delete_flag=0 2020-03-22 17:27:46 INFO main ShardingSphere-SQL Actual SQL: ds1 ::: SELECT COUNT( 1 ) FROM sys_user1 WHERE delete_flag=0 2020-03-22 17:27:46 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <== Total: 1 100 ~~~ 至此，分庫分表基于java bena 的方式已完成