SQLAlchemy 技术研究性教程（五）

为面向对象编程思维而生的数据库优雅访问：SQLAlchemy 

本文章仅提供学习，切勿将其用于不法手段！

——分布式场景、读写分离与分库分表实战

前言：当单机数据库撑不住了，我们该怎么办？

经过前三篇教程，你已经能熟练用 SQLAlchemy 应对单机数据库的各种场景：从基础的 CRUD 到高并发异步操作，从复杂查询到工程化规范。但随着业务发展，数据量和并发量可能突破单机极限：

• ​数据爆炸​：用户从 10 万涨到 1 亿，单表数据量超千万，查询越来越慢；
• ​并发飙升​：活动期间每秒上万请求，数据库连接池被打满，接口频繁超时；
• ​容灾需求​：单机数据库宕机直接导致服务不可用，需要“鸡蛋不放一个篮子”。

这时候，就需要引入分布式数据库方案​：读写分离（分担读压力）、分库分表（拆分数据与负载）、分布式事务（保证跨库操作一致性）。这篇番外篇就聚焦这三个场景，用大白话+实战案例，带你解锁 SQLAlchemy 在分布式环境下的“生存法则”。

---

第十二章：读写分离——让数据库“分工合作”

12.1 什么是读写分离？为什么需要它？

想象一家餐厅：只有一个厨师（单数据库），既要炒菜（写操作：增删改）又要端菜（读操作：查），高峰期肯定忙不过来。

​读写分离的思路是：雇两个厨师——一个专门炒菜（主库，负责写操作），一个专门端菜（从库，负责读操作）。这样：

• 写操作集中到主库，避免锁竞争；
• 读操作分散到多个从库，提升查询吞吐量；
• 主库数据同步到从库（通过数据库主从复制），保证数据一致性。

12.2 SQLAlchemy 如何实现读写分离？

SQLAlchemy 本身不直接提供读写分离功能，但可以通过多引擎+路由规则实现：为主库和从库分别创建 Engine，然后根据操作类型（读/写）选择对应的引擎。

12.3 实战：基于 SQLAlchemy 的读写分离方案

12.3.1 环境准备：搭建主从数据库

以 MySQL 为例，先在主库（master）创建一个测试库 blog，并配置主从复制（具体步骤参考 MySQL 官方文档）：

• 主库：192.168.1.100:3306（负责写操作）；
• 从库：192.168.1.101:3306（负责读操作，同步主库数据）。

12.3.2 定义主从引擎与路由规则

创建两个引擎（主库写、从库读），并实现一个“路由类”，根据操作类型选择引擎：

from sqlalchemy import create_engine, MetaData  
from sqlalchemy.orm import sessionmaker, scoped_session  

# --------------------------  
# 1. 定义主库（写）和从库（读）引擎  
# --------------------------  
# 主库引擎（负责增删改）  
master_engine = create_engine(  
    "mysql+pymysql://root:123456@192.168.1.100:3306/blog",  
    pool_size=10,  # 连接池大小（写操作少，池子小一点）  
    max_overflow=5,  
    echo=False  
)  

# 从库引擎（负责查，可配置多个从库轮询）  
slave_engines = [  
    create_engine(  
        "mysql+pymysql://root:123456@192.168.1.101:3306/blog",  
        pool_size=20,  # 读操作多，池子大一点  
        max_overflow=10,  
        echo=False  
    ),  
    # 可添加更多从库，实现负载均衡  
]  

