Sharding-Jdbc 實(shí)現(xiàn)讀寫(xiě)分離 + 分庫(kù)分表，寫(xiě)得太好了！

1、概覽

ShardingSphere-Jdbc定位為輕量級(jí)Java框架，在Java的Jdbc層提供的額外服務(wù)。它使用客戶(hù)端直連數(shù)據(jù)庫(kù)，以jar包形式提供服務(wù)，可理解為增強(qiáng)版的Jdbc驅(qū)動(dòng)，完全兼容Jdbc和各種ORM框架

2、MySQL主從復(fù)制

1）、docker配置mysql主從復(fù)制

1）創(chuàng)建主服務(wù)器所需目錄

mkdir?-p?/usr/local/mysqlData/master/cnf
mkdir?-p?/usr/local/mysqlData/master/data

2）定義主服務(wù)器配置文件

vim?/usr/local/mysqlData/master/cnf/mysql.cnf
[mysqld]
##?設(shè)置server_id,注意要唯一
server-id=1
##?開(kāi)啟binlog
log-bin=mysql-bin
##?binlog緩存
binlog_cache_size=1M
##?binlog格式(mixed、statement、row,默認(rèn)格式是statement)
binlog_format=mixed

3）創(chuàng)建并啟動(dòng)mysql主服務(wù)

docker?run?-itd?-p?3306:3306?--name?master?-v?/usr/local/mysqlData/master/cnf:/etc/mysql/conf.d?-v?/usr/local/mysqlData/master/data:/var/lib/mysql?-e?MYSQL_ROOT_PASSWORD=123456?mysql:5.7

4）添加復(fù)制master數(shù)據(jù)的用戶(hù)reader，供從服務(wù)器使用

[root@aliyun?/]#?docker?ps
CONTAINER?ID????????IMAGE???????????????COMMAND??????????????????CREATED?????????????STATUS??????????????PORTS???????????????????????????????NAMES
6af1df686fff????????mysql:5.7???????????"docker-entrypoint..."???5?seconds?ago???????Up?4?seconds????????0.0.0.0:3306->3306/tcp,?33060/tcp???master
[root@aliyun?/]#?docker?exec?-it?master?/bin/bash
root@41d795785db1:/#?mysql?-u?root?-p123456

mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?to?'reader'@'%'?identified?by?'reader';
Query?OK,?0?rows?affected,?1?warning?(0.00?sec)

mysql>?FLUSH?PRIVILEGES;
Query?OK,?0?rows?affected?(0.00?sec)

5）創(chuàng)建從服務(wù)器所需目錄，編輯配置文件

mkdir?/usr/local/mysqlData/slave/cnf?-p
mkdir?/usr/local/mysqlData/slave/cnf?-p
vim?/usr/local/mysqlData/slave/cnf/mysql.cnf
[mysqld]
##?設(shè)置server_id,注意要唯一
server-id=2
##?開(kāi)啟binlog,以備Slave作為其它Slave的Master時(shí)使用
log-bin=mysql-slave-bin
##?relay_log配置中繼日志
relay_log=edu-mysql-relay-bin
##?如果需要同步函數(shù)或者存儲(chǔ)過(guò)程
log_bin_trust_function_creators=true
##?binlog緩存
binlog_cache_size=1M
##?binlog格式(mixed、statement、row,默認(rèn)格式是statement)
binlog_format=mixed
##?跳過(guò)主從復(fù)制中遇到的所有錯(cuò)誤或指定類(lèi)型的錯(cuò)誤,避免slave端復(fù)制中斷
##?如:1062錯(cuò)誤是指一些主鍵重復(fù),1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫(kù)數(shù)據(jù)不一致
slave_skip_errors=1062

6）創(chuàng)建并運(yùn)行mysql從服務(wù)器

docker?run?-itd?-p?3307:3306?--name?slaver?-v?/usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d?-v?/usr/local/mysqlData/slave/data:/var/lib/mysql?-e?MYSQL_ROOT_PASSWORD=123456?mysql:5.7

