目录

sharding-jdbc快速入门

需求说明

本章节使用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>