分库分表技术之ShardingJDBC（2）

发表于

「这是我参与11月更文挑战的第25天，活动详情查看：2021最后一次更文挑战」

Sharding-JDBC入门使用

搭建基础环境

  • 需求说明

创建数据库lg_order，模拟将订单表进行水平拆分，创建两张表pay_order_1与pay_order_2，这两张表是订单表拆分后的表，我们通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的落入pay_order_1表 ，为奇数的落入pay_order_2表，再通过Sharding-Jdbc进行查询

  • 创建数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql复制代码CREATE DATABASE lg_order CHARACTER SET 'utf8';

DROP TABLE IF EXISTS pay_order_1;
CREATE TABLE pay_order_1 (
    order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
    user_id INT(11) ,
    product_name VARCHAR(128),
    COUNT INT(11)
);

DROP TABLE IF EXISTS pay_order_2;
CREATE TABLE pay_order_2 (
    order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
    user_id INT(11) ,
    product_name VARCHAR(128),
    COUNT INT(11)
);
  • 创建SpringBoot项目引入maven依赖

sharding-jdbc以jar包形式提供服务，所以要先引入maven依赖。

1
2
3
4
5
xml复制代码<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

分片规则配置(水平分表)

使用sharding-jdbc对数据库中水平拆分的表进行操作，通过sharding-jdbc对分库分表的规则进行配置，配置内容包括：数据源、主键生成策略、分片策略等。

application.properties

  • 基础配置
1
2
3
4
5
6
7
8
properties复制代码spring.application.name = sharding-jdbc-simple
server.servlet.context-path = /sharding-jdbc
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true

spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
  • 数据源
1
2
3
4
5
6
7
8
properties复制代码# 定义数据源
spring.shardingsphere.datasource.names = db1

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456
  • 配置数据节点
1
2
properties复制代码#配置数据节点,指定节点的信息
spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db1.pay_order_$->{1..2}

表达式db1.pay_order_$->{1..2}

$会被大括号中的{1..2}所替换

会有两种选择：db1.pay_order_1和db1.pay_order_2

  • 配置主键生成策略
1
2
3
properties复制代码#指定pay_order表 (逻辑表)的主键生成策略为 SNOWFLAKE
spring.shardingsphere.sharding.tables.pay_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.pay_order.key-generator.type=SNOWFLAKE

使用shardingJDBC提供的主键生成策略，全局主键

为避免主键重复，生成主键采用SNOWFLAKE分布式ID生成算法

  • 配置分片算法
1
2
3
properties复制代码#指定pay_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.algorithm-expression = pay_order_$->{order_id % 2 + 1}

分表策略表达式：pay_order_$-> {order_id % 2 + 1}

{order_id % 2 + 1} 结果是偶数操作pay_order_1表

{order_id % 2 + 1} 结果是奇数操作pay_order_2表

  • 打开SQL日志
1
2
properties复制代码# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
  • 步骤总结
    1. 定义数据源
    2. 指定pay_order表的数据分布情况，分布在pay_order_1和pay_order_2
    3. 指定pay_order表的主键生成策略为SNOWFLAKE，是一种分布式自增算法，保证id全局唯一
    4. 定义pay_order分片策略，order_id为偶数的数据下沉到pay_order_1，为奇数下沉到在pay_order_2

编写程序

  • 新增订单
1
2
3
4
5
6
7
8
9
10
11
java复制代码@Mapper
@Component
public interface PayOrderDao {

    /**
    * 新增订单
    * */
    @Insert("insert into pay_order(user_id,product_name,COUNT) values(#{user_id},#{product_name},#{count})")
    int insertPayOrder(@Param("user_id") int user_id, @Param("product_name") String product_name, @Param("count") int count);

}
  • 测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