# --------------------------  
# 2. 定义路由类：根据操作类型选择引擎  
# --------------------------  
class RoutingSession:  
    def __init__(self):  
        self.master_session = scoped_session(sessionmaker(bind=master_engine))  
        # 从库会话池（轮询选择从库）  
        self.slave_sessions = [scoped_session(sessionmaker(bind=engine)) for engine in slave_engines]  
        self.slave_index = 0  # 轮询计数器  

    def get_write_session(self):  
        """获取主库会话（写操作）"""  
        return self.master_session()  

    def get_read_session(self):  
        """轮询获取从库会话（读操作）"""  
        session = self.slave_sessions[self.slave_index % len(self.slave_sessions)]()  
        self.slave_index += 1  
        return session  

    def remove(self):  
        """清理所有会话（类似 session.remove()）"""  
        self.master_session.remove()  
        for s in self.slave_sessions:  
            s.remove()  

# 全局路由实例  
db_routing = RoutingSession()  

12.3.3 封装读写操作方法

基于路由类，封装业务常用的读写方法，隐藏底层引擎选择逻辑：

from sqlalchemy.orm import Query  

class UserService:  
    @staticmethod  
    def create_user(username: str, email: str):  
        """新增用户（写操作，用主库）"""  
        session = db_routing.get_write_session()  
        try:  
            user = User(username=username, email=email)  
            session.add(user)  
            session.commit()  
            return user  
        except Exception as e:  
            session.rollback()  
            raise e  
        finally:  
            session.close()  

    @staticmethod  
    def get_user_by_username(username: str):  
        """查询用户（读操作，用从库）"""  
        session = db_routing.get_read_session()  
        try:  
            user = session.query(User).filter(User.username == username).first()  
            return user  
        finally:  
            session.close()  

    @staticmethod  
    def list_active_users():  
        """查询活跃用户列表（读操作，用从库）"""  
        session = db_routing.get_read_session()  
        try:  
            users = session.query(User).filter(User.is_active == True).all()  
            return users  
        finally:  
            session.close()  

12.3.4 注意事项：主从延迟问题

主从复制是异步的（默认情况下），可能存在“主库数据已更新，但从库还没同步”的延迟（比如主库刚插入一条用户，从库查不到）。

​解决方案​：

• 对实时性要求高的读操作（如“刚下单后立即查订单”），强制走主库；
• 在路由类中增加“强制主库”参数：

class RoutingSession:  
    # ... 其他方法 ...  

    def get_read_session(self, use_master=False):  
        """use_master=True 时强制用主库（解决延迟问题）"""  
        if use_master:  
            return self.master_session()  
        # 否则轮询从库  
        session = self.slave_sessions[self.slave_index % len(self.slave_sessions)]()  
        self.slave_index += 1  
        return session  

# 业务代码中强制主库查询  
user = UserService.get_user_by_username("刚下单的用户", use_master=True)  

​本章重点​：

• 读写分离通过“主库写、从库读”分担压力，核心是主从复制+引擎路由；
• SQLAlchemy 需手动实现多引擎管理和路由规则（主库会话/从库轮询）；
• 注意主从延迟问题，实时性要求高的读操作需强制走主库。

---

第十三章：分库分表——当单表数据“爆仓”时怎么办？

13.1 什么是分库分表？为什么需要它？

如果读写分离后，单表数据量仍超过千万（比如用户表有 5000 万数据），查询会越来越慢（索引树变深、磁盘 IO 增加）。这时候需要分库分表​：

• ​分表​：将一个大表按规则拆成多个小表（如按时间分表：user_2023、user_2024；按 ID 取模分表：user_0~user_9）；
• ​分库​：将数据分散到多个数据库（如按业务分库：user_db、order_db；按用户 ID 哈希分库：db_0~db_3）。

13.2 分库分表的两种核心策略

（1）垂直分表/分库（按业务拆分）

• ​垂直分表​：将一个表的“宽字段”拆成多个表（如用户表拆为 user_base（基本信息）和 user_profile（详细资料））；
• ​垂直分库​：按业务模块拆分数据库（如用户相关表放 user_db，订单相关表放 order_db）。

​优点​：业务解耦，单库/单表压力减小；
​缺点​：跨库关联查询困难（如查用户及其订单需跨 user_db 和 order_db）。

（2）水平分表/分库（按数据行拆分）

