MySQL 分库分表详解

什么是分库分表？

分库（Database Sharding）

将一个数据库拆分为多个数据库，每个数据库存储部分数据。

示例：

• 原始：user_db (存储所有用户数据)
• 分库后：
  • user_db_0 (存储user_id以0,1,2结尾的用户)
  • user_db_1 (存储user_id以3,4,5结尾的用户)
  • user_db_2 (存储user_id以6,7,8,9结尾的用户)

分表（Table Sharding）

将一个大表拆分为多个小表，每个表存储部分数据。

示例：

• 原始：order_table (存储所有订单)
• 分表后：
  • order_table_202301 (2023年1月订单)
  • order_table_202302 (2023年2月订单)
  • order_table_202303 (2023年3月订单)

为什么要分库分表？

1. 性能问题

单表数据量过大

-- 问题：当订单表达到亿级数据时
SELECT * FROM orders WHERE user_id = 12345;
-- 即使有索引，查询性能也会显著下降

┌─────────────────────────────────┐
│           orders 表              │
│                                 │
│  id  │ user_id │ amount │ time   │
├─────────────────────────────────┤
│   1  │     101 │  99.00 │ 2024-01│
│   2  │     102 │ 199.00 │ 2024-01│
│   3  │     103 │  59.00 │ 2024-02│
│   4  │     104 │ 299.00 │ 2024-02│
│   5  │     105 │  79.00 │ 2024-03│
│  ... │    ...  │    ... │    ... │
│ 100万 │     ... │    ... │    ... │
└─────────────────────────────────┘

解决方案：水平分表

-- 按用户ID取模分表
SELECT * FROM orders_${user_id % 10} WHERE user_id = 12345;
-- 数据分散到10个表中，每个表数据量减少10倍

┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
│  orders_202401  │  │  orders_202402  │  │  orders_202403  │
│                 │  │                 │  │                 │
│ id │ amount     │  │ id │ amount     │  │ id │ amount     │
├─────────────────┤  ├─────────────────┤  ├─────────────────┤
│  1 │    99.00   │  │  3 │    59.00   │  │  5 │    79.00   │
│  2 │   199.00   │  │  4 │   299.00   │  │  6 │   159.00   │
│    │            │  │    │            │  │    │            │
│ ~33万行          │  │ ~33万行          │  │ ~34万行         │
└─────────────────┘  └─────────────────┘  └─────────────────┘

2. 高并发压力

单库连接数限制

-- MySQL默认最大连接数151，虽然可调整但有限制
SHOW VARIABLES LIKE 'max_connections';

原始单库结构

┌─────────────────────────────────┐
│         user_db 数据库           │
│                                 │
│  users 表      │  orders 表     │
│ ────────────── │ ────────────── │
│ id  │ name      │ id │ user_id   │
│ 101 │ 张三      │   1│ 101       │
│ 102 │ 李四      │   2│ 102       │
│ 103 │ 王五      │   3│ 103       │
│ 104 │ 赵六      │   4│ 104       │
│ ... │ ...       │ ...│ ...       │
│ 100万│ ...     │ 500万│ ...     │
└─────────────────────────────────┘

解决方案：分库

# 应用层路由到不同数据库
def get_db_connection(user_id):
    db_index = user_id % 3  # 分到3个库
    if db_index == 0:
        return connect_db('db_0')
    elif db_index == 1:
        return connect_db('db_1')
    else:
        return connect_db('db_2')

按用户ID分库后

┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
│   user_db_0     │  │   user_db_1     │  │   user_db_2     │
│                 │  │                 │  │                 │
│ users │ orders  │  │ users │ orders  │  │ users │ orders  │
│ ───── │ ──────  │  │ ───── │ ──────  │  │ ───── │ ──────  │
│ 101   │ 1,101   │  │ 102   │ 2,102   │  │ 103   │ 3,103   │
│ 104   │ 4,104   │  │ 105   │ 5,105   │  │ 106   │ 6,106   │
│ 107   │ 7,107   │  │ 108   │ 8,108   │  │ 109   │ 9,109   │
│ ~33万 │ ~167万   │  │ ~33万 │ ~167万   │  │ ~34万 │ ~166万   │
└─────────────────┘  └─────────────────┘  └─────────────────┘
    ↑                     ↑                     ↑
user_id % 3 = 0     user_id % 3 = 1     user_id % 3 = 2

3. 存储空间限制

单机存储容量瓶颈

• 单机硬盘容量有限
• 备份和恢复时间过长

分库分表的实现方式

1. 水平分表

-- 按时间分表（常用）
CREATE TABLE orders_202401 (...);
CREATE TABLE orders_202402 (...);
CREATE TABLE orders_202403 (...);

-- 按范围分表
CREATE TABLE orders_0001_1000 (...);  -- ID 1-1000
CREATE TABLE orders_1001_2000 (...);  -- ID 1001-2000

-- 按哈希分表
CREATE TABLE orders_00 (...);  -- ID哈希后模4等于0
CREATE TABLE orders_01 (...);  -- ID哈希后模4等于1

2. 垂直分表

