SpringBoot + Sharding JDBC，一文搞定分庫分表、讀寫分離

Sharding-JDBC最早是當當網(wǎng)內(nèi)部使用的一款分庫分表框架，到2017年的時候才開始對外開源，這幾年在大量社區(qū)貢獻者的不斷迭代下，功能也逐漸完善，現(xiàn)已更名為ShardingSphere，2020年4?16日正式成為 Apache 軟件基金會的頂級項目。

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

隨著版本的不斷更迭 ShardingSphere 的核心功能也變得多元化起來。

從最開始 Sharding-JDBC 1.0 版本只有數(shù)據(jù)分片，到 Sharding-JDBC 2.0 版本開始支持數(shù)據(jù)庫治理（注冊中心、配置中心等等），再到 Sharding-JDBC 3.0版本又加分布式事務(wù) （支持 Atomikos、Narayana、Bitronix、Seata），如今已經(jīng)迭代到了 Sharding-JDBC 4.0 版本。

現(xiàn)在的 ShardingSphere 不單單是指某個框架而是一個生態(tài)圈，這個生態(tài)圈Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar這三款開源的分布式數(shù)據(jù)庫中間件解決方案所構(gòu)成。

ShardingSphere 的前身就是 Sharding-JDBC，所以它是整個框架中最為經(jīng)典、成熟的組件，先從 Sharding-JDBC 框架入手學習分庫分表。

1核心概念

分庫分表

分庫，顯而易見，就是一個數(shù)據(jù)庫分成多個數(shù)據(jù)庫，部署到不同機器。

分表，就是一個數(shù)據(jù)庫表分成多個表。

分片

一般在提到分庫分表的時候，大多是以水平切分模式（水平分庫、分表）為基礎(chǔ)來說的，數(shù)據(jù)分片將原本一張數(shù)據(jù)量較大的表例如 t_order 拆分生成數(shù)個表結(jié)構(gòu)完全一致的小數(shù)據(jù)量表 t_order_0、t_order_1、···、t_order_n，每張表只存儲原大表中的一部分數(shù)據(jù)，當執(zhí)行一條SQL時會通過分庫策略、分片策略將數(shù)據(jù)分散到不同的數(shù)據(jù)庫、表內(nèi)。

數(shù)據(jù)節(jié)點

數(shù)據(jù)節(jié)點是分庫分表中一個不可再分的最小數(shù)據(jù)單元（表），它由數(shù)據(jù)源名稱和數(shù)據(jù)表組成，例如上圖中 order_db_1.t_order_0、order_db_2.t_order_1 就表示一個數(shù)據(jù)節(jié)點。

邏輯表

邏輯表是指一組具有相同邏輯和數(shù)據(jù)結(jié)構(gòu)表的總稱。

比如將訂單表 t_order 拆分成 t_order_0 ··· t_order_9 等 10張表。

此時會發(fā)現(xiàn)分庫分表以后數(shù)據(jù)庫中已不在有 t_order 這張表，取而代之的是 t_order_n，但在代碼中寫 SQL 依然按 t_order 來寫。此時 t_order 就是這些拆分表的邏輯表。

真實表

真實表也就是上邊提到的 t_order_n 數(shù)據(jù)庫中真實存在的物理表。

分片鍵

用于分片的數(shù)據(jù)庫字段。將 t_order 表分片以后，當執(zhí)行一條SQL時，通過對字段 order_id 取模的方式來決定，這條數(shù)據(jù)該在哪個數(shù)據(jù)庫中的哪個表中執(zhí)行，此時 order_id 字段就是 t_order 表的分片健。

這樣以來同一個訂單的相關(guān)數(shù)據(jù)就會存在同一個數(shù)據(jù)庫表中，大幅提升數(shù)據(jù)檢索的性能，不僅如此 sharding-jdbc 還支持根據(jù)多個字段作為分片健進行分片。

分片算法

上邊提到可以用分片健取模的規(guī)則分片，但這只是比較簡單的一種，在實際開發(fā)中還希望用 >=、<=、>、<、BETWEEN 和 IN 等條件作為分片規(guī)則，自定義分片邏輯，這時就需要用到分片策略與分片算法。

從執(zhí)行 SQL 的角度來看，分庫分表可以看作是一種路由機制，把 SQL 語句路由到期望的數(shù)據(jù)庫或數(shù)據(jù)表中并獲取數(shù)據(jù)，分片算法可以理解成一種路由規(guī)則。

咱們先捋一下它們之間的關(guān)系，分片策略只是抽象出的概念，它是由分片算法和分片健組合而成，分片算法做具體的數(shù)據(jù)分片邏輯。

分庫、分表的分片策略配置是相對獨立的，可以各自使用不同的策略與算法，每種策略中可以是多個分片算法的組合，每個分片算法可以對多個分片健做邏輯判斷。

分片算法和分片策略的關(guān)系

注意：sharding-jdbc 并沒有直接提供分片算法的實現(xiàn)，需要開發(fā)者根據(jù)業(yè)務(wù)自行實現(xiàn)。

sharding-jdbc 提供了4種分片算法。

