java项目中使用sharding实现读写分离

最新推荐文章于 2024-04-28 13:44:34 发布

一起养一条鱼

最新推荐文章于 2024-04-28 13:44:34 发布

阅读量200

点赞数

分类专栏： JAVA 文章标签： java 开发语言

本文链接：https://blog.csdn.net/weixin_41943486/article/details/132755380

版权

JAVA 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

前言

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

一、mysql主从复制

master将改变记录到二进制日志（binary log）
slave将master的binary log拷贝到它的中继日志（relay log）
slave重做中继日志中的事件，将改变应用到自己的数据库中

当我们没有DBA人员时，可以通过云服务商购买主从服务实现数据库层面的数据同步，而且近实时，有一定保障。

二、Sharding-JDBC 介绍

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。

它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离，增删改操作自动修改主库，查询操作自动在从库中获取。

        适用于任何基于JDBC的ORM框架，如：JPA，Hibernate，Mybatis，Spring JDBC Template或直接使用JDBC。
        支持任何第三方的数据库连接池，如：DBCP，C3PO，BoneCP，Druid，HikariCP等。
        支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

1. maven依赖：

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
<dependency>

2. 配置步骤
使用Sharding-JDBC实现读写分离步骤（不需要修改Java代码，只需完成三步配置）：

导入Sharding-JDBCd的maven坐标

在配置文件中配置读写分离规则(application.yml配置文件)

在配置文件中配置允许bean定义覆盖配置项

【yaml格式】

spring:
   shardingsphere:
       datasource:
           names:
               master,slave
           #主数据源
           master:
               type: com.alibaba.druid.pool.DruidDataSource
               driver-class-name: com.mysql.cj.jdbc.Driver
               url: jdbc:mysql://xxxxxx:3306/rw?characterEncoding=utf-8
               username: root
               password: root
#从数据源
slave:
   type: com.alibaba.druid.pool.DruidDataSource
   driver-class-name: com.mysql.cj.jdbc.Driver
   url: jdbc:mysql://xxxxx:3306/rw?characterEncoding-utf-8
   username: root
   password: root
masterslave:
#读写分离配置
load-balance-algorithm-type: round_robin
#最终的数据源名称
name: dataSource
#主库数据源名称
master-data-source-name: master
#从库数据源名称列表,多个逗号分隔
slave-data-source-names: slave
props:
   sql:
       show: true #开启SQL显示,默认false
main:
   allow-bean-definition-overriding: true #允许bean定义覆盖
【spring.xml格式】

spring.shardingsphere.datasource.names=master,slave0

spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://xxxxxx:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=root
spring.shardingsphere.datasource.master.initialSize=20
spring.shardingsphere.datasource.master.minIdle=20
spring.shardingsphere.datasource.master.maxActive=200
spring.shardingsphere.datasource.master.maxWait=60000
spring.shardingsphere.datasource.master.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.master.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.master.validationQuery=SELECT 'x'
spring.shardingsphere.datasource.master.testWhileIdle=true
spring.shardingsphere.datasource.master.testOnBorrow=false
spring.shardingsphere.datasource.master.testOnReturn=false
spring.shardingsphere.datasource.master.poolPreparedStatements=true
spring.shardingsphere.datasource.master.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.master.connection-init-sqls=set names utf8mb4;
spring.shardingsphere.datasource.master.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://xxxxxx:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root
spring.shardingsphere.datasource.slave0.initialSize=20
spring.shardingsphere.datasource.slave0.minIdle=20
spring.shardingsphere.datasource.slave0.maxActive=200
spring.shardingsphere.datasource.slave0.maxWait=60000
spring.shardingsphere.datasource.slave0.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.slave0.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.slave0.validationQuery=SELECT 'x'
spring.shardingsphere.datasource.slave0.testWhileIdle=true
spring.shardingsphere.datasource.slave0.testOnBorrow=false
spring.shardingsphere.datasource.slave0.testOnReturn=false
spring.shardingsphere.datasource.slave0.poolPreparedStatements=true
spring.shardingsphere.datasource.slave0.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.slave0.connection-init-sqls=set names utf8mb4;
spring.shardingsphere.datasource.slave0.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0

spring.shardingsphere.props.sql.show=true
3. springboot项目中改造
【启动类注解】：
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.circuitbreaker.EnableCircuitBreaker;
import org.springframework.cloud.openfeign.EnableFeignClients;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
//feign服务
@EnableFeignClients(basePackages = {"common.gd", "com.gd.app.feign"})
//开启hystrixDashboard监控
//@EnableHystrixDashboard
@EnableCircuitBreaker
//加载其他配置文件
//一些bean的配置
@MapperScan({"common.gd.base","com.gd.saasmgmt.mapper","common.gd.mapper","com.gd.clob.mapper.**","mybatis.gd.app.mapper.**"})
@Configuration
@ImportResource(locations = {"classpath:config/application-config.xml"})
@EnableScheduling
@EnableAsync
public class AppOrderApplication {
        .......
        ........
【项目pom.xml改造】：
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

【application-config.xml配置】：
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd


         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 实例化spring工具类 -->
    <bean id="springContextUtil" class="common.saas.util.SpringContextUtil"/>
    <!-- 定义扫描包 -->
    <context:component-scan base-package="common, config.gd, redis.gd, com.gd.app.conf,com.gd.app.controller,com.gd.app.service, mybatis.gd"/>
</beans>

【SpringContextUtil类】：
package common.gd.util;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
/**
 * 让静态变量保存到Spring ApplicationContext 可在任何代码任何地方任何时候中取出ApplicaitonContext
 */
public class SpringContextUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext;
    /**
     * 取得存储在静态变量中的ApplicationContext
     */
    public static ApplicationContext getApplicationContext() {
        if (applicationContext == null)
            throw new IllegalStateException(
                    "applicaitonContext未注入,请在applicationContext.xml中定义SpringContextUtil");
        return applicationContext;
    }
    /**
     * 实现ApplicationContextAware接口的context注入函数, 将其存入静态变量
     */
    @Override
    @SuppressWarnings("static-access")
    public void setApplicationContext(ApplicationContext context) {
        if (applicationContext == null) {
            applicationContext = context;
        }
    }
    public static <T> T getBean(String name) {
        return (T) applicationContext.getBean(name);
    }
    public static <T> T getBean(Class cls) {
        return (T) applicationContext.getBean(cls);
    }
}

至此，配置改造部分完成，对业务代码无侵入，方便灵活，如果需要增加从库，则需增加spring db配置，重启项目。

一起养一条鱼

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java项目中使用sharding实现读写分离

java项目中sharding实现读写分离
复制链接

扫一扫

专栏目录

java项目中使用sharding实现读写分离

“相关推荐”对你有帮助么？