Sharding JDBC实现读写分离 、分库分表

MySQL的分库分表

为什么要分库分表？

MySQL作为一个关系型数据库，单机的连接数和储存能力都是有限的 单表还在百万级别时，我们还可以通过通过添加从库，索引优化的方式提升性能 当数据朝着千万级别增长时，这时的优化就是捉襟见肘了，性能大幅度下降

何为分库分表？

分库分表就是要将大量数据分散到多个数据库中，使每个数据库中数据量小响应速度快，以此来提升数据库整体性能 核心理念就是对数据进行切分（ Sharding ），以及切分后如何对数据的快速定位与整合 为了减少数据库的负担，提升数据库响应速度，缩短查询时间，这时候就需要进行 分库分表

针对数据切分类型，大致可以分为：垂直（纵向）切分和水平（横向）切分两种

垂直切分又细分为 垂直分库 和 垂直分表

垂直切分之垂直分库

• 垂直分库是基于业务分类的，和我们常听到的微服务治理观念很相似，每一个独立的服务都拥有自己的数据库，需要不同业务的数据需接口调用。而垂直分库也是按照业务分类进行划分，每个业务有独立数据库，这个比较好理解

垂直切分之垂直分表

• 垂直分表 是基于数据表的列为依据切分的，是一种大表拆小表的模式

• 比如在一张订单表中，把长度较大且访问不频繁的字段，拆分出来创建一个单独的扩展表进行存储

• 数据库是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，可以加载更多数据到内存中，增加查询的命中率，减少磁盘IO，以此来提升数据库性能

垂直切分的思考

• 优势

  • 业务间解耦，不同业务的数据进行独立的维护、监控、扩展

  • 在高并发场景下，一定程度上缓解了数据库的压力 .

• 缺点

  • 提升了开发的复杂度，由于业务的隔离性，很多表无法直接访问，必须通过接口方式聚合数据

  • 分布式事务管理难度增加 .

  • 数据库还是存在单表数据量过大的问题，并未根本上解决，需要配合水平切分

水平切分又分为库内分表和分库分表

水平切分之库内分表

• 前面说了垂直切分无法避免单表数据量过大的问题，此时水平切分来解决

• 比如现在我有一张表里面有3000W数据，此时我水平切分为六个表，每个表中各占500W数据

• 水平切分将一张大数据量的表，切分成多个表结构相同，而每个表只占原表一部分数据

• 库内分表存在的问题在于：

  • 虽然单表的数据量下去了，但是这么多分表仍然在同一个数据库中，共享该数据库的CPU，内存和网络IO

  • 仍然受到MySQL物理上的瓶颈限制

水平切分之分库分表

• 分库分表则是将切分出来的子表，分散到不同的数据库中，从而使得单个表的数据量变小，达到分布式的效果

• 优点：

  • 解决高并发时单库数据量过大的问题，提升系统稳定性和负载能力

  • 业务系统改造的工作量不是很大

• 缺点：

  • 跨分片的事务一致性难以保证

  • 跨库的join关联查询性能较差

  • 扩容的难度和维护量较大，（拆分成几千张子表想想都恐怖）

分库分表后数据怎么存取？

分库分表以后会出现一个问题，一张表会出现在多个数据库里，到底该往哪个库的表里存/取呢？

根据取值范围

可以按照时间或ID区间来切分数据，比如3000W数据，我拆分为6个库中单表500W数据，下面我以ID举一个列子：

• 我可以定义每个库的表中只能存 500W数据

• 第一个库的表中，我就存0-4999999，第二个库就存5000000-9999999，以此类推......

优点

• 单表数据量可控

• 水平扩展简单只需增加节点即可，无需对其他分片的数据进行迁移

• 能快速定位要查询的数据在哪个库

缺点

• 由于连续分片可能存在数据热点，如果按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

• 如果是按照Id字段分片，公司数据量及其容易被竞争公司看出，不好不好。

hash取模

对hash结果取余数的切分方式比较常见 ，还是拿ID来做取模说明吧

• 总数据量 6000W,分库分表为6个库单表500W数据量

• 对表中的ID字段进行取模，得到余数 i，i=1 就存1库，i=2就存2库，以此类推......

优点：

• 数据分片相对比较均匀，不易出现某个库并发访问的问题

缺点：

• 但这种算法存在一些问题，当某一台机器宕机，本应该落在该数据库的请求就无法得到正确的处理，这时宕掉的实例会被踢出集群，此时算法变成hash(userId) mod N-1，用户信息可能就不再在同一个库中

分库分表中间件？

目前这个技术已经相当的成熟了，各家公司都有自己的分库分表中间件

• sharding-jdbc（当当）

