springboot2.1入门系列八 springboot集成sharding-jdbc

ゝ一世哀愁。 2022-03-25 08:38 360阅读 0赞

本文为Spring Boot2.1系列的第八篇，代码可以从github下载 [https://github.com/yinww/demo-springboot2.git][https_github.com_yinww_demo-springboot2.git]

当数据量过大的时候有时需要使用分库分表技术，将单表数据拆分到多个库和多个表中，在一定程度上缓解数据库的存取压力。

本章介绍当当的开源分库分表技术sharding-jdbc，演示Spring Boot结合sharding-jdbc实现分库分表。

一、准备数据库

这里以用户user表为例说明分库分表技术，我们创建2个库，每个库中有10个user表，总共就有20个user表。假设1个user表能正常存取500万的数据，那么20个表就能正常存取1亿个用户数据，对于不是特别大的项目足够用了。

创建test\_0和test\_1两个数据库：

create database test\_0;

create database test\_1;

分别在两个库中创建十个user表：

create table user_0
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_1
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_2
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_3
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_4
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_5
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_6
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_7
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_8
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
    
    create table user_9
    (
       id                   bigint not null,
       name            varchar(64),
       primary key (id)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;

二、创建工程demo008

pom.xml的内容为

<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">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>com.yinww</groupId>
            <artifactId>demo-springboot2</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <artifactId>demo008</artifactId>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.2</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>3.0.0</version>
            </dependency>
        </dependencies>
    </project>

三、Java代码

DataSourceConfig： DataSource及分库分表配置类，这是关键

package com.yinww.demo.springboot2.demo008.config;
    
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Properties;
    import java.util.concurrent.ConcurrentHashMap;
    
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.jdbc.DataSourceBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    
    import com.zaxxer.hikari.HikariDataSource;
    
    import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
    import io.shardingsphere.api.config.ShardingRuleConfiguration;
    import io.shardingsphere.api.config.TableRuleConfiguration;
    import io.shardingsphere.api.config.strategy.InlineShardingStrategyConfiguration;
    import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
    import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
    
    @Configuration
    public class DataSourceConfig {
    
        @Autowired
        private Environment env;
    
        @SuppressWarnings({ "rawtypes", "unchecked" })
        @Bean
        public DataSource getDataSource() throws SQLException {
            // 配置表规则
            TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
            tableRuleConfig.setLogicTable(env.getProperty("shardingjdbc.logic-table"));
            String actualDataNdes = env.getProperty("shardingjdbc.actual-data-nodes");
            tableRuleConfig.setActualDataNodes(actualDataNdes.replace("->", ""));
    
            // 配置分库 + 分表策略
            String databaseShardingColumn = env.getProperty("shardingjdbc.database-strategy.inline.sharding-column");
            String databaseAlgorithmExpression = env.getProperty("shardingjdbc.database-strategy.inline.algorithm-expression");
            String tableShardingColumn = env.getProperty("shardingjdbc.tables.standard.sharding-column");
            tableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration(
                    databaseShardingColumn, databaseAlgorithmExpression.replace("->", "")));
    
            String preciseAlgorithmClassName = env.getProperty("shardingjdbc.tables.standard.precise-algorithm-class-name");
            try {
                PreciseShardingAlgorithm algorithm = (PreciseShardingAlgorithm) Class.forName(preciseAlgorithmClassName).newInstance();
                tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(tableShardingColumn, algorithm));
            } catch (Exception e) {
                e.printStackTrace();
            }
    
            // 配置分片规则
            ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
            shardingRuleConfig.setDefaultDataSourceName(env.getProperty("shardingjdbc.default-data-source-name"));
            shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);
    
            // 获取数据源对象
            return ShardingDataSourceFactory.createDataSource(getDataSourceMap(), shardingRuleConfig,
                    new ConcurrentHashMap(), new Properties());
        }
    
        private Map<String, DataSource> getDataSourceMap() {
            Map<String, DataSource> dataSourceMap = new HashMap<>();
            String[] names = env.getProperty("shardingjdbc.datasource.names").split(",");
            Properties properties = getProperties();
            for (String name : names) {
                dataSourceMap.put(name, buildDataSource(name, properties));
            }
            return dataSourceMap;
        }
    
        @SuppressWarnings("unchecked")
        private DataSource buildDataSource(String name, Properties properties) {
            try {
                String prefix = "shardingjdbc.datasource." + name;
                String type = env.getProperty(prefix + ".type");
                Class<DataSource> typeClass = (Class<DataSource>) Class.forName(type);
                String driverClassName = env.getProperty(prefix + ".driver-class-name");
                String url = env.getProperty(prefix + ".url");
                String username = env.getProperty(prefix + ".username");
                String password = env.getProperty(prefix + ".password");
                DataSource dataSource = DataSourceBuilder.create().type(typeClass).driverClassName(driverClassName).url(url)
                        .username(username).password(password).build();
                if (dataSource instanceof HikariDataSource) {
                    ((HikariDataSource) dataSource).setDataSourceProperties(properties);
                }
                return dataSource;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    
        private Properties getProperties() {
            Properties p = new Properties();
            p.put("minimum-idle", env.getProperty("hikari.minimum-idle"));
            p.put("maximum-pool-size", env.getProperty("hikari.maximum-pool-size"));
            p.put("auto-commit", env.getProperty("hikari.auto-commit"));
            p.put("idle-timeout", env.getProperty("hikari.idle-timeout"));
            p.put("max-lifetime", env.getProperty("hikari.max-lifetime"));
            p.put("connection-timeout", env.getProperty("hikari.connection-timeout"));
            p.put("connection-test-query", env.getProperty("hikari.connection-test-query"));
            return p;
        }
    
    }

Demo008PreciseShardingAlgorithm： 分片算法类

package com.yinww.demo.springboot2.demo008.sharding;
    
    import java.util.Collection;
    
    import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
    import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
    
    public class Demo008PreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    
        @Override
        public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
            String postfix = "" + (shardingValue.getValue() / 2) % 10;
            for (String tableName : availableTargetNames) {
                if (tableName.endsWith(postfix)) {
                    return tableName;
                }
            }
            throw new IllegalArgumentException();
        }
    
    }