java复制代码@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class PayOrderDaoTest {

    @Autowired
    PayOrderDao payOrderDao;

    @Test
    public void testInsertPayOrder(){

        for (int i = 1; i < 10; i++) {
            //插入数据
            payOrderDao.insertPayOrder(1,"小米电视",1);
        }
    }
}
  • 根据Id查询订单
1
2
3
4
5
6
7
8
9
10
11
12
13
java复制代码/**
* 查询订单
* */
@Select({"<script>" +
        "select " +
        " * " +
        " from pay_order p" +
        " where p.order_id in " +
        "<foreach collection='orderIds' item='id' open='(' separator=',' close=')'>" +
        " #{id} " +
        "</foreach>"+
        "</script>"})
List<Map> findOrderByIds(@Param("orderIds") List<Long> orderIds);
  • 测试
1
2
3
4
5
6
7
8
9
10
java复制代码@Test
public void testFindOrderByIds(){

    List<Long> ids = new ArrayList<>();
    ids.add(517020734275452928L); //order_1表
    ids.add(517020734380310529L); //order_2表

    List<Map> mapList = payOrderDao.findOrderByIds(ids);
    System.out.println(mapList);
}

ShardingJDBC执行流程

当ShardingJDBC接收到发送的SQL之后，会执行下面的步骤，最终返回执行结果

image.png

  1. SQL解析：编写SQL查询的是逻辑表，执行时ShardingJDBC要解析SQL，解析的目的是为了找到需要改写的位置。
  2. SQL路由：SQL的路由是指将对逻辑表的操作，映射到对应的数据节点的过程。ShardingJDBC会获取分片键判断是否正确，正确就执行分片策略(算法)来找到真实的表。
  3. SQL改写：程序员面向的是逻辑表编写SQL，并不能直接在真实的数据库中执行，SQL改写用于将逻辑SQL改为在真实的数据库中可以正确执行的SQL。
  4. SQL执行：通过配置规则pay_order_$->{order_id % 2 + 1}，可以知道当order_id为偶数时，应该向 pay_order_1表中插入数据，为奇数时向pay_order_2表插入数据。
  5. 将所有真正执行sql的结果进行汇总合并，然后返回。

Sharding-JDBC分库分表

水平分表

把一张表的数据按照一定规则，分配到同一个数据库的多张表中，每个表只有这个表的部分数据。在Sharding-JDBC入门使用中，我们已经完成了水平分表的操作。

水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。接下来看一下如何使用Sharding-JDBC实现水平分库

将原来的lg_order数据库，拆分为lg_order_1和lg_order_2

  1. 分片规则配置

现在是两个数据库，所以要配置两份数据源信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
properties复制代码# 定义多个数据源
spring.shardingsphere.datasource.names = db1,db2

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order_1?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/lg_order_2?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456

通过配置对数据库的分片策略，来指定数据库进行操作

1
2
3
4
properties复制代码# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作db1数据源，否则操作db2。
spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.sharding-column = user_id

spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.algorithm-expression = db$->{user_id % 2 + 1}
  1. 分库分表的策略
    • 分库策略，目的是将一个逻辑表，映射到多个数据源
1
2
properties复制代码# 分库找的是数据库 db$->{user_id % 2 + 1}
spring.shardingsphere.sharding.tables.逻辑表名称.database-strategy.分片策略.分片策略属性名 = 分片策略表达式
1
diff复制代码- 分表策略，如何将一个逻辑表，映射为多个实际表
1
2
properties复制代码#分表 找的是具体的表 pay_order_$->{order_id % 2 + 1}
spring.shardingsphere.sharding.tables.逻辑表名称.table-strategy.分片策略.algorithm-expression = 分片策略表达式
  1. Sharding-JDBC支持以下几种分片策略：
    • standard：标准分片策略
    • complex：符合分片策略
    • inline：行表达式分片策略,,使用Groovy的表达式.
    • hint：Hint分片策略，对应HintShardingStrategy。
    • none：不分片策略，对应NoneShardingStrategy。不分片的策略。

