简介

本指南面向DouPHP项目的数据库查询优化，覆盖慢查询日志开启与分析、EXPLAIN执行计划解读、常见查询优化技巧（避免SELECT *、合理使用JOIN、子查询优化、分页优化）、批量操作优化（INSERT/UPDATE/DELETE），并结合项目中的ORM与数据库连接实现给出落地建议。文档同时提供实际代码位置参考，便于对照现有实现进行优化。

项目结构

DouPHP的数据库访问层由底层连接与链式查询构建器组成，上层通过ORM Builder封装with预加载、聚合与分页等能力；业务模型以scope方式组织筛选条件与排序，统一使用链式API构造SQL。

graph TB
A["应用控制器/服务"] --> B["ORM Builder<br/>core/orm/Builder.php"]
B --> C["底层连接<br/>core/infra/database/Connection.php"]
B --> D["独立API数据库类<br/>_/.api/lib/Database.php"]
E["业务模型Scope<br/>如 Order.php / Aftersale.php / CouponLog.php"] --> B
F["配置与调试开关<br/>config/config.php"] --> C

核心组件

• 数据库连接与链式查询：Connection负责连接、字符集、事务、表名前缀、字段存在性检查、索引判断、原始SQL执行等；支持join/innerJoin/rightJoin、field过滤、order/limit/offset、where/whereIn/whereRaw等。
• ORM Builder：在Connection之上提供with预加载、全局scope、afterHydrate回调、聚合方法白名单、prefetch等高级能力，屏蔽底层SQL细节。
• 业务模型Scope：将常用筛选、排序逻辑封装为可复用的scope，例如时间范围筛选、默认排序、关联预加载等。
• 配置与调试：配置文件定义数据库连接参数与调试开关，影响错误输出与调试信息。

架构总览

下图展示从业务调用到数据库执行的完整链路，以及关键优化点所在：

sequenceDiagram
participant App as "应用层"
participant Model as "业务模型Scope"
participant ORM as "ORM Builder"
participant Conn as "数据库连接"
participant DB as "MySQL"
App->>Model : 调用列表/详情接口
Model->>ORM : 组合where/order/limit/with
ORM->>Conn : 构建并执行SQL
Conn->>DB : 发送SQL
DB-->>Conn : 返回结果集
Conn-->>ORM : 结果集
ORM-->>Model : 水合/预加载
Model-->>App : 返回数据

详细组件分析

慢查询日志开启与分析

• 阈值设置
  • 建议在数据库层面启用慢查询日志，并通过慢查询阈值控制记录粒度。可在连接建立后或会话级设置相关变量，以便捕获耗时较长的查询。
  • 结合项目中的连接初始化流程，可在连接成功后设置必要的会话模式与日志相关参数，确保后续查询被正确记录。
• 日志文件分析
  • 定期导出慢查询日志，按查询类型、表名、用户、时间段进行聚合统计，识别高频慢查询。
  • 对重复出现的慢查询优先优化，关注全表扫描、临时表、文件排序等指标。
• 工具与方法
  • 使用数据库提供的慢查询日志解析工具或第三方工具，生成可视化报告，辅助定位瓶颈。
  • 结合项目中的调试开关，在开发环境开启更详细的SQL日志，便于快速验证优化效果。

EXPLAIN命令使用方法

• 执行计划解读
  • 使用EXPLAIN查看查询的执行计划，重点关注type（访问类型）、key（使用的索引）、rows（预估行数）、Extra（是否出现Using filesort/Using temporary）。
  • 当出现Using filesort或Using temporary时，优先考虑索引优化或改写查询。
• 索引使用情况分析
  • 确认WHERE、JOIN、ORDER BY、GROUP BY涉及的列是否具备合适索引。
  • 利用项目中的索引检测能力，验证唯一索引是否存在，必要时添加或调整复合索引。
• 临时表和文件排序识别
  • 若EXPLAIN显示临时表或文件排序，尝试减少SELECT字段、增加索引、避免函数包裹列、避免隐式类型转换。

常见查询优化技巧

• 避免SELECT *
  • 明确指定所需字段，减少网络传输与内存占用，提升缓存命中率。
  • 项目中的字段过滤机制支持安全过滤与别名处理，建议使用field方法限定字段。
