MySQL之读写分离与分库分表

ShardingSphere-Proxy 对应用层伪装成一个 MySQL 服务（默认端口3307）。应用把它当成一个普通 MySQL 连接，SQL 全部发给它，由它负责：

• 读写分离路由：写走主库、读走从库；
• 分片路由：按分片算法把 SQL 路由到真实的库/表；
• 结果归并、分布式主键生成、分布式事务协调。

它的角色和之前讲的"网关服务器"一致：一个无状态的 SQL 路由/负载均衡层。

安装参考：Shardingsphere安装-CSDN博客

二、读写分离

2.1 读写分离的思想

• 主库（Master）：处理所有写操作（INSERT/UPDATE/DELETE/DDL）；
• 从库（Slave）：处理读操作（SELECT），多个从库分担读压力；
• 中间通过 binlog relaylog 主从复制保证数据同步。

ShardingSphere 负责把 SQL 按类型分流，应用无需关心主从地址。

2.2 配置文件config-readwrite-splitting.yaml（逐行注释）

# ───────────────────────────────────────────────────── # 逻辑数据库名：应用连接 Proxy 后 USE 的库名，由 Proxy 虚拟出来 # ───────────────────────────────────────────────────── databaseName: readwrite_splitting_db # ───────────────────────────────────────────────────── # dataSources：声明所有「真实 MySQL 实例」的连接池 # ───────────────────────────────────────────────────── dataSources: write_ds: # 自定义数据源名（主库/写库） url: jdbc:mysql://127.0.0.1:3306/test_db? # JDBC 地址：协议://IP:端口/库名 characterEncoding=utf8& # 连接字符编码 serverTimezone=Asia/Shanghai& # 时区 allowPublicKeyRetrieval=true& # 8.0 允许获取公钥 useSSL=false # 内网关闭 SSL username: root # 连接用户名 password: 'xxxxxxxxxxxxxx' # 密码（含 @、. 等特殊字符必须加引号） connectionTimeoutMilliseconds: 30000 # 建立连接超时（毫秒） idleTimeoutMilliseconds: 60000 # 空闲连接回收时间（毫秒） maxLifetimeMilliseconds: 1800000 # 连接最大存活时间（30 分钟） maxPoolSize: 50 # 连接池最大连接数 minPoolSize: 1 # 连接池最小空闲连接数 read_ds_0: # 从库 1（容器映射端口 53307） url: jdbc:mysql://127.0.0.1:53307/test_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 read_ds_1: # 从库 2（容器映射端口 53308，不能与从库1重复） url: jdbc:mysql://127.0.0.1:53308/test_db?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 # ───────────────────────────────────────────────────── # rules：规则区（读写分离、分片、加密等可叠加） # ───────────────────────────────────────────────────── rules: - !READWRITE_SPLITTING # 声明：读写分离规则 dataSources: readwrite_ds: # 逻辑读写分离数据源名（对外引用） staticStrategy: # 静态策略：主从角色固定 writeDataSourceName: write_ds # 写请求 → write_ds readDataSourceNames: # 读请求 → 从下面列表中选 - read_ds_0 - read_ds_1 loadBalancerName: random # 引用负载均衡算法 loadBalancers: # 负载均衡算法定义区 random: # 算法名（与上面对应） type: RANDOM # 类型：RANDOM / ROUND_ROBIN / WEIGHT

⚠️ YAML 格式红线：全部用空格缩进，禁止 Tab；每层 2 个空格；冒号后面必须有一个空格。内容一样却报错时，十有八九是格式问题，建议手敲一遍。

JDBC URL 各段含义

jdbc:mysql://127.0.0.1:3306/test_db?characterEncoding=utf8&serverTimezone=... └──┬──┘ └──┬──┘ └────┬────┘ └─┬─┘ └─┬──┘ └──────────┬───────────────┘ 协议 子协议 主机IP 端口 库名 连接参数

• jdbc:mysql：使用 JDBC 协议连接 MySQL；
• 127.0.0.1：本机回环地址（不是公网 IP），跨主机连接时填真实 IP；
• ?后面是连接参数（字符集、时区、SSL 等）。

