sqlalchemy 读写分离修改数据读数据和写数据库数据未同步 sqlserver数据库读写分离

转载

mob64ca14133dc6 2024-03-30 15:42:29

文章标签 SQL 数据库读写分离 文章分类 机器学习人工智能

背景

有一天DBA反馈数据库慢查较多，需要进行优化。否则，为了保护数据库，DBA就会将慢查SQL Kill掉。

开始考虑慢查优化前，我们首先搞清楚什么是慢查。

慢查询是指一个SQL语句的执行时间超过指定阀值。这个阀值通常结合业务来设定的，我们目前是100ms。

一个慢查SQL的危害有多大？

l 拖慢整个系统的访问速度，并间接影响相关联的系统。

l 对于一个高频率的SQL，一旦出现慢查，当大量流量打到DB时，极有可能导致数据库资源耗尽，DB对外不可用。

l 在一个高流量网站中，大量请求处理延时，几秒内就可能导致应用资源被耗尽，服务对外不可用，分布式环境下甚至会引起雪崩。

因此，慢查必须引起足够重视。

常见SQL优化策略

为了使SQL高效运行，通常，我们会采用以下策略进行优化：

DB存储引擎选择，数据库表设计，字段类型选择等。
编写高效的SQL语句。
DB配置的优化，如缓冲池的配置等。
数据库索引。
在DB之上加一层缓存。
主从读写分离。
数据库拆分，水平拆分和垂直拆分。
抛弃关系型数据库，转向Hbase等NoSQL数据库。

这里每一个主题都会涉及比较多的内容，本文不会对每个主题做深入分析。本文主要谈谈使用数据库读写分离架构优化SQL查询的方法。

认识读写分离

一主多从，读写分离，主从同步，是一种常见的数据库架构。其中：

l 主库，承担全部的写业务，部分读业务

l 从库，承担大部分读业务

l 主从之间通过某种机制进行数据同步，如mysql的binlog

读写分离是必须吗？

不尽然。首先我们看看读写分离架构需要解决的问题：

连接池隔离，每个数据源使用自己的连接池
为了保证高可用，读连接池需要实现故障自动转移
业务接受主从同步延迟

这意味着选择数据库读写分离架构，就必须接受和解决它所带来的问题。

读写分离究竟适合什么场景？

读多写少的业务场景，读已经成为性能瓶颈，使用数据库索引、缓存等方案无法有效解决问题时，可考虑支持读写分离架构。

当前我们的数据库架构支持读写分离架构，原因是：

主库保护机制，超过100ms的SQL直接Kill；
主库高峰期压力较大；
部分SQL已有较高效的索引，高峰期SQL执行时间仍然会超过阀值；
部分业务场景，通过索引或缓存优化已无法满足需求，如分页列表查询，各种查询条件随意组合；
从库SQL执行时间的阀值设置得相对较高。

从库支持，目的是为了提升读性能，分担主库压力，保障业务正常运行。但是，当系统运行中遇到一个慢查时，我们首先应该考虑优化SQL逻辑，分析SQL执行计划，看能否从SQL本身做优化；或者考虑引入缓存来优化读性能，实际上更多场景下建议使用缓存架构来加强系统读性能，替代数据库主从分离架构。当然，缓存架构也有它的问题，假设缓存挂了或发生缓存穿透，流量达到DB，极有可能导致DB雪崩。

读写分离方案思考

数据库读写分离的方案还是比较多的，各有优缺点。下面列举了几个方案供参考：

中间件转发

通过mysql中间件做主从集群，如Mysql Proxy、Amoeba、Atlas等，应用层请求通过中间件完成转发。

优点：对应用透明

缺点：需要代理，增加网络等性能开销

应用层支持

应用层通过路由数据源实现读写分离，通过AOP+注解来动态选择数据源。这是推荐的方案。

优点：无需中间件，轻量级

缺点：耦合度高

数据库驱动支持

可以使用mysql驱动自带的replicationDriver来实现。replicationDriver支持两个connection，一个是masterConnection，一个是slaveConnection。当把connection的readonly设为true时，当前连接就会切换为slaveConnection，否则为masterConnection。

优点：对应用透明，无需中间件

缺点：需要DB支持Replication协议

读写分离实战

Spring动态数据源支持

数据库层面主从架构的支持已经由DBA配置好了，我主要谈谈应用层面多数据源的支持。以Spring boot为例，说明Spring对动态数据源的支持。

spring-jdbc中有这样一个类AbstractRoutingDataSource，它扩展至javax.sql.DataSource，我们来看看它获取connection的源码：

@Override
public Connection getConnection() throws SQLException {
    return determineTargetDataSource().getConnection();
}

@Override
public Connection getConnection(String username, String password) throws SQLException {
    return determineTargetDataSource().getConnection(username, password);
}

它首先会通过调用determineTargetDataSource方法来决定获取connection的目标数据源，继续深入：

protected DataSource determineTargetDataSource() {
    Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
    Object lookupKey = determineCurrentLookupKey();
    DataSource dataSource = this.resolvedDataSources.get(lookupKey);
    if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
        dataSource = this.resolvedDefaultDataSource;
    }
    if (dataSource == null) {
        throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
    }
    return dataSource;
}

