MySQL 分库分表后聚合 mysql的分表分库

一、名词解释

库：database；表：table；分库分表：sharding

二、数据库架构演变

刚开始我们只用单机数据库就够了，随后面对越来越多的请求，我们将数据库的写操作和读操作进行分离， 使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写， 从库从主库同步更新数据，保持数据一致。架构上就是数据库主从同步。 从库可以水平扩展，所以更多的读请求不成问题。但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的， 因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。这时就需要用到分库分表（sharding），对写操作进行切分。

三、分库分表前的问题

任何问题都是太大或者太小的问题，我们这里面对的数据量太大的问题。

1. 用户请求量太大
   因为单服务器TPS，内存，IO都是有限的。 解决方法：分散请求到多个服务器上； 其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。
2. 单库太大
   单个数据库处理能力有限；单库所在服务器上磁盘空间不足；单库上操作的IO瓶颈 解决方法：切分成更多更小的库
3. 单表太大
   CRUD都成问题；索引膨胀，查询超时 解决方法：切分成多个数据集更小的表。

四、分库分表的方式方法

一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。 我们从面临的问题，开始解决，阐述： 首先是用户请求量太大，我们就堆机器搞定（这不是本文重点）。然后是单个库太大，这时我们要看是因为表多而导致数据多，还是因为单张表里面的数据多。 如果是因为表多而数据多，使用垂直切分，根据业务切分成不同的库。如果是因为单张表的数据量太大，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。 分库分表的顺序应该是先垂直分，后水平分。 因为垂直分更简单，更符合我们处理现实世界问题的方式。

1. 垂直拆分

• 垂直分表
  也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
• 垂直分库（就是每个业务专门建库，不要把多个业务的表糅合在一个库里）
  垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么？我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。



• 
  

2. 水平拆分

• 水平分表
  针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
• 水平分库分表
  将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
• 水平分库分表切分规则

• RANGE
  从0到10000一个表，10001到20000一个表；
• HASH取模
  一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
• 地理区域
  比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
• 时间
  按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

• 水平分库
• 水平分表（这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈）
  
  注意：随着数据库数据量增加，不要马上考虑做水平切分，首先考虑缓存处理，读写分离，使用索引等等方式，如果这些方式不能根本解决问题了，再考虑做水平分库和水平分表

五、分库分表后面临的问题

1. 事务支持
   分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
2. 多库(多数据源问题)结果集合并（group by，order by）
3. 跨库join(跨节点连接查询问题（分页、排序）)
   分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。

六、Mysql分库分表例子(shardingsphere)

shardingsphere官网

1. 水平分表

数据库创建两张一样结构的表

application.properties配置

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1
# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
#配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:63306/sharding?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

#指定course表分布情况，配置表在哪个数据库里面，表名称都是什么  m1.course_1 , m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# 指定course表里面主键cid 生成策略  SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略  约定cid值偶数添加到course_1表，如果cid是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

测试：

@SpringBootTest
class ShardingApplicationTests {
    @Autowired
    CourseMapper courseMapper;
    @Test
    void contextLoads() {
        for (long i = 0 ; i<20; i++){
          courseMapper.insert(new Course(null,String.valueOf(i+1),i,String.valueOf(i)));
        }
    }
}

course_1

course_2

2. 水平分库分表
   分别创建sharding_1库，sharding_2库，每个库下再创建course_1表，course_2表，course_3表

application.properties配置

# shardingjdbc分片策略
# 配置数据源，给数据源起名称,
# 水平分库，配置两个数据源(分别对应shardin_g1,sharding_2)
spring.shardingsphere.datasource.names=m1,m2

#配置第一个数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:63306/sharding_1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

#配置第二个数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:63306/sharding_2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root


#指定数据库分布情况，数据库里面表分布情况
# m1  m2    course_1 course_2 course_3
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..3}

# 指定course表里面主键cid 生成策略  SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定表分片策略  约定cid%3=0加到course_1表，如果cid%3=1添加到course_2表, 如果cid%3=2添加到course_3表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 3 + 1}

# 指定库分片策略 约定user_id是偶数添加m1，是奇数添加m2
spring.shardingsphere.sharding.tables.course.database-strategy.inline..sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

测试：