7）在從服務(wù)器上配置連接主服務(wù)器的信息

首先主服務(wù)器上查看master_log_file、master_log_pos兩個(gè)參數(shù)，然后切換到從服務(wù)器上進(jìn)行主服務(wù)器的連接信息的設(shè)置。

另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺(tái)發(fā)送：面試，可以在線(xiàn)閱讀。

主服務(wù)上執(zhí)行：

root@6af1df686fff:/#?mysql?-u?root?-p123456

mysql>?show?master?status;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000003?|??????591?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set?(0.00?sec)

docker查看主服務(wù)器容器的ip地址

[root@aliyun?/]#?docker?inspect?--format='{{.NetworkSettings.IPAddress}}'?master
172.17.0.2

從服務(wù)器上執(zhí)行：

[root@aliyun?/]#?docker?exec?-it?slaver?/bin/bash
root@fe8b6fc2f1ca:/#?mysql?-u?root?-p123456??

mysql>?change?master?to?master_host='172.17.0.2',master_user='reader',master_password='reader',master_log_file='mysql-bin.000003',master_log_pos=591;

8）從服務(wù)器啟動(dòng)I/O 線(xiàn)程和SQL線(xiàn)程

mysql>?start?slave;
Query?OK,?0?rows?affected,?1?warning?(0.00?sec)

mysql>?show?slave?status\G
***************************?1.?row?***************************
???????????????Slave_IO_State:?Waiting?for?master?to?send?event
??????????????????Master_Host:?172.17.0.2
??????????????????Master_User:?reader
??????????????????Master_Port:?3306
????????????????Connect_Retry:?60
??????????????Master_Log_File:?mysql-bin.000003
??????????Read_Master_Log_Pos:?591
???????????????Relay_Log_File:?edu-mysql-relay-bin.000002
????????????????Relay_Log_Pos:?320
????????Relay_Master_Log_File:?mysql-bin.000003
?????????????Slave_IO_Running:?Yes
????????????Slave_SQL_Running:?Yes

Slave_IO_Running: Yes，Slave_SQL_Running: Yes即表示啟動(dòng)成功

2）、binlog和redo log回顧

1）redo log（重做日志）

InnoDB首先將redo log放入到redo log buffer，然后按一定頻率將其刷新到redo log file

下列三種情況下會(huì)將redo log buffer刷新到redo log file：

• Master Thread每一秒將redo log buffer刷新到redo log file
• 每個(gè)事務(wù)提交時(shí)會(huì)將redo log buffer刷新到redo log file
• 當(dāng)redo log緩沖池剩余空間小于1/2時(shí)，會(huì)將redo log buffer刷新到redo log file

MySQL里常說(shuō)的WAL技術(shù)，全稱(chēng)是Write Ahead Log，即當(dāng)事務(wù)提交時(shí)，先寫(xiě)redo log，再修改頁(yè)。也就是說(shuō)，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB會(huì)先把記錄寫(xiě)到redo log里面，并更新Buffer Pool的page，這個(gè)時(shí)候更新操作就算完成了

Buffer Pool是物理頁(yè)的緩存，對(duì)InnoDB的任何修改操作都會(huì)首先在Buffer Pool的page上進(jìn)行，然后這樣的頁(yè)將被標(biāo)記為臟頁(yè)并被放到專(zhuān)門(mén)的Flush List上，后續(xù)將由專(zhuān)門(mén)的刷臟線(xiàn)程階段性的將這些頁(yè)面寫(xiě)入磁盤(pán)

InnoDB的redo log是固定大小的，比如可以配置為一組4個(gè)文件，每個(gè)文件的大小是1GB，循環(huán)使用，從頭開(kāi)始寫(xiě)，寫(xiě)到末尾就又回到開(kāi)頭循環(huán)寫(xiě)（順序?qū)?，?jié)省了隨機(jī)寫(xiě)磁盤(pán)的IO消耗）

