📚 课程目录第4章数据库环境配置

4. 数据库环境配置：PostgreSQL/MySQL安装、数据库初始化、连接池配置、ORM选型

数据库是补货计划系统的持久化基石。本章聚焦于关系型数据库的选型安装、初始化脚本、连接池调优以及ORM框架的工程化选型，确保补货计划在数据读写、事务一致性和并发性能上满足生产级要求。

4.1 数据库选型：PostgreSQL vs MySQL

补货计划系统涉及大量库存事务、时间序列快照和复杂聚合查询，数据库选型直接影响SQL方言、索引策略和运维成本。以下从补货场景出发进行对比：

对比维度	PostgreSQL	MySQL (InnoDB)
事务与ACID	原生MVCC，快照隔离级别成熟	InnoDB支持，但RR级别下间隙锁较多
窗口函数/CTE	支持完善，适合补货计算中的滚动汇总	8.0+支持，但优化器成熟度略低
数组/JSON/范围类型	原生支持，适合存储补货策略参数	JSON支持，但数组和范围类型较弱
并发写入性能	高并发写入稳定，无间隙锁问题	高并发下间隙锁可能引发死锁
主从复制	流复制，逻辑复制灵活	异步/半同步复制，GTID支持
运维生态	Patroni/PgBouncer成熟	ProxySQL/Orchestrator成熟

推荐：补货计划系统优先选择 PostgreSQL，因其窗口函数、CTE递归查询和原生范围类型能显著简化安全库存计算与补货建议生成逻辑。

4.2 数据库安装与初始化

4.2.1 PostgreSQL 安装（Ubuntu 22.04 示例）

# 安装 PostgreSQL 15
sudo apt update
sudo apt install postgresql-15 postgresql-client-15

# 启动并设置开机自启
sudo systemctl enable postgresql
sudo systemctl start postgresql

# 切换到 postgres 用户创建数据库和用户
sudo -u postgres psql -c "CREATE USER replen_user WITH PASSWORD 'SecurePass123';"
sudo -u postgres psql -c "CREATE DATABASE replen_db OWNER replen_user;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE replen_db TO replen_user;"

4.2.2 数据库初始化脚本（核心表结构）

补货计划系统至少需要以下核心表：item（物料主数据）、inventory（库存快照）、replenishment_order（补货订单）、replenishment_parameter（补货参数）。

