ShardingJDBC分库分表+读写分离
准备工作
共四台Mysql,每台Mysql有一个user数据库,每个user库有3张表(user_tb_0,user_tb_1,user_tb_2),共4*3=12张表,表结构都是一样的。
两台master,两台slave。如下图。slave和master实时同步备份,master是主数据库(写操作),slave是从数据库(读操作)。
表结构如下:
CREATE TABLE `user_tb_0` (
`id` bigint(20) NOT NULL,
`username` varchar(40) NOT NULL COMMENT '用户名',
`password` varchar(200) NOT NULL COMMENT '密码',
`nickname` varchar(100) DEFAULT NULL COMMENT '昵称',
`money` decimal(16,4) NOT NULL DEFAULT '0.0000' COMMENT '金额',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除',
`create_date` datetime NOT NULL COMMENT '创建时间',
`modify_date` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
pom.xml 添加以下依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.1</version>
</dependency>
<!--阿里数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
<!--分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
application.yml配置如下
server:
port: 8001
spring:
# main:
# allow-bean-definition-overriding: true
application:
name: sharding-jdbc-example
shardingsphere:
props:
sql:
show: true
# 数据源配置,可配置多个
datasource:
names: master0,master1,slave0,slave1
master0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3310/user?useUnicode=true&useSSL=false
username: root
password: '123456'
slave0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3311/user?useUnicode=true&useSSL=false
username: root
password: '123456'
master1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3312/user?useUnicode=true&useSSL=false
username: root
password: '123456'
slave1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3313/user?useUnicode=true&useSSL=false
username: root
password: '123456'
sharding:
master-slave-rules:
ms0:
master-data-source-name: master0
slave-data-source-names: slave0
ms1:
master-data-source-name: master1
slave-data-source-names: slave1
tables:
user:
key-generator:
column: id
type: SNOWFLAKE
actual-data-nodes: ms$->{0..1}.user_tb_$->{0..2}
database-strategy:
inline:
sharding-column: id
algorithm-expression: ms$->{(id / 10).toBigInteger() % 2}
# algorithm-expression: ms$->{id % 2}
table-strategy:
inline:
sharding-column: id
algorithm-expression: user_tb_$->{id % 3}
mybatis:
type-aliases-package: com.cunzaizhe.entity
mapper-locations: classpath:mapper/*Mapper.xml
pagehelper:
helper-dialect: mysql
support-methods-arguments: true
params: count=countSql
reasonable: false
- 分库分表原理:先根据id的十位上的数%2被平均分到master0和master1数据库上,然后再根据id个位上的数%3被平均分到user_tb_0,user_tb_1,user_tb_2上。
- master0,master1,slave0,slave1四个数据源
- 根据用户id来分库分表
- 分库规则 ms$->{(id / 10).toBigInteger() % 2} 十位上的数模2
- 分表规则 user_tb_$->{id % 3} 个位上的数模3
测试一下效果
共插入了300万条数据 平均每张表50万条左右
根据id查询一条记录 是770毫秒
用户名模糊查询 并按照时间排序,分页取前十条 是1894毫秒
ps:是在一台电脑,docker部署的4台mysql
实际情况根据业务发展趋势和预估在前期设计好数据库,分库分表怎么分,如果是财大气粗可以直接上TiDB,但对服务器配置要求比较高。TiDB是分布式数据库,兼容MySQL,让数据库就做它自己的事情。