Write Pos是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移，寫(xiě)到第3號(hào)文件末尾后就回到0號(hào)文件開(kāi)頭。Check Point是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件

Write Pos和Check Point之間空著的部分，可以用來(lái)記錄新的操作。如果Write Pos追上Check Point，這時(shí)候不能再執(zhí)行新的更新，需要停下來(lái)擦掉一些記錄，把Check Point推進(jìn)一下

當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，數(shù)據(jù)庫(kù)不需要重做所有的日志，因?yàn)镃heck Point之前的頁(yè)都已經(jīng)刷新回磁盤(pán)，只需對(duì)Check Point后的redo log進(jìn)行恢復(fù)，從而縮短了恢復(fù)的時(shí)間

當(dāng)緩沖池不夠用時(shí)，根據(jù)LRU算法會(huì)溢出最近最少使用的頁(yè)，若此頁(yè)為臟頁(yè)，那么需要強(qiáng)制執(zhí)行Check Point，將臟頁(yè)刷新回磁盤(pán)。最新面試題整理好了，點(diǎn)擊Java面試庫(kù)小程序在線(xiàn)刷題。

2）binlog（歸檔日志）

MySQL整體來(lái)看就有兩塊：一塊是Server層，主要做的是MySQL功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。redo log是InnoDB引擎特有的日志，而Server層也有自己的日志，稱(chēng)為binlog

binlog記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作，不包括SELECT和SHOW這類(lèi)操作，主要作用是用于數(shù)據(jù)庫(kù)的主從復(fù)制及數(shù)據(jù)的增量恢復(fù)

使用mysqldump備份時(shí)，只是對(duì)一段時(shí)間的數(shù)據(jù)進(jìn)行全備，但是如果備份后突然發(fā)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器故障，這個(gè)時(shí)候就要用到binlog的日志了

binlog格式有三種：STATEMENT，ROW，MIXED

• STATEMENT模式：binlog里面記錄的就是SQL語(yǔ)句的原文。優(yōu)點(diǎn)是并不需要記錄每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點(diǎn)是在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致
• ROW模式：不記錄每條SQL語(yǔ)句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了，解決了STATEMENT模式下出現(xiàn)master-slave中的數(shù)據(jù)不一致。缺點(diǎn)是會(huì)產(chǎn)生大量的日志，尤其是alter table的時(shí)候會(huì)讓日志暴漲
• MIXED模式：以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式

3）redo log和binlog日志的不同

• redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用
• redo log是物理日志，記錄的是在某個(gè)數(shù)據(jù)也上做了什么修改；binlog是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如給ID=2這一行的c字段加1
• redo log是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog是可以追加寫(xiě)入的，binlog文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志

4）兩階段提交

create?table?T(ID?int?primary?key,?c?int);
update?T?set?c=c+1?where?ID=2;

執(zhí)行器和InnoDB引擎在執(zhí)行這個(gè)update語(yǔ)句時(shí)的內(nèi)部流程：

• 執(zhí)行器先找到引擎取ID=2這一行。ID是主鍵，引擎直接用樹(shù)搜索找到這一行。如果ID=2這一行所在的數(shù)據(jù)也本來(lái)就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤(pán)讀入內(nèi)存，然后再返回
• 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫(xiě)入這行新數(shù)據(jù)
• 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到redo log里面，此時(shí)redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)
• 執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫(xiě)入磁盤(pán)
• 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫(xiě)入的redo log改成提交狀態(tài)，更新完成

update語(yǔ)句的執(zhí)行流程圖如下，圖中淺色框表示在InnoDB內(nèi)部執(zhí)行的，深色框表示是在執(zhí)行器中執(zhí)行的

將redo log的寫(xiě)入拆成了兩個(gè)步驟：prepare和commit，這就是兩階段提交