• ​水平分表​：按规则将同一表的数据行分散到多个表（如按用户 ID 取模：user_id % 10 得到表后缀 0~9）；
• ​水平分库​：按规则将数据行分散到多个数据库（如按用户 ID 哈希：hash(user_id) % 4 得到库后缀 0~3）。

​优点​：单表数据量减少，查询性能提升；
​缺点​：分片规则设计复杂（需避免数据倾斜），跨库分页/排序困难。

13.3 SQLAlchemy 实现水平分表：按用户 ID 取模

以用户表按 user_id % 10 拆分为 10 张表（user_0~user_9）为例，演示如何用 SQLAlchemy 实现分表路由。

13.3.1 定义分表模型与路由规则

为每个分表定义相同的模型结构，再通过路由类根据 user_id 选择对应的表：

from sqlalchemy import Column, Integer, String, create_engine  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import sessionmaker  

Base = declarative_base()  

# 分表公共模型（所有分表结构相同）  
class UserShard(Base):  
    __abstract__ = True  # 抽象类，不生成表  
    id = Column(Integer, primary_key=True, autoincrement=True)  
    username = Column(String(50), nullable=False)  
    email = Column(String(100), unique=True)  

# 动态生成 10 张分表（user_0 ~ user_9）  
sharded_tables = {}  
for i in range(10):  
    class_name = f"User_{i}"  
    table_name = f"user_{i}"  
    # 动态创建类，继承 UserShard，指定表名  
    cls = type(  
        class_name,  
        (UserShard,),  
        {"__tablename__": table_name}  
    )  
    sharded_tables[i] = cls  

# 分表路由函数：根据 user_id 计算分表后缀  
def get_shard(user_id: int) -> int:  
    return user_id % 10  # 取模分表规则  

# 获取分表模型  
def get_user_shard(user_id: int) -> UserShard:  
    shard_id = get_shard(user_id)  
    return sharded_tables[shard_id]  

13.3.2 封装分表 CRUD 操作

基于路由函数，封装分表的增删改查方法：

# 假设所有分表共享同一个数据库引擎（也可分库，需为每个库创建引擎）  
engine = create_engine("mysql+pymysql://root:123456@192.168.1.100:3306/blog")  
Session = sessionmaker(bind=engine)  

class ShardedUserService:  
    @staticmethod  
    def create_user(user_id: int, username: str, email: str):  
        """新增用户（根据 user_id 路由到对应分表）"""  
        session = Session()  
        try:  
            shard_model = get_user_shard(user_id)  # 获取分表模型  
            user = shard_model(id=user_id, username=username, email=email)  
            session.add(user)  
            session.commit()  
            return user  
        except Exception as e:  
            session.rollback()  
            raise e  
        finally:  
            session.close()  

    @staticmethod  
    def get_user(user_id: int):  
        """查询用户（根据 user_id 路由到对应分表）"""  
        session = Session()  
        try:  
            shard_model = get_user_shard(user_id)  
            user = session.query(shard_model).filter(shard_model.id == user_id).first()  
            return user  
        finally:  
            session.close()  

    @staticmethod  
    def list_users_in_shard(shard_id: int):  
        """查询某个分表的所有用户（用于运维）"""  
        session = Session()  
        try:  
            shard_model = sharded_tables[shard_id]  
            users = session.query(shard_model).all()  
            return users  
        finally:  
            session.close()  

13.3.3 分库分表的挑战与应对

• ​跨分片查询​：如“查所有用户名包含‘张’的用户”，需遍历所有分表查询后合并结果（性能差）。​应对​：尽量避免跨分片查询，或在业务层限制（如只允许按分片键查询）。
• ​分布式 ID​：分表后自增 ID 会重复（如 user_0 和 user_1 都可能有 id=1）。​应对​：用全局唯一 ID（如雪花算法、UUID）。
• ​事务一致性​：跨分片的事务（如“用户 A 给用户 B 转账”）无法用单机事务保证。​应对​：用分布式事务（如 Seata、TCC），或业务层重试+补偿。

