摘要:未完待续规划实现分库分表读写分离。计划表按照分库,按照主键分片表按照分库,按照主键分片原有库分成两个库,使用对取模,例如的存入库,的存入库。读写分离设置为个写库,个读库。虚拟表物理表指的是分表后真实存在的表名。
先贴代码, 回头讲解。未完待续
规划实现分库分表、读写分离。
准备两张表: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
配置com.alibaba druid 1.1.5 mysql mysql-connector-java 6.0.6 com.dangdang sharding-jdbc-core 1.5.4.1 org.projectlombok lombok 1.16.18 provided
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分片工具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 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 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分库算法sharding(Collection availableTargetNames, Collection > shardingValues) { long shardingvalue = 0; Iterator > 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分表算法doSharding(Collection availableTargetNames, Collection > shardingValues) { Collection 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主键生成器doSharding(Collection availableTargetNames, Collection > shardingValues) { Collection 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; } }
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/68050.html
摘要:实现数据库分库分表可以自己实现,也可以使用和实现。分布式数据库的自增不是自增的。分布式数据库分页查询需要使用插入时间实现。包含分库分片和读写分离功能。 Sharding-Jdbc实现mysql分库分表 简单介绍 数据库分库分表和读写分离区别,分库分表是在多个库建相同的表和同一个库建不同的表,根据随机或者哈希等方式查找实现。读写分离是为了解决数据库的读写性能不足,使用主库master进行...
摘要:今天就给大家介绍下方式的使用,主要讲解读写分离的配置,其余的后面再介绍。主要还是用提供的,配置如下配置内容如下主数据源从数据源读写分离配置查询时的负载均衡算法,目前有种算法,轮询和随机,算法接口是。 在我《Spring Cloud微服务-全栈技术与案例解析》书中,第18章节分库分表解决方案里有对Sharding-JDBC的使用进行详细的讲解。 之前是通过XML方式来配置数据源,读写分离...
阅读 1826·2021-11-22 09:34
阅读 2939·2021-09-28 09:35
阅读 13130·2021-09-09 11:34
阅读 3547·2019-08-29 16:25
阅读 2757·2019-08-29 15:23
阅读 2004·2019-08-28 17:55
阅读 2398·2019-08-26 17:04
阅读 3015·2019-08-26 12:21