一、Sharding-JDBC介紹
1，介紹
Sharding-JDBC是當(dāng)當(dāng)網(wǎng)研發(fā)的開源分布式數(shù)據(jù)庫中間件，從 3.0 開始Sharding-JDBC被包含在 Sharding-Sphere中，之后該項(xiàng)目進(jìn)入進(jìn)入Apache孵化器，4.0版本之后的版本為Apache版本。
ShardingSphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計(jì)劃中）這3款相互獨(dú)立的產(chǎn)品組成。 他們均提供標(biāo)準(zhǔn)化的數(shù)據(jù)分片、分布式事務(wù)和數(shù)據(jù)庫治理功能，可適用于如Java同構(gòu)、異構(gòu)語言、容器、云原生等各種多樣化的應(yīng)用場景。
Sharding-JDBC的核心功能為數(shù)據(jù)分片和讀寫分離，通過Sharding-JDBC，應(yīng)用可以透明的使用jdbc訪問已經(jīng)分庫分表、讀寫分離的多個(gè)數(shù)據(jù)源，而不用關(guān)心數(shù)據(jù)源的數(shù)量以及數(shù)據(jù)如何分布。
- 適用于任何基于Java的ORM框架，如： Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
- 基于任何第三方的數(shù)據(jù)庫連接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
- 支持任意實(shí)現(xiàn)JDBC規(guī)范的數(shù)據(jù)庫。目前支持MySQL，Oracle，SQLServer和PostgreSQL。
上圖展示了Sharding-Jdbc的工作方式，使用Sharding-Jdbc前需要人工對數(shù)據(jù)庫進(jìn)行分庫分表，在應(yīng)用程序中加入Sharding-Jdbc的Jar包，應(yīng)用程序通過Sharding-Jdbc操作分庫分表后的數(shù)據(jù)庫和數(shù)據(jù)表，由于Sharding-Jdbc是對Jdbc驅(qū)動(dòng)的增強(qiáng)，使用Sharding-Jdbc就像使用Jdbc驅(qū)動(dòng)一樣，在應(yīng)用程序中是無需指定具體要操作的分庫和分表的。
2，與jdbc性能對比
a）性能損耗測試
服務(wù)器資源充足、并發(fā)數(shù)相同，比較JDBC和Sharding-JDBC性能損耗，Sharding-JDBC相對JDBC損耗不超過7%。
b）性能對比測試
服務(wù)器資源使用到極限，相同的場景JDBC與Sharding-JDBC的吞吐量相當(dāng)。
服務(wù)器資源使用到極限，Sharding-JDBC采用分庫分表后，Sharding-JDBC吞吐量較JDBC不分表有接近2倍的提升。
二、Sharding-JDBC執(zhí)行原理
1，基本概念
a）邏輯表
水平拆分的數(shù)據(jù)表的總稱。例：訂單數(shù)據(jù)表根據(jù)主鍵尾數(shù)拆分為10張表，分別是 t_order_0 、 t_order_1 到t_order_9 ，他們的邏輯表名為 t_order 。
b）真實(shí)表
在分片的數(shù)據(jù)庫中真實(shí)存在的物理表。即上個(gè)示例中的 t_order_0 到 t_order_9 。
c）數(shù)據(jù)節(jié)點(diǎn)
數(shù)據(jù)分片的最小物理單元。由數(shù)據(jù)源名稱和數(shù)據(jù)表組成，例： ds_0.t_order_0 。
d）綁定表
指分片規(guī)則一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片，綁定表之間的分區(qū)鍵完全相同，則此兩張表互為綁定表關(guān)系。綁定表之間的多表關(guān)聯(lián)查詢不會(huì)出現(xiàn)笛卡爾積關(guān)聯(lián)，關(guān)聯(lián)查詢效率將大大提升。舉例說明，如果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）廣播表
指所有的分片數(shù)據(jù)源中都存在的表，表結(jié)構(gòu)和表中的數(shù)據(jù)在每個(gè)數(shù)據(jù)庫中均完全一致。適用于數(shù)據(jù)量不大且需要與海量數(shù)據(jù)的表進(jìn)行關(guān)聯(lián)查詢的場景，例如：字典表。
f）分片鍵
用于分片的數(shù)據(jù)庫字段，是將數(shù)據(jù)庫(表)水平拆分的關(guān)鍵字段。例：將訂單表中的訂單主鍵的尾數(shù)取模分片，則訂單主鍵為分片字段。 SQL中如果無分片字段，將執(zhí)行全路由，性能較差。 除了對單分片字段的支持，Sharding-Jdbc也支持根據(jù)多個(gè)字段進(jìn)行分片。
g）分片算法
通過分片算法將數(shù)據(jù)分片，支持通過 = 、 BETWEEN 和 IN 分片。分片算法需要應(yīng)用方開發(fā)者自行實(shí)現(xiàn)，可實(shí)現(xiàn)的靈活度非常高。包括：精確分片算法 、范圍分片算法 ，復(fù)合分片算法 等。例如：where order_id = ? 將采用精確分片算法，where order_id in (?,?,?)將采用精確分片算法，where order_id BETWEEN ? and ? 將采用范圍分片算法，復(fù)合分片算法用于分片鍵有多個(gè)復(fù)雜情況。
h）分片策略
包含分片鍵和分片算法，由于分片算法的獨(dú)立性，將其獨(dú)立抽離。真正可用于分片操作的是分片鍵 + 分片算法，也就是分片策略。內(nèi)置的分片策略大致可分為尾數(shù)取模、哈希、范圍、標(biāo)簽、時(shí)間等。由用戶方配置的分片策略則更加靈活，常用的使用行表達(dá)式配置分片策略，它采用Groovy表達(dá)式表示，如: t_user_$->{u_id % 8} 表示t_user表根據(jù)u_id模8，而分成8張表，表名稱為 t_user_0 到 t_user_7 。
i）自增主鍵生成策略
通過在客戶端生成自增主鍵替換以數(shù)據(jù)庫原生自增主鍵的方式，做到分布式主鍵無重復(fù)。
2，SQL解析
當(dāng)Sharding-JDBC接受到一條SQL語句時(shí)，會(huì)陸續(xù)執(zhí)行** SQL解析 => 查詢優(yōu)化 => SQL路由 => SQL改寫 => SQL執(zhí)行 =>結(jié)果歸并** ，最終返回執(zhí)行結(jié)果。
SQL解析過程分為詞法解析和語法解析。 詞法解析器用于將SQL拆解為不可再分的原子符號，稱為Token。并根據(jù)不同數(shù)據(jù)庫方言所提供的字典，將其歸類為關(guān)鍵字、表達(dá)式、字面量和操作符。 再使用語法解析器將SQL轉(zhuǎn)換為抽象語法樹。
例如，SQL：
SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18
解析之后的為抽象語法樹見下圖：
為了便于理解，抽象語法樹中的關(guān)鍵字的Token用綠色表示，變量的Token用紅色表示，灰色表示需要進(jìn)一步拆分。
最后，通過對抽象語法樹的遍歷去提煉分片所需的上下文，并標(biāo)記有可能需要SQL改寫(后邊介紹)的位置。 供分片使用的解析上下文包含查詢選擇項(xiàng)（Select Items）、表信息（Table）、分片條件（Sharding Condition）、自增主鍵信息（Auto increment Primary Key）、排序信息（Order By）、分組信息（Group By）以及分頁信息（Limit、Rownum、Top）。
3，路由
SQL路由就是把針對邏輯表的數(shù)據(jù)操作映射到對數(shù)據(jù)結(jié)點(diǎn)操作的過程。
根據(jù)解析上下文匹配數(shù)據(jù)庫和表的分片策略，并生成路由路徑。 對于攜帶分片鍵的SQL，根據(jù)分片鍵操作符不同可以劃分為單片路由(分片鍵的操作符是等號)、多片路由(分片鍵的操作符是IN)和范圍路由(分片鍵的操作符是BETWEEN)，不攜帶分片鍵的SQL則采用廣播路由。根據(jù)分片鍵進(jìn)行路由的場景可分為直接路由、標(biāo)準(zhǔn)路由、笛卡爾路由等。
a）標(biāo)準(zhǔn)路由
標(biāo)準(zhǔn)路由是Sharding-Jdbc最為推薦使用的分片方式，它的適用范圍是不包含關(guān)聯(lián)查詢或僅包含綁定表之間關(guān)聯(lián)查詢的SQL。 當(dāng)分片運(yùn)算符是等于號時(shí)，路由結(jié)果將落入單庫（表），當(dāng)分片運(yùn)算符是BETWEEN或IN時(shí)，則路由結(jié)果不一定落入唯一的庫（表），因此一條邏輯SQL最終可能被拆分為多條用于執(zhí)行的真實(shí)SQL。 舉例說明，如果按照 order_id 的奇數(shù)和偶數(shù)進(jìn)行數(shù)據(jù)分片，一個(gè)單表查詢的SQL如下：
SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
那么路由的結(jié)果應(yīng)為：
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拆分的數(shù)目與單表是一致的。
b）笛卡爾路由
笛卡爾路由是最復(fù)雜的情況，它無法根據(jù)綁定表的關(guān)系定位分片規(guī)則，因此非綁定表之間的關(guān)聯(lián)查詢需要拆解為笛卡爾積組合執(zhí)行。 如果上個(gè)示例中的SQL并未配置綁定表關(guān)系，那么路由的結(jié)果應(yīng)為：
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);
笛卡爾路由查詢性能較低，需謹(jǐn)慎使用。
c）全庫表路由
對于不攜帶分片鍵的SQL，則采取廣播路由的方式。根據(jù)SQL類型又可以劃分為全庫表路由、全庫路由、全實(shí)例路由、單播路由和阻斷路由這5種類型。其中全庫表路由用于處理對數(shù)據(jù)庫中與其邏輯表相關(guān)的所有真實(shí)表的操作，主要包括不帶分片鍵的DQL(數(shù)據(jù)查詢)和DML（數(shù)據(jù)操縱），以及DDL（數(shù)據(jù)定義）等。例如：
SELECT * FROM t_order WHERE good_prority IN (1, 10);
會(huì)遍歷所有數(shù)據(jù)庫中的所有表，逐一匹配邏輯表和真實(shí)表名，能夠匹配得上則執(zhí)行。路由后成為：
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改寫為在真實(shí)數(shù)據(jù)庫中可以正確執(zhí)行的SQL。
例如：
SELECT order_id FROM t_order WHERE order_id=1;
#假設(shè)該SQL配置分片鍵order_id，并且order_id=1的情況，將路由至分片表1。那么改寫之后的SQL應(yīng)該為
SELECT order_id FROM t_order_1 WHERE order_id=1;
Sharding-JDBC需要在結(jié)果歸并時(shí)獲取相應(yīng)數(shù)據(jù)，但該數(shù)據(jù)并未能通過查詢的SQL返回。 這種情況主要是針對GROUP BY和ORDER BY。結(jié)果歸并時(shí)，需要根據(jù) GROUP BY 和 ORDER BY 的字段項(xiàng)進(jìn)行分組和排序，但如果原始SQL的選擇項(xiàng)中若并未包含分組項(xiàng)或排序項(xiàng)，則需要對原始SQL進(jìn)行改寫。
例如：
#如果選擇項(xiàng)中不包含結(jié)果歸并時(shí)所需的列，則需要進(jìn)行補(bǔ)列，如以下SQL：
SELECT order_id FROM t_order ORDER BY user_id;
#原始SQL中并不包含需要在結(jié)果歸并中需要獲取的user_id，因此需要對SQL進(jìn)行補(bǔ)列改寫。
SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;
5，SQL執(zhí)行
Sharding-JDBC采用一套自動(dòng)化的執(zhí)行引擎，負(fù)責(zé)將路由和改寫完成之后的真實(shí)SQL安全且高效發(fā)送到底層數(shù)據(jù)源執(zhí)行。 它不是簡單地將SQL通過JDBC直接發(fā)送至數(shù)據(jù)源執(zhí)行；也并非直接將執(zhí)行請求放入線程池去并發(fā)執(zhí)行。它更關(guān)注平衡數(shù)據(jù)源連接創(chuàng)建以及內(nèi)存占用所產(chǎn)生的消耗，以及最大限度地合理利用并發(fā)等問題。 執(zhí)行引擎的目標(biāo)是自動(dòng)化的平衡資源控制與執(zhí)行效率，他能在以下兩種模式自適應(yīng)切換：
a）內(nèi)存限制模式
使用此模式的前提是，Sharding-JDBC對一次操作所耗費(fèi)的數(shù)據(jù)庫連接數(shù)量不做限制。 如果實(shí)際執(zhí)行的SQL需要對某數(shù)據(jù)庫實(shí)例中的200張表做操作，則對每張表創(chuàng)建一個(gè)新的數(shù)據(jù)庫連接，并通過多線程的方式并發(fā)處理，以達(dá)成執(zhí)行效率最大化。
b）連接限制模式
使用此模式的前提是，Sharding-JDBC嚴(yán)格控制對一次操作所耗費(fèi)的數(shù)據(jù)庫連接數(shù)量。 如果實(shí)際執(zhí)行的SQL需要對某數(shù)據(jù)庫實(shí)例中的200張表做操作，那么只會(huì)創(chuàng)建唯一的數(shù)據(jù)庫連接，并對其200張表串行處理。 如果一次操作中的分片散落在不同的數(shù)據(jù)庫，仍然采用多線程處理對不同庫的操作，但每個(gè)庫的每次操作仍然只創(chuàng)建一個(gè)唯一的數(shù)據(jù)庫連接。
內(nèi)存限制模式適用于OLAP(聯(lián)機(jī)分析處理)操作，可以通過放寬對數(shù)據(jù)庫連接的限制提升系統(tǒng)吞吐量； 連接限制模式適用于OLTP(聯(lián)機(jī)事務(wù)處理)操作，OLTP通常帶有分片鍵，會(huì)路由到單一的分片，因此嚴(yán)格控制數(shù)據(jù)庫連接，以保證在線系統(tǒng)數(shù)據(jù)庫資源能夠被更多的應(yīng)用所使用，是明智的選擇。
6，結(jié)果歸并
將從各個(gè)數(shù)據(jù)節(jié)點(diǎn)獲取的多數(shù)據(jù)結(jié)果集，組合成為一個(gè)結(jié)果集并正確的返回至請求客戶端，稱為結(jié)果歸并。
結(jié)果歸并從結(jié)構(gòu)劃分可分為流式歸并、內(nèi)存歸并和裝飾者歸并。流式歸并和內(nèi)存歸并是互斥的，裝飾者歸并可以在流式歸并和內(nèi)存歸并之上做進(jìn)一步的處理。
a）內(nèi)存歸并
很容易理解，他是將所有分片結(jié)果集的數(shù)據(jù)都遍歷并存儲(chǔ)在內(nèi)存中，再通過統(tǒng)一的分組、排序以及聚合等計(jì)算之后，再將其封裝成為逐條訪問的數(shù)據(jù)結(jié)果集返回。
b）流式歸并
是指每一次從數(shù)據(jù)庫結(jié)果集中獲取到的數(shù)據(jù)，都能夠通過游標(biāo)逐條獲取的方式返回正確的單條數(shù)據(jù)，它與數(shù)據(jù)庫原生的返回結(jié)果集的方式最為契合。
c）裝飾者歸并
是對所有的結(jié)果集歸并進(jìn)行統(tǒng)一的功能增強(qiáng)，比如歸并時(shí)需要聚合SUM前，在進(jìn)行聚合計(jì)算前，都會(huì)通過內(nèi)存歸并或流式歸并查詢出結(jié)果集。因此，聚合歸并是在之前介紹的歸并類型之上追加的歸并能力，即裝飾者模式。
三、水平分表
1，建表
#創(chuàng)建數(shù)據(jù)庫
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
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配置
#數(shù)據(jù)源
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表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn) 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，建表
創(chuàng)建數(shù)據(jù)庫：order_db_1，order_db_2，在兩個(gè)數(shù)據(jù)庫均建立表：t_order_1，t_order_2
#創(chuàng)建數(shù)據(jù)庫
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
#創(chuàng)建數(shù)據(jù)庫
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
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 '訂單價(jià)格',
`user_id` BIGINT (20) NOT NULL COMMENT '下單用戶id',
`status` VARCHAR (50) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '訂單狀態(tài)',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2，maven配置
同上
3，properties配置
application.properties配置
#數(shù)據(jù)源
spring.shardingsphere.datasource.names=m1,m2
#數(shù)據(jù)源m1 連接order_db_1數(shù)據(jù)庫
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
#數(shù)據(jù)源m2 連接order_db_2數(shù)據(jù)庫
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為偶數(shù)操作m1數(shù)據(jù)源，否則操作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表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn) 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，建表
建立垂直的業(yè)務(wù)表，用戶信息表：
#創(chuàng)建數(shù)據(jù)庫
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
#數(shù)據(jù)源
spring.shardingsphere.datasource.names=m0,m1,m2
#數(shù)據(jù)源m0 連接user_db數(shù)據(jù)庫
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真實(shí)表 可將t_user也進(jìn)行表操作
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
六、公共表
公共表屬于系統(tǒng)中數(shù)據(jù)量較小，變動(dòng)少，而且屬于高頻聯(lián)合查詢的依賴表。參數(shù)表、數(shù)據(jù)字典表等屬于此類型?？梢詫⑦@類表在每個(gè)數(shù)據(jù)庫都保存一份，所有更新操作都同時(shí)發(fā)送到所有分庫執(zhí)行。接下來看一下如何使用Sharding-JDBC實(shí)現(xiàn)公共表。
1，建表
需要在所有使用到的數(shù)據(jù)庫中都建表
#在數(shù)據(jù)庫 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讀寫分離是根據(jù)SQL語義的分析，將讀操作和寫操作分別路由至主庫與從庫。它提供透明化讀寫分離，讓使用方盡量像使用一個(gè)數(shù)據(jù)庫一樣使用主從數(shù)據(jù)庫集群。
Sharding-JDBC提供一主多從的讀寫分離配置，可獨(dú)立使用，也可配合分庫分表使用，同一線程且同一數(shù)據(jù)庫連接內(nèi)，如有寫入操作，以后的讀操作均從主庫讀取，用于保證數(shù)據(jù)一致性。Sharding-JDBC不提供主從數(shù)據(jù)庫的數(shù)據(jù)同步功能，需要采用其他機(jī)制支持。
1，配置mysql的主從
略
2，配置application
application.properties配置：這里將m0、s0；定義m0為主服務(wù)器，s0為從服務(wù)器。
#數(shù)據(jù)源 主從
spring.shardingsphere.datasource.names=m0,s0
#數(shù)據(jù)源m0 連接user_db數(shù)據(jù)庫
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
#數(shù)據(jù)源m0 連接user_db數(shù)據(jù)庫
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
# 主庫從庫邏輯數(shù)據(jù)源定義 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真實(shí)表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
八、綜合案例
源碼：購物商品分庫分表
1，數(shù)據(jù)庫設(shè)計(jì)
數(shù)據(jù)庫設(shè)計(jì)如下，其中商品與店鋪信息之間進(jìn)行了垂直分庫，分為了PRODUCT_DB(商品庫)和STORE_DB(店鋪庫)；商品信息還進(jìn)行了垂直分表，分為了商品基本信息(product_info)和商品描述信息(product_descript)，地理區(qū)域信息(region)作為公共表，冗余在兩庫中：
考慮到商品信息的數(shù)據(jù)增長性，對PRODUCT_DB(商品庫)進(jìn)行了水平分庫，分片鍵使用店鋪id，分片策略為店鋪ID%2 + 1，因此商品描述信息對所屬店鋪ID進(jìn)行了冗余；
對商品基本信息(product_info)和商品描述信息(product_descript)進(jìn)行水平分表，分片鍵使用商品id，分片策略為商品ID%2 + 1,并將為這兩個(gè)表設(shè)置為綁定表，避免笛卡爾積join；
為避免主鍵沖突，ID生成策略采用雪花算法來生成全局唯一ID，最終數(shù)據(jù)庫設(shè)計(jì)為下圖：
2，建表
創(chuàng)建store_db數(shù)據(jù)庫(主從數(shù)據(jù)庫均要執(zhí)行)，并執(zhí)行以下腳本創(chuàng)建表：
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 '地理區(qū)域編碼',
`region_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理區(qū)域名稱',
`level` tinyint(1) NULL DEFAULT NULL COMMENT '地理區(qū)域級別(省、市、縣)',
`parent_region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上級地理區(qū)域編碼',
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 '信譽(yù)等級',
`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');
3，配置主從同步
主庫
# 設(shè)置需要同步的數(shù)據(jù)庫
binlog_do_db=store_db
binlog_do_db=product_db_1
binlog_do_db=product_db_2
從庫
#設(shè)置需要同步的數(shù)據(jù)庫
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等
#數(shù)據(jù)源
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
# 主庫從庫邏輯數(shù)據(jù)源定義 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
# 主庫從庫邏輯數(shù)據(jù)源定義 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
# 主庫從庫邏輯數(shù)據(jù)源定義 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
#默認(rèn)數(shù)據(jù)庫分庫策略，以store_info_id為分片鍵，定義分片鍵策略為store_info_id % 2 + 1，store_info_id為偶數(shù)操作ds1數(shù)據(jù)源，否則操作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表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn) 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表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn) 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表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn) 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 避免出現(xiàn)笛卡爾積
spring.shardingsphere.sharding.binding-tables[0]=product_info,product_descript
#設(shè)置region為廣播表(公共表)
spring.shardingsphere.sharding.broadcast-tables=region
5，分庫分表后的操作
a)查詢商品列表
分頁查詢是業(yè)務(wù)中最常見的場景，Sharding-jdbc支持常用關(guān)系數(shù)據(jù)庫的分頁查詢，不過Sharding-jdbc的分頁功能比較容易讓使用者誤解，用戶通常認(rèn)為分頁歸并會(huì)占用大量內(nèi)存。 在分布式的場景中，將 LIMIT 10000000 , 10改寫為 LIMIT 0, 10000010 ，才能保證其數(shù)據(jù)的正確性。 用戶非常容易產(chǎn)生ShardingSphere會(huì)將大量無意義的數(shù)據(jù)加載至內(nèi)存中，造成內(nèi)存溢出風(fēng)險(xiǎn)的錯(cuò)覺。 其實(shí)大部分情況都通過流式歸并獲取數(shù)據(jù)結(jié)果集，因此ShardingSphere會(huì)通過結(jié)果集的next方法將無需取出的數(shù)據(jù)全部跳過，并不會(huì)將其存入內(nèi)存。
但同時(shí)需要注意的是，由于排序的需要，大量的數(shù)據(jù)仍然需要傳輸?shù)?strong>Sharding-Jdbc的內(nèi)存空間。 因此，采用LIMIT這種方式分頁，并非最佳實(shí)踐。 由于LIMIT并不能通過索引查詢數(shù)據(jù)，因此如果可以保證ID的連續(xù)性，通過ID進(jìn)行分頁是比較好的解決方案，例如：
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 語句，因此每個(gè)數(shù)據(jù)結(jié)果集自身是有序的，因此只需要將數(shù)據(jù)結(jié)果集當(dāng)前游標(biāo)指向的數(shù)據(jù)值進(jìn)行排序即可。 這相當(dāng)于對多個(gè)有序的數(shù)組進(jìn)行排序，歸并排序是最適合此場景的排序算法。
b）分組統(tǒng)計(jì)
分組統(tǒng)計(jì)也是業(yè)務(wù)中常見的場景，分組功能的實(shí)現(xiàn)由Sharding-jdbc分組歸并完成。分組歸并的情況最為復(fù)雜，它分為流式分組歸并和內(nèi)存分組歸并。 流式分組歸并要求SQL的排序項(xiàng)與分組項(xiàng)的字段必須保持一致，否則只能通過內(nèi)存歸并才能保證其數(shù)據(jù)的正確性。
舉例說明，假設(shè)根據(jù)科目分片，表結(jié)構(gòu)中包含考生的姓名（為了簡單起見，不考慮重名的情況）和分?jǐn)?shù)。通過SQL獲取每位考生的總分，可通過如下SQL：
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;
在分組項(xiàng)與排序項(xiàng)完全一致的情況下，取得的數(shù)據(jù)是連續(xù)的，分組所需的數(shù)據(jù)全數(shù)存在于各個(gè)數(shù)據(jù)結(jié)果集(分庫或分表中)的當(dāng)前游標(biāo)所指向的數(shù)據(jù)值，因此可以采用流式歸并。
作者：MXC肖某某
原文鏈接：https://www.cnblogs.com/bbgs-xc/p/14319305.html
