53.mysql优化之动态数据源分库

方案说明

随着业务发展,数据量越来越大,迫切需要做分库分表。 每个库里都有所有的完整的表。

1.首先确认分库的方案是1个站点1个数据库实例 dynamic-n,n是站点编码,站点编码从1开始递增。

2.每创建1个站点执行脚本动态创建1个库,以及dynamic库下的所有的表。

3.每次请求时,需要携带token,token中保存了当前用户对应的站点编码。

4.数据源使用自定义的AbstractRoutingDataSource。

5.每次执行sql语句时需要通过数据源获取连接时。

6.首先通过站点编码替换jdbcUrl中的库名,动态获取jdbcUrl。

7.然后根据driverName、jdbcUrl、userName、password获取数据源。

8.从数据源中获取连接并返回。

代码

配置项

注意

1.配置是1个json

2.数据库是dynamic_*[database]

center.dataSourceInfo = {"url": "jdbc:mysql://localhost:3306/dynamic_*[database]?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true","password": "******","username": "******","idleTimeOut": 30000,"minimumIdle": 1,"driverClassName": "com.mysql.jdbc.Driver","maximumPoolSize": 100,"connectionTimeout": 30000}

配置类

``` package dynamic.config; import cn.hutool.core.util.StrUtil; import com.alibaba.fastjson.JSONObject; import dynamic.exception.BusinessRuntimeException; import dynamic.support.MultiTenantAwareRoutingSource; import dynamic.util.EmptyUtil; import dynamic.model.DatasourceConfiguration; import com.zaxxer.hikari.HikariDataSource; import lombok.extern.slf4j.Slf4j; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.http.HttpStatus; import javax.sql.DataSource;

@Slf4j @Configuration public class DatasourceConfig {    Logger logger = LoggerFactory.getLogger(DatasourceConfig.class); ​    @Value("${center.dataSourceInfo}")    private String dataSourceInfo;

   //默认的数据源配置 这里是什么场景使用暂时没想到    @Bean    @ConfigurationProperties(prefix = "datasource.default")    public DatasourceConfiguration defaultDatasourceConfiguration() {        return new DatasourceConfiguration();   } ​    @Bean    public DataSource dataSource() {        //MultiTenantAwareRoutingSource的构造方法的参数是1个数据源工厂        MultiTenantAwareRoutingSource dataSource = new MultiTenantAwareRoutingSource(lookupKey -> {            DataSource ds;            //默认数据源            if (MultiTenantAwareRoutingSource.DSDEFAULT.equals(lookupKey.toString())) {                ds = createDefaultDatasource();           } else {                //动态数据源                if (EmptyUtil.isEmpty(dataSourceInfo)) {                    throw new BusinessRuntimeException("无数据源配置信息", HttpStatus.NOTFOUND);               } //租户编码即站点编码                String tenantCode = StrUtil.removePrefix(lookupKey.toString(), MultiTenantAwareRoutingSource.DSPREFIX);                log.info("dataSourceInfo:{}", dataSourceInfo);                //解析json为配置类                DatasourceConfiguration datasourceConfiguration                   = JSONObject.parseObject(dataSourceInfo, DatasourceConfiguration.class);                //配置类判空                if (datasourceConfiguration == null) {                    logger.error(StrUtil.format("无法获取正确数据源，请检查配置，租户编码：{}", lookupKey.toString()));                    throw new IllegalStateException("无法获取正确数据源，请检查配置");               }                //具体的数据库名称                String database = new StringBuilder().append(tenantCode).toString();                //使用具体的数据库名称替换*[database]                //mysql://uat-other-mysql.yl.com:3306/whiteclouddynamic[database]                datasourceConfiguration.setUrl(datasourceConfiguration.getUrl().replace("[database]", database));                //创建数据源                ds = createDatasource(datasourceConfiguration, lookupKey.toString());           }            return ds;       });        return dataSource;   }

   /*     * 使用默认数据源配置创建默认数据源     */    private DataSource createDefaultDatasource() {        HikariDataSource dataSource = createDatasource(defaultDatasourceConfiguration(), "ds-default");        return dataSource;   }

   /*     * 创建数据源     */    private HikariDataSource createDatasource(DatasourceConfiguration datasourceConfiguration, String datasourcePoolName) {        HikariDataSource dataSource = new HikariDataSource();        dataSource.setMaximumPoolSize(datasourceConfiguration.getMaximumPoolSize());        dataSource.setIdleTimeout(datasourceConfiguration.getIdleTimeOut());        dataSource.setDriverClassName(datasourceConfiguration.getDriverClassName());        dataSource.setJdbcUrl(datasourceConfiguration.getUrl());        dataSource.setUsername(datasourceConfiguration.getUsername());        dataSource.setPoolName("HikariPool-" + datasourcePoolName);        dataSource.setPassword(datasourceConfiguration.getPassword());        dataSource.setMinimumIdle(datasourceConfiguration.getMinimumIdle());        dataSource.setConnectionTestQuery("SELECT 1");        return dataSource;   } } ​ ```

