Back to Details

optimize-database

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Optimization

数据库优化

Optimize any database technology without assuming MongoDB, PostgreSQL, MySQL, an ORM, or a particular hosting provider. Inspect evidence before making recommendations.
无需预设使用MongoDB、PostgreSQL、MySQL、ORM或特定托管服务商，即可对任意数据库技术进行优化。提出建议前需先核查相关依据。

First: detect the data layer

第一步：检测数据层

Search the repository for database drivers, ORM/query-builder packages, connection configuration, schemas/models, migrations, indexes, constraints, repositories, raw queries, transactions, seeds, caches, search systems, background jobs, analytics workloads, tests, deployment configuration, and monitoring.
Identify:
  • database engines, versions, extensions, topology, hosting, and environment;
  • drivers, ORM/query layer, versions, pooling, retry, and timeout configuration;
  • schemas, relationships, constraints, indexes, migrations, and ownership;
  • high-traffic read/write paths, data volume/growth, concurrency, latency, and throughput;
  • replicas, partitions/shards, caches, queues, backup/restore, retention, and observability.
If a data layer exists, explain its current design, healthy choices, measured or evidenced problems, and an ordered safe improvement plan. If none exists, design the smallest suitable persistence approach from the product's consistency, query, scale, security, and operational requirements.
Do not upgrade a database, driver, or ORM silently. Check release notes, compatibility, migration requirements, and rollback before changing versions.
在代码仓库中搜索数据库驱动、ORM/查询构建器包、连接配置、模式/模型、迁移脚本、索引、约束、仓储、原生查询、事务、种子数据、缓存、搜索系统、后台任务、分析工作负载、测试用例、部署配置及监控相关内容。
识别以下内容：
  • 数据库引擎、版本、扩展、拓扑结构、托管方式及运行环境；
  • 驱动、ORM/查询层、版本、连接池、重试及超时配置；
  • 数据模式、关系、约束、索引、迁移及归属；
  • 高流量读写路径、数据量/增长情况、并发量、延迟及吞吐量；
  • 副本、分区/分片、缓存、队列、备份/恢复、数据保留及可观测性。
若存在数据层，需说明其当前设计、合理选型、已验证或可预见的问题，并提供有序的安全改进方案。若不存在数据层，则根据产品的一致性、查询、规模、安全及运维需求，设计最小化的合适持久化方案。
不得静默升级数据库、驱动或ORM。变更版本前需查看发布说明、兼容性、迁移要求及回滚方案。

Mandatory full audit

强制性全面审计

Check every applicable area, even when the request mentions only one slow query:
  1. Correctness and integrity — types, nullability, uniqueness, foreign keys/references, validation, invariants, transactions, isolation, idempotency, and consistency.
  2. Indexes — missing, unused, duplicate, overlapping, invalid, low-selectivity, oversized, write-heavy, foreign-key, compound order, covering, partial, expression, text, geospatial, TTL, and unique indexes.
  3. Queries and plans — scans, selectivity, cardinality estimates, join strategy, sort/group operations, temporary work, N+1 calls, repeated queries, over-fetching, application-side filtering, and plan regressions.
  4. Access patterns — real filters, sorts, joins/population, aggregations, writes, hot keys/rows, batch operations, and read/write amplification.
  5. Pagination and limits — stable ordering, maximum limits, cursor/keyset strategy, large offsets, unbounded exports, and count-query cost.
  6. Schema and modeling — normalization/denormalization, relationship ownership, document growth, row width, large fields, enums, temporal/history data, and multi-tenant boundaries.
  7. Concurrency — transactions, isolation level, locks, deadlocks, optimistic/pessimistic control, race conditions, long-running work, and connection starvation.
  8. Connections — pool sizing, leaks, timeouts, retries, backoff, prepared statements, proxy/serverless behavior, and graceful shutdown.
  9. Caching — need, keys, TTL, invalidation, consistency, stampede protection, negative caching, memory limits, and failure fallback.
  10. Migrations — backward compatibility, locks, table rewrites, online index creation, backfills, expand/contract rollout, validation, rollback, and deployment order.
  11. Operations — slow-query logs, metrics, tracing, alerts, vacuum/analyze or equivalent maintenance, statistics, fragmentation/bloat, storage, and cost.
  12. Reliability — backups, restore tests, point-in-time recovery, replication, failover, disaster recovery, RPO/RTO, and reconciliation.
  13. Growth — retention, archival, partitioning/sharding, replicas, materialized views, queues, warehouse/search separation, capacity forecasts, and scaling thresholds.
  14. Security and privacy — least privilege, credential storage/rotation, encryption, network access, tenant isolation, sensitive fields, audit logs, deletion/retention, and injection risks.
