好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

Java使用Sharding-JDBC分库分表进行操作

Sharding-JDBC 是无侵入式的 MySQL 分库分表 操作工具，所有库表设置仅需要在配置文件中配置即可，无须修改任何代码。
本文写了一个 Demo，使用的是 SpringBoot 框架，通过 Docker 进行 MySQL 实例管理，分库分表结构如下图，同时所有的库都进行了主从复制：

主从库搭建

Docker 项目结构：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

docker

├── docker-compose.yml

├── master

│   ├── data

│   ├── log

│   │   └── error.log

│   ├── my.cnf

│   └── mysql-files  # Win 需要，Linux 不需要

├── README.md

└── slave

     ├── data

     ├── log

     │   └── error.log

     ├── my.cnf

     └── mysql-files

Compose File

?

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

version: '3'

 

networks:

   sharding-jdbc-demo:

     driver: bridge

     ipam:

       config:

         - subnet: 172.25.0.0/24

 

services:

   master:

     image: mysql

     container_name: sharding-jdbc-demo-master

     ports:

       - "3307:3306"

     volumes:

       - "./master/data:/var/lib/mysql"

       - "./master/mysql-files:/var/lib/mysql-files"         # win 下的 MySQL8 需要，Linux 不需要

       - "./master/log/error.log:/var/log/mysql/error.log"

       - "./master/my.cnf:/etc/mysql/my.cnf"

     environment:

       MYSQL_ROOT_PASSWORD: 123456

     entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf && exec /entrypoint.sh mysqld"

     restart: unless-stopped

     networks:

       sharding-jdbc-demo:

         ipv4_address: 172.25.0.101

 

   slave:

     image: mysql

     container_name: sharding-jdbc-demo-slave

     ports:

       - "3308:3306"

     volumes:

       - "./slave/data:/var/lib/mysql"

       - "./slave/mysql-files:/var/lib/mysql-files"

       - "./slave/log/error.log:/var/log/mysql/error.log"

       - "./slave/my.cnf:/etc/mysql/my.cnf"

     environment:

       MYSQL_ROOT_PASSWORD: 123456

     entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf  && exec /entrypoint.sh mysqld"

     restart: unless-stopped

     networks:

       sharding-jdbc-demo:

         ipv4_address: 172.25.0.102

Master 配置

?

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

[mysqld]

pid-file         = /var/run/mysqld/mysqld.pid

socket           = /var/run/mysqld/mysqld.sock

datadir          = /var/lib/mysql

log-error        = /var/log/mysql/error.log

bind-address     = 0.0.0.0

secure-file-priv = NULL

max_connections  = 16384

 

character-set-server = utf8mb4

collation-server     = utf8mb4_general_ci

init_connect         ='SET NAMES utf8mb4'

skip-name-resolve

 

server_id = 1

log-bin = mysql-bin

binlog-do-db = db_order_1 # 复制 db_order_1

binlog-do-db = db_order_2 # 复制 db_order_2

binlog-do-db = db_user    # 复制 db_user

log-slave-updates

sync_binlog = 1

auto_increment_offset = 1

auto_increment_increment = 1

expire_logs_days = 7

log_bin_trust_function_creators = 1

 

# Custom config should go here

!includedir /etc/mysql/conf.d/

Slave 配置

?

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

[mysqld]

pid-file         = /var/run/mysqld/mysqld.pid

socket           = /var/run/mysqld/mysqld.sock

datadir          = /var/lib/mysql

log-error        = /var/log/mysql/error.log

bind-address     = 0.0.0.0

secure-file-priv = NULL

max_connections  = 16384

 

character-set-server = utf8mb4

collation-server     = utf8mb4_general_ci

init_connect         ='SET NAMES utf8mb4'

skip-name-resolve

skip-host-cache

 

server_id = 2

log-bin = mysql-bin

log-slave-updates

sync_binlog = 0

innodb_flush_log_at_trx_commit = 0  # 提交策略

replicate-do-db = db_order_1        # 复制 db_order_1

replicate-do-db = db_order_2        # 复制 db_order_2

replicate-do-db = db_user           # 复制 db_user

slave-net-timeout = 60              # 重连时间

log_bin_trust_function_creators = 1

 

# Custom config should go here

!includedir /etc/mysql/conf.d/

主从配置

