Mysql分区，分库和分表

最新推荐文章于 2025-01-06 16:21:02 发布

转载最新推荐文章于 2025-01-06 16:21:02 发布 · 184 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/rayallenbj/p/9802277.html

文章标签：

#数据库 #java

本文介绍了一种基于SpringBoot+Mybatis+Druid+Sharding-Jdbc的技术栈实现Mysql水平分表的方法。通过具体配置示例展示了如何设置数据源、分表策略等关键步骤。

作者说的非常清楚了，感谢。地址为：http://haitian299.github.io/2016/05/26/mysql-partitioning/。

本人项目实践，使用sharding-jdbc进行Mysql水平分表，从参数可以看出来分表策略。

项目是基于Spring Boot + Mybatis + Druid + Sharding-Jdbc技术进行开发。

配置：

sharding.jdbc.datasource.names = mysqlDataSource
sharding.jdbc.datasource.mysqlDataSource.type = com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.mysqlDataSource.driverClassName = com.mysql.jdbc.Driver
sharding.jdbc.datasource.mysqlDataSource.url = jdbc:mysql://testhost:3306/test-database
sharding.jdbc.datasource.mysqlDataSource.username = root
sharding.jdbc.datasource.mysqlDataSource.password = root
sharding.jdbc.datasource.mysqlDataSource.maxActive = 60
sharding.jdbc.datasource.mysqlDataSource.maxWait = 20000
sharding.jdbc.datasource.mysqlDataSource.initSize = 10
sharding.jdbc.datasource.mysqlDataSource.decrypt = false
sharding.jdbc.datasource.mysqlDataSource.removeAbandoned = false
sharding.jdbc.datasource.mysqlDataSource.minIdle = 10
sharding.jdbc.datasource.mysqlDataSource.timeBetweenEvictionRunsMillis = 60000
sharding.jdbc.datasource.mysqlDataSource.minEvictableIdleTimeMillis = 300000
sharding.jdbc.datasource.mysqlDataSource.validationQuery = select 1 from dual
sharding.jdbc.datasource.mysqlDataSource.testWhileIdle = true
sharding.jdbc.datasource.mysqlDataSource.testOnBorrow = false
sharding.jdbc.datasource.mysqlDataSource.testOnReturn = false
sharding.jdbc.datasource.mysqlDataSource.poolPreparedStatements = true
sharding.jdbc.datasource.mysqlDataSource.maxPoolPreparedStatementPerConnectionSize = 100
sharding.jdbc.datasource.mysqlDataSource.removeAbandonedTimeout = 180
sharding.jdbc.datasource.mysqlDataSource.logAbandoned = true
sharding.jdbc.datasource.mysqlDataSource.filters = config,stat,mergeStat

sharding.jdbc.config.sharding.default-data-source-name = mysqlDataSource
sharding.jdbc.config.sharding.tables.test_table.actualDataNodes = mysqlDataSource.test_table_${0..1}
sharding.jdbc.config.sharding.tables.test_table.tableStrategy.inline.shardingColumn = test_column
sharding.jdbc.config.sharding.tables.test_table.tableStrategy.inline.algorithm-expression = test_table_${Math.abs(test_column.hashCode()) % 2}

Springboot配置类：

@MapperScan(basePackages = {"com.bj.services.mysql.dao"}, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@EnableConfigurationProperties(MybatisProperties.class)
@Configuration
public class DBMysqlConfig {

    /*@Bean(name = "mysqlDataSource")
    @ConfigurationProperties("spring.datasource.druid.mysql")
    public DataSource mysqlDatasource() {
        return DruidDataSourceBuilder.create().build();
    }*/

    @Bean(name = "mysqlTransactionManager")
    public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("shardingDataSource") DataSource dataSource, MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider) throws Exception {
        return buildSessionFactory(dataSource, properties, interceptorsProvider);
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sessionFactory) {
        sessionFactory.openSession(false);
        return new SqlSessionTemplate(sessionFactory);
    }

    // mybatis page plugin
//    @Bean
    /*public Interceptor pageInterceptor() {
        PageHelper pageHelper = BeanUtils.instantiate(PageHelper.class);
        pageHelper.setProperties(PropertiesBuilder.builder().put("dialect", "mysql").toBuild());
        return pageHelper;
    }*/

    static SqlSessionFactory buildSessionFactory(DataSource dataSource, MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setVfs(SpringBootVFS.class);
        Interceptor[] interceptors = interceptorsProvider.getIfAvailable();
        if (!ObjectUtils.isEmpty(interceptors)) {
            sessionFactory.setPlugins(interceptors);
        }
        if (StringUtils.hasLength(properties.getTypeAliasesPackage())) {
            sessionFactory.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        }
        if (StringUtils.hasLength(properties.getTypeHandlersPackage())) {
            sessionFactory.setTypeHandlersPackage(properties.getTypeHandlersPackage());
        }
        properties.setMapperLocations(new String[]{"classpath:mybatis/mysql/**/*Mapper.xml"});
        if (!ObjectUtils.isEmpty(properties.resolveMapperLocations())) {
            sessionFactory.setMapperLocations(properties.resolveMapperLocations());
        }
        //sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(READONLY_MAPPER_PACKAGES));
        return sessionFactory.getObject();
    }