springboot实战之mysql分库分表

lyb-geek

发布于 2019-10-28 17:21:44

6.7K0

发布于 2019-10-28 17:21:44

文章被收录于专栏：Linyb极客之路

什么是分库分表

把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。如果说读写分离是为了分散数据库读写操作压力，分库分表就是为了分散存储压力

什么时候考虑切分

1、能不切分尽量不要切分

并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。

不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

2、数据量过大，正常运维影响业务访问

这里说的运维，指：

1）对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的

2）对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt- online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。

3）大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力

3、随着业务发展，需要对某些字段垂直拆分

4、数据量快速增长

随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量

5、安全性和可用性

鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到100%的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。

分库分表的方式

垂直切分

适用场景：如果是因为表的个数多而让数据多，可以按照功能划分，把联系密切的表切分出来放在同一个库中（分库）；

垂直拆分是指，将一个属性较多，一行数据较大的表，将不同的属性拆分到不同的表中，以降低单库（表）大小，达到提升性能的目的的方法，垂直切分后，各个库（表）的特点是：

（1）每个库（表）的结构都不一样

（2）一般来说，每个库（表）的属性至少有一列交集，一般是主键

（3）所有库（表）的并集是全量数据

水平切分

适用场景：如果是因为表中的数据量过于庞大，则可以采用水平切分，按照某种约定好的规则将数据切分到不同的数据库中；

水平切分是指，以某个字段为依据（例如uid），按照一定规则（例如取模），将一个库（表）上的数据拆分到多个库（表）上，以降低单库（表）大小，达到提升性能的目的的方法，水平切分后，各个库（表）的特点是：

（1）每个库（表）的结构都一样

（2）每个库（表）的数据都不一样，没有交集

（3）所有库（表）的并集是全量数据

几种常用的分库分表的策略

1、HASH取模

假设有用户表user,将其分成3个表user0,user1,user2.路由规则是对3取模,当uid=1时,对应到的是user1,uid=2时,对应的是user2.

2、范围分片

从1-10000一个表,10001-20000一个表。

3、地理位置分片

华南区一个表,华北一个表。

4、时间分片

按月分片，按季度分片等等,可以做到冷热数据。

分库分表后引入的问题

分布式事务问题

如果我们做了垂直分库或者水平分库以后,就必然会涉及到跨库执行SQL的问题,这样就引发了互联网界的老大难问题-"分布式事务"。

那要如何解决这个问题呢？

使用分布式事务中间件
使用MySQL自带的针对跨库的事务一致性方案(XA),不过性能要比单库的慢10倍左右。
能否避免掉跨库操作(比如将用户和商品放在同一个库中)

跨库join的问题

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

粗略的解决方法：

全局表：基础数据，所有库都拷贝一份。
字段冗余：这样有些字段就不用join去查询了。
系统层组装：分别查询出所有，然后组装起来，较复杂。

横向扩容的问题

当我们使用HASH取模做分表的时候,针对数据量的递增,可能需要动态的增加表,此时就需要考虑因为reHash导致数据迁移的问题。

结果集合并、排序的问题

因为我们是将数据分散存储到不同的库、表里的,当我们查询指定数据列表时,数据来源于不同的子库或者子表,就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作,性能肯定会受非常大的影响。走缓存可能一条路!

分库分表实现方法

分库分表的实现主要有2种方案，第一种是代码层封装，比如使用Sharding-JDBC，本文的主要就是通过Sharding-JDBC来进行分库分表。第二种是通过中间件代理，比如使用mycat。

Sharding-JDBC分库分表

正常分库分表大部分都是先单库分表，再来分库分表，下面的例子也是遵循这个原则。另外例子的sharding-jdbc版本为

<sharding.boot.version>3.1.0</sharding.boot.version>

1、单库分表

a、表结构如下

DROP TABLE IF EXISTS `book_0`;

CREATE TABLE `book_0` (
  `id` bigint(20) NOT NULL,
  `book_name` varchar(200) DEFAULT NULL,
  `author` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `stock` int(6) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `book_1` */

DROP TABLE IF EXISTS `book_1`;

CREATE TABLE `book_1` (
  `id` bigint(20) NOT NULL,
  `book_name` varchar(200) DEFAULT NULL,
  `author` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `stock` int(6) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

b、pom.xml

<dependencies>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.dozermapper</groupId>
            <artifactId>dozer-core</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>log4j-over-slf4j</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>


    </dependencies>

c、application.yml 配置

sharding:
  jdbc:
    datasource:
      names: master-ds
      # 数据源master-ds
      master-ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/springboot-learning?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=UTC
        username: root
        password: 123456
    config:
      sharding:
        props:
          sql.show: true
        tables:
          book:  #逻辑表名
            key-generator-column-name: id  #主键
            actual-data-nodes: master-ds.book_${0..1}   #物理表数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: id
                algorithm-expression: book_${id % 2}  #按id模运算分配
spring:
  main:
    allow-bean-definition-overriding: true

仅需上面3步就可以实现一个单库分表方案，更多详细配置可以查看如下链接

https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/configuration/config-yaml/
2、分库分表

例子分库分表表结构和单库分表的表结构是一样的，仅仅只是又多了一个库。

分库分表其配置文件内容如下

sharding:
  jdbc:
    datasource:
      #Canonical names should be kebab-case ('-' separated), lowercase alpha-numeric characters and must start with a letter
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/boot-learning?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=UTC
        username: root
        password: 123456
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/springboot-learning?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=UTC
        username: root
        password: 123456
    config:
      sharding:
        props:
          sql.show: true
        tables:
         book:
            key-generator-column-name: id  #主键
            actual-data-nodes: ds${0..1}.book_${0..1}    #数据节点,均匀分布
            database-strategy:   #分库策略
              inline: #行表达式
                sharding-column: id        #列名称，多个列以逗号分隔
                algorithm-expression: ds${id % 2}    #按id模运算分配
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: stock
                algorithm-expression: book_${stock % 2}

spring:
  main:
    allow-bean-definition-overriding: true

同单库分表相比，通过sharding-jdbc进行分库分表，就配置文件内容不一样，其他sharding-jdbc底层都已经帮你实现好了。更多详细配置可以查看如下链接

https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/configuration/config-yaml/

总结

通过那个例子的整合，也许会给大家一个错觉，分库分表实现看起来也很简单啊，通过shariding-jdbc就可以快速实现，但事实上任何脱离业务的实现，都是纸上谈兵，上边的例子也只是一个简单的入门，具体分库分表方案，还得根据业务的复杂性来定。对于分库分表的一些介绍，大家也可以查看这篇文章浅谈高性能数据库集群 —— 分库分表