django-db-performance

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Django DB Performance

Django数据库性能

Use this as the orchestration skill for Django database performance work. Start with measured evidence, reproduce the slow path, and route to the smallest optimization that changes the measured bottleneck.

将此作为Django数据库性能优化工作的编排指南。从实证数据入手，复现慢路径，选择能解决已识别性能瓶颈的最小优化方案。

Core Workflow

核心工作流

Capture the symptom.
- Identify the exact endpoint, command, task, report, or queryset.
- Record current wall-clock time, query count, slow SQL, database backend, data volume, and Django version.
- Keep the original request parameters or fixture that reproduces the issue.
Profile before changing code.
- Use APM traces, database slow-query logs, Django Debug Toolbar,
```
connection.queries
```
  , or targeted logging.
- If the expensive query is known, use
```
QuerySet.explain()
```
  or database
```
EXPLAIN
```
  .
- For API endpoints, profile serializers and permission checks as well as querysets.
Classify the dominant problem.
- Many repeated similar queries: use
```
django-orm-query-optimization
```
  .
- One or two expensive SQL statements: use
```
django-query-plan-reading
```
  and
```
django-index-design
```
  .
- Large memory use or long loops over querysets: use
```
django-queryset-batch-processing
```
  .
- Python loops computing counts, totals, flags, or latest related rows: use
```
django-db-side-computation
```
  .
- Slow aggregate/report query that is acceptable when stale: use
```
django-materialized-views
```
  .
- Slow or inconsistent list pages: use
```
django-pagination-performance
```
  .
- Unclear evidence: use
```
django-query-profiling
```
  .
Apply one change at a time.
- Prefer the narrowest change with a clear expected effect.
- Avoid adding indexes, prefetches, or materialized views speculatively.
- Confirm that the optimization helps real production-like data, not only tiny fixtures.
Verify and document the result.
- Re-run the same request or command with the same parameters.
- Compare query count, total DB time, wall-clock time, memory, and query plan.
- Keep before/after evidence in the PR or final report.

See diagnostic-flow.md for routing checklists, common symptoms, and before/after evidence templates.

记录症状。
- 确定具体的端点、命令、任务、报表或查询集。
- 记录当前的实际耗时、查询次数、慢SQL、数据库后端、数据量和Django版本。
- 保留能复现问题的原始请求参数或测试数据。
在修改代码前进行性能分析。
- 使用APM追踪、数据库慢查询日志、Django Debug Toolbar、
```
connection.queries
```
  或针对性日志。
- 如果已知耗时较高的查询，使用
```
QuerySet.explain()
```
  或数据库
```
EXPLAIN
```
  命令。
- 对于API端点，除了查询集，还要分析序列化器和权限检查的性能。
确定主要问题类型。
- 存在大量重复的相似查询：使用
```
django-orm-query-optimization
```
  技能。
- 存在一两个耗时极高的SQL语句：使用
```
django-query-plan-reading
```
  和
```
django-index-design
```
  技能。
- 查询集导致内存占用过高或循环耗时过长：使用
```
django-queryset-batch-processing
```
  技能。
- Python循环中计算计数、总计、标记或最新关联行：使用
```
django-db-side-computation
```
  技能。
- 聚合/报表查询较慢，但允许数据存在一定延迟：使用
```
django-materialized-views
```
  技能。
- 列表页面加载缓慢或表现不一致：使用
```
django-pagination-performance
```
  技能。
- 实证不明确：使用
```
django-query-profiling
```
  技能。
每次只应用一项修改。
- 优先选择范围最窄、预期效果明确的修改。
- 避免随意添加索引、预取或物化视图。
- 确认优化对类生产环境数据有效，而非仅对小型测试数据有效。
验证并记录结果。
- 使用相同参数重新运行相同的请求或命令。
- 对比查询次数、数据库总耗时、实际耗时、内存占用和查询计划。
- 在PR或最终报告中保留优化前后的实证数据。

查看diagnostic-flow.md获取路由检查清单、常见症状以及优化前后实证模板。

Modern Django Notes

现代Django注意事项

Prefer
```
QuerySet.explain()
```
for ORM query plans before dropping to raw
```
EXPLAIN
```
.
Prefer native Django/PostgreSQL migration operations such as
```
AddIndexConcurrently
```
over hand-written concurrent index SQL when they fit.
Prefer ORM expressions,
```
Subquery
```
,
```
Exists
```
,
```
Window
```
,
```
GeneratedField
```
, and
```
db_default
```
when they make database work explicit and portable enough.
Treat every backend-specific optimization as conditional on the project database. PostgreSQL patterns do not automatically apply to MySQL, MariaDB, SQLite, or Oracle.

在使用原生
```
EXPLAIN
```
命令之前，优先使用
```
QuerySet.explain()
```
分析ORM查询计划。
当适用时，优先使用Django/PostgreSQL原生迁移操作（如
```
AddIndexConcurrently
```
），而非手写并发索引SQL。
当ORM表达式、
```
Subquery
```
、
```
Exists
```
、
```
Window
```
、
```
GeneratedField
```
和
```
db_default
```
能使数据库操作更明确且具备足够可移植性时，优先使用它们。
所有针对特定数据库后端的优化都需以项目所使用的数据库为前提。PostgreSQL的优化模式并不自动适用于MySQL、MariaDB、SQLite或Oracle。

Verification

验证要求

Show the slow path is still functionally correct.
Show the measured bottleneck improved.
Show the optimization did not create a worse query, stale data bug, write-path regression, or memory spike.

证明慢路径的功能仍然正确。
证明已测量到的性能瓶颈得到改善。
证明优化未导致更差的查询、数据过期bug、写入路径退化或内存峰值。