1、精確分片算法

精確分片算法（PreciseShardingAlgorithm）用于單個字段作為分片鍵，SQL中有 = 與 IN 等條件的分片，需要在標準分片策略（StandardShardingStrategy ）下使用。

2、范圍分片算法

范圍分片算法（RangeShardingAlgorithm）用于單個字段作為分片鍵，SQL中有 BETWEEN AND、>、<、>=、<= ?等條件的分片，需要在標準分片策略（StandardShardingStrategy ）下使用。

3、復(fù)合分片算法

復(fù)合分片算法（ComplexKeysShardingAlgorithm）用于多個字段作為分片鍵的分片操作，同時獲取到多個分片健的值，根據(jù)多個字段處理業(yè)務(wù)邏輯。需要在復(fù)合分片策略（ComplexShardingStrategy ）下使用。

4、Hint分片算法

Hint分片算法（HintShardingAlgorithm）稍有不同，上邊的算法中都是解析SQL 語句提取分片鍵，并設(shè)置分片策略進行分片。但有些時候并沒有使用任何的分片鍵和分片策略，可還想將 SQL 路由到目標數(shù)據(jù)庫和表，就需要通過手動干預(yù)指定SQL的目標數(shù)據(jù)庫和表信息，這也叫強制路由。

分片策略

上邊講分片算法的時候已經(jīng)說過，分片策略是一種抽象的概念，實際分片操作的是由分片算法和分片健來完成的。

1、標準分片策略

標準分片策略適用于單分片鍵，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 兩個分片算法。

其中 PreciseShardingAlgorithm 是必選的，用于處理 = 和 IN 的分片。RangeShardingAlgorithm 是可選的，用于處理BETWEEN AND， >， <，>=，<= 條件分片，如果不配置RangeShardingAlgorithm，SQL中的條件等將按照全庫路由處理。

2、復(fù)合分片策略

復(fù)合分片策略，同樣支持對 SQL語句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片鍵，具體分配片細節(jié)完全由應(yīng)用開發(fā)者實現(xiàn)。

3、行表達式分片策略

行表達式分片策略，支持對 SQL語句中的 = 和 IN 的分片操作，但只支持單分片鍵。這種策略通常用于簡單的分片，不需要自定義分片算法，可以直接在配置文件中接著寫規(guī)則。

t_order_$->{t_order_id % 4} 代表 t_order 對其字段 t_order_id取模，拆分成4張表，而表名分別是t_order_0 到 t_order_3。

4、Hint分片策略

Hint分片策略，對應(yīng)上邊的Hint分片算法，通過指定分片健而非從 SQL中提取分片健的方式進行分片的策略。

分布式主鍵

數(shù)據(jù)分?后，不同數(shù)據(jù)節(jié)點?成全局唯?主鍵是?常棘?的問題，同?個邏輯表（t_order）內(nèi)的不同真實表（t_order_n）之間的?增鍵由于?法互相感知而產(chǎn)?重復(fù)主鍵。

盡管可通過設(shè)置?增主鍵 初始值 和 步? 的?式避免ID碰撞，但這樣會使維護成本加大，乏完整性和可擴展性。如果后去需要增加分片表的數(shù)量，要逐一修改分片表的步長，運維成本非常高，所以不建議這種方式。

為了讓上手更加簡單，ApacheShardingSphere 內(nèi)置了UUID、SNOWFLAKE 兩種分布式主鍵?成器，默認使用雪花算法（snowflake）?成64bit的?整型數(shù)據(jù)。不僅如此它還抽離出分布式主鍵?成器的接口，方便實現(xiàn)自定義的自增主鍵生成算法。

廣播表

廣播表：存在于所有的分片數(shù)據(jù)源中的表，表結(jié)構(gòu)和表中的數(shù)據(jù)在每個數(shù)據(jù)庫中均完全一致。一般是為字典表或者配置表 t_config，某個表一旦被配置為廣播表，只要修改某個數(shù)據(jù)庫的廣播表，所有數(shù)據(jù)源中廣播表的數(shù)據(jù)都會跟著同步。

綁定表

綁定表：那些分片規(guī)則一致的主表和子表。比如：t_order 訂單表和 t_order_item 訂單服務(wù)項目表，都是按 order_id 字段分片，因此兩張表互為綁定表關(guān)系。

那綁定表存在的意義是啥呢？

通常在業(yè)務(wù)中都會使用 t_order 和 t_order_item 等表進行多表聯(lián)合查詢，但由于分庫分表以后這些表被拆分成N多個子表。如果不配置綁定表關(guān)系，會出現(xiàn)笛卡爾積關(guān)聯(lián)查詢，將產(chǎn)生如下四條SQL。

SELECT*FROMt_order_0oJOINt_order_item_0iONo.order_id=i.order_id
SELECT*FROMt_order_0oJOINt_order_item_1iONo.order_id=i.order_id
SELECT*FROMt_order_1oJOINt_order_item_0iONo.order_id=i.order_id
SELECT*FROMt_order_1oJOINt_order_item_1iONo.order_id=i.order_id