2.3 负载均衡算法

WEIGHT 配置示例：

loadBalancers: weight: type: WEIGHT props: read_ds_0: 2.0 read_ds_1: 1.0

表示总体上按 2:1 分配（不是"先给 0 发两条、再给 1 发一条"）。

2.4 连接与测试

# 连接 ShardingSphere-Proxy（注意是 3307，不是 3306；-h 小写） mysql -h 127.0.0.1 -P 3307 -uroot -p

另开一个终端监控日志：

tail -f logs/stdout.log

验证：

• 执行INSERT，日志显示路由到write_ds（主库）；
• 执行SELECT，日志显示路由到某个read_ds（从库）。

2.5主从延迟问题

异步复制下，刚写入主库的数据可能还没同步到从库，此时读从库会读到旧数据。处理策略：

• 写后立即读的强一致场景（支付后跳订单页）强制走主库；
• 开启半同步复制缩短延迟；
• 监控从库延迟，延迟过大时摘除该从库。

三、分库分表核心概念

ShardingSphere 把分库分表统称为分片（Sharding）。

3.1 垂直分库

一个库里表太多、业务太杂，按业务关联性拆库，部署到不同服务器：

• 例：用户库db_user、订单库db_order、商品库db_goods；
• 效果：既缓解压力，又真正扩容（不同服务器）。

3.2 垂直分表

一个表字段太多、存在大字段（如商品描述 TEXT），把不常用大字段拆成 1:1 扩展表：

• t_goods(id, name, price)与t_goods_desc(goods_id, description)；
• 通过主键 goods_id 对应；
• 效果：主表行变小、B+ 树更矮、单页能放更多行；但仍在同一服务器，不扩容。

3.3 水平分表

表行数太多，按分片键 + 算法把数据行拆到多张结构相同的表：

• 例：user_id % 2 = 0进goods_0，= 1进goods_1；
• 仍在同一服务器，不扩容，解决单表行数过多。

3.4 水平分库

把数据行拆到不同服务器上的同名库：

• 例：goods_0在机器 A，goods_1在机器 B；
• 效果：性能与容量双提升。

3.5 四种方式对比

分库基本针对服务器层面；分表常在单服务器内操作。

四、垂直分库配置

config-sharding.yaml：

databaseName: sharding_db dataSources: server_user: url: jdbc:mysql://127.0.0.1:3306/test_db_id?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '@Czm388512369.' connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 server_order: url: jdbc:mysql://192.168.80.138:53311/test_db_desc?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 rules: - !SHARDING tables: user: # 逻辑表名称(自定义) actualDataNodes: server_user.t_user # 数据源名.真实表名 order: actualDataNodes: server_order.t_order

• actualDataNodes：数据源名.真实表名，指定逻辑表数据落在哪个真实节点；
• 连接逻辑库USE sharding_db后，对user的 SQL 路由到用户库，对order的 SQL 路由到订单库。

只在读写分离文件里配多个写库也能"间接"实现垂直分库，参考附录！！！，但 SHARDING 规则提供了自动路由、分片算法、绑定表、主键生成等完整能力，生产应在分片规则中做（或两条规则叠加，见第九章）。

五、水平分片配置（包括bind、broadcast、keygenerator）

假设：先按user_id分库（2 个库），再按goods_id分表（每个库 2 张表），共 4 个真实节点。

5.1 建真实表

-- 实例0（test_db_id0） CREATE DATABASE IF NOT EXISTS test_db_id0; USE test_db_id0; CREATE TABLE IF NOT EXISTS goods_id_gd0 (id INT PRIMARY KEY, user_id INT NOT NULL, goods_id VARCHAR(20) NOT NULL); CREATE TABLE IF NOT EXISTS goods_id_gd1 (id INT PRIMARY KEY, user_id INT NOT NULL, goods_id VARCHAR(20) NOT NULL); -- 实例1（test_db_id1） CREATE DATABASE IF NOT EXISTS test_db_id1; USE test_db_id1; CREATE TABLE IF NOT EXISTS goods_id_gd0 (id INT PRIMARY KEY, user_id INT NOT NULL, goods_id VARCHAR(20) NOT NULL); CREATE TABLE IF NOT EXISTS goods_id_gd1 (id INT PRIMARY KEY, user_id INT NOT NULL, goods_id VARCHAR(20) NOT NULL);