-- 将大字段分离到单独表
CREATE TABLE user_basic (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE user_profile (
    user_id BIGINT PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    settings JSON
);

3. 分库策略

• 范围分片

用户ID范围      分配到数据库
───────────     ──────────
1 - 1,000,000   → db_0
1,000,001 - 2,000,000 → db_1  
2,000,001 - 3,000,000 → db_2

• 哈希分片

用户ID    哈希计算      分配到
──────   ──────────    ──────
101   →  101 % 3 = 2  → db_2
102   →  102 % 3 = 0  → db_0
103   →  103 % 3 = 1  → db_1
104   →  104 % 3 = 2  → db_2

• 时间分片

时间范围        表名
─────────      ───────────
2024年1月   →  orders_202401
2024年2月   →  orders_202402
2024年3月   →  orders_202403

分库分表带来的挑战

1. 分布式事务

# 使用XA事务或最终一致性方案
def transfer_money(from_user, to_user, amount):
    try:
        # 开启分布式事务
        with distributed_transaction():
            # 扣减转出用户余额（可能在db_0）
            deduct_balance(from_user, amount)
            # 增加转入用户余额（可能在db_1）
            add_balance(to_user, amount)
    except Exception as e:
        # 事务回滚
        rollback_transaction()

2. 跨库查询

-- 问题：无法直接JOIN分库后的表
SELECT u.username, o.order_amount 
FROM users u JOIN orders o ON u.id = o.user_id 
WHERE u.create_time > '2024-01-01';

-- 解决方案：
-- 1. 应用层分别查询再合并
-- 2. 使用中间件（如MyCat、ShardingSphere）
-- 3. 冗余设计或数据异构

• 跨库JOIN问题

┌─────────────┐         ┌─────────────┐
│   user_db_0 │         │   user_db_1 │
│             │         │             │
│ users表     │          │ users表     │
│ id │ name   │         │ id │ name   │
│ 101│ 张三   │          │ 102│ 李四   │
└─────────────┘         └─────────────┘
         │                      │
         └──────────────────────┘
                 │
                 ▼
        ┌─────────────────┐
        │ 无法直接执行：    │
        │ SELECT u.name,  │
        │        o.amount │
        │ FROM users u    │
        │ JOIN orders o ON│
        │ u.id = o.user_id│
        └─────────────────┘

• 解决方案：应用层JOIN

1. 先从各个库查询用户信息
   ┌→ user_db_0: SELECT * FROM users WHERE id IN (101,102)
   └→ user_db_1: SELECT * FROM users WHERE id IN (103,104)

2. 应用层合并结果
   ┌─────────────────┐
   │  应用服务器      │
   │                  │
   │ 合并所有用户数据  │
   │ 进行内存JOIN     │
   └─────────────────┘

3. 返回最终结果

3. 全局ID生成

import snowflake
 
# 雪花算法生成分布式ID
def generate_id():
    return snowflake.generate_id()

# 数据库自增ID（需要设置不同步长）
# DB1: AUTO_INCREMENT = 1, 步长=2 (1,3,5,7...)
# DB2: AUTO_INCREMENT = 2, 步长=2 (2,4,6,8...)

4. 数据迁移与扩容

class DataMigrator:
    def migrate_data(self, old_shards, new_shards):
        """数据迁移：从4个分库扩展到8个分库"""
        for old_db in old_shards:
            for record in old_db.get_all_records():
                new_db_index = self.calculate_new_shard(record.id, new_shards)
                new_shards[new_db_index].insert(record)

分库分表的最佳实践

1. 选择合适的分片键

# 好的分片键：用户ID、订单ID（查询频繁且分布均匀）
shard_key = user_id

# 不好的分片键：性别、状态（数据分布不均匀）
shard_key = gender  # 可能导致数据倾斜

2. 提前规划容量

# 设计时考虑未来3-5年的数据增长
class ShardingConfig:
    def __init__(self):
        # 初始设计支持1亿用户
        self.db_count = 8      # 8个数据库
        self.table_per_db = 16 # 每个库16张表
        # 总支持：8 × 16 = 128个分片
        # 每张表约78万用户，总计约1亿用户

3. 监控与运维

-- 监控每个分片的数据量和性能
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH
FROM information_schema.TABLES 
WHERE TABLE_NAME LIKE 'orders_%';

4. 性能对比示意图

性能指标       分库分表前        分库分表后
           ┌─────────────┐   ┌─────────────┐
查询响应时间 │ ████████    │   │ ████        │
           └─────────────┘   └─────────────┘
              200ms             50ms

           ┌─────────────┐   ┌─────────────┐
并发处理能力 │ ████        │   │ ████████    │
           └─────────────┘   └─────────────┘
             1000 QPS         5000 QPS

           ┌─────────────┐   ┌─────────────┐
数据量限制   │ ████        │   │ ████████    │
           └─────────────┘   └─────────────┘
             500GB            10TB+

总结

分库分表的核心价值：

• ✅ 提升性能：降低单表数据量，提高查询效率
• ✅ 提高并发：分散数据库连接压力
• ✅ 扩展存储：突破单机存储限制
• ✅ 提升可用性：单个数据库故障不影响全部服务

需要考虑的代价：

• ❌ 复杂度增加：需要处理分布式事务、跨库查询
• ❌ 运维复杂：备份、监控、扩容更复杂
• ❌ 开发成本：需要修改业务代码和查询逻辑

适用场景：

• 单表数据量超过千万级别
• 数据库连接数成为瓶颈
• 存储空间接近物理限制
• 高并发读写场景