Sharding-Sphere：Sharding-JDBC分库分表（基于MyBatis）

Joe.Ye • 2023-04-01 • Sharding-Sphere

分享了 Sharding-JDBC 搭建读写分离，本章将用 Sharding-JDBC 实现分库分表功能

为什么要分库分表？

举个栗子：当单表数据量超过1000万后，查询的速度将会慢下来，利用索引，读写分离等优化，如果当数据量超过5000万时，一些常见的优化方法将失去作用，此时把单表水平划分到多库多表中，提升查询速度，而sharding-jdbc可以帮助完成水平拆分，而我们只需添加一些配置即可

官方文档

GitHub的地址：https://github.com/apache/incubator-shardingsphere
shardingsphere：https://github.com/apache/incubator-shardingsphere-example
中文文档地址：https://shardingsphere.apache.org/document/current/cn/overview/

代码实现（MyBatis）

（1）有3张表t_member、t_order、t_order_item，拆分到2个数据库中，每个数据库2份，相当于每张表拆成了4张，需要注意的是每个表的主键Id尽量设置成 bigint （我开始设置为varchar，结果分表时报错），结构如下：

分库分表设计

-- ----------------------------
-- Table structure for t_member0
-- ----------------------------
DROP TABLE IF EXISTS `t_member0`;
CREATE TABLE `t_member0` (
  `member_id` bigint(20) NOT NULL,
  `member_name` varchar(36) DEFAULT NULL,
  `nick_name` varchar(36) DEFAULT NULL,
  `account_no` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth_date` datetime DEFAULT NULL,
  `ebl_flag` varchar(1) DEFAULT '1',
  `del_flag` varchar(1) DEFAULT '0',
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_member1
-- ----------------------------
DROP TABLE IF EXISTS `t_member1`;
CREATE TABLE `t_member1` (
  `member_id` bigint(20) NOT NULL,
  `member_name` varchar(36) DEFAULT NULL,
  `nick_name` varchar(36) DEFAULT NULL,
  `account_no` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth_date` datetime DEFAULT NULL,
  `ebl_flag` varchar(1) DEFAULT '1',
  `del_flag` varchar(1) DEFAULT '0',
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_order0
-- ----------------------------
DROP TABLE IF EXISTS `t_order0`;
CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL,
  `member_id` varchar(36) DEFAULT NULL,
  `order_code` varchar(36) DEFAULT NULL,
  `order_amount` varchar(20) DEFAULT NULL,
  `status` varchar(1) DEFAULT '1',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order1
-- ----------------------------
DROP TABLE IF EXISTS `t_order1`;
CREATE TABLE `t_order1` (
  `order_id` bigint(20) NOT NULL,
  `member_id` varchar(36) DEFAULT NULL,
  `order_code` varchar(36) DEFAULT NULL,
  `order_amount` double DEFAULT NULL,
  `status` varchar(1) DEFAULT '1',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order_item0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item0`;
CREATE TABLE `t_order_item0` (
  `item_id` bigint(20) NOT NULL,
  `order_id` varchar(36) DEFAULT NULL,
  `product_name` varchar(128) DEFAULT NULL,
  `item_account` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

-- ----------------------------
-- Table structure for t_order_item1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item1`;
CREATE TABLE `t_order_item1` (
  `item_id` bigint(20) NOT NULL,
  `order_id` varchar(36) DEFAULT NULL,
  `product_name` varchar(128) DEFAULT NULL,
  `item_account` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

（2）新建SpringBoot项目，引入依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.16</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

（3）引入 mybatis generator 自动生成代码插件，生成实体类与mybatis xml文件

<plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.3.6</version>
    <configuration>
        <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
        <overwrite>true</overwrite>
        <verbose>true</verbose>
    </configuration>
</plugin>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
    <classPathEntry  location="D:\software\database\mybatis-generator-core-1.3.2\lib\mysql-connector-java-5.1.25-bin.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3" defaultModelType="flat">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- 是否去除自动生成的注释 true: 是, false: 否 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" userId="xnxx" password="Xnxx85781245!" connectionURL="jdbc:mysql://47.107.177.150:3306/zy_sharding_business1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&maxReconnects=15000&allowMultiQueries=true">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- 生成模型的包名和位置-->
        <javaModelGenerator targetPackage="com.zypcy.sharding.sublibrarytable.entity" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- 生成映射文件的包名和位置-->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.zypcy.sharding.sublibrarytable.repository" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
        <!-- 注意：已生成的不要再生成，不然会覆盖已生成的文件 -->
        <!--
        <table tableName="t_member" domainObjectName="Member" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="t_order" domainObjectName="Order" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="t_order_item" domainObjectName="OrderItem" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        -->
    </context>
</generatorConfiguration>

（4）添加配置文件，这点很重要，否则无法实现分库分表，ds$->{0..1}.t_member$->{0..1}，表达式相当于：ds0.t_member0、ds0.t_member1、ds1.t_member0、ds1.t_member1，在配置中配置了key-generator.column，所以新增该表数据时，不需要给主键id赋值，sharding-jdbc会调用内置的SNOWFLAKE算法生成分布式id，请往下看

server.port=8071

# mybatis 配置
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=com.zypcy.sharding.sublibrarytable.entity

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 分库配置，根据member_id分库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{member_id % 2}

# 分表配置，根据member_id分表
spring.shardingsphere.sharding.tables.t_member.actual-data-nodes=ds$->{0..1}.t_member$->{0..1}
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.algorithm-expression=t_member$->{member_id % 2}
spring.shardingsphere.sharding.tables.t_member.key-generator.column=member_id
spring.shardingsphere.sharding.tables.t_member.key-generator.type=SNOWFLAKE

# 分表配置，根据order_id分表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
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}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 分表配置，根据order_id分表
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.binding-tables=t_member,t_order,t_order_item
#spring.shardingsphere.sharding.broadcast-tables=t_config

spring.shardingsphere.props.sql.show=true

（5）Durid配置

@Configuration
public class DuridConfig {

    @Bean
    public Filter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(5000);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }

    @Bean
    public ServletRegistrationBean statViewServlet() {
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        //设置ip黑名单，如果allow与deny共同存在时,deny优先于allow
        //servletRegistrationBean.addInitParameter("deny","192.168.0.19");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }
}

（6）添加MemberService服务接口与实现

public interface IMemberService {

    int insert(Member record);

    Member selectByPrimaryKey(Long memberId);

    int deleteByPrimaryKey(Long memberId);
}

@Service
public class MemberServiceImpl implements IMemberService {

    @Autowired private MemberMapper memberMapper;

    @Override
    public int insert(Member record) {
        return memberMapper.insertSelective(record);
    }

    @Override
    public Member selectByPrimaryKey(Long memberId) {
        return memberMapper.selectByPrimaryKey(memberId);
    }

    @Override
    public int deleteByPrimaryKey(Long memberId) {
        return memberMapper.deleteByPrimaryKey(memberId);
    }
}

@Mapper
public interface MemberMapper {
    int deleteByPrimaryKey(Long memberId);

    int insert(Member record);

    int insertSelective(Member record);

    Member selectByPrimaryKey(Long memberId);

    int updateByPrimaryKeySelective(Member record);

    int updateByPrimaryKey(Member record);
}

（7）添加MemberController

@RequestMapping("/member")
@RestController
public class MemberController {

    @Autowired
    private IMemberService memberService;

    @RequestMapping("/add")
    public Member add(){
        Member member = new Member();
        //member.setMemberId(IdWorker.getLongId()); 不用手动设置主键id，新增时，sharding-jdbc会自动赋值，因为在配置文件中配置了该列使用SNOWFLAKE算法生成值
        member.setMemberName("张三");
        member.setNickName("闪耀的瞬间");
        member.setAccountNo(member.getMemberId()+"");
        member.setPassword("123465");
        member.setAge(27);
        member.setBirthDate(new Date());
        member.setEblFlag("1");
        member.setDelFlag("0");
        member.setDescription("xxx");
        member.setCreateTime(new Date());
        member.setUpdateTime(new Date());
        memberService.insert(member);
        return member;
    }

    @RequestMapping("/findById")
    public Member findById(Long memberId){
        return memberService.selectByPrimaryKey(memberId);
    }

    @RequestMapping("/delete")
    public String delete(Long memberId){
        memberService.deleteByPrimaryKey(memberId);
        return "success";
    }
}

（8）启动项目，访问新增接口 http://localhost:8071/member/add ，可以看到页面返回了新增后的数据

同时查看Intellij IDEA控制台，可以看到数据插入到ds1库的t_member1表了

再查看ds1数据库的t_member1表，有刚刚新增的记录

（9）访问查询接口 http://localhost:8071/member/findById?memberId=xxxxxx

同时查看控制台，sql会自动路由到ds1库去查询数据

参考：https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business/sub-library-table

版权声明：
作者：Joe.Ye
链接：https://www.appblog.cn/index.php/2023/04/01/sharding-sphere-sharding-jdbc-sub-database-and-sub-table-based-on-mybatis/
来源：APP全栈技术分享
文章版权归作者所有，未经允许请勿转载。

THE END
分享
二维码
打赏
海报
Sharding-Sphere：Sharding-JDBC分库分表（基于MyBatis）
分享了 Sharding-JDBC 搭建读写分离，本章将用 Sharding-JDBC 实现分库分表功能 为什么要分库分表？ 举个栗子：当单表数据量超过1000万后，查询的速度将会慢下……
<<上一篇
下一篇>>
文章目录
关闭
目 录