而配置綁定表關(guān)系后再進行關(guān)聯(lián)查詢時，只要對應(yīng)表分片規(guī)則一致產(chǎn)生的數(shù)據(jù)就會落到同一個庫中，那么只需 t_order_0 和 t_order_item_0 表關(guān)聯(lián)即可。

SELECT*FROMt_order_0oJOINt_order_item_0iONo.order_id=i.order_id
SELECT*FROMt_order_1oJOINt_order_item_1iONo.order_id=i.order_id

注意：在關(guān)聯(lián)查詢時 t_order 它作為整個聯(lián)合查詢的主表。所有相關(guān)的路由計算都只使用主表的策略，t_order_item 表的分片相關(guān)的計算也會使用 t_order 的條件，所以要保證綁定表之間的分片鍵要完全相同。

2MySQL主從復(fù)制

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

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

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

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

vim /usr/local/mysqlData/master/cnf/mysql.cnf

[mysqld]
## 設(shè)置server_id,注意要唯一
server-id=1
## 開啟binlog
log-bin=mysql-bin
## binlog緩存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默認格式是statement)
binlog_format=mixed

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

dockerrun-itd-p3306:3306--namemaster-v/usr/local/mysqlData/master/cnf:/etc/mysql/conf.d-v/usr/local/mysqlData/master/data:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=123456mysql:5.7

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

[root@aliyun/]#dockerps
CONTAINERIDIMAGECOMMANDCREATEDSTATUSPORTSNAMES
6af1df686fffmysql:5.7"docker-entrypoint..."5secondsagoUp4seconds0.0.0.0:3306->3306/tcp,33060/tcpmaster
[root@aliyun/]#dockerexec-itmaster/bin/bash
root@41d795785db1:/#mysql-uroot-p123456

mysql>GRANTREPLICATIONSLAVEON*.*to'reader'@'%'identifiedby'reader';
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>FLUSHPRIVILEGES;
QueryOK,0rowsaffected(0.00sec)

創(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
## 開啟binlog,以備Slave作為其它Slave的Master時使用
log-bin=mysql-slave-bin
## relay_log配置中繼日志
relay_log=edu-mysql-relay-bin
## 如果需要同步函數(shù)或者存儲過程
log_bin_trust_function_creators=true
## binlog緩存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默認格式是statement)
binlog_format=mixed
## 跳過主從復(fù)制中遇到的所有錯誤或指定類型的錯誤,避免slave端復(fù)制中斷
## 如:1062錯誤是指一些主鍵重復(fù),1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致
slave_skip_errors=1062

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

dockerrun-itd-p3307:3306--nameslaver-v/usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d-v/usr/local/mysqlData/slave/data:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=123456mysql:5.7

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

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

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

root@6af1df686fff:/#mysql-uroot-p123456

mysql>showmasterstatus;
+------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000003|591||||
+------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

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

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

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

[root@aliyun/]#dockerexec-itslaver/bin/bash
root@fe8b6fc2f1ca:/#mysql-uroot-p123456

mysql>changemastertomaster_host='172.17.0.2',master_user='reader',master_password='reader',master_log_file='mysql-bin.000003',master_log_pos=591;

從服務(wù)器啟動I/O 線程和SQL線程

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

mysql> show slave statusG
*************************** 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 即表示啟動成功

binlog和redo log回顧

redo log（重做日志）

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

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

Master Thread每一秒將redo log buffer刷新到redo log file

每個事務(wù)提交時會將redo log buffer刷新到redo log file

當redo log緩沖池剩余空間小于1/2時，會將redo log buffer刷新到redo log file

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

Buffer Pool是物理頁的緩存，對InnoDB的任何修改操作都會首先在Buffer Pool的page上進行，然后這樣的頁將被標記為臟頁并被放到專門的Flush List上，后續(xù)將由專門的刷臟線程階段性的將這些頁面寫入磁盤

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

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

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

當數(shù)據(jù)庫發(fā)生宕機時，數(shù)據(jù)庫不需要重做所有的日志，因為Check Point之前的頁都已經(jīng)刷新回磁盤，只需對Check Point后的redo log進行恢復(fù)，從而縮短了恢復(fù)的時間

當緩沖池不夠用時，根據(jù)LRU算法會溢出最近最少使用的頁，若此頁為臟頁，那么需要強制執(zhí)行Check Point，將臟頁刷新回磁盤

binlog（歸檔日志）

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

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

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

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

STATEMENT模式：binlog里面記錄的就是SQL語句的原文。優(yōu)點是并不需要記錄每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點是在某些情況下會導(dǎo)致master-slave中的數(shù)據(jù)不一致

ROW模式：不記錄每條SQL語句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了，解決了STATEMENT模式下出現(xiàn)master-slave中的數(shù)據(jù)不一致。缺點是會產(chǎn)生大量的日志，尤其是alter table的時候會讓日志暴漲

MIXED模式：以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式

redo log和binlog日志的不同

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現(xiàn)的，所有引擎都可以使用

redo log是物理日志，記錄的是在某個數(shù)據(jù)也上做了什么修改；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c字段加1

redo log是循環(huán)寫的，空間固定會用完；binlog是可以追加寫入的，binlog文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志

兩階段提交

createtableT(IDintprimarykey,cint);

updateTsetc=c+1whereID=2;

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

執(zhí)行器先找到引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=2這一行所在的數(shù)據(jù)也本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回

執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)

