MySQL 高性能架构详解：基于 Apache ShardingSphere 的读写分离与分片

MySQL 高性能架构详解：基于 Apache ShardingSphere 的读写分离与分片 | 极客日志

# -e JVM_OPTS="-Xms256m -Xmx256m -Xmn128m" # 初始堆内存 256m, 最大堆内存 256m, 新生代内存 128m
docker run -d\
-p3307:3307 \
-v /org/shardingsphere/proxy/conf:/opt/shardingsphere-proxy/conf \
-v /org/shardingsphere/proxy/ext-lib:/opt/shardingsphere-proxy/ext-lib \
-v /org/shardingsphere/proxy/logs:/opt/shardingsphere-proxy/logs \
-e JVM_OPTS="-Xms256m -Xmx256m -Xmn128m" \
--name ss-proxy apache/shardingsphere-proxy:5.3.2

mode:
  type: Standalone # 单机模式
authority:
  users:
    - user: root@% # 配置一个用户，用户名为 root@%
      password: 123456 # 为用户指定密码
  privilege:
    type: ALL_PERMITTED # 授予用户所有权限
props:
  sql-show: true # 显示执行的 SQL 语句
  proxy-mysql-default-version: 8.0.44 # MySQL 版本号

<?xml version="1.0"?>
<configuration>
  <!-- 日志输入到文件 -->
  <appender name="SHARDING_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <!-- 日志路径 -->
    <file>./logs/shardingsphere.log</file>
    <encoder>
      <!-- 日志输入的样式 -->
      <pattern>[%-5level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
    </encoder>
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
      <fileNamePattern>shardingsphere.%d{yyyy-MMdd}.%i.log</fileNamePattern>
      <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
        <maxFileSize>100MB</maxFileSize>
      </timeBasedFileNamingAndTriggeringPolicy>
    </rollingPolicy>
  </appender>
  <root level="INFO">
    <appender-ref ref="SHARDING_FILE"/>
  </root>
</configuration>

# 重新启动容器
docker restart ss-proxy
# 查看状态，启动成功
docker ps
# 指定主机和端口号进行连接
mysql -h 127.0.0.1 -P 3307 -u root -p123456

数据库服务器	容器名	端口号
主服务器	org-mysql-master	53306
从服务器 1	org-mysql-slave1	53307
从服务器 2	org-mysql-slave2	53308

databaseName: org_proxy_db # shardingsphere 中代理的库名
dataSources:
  write_ds: # 主节点，写服务器
    url: jdbc:mysql://YOUR_IP_ADDRESS:53306/org_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_0: # 从节点，读服务器
    url: jdbc:mysql://YOUR_IP_ADDRESS:53306/org_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_1: # 从节点，读服务器
    url: jdbc:mysql://YOUR_IP_ADDRESS:53306/org_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        staticStrategy:
          writeDataSourceName: write_ds
          readDataSourceNames:
            - read_ds_0
            - read_ds_1
        loadBalancerName: random # 负载均衡策略名，必须与下面配置的名字一致
    loadBalancers:
      # 分别为不的同负载均衡策略指定内置的类型
      random: # 随机，与上面的负载均衡策略名对应
        type: RANDOM # 内置类型，固定为 RANDOM
      round_robin: # 轮询
        type: ROUND_ROBIN # 固定为 ROUND_ROBIN
      weight: # 权重
        type: WEIGHT # 固定为 WEIGHT
        props:
          read_ds_0: 2.0
          read_ds_1: 1.0

# 启动容器
docker start ss-proxy
# 查看容器是否启动成功
docker ps
# 连接 ShardingSphere
mysql -h 127.0.0.1 -P 3307 -u root -p

# 进入容器
docker exec -it ss-proxy env LANG=C.UTF-8 /bin/bash
# 查看 ShardingSphere 的实时日志，以实际目录和文件名为准
tail -f /opt/shardingsphere-proxy/logs/shardingsphere.log

mysql> use org_proxy_db;
Database changed
mysql> INSERT INTO t_user VALUES (1,'张三'),(2,'李四'),(3,'王五');
Query OK, 3 rows affected (0.01 sec)
mysql> SELECT * FROM t_user;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM t_user;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
3 rows in set (0.01 sec)

数据库服务器	容器名	端口号
用户服务器	server-user	53310
订单服务器	server-order	53311

CREATE DATABASE IF NOT EXISTS user_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- 选择数据库
USE user_db;
-- 创建用户表
CREATE TABLE IF NOT EXISTS t_user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

# 进入 Docker 容器
root@VM-0-7-ubuntu:~# docker exec -it server-user env LANG=C.UTF-8 /bin/bash
# 运行 Mysql 客户端
bash-5.1# mysql -uroot -p

# 修改密码
mysql> SET PASSWORD = '123456';

docker run -d\
-p53310:3306 \
-v /org/mysql/user/conf:/etc/mysql/conf.d \
-v /org/mysql/user/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-user \
mysql:8.0.44

CREATE DATABASE IF NOT EXISTS order_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- 选择数据库
USE order_db;
-- 创建订单表
CREATE TABLE IF NOT EXISTS t_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(30) COMMENT '订单号',
  amount DECIMAL(12,2) COMMENT '订单金额',
  user_id BIGINT COMMENT '用户编号'
);

# 进入 Docker 容器
root@VM-0-7-ubuntu:~# docker exec -it server-order env LANG=C.UTF-8 /bin/bash
# 运行 Mysql 客户端
bash-5.1# mysql -uroot -p

