摘要:未完待續(xù)規(guī)劃實(shí)現(xiàn)分庫(kù)分表讀寫(xiě)分離。計(jì)劃表按照分庫(kù),按照主鍵分片表按照分庫(kù),按照主鍵分片原有庫(kù)分成兩個(gè)庫(kù),使用對(duì)取模,例如的存入庫(kù),的存入庫(kù)。讀寫(xiě)分離設(shè)置為個(gè)寫(xiě)庫(kù),個(gè)讀庫(kù)。虛擬表物理表指的是分表后真實(shí)存在的表名。
先貼代碼, 回頭講解。未完待續(xù)
規(guī)劃實(shí)現(xiàn)分庫(kù)分表、讀寫(xiě)分離。
準(zhǔn)備兩張表:svc_order, svc_order_item
結(jié)構(gòu)如下,可以想象成是未實(shí)施分庫(kù)分表之前的數(shù)據(jù)庫(kù)結(jié)構(gòu):
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;
一定要對(duì)原有結(jié)構(gòu)做到心中有數(shù),重復(fù)表述一下:
我們?cè)幸粋€(gè)數(shù)據(jù)庫(kù)叫 database, 里面有兩張表,分別是 svc_order 訂單表和 svc_order_item 訂單明細(xì)表,每個(gè)表都有一個(gè)主鍵ID 、用戶ID(user_id) 和 名稱。
計(jì)劃:
svc_order 表按照 user_id 分庫(kù),按照主鍵 order_id 分片
svc_order_item 表按照 user_id 分庫(kù),按照主鍵 order_item_id 分片
原有庫(kù)分成兩個(gè)庫(kù),使用 user_id 對(duì) 2 取模,例如 user_id = 2 的存入 0 庫(kù),user_id = 3 的存入 1 庫(kù)。
原有表分成兩個(gè)表,svc_order 訂單表使用 order_id 對(duì) 2 取模,svc_order_item 表使用 order_item_id 對(duì) 2 取模,例如 order_id = 2 的存入 0 表,order_id = 3 的存入 1 表。
讀寫(xiě)分離設(shè)置為 1 個(gè)寫(xiě)庫(kù),2 個(gè)讀庫(kù)。
為了便于理解,我們定義常用名詞:
邏輯表:指的就是未實(shí)施分表之前的表名。例如 svc_order 就是邏輯表名。
虛擬表、物理表:指的是分表后真實(shí)存在的表名。上面我們說(shuō)到存到 0 表、 1 表就是虛擬表,為了便于理解,我們把邏輯表名加在前面,比如 svc_order 的兩個(gè)虛表我們可以命名為 svc_order_0、svc_order_1
順便說(shuō)明一下數(shù)據(jù)庫(kù)名,我們?cè)?database, 由于要分成兩個(gè)庫(kù),我們可以命名為 database_0、database_1,由于,需要讀寫(xiě)分離(1帶2),相當(dāng)于又多了四個(gè)數(shù)據(jù)庫(kù),至此,我們的數(shù)據(jù)庫(kù)應(yīng)該有 write_database_0,read_0_database_0,read_1_database_0,write_database_1,read_0_database_1,read_1_database_1
數(shù)據(jù)庫(kù)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 { /*讀寫(xiě)分離 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); /*讀寫(xiě)分離 end */ Map dataSourceMap = new HashMap<>(2); dataSourceMap.put("database_0", database0); dataSourceMap.put("database_1", database1); /*分庫(kù)分表 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(); /*分庫(kù)分表 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分庫(kù)算法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; /** * 利用多分片鍵接口實(shí)現(xiàn)單鍵分片算法 */ 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; /** * 利用多分片鍵接口實(shí)現(xiàn)單鍵分片算法 */ 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 僅供單機(jī)測(cè)試
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; } }
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://www.ezyhdfw.cn/yun/68050.html
摘要:實(shí)現(xiàn)數(shù)據(jù)庫(kù)分庫(kù)分表可以自己實(shí)現(xiàn),也可以使用和實(shí)現(xiàn)。分布式數(shù)據(jù)庫(kù)的自增不是自增的。分布式數(shù)據(jù)庫(kù)分頁(yè)查詢需要使用插入時(shí)間實(shí)現(xiàn)。包含分庫(kù)分片和讀寫(xiě)分離功能。 Sharding-Jdbc實(shí)現(xiàn)mysql分庫(kù)分表 簡(jiǎn)單介紹 數(shù)據(jù)庫(kù)分庫(kù)分表和讀寫(xiě)分離區(qū)別,分庫(kù)分表是在多個(gè)庫(kù)建相同的表和同一個(gè)庫(kù)建不同的表,根據(jù)隨機(jī)或者哈希等方式查找實(shí)現(xiàn)。讀寫(xiě)分離是為了解決數(shù)據(jù)庫(kù)的讀寫(xiě)性能不足,使用主庫(kù)master進(jìn)行...
摘要:今天就給大家介紹下方式的使用,主要講解讀寫(xiě)分離的配置,其余的后面再介紹。主要還是用提供的,配置如下配置內(nèi)容如下主數(shù)據(jù)源從數(shù)據(jù)源讀寫(xiě)分離配置查詢時(shí)的負(fù)載均衡算法,目前有種算法,輪詢和隨機(jī),算法接口是。 在我《Spring Cloud微服務(wù)-全棧技術(shù)與案例解析》書(shū)中,第18章節(jié)分庫(kù)分表解決方案里有對(duì)Sharding-JDBC的使用進(jìn)行詳細(xì)的講解。 之前是通過(guò)XML方式來(lái)配置數(shù)據(jù)源,讀寫(xiě)分離...
摘要:如成千上萬(wàn)的螞蟻完成一項(xiàng)搬運(yùn)工作縱向擴(kuò)展又叫垂直擴(kuò)展,擴(kuò)展一個(gè)點(diǎn)的能力支撐更大的請(qǐng)求。如利用個(gè)人的能力,如蜘蛛俠逼?;疖?chē)參考分庫(kù)分表的基本思想 一 概念:什么是分庫(kù)分表(sharding) 1 將集中于單一節(jié)點(diǎn)的數(shù)據(jù)拆分并分別存儲(chǔ)到多個(gè)數(shù)據(jù)庫(kù)或表,稱為分庫(kù)分表 2 數(shù)據(jù)切分分為兩種方式,垂直切分和水平切分 3 分庫(kù):因?yàn)楸矶鄬?dǎo)致數(shù)據(jù)過(guò)多使用垂直切分,垂直切分就是根據(jù)業(yè)務(wù)的耦合性,將關(guān)聯(lián)...
閱讀 2047·2021-11-22 09:34
閱讀 3391·2021-09-28 09:35
閱讀 13922·2021-09-09 11:34
閱讀 3696·2019-08-29 16:25
閱讀 2899·2019-08-29 15:23
閱讀 2103·2019-08-28 17:55
閱讀 2501·2019-08-26 17:04
閱讀 3102·2019-08-26 12:21