5.2 完整配置与逐行注释

databaseName: sharding_db # 逻辑库名 dataSources: server_user0: # 物理数据源 0 url: jdbc:mysql://127.0.0.1:3306/test_db_id0?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '@Czm388512369.' connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 server_user1: # 物理数据源 1 url: jdbc:mysql://192.168.80.138:53311/test_db_id1?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 rules: - !SHARDING tables: t_goods: # 逻辑表名（应用 SQL 里写的表名） # 行表达式，展开后是 4 个真实节点： # server_user0.goods_id_gd0 / server_user0.goods_id_gd1 # server_user1.goods_id_gd0 / server_user1.goods_id_gd1 actualDataNodes: server_user${0..1}.goods_id_gd${0..1} databaseStrategy: # 分库策略（选库） standard: # 标准分片：单分片键，支持 =、IN、范围 shardingColumn: user_id # 分片键 shardingAlgorithmName: alg_mod # 引用算法 tableStrategy: # 分表策略（选表） standard: shardingColumn: goods_id shardingAlgorithmName: alg_hash_mod keyGenerateStrategy: # 分布式主键 column: id keyGeneratorName: snowflake bindingTables: # 绑定表 - t_goods,t_goods_item broadcastTables: # 广播表 - t_dict shardingAlgorithms: # 分片算法定义 alg_mod: type: MOD # 取模（分片键须为数值） props: sharding-count: 2 # 分片值 % 2 alg_hash_mod: type: HASH_MOD # 先哈希再取模（字符串也能用） props: sharding-count: 2 alg_inline: type: INLINE # 行表达式 props: algorithm-expression: server_user${user_id % 2} keyGenerators: snowflake: type: SNOWFLAKE props: worker-id: 1

5.3 行表达式

• ${0..1}：连续枚举；${[a,b,c]}：离散枚举；
• server_user${0..1}.goods_id_gd${0..1}是两侧枚举的笛卡尔组合，得到 4 个节点。

5.4 分片策略

5.5 分片算法

六、绑定表（bind）完整场景

6.1 构造场景

t_order和t_order_item都按order_id分 2 个库（偶数→ds0，奇数→ds1）。

这样子做也能够确保有关数据在同一个库中，避免跨库事务

ds0：

-- t_order order_id=100 user_id=1 amount=100 order_id=102 user_id=2 amount=200 -- t_order_item item_id=1 order_id=100 goods='手机' item_id=2 order_id=100 goods='耳机' item_id=3 order_id=102 goods='电脑'

ds1：

-- t_order order_id=101 user_id=3 amount=50 order_id=103 user_id=4 amount=80 -- t_order_item item_id=4 order_id=101 goods='书' item_id=5 order_id=103 goods='键盘'

全量关联查询：

SELECT o.order_id, i.goods FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id;

6.2 没有配置 bindingTables（笛卡尔积路由）

Proxy 不知道两表分片规则相同，把路由集合做笛卡尔积：t_order 集合{ds0,ds1}× t_order_item 集合{ds0,ds1}=4 条 SQL：

带WHERE o.order_id=100时：t_order 能确定到 ds0，但 t_order_item 未被声明绑定，仍向 ds0、ds1 都发，产生2 条SQL（ds1 白跑）。

6.3 配置绑定表后

bindingTables: - t_order,t_order_item

Proxy 知道同一 order_id 的订单和明细必在同一节点：

• 全量 JOIN：只下发2 条（ds0、ds1 各本地 JOIN）；
• 带order_id=100：精确路由到 ds0，只下发1 条。

6.4 对比

七、广播表完整场景

本质上就是在每一个库中都备份一个相同的表，以避免跨库事务

7.1 构造场景

t_dict是订单状态字典表（5 行，几乎不变），t_order是分片表：

