MySQL 分库分表详解与实践示例

2. 垂直分库（Database Sharding by Business）

定义

将不同业务模块的数据库拆分到独立实例，各库无直接关联，按业务边界隔离。

适用场景

• 单库承载多业务，出现 CPU、内存或 IO 瓶颈；
• 业务模块访问频率差异大（如订单库并发远高于商品库）；
• 业务耦合度低，可独立扩展（如用户、订单、商品模块）。

优缺点

实践示例

-- 原单库架构：all_in_one_db（包含用户、订单、商品表）
all_in_one_db
├─ user（用户表）
├─ order（订单表）
└─ product（商品表）

-- 垂直分库后：按业务拆分 3 个独立库
user_db（用户库）
└─ user（用户相关表）
order_db（订单库）
└─ order（订单相关表）
product_db（商品库）
└─ product（商品相关表）

分库后，查询用户订单需跨库操作（伪代码示意）：

1. 从 user_db 查用户信息：SELECT * FROM user_db.user WHERE id = 1001;
2. 从 order_db 查订单信息：SELECT * FROM order_db.order WHERE user_id = 1001;

3. 水平分表（Row Sharding）

定义

将单表中数据按规则拆分到多个结构相同的表，各表字段一致，数据不同（按行拆分）。

拆分规则

适用场景

• 单表数据量过大（超 1000 万行），查询 / 索引维护效率低；
• 单表写入压力高（每秒数千条 insert），磁盘 IO 瓶颈明显。

优缺点

实践示例

示例 1：按时间范围分表（订单表）