3）、MySQL主從復(fù)制原理

從庫(kù)B和主庫(kù)A之間維持了一個(gè)長(zhǎng)連接。主庫(kù)A內(nèi)部有一個(gè)線(xiàn)程，專(zhuān)門(mén)用于服務(wù)從庫(kù)B的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過(guò)程如下：

• 在從庫(kù)B上通過(guò)change master命令，設(shè)置主庫(kù)A的IP、端口、用戶(hù)名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求binlog，這個(gè)位置包含文件名和日志偏移量
• 在從庫(kù)B上執(zhí)行start slave命令，這時(shí)從庫(kù)會(huì)啟動(dòng)兩個(gè)線(xiàn)程，就是圖中的I/O線(xiàn)程和SQL線(xiàn)程。其中I/O線(xiàn)程負(fù)責(zé)與主庫(kù)建立連接
• 主庫(kù)A校驗(yàn)完用戶(hù)名、密碼后，開(kāi)始按照從庫(kù)B傳過(guò)來(lái)的位置，從本地讀取binlog，發(fā)給B
• 從庫(kù)B拿到binlog后，寫(xiě)到本地文件，稱(chēng)為中繼日志
• SQL線(xiàn)程讀取中繼日志，解析出日志里的命令，并執(zhí)行

由于多線(xiàn)程復(fù)制方案的引入，SQL線(xiàn)程演化成了多個(gè)線(xiàn)程

主從復(fù)制不是完全實(shí)時(shí)地進(jìn)行同步，而是異步實(shí)時(shí)。這中間存在主從服務(wù)之間的執(zhí)行延時(shí)，如果主服務(wù)器的壓力很大，則可能導(dǎo)致主從服務(wù)器延時(shí)較大。

點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)

3、Sharding-Jdbc實(shí)現(xiàn)讀寫(xiě)分離

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">1）、新建Springboot工程，引入相關(guān)依賴(lài)，Spring Boot 基礎(chǔ)就不介紹了，推薦下這個(gè)實(shí)戰(zhàn)教程：https://www.javastack.cn/categories/Spring-Boot/

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" data-linktype="2">
????
????????org.springframework.boot
????????spring-boot-starter-web
????
????
????????org.mybatis.spring.boot
????????mybatis-spring-boot-starter
????????2.1.4
????
????
????????mysql
????????mysql-connector-java
????????runtime
????
????
????????com.alibaba
????????druid-spring-boot-starter
????????1.1.21
????
????
????????org.apache.shardingsphere
????????sharding-jdbc-spring-boot-starter
????????4.0.0-RC1
????
????
????????org.projectlombok
????????lombok
????????true
????
????
????????org.springframework.boot
????????spring-boot-starter-test
????????test
????

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">2）、application.properties配置文件

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" data-linktype="2">spring.main.allow-bean-definition-overriding=true
#顯示sql
spring.shardingsphere.props.sql.show=true

#配置數(shù)據(jù)源
spring.shardingsphere.datasource.names=ds1,ds2,ds3

#master-ds1數(shù)據(jù)庫(kù)連接信息
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.datasource.ds1.maxPoolSize=100
spring.shardingsphere.datasource.ds1.minPoolSize=5

#slave-ds2數(shù)據(jù)庫(kù)連接信息
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456
spring.shardingsphere.datasource.ds2.maxPoolSize=100
spring.shardingsphere.datasource.ds2.minPoolSize=5

#slave-ds3數(shù)據(jù)庫(kù)連接信息
spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds3.username=root
spring.shardingsphere.datasource.ds3.password=123456
spring.shardingsphere.datasource.ds.maxPoolSize=100
spring.shardingsphere.datasource.ds3.minPoolSize=5

