ShardingSphere-JDBC垂直分片

原创于 2024-09-25 11:56:05 发布 · 828 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#shardingsphere

MySQL高级篇专栏收录该内容

79 篇文章

订阅专栏

文章目录

1、订单库 db_order
- 1.2、创建数据库
2、用户库 db_user
3、创建实体类 TOrder
4、创建实体类 TUser
5、创建 TOrderMapper
6、创建 TUserMapper
7、application.yml
8、shardingsphere.yaml
9、ShardingJdbcDemoApplicationTests

垂直分片：在项目中可以将垂直分片到不同库中的多张表加载到一个项目中使用。

1、订单库 db_order

3306主 -> 3316从：订单库 db_order
订单库将来访问压力比较大，我们要考虑主从复制
3306和3316容器我们已经在 mysql搭建主从复制的时候创建好了，参考这篇博客：mysql-搭建主从复制

1.2、创建数据库

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order (
  id BIGINT AUTO_INCREMENT,
  order_no VARCHAR(30),
  user_id BIGINT,
  PRIMARY KEY(id) 
);

创建完之后，我们会发现从库3316没有同步创建数据库 db_order，因为当时我们在搭建mysql的主从复制的时候，只设置了复制mydb2，mydb3数据库到3316从库中。
在这里插入图片描述

2、用户库 db_user

3326主
因为用户的数据量没有那么大，并发操作没那么多，所以我们不需要考虑主从复制

2.1、创建 atguigu-mysql-user 容器

docker run -d \
-p 3326:3306 \
-v mysql-user-conf:/etc/mysql/conf.d \
-v mysql-user-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-user \
mysql:8

[root@localhost ~]# docker run -d \
> -p 3326:3306 \
> -v mysql-user-conf:/etc/mysql/conf.d \
> -v mysql-user-data:/var/lib/mysql \
> -e MYSQL_ROOT_PASSWORD=123456 \
> --name atguigu-mysql-user \
> mysql:8
d2e24da102d53cdeb3db591b8667facec3973a3fa1d2029e2d5306f3441ff574

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE                    COMMAND                   CREATED          STATUS          PORTS                                                                                  NAMES
d2e24da102d5   mysql:8                  "docker-entrypoint.s…"   28 seconds ago   Up 26 seconds   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp                                   atguigu-mysql-user
c236f876ae40   mysql:8                  "docker-entrypoint.s…"   11 days ago      Up 21 hours     33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp                                   atguigu-mysql-slave1
89bb2276fc3d   elasticsearch:8.8.2      "/bin/tini -- /usr/l…"   4 weeks ago      Up 22 hours     0.0.0.0:9200->9200/tcp, :::9200->9200/tcp, 0.0.0.0:9300->9300/tcp, :::9300->9300/tcp   elasticsearch
8c71efe9dca7   wurstmeister/zookeeper   "/bin/sh -c '/usr/sb…"   4 weeks ago      Up 22 hours     22/tcp, 2888/tcp, 3888/tcp, 0.0.0.0:2181->2181/tcp, :::2181->2181/tcp                  zookeeper
c14772057ab8   redis                    "docker-entrypoint.s…"   9 months ago     Up 22 hours     0.0.0.0:6379->6379/tcp, :::6379->6379/tcp                                              spzx-redis
ab66508d9441   mysql:8                  "docker-entrypoint.s…"   9 months ago     Up 22 hours     0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp                                   spzx-mysql

创建完容器，配置文件不需要改，因为 atguigu-mysql-user 容器我们不需要搭建主从复制。

2.2、登录 atguigu-mysql-user 容器

[root@localhost ~]# docker exec -it atguigu-mysql-user /bin/bash
root@d2e24da102d5:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.3、设置密码

-- 设置密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 刷新权限
FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

2.4、创建数据库

CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);

在这里插入图片描述

3、创建实体类 TOrder

package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("t_order")
public class TOrder {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String orderNo;
    private Long userId;
}

4、创建实体类 TUser

package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_user")
public class TUser {

    private Long id;

    private String uname;

}

5、创建 TOrderMapper

package com.atguigu.sharding.jdbc.demo.mapper;

import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TOrderMapper extends BaseMapper<TOrder> {
}

6、创建 TUserMapper

package com.atguigu.sharding.jdbc.demo.mapper;

import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TUserMapper extends BaseMapper<TUser> {
}

7、application.yml

spring:
  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
    # 驱动类：使用shardingjdbc提供的驱动类
     driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
#    username: root
#    password: 123456
#    url: jdbc:mysql://192.168.74.148:3306/mydb2?serverTimezone=GMT%2B8
     url: jdbc:shardingsphere:classpath:shardingsphere.yaml

8、shardingsphere.yaml

# 模式配置
mode:
  # shardingjdbc：使用的是单机模式
  type: Standalone
  repository:
    type: JDBC
# 数据源配置
dataSources:
  write_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.74.148:3306/mydb2
    username: root
    password: 123456
  read_ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.74.148:3316/mydb2
    username: root
    password: 123456
  user_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.74.148:3326/db_user
    username: root
    password: 123456
  order_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.74.148:3306/db_order
    username: root
    password: 123456

rules:
  # 读写分离配置：指定读交给哪个数据源 写交给哪个
  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        writeDataSourceName: write_ds
        readDataSourceNames:
          - read_ds_0
        transactionalReadQueryStrategy: PRIMARY # 事务内读请求的路由策略，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC
        # 配置读操作的负载均衡策略
        loadBalancerName: random
    loadBalancers:
      random:
        type: RANDOM
      round_robin:
        type: ROUND_ROBIN
      weight:
        type: WEIGHT
        props:
          read_ds_0: 1
  # 垂直分片配置
  - !SHARDING
    tables:
      t_user:
        actualDataNodes: user_ds.t_user
      t_order:
        actualDataNodes: order_ds.t_order
# 输出sql：
props:
  # 打印 sharding 的sql日志
  sql-show: true

9、ShardingJdbcDemoApplicationTests

package com.atguigu.sharding.jdbc.demo;

import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.atguigu.sharding.jdbc.demo.mapper.TOrderMapper;
import com.atguigu.sharding.jdbc.demo.mapper.TUserMapper;
import jakarta.annotation.Resource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ShardingJdbcDemoApplicationTests {

    @Resource
    TUserMapper tUserMapper;

    @Resource
    TOrderMapper torderMapper;

    @Test
    void contextLoads() {

        TUser tUser = new TUser();
        tUser.setUname("张三");
        tUserMapper.insert(tUser);

        TOrder tOrder = new TOrder();
        tOrder.setOrderNo("1234");
        tOrder.setUserId(1L);
        torderMapper.insert(tOrder);

    }

}

2024-09-25T11:53:56.395+08:00  INFO 15748 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( id,
uname )  VALUES  ( ?,
? )
2024-09-25T11:53:56.395+08:00  INFO 15748 --- [           main] ShardingSphere-SQL  Actual SQL: user_ds ::: INSERT INTO t_user  ( id,
uname )  VALUES  (?, ?) ::: [1838788985485291522, 张三]
2024-09-25T11:53:56.436+08:00  INFO 15748 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( order_no,
user_id )  VALUES  ( ?,
? )
2024-09-25T11:53:56.437+08:00  INFO 15748 --- [           main] ShardingSphere-SQL  Actual SQL: order_ds ::: INSERT INTO t_order  ( order_no,
user_id )  VALUES  (?, ?) ::: [1234, 1]