连接池、读写分离与慢查询排查

## 第 17 章 · 连接池、读写分离与慢查询排查

本章目标：掌握 Django CONN_MAX_AGE、pgBouncer 概念与 django-db-connection-pool 简介；通过 DATABASE_ROUTERS 实现读写分离（db-primary.example.com / db-replica.example.com）；复习 select_related / prefetch_related，并运用 only / defer、annotate 编写复杂查询；使用 django-debug-toolbar、django-silk 或 logging 定位慢查询；理解 EXPLAIN ANALYZE 概念；建立 N+1 回归测试基线。

学时建议：6～7 小时（含 2 小时性能实验）

前置：本模块 ch05 ORM 查询、ch09 日志、ch11 生产部署；ch16 RBAC 列表查询可作为优化对象。

17.1 性能问题从哪来

shop-demo 商品量上涨后的典型症状：

 Django Worker / Gunicorn       PostgreSQL
┌────────────────┐            ┌────────────────────┐
│ Django ORM     │── pool ──►│ db-primary.example │ 写
└────────────────┘            └─────────┬──────────┘
      │ 读查询（选修）                    │ 流复制
      └──────────────────────────────►┌────────────────────┐
                                      │ db-replica.example │ 读
                                      └────────────────────┘

数据库主机均为虚构域名；本地可用 SQLite 学 ORM，连接池与读写分离在 staging / 生产用 PostgreSQL 验证。

17.2 连接池：CONN_MAX_AGE、pgBouncer 与第三方包

Django 默认每个请求打开并关闭数据库连接。CONN_MAX_AGE 让连接在请求结束后保留在进程内复用。

shopdemo/settings_prod.py：

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ.get("DB_NAME", "shop_demo"),
        "USER": os.environ.get("DB_USER", "shop_demo"),
        "PASSWORD": os.environ.get("DB_PASSWORD", ""),
        "HOST": os.environ.get("DB_HOST", "db-primary.example.com"),
        "PORT": "5432",
        "CONN_MAX_AGE": 600,          # 秒；0 表示每次请求关闭
        "CONN_HEALTH_CHECKS": True,   # Django 4.1+：取用前检测连接
    },
}

连接数估算：总连接 ≈ Gunicorn workers × 每 worker 连接数 + Celery workers × 连接数。示例 4 workers × 2 库 ≈ 8～16，数据库 max_connections 须留余量给管理工具与迁移。

pgBouncer（概念）

应用多进程时，pgBouncer 作为 PostgreSQL 前连接池代理：Gunicorn 连 6432，PgBouncer 用少量连接连真实库 5432。

Django 连 PgBouncer 时通常 CONN_MAX_AGE=0（由 PgBouncer 负责池化），并启用 CONN_HEALTH_CHECKS。

django-db-connection-pool（简介）

社区包在 Engine 层做 SQLAlchemy 风格池化，例如：

pip install django-db-connection-pool

DATABASES = {
    "default": {
        "ENGINE": "dj_db_conn_pool.backends.postgresql",
        "POOL_OPTIONS": {
            "POOL_SIZE": 10,
            "MAX_OVERFLOW": 10,
            "RECYCLE": 1800,
            "PRE_PING": True,
        },
        # ... HOST / NAME 等
    },
}

17.3 DATABASE_ROUTERS 读写分离

settings_prod.py：

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "db-primary.example.com",
        # ... NAME, USER, PASSWORD
        "CONN_MAX_AGE": 600,
        "CONN_HEALTH_CHECKS": True,
    },
    "replica": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "db-replica.example.com",
        "NAME": os.environ.get("DB_NAME", "shop_demo"),
        "USER": os.environ.get("DB_USER", "shop_demo"),
        "PASSWORD": os.environ.get("DB_PASSWORD", ""),
        "CONN_MAX_AGE": 600,
        "CONN_HEALTH_CHECKS": True,
    },
}
DATABASE_ROUTERS = ["shopdemo.db_router.PrimaryReplicaRouter"]

shopdemo/db_router.py：

class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        return "replica"

    def db_for_write(self, model, **hints):
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        return True

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "default"

写后读主库（避免复制延迟 50～500ms 导致「刚创建查不到」）：

from django.db import transaction

@transaction.atomic(using="default")
def create_product_and_return(user, data):
    product = Product.objects.create(**data, created_by=user)
    # 同一事务内读 default（主库）
    return Product.objects.using("default").get(pk=product.pk)

# 只读列表可走 replica
def published_products():
    return Product.objects.using("replica").filter(is_published=True)

17.4 查询优化：select_related、prefetch_related、only、annotate

N+1 识别与修复

# 坏：11 次 SQL（10 条商品 + 每条查 category）
products = Product.objects.filter(is_published=True)[:10]
for p in products:
    print(p.category.name)

select_related（多对一 / 一对一，一次 JOIN）：

products = (
    Product.objects
    .select_related("category", "created_by")
    .filter(is_published=True)[:10]
)

prefetch_related（反向外键 / 多对多，两次 IN 查询）：

categories = (
    Category.objects
    .prefetch_related("products")
    .all()
)