引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到redo log里面，此時redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)

執(zhí)行器生成這個操作的binlog，并把binlog寫入磁盤

執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交狀態(tài)，更新完成

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

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

MySQL主從復(fù)制原理

從庫B和主庫A之間維持了一個長連接。主庫A內(nèi)部有一個線程，專門用于服務(wù)從庫B的這個長連接。一個事務(wù)日志同步的完整過程如下：

在從庫B上通過change master命令，設(shè)置主庫A的IP、端口、用戶名、密碼，以及要從哪個位置開始請求binlog，這個位置包含文件名和日志偏移量

在從庫B上執(zhí)行start slave命令，這時從庫會啟動兩個線程，就是圖中的I/O線程和SQL線程。其中I/O線程負責與主庫建立連接

主庫A校驗完用戶名、密碼后，開始按照從庫B傳過來的位置，從本地讀取binlog，發(fā)給B

從庫B拿到binlog后，寫到本地文件，稱為中繼日志

SQL線程讀取中繼日志，解析出日志里的命令，并執(zhí)行

由于多線程復(fù)制方案的引入，SQL線程演化成了多個線程。

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

3為什么需要分庫分表？

首先要明確一個問題，單一的數(shù)據(jù)庫是否能夠滿足公司目前的線上業(yè)務(wù)需求，比如用戶表，可能有幾千萬，甚至上億的數(shù)據(jù)，只是說可能，如果有這么多用戶，那必然是大公司了，那么這個時候，如果不分表也不分庫的話，那么數(shù)據(jù)了上來的時候，稍微一個不注意，MySQL單機磁盤容量會撐爆，但是如果拆成多個數(shù)據(jù)庫，磁盤使用率大大降低。

這樣就把磁盤使用率降低，這是通過硬件的形式解決問題，如果數(shù)據(jù)量是巨大的，這時候，SQL 如果沒有命中索引，那么就會導(dǎo)致一個情況，查這個表的SQL語句直接把數(shù)據(jù)庫給干崩了。

即使SQL命中了索引，如果表的數(shù)據(jù)量 超過一千萬的話， 查詢也是會明顯變慢的。這是因為索引一般是B+樹結(jié)構(gòu)，數(shù)據(jù)千萬級別的話，B+樹的高度會增高，查詢自然就變慢了，當然，這是題外話了。

4MySQL分庫分表原理

分庫分表

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

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

不停機分庫分表數(shù)據(jù)遷移

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

利用MySQL+Canal做增量數(shù)據(jù)同步，利用分庫分表中間件，將數(shù)據(jù)路由到對應(yīng)的新表中

利用分庫分表中間件，全量數(shù)據(jù)導(dǎo)入到對應(yīng)的新表中

通過單表數(shù)據(jù)和分庫分表數(shù)據(jù)兩兩比較，更新不匹配的數(shù)據(jù)到新表中

數(shù)據(jù)穩(wěn)定后，將單表的配置切換到分庫分表配置上

5分庫分表方案

分庫分表方案，不外乎就兩種，一種是垂直切分，一種是水平切分。

但是總有做開發(fā)的小伙伴不知道這垂直切分和水平切分到底是什么樣的，為什么垂直切分，為什么水平切分，什么時候應(yīng)該選擇垂直切分，什么時候應(yīng)該選擇水平切分。

有人是這么說的，垂直切分是根據(jù)業(yè)務(wù)來拆分數(shù)據(jù)庫，同一類業(yè)務(wù)的數(shù)據(jù)表拆分到一個獨立的數(shù)據(jù)庫，另一類的數(shù)據(jù)表拆分到其他數(shù)據(jù)庫。

有些人不理解這個，實際上垂直切分也是有劃分的，上面描述的是垂直切分數(shù)據(jù)庫，可能容易讓很多人不太理解，但是如果是垂直切分表，那么肯定百分之90的人都能理解。

垂直切分

有一張Order表，表中有諸多記錄，比如設(shè)計這么一張簡單的表。

以上是簡化版Order表，如果想要垂直切分，那么應(yīng)該怎么處理？

直接拆分成2個表，這時候就直接就一分為2 ，咔的一下拆分成兩個表

Order1

Order2

這時候主鍵ID保持的時一致的，而這個操作，就是垂直拆分，分表的操作。

既然說了垂直拆分，那么必然就有水平拆分，

什么是水平拆分呢？

實際上水平拆分的話，那真的是只有一句話。

水平切分

按照數(shù)據(jù)來拆分

水平拆分數(shù)據(jù)庫：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) 分到多個不同的數(shù)據(jù)庫。每個庫的表結(jié)構(gòu)相同，每個庫都只有這張表的部分數(shù)據(jù)，當單表的數(shù)據(jù)量過大，如果繼續(xù)使用水平分庫，那么數(shù)據(jù)庫的實例 就會不斷增加，不利于系統(tǒng)的運維。這時候就要采用水平分表。