status_code=1 status_name='待支付' status_code=2 status_name='已支付' status_code=3 status_name='已发货' status_code=4 status_name='已完成'

查询：

SELECT o.order_id, d.status_name FROM t_order o JOIN t_dict d ON o.status = d.status_code;

7.2 没有广播表（字典只在 ds0）

• ds0 能本地 JOIN；
• ds1 没有 t_dict，无法本地 JOIN，Proxy 只能把 ds0 的字典和 ds1 的订单拉到内存归并；
• 且在 ds0 新增状态（如"已退款"）后，ds1 不知道，字典副本不一致。

7.3 配置广播表后

broadcastTables: - t_dict

• 查询：t_dict 在 ds0、ds1 各有一份完整副本，每个库都能本地 JOIN；
• 写入自动广播：

  INSERT INTO t_dict VALUES (5, '已退款');

  Proxy 自动向所有节点下发：

  → ds0: INSERT INTO t_dict VALUES (5,'已退款'); → ds1: INSERT INTO t_dict VALUES (5,'已退款');

• 单表查询：SELECT * FROM t_dict随便路由到一个节点即可。

7.4 选用条件

典型：字典表、配置表、地区表。

八、统一序列号（分布式主键）

8.1 为什么分库分表后需要

分库/分表后，每个库各自自增，会产生重复主键（库 0 和库 1 都从 1 开始），因此必须有全局唯一 ID 方案。

8.2 雪花算法（Snowflake，ShardingSphere 默认）

64 位 long 型 ID，分四段：

1 bit 41 bit 时间戳 10 bit 工作机器 12 bit 序列号 ┌──┬────────────────────┬────────────────┬──────────────────┐ │ 0│ 毫秒级时间戳 │ 5位数据中心+5位机器│ 同毫秒内自增序列 │ └──┴────────────────────┴────────────────┴──────────────────┘

• 1 位符号：固定 0，保证正数；
• 41 位时间戳：毫秒级，可用约69 年；
• 10 位工作机器：5 位数据中心 + 5 位机器，共 1024 个节点；
• 12 位序列号：同一毫秒可生成4096个 ID。

优点：本地生成无网络开销、性能高、整体趋势递增（对 B+ 树索引友好）。

8.3 时钟回拨

机器时钟因 NTP 校时往回拨时可能生成重复 ID。（这也要求了我们不能够轻易更换时区）

处理：记录上次时间戳，回拨很小则等待追上，回拨较大则报错拒绝。

8.4 其他方案

8.5 配置

keyGenerateStrategy: column: id keyGeneratorName: snowflake keyGenerators: snowflake: type: SNOWFLAKE props: worker-id: 1

插入时不指定 id，Proxy 自动填充雪花 ID。

九、读写分离 + 多集群垂直分库（其余组合可以参考）

9.1 架构形态

多个业务集群，每个集群内部是一主多从的读写分离，集群之间是垂直分库：

ShardingSphere-Proxy :3307（逻辑库 mall_db） │ ┌───────────────────────┴───────────────────────┐ ▼ ▼ 用户库集群 user_ds 订单库集群 order_ds ├─ user_master 写 3306 ├─ order_master 写 53311 ├─ user_slave0 读 53307 └─ order_slave0 读 53312 └─ user_slave1 读 53308 │ │ t_user / t_user_log t_order / t_order_item t_goods / t_goods_desc（垂直分表）

核心思路：先用 READWRITE_SPLITTING 把每个集群的物理主从封装成一个逻辑数据源（user_ds、order_ds），再用 SHARDING 的actualDataNodes引用这些逻辑数据源——两条规则叠加，路由两层级联。

9.2 完整配置

