sharding-jdbc读写分离

今天药忘吃喽~ 2023-01-21 11:20 8阅读 0赞

首先需要mysql数据库实现主从复制。  
注意，sharding-jdbc只负责主从数据库的路由分发，不负责主从数据库之间的复制，复制由mysql实现。

## 1.配置文件 ##

#分片规则
    #数据源 、《《读写分离》》 数据源声明
    spring.shardingsphere.datasource.names=m1,m2,m0,s0
    spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.230.145:3306/order_db_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    spring.shardingsphere.datasource.m1.username=root
    spring.shardingsphere.datasource.m1.password=root
    spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.230.145:3306/order_db_2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    spring.shardingsphere.datasource.m2.username=root
    spring.shardingsphere.datasource.m2.password=root
    # 《《读写分离》》 数据源
    spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.m0.url=jdbc:mysql://192.168.230.145:3306/user-db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    spring.shardingsphere.datasource.m0.username=root
    spring.shardingsphere.datasource.m0.password=root
    spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.s0.url=jdbc:mysql://192.168.230.147:3306/user-db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    spring.shardingsphere.datasource.s0.username=root
    spring.shardingsphere.datasource.s0.password=root
    
    #分库策略
    spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
    spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=m$->{ user_id%2+1}
    # 《《读写分离》》 分库策略
    spring.shardingsphere.sharding.master-slave-rules.db0.master-data-source-name=m0
    spring.shardingsphere.sharding.master-slave-rules.db0.slave-data-source-names=s0
    
    #指定表数据分布情况，配置数据节点
    spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=m$->{ 1..2}.c_order_$->{ 1..2}
    # 《《读写分离》》 指定表数据分布情况，配置数据节点
    spring.shardingsphere.sharding.tables.c_user.actual-data-nodes=db0.c_user
    
    #主键生成策略:雪花算法
    spring.shardingsphere.sharding.tables.c_order.key-generator.column=order_id
    spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
    
    #分表策略
    spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=order_id
    spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order_$->{ order_id % 2 + 1}
    
    #输出日志
    spring.shardingsphere.props.sql.show=true
    
    #公共表，广播表
    spring.shardingsphere.sharding.broadcast-tables=c_dict

## 2.sql执行语句 ##

@Insert("insert into c_user (id,name) value (#{id},#{name})")
    void insertUser(@Param("id")Integer id, @Param("name")String name);
    
    @Select("select * from c_user where id = #{id}")
    Map selectUser(@Param("id")Integer id);

## 3.测试类 ##

@Test
        public void testMasterSlaveDistrict(){ 
            cUserMapper.insertUser(1,"jack");
        }
    
        @Test
        public void testMasterSlaveDistrictRead(){ 
            Map map = cUserMapper.selectUser(1);
            System.out.println(map);
        }

## 4.执行结果 ##

2021-05-15 21:05:18.302 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : ==>  Preparing: insert into c_user (id,name) value (?,?)
    2021-05-15 21:05:18.330 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : ==> Parameters: 1(Integer), jack(String)
    2021-05-15 21:05:18.910  INFO 16748 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
    2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into c_user (id,name) value (?,?)
    2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=c_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=c_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=2, logicSQL=insert into c_user (id,name) value (?,?)), deleteStatement=false, updateTableAlias={ }, updateColumnValues={ }, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@498c535d, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@16ee9aa7])])
    2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : Actual SQL: m0 ::: insert into c_user  (id, name) VALUES (?, ?) ::: [1, jack]
    2021-05-15 21:05:18.945 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : <==    Updates: 1

2021-05-15 21:05:51.762 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : ==>  Preparing: select * from c_user where id = ?
    2021-05-15 21:05:51.793 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : ==> Parameters: 1(Integer)
    2021-05-15 21:05:52.377  INFO 3704 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
    2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from c_user where id = ?
    2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=c_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=c_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from c_user where id = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
    2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : Actual SQL: s0 ::: select * from c_user where id = ? ::: [1]
    2021-05-15 21:05:52.420 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : <==      Total: 1
    { name=jack, id=1}