具体信息请查阅官方文档：shardingsphere.apache.org

  1. 插入测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
java复制代码@Test
public void testInsertPayOrder(){

    //user_1 为奇数,插入到 lg_order_1 数据库
    for (int i = 0; i < 5; i++) {
        //插入数据
        payOrderDao.insertPayOrder(1,"海尔电视",1);
    }

    //user_2 为偶数,插入到 lg_order_2 数据库
    for (int i = 0; i < 5; i++) {
        //插入数据
        payOrderDao.insertPayOrder(4,"王牌电视",1);
    }
}

首先会根据分库策略找到对应的数据库db$->{user_id % 2 + 1}

然后再根据分表策略找到要插入数据的表pay_order_$->{order_id % 2 + 1}

  1. 查询测试
1
2
3
4
5
6
7
8
9
10
java复制代码@Test
public void testFindOrderByIds(){

    List<Long> ids = new ArrayList<>();
    ids.add(517399941648220160L); //lg_order_1数据库的 order_1表
    ids.add(517399941518196736L); //lg_order_2数据库的 order_1表

    List<Map> mapList = payOrderDao.findOrderByIds(ids);
    System.out.println(mapList);
}

通过日志发现，sharding-jdbc将sql路由到了db1

原因在配置上有问题，数据库只指定了db1

  1. 修改数据节点配置
1
2
properties复制代码#数据节点: db1.pay_order_1 , db1.pay_order_2, db2.pay_order_1,db2.pay_order_2
spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db$->{1..2}.pay_order_$->{1..2}

垂直分库

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

在使用微服务架构时，业务切割得足够独立，数据也会按照业务切分，保证业务数据隔离，大大提升了数据库的吞吐能力。

  1. 创建数据库
1
mysql复制代码CREATE DATABASE lg_user CHARACTER SET 'utf8';
  1. 在lg_user数据库中users创建表
1
2
3
4
5
6
7
mysql复制代码DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGINT(20) PRIMARY KEY,
    username VARCHAR(20) ,
    phone VARCHAR(11),
    STATUS VARCHAR(11)
);
  1. 规则配置
  • 配置数据源信息
1
2
3
4
5
6
properties复制代码spring.shardingsphere.datasource.names = db1,db2,db3
spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/lg_user?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456
  • 配置数据节点
1
properties复制代码spring.shardingsphere.sharding.tables.users.actual-data-nodes = db$->{3}.users
1
2
properties复制代码spring.shardingsphere.sharding.tables.users.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.users.table-strategy.inline.algorithm-expression = users
  1. 测试插入与查询
  • UserDao
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
java复制代码@Mapper
@Component
public interface UsersDao {

    /**
    * 新增用户
    * */
    @Insert("INSERT INTO users(id, username,phone,status) VALUE(#{id},#{username},#{phone},#{status})")
    int insertUser(@Param("id")Long id, @Param("username")String username, @Param("phone")String phone,@Param("status")String status);