@SpringBootTest
class ShardingApplicationTests {
    @Autowired
    CourseMapper courseMapper;
    @Test
    void contextLoads() {
        for (long i = 0 ; i<20; i++){
          courseMapper.insert(new Course(null,String.valueOf(i+1),i,String.valueOf(i)));
        }
    }
}

这里只展示userid为偶数的数据（完美实现分库分表）

测试获取一条数据

@Test
    void get(){
        Course course = courseMapper.selectById(631501645238239232L);
    }

可以看到，因为无法确定该数据是在哪个库（因为我们查询的时候并没有指定分库时用的userid），所以shardingproxy就到两个库里找，但是根据cid，shardingproxy能确定在course_3库，所以，只在course_3里查找。参考文章

3. 公共表
   公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。

• 创建dict表

添加以下配置：

# 添加公共表
spring.shardingsphere.sharding.broadcast-tables=dict
spring.shardingsphere.sharding.tables.dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.dict.key-generator.type=SNOWFLAKE

测试

@Test
    void insertCommon()
    {
        dictMapper.insert(new Dict(null,"1","1","1"));
    }

结果可以看到，两个数据源中都插了该信息

删除的时候也是全部删除

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



6. 可以看到，读写分离需要配合数据库的主从复制，而Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作（select）和写操作(insert update)分别路由至主库与从库，它提供透明化的读写分离，让使用方尽量使用一个数据库一样使用主从数据库集群。

在这里我部署了两台mysql从机，和一台mysql主机，主机负责写，两台从机同步主机数据并负责读。

结构如下：

数据分离+读写分离的配置文件

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
# 水平分库
spring.shardingsphere.datasource.names=m1,m2,s1,s2,s3,s4

##配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://ip:13306/sharding_1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://ip:13306/sharding_2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://ip:23306/sharding_1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root

spring.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s2.url=jdbc:mysql://ip:23306/sharding_2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=root

spring.shardingsphere.datasource.s3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s3.url=jdbc:mysql://ip:33306/sharding_1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.s3.username=root
spring.shardingsphere.datasource.s3.password=root

spring.shardingsphere.datasource.s4.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s4.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s4.url=jdbc:mysql://ip:33306/sharding_2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.shardingsphere.datasource.s4.username=root
spring.shardingsphere.datasource.s4.password=root


#指定数据库分布情况，数据库里面表分布情况
# m1  m2    course_1 course_2 course_3
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..3}
# 指定course表里面主键cid及其生成策略:SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略约定cid%3=0写入course_1表，cid%3=1写入course_2表，cid%3=2写入course_3表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 3 + 1}

# 指定数据库分片策略约定user_id是偶数添加m1，是奇数添加m2
spring.shardingsphere.sharding.tables.course.database-strategy.inline..sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

spring.main.allow-bean-definition-overriding=true

# 指定公共表
spring.shardingsphere.sharding.broadcast-tables=dict
spring.shardingsphere.sharding.tables.dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.dict.key-generator.type=SNOWFLAKE

# 绑定主从库关系
spring.shardingsphere.sharding.master-slave-rules.m1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.m1.slave-data-source-names=s1,s3
spring.shardingsphere.sharding.master-slave-rules.m1.load-balance-algorithm-type=ROUND_ROBIN
spring.shardingsphere.sharding.master-slave-rules.m2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.m2.slave-data-source-names=s2,s4

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

• 查询：

@Test
    void get(){
        LambdaQueryWrapper<Course> queryWrapper=new LambdaQueryWrapper();
        queryWrapper.eq(Course::getUserId, 43);
        System.out.println(courseMapper.selectOne(queryWrapper));
    }

可以看到，由于是使用user_id进行查询的，sharding根据43自然知道只需要在user_id为单数的那个库查找即可，而且由于查询是轮询的，所以可以看到两个从库被轮询查找，但是每次查找的表是不一样的

• 插入

@Test
    void contextLoads() {
        for (long i = 102 ; i<112; i++){
            Course course=new Course(null,String.valueOf(i),i+1,String.valueOf(i));
            courseMapper.insert(course);
        }
    }

可以看到，只在m1，m2之间轮询插入数据，而不会在从库(s1,s2,s3,s4)进行插入

更详细的配置文件参考官网