启动容器 docker compose up -d;
登录 Master mysql -uroot -h 127.0.0.1 -P 3307 -p ;
查看 master 状态。

?

1

2

3

4

5

6

7

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000004                            # 记住 Bin log 当前文件名称

Position: 156                                     # 记住 Bin log 当前偏移量

Binlog_Do_DB: db_order_1,db_order_2,db_user       # 确认复制数据库是否正确

Binlog_Ignore_DB:

Executed_Gtid_Set:

登录 Slave mysql -uroot -h 127.0.0.1 -P 3308 -p
设置 Master 连接，注意 host 与 port 是内网的地址和端口。

?

1

2

3

4

5

6

mysql> change master to master_host= '172.25.0.101' ,

     master_user= 'root' ,

     master_password= '123456' ,

     master_port=3306,

     master_log_file= 'mysql-bin.000004' ,

     master_log_pos=156;

启动同步

?

1

mysql> start slave;

查看 Slave 状态，若 Slave_IO 与 Slave_SQL 都在运行为 YES 即成功。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host: 172.25.0.101

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 156

Relay_Log_File: d2a706a02933-relay-bin.000002

Relay_Log_Pos: 324

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: db_order_1,db_order_2,db_user

创建分库分表

登录 Master，创建数据库：

?

1

2

3

CREATE DATABASE db_order_1;

CREATE DATABASE db_order_2;

CREATE DATABASE db_user;

此时从库也会创建数据库，若没有，则是主从配置失败了。
此时已完成垂直分库和水平分库。接下来创建数据表：

Order 1 库

先 USE db_order_1;，再分别创建 t_dict 全局表、t_order_1 和 t_order_2 水平分表。

?

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

DROP TABLE IF EXISTS `t_dict`;

CREATE TABLE `t_dict`