-- 补货计划系统核心表初始化 DDL
CREATE TABLE IF NOT EXISTS item (
    item_id        SERIAL PRIMARY KEY,
    sku            VARCHAR(50) UNIQUE NOT NULL,
    item_name      VARCHAR(200),
    category       VARCHAR(50),
    unit_weight    NUMERIC(10,4),
    created_at     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS inventory (
    inventory_id   SERIAL PRIMARY KEY,
    item_id        INTEGER REFERENCES item(item_id),
    warehouse_code VARCHAR(20) NOT NULL,
    on_hand_qty    NUMERIC(12,4) NOT NULL DEFAULT 0,
    allocated_qty  NUMERIC(12,4) NOT NULL DEFAULT 0,
    snapshot_time  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(item_id, warehouse_code, snapshot_time)
);

CREATE TABLE IF NOT EXISTS replenishment_parameter (
    param_id       SERIAL PRIMARY KEY,
    item_id        INTEGER REFERENCES item(item_id),
    warehouse_code VARCHAR(20) NOT NULL,
    safety_stock   NUMERIC(12,4) NOT NULL DEFAULT 0,
    reorder_point  NUMERIC(12,4) NOT NULL DEFAULT 0,
    order_up_to    NUMERIC(12,4) NOT NULL DEFAULT 0,
    lead_time_days INTEGER NOT NULL DEFAULT 1,
    effective_from DATE NOT NULL,
    effective_to   DATE,
    UNIQUE(item_id, warehouse_code, effective_from)
);

CREATE TABLE IF NOT EXISTS replenishment_order (
    order_id       SERIAL PRIMARY KEY,
    item_id        INTEGER REFERENCES item(item_id),
    warehouse_code VARCHAR(20) NOT NULL,
    suggested_qty  NUMERIC(12,4) NOT NULL,
    order_date     DATE NOT NULL DEFAULT CURRENT_DATE,
    status         VARCHAR(20) DEFAULT 'PENDING',
    created_at     TIMESTAMPTZ DEFAULT NOW()
);

-- 创建索引以加速补货查询
CREATE INDEX idx_inventory_item_wh ON inventory(item_id, warehouse_code);
CREATE INDEX idx_repl_param_item_wh ON replenishment_parameter(item_id, warehouse_code);
CREATE INDEX idx_repl_order_status ON replenishment_order(status);

注意：生产环境中建议使用数据库迁移工具（如 Alembic）管理 DDL 版本，避免手动执行初始化脚本导致环境不一致。

4.3 连接池配置

补货计划系统在批量计算时可能产生大量数据库连接，连接池配置不当会导致连接泄漏或性能瓶颈。以下以 PostgreSQL 的 PgBouncer 和 Python 应用层的 SQLAlchemy 连接池为例。

4.3.1 PgBouncer 事务级连接池配置

# pgbouncer.ini 关键配置
[databases]
replen_db = host=127.0.0.1 port=5432 dbname=replen_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式：transaction（事务级复用）
pool_mode = transaction

# 默认池大小
default_pool_size = 25
max_client_conn = 100
max_db_connections = 50

# 空闲超时（秒）
server_idle_timeout = 600

4.3.2 SQLAlchemy 应用层连接池配置

from sqlalchemy import create_engine

# PostgreSQL 连接串
DATABASE_URL = "postgresql://replen_user:SecurePass123@localhost:6432/replen_db"

engine = create_engine(
    DATABASE_URL,
    pool_size=10,           # 连接池保持的连接数
    max_overflow=20,        # 超过 pool_size 时最多额外创建数
    pool_timeout=30,        # 等待连接的超时秒数
    pool_recycle=1800,      # 连接回收时间（秒），防止长连接断开
    pool_pre_ping=True,     # 每次连接前检查连接是否有效
    echo=False
)

  连接池参数建议：补货批量计算任务建议 pool_size=10~20，max_overflow=10~30；Web API 服务建议 pool_size=5~10，避免连接数过高压垮数据库。

4.4 ORM 选型：SQLAlchemy vs Django ORM

补货计划系统通常采用微服务或模块化架构，ORM 选型需考虑灵活性、性能、迁移工具和异步支持。

对比维度	SQLAlchemy (Core + ORM)	Django ORM
架构耦合度	低，可独立使用 Core 层执行原生SQL	高，强依赖 Django 框架
查询灵活性	支持复杂 JOIN、子查询、窗口函数	ORM 层对窗口函数支持较弱，需 raw SQL
异步支持	原生支持 asyncio (asyncpg)	Django 3.1+ 支持，但生态成熟度略低
迁移工具	Alembic（独立、灵活）	Django migrations（集成、自动）
批量操作性能	bulk_insert_mappings / bulk_update_mappings 高效	bulk_create / bulk_update 支持，但功能有限
适用场景	微服务、数据管道、复杂计算	全栈 Django 应用、快速原型

推荐：补货计划系统选择 SQLAlchemy + Alembic 组合，既能利用 ORM 的便利性，又能在补货计算核心模块中使用 Core 层执行高性能原生 SQL。

4.5 数据库环境架构总览

下图展示了补货计划系统的数据库环境整体架构，涵盖连接池、ORM 层与数据库实例的关系：

4.6 工程落地检查清单

数据库版本锁定：使用 Docker 或包管理器固定 PostgreSQL 15 / MySQL 8.0 大版本，避免跨版本兼容问题。
初始化脚本版本化：所有 DDL 和初始数据放入 Git 仓库，配合 Alembic 迁移脚本管理。
连接池监控：在 Prometheus 中采集 PgBouncer 指标（活跃连接数、等待数、池利用率），设置告警阈值。
ORM 层封装：将 SQLAlchemy session 管理封装为上下文管理器，确保事务提交/回滚的确定性。
批量操作优化：补货计算批量写入时使用 bulk_insert_mappings 和 bulk_update_mappings，避免逐条提交。
连接字符串安全：通过环境变量或密钥管理服务注入数据库密码，禁止硬编码。

  最佳实践：在 CI/CD 流水线中增加数据库初始化步骤，使用临时容器执行迁移脚本，验证 DDL 兼容性后再部署到生产环境。