路由数据源

``` package dynamic.support; import cn.hutool.core.util.StrUtil; import com.alibaba.fastjson.JSONObject; import dynamic.context.AccessContextHolder; import dynamic.exception.BusinessRuntimeException; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.locks.Lock; import java.util.concurrent.locks.ReentrantLock; import javax.sql.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpStatus; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class MultiTenantAwareRoutingSource extends AbstractRoutingDataSource { private static final Logger log = LoggerFactory.getLogger(MultiTenantAwareRoutingSource.class); Logger logger = LoggerFactory.getLogger(AbstractRoutingDataSource.class); /* * 为保证线程安全 并发创建数据源的锁 / private final Lock lock = new ReentrantLock(); public static final String DS_PREFIX = "ds-"; public static final String DS_DEFAULT = "ds-default"; / * 数据源缓存 / Map** * 创建数据源工厂类 */ private MultiTenantAwareRoutingSource.IDatasourceFactory datasourceFactory;

/***
 * 路由数据源构造方法
 */
public MultiTenantAwareRoutingSource(MultiTenantAwareRoutingSource.IDatasourceFactory datasourceFactory) {
    this.datasourceFactory = datasourceFactory;
    this.dataSources = new ConcurrentHashMap();
}
/***
 * 数据源缓存
 */
public Map<Object, DataSource> getDataSources() {
    return this.dataSources;
}
/***
 * 添加数据源到数据源缓存
 */
public void addDataSources(Object lookupKey, DataSource datasource) {
    this.dataSources.put(lookupKey, datasource);
}
/***
 *重写父类的determineCurrentLookupKey方法 
 *用于设置查找数据源时 设置数据源的路由key
 */
protected Object determineCurrentLookupKey() {
    //注意这个AccessContextHolder.getAccessContext()
    //是1个ThreadLocal 
    //这个ThreadLocal是在哪设置的值？
    //AuthRestInterceptor 下面贴代码
    String tenantCode = AccessContextHolder.getAccessContext() == null ? 
                                null : AccessContextHolder.getAccessContext().getTenantCode();
    if (StrUtil.isEmpty(tenantCode)) {
        log.error("无法确定租户：{}", JSONObject.toJSONString(AccessContextHolder.getAccessContext()));
        throw new BusinessRuntimeException("无法确定租户", HttpStatus.NOT_FOUND);
    } else {
        return "ds-" + tenantCode;
    }
}
/***
 * 重写父类的determineCurrentLookupKey方法 
 * 根据路由key创建数据源 
 */
protected DataSource determineTargetDataSource() {
    Object lookupKey = this.determineCurrentLookupKey();
    //根据路由key从缓存中获取数据源
    DataSource dataSource = (DataSource)this.dataSources.get(lookupKey);
    if (dataSource == null) {
        //加锁防止重复创建 重复创建可能导致的问题
        //线程A创建了dynamic-1 正在使用
        //线程B也创建了dynamic-1 覆盖了A正在使用的dynamic-1数据源
        //A有2个方法要执行 删除和增加 且需要保证事务
        //那么如果第一次 用的是A创建的数据源 第二次 用的是B创建的数据源会不会有问题？
        //个人觉得会有问题 因为2个connection连接不是1个连接了
        this.lock.lock();
        try {
            if (dataSource == null) {
                //使用数据源工厂 创建数据源
                dataSource = this.datasourceFactory.create(lookupKey);
                //添加到缓存
                this.addDataSources(lookupKey, dataSource);
            }
        } finally {
            this.lock.unlock();
        }
    } else {
        this.logger.debug("==>使用已有数据源【{}】，当前数据源数量:【{}】<==", lookupKey.toString(), this.getDataSources().size());
    }
    return dataSource;
}

@Override
public void afterPropertiesSet() {
    //重写父类的afterPropertiesSet 跳过一些校验!
}

public interface IDatasourceFactory {
    DataSource create(Object lookUpKey);
}

} ```

AuthRestInterceptor