即使仅提及慢查询问题，也需检查所有适用领域：
  1. 正确性与完整性 —— 数据类型、可空性、唯一性、外键/引用、验证规则、不变量、事务、隔离级别、幂等性及一致性。
  2. 索引 —— 缺失索引、未使用索引、重复索引、重叠索引、无效索引、低选择性索引、过大索引、写密集型索引、外键索引、复合顺序索引、覆盖索引、部分索引、表达式索引、文本索引、地理空间索引、TTL索引及唯一索引。
  3. 查询与执行计划 —— 扫描操作、选择性、基数估算、连接策略、排序/分组操作、临时工作、N+1调用、重复查询、过度获取数据、应用端过滤及执行计划退化。
  4. 访问模式 —— 实际过滤条件、排序方式、连接/填充操作、聚合运算、写操作、热点键/行、批量操作及读写放大。
  5. 分页与限制 —— 稳定排序、最大限制值、游标/键集策略、大偏移量、无界导出及计数查询成本。
  6. 模式与建模 —— 规范化/反规范化、关系归属、文档增长、行宽度、大字段、枚举、时间/历史数据及多租户边界。
  7. 并发控制 —— 事务、隔离级别、锁、死锁、乐观/悲观控制、竞态条件、长时运行任务及连接耗尽。
  8. 连接管理 —— 连接池大小、连接泄漏、超时设置、重试机制、退避策略、预编译语句、代理/无服务行为及优雅关闭。
  9. 缓存策略 —— 缓存必要性、缓存键、TTL、失效机制、一致性、缓存击穿防护、负缓存、内存限制及故障降级。
  10. 迁移管理 —— 向后兼容性、锁、表重写、在线索引创建、数据回填、扩容/缩容发布流程、验证、回滚及部署顺序。
  11. 运维监控 —— 慢查询日志、指标、追踪、告警、Vacuum/Analyze或等效维护操作、统计信息、碎片/膨胀、存储及成本。
  12. 可靠性保障 —— 备份、恢复测试、时点恢复、复制、故障转移、灾难恢复、RPO/RTO及一致性校验。
  13. 可扩展性规划 —— 数据保留、归档、分区/分片、副本、物化视图、队列、仓库/搜索分离、容量预测及扩容阈值。
  14. 安全与隐私 —— 最小权限原则、凭证存储/轮换、加密、网络访问控制、租户隔离、敏感字段、审计日志、删除/保留规则及注入风险。

Workflow

工作流程

  1. Establish a baseline using timings, throughput, resource use, query plans, slow-query logs, traces, or representative code and data.
  2. Trace the complete request/job to every database and cache operation.
  3. Rank bottlenecks by evidence and user impact instead of applying generic tuning.
  4. Match indexes to real equality/range filters, joins, sort order, uniqueness, selectivity, and projection while accounting for writes and storage.
  5. Fix query shape and data access before adding infrastructure. Select only needed fields, batch work, remove N+1 calls, enforce limits, and use stable cursor/keyset pagination where appropriate.
  6. Check correctness, transactions, concurrency, and tenant/security boundaries before performance changes.
  7. Add caching, replicas, partitions, search engines, queues, or sharding only when simpler fixes and measured requirements justify their operational cost.
  8. Treat every schema, constraint, index, or data rewrite as a migration with staging evidence, rollout monitoring, and rollback/recovery.
  9. Re-run the original measurements and report before/after evidence, tradeoffs, and remaining capacity limits.
  1. 利用计时数据、吞吐量、资源使用情况、查询计划、慢查询日志、追踪信息或代表性代码与数据建立基准。
  2. 追踪完整请求/任务对应的所有数据库及缓存操作。
  3. 根据依据及用户影响对瓶颈进行排序，而非套用通用调优方案。
  4. 根据实际等值/范围过滤条件、连接、排序顺序、唯一性、选择性及投影需求匹配索引，同时考虑写操作及存储成本。
  5. 在添加基础设施前先优化查询形态及数据访问方式。仅选择所需字段、批量处理工作、消除N+1调用、强制执行限制，并在合适场景下使用稳定的游标/键集分页。
  6. 在进行性能变更前，检查正确性、事务、并发及租户/安全边界。
  7. 仅当简单优化方案及已验证的需求证明其运维成本合理时，才添加缓存、副本、分区、搜索引擎、队列或分片。
  8. 将所有模式、约束、索引或数据改写视为迁移操作，需具备 staging 环境验证、发布监控及回滚/恢复方案。
  9. 重新运行初始测量并报告优化前后的依据、权衡点及剩余容量限制。

Safety

安全注意事项

Never run destructive or irreversible database operations without explicit confirmation. This includes dropping data or indexes, truncation, unsafe deletes, column removal or rename, destructive type changes, migration resets, repartitioning, and blind production-cache flushes. Confirm the exact environment and target, verify a usable backup/restore path, test on representative staging data, assess locks and disk headroom, and prepare rollback steps first.
未经明确确认，不得执行破坏性或不可逆的数据库操作。此类操作包括删除数据或索引、截断表、不安全删除、列移除或重命名、破坏性类型变更、迁移重置、重新分区及盲目清空生产缓存。需先确认具体环境及目标、验证可用的备份/恢复路径、在代表性 staging 数据上测试、评估锁及磁盘空间，并准备回滚步骤。

Detailed guidance

详细指南

Read references/database-guide.md for database-specific index and query patterns, ORM advice, pooling, caching, migration and backup checklists, large-scale strategies, or the detailed review format. Search by heading and load only the sections relevant to the detected stack.
阅读references/database-guide.md获取数据库特定的索引与查询模式、ORM建议、连接池、缓存、迁移及备份检查清单、大规模扩展策略或详细评审格式。按标题搜索并仅加载与检测到的技术栈相关的章节。

Output

输出要求

Lead with whether an existing data layer was found and summarize its engines, versions, topology, and query tools. Report findings by severity and measured or likely impact. For every recommendation, state evidence, expected benefit, tradeoff, safe rollout, rollback, and verification method. Include an index review even when no new index is recommended. Separate correctness/data-loss risks, security risks, performance bottlenecks, reliability gaps, capacity concerns, and speculative tuning.
首先说明是否检测到现有数据层，并概述其引擎、版本、拓扑结构及查询工具。按严重性及已验证或潜在影响报告发现的问题。针对每一项建议，需说明依据、预期收益、权衡点、安全发布流程、回滚方案及验证方法。即使未建议新增索引，也需包含索引评审内容。区分正确性/数据丢失风险、安全风险、性能瓶颈、可靠性缺口、容量问题及推测性调优内容。