水平拆分表：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) ，分配到同一個數(shù)據(jù)庫的多張表中，每個表都只有一部分數(shù)據(jù)。

來看看Order表進行水平拆分的話，是什么樣子的。

Order1

Order2

實際上就是水平的把表數(shù)據(jù)給分成了2份，這么看起來是不是就很好理解了。

6分庫分表帶來的問題

事務(wù)問題

首先，分庫分表最大的隱患就是，事務(wù)的一致性， 當需要更新的內(nèi)容同時分布在不同的庫時，不可避免的會產(chǎn)生跨庫的事務(wù)問題。

原來在一個數(shù)據(jù)庫操 作，本地事務(wù)就可以進行控制，分庫之后 一個請求可能要訪問多個數(shù)據(jù)庫，如何保證事務(wù)的一致性，目前還沒有簡單的解決方案。

無法聯(lián)表的問題

還有一個就是，沒有辦法進行聯(lián)表查詢了，因為，原來在一個庫中的一些表，被分散到多個庫，并且這些數(shù)據(jù)庫可能還不在一臺服務(wù)器，無法關(guān)聯(lián)查詢，所以相對應(yīng)的業(yè)務(wù)代碼可能就比較多了。

分頁問題

分庫并行查詢時，如果用到了分頁，每個庫返回的結(jié)果集本身是無序的，只有將多個庫中的數(shù)據(jù)先查出來，然后再根據(jù)排序字段在內(nèi)存中進行排序，如果查詢結(jié)果過大也是十分消耗資源的。

分庫分表的技術(shù)

目前比較流行的就兩種，一種是MyCat，另外一種則是Sharding-Jdbc，都是可以進行分庫的，

MyCat是一個數(shù)據(jù)庫中間件，Sharding-Jdbc是以 jar 包提供服務(wù)的jdbc框架。

Mycat和Sharding-jdbc 實現(xiàn)原理也是不同：

Mycat的原理中最重要的一個動詞是“攔截”，它攔截了用戶發(fā)送過來的SQL語句，首先對SQL語句做了一些特定的分析：如分庫分表分析、路由分析、讀寫分離分析、緩存分析等，然后將此SQL發(fā)往后端的真實數(shù)據(jù)庫，并將返回的結(jié)果做適當?shù)奶幚?，最終再返回給用戶。

而Sharding-JDBC的原理是接受到一條SQL語句時，會陸續(xù)執(zhí)行SQL解析 => 查詢優(yōu)化 => SQL路由 => SQL改寫 => SQL執(zhí)行 => 結(jié)果歸并 ，最終返回執(zhí)行結(jié)果。

小結(jié)

垂直分表：將一張寬表(字段很多的表)，按照字段的訪問頻次進行拆分，就是按照表單結(jié)構(gòu)進行 拆。

垂直分庫：根據(jù)不同的業(yè)務(wù)，將表進行分類，拆分到不同的數(shù)據(jù)庫。這些庫可以部署在不同的服 務(wù)器，分攤訪問壓力。

水平分庫：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) 分到多個不同的數(shù)據(jù)庫。每個庫的表結(jié)構(gòu)相同

水平分表：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) ，分配到同一個數(shù)據(jù)庫的多張表中，每個表都只有一部 分數(shù)據(jù)。

7Sharding-Jdbc實現(xiàn)讀寫分離

搭建mysql主從服務(wù)

根據(jù)上面docker配置mysql主從復(fù)制部分搭建。

主服務(wù)創(chuàng)建庫表

CREATEDATABASEsharding-jdbc-db;