    /**
    * 查询用户
    * */
    @Select({"<script>",
            " select",
            " * ",
            " from users u ",
            " where u.id in",
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Map> selectUserbyIds(@Param("userIds")List<Long> userIds);

}
  • UserDaoTest
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
java复制代码@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class UserDaoTest {

    @Autowired
    UsersDao usersDao;

    @Test
    public void testInsert(){

        for (int i = 0; i < 10 ; i++) {
            Long id = i + 100L;
            usersDao.insertUser(id,"giao桑"+i,"13511112222", "1");
        }
    }

    @Test
    public void testSelect(){

        List<Long> ids = new ArrayList<>();
        ids.add(101L);
        ids.add(105L);

        List<Map> list = usersDao.selectUserbyIds(ids);
        System.out.println(list);
    }
}

Sharding-JDBC 操作公共表

什么是公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。

可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表的数据维护。

image.png

公共表配置与测试

  1. 创建数据库

分别在lg_order_1、lg_order_2、lg_user都创建district表

1
2
3
4
5
6
mysql复制代码-- 区域表
CREATE TABLE district (
    id BIGINT(20) PRIMARY KEY COMMENT '区域ID',
    district_name VARCHAR(100) COMMENT '区域名称',
    LEVEL INT COMMENT '等级'
);
  1. 在Sharding-JDBC的配置文件中指定公共表
1
2
3
4
5
properties复制代码# 指定district为公共表
spring.shardingsphere.sharding.broadcast-tables=district
# 主键生成策略
spring.shardingsphere.sharding.tables.district.key-generator.column=id
spring.shardingsphere.sharding.tables.district.key-generator.type=SNOWFLAKE
  1. 编写代码，操作公共表
  • DistrictDao
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
java复制代码@Mapper
@Component
public interface DistrictDao {

    /**
    * 插入数据
    * */
    @Insert("INSERT INTO district(district_name,level) VALUES(#{district_name},#{level})")
    public void insertDist(@Param("district_name") String district_name,@Param("level") int level);

    /**
    * 删除数据
    */
    @Delete("delete from district where id = #{id}")
    int deleteDict(@Param("id") Long id);

}
  • DistrictDaoTest
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
java复制代码@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class DistrictDaoTest {

    @Autowired
    DistrictDao districtDao;

    @Test
    public void testInsert(){
        districtDao.insertDist("昌平区",2);
        districtDao.insertDist("朝阳区",2);
    }

    @Test
    public void testDelete(){
        districtDao.deleteDict(523944169266216961L);
    }
}

Sharding-JDBC读写分离

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。

image.png

MySQL主从同步

为了实现Sharding-JDBC的读写分离，首先，要进行mysql的主从同步配置。

我们直接使用MyCat讲解中，在虚拟机上搭建的主从数据库

  • 在主服务器中的test数据库创建商品表
1
2
3
4
5
6
mysql复制代码CREATE TABLE products (
    pid BIGINT(32) PRIMARY KEY ,
    pname VARCHAR(50) DEFAULT NULL,
    price INT(11) DEFAULT NULL,
    flag VARCHAR(2) DEFAULT NULL
);
  • 主库新建表之后，从库会根据binlog日志，同步创建

sharding-jdbc实现读写分离

  1. 配置数据源
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
properties复制代码
# 定义多个数据源
spring.shardingsphere.datasource.names = db1,db2,db3,m1,s1

spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.52.10:3306/test?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456

spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.52.11:3306/test?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = 123456
  1. 配置主库与从库的相关信息
  • ms1包含了m1和s1
1
2
properties复制代码spring.shardingsphere.sharding.master-slave-rules.ms1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ms1.slave-data-source-names=s1
  1. 配置数据节点
1
2
properties复制代码#配置数据节点
spring.shardingsphere.sharding.tables.products.actual-data-nodes = ms1.products
  1. 编写测试代码
  • ProductsDao
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
java复制代码@Mapper
@Component
public interface ProductsDao {

    /**
    * 读写分离 插入
    * */
    @Insert("insert into products(pid,pname,price,flag) values(#{pid},#{pname},#{price},#{flag})")
    int insertProduct(@Param("pid") Long pid, @Param("pname") String pname,@Param("price") int price,@Param("flag") String flag);

    /**
    * 读写分离 查询
    * */
    @Select({"select * from products"})
    List<Map> findAll();
}
  • 测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
java复制代码@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class ProductsDaoTest {

    @Autowired
    ProductsDao productsDao;

    /**
    * 测试插入
    * */
    @Test
    public void testInsert(){

        for (int i = 0; i < 5; i++) {
            productsDao.insertProduct(100L+i,"小米手机",1888,"1");
        }

    }

    /**
    * 测试查询
    * */
    @Test
    public void testSelect(){

        List<Map> all = productsDao.findAll();
        System.out.println(all);
    }
}

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%