首先调用determineCurrentLookupKey方法拿到一个lookupKey，每个数据源对应一个唯一的Key，然后到数据源路由中查找对应的数据源。我们再看看determineCurrentLookupKey方法的实现：

protected abstract Object determineCurrentLookupKey();

这是一个抽象方法，意味着支持扩展。奈何没有提供向resolvedDataSources写入数据的入口，再看看内部是如何向resolvedDataSources中写入数据的，源码如下：

@Override
public void afterPropertiesSet() {
    if (this.targetDataSources == null) {
        throw new IllegalArgumentException("Property 'targetDataSources' is required");
    }
    this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
    for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) {
        Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
        DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
        this.resolvedDataSources.put(lookupKey, dataSource);
    }
    if (this.defaultTargetDataSource != null) {
        this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
    }
}

由afterPropertiesSet方法可知，它会在bean属性初始化之后，将targetDataSources中的数据复制到resolvedDataSources，而AbstractRoutingDataSource中也提供了设置targetDataSources的入口。

基于此，项目中支持多数据源就不是梦了。当创建DataSource这个bean时，我们将主从数据源作为独立的bean加入targetDataSources，并重写determineCurrentLookupKey，实现动态key获取。这样JDBC就可以根据不同的数据源配置创建不同的数据库connection了。

重点代码分析

扩展AbstractRoutingDataSource，实现自己的数据源实现：

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

多数据源配置：

@Bean(name = DataSourceConstant.DATASOURCE_NAME_MASTER)
@Primary
@ConfigurationProperties(prefix = DataSourceConstant.PROFILE_PREFIX_MASTER)
public DataSource dataSourceMaster() {
    return DataSourceBuilder.create().build();
}

@Bean(name = DataSourceConstant.DATASOURCE_NAME_SLAVE)
@ConfigurationProperties(prefix = DataSourceConstant.PROFILE_PREFIX_SLAVE)
public DataSource dataSourceSlave() {
    return DataSourceBuilder.create().build();
}

@Bean(name = DataSourceConstant.DATASOURCE_NAME)
public DataSource dataSource(@Qualifier(DataSourceConstant.DATASOURCE_NAME_MASTER) DataSource master,
                             @Qualifier(DataSourceConstant.DATASOURCE_NAME_SLAVE) DataSource slave) {
    DynamicDataSource dynamicDataSource = new DynamicDataSource();
    dynamicDataSource.setDefaultTargetDataSource(master);
    dynamicDataSource.setTargetDataSources(ImmutableMap.of(DataSourceConstant.DATASOURCE_NAME_MASTER, master,
            DataSourceConstant.DATASOURCE_NAME_SLAVE, slave));
    
    DynamicDataSourceContextHolder.setDefaultDataSource(DataSourceConstant.DATASOURCE_NAME_MASTER);
    DynamicDataSourceContextHolder.addDataSource(DataSourceConstant.DATASOURCE_NAME_MASTER);
    DynamicDataSourceContextHolder.addDataSource(DataSourceConstant.DATASOURCE_NAME_SLAVE);
    return dynamicDataSource;
}

当有一天数据库架构改为一主多从时，只需要修改上面这部分代码，增加bean配置即可。此外，为了方便运行时动态切换数据源，我们通过自定义注解 + AOP的方式来实现。

@Aspect
@Order(-1) 
public class DynamicDataSourceAspect {
    protected static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    public boolean preValidate(JoinPoint point, TargetDataSource targetDataSource) {
        return Boolean.TRUE;
    }

    @Before("@annotation(targetDataSource)")
    private void changeDataSource(JoinPoint point, TargetDataSource targetDataSource) throws Throwable {
        String dsName = targetDataSource.name();
        if (dsName == null || dsName.length() <= 0) {
            DynamicDataSourceContextHolder.setDefaultDataSourceType();
            return;
        }
        if (!DynamicDataSourceContextHolder.containsDataSource(dsName)) {
            logger.warn("DataSource not exist，use default {} > {}", dsName, point.getSignature());
            DynamicDataSourceContextHolder.setDefaultDataSourceType();
            return;
        }
        if (!preValidate(point, targetDataSource)) {
            return;
        }
        if (!DynamicDataSourceContextHolder.isDefaultDataSource(dsName)) {
            logger.info("change DataSource salve : {} > {}", dsName, point.getSignature());
        }
        DynamicDataSourceContextHolder.setDataSourceType(dsName);
    }

    @After("@annotation(targetDataSource)")
    private void restoreDataSource(JoinPoint point, TargetDataSource targetDataSource) {
        DynamicDataSourceContextHolder.clearDataSourceType();
    }
}

这里没有把DynamicDataSourceAspect声明为bean，主要方便后期扩展。

@Bean
@Primary
public DynamicDataSourceAspect initDataSourceAspect() {
    return new DynamicDataSourceAspect();
}

全部源码下载：https://github.com/JeffreyHy/daily-code，找到com.huang.datasource即可！

从库故障自动转移

为了保证高可用，当从库故障时，希望将从库的流量自动切换到主库，待从库恢复之后再切换回来。有很多方案可以实现从库故障切换，我们的实现是接入配置中心，设置一个开关来控制从库切换。只需扩展DynamicDataSourceAspect并重写preValidate方法，实现自己的开关逻辑即可。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：jenkins 集群 HA jenkins集群搭建

下一篇：session在什么时候获取 session什么时候创建

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