CREATETABLE`t_user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`nickname`varchar(100)DEFAULTNULL,
`password`varchar(100)DEFAULTNULL,
`sex`int(11)DEFAULTNULL,
`birthday`varchar(50)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

創(chuàng)建SpringBoot工程，引入依賴

4.0.0

org.springframework.boot
spring-boot-starter-parent
2.6.8
 

com.itjing
springboot-sharding-jdbc
0.0.1-SNAPSHOT
springboot-sharding-jdbc
springboot-sharding-jdbc

1.8


 

org.springframework.boot
spring-boot-starter-web


 

org.projectlombok
lombok
true


 

org.mybatis.spring.boot
mybatis-spring-boot-starter
1.3.2


 

mysql
mysql-connector-java
5.1.47


 

com.alibaba
druid-spring-boot-starter
1.1.17


 

org.apache.shardingsphere
sharding-jdbc-spring-boot-starter
4.0.0-RC1


 

org.springframework.boot
spring-boot-starter-test
test






org.springframework.boot
spring-boot-maven-plugin



org.projectlombok
lombok

配置文件

spring:
main:
allow-bean-definition-overriding:true
shardingsphere:
datasource:
ds:
maxPoolSize:100
#master-ds1數(shù)據(jù)庫連接信息
ds1:
driver-class-name:com.mysql.jdbc.Driver
maxPoolSize:100
minPoolSize:5
password:123456
type:com.alibaba.druid.pool.DruidDataSource
url:jdbc//192.168.56.111:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
username:root
#slave-ds2數(shù)據(jù)庫連接信息
ds2:
driver-class-name:com.mysql.jdbc.Driver
maxPoolSize:100
minPoolSize:5
password:123456
type:com.alibaba.druid.pool.DruidDataSource
url:jdbc//192.168.56.111:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
username:root
#slave-ds3數(shù)據(jù)庫連接信息
ds3:
driver-class-name:com.mysql.jdbc.Driver
minPoolSize:5
password:123456
type:com.alibaba.druid.pool.DruidDataSource
url:jdbc//192.168.56.111:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
username:root
#配置數(shù)據(jù)源
names:ds1,ds2,ds3
masterslave:
#配置slave節(jié)點的負載均衡均衡策略,采用輪詢機制
load-balance-algorithm-type:round_robin
#配置主庫master,負責數(shù)據(jù)的寫入
master-data-source-name:ds1
#配置主從名稱
name:ms
#配置從庫slave節(jié)點
slave-data-source-names:ds2,ds3
#顯示sql
props:
sql:
show:true
#配置默認數(shù)據(jù)源ds1默認數(shù)據(jù)源,主要用于寫
sharding:
default-data-source-name:ds1

#整合mybatis的配置
mybatis:
type-aliases-package:com.itjing.sharding.entity

定義Controller、Mapper、Entity

packagecom.itjing.sharding.entity;

importlombok.Data;

/**
*@authorlijing
*@date2022年06月19日10:45
*@description
*/
@Data
publicclassUser{
privateIntegerid;

privateStringnickname;

privateStringpassword;

privateIntegersex;

privateStringbirthday;
}

packagecom.itjing.sharding.controller;

importcom.itjing.sharding.entity.User;
importcom.itjing.sharding.mapper.UserMapper;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.web.bind.annotation.GetMapping;
importorg.springframework.web.bind.annotation.PostMapping;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.bind.annotation.RestController;

importjava.util.List;
importjava.util.Random;

/**
*@authorlijing
*@date2022年06月19日10:45
*@description
*/
@RestController
@RequestMapping("/api/user")
publicclassUserController{

@Autowired
privateUserMapperuserMapper;

@PostMapping("/save")
publicStringaddUser(){
Useruser=newUser();
user.setNickname("zhangsan"+newRandom().nextInt());
user.setPassword("123456");
user.setSex(1);
user.setBirthday("1997-12-03");
userMapper.addUser(user);
return"success";
}

@GetMapping("/findUsers")
publicListfindUsers(){
returnuserMapper.findUsers();
}
}


packagecom.itjing.sharding.mapper;

importcom.itjing.sharding.entity.User;
importorg.apache.ibatis.annotations.Insert;
importorg.apache.ibatis.annotations.Mapper;
importorg.apache.ibatis.annotations.Select;

importjava.util.List;

/**
*@authorlijing
*@date2022年06月19日10:46
*@description
*/
@Mapper
publicinterfaceUserMapper{

@Insert("insertintot_user(nickname,password,sex,birthday)values(#{nickname},#{password},#{sex},#{birthday})")
voidaddUser(Useruser);

@Select("select*fromt_user")
ListfindUsers();
}

啟動項目驗證

啟動日志中三個數(shù)據(jù)源初始化成功：

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

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

8Sharding-Jdbc實現(xiàn)分庫分表

分表

創(chuàng)建庫表

創(chuàng)建數(shù)據(jù)庫及其對應(yīng)的相同的兩張表結(jié)構(gòu)的表

先在MySQL上創(chuàng)建數(shù)據(jù)庫，直接起名叫做order

然后分別創(chuàng)建兩個表，分別是order_1和order_2。

這兩張表是訂單表拆分后的表，通過Sharding-Jdbc向訂單表插入數(shù)據(jù)，按照一定的分片規(guī)則，主鍵為偶數(shù)的落入order_1表 ，為奇數(shù)的落入order_2表，再通過Sharding-Jdbc 進行查詢。

DROPTABLEIFEXISTSorder_1;
CREATETABLEorder_1(
order_idBIGINT(20)PRIMARYKEYAUTO_INCREMENT,
user_idINT(11),
product_nameVARCHAR(128),
COUNTINT(11)
);

DROPTABLEIFEXISTSorder_2;
CREATETABLEorder_2(
order_idBIGINT(20)PRIMARYKEYAUTO_INCREMENT,
user_idINT(11),
product_nameVARCHAR(128),
COUNTINT(11)
);

創(chuàng)建SpringBoot的項目

略

依賴

參照讀寫分離的依賴

配置文件

比較重要的一步，那就是配置分片規(guī)則，因為這里的分表是直接把數(shù)據(jù)進行水平拆分成到2個表中，所以屬于水平切分數(shù)據(jù)表的操作，配置如下：

#讀寫分離
server:
servlet:
encoding:
enabled:true
charset:UTF-8
force:true
spring:
application:
name:sharding-jdbc-simple
main:
allow-bean-definition-overriding:true
shardingsphere:
datasource:
names:db1
db1:
type:com.alibaba.druid.pool.DruidDataSource
driver-class-name:com.mysql.jdbc.Driver
url:jdbc//127.0.0.1:3306/order?characterEncoding=UTF-8&useSSL=false
username:root
password:root
sharding:
tables:
order:
actual-data-nodes:db1.order_$->{1..2}
key-generator:
column:order_id
type:SNOWFLAKE
#分表策略
table-strategy:
inline:
sharding-column:order_id
algorithm-expression:order_$->{order_id%2+1}
props:
sql:
show:true

mybatis:
configuration:
map-underscore-to-camel-case:true

測試

packagecom.itjing.sharding.mapper;

importorg.apache.ibatis.annotations.Insert;
importorg.apache.ibatis.annotations.Mapper;
importorg.apache.ibatis.annotations.Param;

/**
*@authorlijing
*@date2022年06月19日11:18
*@description
*/
@Mapper
publicinterfaceOrderMapper{

/**
*新增訂單
*/
@Insert("INSERTINTOorder(user_id,product_name,COUNT)VALUES(#{user_id},#{product_name},#{count})")
intinsertOrder(@Param("user_id")intuser_id,@Param("product_name")Stringproduct_name,@Param("count")intcount);

}


packagecom.itjing.sharding.controller;

importcom.itjing.sharding.mapper.OrderMapper;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.web.bind.annotation.PostMapping;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.bind.annotation.RestController;

/**
*@authorlijing
*@date2022年06月19日11:20
*@description
*/
@RestController
@RequestMapping("/api/order")
publicclassOrderController{

@Autowired
privateOrderMapperorderMapper;

@PostMapping("/save")
publicStringtestInsertOrder(){
for(inti=0;i

	

	當執(zhí)行完畢的時候，可以看下日志：

	

	再看下數(shù)據(jù)庫：

	

	偶數(shù)訂單在表1中，奇數(shù)訂單在表2中。

	接下來就是直接執(zhí)行查詢，然后去查詢對應(yīng)表中的數(shù)據(jù)。

	給定1表和2表中的一個order_id 來進行 In 查詢，看是否能正確返回想要的數(shù)據(jù)：

	
/**
*查詢訂單
*/
@Select({""})
List

	

	很成功，使用Sharding-JDBC 進行單庫水平切分表的操作已經(jīng)完成了。

	分庫

	把同一個表的數(shù)據(jù)按一定規(guī)則拆到不同的數(shù)據(jù)庫中，每個庫可以放在不同的服務(wù)器上，在上面裝好數(shù)據(jù)庫之后，就可以開始進行操作了。

	建立庫表

	建立數(shù)據(jù)庫 order1 和 order2，然后創(chuàng)建相同表結(jié)構(gòu)的表。

	
DROPTABLEIFEXISTSorder_info;
CREATETABLEorder_info(
order_idBIGINT(20)PRIMARYKEYAUTO_INCREMENT,
user_idINT(11),
product_nameVARCHAR(128),
COUNTINT(11)
);


	

	配置

	
#讀寫分離
server:
servlet:
encoding:
enabled:true
charset:UTF-8
force:true
spring:
application:
name:sharding-jdbc-simple
main:
allow-bean-definition-overriding:true
shardingsphere:
datasource:
names:db1,db2
db1:
type:com.alibaba.druid.pool.DruidDataSource
driver-class-name:com.mysql.jdbc.Driver
url:jdbc//localhost:3306/order1?characterEncoding=UTF-8&useSSL=false
username:root
password:root
db2:
type:com.alibaba.druid.pool.DruidDataSource
driver-class-name:com.mysql.jdbc.Driver
url:jdbc//localhost:3306/order2?characterEncoding=UTF-8&useSSL=false
username:root
password:root
##分庫策略，以user_id為分片鍵，分片策略為user_id % 2 + 1，user_id為偶數(shù)操作db1數(shù)據(jù)源，否則操作db2。
sharding:
tables:
order_info:
actual-data-nodes:db$->{1..2}.order_info
key-generator:
column:order_id
type:SNOWFLAKE
#分庫策略
database-strategy:
inline:
sharding-column:user_id
algorithm-expression:db$->{user_id%2+1}
props:
sql:
show:true

mybatis:
configuration:
map-underscore-to-camel-case:true


	

	配置文件，在這里是通過配置對數(shù)據(jù)庫的分片策略，來指定數(shù)據(jù)庫進行操作。

	分庫策略，以user_id為分片鍵，分片策略為user_id % 2 + 1，user_id為偶數(shù)操作db1數(shù)據(jù)源，否則操作db2。

	這樣的分庫策略，直接通過 user_id 的奇偶性，來判斷到底是用哪個數(shù)據(jù)源，用哪個數(shù)據(jù)庫和表數(shù)據(jù)的。

	測試

	
@Insert("INSERTINTOorder_info(user_id,product_name,COUNT)VALUES(#{user_id},#{product_name},#{count})")
intinsertOrderFk(@Param("user_id")intuser_id,@Param("product_name")Stringproduct_name,@Param("count")intcount);


@PostMapping("/saveFk")
publicStringsaveFk(){
for(inti=0;i

	

	看日志的話，看樣子是成功了，看一下數(shù)據(jù)庫：

	

	這么看下來，保存的數(shù)據(jù)是沒問題的，從水平切分來看，把數(shù)據(jù)分別保存了order1和order2庫中的 order_info 里面，也就是說數(shù)據(jù)算是水平切分到了不同的數(shù)據(jù)庫對應(yīng)的表中。

	分庫分表后的查詢

	
@Select({""})
List

	

	9相關(guān)配置

	在說配置之前，得先了解一下關(guān)于Sharding-JDBC的執(zhí)行流程，不然也不知道這些配置都是干嘛用的。

	當把SQL發(fā)送給 Sharding 之后，Sharding 會經(jīng)過五個步驟，然后返回接口，這五個步驟分別是：

	SQL解析

	SQL路由

	SQL改寫

	SQL執(zhí)行

	結(jié)果歸并

	SQL解析：編寫SQL查詢的是邏輯表，執(zhí)行時 ShardingJDBC 要解析SQL，解析的目的是為了找到需要改寫的位置。

	SQL路由：SQL的路由是指將對邏輯表的操作，映射到對應(yīng)的數(shù)據(jù)節(jié)點的過程. ShardingJDBC會獲取分片鍵判斷是否正確，正確 就執(zhí)行分片策略(算法) 來找到真實的表。

	SQL改寫：程序員面向的是邏輯表編寫SQL，并不能直接在真實的數(shù)據(jù)庫中執(zhí)行，SQL改寫用于將邏輯 SQL改為在真實的數(shù)據(jù)庫中可以正確執(zhí)行的SQL。

	SQL執(zhí)行：通過配置規(guī)則order_$->{order_id % 2 + 1}，可以知道當 order_id 為偶數(shù)時，應(yīng)該向 order_1表中插入數(shù)據(jù)，為奇數(shù)時向 order_2表插入數(shù)據(jù)。

	結(jié)果歸并：將所有真正執(zhí)行sql的結(jié)果進行匯總合并，然后返回。

	都知道，要是用Sharding分庫分表，那么自然就會有相對應(yīng)的配置，而這些配置才是比較重要的地方，而其中比較經(jīng)典的就是分片策略了。

	分片策略

	分片策略分為分表策略和分庫策略，它們實現(xiàn)分片算法的方式基本相同，沒有太大的區(qū)別，無非一個是針對庫，一個是針對表。

	而一般分片策略主要是分為如下的幾種：

	standard：標準分片策略

	complex：復(fù)合分片策略

	inline：行表達式分片策略，使用Groovy的表達式.

	hint：Hint分片策略，對應(yīng)HintShardingStrategy。

	none：不分片策略，對應(yīng)NoneShardingStrategy。

	標準分片策略StandardShardingStrategy

	使用場景：SQL 語句中有>，>=, <=，<，=，IN 和BETWEEN AND操作符，都可以應(yīng)用此分片策略。

	也就是說，SQL 語句中頻繁的出現(xiàn)這些符號的時候，而且這個時候還想要進行分庫分表的時候，就可以采用這個策略了。

	但是這個時候要謹記一些內(nèi)容，那就是標準分片策略（StandardShardingStrategy），它只支持對單個分片鍵（字段）為依據(jù)的分庫分表，并提供了兩種分片算法PreciseShardingAlgorithm（精準分片）和RangeShardingAlgorithm（范圍分片）。

	在使用標準分片策略時，精準分片算法是必須實現(xiàn)的算法，用于 SQL 含有 = 和 IN 的分片處理；范圍分片算法是非必選的，用于處理含有 BETWEEN AND 的分片處理。

	復(fù)合分片策略

	使用場景：SQL 語句中有>，>=，<=，<，=，IN 和BETWEEN AND等操作符，不同的是復(fù)合分片策略支持對多個分片鍵操作。

	這里要注意的就是多個分片鍵，也就是說，如果分片的話需要使用兩個字段作為分片鍵，自定義復(fù)合分片策略。

	行表達式分片策略

	它的配置相當簡潔，這種分片策略利用inline.algorithm-expression書寫表達式。

	這里就是使用的這個，來完成的分片，而且行表達式分片策略適用于做簡單的分片算法，無需自定義分片算法，省去了繁瑣的代碼開發(fā)，是幾種分片策略中最為簡單的。

	但是要注意，行表達式分片策略，它只支持單分片鍵。

	Hint分片策略

	Hint分片策略（HintShardingStrategy）和其他的分片策略都不一樣了，這種分片策略無需配置分片鍵，分片鍵值也不再從 SQL中解析，而是由外部指定分片信息，讓 SQL在指定的分庫、分表中執(zhí)行。

	不分片策略

	不分片策略這個沒啥可說的，不分片的話，用Sharing-JDBC的話，可能就沒啥意思了。畢竟玩的就是分片。

	官方文檔：https://shardingsphere.apache.org/document/current/cn/overview/