• 合理使用JOIN
  • 优先INNER JOIN，避免不必要的LEFT/RIGHT JOIN；确保JOIN条件有索引支撑。
  • 使用with预加载替代N+1查询，减少多次往返。
• 子查询优化
  • 尽量将子查询改写为JOIN或临时表；避免在WHERE中使用复杂子查询导致全表扫描。
• 分页查询优化
  • 使用基于主键或有序索引的分页，避免大偏移量导致的性能下降。
  • 结合limit/offset与scope默认排序，保证分页稳定性。

批量操作优化

• INSERT批量插入
  • 合并多条INSERT为单次多值插入，减少事务开销与网络往返。
  • 注意批次大小，避免单条过大导致超时或锁竞争。
• UPDATE批量更新
  • 使用CASE WHEN或临时表关联进行批量更新，减少循环更新次数。
  • 确保更新条件有索引，避免全表扫描。
• DELETE批量删除
  • 使用IN或范围条件批量删除，避免逐条删除。
  • 对于大表删除，考虑分批删除与归档策略。

实际代码示例与优化前后对比

• 时间范围筛选
  • 前：未使用索引的时间函数包裹列，导致全表扫描。
  • 后：直接使用created_at范围比较，命中索引，提升查询速度。
  • 参考路径：Order.php:191-209、Aftersale.php:64-89
• 关联预加载
  • 前：N+1查询，多次往返数据库。
  • 后：使用with预加载，一次查询获取关联数据，减少IO。
  • 参考路径：Order.php:211-220
• 批量删除
  • 前：逐条删除，事务频繁提交。
  • 后：使用IN批量删除，减少事务开销。
  • 参考路径：CouponLog.php:104-115

依赖关系分析

• 组件耦合
  • ORM Builder依赖底层Connection，业务模型通过scope组合查询条件，形成松耦合的查询构建体系。
• 外部依赖
  • 数据库驱动（mysqli）与扩展可用性直接影响连接与查询执行。
  • 配置项决定字符集、调试模式等运行时行为。
• 潜在循环依赖
  • 当前架构中未发现明显循环依赖，但需注意with预加载与关联关系的复杂度管理。

graph LR
Builder["ORM Builder"] --> Connection["数据库连接"]
Model["业务模型Scope"] --> Builder
Config["配置与调试"] --> Connection
API_DB["独立API数据库类"] --> Connection

性能考量

• 索引设计
  • 针对高频查询的WHERE、JOIN、ORDER BY、GROUP BY列建立合适索引，避免过多索引影响写入性能。
  • 使用复合索引覆盖常见查询场景，减少回表。
• 查询改写
  • 避免在列上使用函数或表达式，防止索引失效。
  • 使用EXPLAIN验证执行计划，确保走索引扫描而非全表扫描。
• 分页与大表
  • 使用基于主键或有序索引的分页，避免大偏移量。
  • 对历史数据进行归档，减少主表体积。
• 批量操作
  • 合并INSERT/UPDATE/DELETE，减少事务与网络开销。
  • 合理设置批次大小，避免锁竞争与超时。

故障排查指南

• 连接问题
  • 检查数据库扩展是否启用，主机地址与端口是否正确，字符集设置是否兼容。
  • 参考连接建立流程，确认sql_mode与数据库选择是否成功。
• 查询异常
  • 使用调试开关获取最后执行的SQL与绑定参数，定位语法或参数问题。
  • 结合EXPLAIN分析执行计划，识别全表扫描、临时表、文件排序等问题。
• 事务问题
  • 检查beginTransaction/commit/rollback的使用，确保嵌套事务正确使用savepoint。
  • 遇到异常及时回滚，避免部分提交导致数据不一致。

结论

通过启用慢查询日志、使用EXPLAIN分析执行计划、优化索引与查询写法、合理使用预加载与批量操作，可以显著提升DouPHP项目的数据库查询性能。建议在生产环境持续监控慢查询，结合项目中的ORM与连接能力，逐步优化高频热点查询，确保系统稳定高效运行。

附录

• 时间字段统一与索引重建
  • 项目提供了时间字段统一与索引重建的工具脚本，可在升级过程中自动迁移旧时间字段并重建新索引，提升查询效率。
  • 参考路径：generate_sql.php:284-323、generate_sql.php:399-435