分库分表之ShardingSphere读写分离（springboot）

红太狼 2023-06-25 02:20 84阅读 0赞

1. 问题

数据库主从如何实现读写分离

> 1） select语句走从表
> 
> 2） insert，update走主表

2. jar包引入

[见之前的文章][Link 1]

3. springboot配置

>     #shardingsphere
>     spring.shardingsphere.datasource.names=master,slave
>     #主库
>     spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
>     spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
>     spring.shardingsphere.datasource.master.url=jdbc:mysql://xx:3306/apporder?useUnicode=true&characterEncoding=utf-8
>     spring.shardingsphere.datasource.master.jdbcUrl=jdbc:mysql://xx:3306/apporder?useUnicode=true&characterEncoding=utf-8
>     spring.shardingsphere.datasource.master.username=root
>     spring.shardingsphere.datasource.master.password=xx
>     spring.shardingsphere.datasource.master.max-active=30
>     spring.shardingsphere.datasource.master.min-idle=5
>     spring.shardingsphere.datasource.master.time-between-eviction-runs-millis=600000
>     spring.shardingsphere.datasource.master.min-evictable-idle-time-millis=600000
>     spring.shardingsphere.datasource.master.remove-abandoned-timeout=10
>     spring.shardingsphere.datasource.master.remove-abandoned=true
>     # 从库
>     spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
>     spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
>     spring.shardingsphere.datasource.slave.url=jdbc:mysql://xx:3306/apporder?useUnicode=true&characterEncoding=utf-8
>     spring.shardingsphere.datasource.slave.jdbcUrl=jdbc:mysql://xx:3306/apporder?useUnicode=true&characterEncoding=utf-8
>     spring.shardingsphere.datasource.slave.username=root
>     spring.shardingsphere.datasource.slave.password=xx
>     spring.shardingsphere.datasource.slave.max-active=30
>     spring.shardingsphere.datasource.slave.min-idle=5
>     spring.shardingsphere.datasource.slave.time-between-eviction-runs-millis=600000
>     spring.shardingsphere.datasource.slave.min-evictable-idle-time-millis=600000
>     spring.shardingsphere.datasource.slave.remove-abandoned-timeout=10
>     spring.shardingsphere.datasource.slave.remove-abandoned=true
>     # 主从库 不支持分库分表
>     spring.shardingsphere.masterslave.name=ms
>     spring.shardingsphere.masterslave.master-data-source-name=master
>     spring.shardingsphere.masterslave.slave-data-source-names=slave
> 
>     # SQL日志
>     spring.shardingsphere.props.sql.show=true
>     spring.shardingsphere.props.query.with.cipher.column=true
>     #mybatis
>     mybatis.mapper-locations=classpath:mybatis/*.xml

4. 测试类

>  
> 
>     @Test
>         public void testApp() {
>             AppAuthRegisterExample example = new AppAuthRegisterExample();
>             List<AppAuthRegister> appAuthRegisters = appAuthRegisterDAO.selectByExample(example);
>             System.out.println(appAuthRegisters);
>         }
>     
>         @Test
>         public void testInsertApp() {
>             AppAuthRegister record = new AppAuthRegister();
>             record.setLogin(0);
>             record.setCreateTime(new Date());
>             record.setUpdateTime(new Date());
>             int insert = appAuthRegisterDAO.insert(record);
>             System.out.println(insert);
>         }

5. 效果

查询走从库（SQL:slave）

> \[2019-12-24 15:55:36 INFO  org.apache.shardingsphere.core.route.SQLLogger:log\] - Actual SQL: slave ::: select  
>            
>            
>           
>         id, user\_id, type, login, display, cp\_category, secret\_key, cp\_secret\_key,  
>         url, create\_time, update\_time  
>        
>         from app\_auth\_register

插入走主库

[Link 1]: https://blog.csdn.net/u010627840/article/details/103671158