#配置默認(rèn)數(shù)據(jù)源ds1?默認(rèn)數(shù)據(jù)源,主要用于寫(xiě)
spring.shardingsphere.sharding.default-data-source-name=ds1
#配置主從名稱(chēng)
spring.shardingsphere.masterslave.name=ms
#置主庫(kù)master,負(fù)責(zé)數(shù)據(jù)的寫(xiě)入
spring.shardingsphere.masterslave.master-data-source-name=ds1
#配置從庫(kù)slave節(jié)點(diǎn)
spring.shardingsphere.masterslave.slave-data-source-names=ds2,ds3
#配置slave節(jié)點(diǎn)的負(fù)載均衡均衡策略,采用輪詢(xún)機(jī)制
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

#整合mybatis的配置
mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">3）、創(chuàng)建t_user表

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" data-linktype="2">CREATE?TABLE?`t_user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`nickname`?varchar(100)?DEFAULT?NULL,
??`password`?varchar(100)?DEFAULT?NULL,
??`sex`?int(11)?DEFAULT?NULL,
??`birthday`?varchar(50)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb4;

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">4）、定義Controller、Mapper、Entity

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" data-linktype="2">@Data
public?class?User?{
????private?Integer?id;

????private?String?nickname;

????private?String?password;

????private?Integer?sex;

????private?String?birthday;
}
@RestController
@RequestMapping("/api/user")
public?class?UserController?{
????@Autowired
????private?UserMapper?userMapper;

????@PostMapping("/save")
????public?String?addUser()?{
????????User?user?=?new?User();
????????user.setNickname("zhangsan"?+?new?Random().nextInt());
????????user.setPassword("123456");
????????user.setSex(1);
????????user.setBirthday("1997-12-03");
????????userMapper.addUser(user);
????????return?"success";
????}

????@GetMapping("/findUsers")
????public?List?findUsers()?{
????????return?userMapper.findUsers();
????}
}
public?interface?UserMapper?{

????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")
????void?addUser(User?user);

????@Select("select?*?from?t_user")
????List?findUsers();
}

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">5）、驗(yàn)證

?findUsers()?{????????return?userMapper.findUsers();????}}public?interface?UserMapper?{????@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")????void?addUser(User?user);????@Select("select?*?from?t_user")????List?findUsers();}5）、驗(yàn)證啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功："" data-itemshowtype="0" tab="innerlink" style="color: rgb(58, 58, 58);" data-linktype="2">啟動(dòng)日志中三個(gè)數(shù)據(jù)源初始化成功：

調(diào)用http://localhost:8080/api/user/save一直進(jìn)入到ds1主節(jié)點(diǎn)

調(diào)用http://localhost:8080/api/user/findUsers一直進(jìn)入到ds2、ds3節(jié)點(diǎn)，并且輪詢(xún)進(jìn)入

4、MySQL分庫(kù)分表原理

1）、分庫(kù)分表

水平拆分：同一個(gè)表的數(shù)據(jù)拆到不同的庫(kù)不同的表中。可以根據(jù)時(shí)間、地區(qū)或某個(gè)業(yè)務(wù)鍵維度，也可以通過(guò)hash進(jìn)行拆分，最后通過(guò)路由訪(fǎng)問(wèn)到具體的數(shù)據(jù)。拆分后的每個(gè)表結(jié)構(gòu)保持一致

垂直拆分：就是把一個(gè)有很多字段的表給拆分成多個(gè)表，或者是多個(gè)庫(kù)上去。每個(gè)庫(kù)表的結(jié)構(gòu)都不一樣，每個(gè)庫(kù)表都包含部分字段。一般來(lái)說(shuō)，可以根據(jù)業(yè)務(wù)維度進(jìn)行拆分，如訂單表可以拆分為訂單、訂單支持、訂單地址、訂單商品、訂單擴(kuò)展等表；也可以，根據(jù)數(shù)據(jù)冷熱程度拆分，20%的熱點(diǎn)字段拆到一個(gè)表，80%的冷字段拆到另外一個(gè)表。

最新分庫(kù)分表面試題整理好了，點(diǎn)擊Java面試庫(kù)小程序在線(xiàn)刷題。

點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)

