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目录下创建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 文件中，具体可参考配置规则。
按照官方文档写一个按用户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 目录下创建 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/
分片规则和数据源配置
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:
# 分库规则
# 语法参考
# https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/inline-expression/
actualDataNodes: orders_0${1..4}.orderlist
# 可以每个表都配置分库规则，也可以只配置一个默认的分库规则
databaseStrategy:
standard:
shardingColumn: taobao_user_id #分库字段
shardingAlgorithmName: db_modulo #分库规则名称
# tableStrategy:
# standard:
# 分表字段
# shardingColumn: created
# 分表规则名称
# shardingAlgorithmName: tables_yyyymm
orderdetail:
actualDataNodes: orders_0${1..4}.orderdetail
# tableStrategy:
# standard:
# shardingColumn: created
# shardingAlgorithmName: tables_yyyymm
# 绑定规则列表
bindingTables:
- orderlist,orderdetail
# 默认分库规则
defaultDatabaseStrategy:
standard:
shardingColumn: taobao_user_id #分库字段
shardingAlgorithmName: db_modulo #分库规则名称
# 默认分表规则
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. 性能调优
通过修改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