• Cobar（阿里巴巴）

• MyCAT（基于Cobar）

• TSharding（蘑菇街）

• Atlas（奇虎360）

• Oceanus（58同城）

• Vitess（谷歌）

Sharding JDBC

Sharding JDBC 架构图

Sharding JDBC核心组件

解析引擎

解析过程分为词法解析和语法解析

• 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token,

  • 并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符

• 再使用语法解析器将SQL转换为抽象语法树

第三代SQL解析器则从3.0.x版本开始，ShardingSphere尝试使用ANTLR作为SQL解析的引擎。ANTLR是指可以根据输入自动生成语法树并可视化的显示出来的开源语法分析器

路由引擎

根据解析上下文匹配数据库和表的分片策略，并生成路由路径

• 对于携带分片键的SQL，根据分片键的不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)

• 不携带分片键的SQL则采用广播路由

• 广播路由，对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路由、单播路由和阻断路由这5种类型

• 用于根据分片键进行路由的场景，又细分为直接路由、标准路由和笛卡尔积路由这3种类型

改写引擎

面向逻辑库与逻辑表书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL。 它包括正确性改写和优化改写两部分

执行引擎

ShardingSphere采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行

• 它不是简单地将SQL通过JDBC直接发送至数据源执行

• 也并非直接将执行请求放入线程池去并发执行

• 它更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最大限度地合理利用并发等问题

• 执行引擎的目标是自动化的平衡资源控制与执行效率

执行引擎分为准备和执行两个阶段。准备阶段用于准备执行的数据。它分为结果集分组和执行单元创建两个步骤。该阶段用于真正的执行SQL，它分为分组执行和归并结果集生成两个步骤

归并引擎

将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并

• ShardingSphere支持的结果归并从功能上分为遍历、排序、分组、分页和聚合5种类型，它们是组合而非互斥的关系。 从结构划分，可分为流式归并、内存归并和装饰者归并。流式归并和内存归并是互斥的，装饰者归并可以在流式归并和内存归并之上做进一步的处理

Sharding JDBC分片策略

• 标准分片策略 ：StandardShardingStrategy

  • 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持

  • 只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法

    • PreciseShardingAlgorithm是必选的，用于处理=和IN的分片

    • RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片

    • 如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理

• 复合分片策略 ：ComplexShardingStrategy

  • 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持

    • 支持多分片键，由于多分片键之间的关系复杂，因此Sharding-JDBC并未做过多的封装，而是直接将分片键值组合以及分片操作符交于算法接口，完全由应用开发者实现，提供最大的灵活度

• Inline表达式分片策略 ：inlineShardingStrategy

  • 使用Groovy的Inline表达式，提供对SQL语句中的=和IN的分片操作支持

  • 只支持单分片键，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，

  • 如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表，表名称为t_user_0到t_user_7

• Hint分片策略：HintShardingStrategy

  • 通过Hint而非SQL解析的方式分片的策略

• 不分片策略：NoneShardingStrategy

Sharding JDBC读写分离

数据库表