2）、不停機(jī)分庫(kù)分表數(shù)據(jù)遷移

一般數(shù)據(jù)庫(kù)的拆分也是有一個(gè)過(guò)程的，一開(kāi)始是單表，后面慢慢拆成多表。那么我們就看下如何平滑的從MySQL單表過(guò)度到MySQL的分庫(kù)分表架構(gòu)

• 利用MySQL+Canal做增量數(shù)據(jù)同步，利用分庫(kù)分表中間件，將數(shù)據(jù)路由到對(duì)應(yīng)的新表中
• 利用分庫(kù)分表中間件，全量數(shù)據(jù)導(dǎo)入到對(duì)應(yīng)的新表中
• 通過(guò)單表數(shù)據(jù)和分庫(kù)分表數(shù)據(jù)兩兩比較，更新不匹配的數(shù)據(jù)到新表中
• 數(shù)據(jù)穩(wěn)定后，將單表的配置切換到分庫(kù)分表配置上

5、Sharding-Jdbc實(shí)現(xiàn)分庫(kù)分表

1）、邏輯表

用戶(hù)數(shù)據(jù)根據(jù)訂單id%2拆分為2個(gè)表，分別是：t_order0和t_order1。他們的邏輯表名是：t_order

多數(shù)據(jù)源相同表：

#多數(shù)據(jù)源$->{0..N}.邏輯表名$->{0..N}?相同表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}

多數(shù)據(jù)源不同表：

#多數(shù)據(jù)源$->{0..N}.邏輯表名$->{0..N}?不同表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->{0..1},ds1.t_order$->{2..4}

單庫(kù)分表：

#單數(shù)據(jù)源的配置方式
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->{0..4}

全部手動(dòng)指定：

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order0,ds1.t_order0,ds0.t_order1,ds1.t_order1

2）、inline分片策略

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
#數(shù)據(jù)源分片策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#數(shù)據(jù)源分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2}
#表分片策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#表分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id%2}

上面的配置通過(guò)user_id%2來(lái)決定具體數(shù)據(jù)源，通過(guò)order_id%2來(lái)決定具體表

insert into t_order(user_id,order_id) values(2,3)，user_id%2 = 0使用數(shù)據(jù)源ds0，order_id%2 = 1使用t_order1，insert語(yǔ)句最終操作的是數(shù)據(jù)源ds0的t_order1表。

3）、分布式主鍵配置

Sharding-Jdbc可以配置分布式主鍵生成策略。默認(rèn)使用雪花算法（snowflake），生成64bit的長(zhǎng)整型數(shù)據(jù)，也支持UUID的方式

#主鍵的列名
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
#主鍵生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

4）、inline分片策略實(shí)現(xiàn)分庫(kù)分表

需求：

對(duì)1000w的用戶(hù)數(shù)據(jù)進(jìn)行分庫(kù)分表，對(duì)用戶(hù)表的數(shù)據(jù)進(jìn)行分表和分庫(kù)的操作。根據(jù)年齡奇數(shù)存儲(chǔ)在t_user1，偶數(shù)t_user0，同時(shí)性別奇數(shù)存儲(chǔ)在ds1，偶數(shù)ds0。

另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺(tái)發(fā)送：面試，可以在線(xiàn)閱讀。

表結(jié)構(gòu)：