databaseName: mall_db dataSources: # ───── 用户库集群（一主两从） ───── user_master: url: jdbc:mysql://127.0.0.1:3306/db_user?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '123456' user_slave0: url: jdbc:mysql://127.0.0.1:53307/db_user?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '123456' user_slave1: url: jdbc:mysql://127.0.0.1:53308/db_user?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '123456' # ───── 订单库集群（一主一从） ───── order_master: url: jdbc:mysql://127.0.0.1:53311/db_order?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '123456' order_slave0: url: jdbc:mysql://127.0.0.1:53312/db_order?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false username: root password: '123456' rules: # ════════ 规则 1：读写分离（把每个集群封装成逻辑数据源） ════════ - !READWRITE_SPLITTING dataSources: user_ds: # 用户库集群逻辑数据源 staticStrategy: writeDataSourceName: user_master readDataSourceNames: - user_slave0 - user_slave1 loadBalancerName: round_robin order_ds: # 订单库集群逻辑数据源 staticStrategy: writeDataSourceName: order_master readDataSourceNames: - order_slave0 loadBalancerName: round_robin loadBalancers: round_robin: type: ROUND_ROBIN # ════════ 规则 2：垂直分库（逻辑表 → 逻辑数据源） ════════ - !SHARDING tables: # 用户域的表全部落在用户库集群 t_user: actualDataNodes: user_ds.t_user t_user_log: actualDataNodes: user_ds.t_user_log # 订单域的表全部落在订单库集群 t_order: actualDataNodes: order_ds.t_order t_order_item: actualDataNodes: order_ds.t_order_item # 垂直分表：商品主表与大字段扩展表（1:1），都在订单库集群 t_goods: actualDataNodes: order_ds.t_goods t_goods_desc: actualDataNodes: order_ds.t_goods_desc bindingTables: - t_order,t_order_item broadcastTables: - t_dict

9.3 路由验证

两级路由：先由 SHARDING 决定"去哪个业务集群"，再由 READWRITE_SPLITTING 决定"走该集群的主还是从"。

9.4 关于垂直分表

垂直分表是同一个库内把大字段拆成 1:1 扩展表（如t_goods拆出t_goods_desc），物理上就是两张独立表，不需要分片算法，在同一数据源下分别声明即可（见 9.2）；应用层按需查询或用goods_id关联。若以后还要水平拆分，再为它们配置相同分片键并加入bindingTables。

十、CAP 理论

10.1 三要素

10.2 为什么 P 必选

网络分区不可避免，无法选择"不要 P"。实际权衡是分区发生时要 C 还是 A：

• CP：分区时拒绝部分请求保一致，牺牲可用性；
• AP：分区时各节点继续服务，允许短暂不一致，恢复后再同步。

10.3 典型系统

对应学过的组件：

• MySQL异步主从复制偏 AP（可能丢已提交数据）；
• 半同步复制向 CP 靠拢；
• MGR可配置 CP 能力；
• Redis Cluster 异步复制偏 AP；
• ZooKeeper（ZAB）是 CP（选举期间不可用）。

CAP 不是简单"三选二"：无分区时 C 和 A 可同时满足，只有分区期间才需取舍。

10.4 BASE 理论（AP 的实践）

• Basically Available（基本可用：允许变慢、降级）；
• Soft State（软状态：允许中间状态）；
• Eventually Consistent（最终一致性：一段时间后所有节点一致）。

十一、分库分表带来的其他问题

十二.附录（只利用读写分离实现垂直分库）

databaseName: readwrite_splitting_db dataSources: write_ds_0: url: jdbc:mysql://127.0.0.1:3306/test_db_id?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: root password: '@Czm388512369.' connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 write_ds_1: url: jdbc:mysql://127.0.0.1:53306/test_db_desc?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 read_ds_0: url: jdbc:mysql://127.0.0.1:53307/test_db_id?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 read_ds_1: url: jdbc:mysql://127.0.0.1:53308/test_db_desc?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false username: backup password: 123456 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 rules: - !READWRITE_SPLITTING dataSources: readwrite_ds_0: staticStrategy: writeDataSourceName: write_ds_0 readDataSourceNames: - read_ds_0 loadBalancerName: round_robin readwrite_ds_1: staticStrategy: writeDataSourceName: write_ds_1 readDataSourceNames: - read_ds_1 loadBalancerName: round_robin loadBalancers: round_robin: type: ROUND_ROBIN

这个方法大多数场景下没有分片模式好，但是也可以了解一下（博主自己突发奇想的）