(

     `id`         int                                     NOT NULL AUTO_INCREMENT,

     `type`       int                                     NOT NULL ,

     `enum_value` int                                     NOT NULL ,

     ` name `       varchar (64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,

     PRIMARY KEY (`id`)

) ENGINE = InnoDB

   AUTO_INCREMENT = 7

   DEFAULT CHARSET = utf8mb4

   COLLATE = utf8mb4_general_ci;

LOCK TABLES `t_dict` WRITE;

INSERT INTO `t_dict` VALUES (1,1,0, '未定义' ),(2,1,1, '未付款' ),(3,1,2, '已付款' ),(4,1,3, '退款中' ),(5,1,4, '已退款' ),(6,1,5, '已完成' ),(7,2,0, '未定义' ),(8,2,1, '已创建' ),(9,2,2, '已验证' ),(10,2,3, '已冻结' ),(11,2,4, '已注销' ),(12,2,5, '已删除' );

UNLOCK TABLES;

 

DROP TABLE IF EXISTS `t_order_1`;

CREATE TABLE `t_order_1`

(

     `id`      bigint          NOT NULL ,

     `user_id` bigint          NOT NULL ,

     `price`   decimal (10, 2) NOT NULL ,

     `status`  int             NOT NULL DEFAULT '1' ,

     PRIMARY KEY (`id`)

) ENGINE = InnoDB

   DEFAULT CHARSET = utf8mb4

   COLLATE = utf8mb4_general_ci;

 

DROP TABLE IF EXISTS `t_order_2`;

CREATE TABLE `t_order_2`

(

     `id`      bigint          NOT NULL ,

     `user_id` bigint          NOT NULL ,

     `price`   decimal (10, 2) NOT NULL ,

     `status`  int             NOT NULL DEFAULT '1' ,

     PRIMARY KEY (`id`)

) ENGINE = InnoDB

   DEFAULT CHARSET = utf8mb4

   COLLATE = utf8mb4_general_ci;

Order 2 库

先 USE db_order_2;，再分别创建 t_dict 全局表、t_order_1 和 t_order_2 水平分表。所执行 SQL 与 db_order_1 一致。

User 库

先 USE db_user;，再分别创建 t_dict 全局表和 t_user 表，此处就不进行水平或垂直分表了。垂直分表 sharding-jdbc 不会去处理，因为垂直分表之后就是异表异构了，执行 Join 操作就可以了，或者代码进行多次查询实现。

?

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

DROP TABLE IF EXISTS `t_dict`;

CREATE TABLE `t_dict`

(

     `id`         int                                     NOT NULL AUTO_INCREMENT,

     `type`       int                                     NOT NULL ,

     `enum_value` int                                     NOT NULL ,

     ` name `       varchar (64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,

     PRIMARY KEY (`id`)

) ENGINE = InnoDB

   AUTO_INCREMENT = 7

   DEFAULT CHARSET = utf8mb4

   COLLATE = utf8mb4_general_ci;

LOCK TABLES `t_dict` WRITE;

INSERT INTO `t_dict` VALUES (1,1,0, '未定义' ),(2,1,1, '未付款' ),(3,1,2, '已付款' ),(4,1,3, '退款中' ),(5,1,4, '已退款' ),(6,1,5, '已完成' ),(7,2,0, '未定义' ),(8,2,1, '已创建' ),(9,2,2, '已验证' ),(10,2,3, '已冻结' ),(11,2,4, '已注销' ),(12,2,5, '已删除' );

UNLOCK TABLES;

 

 

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user`

(

     `id`   bigint                                   NOT NULL AUTO_INCREMENT,

     ` name ` varchar (128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,

     `type` int                                      NOT NULL DEFAULT '1' ,

     PRIMARY KEY (`id`)

) ENGINE = InnoDB

   AUTO_INCREMENT = 1426999086541635586

   DEFAULT CHARSET = utf8mb4

   COLLATE = utf8mb4_general_ci;

Sharding-JDBC 引入

Sharding-JDBC maven 包：

?

1

2

3

4

5

6

<!-- Sharding-jdbc -->

< dependency >

         < groupId >org.apache.shardingsphere</ groupId >

         < artifactId >sharding-jdbc-spring-boot-starter</ artifactId >

         < version >4.1.1</ version >

</ dependency >

本 Demo 其他用到的依赖，分别是 Junit 测试、Lombok、MyBatis Plus、Druid 连接池、MySQL 驱动、 Java Faker 数据生成器：

?

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

< dependencies >

         < dependency >

                 < groupId >org.springframework.boot</ groupId >

                 < artifactId >spring-boot-starter-test</ artifactId >

                 < scope >test</ scope >

         </ dependency >

         < dependency >

                 < groupId >junit</ groupId >

                 < artifactId >junit</ artifactId >

                 < version >4.13.2</ version >

                 < scope >test</ scope >

         </ dependency >

         < dependency >

                 < groupId >org.projectlombok</ groupId >

                 < artifactId >lombok</ artifactId >

                 < optional >true</ optional >

         </ dependency >

         <!-- MyBatis Plus -->

         < dependency >

                 < groupId >com.baomidou</ groupId >

                 < artifactId >mybatis-plus-boot-starter</ artifactId >

                 < version >3.4.3.1</ version >

         </ dependency >

         <!-- Druid -->

         < dependency >

                 < groupId >com.alibaba</ groupId >

                 < artifactId >druid</ artifactId >

                 < version >1.2.6</ version >

         </ dependency >

         <!-- MySQL -->

         < dependency >

                 < groupId >mysql</ groupId >

                 < artifactId >mysql-connector-java</ artifactId >

         </ dependency >

         <!-- Sharding-jdbc -->

         < dependency >

                 < groupId >org.apache.shardingsphere</ groupId >

                 < artifactId >sharding-jdbc-spring-boot-starter</ artifactId >

                 < version >4.1.1</ version >

         </ dependency >

         <!-- Data Faker -->

         < dependency >

                 < groupId >com.github.javafaker</ groupId >

                 < artifactId >javafaker</ artifactId >

                 < version >1.0.2</ version >

         </ dependency >

</ dependencies >

Sharding-JDBC 配置

可选配置

启用 SQL 打印：

?

1

spring.shardingsphere.props.sql.show = true

数据源配置

总共有 t_order_1、t_order_2 和 t_user 三个库，加上单主单从的复制，因此有 6 个数据库，需要配置六个数据源：

?

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

# Datasource Define

spring.shardingsphere.datasource.names = o1-master,o2-master,o1-slave,o2-slave,u-master,u-slave

# datasource o1-master

spring.shardingsphere.datasource.o1-master.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.o1-master.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.o1-master.url = jdbc:mysql://localhost:3307/db_order_1?useUnicode=true

spring.shardingsphere.datasource.o1-master.username = root

spring.shardingsphere.datasource.o1-master.password = 123456

# datasource o1-slave

spring.shardingsphere.datasource.o1-slave.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.o1-slave.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.o1-slave.url = jdbc:mysql://localhost:3308/db_order_1?useUnicode=true

spring.shardingsphere.datasource.o1-slave.username = root

spring.shardingsphere.datasource.o1-slave.password = 123456

# datasource o2-master

spring.shardingsphere.datasource.o2-master.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.o2-master.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.o2-master.url = jdbc:mysql://localhost:3307/db_order_2?useUnicode=true

spring.shardingsphere.datasource.o2-master.username = root

spring.shardingsphere.datasource.o2-master.password = 123456

# datasource o2-slave

spring.shardingsphere.datasource.o2-slave.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.o2-slave.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.o2-slave.url = jdbc:mysql://localhost:3308/db_order_2?useUnicode=true

spring.shardingsphere.datasource.o2-slave.username = root

spring.shardingsphere.datasource.o2-slave.password = 123456

# datasource u-master

spring.shardingsphere.datasource.u-master.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.u-master.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.u-master.url = jdbc:mysql://localhost:3307/db_user?useUnicode=true

spring.shardingsphere.datasource.u-master.username = root

spring.shardingsphere.datasource.u-master.password = 123456

# datasource u-slave

spring.shardingsphere.datasource.u-slave.type = com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.u-slave.driver-class-name = com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.u-slave.url = jdbc:mysql://localhost:3308/db_user?useUnicode=true

spring.shardingsphere.datasource.u-slave.username = root

spring.shardingsphere.datasource.u-slave.password = 123456

主从复制配置

主从配置不需要声明，在定义时会自动读取 key 中的主从配置库作为逻辑库，如下面的 db-order-1。

?

1

2

3

4

5

6

7

# Replication Define

spring.shardingsphere.sharding.master-slave-rules.db-order-1.master-data-source-name=o1-master

spring.shardingsphere.sharding.master-slave-rules.db-order-1.slave-data-source-names=o1-slave

spring.shardingsphere.sharding.master-slave-rules.db-order-2.master-data-source-name=o2-master

spring.shardingsphere.sharding.master-slave-rules.db-order-2.slave-data-source-names=o2-slave

spring.shardingsphere.sharding.master-slave-rules.db-user.master-data-source-name=u-master

spring.shardingsphere.sharding.master-slave-rules.db-user.slave-data-source-names=u-slave

数据节点配置

数据节点，指的是每张数据表，由于存在分库、分表、全局的不同类型，因此数据节点也有不同类型。注意，由于我们进行了主从复制，因此这里的数据库不能直接填数据源的名称，应该填在主从复制配置的 Key 中定义的名称，如 db-user 而不是 u-master 或 u-slave。

全局表：

?

1

2

# BroadCast Table

spring.shardingsphere.sharding.broadcast-tables = t_dict

单库单表：
key-generator.column 设置主键列。
key-generator.type 设置主键生成类型，这里使用雪花算法，其实没必要因为不是分表的，但是不填也会默认使用这个。

?

1

2

3

4

# Data Node t_user

spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db-user.t_user

spring.shardingsphere.sharding.tables.t_user.key-generator.column = id

spring.shardingsphere.sharding.tables.t_user.key-generator.type = SNOWFLAKE

分库分表：
在 actual-data-nodes 中使用 groovy 表达式进行设置。
在 database-strategy 中设置切分方式，具体自查，暂没时间写。

?

1

2

3

4

5

6

7

8

9

10

# Data Node t_order, If there is not master-salve-replication, use datasource name like "o$-master->{1..2}.t_order_$->{1..2}"

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db-order-$->{1..2}.t_order_$->{1..2}

spring.shardingsphere.sharding.tables.t_order.key-generator.column = id

spring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE

# database sharding strategy

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = db-order-$->{user_id % 2 + 1}

# table sharding strategy

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = id

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{id % 2 + 1}

Demo 程序

参见： zoharyips/sharding-jdbc-demo (github测试数据)

到此这篇关于Java使用Sharding-JDBC分库分表进行操作的文章就介绍到这了,更多相关Java Sharding-JDBC分库分表内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！ 

原文链接：https://juejin.cn/post/6996983642736033822

查看更多关于Java使用Sharding-JDBC分库分表进行操作的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did213046
  阅读：24次

上一篇： 关于@RequestParam的使用所遇到的404问题

下一篇：LCN分布式事务解决方案详解