-- 2023 年 1 月订单表
CREATE TABLE `order_202301` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `order_no` varchar(32) UNIQUE NOT NULL,
  `user_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  INDEX idx_user_id (`user_id`)
);

-- 2023 年 2 月订单表（结构与 order_202301 一致）
CREATE TABLE `order_202302` (...);

-- 插入数据（按 create_time 路由到对应表）
INSERT INTO `order_202301` (order_no, user_id, amount, create_time) VALUES ('ORD20230105001', 1001, 99.99, '2023-01-05 10:30:00');

示例 2：按哈希拆分（用户表）

-- 按 user_id%4 分 4 张表（user_0~user_3）
CREATE TABLE `user_0` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `user_id` bigint UNIQUE NOT NULL, -- 哈希分表键
  `username` varchar(50) NOT NULL,
  `create_time` datetime NOT NULL
);
CREATE TABLE `user_1` (...); -- 结构与 user_0 一致
CREATE TABLE `user_2` (...);
CREATE TABLE `user_3` (...);

-- 插入数据（按 user_id 哈希路由）
-- user_id=1001：1001%4=1 → 插入 user_1
INSERT INTO `user_1` (user_id, username, create_time) VALUES (1001, 'zhangsan', '2023-01-01 08:00:00');

4. 水平分库（Database Sharding by Data）

定义

将水平分表后的表分布到多个数据库实例，同时分散库和表的压力（分库 + 分表结合）。

适用场景

• 水平分表后单库压力仍过大（CPU / 内存 / 连接数瓶颈）；
• 需支持更高并发（多库可同时处理更多请求）。

优缺点

实践示例

-- 分 4 个库，每个库含 4 张订单表（共 4 库×4 表=16 分片）
order_db_0（库 0）
├─ order_0（user_id%16=0）
├─ order_1（user_id%16=1）
├─ order_2（user_id%16=2）
└─ order_3（user_id%16=3）
order_db_1（库 1）
├─ order_4（user_id%16=4）
├─ order_5（user_id%16=5）
├─ order_6（user_id%16=6）
└─ order_7（user_id%16=7）
order_db_2（库 2）→ 存储 8-11 分片
order_db_3（库 3）→ 存储 12-15 分片

-- 路由规则：
-- 库索引 = user_id % 4
-- 表索引 = user_id % 16
-- 例：user_id=1001 → 1001%4=1（库 1），1001%16=9（表 9）
INSERT INTO `order_db_1.order_9` (order_no, user_id, amount) VALUES ('ORD20230501001', 1001, 199.99);

二、按实现方式分类

1. 客户端方案（嵌入式分片）

原理

在应用中集成分片逻辑（如 JDBC 驱动扩展），直接路由请求到目标库表。

代表工具

Sharding-JDBC（Apache ShardingSphere）

实现流程

1. 应用配置分片规则（分库键、分表键、路由算法）；
2. Sharding-JDBC 拦截 SQL，解析后路由到目标库表；
3. 聚合结果返回给应用。

优缺点

示例配置（Sharding-JDBC）

# 分片规则配置（简化版）
spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          t_order: # 逻辑表名
            actual-data-nodes: order_db_${0..3}.order_${0..15} # 实际分片
            database-strategy: # 分库策略
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order_db_inline
            table-strategy: # 分表策略
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order_table_inline
            sharding-algorithms:
              order_db_inline: # 分库算法（user_id%4）
                type: INLINE
                props:
                  algorithm-expression: order_db_${user_id % 4}
              order_table_inline: # 分表算法（user_id%16）
                type: INLINE
                props:
                  algorithm-expression: order_${user_id % 16}

2. 中间件方案（代理层分片）

原理

在应用与数据库间部署代理服务，统一处理路由、聚合、事务等逻辑。

代表工具

MyCat、ShardingSphere-Proxy、ProxySQL

实现流程

1. 应用连接代理（如 MyCat），按单库单表方式写 SQL；
2. 中间件解析 SQL，按规则路由到目标库表；
3. 聚合结果返回给应用。

优缺点

示例（MyCat 逻辑表配置）

<!-- MyCat schema.xml 配置 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
  <!-- 逻辑表 t_order，对应物理分片 -->
  <table name="t_order" dataNode="dn$0-3" rule="order_sharding" />
</schema>
<!-- 数据节点（分库） -->
<dataNode name="dn0" dataHost="host1" database="order_db_0" />
<dataNode name="dn1" dataHost="host1" database="order_db_1" />
<dataNode name="dn2" dataHost="host2" database="order_db_2" />
<dataNode name="dn3" dataHost="host2" database="order_db_3" />
<!-- 分片规则（user_id 哈希） -->
<rule name="order_sharding">
  <ruleAlgorithm name="mod-long">
    <properties>
      <property name="column">user_id</property>
      <property name="count">4</property> <!-- 分 4 库 -->
    </properties>
  </ruleAlgorithm>
</rule>

3. 数据库原生方案（分区表）

原理

MySQL 原生支持分区表，物理上拆分到多个文件，逻辑上为单表，数据库自动管理路由。

分区类型

• 范围分区（RANGE）：按范围拆分（如时间、数值）；
• 列表分区（LIST）：按枚举值拆分（如地区）；
• 哈希分区（HASH）：按哈希值拆分；
• 键分区（KEY）：基于 MySQL 内部哈希函数。

优缺点

实践示例（RANGE 分区）

-- 订单表按季度分区
CREATE TABLE `order_partition` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `order_no` varchar(32) UNIQUE NOT NULL,
  `user_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  INDEX idx_user_id (`user_id`)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p2023Q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
  PARTITION p2023Q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
  PARTITION p2023Q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
  PARTITION p2023Q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))
);

-- 插入数据（自动路由到对应分区）
INSERT INTO `order_partition` (order_no, user_id, amount, create_time) VALUES ('ORD20230501001', 1001, 199.99, '2023-05-01 14:30:00');
-- 进入 p2023Q2

-- 查询指定分区（优化性能）
SELECT * FROM `order_partition` PARTITION (p2023Q2) WHERE user_id = 1001;

三、关键挑战与解决方案

四、总结

分库分表需结合业务场景选择方案：

• 字段多、访问频率差异大 → 垂直分表；
• 业务模块独立、单库压力大 → 垂直分库；
• 单表数据量过大（千万级 +） → 水平分表；
• 分表后单库仍压力大 → 水平分库；
• 轻量场景、不愿引入中间件 → MySQL 分区表；
• 高并发、多语言 → 中间件方案（MyCat/ShardingSphere-Proxy）；
• 性能敏感、Java 应用 → 客户端方案（Sharding-JDBC）。

实际落地中，通常采用'垂直分库 + 水平分表'组合，并提前规划分片规则（预留扩容空间），结合中间件降低维护复杂度。