简介 之前的文章中提到了使用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); } }