简介
本指南面向DouPHP项目的数据库索引优化，围绕主键索引设计、联合索引最佳实践、全文检索与中文分词、索引监控与维护等主题展开。内容基于仓库中的DDL、升级脚本与ORM/查询实现进行归纳，并给出可操作的SQL示例与EXPLAIN解读方法，帮助在现有代码基础上持续优化查询性能。
项目结构
- 数据定义集中在“系统表结构.sql”与各模块的“storage/backup/*.sql”中，体现各表的主键、唯一键与普通索引。
- 索引变更通过各模块的“_update/data/upgrade.php”增量脚本维护，确保生产环境索引一致性与幂等性。
- ORM基类“core/orm/Model.php”统一了主键约定（默认id），便于跨模块一致性。
- 业务查询多通过DB门面或ORM构建器生成SQL，部分复杂查询在服务层组装（如文档搜索）。
graph TB
A["系统表结构<br/>_'\doc\\开发手册\\系统表结构.sql"] --> B["模块DDL备份<br/>_'\module\\*\\storage\\backup\\*.sql"]
C["升级脚本<br/>_'\module\\*\\_update\\data\\upgrade.php"] --> B
D["ORM基类<br/>core\\orm\\Model.php"] --> E["业务查询与服务<br/>core\\service\\* / admin\\model\\*"]
E --> F["控制器/路由<br/>admin\\controller\\* / front\\controller\\*"]核心组件
- 主键约定：ORM默认主键为id，多数表遵循自增整型主键；部分历史表使用业务ID作为主键（如管理员、分类等）。
- 常见索引模式：
- 单列索引：slug、status、created_at等高频过滤字段。
- 联合索引：operator_type+operator_id、status+时间字段、用户维度统计维度的复合键。
- 唯一索引：slug、业务唯一约束（如配额、每日统计维度）。
- 索引维护：通过upgrade.php以幂等方式添加/校验索引，避免重复创建。
架构总览
下图展示从控制器到服务再到数据库的调用链，以及关键索引在查询路径中的作用点。
sequenceDiagram
participant C as "控制器"
participant S as "服务/模型"
participant DB as "数据库"
C->>S : 构造查询条件含状态/时间/分类等
S->>DB : 执行SELECT可能带JOIN/聚合
DB-->>S : 利用主键/联合索引/覆盖索引返回结果
S-->>C : 返回数据集合详细组件分析
主键索引设计原则与实践
- 自增ID：推荐用于高写入、顺序插入的流水型表（如日志、任务、消息），减少页分裂与B+树碎片。
- 示例：AI使用日志、AI任务、聊天消息等表采用自增主键。
- UUID：适合分布式场景且需要全局唯一、不可预测的场景；但会增大索引体积并降低顺序插入效率，需谨慎评估。
- 业务主键：当存在稳定、短小、唯一的外部标识时可使用（如slug），通常配合唯一索引。
- 建议：
- 所有表保留自增主键id，保证聚簇索引顺序与性能。
- 对外暴露的业务唯一键（如slug）建立UNIQUE索引，避免业务冲突。
联合索引最佳实践
- 字段顺序选择：将区分度高、等值匹配多的字段放前面；范围查询字段尽量靠后。
- 最左前缀匹配：联合索引(a,b,c)可支持a、a+b、a+b+c的查询，但不支持b、b+c单独命中。
- 覆盖索引：当查询仅访问索引列时，可避免回表，显著提升性能。
- 典型实践：
- operator_type + operator_id：按创建者类型与ID筛选记录。
- status + created_at：按状态和时间范围筛选列表。
- stat_date + user_id + chat_id + model_id：多维统计去重与聚合。
flowchart TD
Start(["开始"]) --> Q1{"是否等值过滤?"}
Q1 -- 是 --> P1["将等值字段放在联合索引前列"]
Q1 -- 否 --> Q2{"是否存在范围过滤?"}
Q2 -- 是 --> P2["范围字段尽量后置，避免破坏前缀匹配"]
Q2 -- 否 --> P3["按选择性排序放置字段"]
P1 --> End(["结束"])
P2 --> End
P3 --> End全文索引与中文搜索优化
- 现状：仓库未显式创建FULLTEXT索引；文档搜索通过应用层字符串匹配实现（先轻量查询再内存过滤）。
- 建议：
- 对大文本字段（title/content）启用FULLTEXT，并配置中文分词器（如ngram或第三方搜索引擎）。
- 对高频短词搜索，优先使用前缀索引或倒排索引方案。
- 结合缓存与分页，避免全表扫描与大结果集。
- 当前实现参考：
- 文档服务在获取候选集后进行关键词匹配，属于轻量先行、内存过滤的模式。
索引监控与维护方案
- 使用率分析：
- 通过INFORMATION_SCHEMA.STATISTICS或性能_schema.index_statistics查看索引使用情况。
- 关注长期无使用的索引，评估删除。
- 冗余识别：
- 若存在(a)与(a,b)同时存在，且查询多为a等值，则(a)可能冗余。
- 若(b,c)与(a,b,c)共存，需结合查询分布判断。
- 重建策略：
- 定期OPTIMIZE TABLE或重建索引以减少碎片。
- 批量导入/更新后重建索引，提升后续查询性能。
- 升级脚本规范：
- 使用幂等检查（如查询STATISTICS）后再ADD INDEX，避免重复创建。
SQL示例与EXPLAIN解读
- 示例1：按状态与时间范围查询售后记录
- 目标：命中idx_status_expired(status, expired_at)。
- EXPLAIN关注：type=ref/range，rows较小，Extra包含Using index condition。
- 示例2：按创建者类型与ID查询商品
- 目标：命中idx_operator(operator_type, operator_id)。
- EXPLAIN关注：type=ref，rows接近过滤比例。
- 示例3：按slug精确查找
- 目标：命中idx_slug(slug)。
- EXPLAIN关注：type=const/ref，rows=1。
- 示例4：AI任务按状态与时间分页
- 目标：命中idx_status_created(status, created_at)。
- EXPLAIN关注：range扫描，limit/pagination合理。
说明：以上示例为通用写法，实际字段名与索引名以仓库DDL为准。
依赖关系分析
- 模型与表：
- Model基类约定主键id，影响find/whereKey等行为。
- 各模块表结构由DDL与升级脚本共同维护。
- 查询与服务：
- 控制器触发服务层查询，服务层组合条件并执行SQL。
- 复杂搜索在服务层进行预处理，减少对数据库的压力。
classDiagram
class Model {
+string table
+string primary
+query()
+find(id)
+where(...)
}
class AiLog {
+scopeFilterByHasError(...)
+scopeFilterByCreatedAtStart(...)
+scopeFilterByCreatedAtEnd(...)
}
class DocService {
+search(keyword, filters)
}
Model <|-- AiLog : "继承"
DocService --> Model : "使用"性能考量
- 索引成本：每增加一个索引都会带来写放大与存储开销，应基于真实查询热点设计。
- 覆盖索引：尽量让查询只访问索引列，避免回表。
- 范围查询：范围字段后的列无法继续高效使用索引，需拆分或调整索引顺序。
- 字符集与排序规则：统一utf8mb4_unicode_ci，避免隐式转换导致索引失效。
- 分页与限制：合理使用LIMIT/OFFSET，避免深分页。
故障排查指南
- 慢查询定位：
- 开启慢查询日志，结合EXPLAIN分析type、key、rows、Extra。
- 关注Full scan、Using filesort、Using temporary等警告。
- 索引失效常见原因：
- 函数/表达式包裹字段、隐式类型转换、LIKE前导通配符。
- 解决步骤：
- 调整查询条件以匹配索引前缀。
- 必要时新增或调整联合索引。
- 对历史数据重建索引并观察效果。
结论
- 保持统一的主键策略（自增id）与稳定的业务唯一键（如slug）是唯一索引。
- 联合索引应围绕高频查询模式设计，遵循最左前缀与范围字段后置原则。
- 全文检索建议引入合适的中文分词器或外部搜索引擎，结合应用层轻量过滤。
- 通过升级脚本幂等维护索引，定期监控使用率与冗余，持续优化。
附录
- 常用索引诊断SQL（概念性）：
- 查看索引使用：SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = DATABASE();
- 查看统计信息：SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE();
- 索引命名规范：
- 主键：PRIMARY KEY (id)
- 唯一键：UNIQUE KEY uk_xxx (field)
- 普通索引：KEY idx_xxx (field1, field2)