​本章重点​：

• 分库分表解决单表数据量过大的问题，分表可按规则（如取模、时间）拆分，分库可按业务或哈希拆分；
• SQLAlchemy 通过动态模型+路由函数实现分表，需手动处理分片规则和数据路由；
• 分库分表带来跨分片查询、分布式 ID、事务一致性等挑战，需结合业务设计规避。

---

第十四章：分布式事务——跨库操作的“最终一致性”

14.1 为什么需要分布式事务？

在分库分表场景下，一个业务操作可能涉及多个数据库（如“下单”需写 order_db 和 stock_db）。单机事务（ACID）无法保证跨库操作的原子性：如果 order_db 写入成功，但 stock_db 扣减库存失败，就会出现“订单创建了但库存没扣”的不一致状态。

14.2 分布式事务的两种经典方案

（1）XA 事务（两阶段提交）

• ​原理​：通过一个“协调者”（如数据库中间件）统一调度，分“准备阶段”（各库预提交）和“提交阶段”（所有库确认提交或回滚）；
• ​优点​：强一致性；
• ​缺点​：性能差（协调者故障会导致资源锁定），不适合高并发场景。

（2）柔性事务（最终一致性）

• ​原理​：不追求强一致性，而是通过“重试+补偿”保证最终一致（如“下单”失败后，异步重试扣库存，或人工介入补偿）；
• ​常见模式​：TCC（Try-Confirm-Cancel）、Saga（长事务拆分）、本地消息表；
• ​优点​：高性能，适合互联网高并发场景；
• ​缺点​：实现复杂，需业务层处理补偿逻辑。

14.3 SQLAlchemy 结合 TCC 模式实现分布式事务

以“下单”场景为例（涉及 order_db 和 stock_db），用 TCC 模式保证最终一致性。

14.3.1 TCC 模式三阶段

• ​Try​：预留资源（如检查库存是否充足，冻结库存）；
• ​Confirm​：确认执行业务（如正式扣减库存，创建订单）；
• ​Cancel​：取消执行业务（如解冻库存，取消订单）。

14.3.2 代码实现（简化版）

from sqlalchemy.orm import Session  
import time  

# 假设已有两个库的引擎和会话（order_engine、stock_engine）  
OrderSession = sessionmaker(bind=order_engine)  
StockSession = sessionmaker(bind=stock_engine)  

