首页/资讯中心/详情

MySQL 系列：第29篇 分库分表与分布式扩展

MySQL 系列：第29篇 分库分表与分布式扩展
📅 发布时间：2026/6/30 1:47:01

IT策士 10余年一线大厂经验，专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章，助你少走弯路。

前面的文章中，分区表让我们在单机上把大表“切碎”了。但如果业务持续爆炸式增长，单台服务器的 CPU、内存、磁盘 I/O 终将触及天花板。此时唯一的出路就是分库分表——将数据分散到多台 MySQL 实例上，实现真正的水平扩展。今天我们用 Python 配合 ShardingSphere 和雪花算法，从拆分策略到分布式 ID，再到跨库事务，彻底讲透分库分表。

1. 为什么分区不够用？

分区表仍然是单机方案，受限于一台服务器的物理资源。当以下情况出现时，就需要分库分表了：

分库分表的核心思想：将原本一个数据库实例的负载，分摊到多个数据库实例上。

2. 垂直拆分 vs 水平拆分

垂直拆分（按业务模块） ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ 用户服务 │ │ 订单服务 │ │ 商品服务 │ │ MySQL 实例A │ │ MySQL 实例B │ │ MySQL 实例C │ └──────────────┘ └──────────────┘ └──────────────┘ 水平拆分（按数据行分片） ┌──────────────────────────────────────────────┐ │ 订单表(order_0)│ 订单表(order_1)│... │ │ user_id %4=0│ user_id %4=1│ │ │ MySQL 实例1 │ MySQL 实例2 │ │ └──────────────────────────────────────────────┘

垂直拆分（按业务）：不同业务表放在不同库，简单直接，但不能解决单表过大的问题。
水平拆分（按数据）：将一张大表的多行数据分散到多张结构相同的表中，是我们今天的主角。

绝大多数大型系统是垂直拆分 + 水平拆分组合使用。

3. 水平拆分策略：数据如何分布

3.1 HASH 分片

# 用 Python 模拟 HASH 分片路由def hash_route(user_id, total_shards):"""根据 user_id 哈希决定落在哪个分片"""returnuser_id % total_shards# 假设 4 个分片foruidin[1001,1002,1003,1004,2001]: shard=hash_route(uid,4)print(f"user_id={uid} → shard_{shard}")

预期输出：

user_id=1001→ shard_1user_id=1002→ shard_2user_id=1003→ shard_3user_id=1004→ shard_0user_id=2001→ shard_1

优点：数据均匀分布，写入负载均衡。
缺点：增加分片数时需要重新哈希，数据迁移量大（一致性哈希可缓解）。

3.2 RANGE 分片

# 按时间范围分片def range_route(order_date, shard_ranges):"""根据日期范围路由"""forshard_id,(start, end)inenumerate(shard_ranges):ifstart<=order_date<end:returnshard_idreturn-1# 2020~2025 每年一个分片ranges=[("2020-01-01","2021-01-01"),("2021-01-01","2022-01-01"),("2022-01-01","2023-01-01"),("2023-01-01","2024-01-01"),("2024-01-01","2025-01-01"),("2025-01-01","2026-01-01")]print(f"2021-06-15 订单 → shard_{range_route('2021-06-15', ranges)}")print(f"2024-12-01 订单 → shard_{range_route('2024-12-01', ranges)}")

优点：扩容简单（添加新分片即可），按时间范围查询效率高。
缺点：写入热点（最新分片压力大），数据不均匀。

3.3 分片键的选择

分片键是决定数据落在哪个分片的列，选择标准：

  • 大部分查询都带这个条件

  • 数据分布均匀

  • 避免跨分片事务

电商订单通常选user_id或order_id。

4. 全局唯一 ID 生成

分库分表后，原本的自增主键会冲突。需要全局唯一的 ID 生成方案。

4.1 雪花算法（Snowflake）

Twitter 开源的经典方案，生成 64 位长整型 ID：

