1. 準備
官網下載指定版本二進制包
https://shardingsphere.apache.org/document/current/cn/downloads/
至少2G內存的centos7服務器
下載MYSQL依賴
https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
2. 安裝ShardingSphere-Proxy
將下載好的二進制包上傳到服務器
解壓
tar -zxvf /opt/software/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz -C /opt/module
重命名
mv /opt/module/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /opt/module/shardingsphere-5.1.0-proxy-bin
將MySQL依賴上傳到ext-lib目錄下
cd /opt/module/shardingsphere-5.1.0-proxy-bin
mkdir ext-lib
# 上傳操作省略
3. 編寫自定義算法
官方的文檔
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/startup/bin/
- 實現
ShardingAlgorithm接口定義的算法實現類。 - 在項目
resources目錄下創(chuàng)建META-INF/services目錄绽媒。 - 在
META-INF/services目錄下新建文件org.apache.shardingsphere.sharding.spi.ShardingAlgorithm - 將實現類的絕對路徑寫入至文件
org.apache.shardingsphere.sharding.spi.ShardingAlgorithm - 將上述 Java 文件打包成 jar 包页慷。
- 將上述 jar 包拷貝至 ShardingSphere-Proxy 解壓后的
ext-lib/目錄。 - 將上述自定義算法實現類的 Java 文件引用配置在 YAML 文件中，具體可參考配置規(guī)則署隘。
按照官方文檔寫一個按用戶ID取模指定數據庫例子
新建Maven工程
pom.xml添加以下依賴
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-sharding-api</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>compile</scope>
</dependency>
</dependencies>
實現 ShardingAlgorithm 接口定義的算法實現類。
新建類ShardingDatabaseModuloAlgorithm實現StandardShardingAlgorithm接口
package com.demo.order_sharding;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.util.Collection;
/**
* 按照取模分庫
*/
public class ShardingDatabaseModuloAlgorithm<T extends Comparable<?>> implements StandardShardingAlgorithm<T> {
/**
* 當條件為單個值時進入
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<T> preciseShardingValue) {
Long value = Long.valueOf(preciseShardingValue.getValue().toString());
Long mo = (value % collection.size() + 1);
String db_suffix;
if (mo < 10) {
db_suffix = "_0" + mo;
} else {
db_suffix = "_" + mo;
}
for (String each : collection) {
if (each.endsWith(db_suffix)) {
return each;
}
}
throw new UnsupportedOperationException("不支持的庫" + value);
}
/**
* 當條件為范圍時進入
*/
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<T> rangeShardingValue) {
return collection;
}
@Override
public void init() {
System.out.println("進入init");
}
/**
* 算法類型名稱淆院，可自定義
*/
@Override
public String getType() {
return "STANDDARD_DB_MODULO";
}
}
在項目 resources 目錄下創(chuàng)建 META-INF/services 目錄此再。
在 META-INF/services 目錄下新建文件 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
將實現類的絕對路徑寫入至文件 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
打包成jar包
上傳至/opt/module/shardingsphere-5.1.0-bin/ext-lib
4. YAML配置
官方說明
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/startup/bin/
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/
分片規(guī)則和數據源配置
ShardingSphere-Proxy 支持多邏輯數據源昔搂，每個以 config- 前綴命名的 YAML 配置文件，即為一個邏輯數據源引润。
在/opt/module/shardingsphere-5.1.0-bin/conf中新建一個文件config-sharding-orders.yaml
schemaName: orders
dataSources:
orders_01:
url: jdbc:mysql://127.0.0.1:3306/orders_01?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
username: demo
password: demo123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 100
minPoolSize: 1
orders_02:
url: jdbc:mysql://127.0.0.1:3306/orders_02?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
username: demo
password: demo123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 100
minPoolSize: 1
orders_03:
url: jdbc:mysql://127.0.0.1:3306/orders_03?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
username: demo
password: demo123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 100
minPoolSize: 1
orders_04:
url: jdbc:mysql://127.0.0.1:3306/orders_04?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
username: demo
password: demo123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 100
minPoolSize: 1
rules:
- !SHARDING
tables:
# 邏輯表名
orderlist:
# 分庫規(guī)則
# 語法參考
# https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/inline-expression/
actualDataNodes: orders_0${1..4}.orderlist
# 可以每個表都配置分庫規(guī)則巩趁，也可以只配置一個默認的分庫規(guī)則
databaseStrategy:
standard:
shardingColumn: taobao_user_id #分庫字段
shardingAlgorithmName: db_modulo #分庫規(guī)則名稱
# tableStrategy:
# standard:
# 分表字段
# shardingColumn: created
# 分表規(guī)則名稱
# shardingAlgorithmName: tables_yyyymm
orderdetail:
actualDataNodes: orders_0${1..4}.orderdetail
# tableStrategy:
# standard:
# shardingColumn: created
# shardingAlgorithmName: tables_yyyymm
# 綁定規(guī)則列表
bindingTables:
- orderlist,orderdetail
# 默認分庫規(guī)則
defaultDatabaseStrategy:
standard:
shardingColumn: taobao_user_id #分庫字段
shardingAlgorithmName: db_modulo #分庫規(guī)則名稱
# 默認分表規(guī)則
defaultTableStrategy:
none:
# 分片算法配置
shardingAlgorithms:
db_modulo:
type: STANDDARD_DB_MODULO
權限配置
修改/opt/module/shardingsphere-5.1.0-bin/conf下的server.yaml
rules:
- !AUTHORITY
users:
# https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/yaml-config/authentication/
# 設置賬號密碼
# - root@:root
- orders@:123456
provider:
# https://shardingsphere.apache.org/document/current/cn/dev-manual/proxy/
# ALL_PRIVILEGES_PERMITTED
# SCHEMA_PRIVILEGES_PERMITTED
type: SCHEMA_PRIVILEGES_PERMITTED
props:
# 對賬號指定表名
user-schema-mappings: orders@=orders
# https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/yaml-config/props/
props:
sql-show: false
5. 啟動服務
運行 /opt/module/shardingsphere-5.1.0-bin/bin下的start.sh
cd /opt/module/shardingsphere-5.1.0-bin
bin/start.sh 3080 # 3080為端口號
使用MYSQL客戶端連接試試
成功
6. 性能調優(yōu)
通過修改start.sh內的JVM參數進行內存的配置
修改server.yml中props.proxy-frontend-max-connections參數增大并行SQL數
7. 常用的DistSQL
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/distsql/usage/sharding-rule/
例如：
預覽實際 SQL
語法： PREVIEW SQL
SQL語句： PREVIEW SELECT * FROM t_order
設置屬性值
語法：SET VARIABLE proxy_property_name = xx
SQL語句： SET VARIABLE sql_show = true 設置sql寫入到日志
查詢所有屬性
SHOW ALL VARIABLES
查詢單個屬性
SHOW VARIABLE sql_show
查看當前模式
SHOW INSTANCE MODE
8. 踩得坑
StandardShardingAlgorithm<T>實現這個接口時如果指定了泛型的類型可能會遇到類型轉化的錯
例如：StandardShardingAlgorithm<Long>
當SQL語句為
select * from orderlist where taobao_user_id = 1
會報異常 java.lang.integer cannot be cast to java.lang.long
只有數值超過Integer范圍時才不會報錯
總結
寫這篇文章前，找了很多的資料都沒有說自定義接口的每個方法的作用淳附，完全是靠試出來的议慰，官方文檔寫的對新手太不友好了，很多文檔都得靠一個個目錄去翻奴曙，東西確實不多别凹，但是很多示例不寫就很浪費時間自己去琢磨
擴展閱讀
分片策略可以看下面文章了解
https://zhuanlan.zhihu.com/p/272629526