CREATE?TABLE?`t_user0`?(
??`id`?bigint(20)?DEFAULT?NULL,
??`nickname`?varchar(200)?DEFAULT?NULL,
??`password`?varchar(200)?DEFAULT?NULL,
??`age`?int(11)?DEFAULT?NULL,
??`sex`?int(11)?DEFAULT?NULL,
??`birthday`?varchar(100)?DEFAULT?NULL
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4;

CREATE?TABLE?`t_user1`?(
??`id`?bigint(20)?DEFAULT?NULL,
??`nickname`?varchar(200)?DEFAULT?NULL,
??`password`?varchar(200)?DEFAULT?NULL,
??`age`?int(11)?DEFAULT?NULL,
??`sex`?int(11)?DEFAULT?NULL,
??`birthday`?varchar(100)?DEFAULT?NULL
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4;

兩個(gè)數(shù)據(jù)庫(kù)中都包含t_user0和t_user1兩張表。Spring Boot 基礎(chǔ)就不介紹了，推薦下這個(gè)實(shí)戰(zhàn)教程：https://www.javastack.cn/categories/Spring-Boot/

application.properties：

spring.main.allow-bean-definition-overriding=true
#顯示sql
spring.shardingsphere.props.sql.show=true

#配置數(shù)據(jù)源
spring.shardingsphere.datasource.names=ds0,ds1

#ds0數(shù)據(jù)庫(kù)連接信息
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://47.101.58.187:3306/t_user_db0?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds0.maxPoolSize=100
spring.shardingsphere.datasource.ds0.minPoolSize=5

#ds1數(shù)據(jù)庫(kù)連接信息
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/t_user_db1?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.datasource.ds1.maxPoolSize=100
spring.shardingsphere.datasource.ds1.minPoolSize=5

#整合mybatis的配置
mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity

spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user$->{0..1}
#數(shù)據(jù)源分片策略
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=sex
#數(shù)據(jù)源分片算法
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds$->{sex%2}
#表分片策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=age
#表分片算法
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{age%2}
#主鍵的列名
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

測(cè)試類(lèi)：

@SpringBootTest
class?ShardingJdbcApplicationTests?{

????@Autowired
????private?UserMapper?userMapper;

????/**
?????*?sex:奇數(shù)
?????*?age:奇數(shù)
?????*?ds1.t_user1
?????*/
????@Test
????public?void?test01()?{
????????User?user?=?new?User();
????????user.setNickname("zhangsan"?+?new?Random().nextInt());
????????user.setPassword("123456");
????????user.setAge(17);
????????user.setSex(1);
????????user.setBirthday("1997-12-03");
????????userMapper.addUser(user);
????}

????/**
?????*?sex:奇數(shù)
?????*?age:偶數(shù)
?????*?ds1.t_user0
?????*/
????@Test
????public?void?test02()?{
????????User?user?=?new?User();
????????user.setNickname("zhangsan"?+?new?Random().nextInt());
????????user.setPassword("123456");
????????user.setAge(18);
????????user.setSex(1);
????????user.setBirthday("1997-12-03");
????????userMapper.addUser(user);
????}

????/**
?????*?sex:偶數(shù)
?????*?age:奇數(shù)
?????*?ds0.t_user1
?????*/
????@Test
????public?void?test03()?{
????????User?user?=?new?User();
????????user.setNickname("zhangsan"?+?new?Random().nextInt());
????????user.setPassword("123456");
????????user.setAge(17);
????????user.setSex(2);
????????user.setBirthday("1997-12-03");
????????userMapper.addUser(user);
????}

????/**
?????*?sex:偶數(shù)
?????*?age:偶數(shù)
?????*?ds0.t_user0
?????*/
????@Test
????public?void?test04()?{
????????User?user?=?new?User();
????????user.setNickname("zhangsan"?+?new?Random().nextInt());
????????user.setPassword("123456");
????????user.setAge(18);
????????user.setSex(2);
????????user.setBirthday("1997-12-03");
????????userMapper.addUser(user);
????}
}

參考文檔：
https://shardingsphere.apache.org/document/current/cn/overview/
https://www.bilibili.com/video/BV1ei4y1K7dn

原文鏈接：https://blog.csdn.net/qq_40378034/article/details/115264837

版權(quán)聲明：本文為CSDN博主「邋遢的流浪劍客」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。

獲取 Spring Boot 實(shí)戰(zhàn)筆記！