主类

package com.yinww.demo.springboot2.demo008;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class Demo008Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Demo008Application.class, args);
        }
    
    }

User类

package com.yinww.demo.springboot2.demo008.domain;
    
    public class User {
    
        private Long id;
        private String name;
    
        // gette and setter
    
    }

UserMapper类，提供保存和读取的接口

package com.yinww.demo.springboot2.demo008.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Mapper;
    
    import com.yinww.demo.springboot2.demo008.domain.User;
    
    @Mapper
    public interface UserMapper {
    
        void addUser(User user);
    
        List<User> getUsers();
    
    }

UserService

package com.yinww.demo.springboot2.demo008.service;
    
    import java.util.List;
    
    import com.yinww.demo.springboot2.demo008.domain.User;
    
    public interface UserService {
        
        void addUser(User user);
        
        List<User> getUsers();
    
    }

UserServiceImpl

package com.yinww.demo.springboot2.demo008.service.impl;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.yinww.demo.springboot2.demo008.domain.User;
    import com.yinww.demo.springboot2.demo008.mapper.UserMapper;
    import com.yinww.demo.springboot2.demo008.service.UserService;
    
    @Service
    public class UserServiceImpl implements UserService {
        
        @Autowired
        private UserMapper userMapper;
    
        @Override
        public void addUser(User user) {
            userMapper.addUser(user);
        }
    
        @Override
        public List<User> getUsers() {
            return userMapper.getUsers();
        }
    
    }

UserController

package com.yinww.demo.springboot2.demo008.controller;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.yinww.demo.springboot2.demo008.domain.User;
    import com.yinww.demo.springboot2.demo008.service.UserService;
    
    @RestController
    public class UserController {
        
        @Autowired
        private UserService userService;
        
        @ResponseBody
        @RequestMapping("add")
        public Object addUser() {
            for (int i = 0; i < 25; i++) {
                User user = new User();
                user.setId(new Long(i));
                user.setName("name_" + i);
                userService.addUser(user);
            }
            return "ok";
        }
        
        @ResponseBody
        @RequestMapping("get")
        public Object getUsers() {
            List<User> users = userService.getUsers();
            return users;
        }
    
    }

四、配置文件

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="com.yinww.demo.springboot2.demo008.mapper.UserMapper">
        <resultMap id="UserResultMap" type="com.yinww.demo.springboot2.demo008.domain.User">
            <id column="id" jdbcType="BIGINT" property="id"/>
            <result column="name" jdbcType="VARCHAR" property="name"/>
        </resultMap>
    
        <select id="getUsers" resultMap="UserResultMap">
            select * from user order by id;
        </select>
    
        <insert id="addUser">
            insert into user (id, name) values(#{id}, #{name})
        </insert>
    </mapper>

application.properties

spring.application.name=mail
    
    # ds0 datasource
    shardingjdbc.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
    shardingjdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
    shardingjdbc.datasource.ds0.url=jdbc:mysql://127.0.0.1:3306/test_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8
    shardingjdbc.datasource.ds0.username=root
    shardingjdbc.datasource.ds0.password=root
    
    # ds1 datasource
    shardingjdbc.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
    shardingjdbc.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
    shardingjdbc.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8
    shardingjdbc.datasource.ds1.username=root
    shardingjdbc.datasource.ds1.password=root
    
    shardingjdbc.datasource.names=ds0,ds1
    shardingjdbc.logic-table=user
    shardingjdbc.actual-data-nodes=ds$->{0..1}.user_$->{[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]}
    shardingjdbc.database-strategy.inline.sharding-column=id
    shardingjdbc.database-strategy.inline.algorithm-expression=ds$->{id % 2}
    shardingjdbc.tables.standard.sharding-column=id
    shardingjdbc.tables.standard.precise-algorithm-class-name=com.yinww.demo.springboot2.demo008.sharding.Demo008PreciseShardingAlgorithm
    shardingjdbc.default-data-source-name=ds0
    
    hikari.minimum-idle=5
    hikari.maximum-pool-size=15
    hikari.auto-commit=true
    hikari.idle-timeout=30000
    hikari.max-lifetime=1800000
    hikari.connection-timeout=30000
    hikari.connection-test-query=SELECT 1
    
    mybatis.type-aliases-package=com.yinww.demo.springboot2.demo008.domain
    mybatis.mapper-locations=classpath*:mapper/*.xml

五、运行

执行

java -jar demo008-0.0.1-SNAPSHOT.jar

添加24条数据

![20190119190156904.png][]

获取数据，可以看到插入了24条记录

![20190119190221321.png][]

再查看两个数据库中的20个user表，可以看到表中的数据是按程序的分片算法分配到各表中的。

本文内容到此结束，更多内容可关注公众号：

![2018111718160012.jpg][]

[https_github.com_yinww_demo-springboot2.git]: https://github.com/yinww/demo-springboot2.git
[20190119190156904.png]: /images/20220325/733facfdb5704c72bad596c980c9b445.png
[20190119190221321.png]: /images/20220325/832f915bf4f5415ebab51b3c5d673bdf.png
[2018111718160012.jpg]: /images/20220325/ba666dc4bd094655a6ef726d5371e1ef.png