Sharding-Sphere:Sharding-Proxy分库分表

Sharding-Sphere说明

Sharding-Sphere提供3款产品,以下是其中两款

  • Sharding-JDBC 分库分表、读写分离(参考前两篇文章)
  • Sharding-Proxy 分库分表、读写分离,它和mycat类似,属于中间件代理层,它类似一个数据库,代理后面的分库分表的多个数据库,它屏蔽了后端多个数据库的复杂性,应用开发时直接连接 Sharding-Proxy 即可

Sharding-JDBC与Sharding-Proxy对比

  • 如果你的应用只读写分离、或少量的分出了几个库,使用Sharding-JDBC即可,简单、方便
  • 如果你的应用有进行服务化,且有很多服务,每个服务连接所有数据库,以MySQL为例,分库分表后有20个数据库,有100个服务需要连接,每个服务的连接池设置10个连接,那么每个MySQL实例将要提供 20 * 100 * 10 = 20000 个连接,MySQL 默认连接数是 100,最大连接数是 16384,那么连接数不够用,如果你购买的云数据库,只能升级配置,来提供更多的连接数,将来你的服务更多怎么办,使用proxy代理,应用服务只要连接Sharding-Proxy代理层来解决连接数问题,但代理会有点性能损失
  • Sharding-Proxy连接数消耗低,可以支持很多服务进行连接,支持任意开发语言,但是会有一定性能损耗,因为多了一层代理

Sharding-JDBC与Sharding-Proxy对比

Sharding-Proxy架构图

Sharding-Proxy架构图

安装、运行Sharding-Proxy

(1)下载Sharding-Proxy:https://shardingsphere.apache.org/document/current/cn/downloads/

(2)按使用手册,配置、启动Sharding-Proxy:https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/usage/

(3)上传服务器,解压,进入conf目录,有2个重要的配置文件:server.yamlconfig-sharding.yaml

(4)配置server.yaml,把下面的配置的#注释打开,改为符合自己的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 应用连接 Sharding-Proxy 时需要的账户和密码
authentication:
username: root
password: root

props:
max.connections.size.per.query: 1
acceptor.size: 4 # CPU核心数 * 2
executor.size: 2 # CPU核心数
proxy.frontend.flush.threshold: 128 # The default value is 128.
# LOCAL: Proxy will run with LOCAL transaction.
# XA: Proxy will run with XA transaction.
# BASE: Proxy will run with B.A.S.E transaction.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
sql.show: false

(5)配置config-sharding.yaml,把最下面MySQL的注释改为符合自己的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 应用连接 Sharding-Proxy 的数据库名称
schemaName: sharding_db

dataSources:
ds0:
url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 30
ds1:
url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 30

shardingRule:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_id
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: member_id
algorithmExpression: ds${member_id % 2}
defaultTableStrategy:
none:

(6)启动 Sharding-Proxy ,如果是windows系统,双击start.bat,启动后sharding-proxy目录会有日志文件logs/stdout.log

1
2
3
4
5
6
7
8
cd /usr/local/sharding-proxy/bin
sh start.sh

#默认端口 3307 ,可自定义端口使用
sh start.sh 3308

#查看日志观察启动是否成功
tail -f /usr/local/sharding-proxy/logs/stdout.log

(7)使用 Navicat Mysql 客户端连接 Sharding-Proxy,然后创建表,该代理层会根据分库分表规则自动在后端对应的分库中创建表

SpringBoot接入 Sharing-Proxy

(1)新建项目,引入依赖,以spring-boot-starter-data-jpa操作sharding-proxy为例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>

(2)修改application.properties,数据库连接地址为sharding-proxy代理地址

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
server.port=8073
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

# sharding-proxy 上配置的地址、帐号、密码
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/sharding_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

# Hikari will use the above plus the following to setup connection pooling
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=25
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=HikariCP
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1

(3)JPA实现增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Data
@Entity
@Table(name = "t_order")
public class Order {

@Id
@Column(name = "order_id")
private Long orderId;

@Column(name = "member_id")
private Long memberId;

@Column(name = "order_code")
private String orderCode;

@Column(name = "order_amount")
private String orderAmount;

@Column(name = "status")
private String status;

@Column(name = "create_time")
private Date createTime;
}


@Repository
public inte
rface OrderRepository extends JpaRepository<Order , Long> {

}

(4)OrderController提供数据操作接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@RestController
@RequestMapping("order")
public class OrderController {

@Autowired private OrderRepository orderRepository;

@RequestMapping("add")
public Order add(){
Order order = new Order();
order.setOrderId(IdWorker.getLongId());
order.setMemberId(IdWorker.getLongId());
order.setCreateTime(new Date());
order.setOrderAmount("330.2");
order.setOrderCode("abc");
order.setStatus("1");
return orderRepository.save(order);
}

@GetMapping("findById/{orderId}")
public Order findById(@PathVariable long orderId){
return orderRepository.findById(orderId).get();
}

@GetMapping("findAll")
public List<Order> findAll(){
return orderRepository.findAll();
}
}

(5)启动项目,验证这些接口,与对应的数据是否按分库分表规则插入

新增order,http://localhost:8073/order/add ,多新增几条数据,在sharding-proxy代理层查询order表,可以看到新增结果

查询接口:http://localhost:8073/order/findById/xxxxxx
查询所有:http://localhost:8073/order/findAll

(6)单独连接后端的ds0ds1数据库,查看刚刚新增的数据是否分到这两个库中,可以看到确实实现分库分表,使用 sharding-proxy 代理进行分库分表验证成功

参考:https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business/sharding-proxy

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :