每天20分钟之shardingjdbc续之主从分离和强制主库

原创

李子健

修改于 2022-07-31 22:50:04

1.9K0

修改于 2022-07-31 22:50:04

文章被收录于专栏：每日一善每日一善每日一善

背景

之前写过一篇sharding-jdbc的文章，实际是简单介绍，配置部分就是线上项目使用，未能摘出来成文件，这次整理成代码，大家可以在工作上使用。

作用

shardingjdbc的作用：在客户端对jdbc进行增强和改写，实现了分库分表

官网

整体流程

支持的功能和概念（之前写过，这次作为回忆了:)）

分库分表
读写分离：支持基于Hint的强制主库路由
柔性事务
分布式主键（id生成器）
兼容性 jpa hibernate， mybatis，spring-jdbc
支持第三方数据库连接池
支持jdbc规范
支持多样的配置
逻辑分表LogicTable
实际分表ActualTable
数据分片DataNode。数据分片的最小单元
动态分表。DynamicTable
BindingTable，分表之间建立绑定关系
shardingColumn分片字段
shardingAlgorithm分片算法。支持range，hash，tag
SQLHint。分片非sql决定的，支持灵活注入字段

实践

读写分离
强制主库

create database db1;

CREATE TABLE `user` (

  `id` bigint NOT NULL COMMENT '主键ID',

  `name` varchar(30) DEFAULT NULL COMMENT '姓名',

  `age` int DEFAULT NULL COMMENT '年龄',

  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

create database db2;

CREATE TABLE `user` (

  `id` bigint NOT NULL COMMENT '主键ID',

  `name` varchar(30) DEFAULT NULL COMMENT '姓名',

  `age` int DEFAULT NULL COMMENT '年龄',

  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

use db1;

insert user(id,name,age)values(1,'sank',30);

use db2;

insert user(id,name,age)values(2,'tom',13);

项目配置

spring.application.name=ksharding-rw

server.port=7312

spring.shardingsphere.datasource.names=master,slave1

spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai

spring.shardingsphere.datasource.master.username=root

spring.shardingsphere.datasource.master.password=123456

spring.shardingsphere.datasource.master.pool-name=HikariPool-1

spring.shardingsphere.datasource.master.minimum-idle=1

spring.shardingsphere.datasource.master.maximum-pool-size=5

spring.shardingsphere.datasource.master.idle-timeout=600000

spring.shardingsphere.datasource.master.max-lifetime=1200000

spring.shardingsphere.datasource.master.connection-timeout=30000

spring.shardingsphere.datasource.master.connection-test-query=SELECT 1

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://127.0.0.1:3306/db2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai

spring.shardingsphere.datasource.slave1.username=root

spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.datasource.slave1.pool-name=HikariPool-2

spring.shardingsphere.datasource.slave1.minimum-idle=1

spring.shardingsphere.datasource.slave1.maximum-pool-size=5

spring.shardingsphere.datasource.slave1.idle-timeout=600000

spring.shardingsphere.datasource.slave1.max-lifetime=1200000

spring.shardingsphere.datasource.slave1.connection-timeout=30000

spring.shardingsphere.datasource.slave1.connection-test-query=SELECT 1

spring.shardingsphere.masterslave.load-balance-algorithm-type=round\_robin

spring.shardingsphere.sharding.master-slave-rules.master.master-data-source-name=master

spring.shardingsphere.sharding.master-slave-rules.master.slave-data-source-names=slave1

logging.level.org.apache.ibatis=debug

logging.level.com.mysql.cj = debug

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

mybatis.type-aliases-package=cn.beckbi.model

mybatis.mapper-locations=classpath:/mapper/\*.xml

mybatis.configuration.mapUnderscoreToCamelCase = true

management.health.db.enabled=false

mybatis配置

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.beckbi.dao.UserMapper">
<select id="getUserById" parameterType="long"  resultType="cn.beckbi.model.User">
select * from user where id = #{id}
</select>
</mapper>

强制走主库

大家按照我的这个样式去写

###方案一

public User getByIdFromMaster(Long id) {

        return Optional.ofNullable(id).map(uid -> {
            User user = null;
            HintManager.clear();
            try (HintManager hintManager = HintManager.getInstance()) {
                hintManager.setMasterRouteOnly();
                user = userMapper.getUserById(uid);
            }
            return user;
        }).orElse(null);
    }

方案二

    @ShardingJdbcMaster
    @Override
    public User getByIdFromMaster2(Long id) {
        return userMapper.getUserById(id);
    }

定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
public @interface ShardingJdbcMaster {
}

定义aop

@Slf4j
@Component
@Aspect
public class ShardingAop {

    @Around("execution(* cn.beckbi.service.impl.*.*(..))")
    public Object master(ProceedingJoinPoint joinPoint){
        Object[] args = joinPoint.getArgs();
        Object ret = null;
        log.info(joinPoint.toShortString());

        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
        Method method = methodSignature.getMethod();
        ShardingJdbcMaster shardingJdbcMaster = method.getAnnotation(ShardingJdbcMaster.class);

        HintManager hintManager = null;
        try {
            if (Objects.nonNull(shardingJdbcMaster)) {
                HintManager.clear();
                hintManager = HintManager.getInstance();
                hintManager.setMasterRouteOnly();
            }
            ret = joinPoint.proceed(args);
        }catch (Exception ex){
            log.error("exception error",ex);
        }catch (Throwable ex2){
            log.error("Throwable",ex2);
        }finally {
            if (Objects.nonNull(shardingJdbcMaster) && Objects.nonNull(hintManager)) {
                hintManager.close();
            }
        }
        return ret;
    }
}

验证