┌─┬─────────────────────────────┬───────────┬──────────────┐ │0│41位时间戳（毫秒） │10位机器 │12位序列号 │ └─┴─────────────────────────────┴───────────┴──────────────┘1位保留 可用69年1024节点4096/ms
importtimeimportthreading class Snowflake: def __init__(self, worker_id,datacenter_id=1): self.worker_id=worker_id# 机器ID (0-31)self.datacenter_id=datacenter_id# 数据中心ID (0-31)self.sequence=0self.last_timestamp=-1# 起始时间戳 (2020-01-01 00:00:00)self.twepoch=1577836800000self.worker_id_bits=5self.datacenter_id_bits=5self.sequence_bits=12self.max_worker_id=-1^(-1<<self.worker_id_bits)self.max_datacenter_id=-1^(-1<<self.datacenter_id_bits)self.sequence_mask=-1^(-1<<self.sequence_bits)self.worker_id_shift=self.sequence_bits self.datacenter_id_shift=self.sequence_bits + self.worker_id_bits self.timestamp_shift=self.sequence_bits + self.worker_id_bits + self.datacenter_id_bits self.lock=threading.Lock()def _current_millis(self):returnint(time.time()*1000)def _wait_next_millis(self, last_timestamp): timestamp=self._current_millis()whiletimestamp<=last_timestamp: timestamp=self._current_millis()returntimestamp def next_id(self): with self.lock: timestamp=self._current_millis()iftimestamp<self.last_timestamp: raise Exception("时钟回拨异常")iftimestamp==self.last_timestamp: self.sequence=(self.sequence +1)&self.sequence_maskifself.sequence==0: timestamp=self._wait_next_millis(self.last_timestamp)else: self.sequence=0self.last_timestamp=timestampreturn((timestamp - self.twepoch)<<self.timestamp_shift)|\(self.datacenter_id<<self.datacenter_id_shift)|\(self.worker_id<<self.worker_id_shift)|\self.sequence# 测试snowflake=Snowflake(worker_id=1)for_inrange(5): print(f"生成的 ID: {snowflake.next_id()}")

预期输出：

生成的 ID:1234567890123456789生成的 ID:1234567890123456790生成的 ID:1234567890123456791...

优点：高性能（单机可生成数十万/秒）、趋势递增（对索引友好）。
缺点：强依赖机器时钟，时钟回拨可能导致 ID 重复（需额外处理）。

4.2 其他方案

  • 数据库号段模式：如美团 Leaf，每次从数据库取一段号段在内存中分配。

  • Redis 自增：INCR命令直接生成递增 ID，简单但有单点风险。

5. 分布式事务：最终一致性

分库分表后，一个业务操作可能跨多个数据库实例，传统的 InnoDB 事务不够用了。

5.1 CAP 理论与 BASE

  • CAP：一致性（Consistency）、可用性（Availability）、分区容错性（Partition Tolerance），三者不可兼得。

  • BASE：基本可用（Basically Available）、软状态（Soft State）、最终一致性（Eventually Consistent）。

分布式场景下，通常牺牲强一致性换取可用性，采用最终一致性方案。

5.2 常用解决方案

5.3 Python 演示本地消息表

# 简化版：本地事务 + 消息表cursor.execute(""" CREATE TABLE IF NOT EXISTS user_order(idBIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2))""")cursor.execute(""" CREATE TABLE IF NOT EXISTS outbox_message(idBIGINT AUTO_INCREMENT PRIMARY KEY, event_type VARCHAR(50), payload JSON, status VARCHAR(20)DEFAULT'PENDING')""")def create_order_with_message(conn, order_id, user_id, amount):"""在同一个本地事务中创建订单并写入消息""" conn.autocommit=False cursor=conn.cursor()try: cursor.execute("INSERT INTO user_order (id, user_id, amount) VALUES (%s,%s,%s)",(order_id, user_id, amount))cursor.execute("INSERT INTO outbox_message (event_type, payload) VALUES (%s,%s)",('ORDER_CREATED', f'{{"order_id":{order_id}}}'))conn.commit()print("✅ 订单创建 + 消息写入成功")except Exception as e: conn.rollback()print(f"❌ 失败: {e}")# 后续由定时任务扫描 outbox_message 发送到 MQcreate_order_with_message(conn, snowflake.next_id(),1001,299.00)

