0%

使用sharding-jdbc实现mysql的分库分表并主从读写分离

简介

之前的文章中提到了使用sharding-jdbc进行读写分离和分库分表,本文将使用sharding-jdbc并且使用-《使用sharding-jdbc实现mysql的分库分表》这篇文章中的代码,将分库分表和读写分离结结合起来。

配置mysql从库

具体步骤依照《hive mysql元数据表单的主从备份》一文配置好主备,举例一下是主库配置,复制主库的test,user_1,user_2数据库到

1
2
3
4
5
6
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-do-db=user_1
binlog-do-db=user_2
log-slave-updates=1

从库配置,复制主库的test,user_1,user_2数据库

1
2
3
4
5
server-id=2
#read_only=1
replicate-do-db=test
replicate-do-db=user_1
replicate-do-db=user_2

修改application.properties配置文件

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
spring.shardingsphere.datasource.names=master0,master1,master0slave0,master1slave0

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.171.76:3306/user_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=Liush123!@#
spring.shardingsphere.datasource.master0.minimum-idle=10
spring.shardingsphere.datasource.master0.maximum-pool-size=10
spring.shardingsphere.datasource.master0.pool-name=masterHikariCP


spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.171.76:3306/user_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=Liush123!@#
spring.shardingsphere.datasource.master1.minimum-idle=10
spring.shardingsphere.datasource.master1.maximum-pool-size=10
spring.shardingsphere.datasource.master1.pool-name=masterHikariCP2




spring.shardingsphere.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0slave0.jdbc-url=jdbc:mysql://192.168.171.77:3306/user_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master0slave0.username=root
spring.shardingsphere.datasource.master0slave0.password=Liush123!@#
spring.shardingsphere.datasource.master0slave0.minimum-idle=10
spring.shardingsphere.datasource.master0slave0.maximum-pool-size=10
spring.shardingsphere.datasource.master0slave0.pool-name=slaveHikariCP




spring.shardingsphere.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1slave0.jdbc-url=jdbc:mysql://192.168.171.77:3306/user_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master1slave0.username=root
spring.shardingsphere.datasource.master1slave0.password=Liush123!@#
spring.shardingsphere.datasource.master1slave0.minimum-idle=10
spring.shardingsphere.datasource.master1slave0.maximum-pool-size=10
spring.shardingsphere.datasource.master1slave0.pool-name=slaveHikariCP2




spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id_
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=master$->{user_id_ % 2}
#如果不#如果不设置此参数,会产生笛卡尔积查询,使效率大大减慢(表间绑定,比如这里相同的orderID的t_order和t_order_item会划分到同一分片下,这样就不会产生笛卡尔积查询)
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_address



spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=master$->{0..1}.t_user_$->{0..1}
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_$->{user_id_ % 2}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id_
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.props.worker.id=123


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=master$->{0..1}.t_order_$->{0..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}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id_
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=master$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id_
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id_ % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id_
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123

# 显示执行sql
spring.shardingsphere.props.sql.show=true




## 注意这段代码和官方提供的日志不一致,官方提供的配置文件中master0,master1并没有使用,spring.shardingsphere.datasource.names中配置的源,但是实测中不使用spring.shardingsphere.datasource.names中配置的源配置的数据源会报 org.apache.shardingsphere.core.exception.ShardingException: Cannot find data source in sharding rule, invalid actual data node
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=master0slave0
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=master1slave0

测试方法

执行使用sharding-jdbc实现mysql的分库分表中的findOrderItem方法

1
2
@Select("select item_name_ from t_order t1 inner join t_order_item t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( #{orderId1},#{orderId2} )")
List<String> findOrderItem(@Param("orderId1")long orderId1,@Param("orderId2")long orderId2);

查看打印代码,发现所有的读都已经分发到配置的读节点中了

1
2
2019-10-14 09:50:04.045  INFO 12276 --- [           main] ShardingSphere-SQL                       : Actual SQL: master0slave0 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]
2019-10-14 09:50:04.046 INFO 12276 --- [ main] ShardingSphere-SQL : Actual SQL: master1slave0 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]

事务

本地事务

在不开启任何分布式事务管理器的前提下,让每个数据节点各自管理自己的事务。 它们之间没有协调以及通信的能力,也并不互相知晓其他数据节点事务的成功与否。 本地事务在性能方面无任何损耗,但在强一致性以及最终一致性方面则力不从心。

  • 完全支持非跨库事务,例如:仅分表,或分库但是路由的结果在单库中。

  • 完全支持因逻辑异常导致的跨库事务。例如:同一事务中,跨两个库更新。更新完毕后,抛出空指针,则两个库的内容都能回滚。

  • 不支持因网络、硬件异常导致的跨库事务。例如:同一事务中,跨两个库更新,更新完毕后、未提交之前,第一个库宕机,则只有第二个库数据提交。

我们查看本地事务提交代码就可看到,如果我们的事务是在commit阶段发生异常,则事务不会生效,如果是发生在预编译PreparedStatement阶段,也就是语句错误,或者业务逻辑异常,这些异常在进入commit方法前就已经跑出则事务生效,所以在不引入分布式事务时,应该保证一次事务提交是在单库中进行的,比如订单,和订单明细都分片到一个库中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public abstract class AbstractConnectionAdapter extends AbstractUnsupportedOperationConnection {
@Override
public final void commit() throws SQLException {
Collection<SQLException> exceptions = new LinkedList<>();
for (Connection each : cachedConnections.values()) {
try {
each.commit();
} catch (final SQLException ex) {
exceptions.add(ex);
}
}
throwSQLExceptionIfNecessary(exceptions);
}
}