// // Source code recreated from a .class file by IntelliJ IDEA // (powered by Fernflower decompiler) // ​ package dynamic.security; ​ import cn.hutool.core.annotation.AnnotationUtil; import cn.hutool.core.lang.Assert; import cn.hutool.core.util.StrUtil; import dynamic.base.event.BaseInterceptor; import dynamic.context.AbstractAccessUserService; import dynamic.context.AccessContext; import dynamic.context.AccessContextHolder; import dynamic.security.annotation.RequiresPermissions; import dynamic.util.HttpUtils; import dynamic.util.JwtUtils; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpMethod; import org.springframework.http.HttpStatus; import org.springframework.util.StringUtils; import org.springframework.web.method.HandlerMethod; ​ public class AuthRestInterceptor extends BaseInterceptor {    private static final Logger log = LoggerFactory.getLogger(AuthRestInterceptor.class);    public static ThreadLocal<Long> threadLocal = new ThreadLocal();    private String tokenHeader;    private String secret;    private AbstractAccessUserService accessUserService; ​    public AuthRestInterceptor(String tokenHeader, String secret, AbstractAccessUserService accessUserService) {        this.tokenHeader = tokenHeader;        this.secret = secret;        this.accessUserService = accessUserService;   } ​    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {        if (request.getMethod().equals(HttpMethod.OPTIONS.name())) {            return true;       } else {            threadLocal.set(System.currentTimeMillis());            String authToken = request.getHeader(this.tokenHeader);            if (StringUtils.isEmpty(authToken)) {                log.info("[IP:{}]未授权的访问，token不能为空", HttpUtils.getIP(request));                this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, "未授权的访问，token不能为空");                return false;           } else {                AccessContext accessContext;                try {                    accessContext = JwtUtils.getAccessUserFromToken(authToken, this.secret);                    accessContext.setToken(authToken);               } catch (Exception var8) {                    var8.printStackTrace();                    log.info("[IP:{}]未授权的访问，请重新登录获取授权,异常原因:{}", HttpUtils.getIP(request), var8.getMessage());                    this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, StrUtil.format("未授权的访问，请重新登录获取授权，异常原因:{}", new Object[]{var8.getMessage()}));                    return false;               } ​                Assert.notNull(accessContext);                if (this.accessUserService.isLogoutToken(accessContext.getToken())) {                    log.info("[用户名:{}-{}][IP:{}]会话已注销，请重新登录", new Object[]{accessContext.getName(), accessContext.getLoginName(), HttpUtils.getIP(request)});                    this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, "会话已注销，请重新登录");                    return false;               } else {                    AccessContextHolder.setAccessContext(accessContext);                    HandlerMethod handlerMethod = (HandlerMethod)handler;                    RequiresPermissions requiresPermissions = (RequiresPermissions)AnnotationUtil.getAnnotation(handlerMethod.getMethod(), RequiresPermissions.class);                    if (requiresPermissions != null && !this.isPermitted(requiresPermissions)) {                        log.info("[用户名:{}-{}][IP:{}]权限不足，无此权限:{}", new Object[]{accessContext.getName(), accessContext.getLoginName(), HttpUtils.getIP(request), this.getFormattedPermissions(requiresPermissions.value())});                        this.buildFailResponse(response, HttpStatus.FORBIDDEN, "权限不足，无此权限:" + this.getFormattedPermissions(requiresPermissions.value()));                        return false;                   } else {                        return super.preHandle(request, response, handler);                   }               }           }       }   } ​    protected boolean isPermitted(RequiresPermissions requiresPermissions) {        return this.accessUserService.isPermitted(requiresPermissions);   } ​    protected String getFormattedPermissions(String[] permissions) {        StringBuilder sb = new StringBuilder();        if (permissions.length == 1) {            return permissions[0];       } else {            String[] var3 = permissions;            int var4 = permissions.length; ​            for(int var5 = 0; var5 < var4; ++var5) {                String permission = var3[var5];                sb.append(permission).append(",");           } ​            return sb.length() > 0 ? sb.substring(0, sb.length() - 1) : "系统数据异常";       }   } ​    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {        if (!request.getMethod().equals(HttpMethod.OPTIONS.name())) {            if (ex != null) {                log.error("[用户:{}-{}][IP:{}][uri:{}][耗时:{}ms]访问出错，异常原因:[{}]", new Object[]{AccessContextHolder.getAccessContext() == null ? "游客" : AccessContextHolder.getAccessContext().getName(), AccessContextHolder.getAccessContext() == null ? "guest" : AccessContextHolder.getAccessContext().getLoginName(), HttpUtils.getIP(request), request.getRequestURI(), System.currentTimeMillis() - (Long)threadLocal.get(), ex.getMessage()});           } else {                log.info("[用户:{}-{}][IP:{}][uri:{}][耗时:{}ms]访问成功", new Object[]{AccessContextHolder.getAccessContext() == null ? "游客" : AccessContextHolder.getAccessContext().getName(), AccessContextHolder.getAccessContext() == null ? "guest" : AccessContextHolder.getAccessContext().getLoginName(), HttpUtils.getIP(request), request.getRequestURI(), System.currentTimeMillis() - (Long)threadLocal.get()});           } ​            AccessContextHolder.remove();            threadLocal.remove();       } ​        super.afterCompletion(request, response, handler, ex);   } } ​