一、Sharding-JDBC介紹
1，介紹
Sharding-JDBC是噹噹網研發的開源分散式資料庫中介軟體，從 3.0 開始Sharding-JDBC被包含在 Sharding-Sphere中，之後該專案進入進入Apache孵化器，4.0版本之後的版本為Apache版本。
ShardingSphere是一套開源的分散式資料庫中介軟體解決方案組成的生態圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計劃中）這3款相互獨立的產品組成。 他們均提供標準化的資料分片、分散式事務和資料庫治理功能，可適用於如Java同構、異構語言、容器、雲原生等各種多樣化的應用場景。
Sharding-JDBC的核心功能為資料分片和讀寫分離，通過Sharding-JDBC，應用可以透明的使用jdbc訪問已經分庫分表、讀寫分離的多個資料來源，而不用關心資料來源的數量以及資料如何分佈。
- 適用於任何基於Java的ORM框架，如： Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
- 基於任何第三方的資料庫連線池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
- 支援任意實現JDBC規範的資料庫。目前支援MySQL，Oracle，SQLServer和PostgreSQL。
上圖展示了Sharding-Jdbc的工作方式，使用Sharding-Jdbc前需要人工對資料庫進行分庫分表，在應用程式中加入Sharding-Jdbc的Jar包，應用程式通過Sharding-Jdbc操作分庫分表後的資料庫和資料表，由於Sharding-Jdbc是對Jdbc驅動的增強，使用Sharding-Jdbc就像使用Jdbc驅動一樣，在應用程式中是無需指定具體要操作的分庫和分表的。
2，與jdbc效能對比
a）效能損耗測試
伺服器資源充足、併發數相同，比較JDBC和Sharding-JDBC效能損耗，Sharding-JDBC相對JDBC損耗不超過7%。
b）效能對比測試
伺服器資源使用到極限，相同的場景JDBC與Sharding-JDBC的吞吐量相當。
伺服器資源使用到極限，Sharding-JDBC採用分庫分表後，Sharding-JDBC吞吐量較JDBC不分表有接近2倍的提升。
二、Sharding-JDBC執行原理
1，基本概念
a）邏輯表
水平拆分的資料表的總稱。例：訂單資料表根據主鍵尾數拆分為10張表，分別是 t_order_0 、 t_order_1 到t_order_9 ，他們的邏輯表名為 t_order 。
b）真實表
在分片的資料庫中真實存在的物理表。即上個示例中的 t_order_0 到 t_order_9 。
c）資料節點
資料分片的最小物理單元。由資料來源名稱和資料表組成，例： ds_0.t_order_0 。
d）繫結表
指分片規則一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片，繫結表之間的分割槽鍵完全相同，則此兩張表互為繫結表關係。繫結表之間的多表關聯查詢不會出現笛卡爾積關聯，關聯查詢效率將大大提升。舉例說明，如果SQL為：
SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11)
e）廣播表
指所有的分片資料來源中都存在的表，表結構和表中的資料在每個資料庫中均完全一致。適用於資料量不大且需要與海量資料的表進行關聯查詢的場景，例如：字典表。
f）分片鍵
用於分片的資料庫欄位，是將資料庫(表)水平拆分的關鍵欄位。例：將訂單表中的訂單主鍵的尾數取模分片，則訂單主鍵為分片欄位。 SQL中如果無分片欄位，將執行全路由，效能較差。 除了對單分片欄位的支援，Sharding-Jdbc也支援根據多個欄位進行分片。
g）分片演算法
通過分片演算法將資料分片，支援通過 = 、 BETWEEN 和 IN 分片。分片演算法需要應用方開發者自行實現，可實現的靈活度非常高。包括：精確分片演算法 、範圍分片演算法 ，複合分片演算法 等。例如：where order_id = ? 將採用精確分片演算法，where order_id in (?,?,?)將採用精確分片演算法，where order_id BETWEEN ? and ? 將採用範圍分片演算法，複合分片演算法用於分片鍵有多個複雜情況。
h）分片策略
包含分片鍵和分片演算法，由於分片演算法的獨立性，將其獨立抽離。真正可用於分片操作的是分片鍵 + 分片演算法，也就是分片策略。內建的分片策略大致可分為尾數取模、雜湊、範圍、標籤、時間等。由使用者方配置的分片策略則更加靈活，常用的使用行表示式配置分片策略，它採用Groovy表示式表示，如: t_user_$->{u_id % 8} 表示t_user表根據u_id模8，而分成8張表，表名稱為 t_user_0 到 t_user_7 。
i）自增主鍵生成策略
通過在客戶端生成自增主鍵替換以資料庫原生自增主鍵的方式，做到分散式主鍵無重複。
2，SQL解析
當Sharding-JDBC接受到一條SQL語句時，會陸續執行 SQL解析 => 查詢優化 => SQL路由 => SQL改寫 => SQL執行 =>結果歸併 ，最終返回執行結果。
SQL解析過程分為詞法解析和語法解析。 詞法解析器用於將SQL拆解為不可再分的原子符號，稱為Token。並根據不同資料庫方言所提供的字典，將其歸類為關鍵字、表示式、字面量和操作符。 再使用語法解析器將SQL轉換為抽象語法樹。
例如，SQL：
SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18
解析之後的為抽象語法樹見下圖：
為了便於理解，抽象語法樹中的關鍵字的Token用綠色表示，變數的Token用紅色表示，灰色表示需要進一步拆分。
最後，通過對抽象語法樹的遍歷去提煉分片所需的上下文，並標記有可能需要SQL改寫(後邊介紹)的位置。 供分片使用的解析上下文包含查詢選擇項（Select Items）、表資訊（Table）、分片條件（Sharding Condition）、自增主鍵資訊（Auto increment Primary Key）、排序資訊（Order By）、分組資訊（Group By）以及分頁資訊（Limit、Rownum、Top）。
3，路由
SQL路由就是把針對邏輯表的資料操作對映到對資料結點操作的過程。
根據解析上下文匹配資料庫和表的分片策略，並生成路由路徑。 對於攜帶分片鍵的SQL，根據分片鍵操作符不同可以劃分為單片路由(分片鍵的操作符是等號)、多片路由(分片鍵的操作符是IN)和範圍路由(分片鍵的操作符是BETWEEN)，不攜帶分片鍵的SQL則採用廣播路由。根據分片鍵進行路由的場景可分為直接路由、標準路由、笛卡爾路由等。
a）標準路由
標準路由是Sharding-Jdbc最為推薦使用的分片方式，它的適用範圍是不包含關聯查詢或僅包含繫結表之間關聯查詢的SQL。 當分片運算子是等於號時，路由結果將落入單庫（表），當分片運算子是BETWEEN或IN時，則路由結果不一定落入唯一的庫（表），因此一條邏輯SQL最終可能被拆分為多條用於執行的真實SQL。 舉例說明，如果按照 order_id 的奇數和偶數進行資料分片，一個單表查詢的SQL如下：
SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
那麼路由的結果應為：
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
可以看到，SQL拆分的數目與單表是一致的。
b）笛卡爾路由
笛卡爾路由是最複雜的情況，它無法根據繫結表的關係定位分片規則，因此非繫結表之間的關聯查詢需要拆解為笛卡爾積組合執行。 如果上個示例中的SQL並未配置繫結表關係，那麼路由的結果應為：
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
笛卡爾路由查詢效能較低，需謹慎使用。
c）全庫表路由
對於不攜帶分片鍵的SQL，則採取廣播路由的方式。根據SQL型別又可以劃分為全庫表路由、全庫路由、全例項路由、單播路由和阻斷路由這5種型別。其中全庫表路由用於處理對資料庫中與其邏輯表相關的所有真實表的操作，主要包括不帶分片鍵的DQL(資料查詢)和DML（資料操縱），以及DDL（資料定義）等。例如：
SELECT * FROM t_order WHERE good_prority IN (1, 10);
會遍歷所有資料庫中的所有表，逐一匹配邏輯表和真實表名，能夠匹配得上則執行。路由後成為：
SELECT * FROM t_order_0 WHERE good_prority IN (1, 10); SELECT * FROM t_order_1 WHERE good_prority IN (1, 10); SELECT * FROM t_order_2 WHERE good_prority IN (1, 10); SELECT * FROM t_order_3 WHERE good_prority IN (1, 10);
4，SQL改寫
SQL改寫用於將邏輯SQL改寫為在真實資料庫中可以正確執行的SQL。
例如：
SELECT order_id FROM t_order WHERE order_id=1; #假設該SQL配置分片鍵order_id，並且order_id=1的情況，將路由至分片表1。那麼改寫之後的SQL應該為 SELECT order_id FROM t_order_1 WHERE order_id=1;
Sharding-JDBC需要在結果歸併時獲取相應資料，但該資料並未能通過查詢的SQL返回。 這種情況主要是針對GROUP BY和ORDER BY。結果歸併時，需要根據 GROUP BY 和 ORDER BY 的欄位項進行分組和排序，但如果原始SQL的選擇項中若並未包含分組項或排序項，則需要對原始SQL進行改寫。
例如：
#如果選擇項中不包含結果歸併時所需的列，則需要進行補列，如以下SQL： SELECT order_id FROM t_order ORDER BY user_id; #原始SQL中並不包含需要在結果歸併中需要獲取的user_id，因此需要對SQL進行補列改寫。 SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;
5，SQL執行
Sharding-JDBC採用一套自動化的執行引擎，負責將路由和改寫完成之後的真實SQL安全且高效傳送到底層資料來源執行。 它不是簡單地將SQL通過JDBC直接傳送至資料來源執行；也並非直接將執行請求放入執行緒池去併發執行。它更關注平衡資料來源連線建立以及記憶體佔用所產生的消耗，以及最大限度地合理利用併發等問題。 執行引擎的目標是自動化的平衡資源控制與執行效率，他能在以下兩種模式自適應切換：
a）記憶體限制模式
使用此模式的前提是，Sharding-JDBC對一次操作所耗費的資料庫連線數量不做限制。 如果實際執行的SQL需要對某資料庫例項中的200張表做操作，則對每張表建立一個新的資料庫連線，並通過多執行緒的方式併發處理，以達成執行效率最大化。
b）連線限制模式
使用此模式的前提是，Sharding-JDBC嚴格控制對一次操作所耗費的資料庫連線數量。 如果實際執行的SQL需要對某資料庫例項中的200張表做操作，那麼只會建立唯一的資料庫連線，並對其200張表序列處理。 如果一次操作中的分片散落在不同的資料庫，仍然採用多執行緒處理對不同庫的操作，但每個庫的每次操作仍然只建立一個唯一的資料庫連線。
View Code
View Code
View Code
View Code
View Code
記憶體限制模式適用於OLAP(聯機分析處理)操作，可以通過放寬對資料庫連線的限制提升系統吞吐量； 連線限制模式適用於OLTP(聯機事務處理)操作，OLTP通常帶有分片鍵，會路由到單一的分片，因此嚴格控制資料庫連線，以保證線上系統資料庫資源能夠被更多的應用所使用，是明智的選擇。
6，結果歸併
將從各個資料節點獲取的多資料結果集，組合成為一個結果集並正確的返回至請求客戶端，稱為結果歸併。
結果歸併從結構劃分可分為流式歸併、記憶體歸併和裝飾者歸併。流式歸併和記憶體歸併是互斥的，裝飾者歸併可以在流式歸併和記憶體歸併之上做進一步的處理。
a）記憶體歸併
很容易理解，他是將所有分片結果集的資料都遍歷並儲存在記憶體中，再通過統一的分組、排序以及聚合等計算之後，再將其封裝成為逐條訪問的資料結果集返回。
b）流式歸併
是指每一次從資料庫結果集中獲取到的資料，都能夠通過遊標逐條獲取的方式返回正確的單條資料，它與資料庫原生的返回結果集的方式最為契合。
c）裝飾者歸併
是對所有的結果集歸併進行統一的功能增強，比如歸併時需要聚合SUM前，在進行聚合計算前，都會通過記憶體歸併或流式歸併查詢出結果集。因此，聚合歸併是在之前介紹的歸併型別之上追加的歸併能力，即裝飾者模式。
三、水平分表
1，建表
#建立資料庫 CREATE DATABASE `order_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; #建表 USE order_db; DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `t_order_2`; CREATE TABLE `t_order_2` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2，新增maven依賴
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency>
3，properties配置
#資料來源 spring.shardingsphere.datasource.names=m1 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://hadoop102:3306/order_db?useUnicode=true spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=123456 # 指定t_order表的資料分佈情況，配置資料節點 m1.t_order_1,m1.t_order_2 spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2} # 指定t_order表的主鍵生成策略為SNOWFLAKE spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE # 指定t_order表的分片策略，分片策略包括分片鍵(order_id)和分片演算法(t_order_$->{order_id % 2 + 1}) 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 + 1}
四、水平分庫
1，建表
建立資料庫：order_db_1，order_db_2，在兩個資料庫均建立表：t_order_1，t_order_2
#建立資料庫 CREATE DATABASE `order_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; #建表 USE order_db_1; DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `t_order_2`; CREATE TABLE `t_order_2` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; #建立資料庫 CREATE DATABASE `order_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; #建表 USE order_db_2; DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `t_order_2`; CREATE TABLE `t_order_2` ( `order_id` BIGINT (20) NOT NULL COMMENT '訂單id', `price` DECIMAL (10, 2) NOT NULL COMMENT '訂單價格', `user_id` BIGINT (20) NOT NULL COMMENT '下單使用者id', `status` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2，maven配置
同上
3，properties配置
#資料來源 spring.shardingsphere.datasource.names=m1,m2 #資料來源m1 連線order_db_1資料庫 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://hadoop102:3306/order_db_1?useUnicode=true spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=123456 #資料來源m2 連線order_db_2資料庫 spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.m2.url=jdbc:mysql://hadoop102:3306/order_db_2?useUnicode=true spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=123456 # 分庫策略，以user_id為分片鍵，分片策略為user_id % 2 + 1，user_id為偶數操作m1資料來源，否則操作m2。 spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = m$->{user_id % 2 + 1} # 指定t_order表的資料分佈情況，配置資料節點 m1.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_2 spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.t_order_$->{1..2} # 指定t_order表的主鍵生成策略為SNOWFLAKE spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE # 指定t_order表的分片策略，分片策略包括分片鍵(order_id)和分片演算法(t_order_$->{order_id % 2 + 1}) 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 + 1}
五、垂直分庫
1，建表
建立垂直的業務表，使用者資訊表：
#建立資料庫 CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; #建表 USE user_db; DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `user_id` BIGINT (20) NOT NULL COMMENT '使用者id', `fullname` VARCHAR (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者姓名', `user_type` CHAR (1) DEFAULT NULL COMMENT '使用者型別', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2，配置properties
application.properties配置：這裡將m0，m1和m2共存了，但是真正使用的是m0
#資料來源 spring.shardingsphere.datasource.names=m0,m1,m2 #資料來源m0 連線user_db資料庫 spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url=jdbc:mysql://hadoop102:3306/user_db?useUnicode=true spring.shardingsphere.datasource.m0.username=root spring.shardingsphere.datasource.m0.password=123456 # t_user分表策略，固定分配至m0的t_user真實表 可將t_user也進行表操作 spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m0.t_user spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
六、公共表
公共表屬於系統中資料量較小，變動少，而且屬於高頻聯合查詢的依賴表。參數列、資料字典表等屬於此型別。可以將這類表在每個資料庫都儲存一份，所有更新操作都同時傳送到所有分庫執行。接下來看一下如何使用Sharding-JDBC實現公共表。
1，建表
需要在所有使用到的資料庫中都建表
#在資料庫 user_db、order_db_1、order_db_2中均要建表 CREATE TABLE `t_dict` ( `dict_id` BIGINT (20) NOT NULL COMMENT '字典id', `type` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典型別', `code` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典編碼', `value` VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值', PRIMARY KEY (`dict_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2，配置properties
# 指定t_dict為公共表 spring.shardingsphere.sharding.broadcast‐tables=t_dict
七、讀寫分離
Sharding-JDBC讀寫分離是根據SQL語義的分析，將讀操作和寫操作分別路由至主庫與從庫。它提供透明化讀寫分離，讓使用方儘量像使用一個資料庫一樣使用主從資料庫叢集。
Sharding-JDBC提供一主多從的讀寫分離配置，可獨立使用，也可配合分庫分表使用，同一執行緒且同一資料庫連線內，如有寫入操作，以後的讀操作均從主庫讀取，用於保證資料一致性。Sharding-JDBC不提供主從資料庫的資料同步功能，需要採用其他機制支援。
1，配置mysql的主從
略
2，配置application
application.properties配置：這裡將m0、s0；定義m0為主伺服器，s0為從伺服器。
#資料來源 主從 spring.shardingsphere.datasource.names=m0,s0 #資料來源m0 連線user_db資料庫 spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.m0.username=root spring.shardingsphere.datasource.m0.password=123456 #資料來源m0 連線user_db資料庫 spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?useUnicode=true&characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.s0.username=root spring.shardingsphere.datasource.s0.password=123456 # 主庫從庫邏輯資料來源定義 ds0為user_db spring.shardingsphere.sharding.master‐slave‐rules.ds0.master-data-source-name=m0 spring.shardingsphere.sharding.master‐slave‐rules.ds0.slave-data-source-names=s0 # t_user分表策略 固定分配至ds0的t_user真實表 spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
八、綜合案例
原始碼：購物商品分庫分表
1，資料庫設計
資料庫設計如下，其中商品與店鋪資訊之間進行了垂直分庫，分為了PRODUCT_DB(商品庫)和STORE_DB(店鋪庫)；商品資訊還進行了垂直分表，分為了商品基本資訊(product_info)和商品描述資訊(product_descript)，地理區域資訊(region)作為公共表，冗餘在兩庫中：
考慮到商品資訊的資料增長性，對PRODUCT_DB(商品庫)進行了水平分庫，分片鍵使用店鋪id，分片策略為店鋪ID%2 + 1，因此商品描述資訊對所屬店鋪ID進行了冗餘；
對商品基本資訊(product_info)和商品描述資訊(product_descript)進行水平分表，分片鍵使用商品id，分片策略為商品ID%2 + 1,並將為這兩個表設定為繫結表，避免笛卡爾積join；
為避免主鍵衝突，ID生成策略採用雪花演算法來生成全域性唯一ID，最終資料庫設計為下圖：
2，建表
建立store_db資料庫(主從資料庫均要執行)，並執行以下指令碼建立表：
CREATE DATABASE store_db; USE store_db; DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `id` bigint(20) NOT NULL COMMENT 'id', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理區域編碼', `region_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理區域名稱', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理區域級別(省、市、縣)', `parent_region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上級地理區域編碼', PRIMARY KEY USING BTREE (`id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `region` VALUES (1, '110000', '北京', 0, NULL); INSERT INTO `region` VALUES (2, '410000', '河南省', 0, NULL); INSERT INTO `region` VALUES (3, '110100', '北京市', 1, '110000'); INSERT INTO `region` VALUES (4, '410100', '鄭州市', 1, '410000'); DROP TABLE IF EXISTS `store_info`; CREATE TABLE `store_info` ( `id` bigint(20) NOT NULL COMMENT 'id', `store_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店鋪名稱', `reputation` int(11) NULL DEFAULT NULL COMMENT '信譽等級', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店鋪所在地', PRIMARY KEY USING BTREE (`id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `store_info` VALUES (1, 'XX零食店', 4, '110100'); INSERT INTO `store_info` VALUES (2, 'XX飲品店', 3, '410100');
建立product_db_1、product_db_2資料庫(主從資料庫均要執行)，並分別執行以下指令碼建立表
DROP TABLE IF EXISTS `product_descript_1`; CREATE TABLE `product_descript_1` ( `id` bigint(20) NOT NULL COMMENT 'id', `product_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬商品id', `descript` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述', `store_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬店鋪id', PRIMARY KEY USING BTREE (`id`), INDEX `FK_Reference_2` USING BTREE(`product_info_id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `product_descript_2`; CREATE TABLE `product_descript_2` ( `id` bigint(20) NOT NULL COMMENT 'id', `product_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬商品id', `descript` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述', `store_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬店鋪id', PRIMARY KEY USING BTREE (`id`), INDEX `FK_Reference_2` USING BTREE(`product_info_id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `product_info_1`; CREATE TABLE `product_info_1` ( `product_info_id` bigint(20) NOT NULL COMMENT 'id', `store_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬店鋪id', `product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名稱', `spec` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '規 格', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '產地', `price` decimal(10, 0) NULL DEFAULT NULL COMMENT '商品價格', `image_url` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品圖片', PRIMARY KEY USING BTREE (`product_info_id`), INDEX `FK_Reference_1` USING BTREE(`store_info_id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `product_info_2`; CREATE TABLE `product_info_2` ( `product_info_id` bigint(20) NOT NULL COMMENT 'id', `store_info_id` bigint(20) NULL DEFAULT NULL COMMENT '所屬店鋪id', `product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名稱', `spec` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '規 格', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '產地', `price` decimal(10, 0) NULL DEFAULT NULL COMMENT '商品價格', `image_url` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品圖片', PRIMARY KEY USING BTREE (`product_info_id`), INDEX `FK_Reference_1` USING BTREE(`store_info_id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `id` bigint(20) NOT NULL COMMENT 'id', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理區域編碼', `region_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理區域名稱', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理區域級別(省、市、縣)', `parent_region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上級地理區域編碼', PRIMARY KEY USING BTREE (`id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `region` VALUES (1, '110000', '北京', 0, NULL); INSERT INTO `region` VALUES (2, '410000', '河南省', 0, NULL); INSERT INTO `region` VALUES (3, '110100', '北京市', 1, '110000'); INSERT INTO `region` VALUES (4, '410100', '鄭州市', 1, '410000');
3，配置主從同步
主庫
# 設定需要同步的資料庫 binlog_do_db=store_db binlog_do_db=product_db_1 binlog_do_db=product_db_2
從庫
#設定需要同步的資料庫 replicate_wild_do_table=store_db.% replicate_wild_do_table=product_db_1.% replicate_wild_do_table=product_db_2.%
4，配置分片策略
application配置：分別配置db_store的主從、水平分庫product_db_1和product_db_2的主從以及邏輯庫(product_db)中的水平分表product_info_1和product_info_2等
#資料來源 spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2 spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/store_db?useUnicode=true spring.shardingsphere.datasource.m0.username = root spring.shardingsphere.datasource.m0.password = 123456 spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/product_db_1?useUnicode=true spring.shardingsphere.datasource.m1.username = root spring.shardingsphere.datasource.m1.password = 123456 spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/product_db_2?useUnicode=true spring.shardingsphere.datasource.m2.username = root spring.shardingsphere.datasource.m2.password = 123456 spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/store_db?useUnicode=true spring.shardingsphere.datasource.s0.username = root spring.shardingsphere.datasource.s0.password = 123456 spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s1.url = jdbc:mysql://localhost:3307/product_db_1?useUnicode=true spring.shardingsphere.datasource.s1.username = root spring.shardingsphere.datasource.s1.password = 123456 spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s2.url = jdbc:mysql://localhost:3307/product_db_2?useUnicode=true spring.shardingsphere.datasource.s2.username = root spring.shardingsphere.datasource.s2.password = 123456 # 主庫從庫邏輯資料來源定義 ds0為store_db spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0 spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0 # 主庫從庫邏輯資料來源定義 ds1為product_db_1 spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1 spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1 # 主庫從庫邏輯資料來源定義 ds2為product_db_2 spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2 spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2 #預設資料庫分庫策略，以store_info_id為分片鍵，定義分片鍵策略為store_info_id % 2 + 1，store_info_id為偶數操作ds1資料來源，否則操作ds2。 spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=store_info_id spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{store_info_id % 2 + 1} # 指定product_info表的資料分佈情況，配置資料節點 ds1.product_info_1,ds1.product_info_2,ds2.product_info_1,ds2.product_info_2 spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2} # 指定product_info表的主鍵生成策略為SNOWFLAKE spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE # 分表策略，指定product_info表的分片策略，分片策略包括分片鍵和分片演算法 spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{product_info_id % 2 + 1} # 指定store_info表的資料分佈情況，配置資料節點 ds0.store_info spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column = id spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression = store_info # 指定product_descript表的資料分佈情況，配置資料節點 ds1.product_descript_1,ds1.product_descript_2,ds2.product_descript_1,ds2.product_descript_2 spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2} # 指定product_descript表的主鍵生成策略為SNOWFLAKE spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE # 分表策略，指定product_descript表的分片策略，分片策略包括分片鍵和分片演算法 spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id % 2 + 1} #繫結表product_descript與product_info 避免出現笛卡爾積 spring.shardingsphere.sharding.binding-tables[0]=product_info,product_descript #設定region為廣播表(公共表) spring.shardingsphere.sharding.broadcast-tables=region
5，分庫分表後的操作
a)查詢商品列表
分頁查詢是業務中最常見的場景，Sharding-jdbc支援常用關聯式資料庫的分頁查詢，不過Sharding-jdbc的分頁功能比較容易讓使用者誤解，使用者通常認為分頁歸併會佔用大量記憶體。 在分散式的場景中，將 LIMIT 10000000 , 10改寫為 LIMIT 0, 10000010 ，才能保證其資料的正確性。 使用者非常容易產生ShardingSphere會將大量無意義的資料載入至記憶體中，造成記憶體溢位風險的錯覺。 其實大部分情況都通過流式歸併獲取資料結果集，因此ShardingSphere會通過結果集的next方法將無需取出的資料全部跳過，並不會將其存入記憶體。
但同時需要注意的是，由於排序的需要，大量的資料仍然需要傳輸到Sharding-Jdbc的記憶體空間。 因此，採用LIMIT這種方式分頁，並非最佳實踐。 由於LIMIT並不能通過索引查詢資料，因此如果可以保證ID的連續性，通過ID進行分頁是比較好的解決方案，例如：
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id; #或者是 SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;
排序功能是由Sharding-jdbc的排序歸併來完成，由於在SQL中存在 ORDER BY 語句，因此每個資料結果集自身是有序的，因此只需要將資料結果集當前遊標指向的資料值進行排序即可。 這相當於對多個有序的陣列進行排序，歸併排序是最適合此場景的排序演算法。
b）分組統計
分組統計也是業務中常見的場景，分組功能的實現由Sharding-jdbc分組歸併完成。分組歸併的情況最為複雜，它分為流式分組歸併和記憶體分組歸併。 流式分組歸併要求SQL的排序項與分組項的欄位必須保持一致，否則只能通過記憶體歸併才能保證其資料的正確性。
舉例說明，假設根據科目分片，表結構中包含考生的姓名（為了簡單起見，不考慮重名的情況）和分數。通過SQL獲取每位考生的總分，可通過如下SQL：
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;
在分組項與排序項完全一致的情況下，取得的資料是連續的，分組所需的資料全數存在於各個資料結果集(分庫或分表中)的當前遊標所指向的資料值，因此可以採用流式歸併。