class OrderService:  
    @staticmethod  
    def try_create_order(user_id: int, product_id: int, quantity: int):  
        """Try 阶段：检查并冻结库存"""  
        stock_session = StockSession()  
        order_session = OrderSession()  
        try:  
            # 1. 检查库存是否充足（Try 操作）  
            stock = stock_session.query(Stock).filter(Stock.product_id == product_id).first()  
            if not stock or stock.available < quantity:  
                return False, "库存不足"  

            # 2. 冻结库存（预留资源）  
            stock.available -= quantity  
            stock.frozen += quantity  
            stock_session.commit()  

            # 3. 预创建订单（状态为“待确认”）  
            order = Order(  
                user_id=user_id,  
                product_id=product_id,  
                quantity=quantity,  
                status="PENDING"  # 待确认状态  
            )  
            order_session.add(order)  
            order_session.commit()  

            return True, {"order_id": order.id, "stock_frozen": quantity}  
        except Exception as e:  
            stock_session.rollback()  
            order_session.rollback()  
            return False, f"Try 阶段失败：{str(e)}"  
        finally:  
            stock_session.close()  
            order_session.close()  

    @staticmethod  
    def confirm_order(order_id: int):  
        """Confirm 阶段：确认订单和扣减库存"""  
        stock_session = StockSession()  
        order_session = OrderSession()  
        try:  
            # 1. 更新订单状态为“已完成”  
            order = order_session.query(Order).filter(Order.id == order_id).first()  
            if not order or order.status != "PENDING":  
                return False, "订单状态异常"  
            order.status = "CONFIRMED"  

            # 2. 扣减冻结库存（正式扣减）  
            stock = stock_session.query(Stock).filter(Stock.product_id == order.product_id).first()  
            stock.frozen -= order.quantity  
            stock.used += order.quantity  

            # 提交事务（两个库各自提交）  
            order_session.commit()  
            stock_session.commit()  
            return True, "订单确认成功"  
        except Exception as e:  
            order_session.rollback()  
            stock_session.rollback()  
            # 触发 Cancel 阶段  
            OrderService.cancel_order(order_id)  
            return False, f"Confirm 阶段失败：{str(e)}"  
        finally:  
            stock_session.close()  
            order_session.close()  

    @staticmethod  
    def cancel_order(order_id: int):  
        """Cancel 阶段：取消订单并解冻库存"""  
        stock_session = StockSession()  
        order_session = OrderSession()  
        try:  
            # 1. 更新订单状态为“已取消”  
            order = order_session.query(Order).filter(Order.id == order_id).first()  
            if not order:  
                return False, "订单不存在"  
            order.status = "CANCELLED"  

            # 2. 解冻库存  
            stock = stock_session.query(Stock).filter(Stock.product_id == order.product_id).first()  
            stock.frozen -= order.quantity  
            stock.available += order.quantity  

            # 提交事务  
            order_session.commit()  
            stock_session.commit()  
            return True, "订单取消成功"  
        except Exception as e:  
            order_session.rollback()  
            stock_session.rollback()  
            return False, f"Cancel 阶段失败：{str(e)}"  
        finally:  
            stock_session.close()  
            order_session.close()  

# 业务入口：下单流程  
def place_order(user_id: int, product_id: int, quantity: int):  
    # Step 1: Try 阶段  
    success, result = OrderService.try_create_order(user_id, product_id, quantity)  
    if not success:  
        return result  

    # Step 2: 模拟业务确认（实际可能是支付回调触发）  
    order_id = result["order_id"]  
    time.sleep(1)  # 模拟业务处理时间  

    # Step 3: Confirm 阶段（失败会自动触发 Cancel）  
    success, msg = OrderService.confirm_order(order_id)  
    return msg  

​本章重点​：

• 分布式事务解决跨库操作的原子性问题，XA 强一致但性能差，柔性事务（如 TCC）最终一致更适合高并发；
• TCC 模式通过 Try（预留）、Confirm（确认）、Cancel（取消）三阶段保证一致性，需业务层实现补偿逻辑；
• SQLAlchemy 作为 ORM 工具，需配合业务逻辑实现分布式事务的“资源管理”和“状态同步”。

---

结语：从“会用”到“用好”，SQLAlchemy 的无限可能

从单机到分布式，我们从“数据库小白”走到了“分布式数据库实践者”：

• ​基础篇​：用 ORM 简化 CRUD，用关系映射理清表关联；
• ​进阶篇​：用 Alembic 管理表结构变更，用性能调优应对数据增长；
• ​终章​：用异步操作扛住高并发，用复杂查询搞定统计分析；
• ​番外篇​：用读写分离、分库分表突破单机极限，用分布式事务保证数据一致。

但技术探索永无止境：云原生数据库（如 TiDB、CockroachDB）、Serverless 数据库、AI 驱动的查询优化……未来还有更多场景等待我们去解锁。

记住：SQLAlchemy 是“工具”，不是“目的”。真正的价值在于用它解决实际问题——让用户更快看到页面，让数据更安全可靠，让业务走得更远。

保持好奇，持续实践，你会在数据库技术的路上越走越宽。SQLAlchemy 之旅，我们更高处见！

注：本文仅用于教育目的，实际渗透测试必须获得合法授权。未经授权的黑客行为是违法的。