分布式架构之数据库分库分表，Sharding-JDBC实战

一、为什么要分库分表

1、高并发：数据库单实例扛不住高并发，需多实例承受

2、数据量大：单机磁盘容量有限，数据库数据量大时撑满磁盘

3、sql执行速度：单表数据过大，sql执行速度极慢

二、有哪些技术支持

1、技术

• sharding-jdbc(sharding-sphere)：当当开源的，client层方案，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）
• mycat：基于 cobar 改造的，proxy 层方案
• TDDL：淘宝团队开发的，client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法
• cobar：阿里 b2b 团队开发和开源的，proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群，cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行，不支持读写分离、存储过程、跨库 join 和分页等操作。
• atlas：360 开源的，proxy 层方案

2、选型

sharding-jdbc和mycat是现在最火的，另外三种基本很少人用，然后sharding-jdbc呢不需要部署一套中间件，比较适合中小型企业，而mycat需要部署中间件，需要额外的维护但对项目透明不需要每个项目依赖，适合大型公司

三、如何进行拆分

1、拆分方式

1）水平拆分

水平拆分即把一个表数据按照行的级别进行拆分，比如表中按每一千万行为单位，拆成多个库表，而它们加起来就是全部数据，特点在于每个分表的表结构都是一样的。这样拆分使得每个表数据限制在一定的范围内，以此来支撑高并发和保证sql的执行效率，一般sql越复杂，要求数据行数约少

2）垂直拆分

垂直拆分即是把一个表中不同列拆分出来，每个库表包含部分字段，特点就是每个分表的表结构是不同的，一般拆分是将访问较多的字段拆分出来，数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好

2、拆分策略

1）range方式

就是每库存一段连续的数据，比如按时间范围或者连续主键分类，如上个月的存在一个库表，下一月的存在另一个库表，但这种容易产生热点问题，因为多数系统查询的都是较新的数据，那部分会经常使用而较久远的数据很少被访问到

2）hash方式

这种是基于hash算法，根据hash值分配到不同库表，均匀分散，但可能在扩容的时候没有上一种方式来的方便

四、拆分思考

1、只选一个sharding column进行分库分表

2、多个sharding column多个分库分表

• 冗余全量
• 冗余关系

3、sharding column分库分表 + es

五、使用Sharding-JDBC分库分表实战

1、集成过程，大部分配置类似上一篇文章读写分离 https://blog.csdn.net/qq_20475615/article/details/99657628

• 首先引入依赖，这里使用springboot+mybatis-plus+druid+mysql+sharding-jdbc

<!-- starter -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- test -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<scope>test</scope>
</dependency>
<!-- Mysql Connector -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.44</version>
</dependency>
<!-- druid -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.1.10</version>
</dependency>
<!-- MybatisPlus -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.0.1</version>
</dependency>
<!-- sharding-jdbc -->
<dependency>
	<groupId>io.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>3.1.0.M1</version>
</dependency>

• application.yml文件配置，配置规则在后面测试过程有解释