CREATE TABLE `t_user` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 10 ) DEFAULT NULL,
    `age` INT ( 11 ) DEFAULT NULL,
    `address` VARCHAR ( 20 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8;

pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>4.1.1</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.23</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.20</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

User实体类

@Data
@ToString
public class User {
    private int id;
    private String name;
    private int age;
    private String address;
}

sharding jdbc 配置读写分离数据源

/**
 * sharding jdbc实现读写分离
 */
public class MasterSlaveDataSource {
​
    private static DataSource dataSource;
​
    public static DataSource getInstance() {
        if (dataSource != null) {
            return dataSource;
        }
        try {
            return create();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
​
    private static DataSource create() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
​
        // 配置第 1 个数据源
        DruidDataSource masterDataSource = new DruidDataSource();
        masterDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        masterDataSource.setUrl("jdbc:mysql://192.168.219.150:3306/hello?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
        masterDataSource.setUsername("root");
        masterDataSource.setPassword("root");
        dataSourceMap.put("master", masterDataSource);
​
        // 配置第 2 个数据源
        DruidDataSource slaveDataSource = new DruidDataSource();
        slaveDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        slaveDataSource.setUrl("jdbc:mysql://192.168.219.160:3306/hello?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
        slaveDataSource.setUsername("root");
        slaveDataSource.setPassword("root");
        dataSourceMap.put("slave", slaveDataSource);
​
        //创建主从复制数据源
        MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("masterSlaveDataSource", "master", Arrays.asList("slave"));
        dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig , new Properties());
        //返回数据源
        return dataSource;
    }
}

业务类

@Service
public class UserService {
​
    public boolean addUser(User user) throws Exception{
        DataSource dataSource = MasterSlaveDataSource.getInstance();
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_user(name, age, address) values (?,?,?)");
        preparedStatement.setString(1, user.getName());
        preparedStatement.setInt(2, user.getAge());
        preparedStatement.setString(3, user.getAddress());
        boolean result = preparedStatement.execute();
        return result;
    }
​
    public List<User> getUserList() throws Exception {
        DataSource dataSource = MasterSlaveDataSource.getInstance();
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("Select * from t_user");
        ResultSet resultSet = preparedStatement.executeQuery();
        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name"));
            user.setAge(resultSet.getInt("age"));
            user.setAddress(resultSet.getString("address"));
            userList.add(user);
        }
        return userList;
    }
}

测试类

@Test
    public void addUser() throws Exception {
        User user = new User();
        user.setName("武松");
        user.setAge(23);
        user.setAddress("清河县");
        userService.addUser(user);
    }
    @Test
    public void getUserList() throws Exception {
        List<User> userList = userService.getUserList();
        userList.forEach(System.out::println);
    }

Sharding JDBC分库分表

数据库表

CREATE TABLE `t_order0` (
    `order_id` INT ( 11 ) NOT NULL,
    `user_id` INT ( 11 ) NOT NULL,
    `info` VARCHAR ( 100 ) DEFAULT NULL,
PRIMARY KEY ( `order_id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

pom依赖和上面一致

Order实体类

@Data
public class Order {
    private int orderId;
    private int userId;
    private String info;
}

sharding jdbc 配置分库分表数据源

/**
 * Sharding JDBC实现分库分表
 */
public class ShardingDataSource {
​
    private static DataSource dataSource;
​
    public static DataSource getInstance() {
        if (dataSource != null) {
            return dataSource;
        }
        try {
            return create();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
​
    private static DataSource create() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
​
        // 配置第 1 个数据源
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://192.168.219.150:3306/hello?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        dataSourceMap.put("ds0", dataSource1);
​
        // 配置第 2 个数据源
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://192.168.219.160:3306/hello?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
        dataSource2.setUsername("root");
        dataSource2.setPassword("root");
        dataSourceMap.put("ds1", dataSource2);
​
        // 配置 t_order 表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order","ds${0..1}.t_order${0..1}");
​
        // 配置数据库表分片策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
​
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
​
        // 创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        return dataSource;
    }
}

业务类

@Service
public class OrderService {
    public boolean addOrderInfo(Order order) throws Exception{
        DataSource dataSource = ShardingDataSource.getInstance();
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_order(order_id, user_id, info) values (?,?,?)");
        preparedStatement.setInt(1, order.getOrderId());
        preparedStatement.setInt(2, order.getUserId());
        preparedStatement.setString(3, order.getInfo());
        boolean result = preparedStatement.execute();
        return result;
    }
}

测试类

@Test
public void addOrder() throws Exception {
    int userId = 10;
    for (int i = 1;i <= 20; i++) {
        if (i >= 10) {
            userId = 21;
        }
        Order order = new Order();
        order.setOrderId(i);
        order.setUserId(userId);
        order.setInfo("订单信息：user_id=" + userId + ",order_id=" + i);
        boolean result = orderService.addOrderInfo(order);
        if (result) {
            System.out.println("订单" + i + "添加成功");
        }
    }
}

Mycat简单介绍

MyCat的核心概念

• Schema：由它指定逻辑数据库（相当于MySQL的database数据库）

• Table：逻辑表（相当于MySQL的table表）

• DataNode：真正存储数据的物理节点

• DataHost：存储节点所在的数据库主机（指定MySQL数据库的连接信息）

• User：MyCat的用户（类似于MySQL的用户，支持多用户）

Mycat分片策略

• MyCAT支持水平分片与垂直分片

  • 水平分片：一个表格的数据分割到多个节点上，按照行分隔

  • 垂直分片：一个数据库中多个表格A，B，C，A存储到节点1上，B存储到节点2上，C存储到节点3上

• MyCAT通过定义表的分片规则来实现分片，每个表格可以捆绑一个分片规则，每个分片规则指定一个分片字段并绑定一个函数，来实现动态分片算法

  • Schema：逻辑库，与MySQL中的Database（数据库）对应，一个逻辑库中定义了所包括的Table。

  • Table：表，即物理数据库中存储的某一张表，与传统数据库不同，这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则。

  • DataNode：MyCAT的逻辑数据节点，是存放table的具体物理节点，也称之为分片节点，通过DataHost来关联到后端某个具体数据库上

  • DataHost：定义某个物理库的访问地址，用于捆绑到Datanode上

.