Django 深分页性能优化技术方案

1️⃣ 背景与问题描述

在计量明细数据查询接口中，随着数据量增长（约 240 万条记录），分页查询出现严重性能问题：

• 查询方式：Django ORM + MySQL
• 分页方式：PageNumberPagination（LIMIT + OFFSET）
• 查询场景：按 create_at DESC, id DESC 排序
• 深分页场景：page ≈ 200,000+

❗ 初始问题表现

❗ SQL示例（优化前）

SELECT *
FROM operation_center_measuredetail
ORDER BY create_at DESC, id DESC
LIMIT 10 OFFSET 2400000;

2️⃣ 问题分析过程

2.1 SQL执行计划分析

通过 EXPLAIN 和实际压测发现：

• OFFSET 导致 MySQL 必须扫描大量索引记录
• 深分页本质为：“扫描前 N 条数据并丢弃”

2.2 性能瓶颈拆解

主要瓶颈：

① 深分页 OFFSET 扫描（核心瓶颈）
② SELECT * 导致大字段回表
③ ORDER BY + filesort（部分场景）
④ 宽表数据 IO 开销

2.3 关键观察

• page 越大，耗时线性增长
• 单次请求耗时极不稳定
• SQL耗时占主导，但回表和 ORM 也存在开销

3️⃣ 优化方案设计

3.1 核心优化思路

采用“ID子查询 + 主表 JOIN”替代直接 OFFSET 查询：

✔ 优化SQL

SELECT md.*
FROM operation_center_measuredetail md
INNER JOIN (
    SELECT id
    FROM operation_center_measuredetail
    WHERE create_at BETWEEN ...
    ORDER BY create_at DESC, id DESC
    LIMIT 10 OFFSET 2400000
) t ON md.id = t.id;

3.2 优化原理

✔ 核心优化点

1. 子查询只扫描 id（减少回表）
2. JOIN 主表只查询 10 条记录
3. 将“大扫描 → 小回表”结构化拆分

3.3 优化效果

4️⃣ 并发压测验证

4.1 测试环境

• MySQL（Docker环境）
• Django + Gunicorn
• workers=2, threads=4
• 并发：10

4.2 测试结果

并发数：10
成功率：100%
总耗时：13.17s
平均耗时：8.94s
最小耗时：7.17s
最大耗时：13.16s

4.3 分析结论

• 单请求 SQL：约 1.7s
• 总接口：约 3s（单线程）
• 并发后性能下降 → 资源竞争导致排队

竞争来源：

• MySQL IO + buffer pool 竞争
• Gunicorn worker/threads 限制
• ORM + 序列化开销叠加

5️⃣ 深层优化分析

5.1 SQL vs ORM vs 序列化拆解

推测耗时分布：

SQL执行        ~1.7s
ORM构建对象    ~0.3–0.8s
Serializer     ~0.2–1.0s
JSON输出       ~0.1–0.3s

👉 后端非SQL开销约 30%–40%

5.2 核心瓶颈结论

当前系统瓶颈已从：

❗ SQL执行瓶颈转变为：❗ 深分页 + ORM + 并发资源竞争

6️⃣ 最终优化结论

✔ 已完成优化

• 深分页 SQL 重构（OFFSET → 子查询 + JOIN）
• 回表次数从 240万 → 10
• 查询性能提升约 30倍
• 深分页从 90s 优化至 3s

⚠ 当前系统状态

SQL已优化完成
但并发性能受限于：
- MySQL IO竞争
- ORM + serializer开销
- worker线程模型

7️⃣ 后续优化方向

🥇 必选优化（强烈建议）

✔ 限制深分页

max_page 或 max_offset

或：

限制查询时间范围（7/30天）

🥈 推荐优化

✔ Keyset Pagination（游标分页）

• 不依赖 OFFSET
• 性能稳定
• 深分页无退化

🥉 进阶优化

• Redis缓存热点页
• 只查询必要字段（避免 SELECT *）
• values() 替代 ORM model
• 冷热数据分离

🧊 架构级优化

• 历史数据归档（OLAP）
• MySQL + ClickHouse 分层
• 读写分离

8️⃣ 产出价值总结

本次优化带来的核心收益：

✔ 性能提升

• 90s → 3s（约30倍优化）

✔ 系统稳定性

• 深分页可用
• 支持千万级数据扩展

✔ 工程价值

• 保留业务“任意跳页能力”
• 不改前端接口结构
• 可复用为通用分页优化方案

9️⃣ 一句话总结

通过将 OFFSET 深分页改造为“子查询 ID 分页 + 主表 JOIN”，显著减少回表与扫描成本，使240万级数据深分页查询从分钟级优化至秒级，同时验证了系统瓶颈已从 SQL 执行转移至并发资源竞争与 ORM 序列化阶段。