mybatis-plus:
  # 放在resource目录 classpath:/mapper/*Mapper.xml
  mapper-locations: classpath:/mapper/*.xml
  # 实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.example.project.*.*.mapper
  global-config:
    # 主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    id-type: 2
    # 字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"
    field-strategy: 2
    # 驼峰下划线转换
    db-column-underline: true
    # 刷新mapper 调试神器
    refresh-mapper: true
    # 数据库大写下划线转换
    #capital-mode: true
    # 逻辑删除配置（下面3个配置）
    logic-delete-value: 0
    logic-not-delete-value: 1
    # SQL 解析缓存，开启后多租户 @SqlParser 注解生效
    sql-parser-cache: true
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false


sharding:
  jdbc:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.60:23306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.60:63306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
    config:
      sharding:
        props:
          sql.show: true
        tables:
          org_user:  #表名
            key-generator-column-name: id  #主键
#            actual-data-nodes: ds${0..1}.org_user${0..1}  #数据节点
            actual-data-nodes: ds${0..1}.org_user${0..1}  #数据节点
            #分库策略
            database-strategy:
              inline:
                sharding-column: age
                algorithm-expression: ds${age % 2}
            #分表策略
            table-strategy:
              inline:
                shardingColumn: sex
                algorithm-expression: org_user${sex % 2}
            #配置另一个表加多个节点即可
#          org_address:
#            key-generator-column-name: id
#            actual-data-nodes: ds${0..1}.org_address
#            database-strategy:
#              inline:
#                shardingColumn: lit
#                algorithm-expression: ds${lit % 2}

• 业务代码，这里要说的就是分库分表后最重要的就是表 id的生成，如何保证它的唯一性如雪花算法之类的，当然这部分不在这里详述，这里直接写死，重点在于分库分表部分

//mybatis-plus 配置类
@Configuration
@MapperScan("com.example.project.*.*.mapper") //这里千万注意只写mapper 所在文件夹，mybatis会进行代理，免得误伤其他文件夹
public class MybatisPlusConfig {
    /**
     * 分页插件，自动识别数据库类型
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

//简单实体类
@TableName("org_user")
public class User {
    private Integer id;
    private String name;
    private Integer age;
    private Integer sex;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }
}

//mapper
public interface UserMapper extends BaseMapper<User> {
}

//简单的服务代码
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    /**
     * 测试读
     */
    public void getUser(){
        System.out.println(null != userMapper.selectById(1) ? userMapper.selectById(1).getName():"1空");
        System.out.println(null != userMapper.selectById(2) ? userMapper.selectById(2).getName():"2空");
        System.out.println(null != userMapper.selectById(3) ? userMapper.selectById(3).getName():"3空");
        System.out.println(null != userMapper.selectById(4) ? userMapper.selectById(4).getName():"4空");
    }
    /**
     * 测试写
     */
    public void saveUser(int id,int age,int sex,String name){
        User user = new User();
        user.setId(id);
        user.setAge(age);
        user.setSex(sex);
        user.setName(name);
        userMapper.insert(user);
    }

}
//测试类
@SpringBootTest
@RunWith(SpringRunner.class)
public class TestService {
    @Autowired
	UserService userService;
    @Test
    public void test(){
	    userService.saveUser(1,2,0,"小米1");//按道理应该去的库0，org_user0表
        userService.saveUser(2,1,0,"小米2");//按道理应该去的库1，org_user0表
        userService.saveUser(3,2,1,"小米3");//按道理应该去的库0，org_user1表
        userService.saveUser(4,1,1,"小米4");//按道理应该去的库1，org_user1表
//        userService.getUser();
    }
}

• 在两个数据库创建好对应的表，字段对应着实体来就可以，四个表保持结构一致，库0也就是23306端口，库1也就是63306端口

      

    

• 测试过程，我们配置的库0 是23306端口，库1 是63306端口，按照我们配置的规则是，年龄双数是分到库0，年龄单数的是分到库1 ，性别我们用 0 代表男，1 代表女，所以规则是男的分到 org_user0 表，女的分到 org_user1 表，看一下结果是否如我们所想

①测试写，我们查看结果

   

  

 

②测试读，查看结果是否能自动路由，正常取出对应的值

2、拓展，类似上一篇文章读写分离 https://blog.csdn.net/qq_20475615/article/details/99657628，我们用另一种依赖来配置

• 上面的集成方法中我们用的sharding-jdbc是<artifactId>sharding-jdbc-spring-boot-starter</artifactId>，下面我们换一种依赖来集成，其他的依赖还是跟上面一样，只要修改sharding-jdbc的

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-core</artifactId>
	<version>4.0.0-RC1</version>
</dependency>

• 接着增加一个sharding-druid.yml，放到和application.yml平级，内容如下，可以看到配置方式跟上面是不一样的（记住不要把该配置放到application.yml，否则会引起冲突导致启动失败），application.yml里去掉sharding节点那部分，至于mybatis的配置还是跟上面一样

dataSources:
  ds0: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.1.60:23306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
  ds1: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.1.60:63306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456

shardingRule:
  tables:
    #表名
    org_user:
      actualDataNodes: ds${0..1}.org_user${0..1}
      #分库策略
      databaseStrategy:
        inline:
          shardingColumn: age
          algorithmExpression: ds${age % 2}
      #分表策略
      tableStrategy:
        inline:
          shardingColumn: sex
#这里不同版本会有不同，开始按官网的配置是algorithmInlineExpression，发现在我的这个版本里报错，具体看YamlInlineShardingStrategyConfiguration里面是什么属性
          algorithmExpression: org_user${sex % 2}

• 增加一个配置类（上面的方式是不需要的）

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() throws Exception {
        return YamlShardingDataSourceFactory.createDataSource(ResourceUtils.getFile("classpath:sharding-druid.yml"));
    }
}

• 代码部分跟上面一样测试即可