SpringBoot + Sharding JDBC，一文搞定分庫(kù)分表、讀寫分離
Sharding-JDBC最早是當(dāng)當(dāng)網(wǎng)內(nèi)部使用的一款分庫(kù)分表框架，到2017年的時(shí)候才開(kāi)始對(duì)外開(kāi)源，這幾年在大量社區(qū)貢獻(xiàn)者的不斷迭代下，功能也逐漸完善，現(xiàn)已更名為ShardingSphere，2020年4?16日正式成為 Apache 軟件基金會(huì)的頂級(jí)項(xiàng)目。
ShardingSphere-Jdbc定位為輕量級(jí)Java框架，在Java的Jdbc層提供的額外服務(wù)。它使用客戶端直連數(shù)據(jù)庫(kù)，以jar包形式提供服務(wù)，可理解為增強(qiáng)版的Jdbc驅(qū)動(dòng)，完全兼容Jdbc和各種ORM框架。
隨著版本的不斷更迭 ShardingSphere 的核心功能也變得多元化起來(lái)。
從最開(kāi)始 Sharding-JDBC 1.0 版本只有數(shù)據(jù)分片，到 Sharding-JDBC 2.0 版本開(kāi)始支持?jǐn)?shù)據(jù)庫(kù)治理（注冊(cè)中心、配置中心等等），再到 Sharding-JDBC 3.0版本又加分布式事務(wù) （支持 Atomikos、Narayana、Bitronix、Seata），如今已經(jīng)迭代到了 Sharding-JDBC 4.0 版本。
現(xiàn)在的 ShardingSphere 不單單是指某個(gè)框架而是一個(gè)生態(tài)圈，這個(gè)生態(tài)圈Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar這三款開(kāi)源的分布式數(shù)據(jù)庫(kù)中間件解決方案所構(gòu)成。
ShardingSphere 的前身就是 Sharding-JDBC，所以它是整個(gè)框架中最為經(jīng)典、成熟的組件，先從 Sharding-JDBC 框架入手學(xué)習(xí)分庫(kù)分表。
1核心概念
分庫(kù)分表
分庫(kù)，顯而易見(jiàn)，就是一個(gè)數(shù)據(jù)庫(kù)分成多個(gè)數(shù)據(jù)庫(kù)，部署到不同機(jī)器。
分表，就是一個(gè)數(shù)據(jù)庫(kù)表分成多個(gè)表。
分片
一般在提到分庫(kù)分表的時(shí)候，大多是以水平切分模式（水平分庫(kù)、分表）為基礎(chǔ)來(lái)說(shuō)的，數(shù)據(jù)分片將原本一張數(shù)據(jù)量較大的表例如 t_order 拆分生成數(shù)個(gè)表結(jié)構(gòu)完全一致的小數(shù)據(jù)量表 t_order_0、t_order_1、···、t_order_n，每張表只存儲(chǔ)原大表中的一部分?jǐn)?shù)據(jù)，當(dāng)執(zhí)行一條SQL時(shí)會(huì)通過(guò)分庫(kù)策略、分片策略將數(shù)據(jù)分散到不同的數(shù)據(jù)庫(kù)、表內(nèi)。
數(shù)據(jù)節(jié)點(diǎn)
數(shù)據(jù)節(jié)點(diǎn)是分庫(kù)分表中一個(gè)不可再分的最小數(shù)據(jù)單元（表），它由數(shù)據(jù)源名稱和數(shù)據(jù)表組成，例如上圖中 order_db_1.t_order_0、order_db_2.t_order_1 就表示一個(gè)數(shù)據(jù)節(jié)點(diǎn)。
邏輯表
邏輯表是指一組具有相同邏輯和數(shù)據(jù)結(jié)構(gòu)表的總稱。
比如將訂單表 t_order 拆分成 t_order_0 ··· t_order_9 等 10張表。
此時(shí)會(huì)發(fā)現(xiàn)分庫(kù)分表以后數(shù)據(jù)庫(kù)中已不在有 t_order 這張表，取而代之的是 t_order_n，但在代碼中寫 SQL 依然按 t_order 來(lái)寫。此時(shí) t_order 就是這些拆分表的邏輯表。
真實(shí)表
真實(shí)表也就是上邊提到的 t_order_n 數(shù)據(jù)庫(kù)中真實(shí)存在的物理表。
分片鍵
用于分片的數(shù)據(jù)庫(kù)字段。將 t_order 表分片以后，當(dāng)執(zhí)行一條SQL時(shí)，通過(guò)對(duì)字段 order_id 取模的方式來(lái)決定，這條數(shù)據(jù)該在哪個(gè)數(shù)據(jù)庫(kù)中的哪個(gè)表中執(zhí)行，此時(shí) order_id 字段就是 t_order 表的分片健。
這樣以來(lái)同一個(gè)訂單的相關(guān)數(shù)據(jù)就會(huì)存在同一個(gè)數(shù)據(jù)庫(kù)表中，大幅提升數(shù)據(jù)檢索的性能，不僅如此 sharding-jdbc 還支持根據(jù)多個(gè)字段作為分片健進(jìn)行分片。
分片算法
上邊提到可以用分片健取模的規(guī)則分片，但這只是比較簡(jiǎn)單的一種，在實(shí)際開(kāi)發(fā)中還希望用 >=、<=、>、<、BETWEEN 和 IN 等條件作為分片規(guī)則，自定義分片邏輯，這時(shí)就需要用到分片策略與分片算法。
從執(zhí)行 SQL 的角度來(lái)看，分庫(kù)分表可以看作是一種路由機(jī)制，把 SQL 語(yǔ)句路由到期望的數(shù)據(jù)庫(kù)或數(shù)據(jù)表中并獲取數(shù)據(jù)，分片算法可以理解成一種路由規(guī)則。
咱們先捋一下它們之間的關(guān)系，分片策略只是抽象出的概念，它是由分片算法和分片健組合而成，分片算法做具體的數(shù)據(jù)分片邏輯。
分庫(kù)、分表的分片策略配置是相對(duì)獨(dú)立的，可以各自使用不同的策略與算法，每種策略中可以是多個(gè)分片算法的組合，每個(gè)分片算法可以對(duì)多個(gè)分片健做邏輯判斷。
分片算法和分片策略的關(guān)系
注意：sharding-jdbc 并沒(méi)有直接提供分片算法的實(shí)現(xiàn)，需要開(kāi)發(fā)者根據(jù)業(yè)務(wù)自行實(shí)現(xiàn)。
sharding-jdbc 提供了4種分片算法。
1、精確分片算法
精確分片算法（PreciseShardingAlgorithm）用于單個(gè)字段作為分片鍵，SQL中有 = 與 IN 等條件的分片，需要在標(biāo)準(zhǔn)分片策略（StandardShardingStrategy ）下使用。
2、范圍分片算法
范圍分片算法（RangeShardingAlgorithm）用于單個(gè)字段作為分片鍵，SQL中有 BETWEEN AND、>、<、>=、<= 等條件的分片，需要在標(biāo)準(zhǔn)分片策略（StandardShardingStrategy ）下使用。
3、復(fù)合分片算法
復(fù)合分片算法（ComplexKeysShardingAlgorithm）用于多個(gè)字段作為分片鍵的分片操作，同時(shí)獲取到多個(gè)分片健的值，根據(jù)多個(gè)字段處理業(yè)務(wù)邏輯。需要在復(fù)合分片策略（ComplexShardingStrategy ）下使用。
4、Hint分片算法
Hint分片算法（HintShardingAlgorithm）稍有不同，上邊的算法中都是解析SQL 語(yǔ)句提取分片鍵，并設(shè)置分片策略進(jìn)行分片。但有些時(shí)候并沒(méi)有使用任何的分片鍵和分片策略，可還想將 SQL 路由到目標(biāo)數(shù)據(jù)庫(kù)和表，就需要通過(guò)手動(dòng)干預(yù)指定SQL的目標(biāo)數(shù)據(jù)庫(kù)和表信息，這也叫強(qiáng)制路由。
分片策略
上邊講分片算法的時(shí)候已經(jīng)說(shuō)過(guò)，分片策略是一種抽象的概念，實(shí)際分片操作的是由分片算法和分片健來(lái)完成的。
1、標(biāo)準(zhǔn)分片策略
標(biāo)準(zhǔn)分片策略適用于單分片鍵，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 兩個(gè)分片算法。
其中 PreciseShardingAlgorithm 是必選的，用于處理 = 和 IN 的分片。RangeShardingAlgorithm 是可選的，用于處理BETWEEN AND， >， <，>=，<= 條件分片，如果不配置RangeShardingAlgorithm，SQL中的條件等將按照全庫(kù)路由處理。
2、復(fù)合分片策略
復(fù)合分片策略，同樣支持對(duì) SQL語(yǔ)句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片鍵，具體分配片細(xì)節(jié)完全由應(yīng)用開(kāi)發(fā)者實(shí)現(xiàn)。
3、行表達(dá)式分片策略
行表達(dá)式分片策略，支持對(duì) SQL語(yǔ)句中的 = 和 IN 的分片操作，但只支持單分片鍵。這種策略通常用于簡(jiǎn)單的分片，不需要自定義分片算法，可以直接在配置文件中接著寫規(guī)則。
t_order_$->{t_order_id % 4} 代表 t_order 對(duì)其字段 t_order_id取模，拆分成4張表，而表名分別是t_order_0 到 t_order_3。
4、Hint分片策略
Hint分片策略，對(duì)應(yīng)上邊的Hint分片算法，通過(guò)指定分片健而非從 SQL中提取分片健的方式進(jìn)行分片的策略。
分布式主鍵
數(shù)據(jù)分?后，不同數(shù)據(jù)節(jié)點(diǎn)?成全局唯?主鍵是?常棘?的問(wèn)題，同?個(gè)邏輯表（t_order）內(nèi)的不同真實(shí)表（t_order_n）之間的?增鍵由于?法互相感知而產(chǎn)?重復(fù)主鍵。
盡管可通過(guò)設(shè)置?增主鍵 初始值 和 步? 的?式避免ID碰撞，但這樣會(huì)使維護(hù)成本加大，乏完整性和可擴(kuò)展性。如果后去需要增加分片表的數(shù)量，要逐一修改分片表的步長(zhǎng)，運(yùn)維成本非常高，所以不建議這種方式。
為了讓上手更加簡(jiǎn)單，ApacheShardingSphere 內(nèi)置了UUID、SNOWFLAKE 兩種分布式主鍵?成器，默認(rèn)使用雪花算法（snowflake）?成64bit的?整型數(shù)據(jù)。不僅如此它還抽離出分布式主鍵?成器的接口，方便實(shí)現(xiàn)自定義的自增主鍵生成算法。
廣播表
廣播表：存在于所有的分片數(shù)據(jù)源中的表，表結(jié)構(gòu)和表中的數(shù)據(jù)在每個(gè)數(shù)據(jù)庫(kù)中均完全一致。一般是為字典表或者配置表 t_config，某個(gè)表一旦被配置為廣播表，只要修改某個(gè)數(shù)據(jù)庫(kù)的廣播表，所有數(shù)據(jù)源中廣播表的數(shù)據(jù)都會(huì)跟著同步。
綁定表
綁定表：那些分片規(guī)則一致的主表和子表。比如：t_order 訂單表和 t_order_item 訂單服務(wù)項(xiàng)目表，都是按 order_id 字段分片，因此兩張表互為綁定表關(guān)系。
那綁定表存在的意義是啥呢？
通常在業(yè)務(wù)中都會(huì)使用 t_order 和 t_order_item 等表進(jìn)行多表聯(lián)合查詢，但由于分庫(kù)分表以后這些表被拆分成N多個(gè)子表。如果不配置綁定表關(guān)系，會(huì)出現(xiàn)笛卡爾積關(guān)聯(lián)查詢，將產(chǎn)生如下四條SQL。
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
而配置綁定表關(guān)系后再進(jìn)行關(guān)聯(lián)查詢時(shí)，只要對(duì)應(yīng)表分片規(guī)則一致產(chǎn)生的數(shù)據(jù)就會(huì)落到同一個(gè)庫(kù)中，那么只需 t_order_0 和 t_order_item_0 表關(guān)聯(lián)即可。
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
注意：在關(guān)聯(lián)查詢時(shí) t_order 它作為整個(gè)聯(lián)合查詢的主表。所有相關(guān)的路由計(jì)算都只使用主表的策略，t_order_item 表的分片相關(guān)的計(jì)算也會(huì)使用 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
## 開(kāi)啟binlog
log-bin=mysql-bin
## binlog緩存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默認(rèn)格式是statement)
binlog_format=mixed
創(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
添加復(fù)制master數(shù)據(jù)的用戶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)
創(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ò)誤或指定類型的錯(cuò)誤,避免slave端復(fù)制中斷
## 如:1062錯(cuò)誤是指一些主鍵重復(fù),1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫(kù)數(shù)據(jù)不一致
slave_skip_errors=1062
創(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
在從服務(wù)器上配置連接主服務(wù)器的信息
首先主服務(wù)器上查看master_log_file、master_log_pos兩個(gè)參數(shù)，然后切換到從服務(wù)器上進(jìn)行主服務(wù)器的連接信息的設(shè)置
主服務(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;
從服務(wù)器啟動(dòng)I/O 線程和SQL線程
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)成功
binlog和redo log回顧
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ù)，全稱是Write Ahead Log，即當(dāng)事務(wù)提交時(shí)，先寫redo log，再修改頁(yè)。也就是說(shuō)，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB會(huì)先把記錄寫到redo log里面，并更新Buffer Pool的page，這個(gè)時(shí)候更新操作就算完成了
Buffer Pool是物理頁(yè)的緩存，對(duì)InnoDB的任何修改操作都會(huì)首先在Buffer Pool的page上進(jìn)行，然后這樣的頁(yè)將被標(biāo)記為臟頁(yè)并被放到專門的Flush List上，后續(xù)將由專門的刷臟線程階段性的將這些頁(yè)面寫入磁盤
InnoDB的redo log是固定大小的，比如可以配置為一組4個(gè)文件，每個(gè)文件的大小是1GB，循環(huán)使用，從頭開(kāi)始寫，寫到末尾就又回到開(kāi)頭循環(huán)寫（順序?qū)懀?jié)省了隨機(jī)寫磁盤的IO消耗）
Write Pos是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第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)刷新回磁盤，只需對(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è)刷新回磁盤
binlog（歸檔日志）
MySQL整體來(lái)看就有兩塊：一塊是Server層，主要做的是MySQL功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。redo log是InnoDB引擎特有的日志，而Server層也有自己的日志，稱為binlog
binlog記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作，不包括SELECT和SHOW這類操作，主要作用是用于數(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ǔ)句選擇日志保存方式
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)寫的，空間固定會(huì)用完；binlog是可以追加寫入的，binlog文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志
兩階段提交
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í)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回 -
執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù) -
引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到redo log里面，此時(shí)redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù) -
執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫入磁盤 -
執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交狀態(tài)，更新完成
update語(yǔ)句的執(zhí)行流程圖如下，圖中淺色框表示在InnoDB內(nèi)部執(zhí)行的，深色框表示是在執(zhí)行器中執(zhí)行的
將redo log的寫入拆成了兩個(gè)步驟：prepare和commit，這就是兩階段提交
MySQL主從復(fù)制原理
從庫(kù)B和主庫(kù)A之間維持了一個(gè)長(zhǎng)連接。主庫(kù)A內(nèi)部有一個(gè)線程，專門用于服務(wù)從庫(kù)B的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過(guò)程如下：
-
在從庫(kù)B上通過(guò)change master命令，設(shè)置主庫(kù)A的IP、端口、用戶名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求binlog，這個(gè)位置包含文件名和日志偏移量 -
在從庫(kù)B上執(zhí)行start slave命令，這時(shí)從庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的I/O線程和SQL線程。其中I/O線程負(fù)責(zé)與主庫(kù)建立連接 -
主庫(kù)A校驗(yàn)完用戶名、密碼后，開(kāi)始按照從庫(kù)B傳過(guò)來(lái)的位置，從本地讀取binlog，發(fā)給B -
從庫(kù)B拿到binlog后，寫到本地文件，稱為中繼日志 -
SQL線程讀取中繼日志，解析出日志里的命令，并執(zhí)行
由于多線程復(fù)制方案的引入，SQL線程演化成了多個(gè)線程。
主從復(fù)制不是完全實(shí)時(shí)地進(jìn)行同步，而是異步實(shí)時(shí)。這中間存在主從服務(wù)之間的執(zhí)行延時(shí)，如果主服務(wù)器的壓力很大，則可能導(dǎo)致主從服務(wù)器延時(shí)較大。
3為什么需要分庫(kù)分表？
首先要明確一個(gè)問(wèn)題，單一的數(shù)據(jù)庫(kù)是否能夠滿足公司目前的線上業(yè)務(wù)需求，比如用戶表，可能有幾千萬(wàn)，甚至上億的數(shù)據(jù)，只是說(shuō)可能，如果有這么多用戶，那必然是大公司了，那么這個(gè)時(shí)候，如果不分表也不分庫(kù)的話，那么數(shù)據(jù)了上來(lái)的時(shí)候，稍微一個(gè)不注意，MySQL單機(jī)磁盤容量會(huì)撐爆，但是如果拆成多個(gè)數(shù)據(jù)庫(kù)，磁盤使用率大大降低。
這樣就把磁盤使用率降低，這是通過(guò)硬件的形式解決問(wèn)題，如果數(shù)據(jù)量是巨大的，這時(shí)候，SQL 如果沒(méi)有命中索引，那么就會(huì)導(dǎo)致一個(gè)情況，查這個(gè)表的SQL語(yǔ)句直接把數(shù)據(jù)庫(kù)給干崩了。
即使SQL命中了索引，如果表的數(shù)據(jù)量 超過(guò)一千萬(wàn)的話， 查詢也是會(huì)明顯變慢的。這是因?yàn)樗饕话闶荁+樹(shù)結(jié)構(gòu)，數(shù)據(jù)千萬(wàn)級(jí)別的話，B+樹(shù)的高度會(huì)增高，查詢自然就變慢了，當(dāng)然，這是題外話了。
4MySQL分庫(kù)分表原理
分庫(kù)分表
水平拆分：同一個(gè)表的數(shù)據(jù)拆到不同的庫(kù)不同的表中?？梢愿鶕?jù)時(shí)間、地區(qū)或某個(gè)業(yè)務(wù)鍵維度，也可以通過(guò)hash進(jìn)行拆分，最后通過(guò)路由訪問(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è)表
不停機(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分庫(kù)分表方案
分庫(kù)分表方案，不外乎就兩種，一種是垂直切分，一種是水平切分。
但是總有做開(kāi)發(fā)的小伙伴不知道這垂直切分和水平切分到底是什么樣的，為什么垂直切分，為什么水平切分，什么時(shí)候應(yīng)該選擇垂直切分，什么時(shí)候應(yīng)該選擇水平切分。
有人是這么說(shuō)的，垂直切分是根據(jù)業(yè)務(wù)來(lái)拆分?jǐn)?shù)據(jù)庫(kù)，同一類業(yè)務(wù)的數(shù)據(jù)表拆分到一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)，另一類的數(shù)據(jù)表拆分到其他數(shù)據(jù)庫(kù)。
有些人不理解這個(gè)，實(shí)際上垂直切分也是有劃分的，上面描述的是垂直切分?jǐn)?shù)據(jù)庫(kù)，可能容易讓很多人不太理解，但是如果是垂直切分表，那么肯定百分之90的人都能理解。
垂直切分
有一張Order表，表中有諸多記錄，比如設(shè)計(jì)這么一張簡(jiǎn)單的表。
| id | order_id | order_date | order_type | order_state |
|---|---|---|---|---|
| 1 | cd96cff0356e483caae6b2ff4e878fd6 | 2022-06-18 13:57:11 | 支付寶 | 1 |
| 2 | e2496f9e22ce4391806b18480440526a | 2022-06-18 14:22:33 | 微信 | 2 |
| 3 | 9e7ab5a1915c4570a9eaaaa3c01f79c1 | 2022-06-18 15:21:44 | 現(xiàn)金 | 2 |
以上是簡(jiǎn)化版Order表，如果想要垂直切分，那么應(yīng)該怎么處理？
直接拆分成2個(gè)表，這時(shí)候就直接就一分為2 ，咔的一下拆分成兩個(gè)表
Order1
| id | order_id | order_date |
|---|---|---|
| 1 | cd96cff0356e483caae6b2ff4e878fd6 | 2022-06-18 13:57:11 |
| 2 | e2496f9e22ce4391806b18480440526a | 2022-06-18 14:22:33 |
| 3 | 9e7ab5a1915c4570a9eaaaa3c01f79c1 | 2022-06-18 15:21:44 |
Order2
| id | order_type | order_state |
|---|---|---|
| 1 | 支付寶 | 1 |
| 2 | 微信 | 2 |
| 3 | 現(xiàn)金 | 2 |
這時(shí)候主鍵ID保持的時(shí)一致的，而這個(gè)操作，就是垂直拆分，分表的操作。
既然說(shuō)了垂直拆分，那么必然就有水平拆分，
什么是水平拆分呢？
實(shí)際上水平拆分的話，那真的是只有一句話。
水平切分
按照數(shù)據(jù)來(lái)拆分
水平拆分?jǐn)?shù)據(jù)庫(kù)：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) 分到多個(gè)不同的數(shù)據(jù)庫(kù)。每個(gè)庫(kù)的表結(jié)構(gòu)相同，每個(gè)庫(kù)都只有這張表的部分?jǐn)?shù)據(jù)，當(dāng)單表的數(shù)據(jù)量過(guò)大，如果繼續(xù)使用水平分庫(kù)，那么數(shù)據(jù)庫(kù)的實(shí)例 就會(huì)不斷增加，不利于系統(tǒng)的運(yùn)維。這時(shí)候就要采用水平分表。
水平拆分表：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) ，分配到同一個(gè)數(shù)據(jù)庫(kù)的多張表中，每個(gè)表都只有一部分?jǐn)?shù)據(jù)。
來(lái)看看Order表進(jìn)行水平拆分的話，是什么樣子的。
Order1
| id | order_id | order_date | order_type | order_state |
|---|---|---|---|---|
| 1 | cd96cff0356e483caae6b2ff4e878fd6 | 2022-06-18 13:57:11 | 支付寶 | 1 |
| 2 | e2496f9e22ce4391806b18480440526a | 2022-06-18 14:22:33 | 微信 | 2 |
Order2
| id | order_id | order_date | order_type | order_state |
|---|---|---|---|---|
| 3 | 9e7ab5a1915c4570a9eaaaa3c01f79c1 | 2022-06-18 15:21:44 | 現(xiàn)金 | 2 |
實(shí)際上就是水平的把表數(shù)據(jù)給分成了2份，這么看起來(lái)是不是就很好理解了。
6分庫(kù)分表帶來(lái)的問(wèn)題
事務(wù)問(wèn)題
首先，分庫(kù)分表最大的隱患就是，事務(wù)的一致性， 當(dāng)需要更新的內(nèi)容同時(shí)分布在不同的庫(kù)時(shí)，不可避免的會(huì)產(chǎn)生跨庫(kù)的事務(wù)問(wèn)題。
原來(lái)在一個(gè)數(shù)據(jù)庫(kù)操 作，本地事務(wù)就可以進(jìn)行控制，分庫(kù)之后 一個(gè)請(qǐng)求可能要訪問(wèn)多個(gè)數(shù)據(jù)庫(kù)，如何保證事務(wù)的一致性，目前還沒(méi)有簡(jiǎn)單的解決方案。
無(wú)法聯(lián)表的問(wèn)題
還有一個(gè)就是，沒(méi)有辦法進(jìn)行聯(lián)表查詢了，因?yàn)?，原?lái)在一個(gè)庫(kù)中的一些表，被分散到多個(gè)庫(kù)，并且這些數(shù)據(jù)庫(kù)可能還不在一臺(tái)服務(wù)器，無(wú)法關(guān)聯(lián)查詢，所以相對(duì)應(yīng)的業(yè)務(wù)代碼可能就比較多了。
分頁(yè)問(wèn)題
分庫(kù)并行查詢時(shí)，如果用到了分頁(yè)，每個(gè)庫(kù)返回的結(jié)果集本身是無(wú)序的，只有將多個(gè)庫(kù)中的數(shù)據(jù)先查出來(lái)，然后再根據(jù)排序字段在內(nèi)存中進(jìn)行排序，如果查詢結(jié)果過(guò)大也是十分消耗資源的。
分庫(kù)分表的技術(shù)
目前比較流行的就兩種，一種是MyCat，另外一種則是Sharding-Jdbc，都是可以進(jìn)行分庫(kù)的，
MyCat是一個(gè)數(shù)據(jù)庫(kù)中間件，Sharding-Jdbc是以 jar 包提供服務(wù)的jdbc框架。
Mycat和Sharding-jdbc 實(shí)現(xiàn)原理也是不同：
Mycat的原理中最重要的一個(gè)動(dòng)詞是“攔截”，它攔截了用戶發(fā)送過(guò)來(lái)的SQL語(yǔ)句，首先對(duì)SQL語(yǔ)句做了一些特定的分析：如分庫(kù)分表分析、路由分析、讀寫分離分析、緩存分析等，然后將此SQL發(fā)往后端的真實(shí)數(shù)據(jù)庫(kù)，并將返回的結(jié)果做適當(dāng)?shù)奶幚?，最終再返回給用戶。
而Sharding-JDBC的原理是接受到一條SQL語(yǔ)句時(shí)，會(huì)陸續(xù)執(zhí)行SQL解析 => 查詢優(yōu)化 => SQL路由 => SQL改寫 => SQL執(zhí)行 => 結(jié)果歸并 ，最終返回執(zhí)行結(jié)果。
小結(jié)
垂直分表：將一張寬表(字段很多的表)，按照字段的訪問(wèn)頻次進(jìn)行拆分，就是按照表單結(jié)構(gòu)進(jìn)行 拆。
垂直分庫(kù)：根據(jù)不同的業(yè)務(wù)，將表進(jìn)行分類，拆分到不同的數(shù)據(jù)庫(kù)。這些庫(kù)可以部署在不同的服 務(wù)器，分?jǐn)傇L問(wèn)壓力。
水平分庫(kù)：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) 分到多個(gè)不同的數(shù)據(jù)庫(kù)。每個(gè)庫(kù)的表結(jié)構(gòu)相同
水平分表：將一張表的數(shù)據(jù) ( 按照數(shù)據(jù)行) ，分配到同一個(gè)數(shù)據(jù)庫(kù)的多張表中，每個(gè)表都只有一部 分?jǐn)?shù)據(jù)。
7Sharding-Jdbc實(shí)現(xiàn)讀寫分離
搭建mysql主從服務(wù)
根據(jù)上面docker配置mysql主從復(fù)制部分搭建。
主服務(wù)創(chuàng)建庫(kù)表
CREATE DATABASE sharding-jdbc-db;
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 DEFAULT CHARSET=utf8mb4;
創(chuàng)建SpringBoot工程，引入依賴
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.8</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.itjing</groupId>
<artifactId>springboot-sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-sharding-jdbc</name>
<description>springboot-sharding-jdbc</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<!-- sharding-jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
配置文件
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
ds:
maxPoolSize: 100
# master-ds1數(shù)據(jù)庫(kù)連接信息
ds1:
driver-class-name: com.mysql.jdbc.Driver
maxPoolSize: 100
minPoolSize: 5
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.56.111:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
# slave-ds2數(shù)據(jù)庫(kù)連接信息
ds2:
driver-class-name: com.mysql.jdbc.Driver
maxPoolSize: 100
minPoolSize: 5
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.56.111:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
# slave-ds3數(shù)據(jù)庫(kù)連接信息
ds3:
driver-class-name: com.mysql.jdbc.Driver
minPoolSize: 5
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://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é)點(diǎn)的負(fù)載均衡均衡策略,采用輪詢機(jī)制
load-balance-algorithm-type: round_robin
# 配置主庫(kù)master,負(fù)責(zé)數(shù)據(jù)的寫入
master-data-source-name: ds1
# 配置主從名稱
name: ms
# 配置從庫(kù)slave節(jié)點(diǎn)
slave-data-source-names: ds2,ds3
# 顯示sql
props:
sql:
show: true
# 配置默認(rèn)數(shù)據(jù)源ds1 默認(rèn)數(shù)據(jù)源,主要用于寫
sharding:
default-data-source-name: ds1
# 整合mybatis的配置
mybatis:
type-aliases-package: com.itjing.sharding.entity
定義Controller、Mapper、Entity
package com.itjing.sharding.entity;
import lombok.Data;
/**
* @author lijing
* @date 2022年06月19日 10:45
* @description
*/
@Data
public class User {
private Integer id;
private String nickname;
private String password;
private Integer sex;
private String birthday;
}
package com.itjing.sharding.controller;
import com.itjing.sharding.entity.User;
import com.itjing.sharding.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Random;
/**
* @author lijing
* @date 2022年06月19日 10:45
* @description
*/
@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<User> findUsers() {
return userMapper.findUsers();
}
}
package com.itjing.sharding.mapper;
import com.itjing.sharding.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author lijing
* @date 2022年06月19日 10:46
* @description
*/
@Mapper
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<User> findUsers();
}
啟動(dòng)項(xiàng)目驗(yàn)證
啟動(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)，并且輪詢進(jìn)入
8Sharding-Jdbc實(shí)現(xiàn)分庫(kù)分表
分表
創(chuàng)建庫(kù)表
創(chuàng)建數(shù)據(jù)庫(kù)及其對(duì)應(yīng)的相同的兩張表結(jié)構(gòu)的表
先在MySQL上創(chuàng)建數(shù)據(jù)庫(kù)，直接起名叫做order
然后分別創(chuàng)建兩個(gè)表，分別是order_1和order_2。
這兩張表是訂單表拆分后的表，通過(guò)Sharding-Jdbc向訂單表插入數(shù)據(jù)，按照一定的分片規(guī)則，主鍵為偶數(shù)的落入order_1表 ，為奇數(shù)的落入order_2表，再通過(guò)Sharding-Jdbc 進(jìn)行查詢。
DROP TABLE IF EXISTS order_1;
CREATE TABLE order_1 (
order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
user_id INT(11) ,
product_name VARCHAR(128),
COUNT INT(11)
);
DROP TABLE IF EXISTS order_2;
CREATE TABLE order_2 (
order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
user_id INT(11) ,
product_name VARCHAR(128),
COUNT INT(11)
);
創(chuàng)建SpringBoot的項(xiàng)目
略
依賴
參照讀寫分離的依賴
配置文件
比較重要的一步，那就是配置分片規(guī)則，因?yàn)檫@里的分表是直接把數(shù)據(jù)進(jìn)行水平拆分成到2個(gè)表中，所以屬于水平切分?jǐn)?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:mysql://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
測(cè)試
package com.itjing.sharding.mapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
/**
* @author lijing
* @date 2022年06月19日 11:18
* @description
*/
@Mapper
public interface OrderMapper {
/**
* 新增訂單
*/
@Insert("INSERT INTO order(user_id,product_name,COUNT) VALUES(#{user_id},#{product_name},#{count})")
int insertOrder(@Param("user_id") int user_id, @Param("product_name") String product_name, @Param("count") int count);
}
package com.itjing.sharding.controller;
import com.itjing.sharding.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author lijing
* @date 2022年06月19日 11:20
* @description
*/
@RestController
@RequestMapping("/api/order")
public class OrderController {
@Autowired
private OrderMapper orderMapper;
@PostMapping("/save")
public String testInsertOrder() {
for (int i = 0; i < 10; i++) {
orderMapper.insertOrder(100 + i, "空調(diào)" + i, 10);
}
return "success";
}
}
當(dāng)執(zhí)行完畢的時(shí)候，可以看下日志：
再看下數(shù)據(jù)庫(kù)：
偶數(shù)訂單在表1中，奇數(shù)訂單在表2中。
接下來(lái)就是直接執(zhí)行查詢，然后去查詢對(duì)應(yīng)表中的數(shù)據(jù)。
給定1表和2表中的一個(gè)order_id 來(lái)進(jìn)行 In 查詢，看是否能正確返回想要的數(shù)據(jù)：
/**
* 查詢訂單
*/
@Select({"<script>" +
"select * from order p where p.order_id in " +
"<foreach collection='orderIds' item='id' open='(' separator = ',' close=')'>#{id}</foreach>"
+ "</script>"})
List<Map> findOrderByIds(@Param("orderIds") List<Long> orderIds);
@GetMapping("find")
public void testFindOrderByIds() {
List<Long> ids = new ArrayList<>();
ids.add(745241267423674369L);
ids.add(745241268338032640L);
List<Map> list = orderMapper.findOrderByIds(ids);
System.out.println(list);
}
很成功，使用Sharding-JDBC 進(jìn)行單庫(kù)水平切分表的操作已經(jīng)完成了。
分庫(kù)
把同一個(gè)表的數(shù)據(jù)按一定規(guī)則拆到不同的數(shù)據(jù)庫(kù)中，每個(gè)庫(kù)可以放在不同的服務(wù)器上，在上面裝好數(shù)據(jù)庫(kù)之后，就可以開(kāi)始進(jìn)行操作了。
建立庫(kù)表
建立數(shù)據(jù)庫(kù) order1 和 order2，然后創(chuàng)建相同表結(jié)構(gòu)的表。
DROP TABLE IF EXISTS order_info;
CREATE TABLE order_info (
order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
user_id INT(11) ,
product_name VARCHAR(128),
COUNT INT(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:mysql://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:mysql://localhost:3306/order2?characterEncoding=UTF-8&useSSL=false
username: root
password: root
## 分庫(kù)策略，以u(píng)ser_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
# 分庫(kù)策略
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
配置文件，在這里是通過(guò)配置對(duì)數(shù)據(jù)庫(kù)的分片策略，來(lái)指定數(shù)據(jù)庫(kù)進(jìn)行操作。
分庫(kù)策略，以u(píng)ser_id為分片鍵，分片策略為user_id % 2 + 1，user_id為偶數(shù)操作db1數(shù)據(jù)源，否則操作db2。
這樣的分庫(kù)策略，直接通過(guò) user_id 的奇偶性，來(lái)判斷到底是用哪個(gè)數(shù)據(jù)源，用哪個(gè)數(shù)據(jù)庫(kù)和表數(shù)據(jù)的。
測(cè)試
@Insert("INSERT INTO order_info(user_id,product_name,COUNT) VALUES(#{user_id},#{product_name},#{count})")
int insertOrderFk(@Param("user_id") int user_id, @Param("product_name") String product_name, @Param("count") int count);
@PostMapping("/saveFk")
public String saveFk() {
for (int i = 0; i < 10; i++) {
orderMapper.insertOrderFk(i, "空調(diào)" + i, 1);
}
return "success";
}
看日志的話，看樣子是成功了，看一下數(shù)據(jù)庫(kù)：
這么看下來(lái)，保存的數(shù)據(jù)是沒(méi)問(wèn)題的，從水平切分來(lái)看，把數(shù)據(jù)分別保存了order1和order2庫(kù)中的 order_info 里面，也就是說(shuō)數(shù)據(jù)算是水平切分到了不同的數(shù)據(jù)庫(kù)對(duì)應(yīng)的表中。
分庫(kù)分表后的查詢
@Select({"<script>"+
"select * from order_info p where p.order_id in " +
"<foreach collection='orderIds' item='id' open='(' separator = ',' close=')'>#{id}</foreach>"
+"</script>"})
List<Map> findOrderByIdsFk(@Param("orderIds") List<Long> orderIds);
@GetMapping("findFk")
public void testFindOrderByIdsFk() {
List<Long> ids = new ArrayList<>();
ids.add(745252093001990145L);
ids.add(745252094096703488L);
List<Map> list = orderMapper.findOrderByIdsFk(ids);
System.out.println(list);
}
9相關(guān)配置
在說(shuō)配置之前，得先了解一下關(guān)于Sharding-JDBC的執(zhí)行流程，不然也不知道這些配置都是干嘛用的。
當(dāng)把SQL發(fā)送給 Sharding 之后，Sharding 會(huì)經(jīng)過(guò)五個(gè)步驟，然后返回接口，這五個(gè)步驟分別是：
-
SQL解析 -
SQL路由 -
SQL改寫 -
SQL執(zhí)行 -
結(jié)果歸并
SQL解析：編寫SQL查詢的是邏輯表，執(zhí)行時(shí) ShardingJDBC 要解析SQL，解析的目的是為了找到需要改寫的位置。
SQL路由：SQL的路由是指將對(duì)邏輯表的操作，映射到對(duì)應(yīng)的數(shù)據(jù)節(jié)點(diǎn)的過(guò)程. ShardingJDBC會(huì)獲取分片鍵判斷是否正確，正確 就執(zhí)行分片策略(算法) 來(lái)找到真實(shí)的表。
SQL改寫：程序員面向的是邏輯表編寫SQL，并不能直接在真實(shí)的數(shù)據(jù)庫(kù)中執(zhí)行，SQL改寫用于將邏輯 SQL改為在真實(shí)的數(shù)據(jù)庫(kù)中可以正確執(zhí)行的SQL。
SQL執(zhí)行：通過(guò)配置規(guī)則order_$->{order_id % 2 + 1}，可以知道當(dāng) order_id 為偶數(shù)時(shí)，應(yīng)該向 order_1表中插入數(shù)據(jù)，為奇數(shù)時(shí)向 order_2表插入數(shù)據(jù)。
結(jié)果歸并：將所有真正執(zhí)行sql的結(jié)果進(jìn)行匯總合并，然后返回。
都知道，要是用Sharding分庫(kù)分表，那么自然就會(huì)有相對(duì)應(yīng)的配置，而這些配置才是比較重要的地方，而其中比較經(jīng)典的就是分片策略了。
分片策略
分片策略分為分表策略和分庫(kù)策略，它們實(shí)現(xiàn)分片算法的方式基本相同，沒(méi)有太大的區(qū)別，無(wú)非一個(gè)是針對(duì)庫(kù)，一個(gè)是針對(duì)表。
而一般分片策略主要是分為如下的幾種：
-
standard：標(biāo)準(zhǔn)分片策略 -
complex：復(fù)合分片策略 -
inline：行表達(dá)式分片策略，使用Groovy的表達(dá)式. -
hint：Hint分片策略，對(duì)應(yīng) HintShardingStrategy。 -
none：不分片策略，對(duì)應(yīng) NoneShardingStrategy。
標(biāo)準(zhǔn)分片策略StandardShardingStrategy
使用場(chǎng)景：SQL 語(yǔ)句中有>，>=, <=，<，=，IN 和BETWEEN AND操作符，都可以應(yīng)用此分片策略。
也就是說(shuō)，SQL 語(yǔ)句中頻繁的出現(xiàn)這些符號(hào)的時(shí)候，而且這個(gè)時(shí)候還想要進(jìn)行分庫(kù)分表的時(shí)候，就可以采用這個(gè)策略了。
但是這個(gè)時(shí)候要謹(jǐn)記一些內(nèi)容，那就是標(biāo)準(zhǔn)分片策略（StandardShardingStrategy），它只支持對(duì)單個(gè)分片鍵（字段）為依據(jù)的分庫(kù)分表，并提供了兩種分片算法PreciseShardingAlgorithm（精準(zhǔn)分片）和RangeShardingAlgorithm（范圍分片）。
在使用標(biāo)準(zhǔn)分片策略時(shí)，精準(zhǔn)分片算法是必須實(shí)現(xiàn)的算法，用于 SQL 含有 = 和 IN 的分片處理；范圍分片算法是非必選的，用于處理含有 BETWEEN AND 的分片處理。
復(fù)合分片策略
使用場(chǎng)景：SQL 語(yǔ)句中有>，>=，<=，<，=，IN 和BETWEEN AND等操作符，不同的是復(fù)合分片策略支持對(duì)多個(gè)分片鍵操作。
這里要注意的就是多個(gè)分片鍵，也就是說(shuō)，如果分片的話需要使用兩個(gè)字段作為分片鍵，自定義復(fù)合分片策略。
行表達(dá)式分片策略
它的配置相當(dāng)簡(jiǎn)潔，這種分片策略利用inline.algorithm-expression書寫表達(dá)式。
這里就是使用的這個(gè)，來(lái)完成的分片，而且行表達(dá)式分片策略適用于做簡(jiǎn)單的分片算法，無(wú)需自定義分片算法，省去了繁瑣的代碼開(kāi)發(fā)，是幾種分片策略中最為簡(jiǎn)單的。
但是要注意，行表達(dá)式分片策略，它只支持單分片鍵。
Hint分片策略
Hint分片策略（HintShardingStrategy）和其他的分片策略都不一樣了，這種分片策略無(wú)需配置分片鍵，分片鍵值也不再?gòu)?SQL中解析，而是由外部指定分片信息，讓 SQL在指定的分庫(kù)、分表中執(zhí)行。
不分片策略
不分片策略這個(gè)沒(méi)啥可說(shuō)的，不分片的話，用Sharing-JDBC的話，可能就沒(méi)啥意思了。畢竟玩的就是分片。
官方文檔：https://shardingsphere.apache.org/document/current/cn/overview/
來(lái)源：blog.csdn.net/qq_40378034/article/details/115264837
-End-
最近有一些小伙伴，讓我?guī)兔φ乙恍?nbsp;面試題 資料，于是我翻遍了收藏的 5T 資料后，匯總整理出來(lái)，可以說(shuō)是程序員面試必備！所有資料都整理到網(wǎng)盤了，歡迎下載！
面試題】即可獲取
好文分享給更多人↓↓