迦南地
所有文章 标签 分类 实验室 关于
迦南地

目录

sharding-jdbc快速入门

 收录于 技术
   约 2285 字   预计阅读 5 分钟    次阅读  

需求说明

本章节使用Sharding-JDBC完成对订单表的水平分表，通过快速入门程序的开发，快速体验Sharding-JDBC的使用 方法。

人工创建两张表，t_order_1和t_order_2，这两张表是订单表拆分后的表，通过Sharding-Jdbc向订单表插入数据， 按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过Sharding-Jdbc 查询数 据，根据 SQL语句的内容从t_order_1或t_order_2查询数据。

环境搭建

环境说明：

  • 数据库：mysql-5.7
  • jdk：1.8
  • springboot:2.1.3.RELEASE
  • sharding-jdbc-spring-boot-starter:4.0.0-RC1
  • mybatis-spring-boot-starter:2.0.0
  • druid-spring-boot-starter:1.1.16
  • mysql-connector-java:5.1.47

数据库：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

version: '2'
services: 
    standalone: 
        image: mysql:5.7
        container_name: standalone
        volumes: 
            - ./standalone/log:/var/log/mysql
            - ./standalone/data:/var/lib/mysql
            - ./standalone/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
            - MYSQL_ROOT_HOST=%
        ports: 
            - 3306:3306

数据准备：

 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

CREATE DATABASE `order_db` CHARACTER 
SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
	`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
	`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
	`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
	`status` VARCHAR ( 50 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
	PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
	`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
	`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
	`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
	`status` VARCHAR ( 50 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
	PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

show tables;

编写程序

pom

引入 sharding-jdbc和SpringBoot整合的Jar包：

1
2
3
4
5
6

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

完整pom配置

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>sharding-jdbc-test</artifactId>
        <groupId>com.eh</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharding-jdbc-simple</artifactId>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--数据库-->
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <!--数据源druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>
        <!--mysql-connector-java-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

</project>

yml

 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

debug=true
server.port=8001
spring.application.name=sharding-jdbc-simple
# 使用bean定义覆盖，sharding-jdbc的datasource覆盖druid定义的datasource，如果不覆盖会报
# Invalid bean definition with name 'dataSource' defined in class path resource
spring.main.allow-bean-definition-overriding = true
# 以下是分片规则配置
# 定义数据源
# 数据源名称
spring.shardingsphere.datasource.names=m1
# 给m1数据源设置连接属性
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://localhost:3306/order_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=333
# 指定t_order表的数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
# 分片键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=\
  t_order_$->{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

主启动

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

package com.eh.shardingjdbc.order;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.eh.shardingjdbc.order.dao")
@SpringBootApplication
public class OrderMainApplication {
    public static void main(String[] args) {
        SpringApplication.run(OrderMainApplication.class, args);
    }
}

业务类

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

package com.eh.shardingjdbc.order.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

import java.math.BigDecimal;

public interface OrderMapper {
    /**
     * 新增订单 * @param price 订单价格 * @param userId 用户id * @param status 订单状态 * @return
     */
    @Insert("insert into t_order(price,user_id,status) value(#{price},#{userId},#{status})")
    int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);
}

测试类

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

package com.eh.shardingjdbc.order.dao;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;

@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderMapperTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void insertOrder() {
        for (int i = 1; i < 20; i++) {
            orderMapper.insertOrder(new BigDecimal(i), 1L, "SUCCESS");
        }
    }
}

往数据库插入了19条数据，按照分表规则，应该是偶数id进t_order_1表，奇数id进t_order_2表，查看数据库，确实如此

http://img.cana.space/picStore/20201122183458.png

再来一个查询测试

OrderMapper

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

 /**
     * 根据id列表查询订单
     *
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderByIds(@Param("orderIds") List<Long> orderIds);

测试类：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

/**
 * 查询两个表中的id
 */
@Test
public void testSelectOrderByIds() {
    List<Long> ids = new ArrayList<>();
    ids.add(537337422749368320L);
    ids.add(537337421923090433L);

    List<Map> maps = orderMapper.selectOrderByIds(ids);
    System.out.println(maps);
}

执行日志：

http://img.cana.space/picStore/20201122184519.png

流程分析

通过日志分析，Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿：

  1. 解析sql，获取片键值，在本例中是order_id
  2. Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往 t_order_1表插数据，为奇数时，往t_order_2插数据。
  3. 于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语句。
  4. 执行改写后的真实sql语句
  5. 将所有真正执行sql的结果进行汇总合并，返回。

其他配置方式

Sharding-JDBC可以与spring boot良好集成，它还支持其他配置方式，共支持以下四种集成方式。

  • yml（配置复杂了之后看起来杂乱无章）

  • properties(推荐)

  • Java配置类

     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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

    package com.eh.shardingjdbc.order.dao;
  
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
  
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
  
@Configuration
public class ShardingJdbcConfig {
  
    // 定义数据源
    Map<String, DataSource> createDataSourceMap() {
  
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        Map<String, DataSource> result = new HashMap<>();
        result.put("m1", dataSource1);
        return result;
  
    }
  
    // 定义主键生成策略
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "order_id");
        return result;
    }
  
    // 定义t_order表的分片策略
    TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("t_order", "m1.t_order_$‐> {1..2}");
        result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$‐>{order_id % 2 + 1}"));
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        return result;
    }
  
    // 定义sharding‐Jdbc数据源
    @Bean
    DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        Properties properties = new Properties();
        //spring.shardingsphere.props.sql.show = true
        properties.put("sql.show", "true");
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }
}

  • xml（不推荐）

     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
30
31
32
33
34
35
36

    <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd http://shardingsphere.apache.org/schema/shardingsphere/sharding
        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
    <context:annotation-config/>
  
    <!--定义多个数据源-->
    <bean id="m1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/order_db_1?useUnicode=true"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
  
    <!--定义分库策略-->
    <sharding:inline-strategy id="tableShardingStrategy" sharding-column="order_id"
                              algorithm-expression="t_order_$->{order_id % 2 + 1}"/>
  
    <!--定义主键生成策略-->
    <sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>
  
    <!--定义sharding-Jdbc数据源-->
    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="m1">
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" table-strategy-ref="tableShardingStrategy"
                                     key-generator-ref="orderKeyGenerator"/>
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
</beans>
更新于 2020-11-22
 sharding-jdbc
返回 | 主页