Seata 接入示例（需部署 Seata Server）：

# Seata 对 SQLAlchemy 提供了拦截器，自动管理全局事务# from seata import GlobalTransaction# 具体集成代码请参考 Seata 官方文档

6. 中间件概览：ShardingSphere 与 Vitess

实际生产中，不会用 Python 手写路由逻辑，而是借助成熟中间件。

6.1 Apache ShardingSphere

Java 生态最流行的分库分表中间件，提供ShardingSphere-JDBC（客户端 SDK）和ShardingSphere-Proxy（独立代理）两种模式。

应用(Python)──→ ShardingSphere-Proxy ──→ 分片1(MySQL)├──→ 分片2(MySQL)└──→ 分片3(MySQL)

通过 Python 连接 ShardingSphere-Proxy：

# ShardingSphere-Proxy 像一个普通 MySQLsharding_conn=mysql.connector.connect(host="127.0.0.1",port=3307,# Proxy 端口user="root",password="root",database="sharding_db")sharding_cursor=sharding_conn.cursor()# SQL 正常写，Proxy 自动路由到对应分片sharding_cursor.execute("SELECT * FROM t_order WHERE user_id = 1001")print(sharding_cursor.fetchall())

6.2 Vitess

YouTube 开源的 Go 语言数据库集群系统，原生支持 MySQL 协议，被大量云原生项目采用。

核心组件：

  • VtGate：智能代理，解析 SQL 并路由到正确分片

  • VtTablet：每个 MySQL 实例的代理

  • Topology：存储集群元数据

适用场景：超大规模（数百节点）、Kubernetes 原生环境。

# Vitess 也兼容 MySQL 协议，Python 连接方式不变# conn = mysql.connector.connect(host="vtgate-host", port=3306, ...)

选型建议：

7. 动手试试：设计一个分片系统

  1. 设计分片规则：假设user表按id % 4哈希分片到 4 个库，写 Python 路由函数，输入 user_id 返回目标数据库连接信息。

  2. 用雪花算法生成 ID：在 Python 中实现 Snowflake，为每个分片实例分配不同的worker_id。

  3. 模拟跨分片查询：当查询不带分片键时，需要查询所有分片再合并结果。写一个函数实现“全分片扫描 + 聚合”。

参考代码（第1题）：

SHARDS={0:{"host":"127.0.0.1","port":3306,"database":"user_shard0"},1:{"host":"127.0.0.1","port":3307,"database":"user_shard1"},2:{"host":"127.0.0.1","port":3308,"database":"user_shard2"},3:{"host":"127.0.0.1","port":3309,"database":"user_shard3"},}def get_shard_connection(user_id): shard_id=user_id %4cfg=SHARDS[shard_id]returnmysql.connector.connect(**cfg)

8. 总结

今天我们正式进入了分布式数据库的世界：

  • 垂直拆分：按业务模块分库，降低耦合。

  • 水平拆分：按数据行分片，HASH 均匀但迁移难，RANGE 易扩容但有热点。

  • 全局 ID：雪花算法（高性能、趋势递增）是主流选择。

  • 分布式事务：最终一致性是常态，本地消息表、Seata、TCC 各有所长。

  • 中间件：ShardingSphere-Proxy 是异构语言最佳拍档，Vitess 是超大规模利器。

下一篇将是本系列的收官之战——大型项目综合实战与性能调优复盘，我们将整合所有知识设计一个高并发电商系统。

想了解更多还可以去各个平台搜索「IT策士」，一起升级 IT 思维 ！