# 修改密码
mysql> SET PASSWORD = '123456';

docker run -d\
-p53311:3306 \
-v /org/mysql/order/conf:/etc/mysql/conf.d \
-v /org/mysql/order/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order \
mysql:8.0.44

databaseName: sharding_db
dataSources:
  server_user:
    url: jdbc:mysql://YOUR_IP_ADDRESS:53310/user_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  server_order:
    url: jdbc:mysql://YOUR_IP_ADDRESS:53311/order_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
  - !SHARDING
    tables:
      t_user:
        actualDataNodes: server_user.t_user
      t_order:
        actualDataNodes: server_order.t_order

# 启动容器
docker start ss-proxy
# 查看容器是否启动成功
docker ps
# 连接 ShardingSphere
mysql -h 127.0.0.1 -P 3307 -u root -p

数据库服务器	容器名	端口号
订单服务器	server-order0	63310
订单服务器	server-order1	63311

# 创建 server-order0
docker run -d\
-p63310:3306 \
-v /org/mysql/order0/conf:/etc/mysql/conf.d \
-v /org/mysql/order0/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order0 \
--restart always \
mysql:8.0.44
# 创建 server-order1
docker run -d\
-p63311:3306 \
-v /org/mysql/order1/conf:/etc/mysql/conf.d \
-v /org/mysql/order1/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order1 \
--restart always \
mysql:8.0.44

# 进入 Docker 容器
docker exec -it server-order0 env LANG=C.UTF-8 /bin/bash
# docker exec -it server-order1 env LANG=C.UTF-8 /bin/bash
# 运行 Mysql 客户端
mysql -uroot -p

# 修改 root 用户密码
mysql> SET PASSWORD = '123456';

-- 在两个 Docker 容器都执行相同的 SQL 语句
CREATE DATABASE IF NOT EXISTS order_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- 选择数据库
USE order_db;
-- 创建订单表 t_order0
CREATE TABLE IF NOT EXISTS t_order0 (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(30) COMMENT '订单号',
  amount DECIMAL(12,2) COMMENT '订单金额',
  user_id BIGINT COMMENT '用户编号'
);
-- 创建订单表 t_order1
CREATE TABLE IF NOT EXISTS t_order1 (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(30) COMMENT '订单号',
  amount DECIMAL(12,2) COMMENT '订单金额',
  user_id BIGINT COMMENT '用户编号'
);

databaseName: sharding_db
dataSources:
  server_order0:
    url: jdbc:mysql://81.69.218.112:63310/order_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  server_order1:
    url: jdbc:mysql://81.69.218.112:63311/order_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: server_order${0..1}.t_order${0..1}
    databaseStrategy:
      # 分库策略
      standard:
        # 用于单分片键的标准分片场景
        shardingColumn: user_id # 分片列名称
        shardingAlgorithmName: alg_db_inline_userid # 分片算法名称
    tableStrategy:
      # 分表策略
      standard:
        shardingColumn: order_no # 分片列名称
        shardingAlgorithmName: alg_hash_mod # 分片算法名称
    # 配置分片算法
    shardingAlgorithms:
      alg_db_inline_userid:
        type: INLINE
        props:
          algorithm-expression: server_order${user_id % 2}
      alg_hash_mod:
        type: HASH_MOD
        props:
          sharding-count: 2

# 启动容器
docker start ss-proxy
# 查看容器是否启动成功
docker ps
# 连接 ShardingSphere
mysql -h 127.0.0.1 -P 3307 -u root -p

# 进入容器
docker exec -it ss-proxy env LANG=C.UTF-8 /bin/bash
# 查看 ShardingSphere 的实时日志，以实际目录和文件名为准
tail -f /opt/shardingsphere-proxy/logs/shardingsphere.log

-- 路由到 server-order1
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (1,'BIT001',1,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (2,'BIT002',1,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (3,'BIT003',1,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (4,'BIT004',1,20.00);
-- 路由到 server-order0
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (1,'BIT001',2,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (2,'BIT002',2,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (3,'BIT003',2,20.00);
INSERT INTO t_order (id, order_no, user_id, amount) VALUES (4,'BIT004',2,20.00);

MySQL 高性能架构详解：基于 Apache ShardingSphere 的读写分离与分片

1. 理论 - 高性能架构模式

1.1 读写分离

1.2 数据分片

1.2.1 垂直分片

1.2.2 水平分片

1.3 读写分离和数据分片

2. 实践方式 - 高性能架构模式

2.1 代码封装

2.2 中间件封装

3. Apache ShardingSphere

3.1 ShardingSphere-JDBC(了解)

3.2 ShardingSphere-Proxy

3.3 Docker 安装 ShardingSphere-Proxy

3.3.1 创建 Docker 容器

3.3.2 配置 server.yaml

3.3.3 上传 MySQL 驱动

3.3.4 日志配置

3.3.5 测试连接

4. 实践 - 读写分离

4.1 架构图

4.2 服务器规划

4.3 创建数据库服务器

4.4 配置读写分离

4.5 启动 ShardingSphere

4.6 测试

5. 实践 - 垂直分片

5.1 架构图

5.2 服务器规划

5.3 创建 server-user 容器

5.4 创建 server-order 容器

5.5 垂直分库配置

5.6 启动 ShardingSphere

6. 实践 - 水平分片

6.1 架构图

6.2 服务器规划

6.3 创建 server-order 容器

6.4 进入 Docker 容器

6.5 创建数据库

6.6 水平分片配置

6.7 启动 ShardingSphere

6.8 测试

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具