索引优化 | DouPHP 开发手册 | 文档中心 | DouPHP企业建站系统

索引优化

简介

本指南面向数据库管理员与后端开发者，围绕DouPHP项目的常见查询模式，提供一套可落地的索引优化方案。内容涵盖：

单列索引、复合索引、全文索引的选择与使用场景
查询性能分析工具（EXPLAIN、慢查询日志）的使用
常见查询模式的优化策略（LIKE、范围查询、排序分组）
索引维护策略（重建、碎片整理、统计信息更新）
监控与调优工具使用方法
实际性能测试案例与优化前后对比方法

项目结构与数据层概览

本项目采用模块化设计，业务逻辑通过控制器/服务/模型分层组织，数据访问统一通过ORM封装的DB构建器进行查询。关键数据定义位于系统表结构脚本中，部分模块在升级脚本中动态添加索引或规范化字段。

graph TB
A["控制器/服务"] --> B["模型(含scope过滤/排序)"]
B --> C["ORM DB构建器<br/>where/order/paginate"]
C --> D["MySQL引擎<br/>InnoDB"]
D --> E["索引与统计信息"]

图表来源

系统表结构.sql:25-110
Aftersale.php:54-100
ListSortOptionBuilder.php:40-92

章节来源

系统表结构.sql:25-110
Aftersale.php:54-100
ListSortOptionBuilder.php:40-92

核心组件与查询模式

列表筛选：大量使用 where + order + paginate 的组合，常见于后台管理列表与前端展示页
模糊搜索：多处使用 LIKE '%keyword%'，对全文检索有潜在需求
时间范围：按 created_at/book_date 等时间字段进行范围筛选
多条件组合：状态、分类、用户ID等多维筛选
排序：默认按 id DESC，或 sort ASC, id DESC 的多键排序

这些模式直接决定了索引设计的方向：优先为高频筛选列建立索引；为复合条件建立复合索引；为排序列建立覆盖索引以减少回表；为全文检索场景评估全文索引或外部搜索引擎。

章节来源

Aftersale.php:54-100
Cases.php:107-122
ChatKnowledge.php:79-107
Order.php:45-99
Book.php:127-168
VoteService.php:137-143
ListSortOptionBuilder.php:40-92

架构总览：从业务到数据库的查询路径

下图展示了典型列表查询从控制器到数据库的执行路径，以及索引在其中发挥的作用。

sequenceDiagram
participant U as "用户"
participant C as "控制器/服务"
participant M as "模型(scope)"
participant Q as "ORM构建器"
participant DB as "MySQL"
participant IDX as "索引"
U->>C : 发起列表请求
C->>M : 调用scope过滤/排序
M->>Q : where + order + paginate
Q->>DB : 生成SQL并执行
DB->>IDX : 选择合适索引扫描
IDX-->>DB : 返回命中行
DB-->>Q : 结果集
Q-->>M : 分页结果
M-->>C : 数据对象
C-->>U : 渲染页面

图表来源

Aftersale.php:54-100
ListSortOptionBuilder.php:40-92
系统表结构.sql:25-110

详细组件分析

售后列表查询（Aftersale）

查询模式：aftersale_sn 模糊匹配、created_at 范围筛选、id 降序排序
索引建议：
- 单列索引：created_at（用于范围筛选）
- 复合索引：(status, created_at) 或 (user_id, created_at)，视具体筛选维度而定
- 模糊列 aftersale_sn：若频繁全前缀/后缀匹配，建议引入全文索引或搜索引擎；否则仅在高基数且选择性高的情况下考虑普通索引
排序优化：order by id DESC 通常走主键，无需额外索引

flowchart TD
Start(["进入售后列表"]) --> F1["应用时间范围筛选<br/>created_at > / <"]
F1 --> F2["可选：aftersale_sn LIKE"]
F2 --> F3["排序：id DESC"]
F3 --> End(["返回分页结果"])

图表来源

Aftersale.php:54-100

章节来源

Aftersale.php:54-100

案例列表查询（Cases）

查询模式：title 模糊匹配、默认排序支持手动排序时 sort ASC, id DESC
索引建议：
- 复合索引：(status, sort, id) 以支撑排序与状态筛选
- title 模糊：如命中率高且频繁，考虑全文索引或外部搜索
注意：当启用手动排序时，需确保复合索引包含排序字段与主键，避免文件排序

flowchart TD
S(["进入案例列表"]) --> K["title LIKE 关键词"]
K --> O{"是否启用手动排序?"}
O -- 是 --> R["排序：sort ASC, id DESC"]
O -- 否 --> D["排序：id DESC"]
R --> P["分页返回"]
D --> P

图表来源

Cases.php:107-122

章节来源

Cases.php:107-122

知识库查询（ChatKnowledge）

查询模式：category_id 精确筛选、title/question 关键字模糊、status 筛选
索引建议：
- 复合索引：(category_id, status) 用于快速定位活跃知识条目
- 全文索引：对 title/question 建立全文索引以提升模糊匹配性能
- 排序：若无显式排序，默认可能按主键或默认排序规则，建议在需要时增加覆盖索引

flowchart TD
S(["进入知识库列表"]) --> C["category_id 筛选"]
C --> ST["status 筛选"]
ST --> KW["title/question LIKE"]
KW --> P["分页返回"]

图表来源

ChatKnowledge.php:79-107

章节来源

ChatKnowledge.php:79-107

订单列表查询（Order）

查询模式：user_id 精确筛选、status 多值合并（待付款/等待确认）、id 降序排序
索引建议：
- 单列索引：user_id
- 复合索引：(status, id) 或 (user_id, status, id) 视查询组合而定
- 注意：OR 条件可能导致索引失效，必要时拆分为 UNION 或使用覆盖索引

flowchart TD
S(["进入订单列表"]) --> U["user_id 筛选"]
U --> ST{"status 是否为待付款/等待确认?"}
ST -- 是 --> OR["OR 条件合并"]
ST -- 否 --> EQ["精确匹配"]
OR --> ORD["排序：id DESC"]
EQ --> ORD
ORD --> P["分页返回"]

图表来源

Order.php:45-99

章节来源

Order.php:45-99

预约列表查询（Book）

查询模式：status 筛选、book_date 范围筛选、id 降序排序
索引建议：
- 复合索引：(status, book_date) 或 (book_date, status) 根据查询选择性决定顺序
- 排序：id DESC 通常走主键

flowchart TD
S(["进入预约列表"]) --> ST["status 筛选"]
ST --> DT["book_date >= / <="]
DT --> ORD["排序：id DESC"]
ORD --> P["分页返回"]

图表来源

Book.php:127-168

章节来源

Book.php:127-168

投票选项列表（VoteService）

查询模式：vote_id 精确筛选、排序 sort ASC, number ASC, id DESC、分页
索引建议：
- 复合索引：(vote_id, sort, number, id) 以完全覆盖排序与筛选，避免文件排序与临时表

flowchart TD
S(["进入投票选项列表"]) --> V["vote_id 筛选"]
V --> O["排序：sort ASC, number ASC, id DESC"]
O --> P["分页返回"]

图表来源

VoteService.php:137-143

章节来源

VoteService.php:137-143

排序构建器（ListSortOptionBuilder）

功能：根据前端传入的 by/sort 参数生成 SQL 排序片段，默认追加 id DESC
索引建议：确保排序字段与 id 的复合索引存在，避免 filesort

flowchart TD
S(["接收 by/sort"]) --> J{"by 是否为有效字段?"}
J -- 是 --> G["拼接字段 + 方向"]
J -- 否 --> D["使用默认排序"]
G --> I["追加 id DESC"]
D --> I
I --> R["返回SQL片段"]

图表来源

ListSortOptionBuilder.php:40-92

章节来源

ListSortOptionBuilder.php:40-92

依赖关系与索引影响分析

表结构与索引现状：
- AI任务表已具备复合索引：(status, created_at)、(provider_id, provider_task_id)、(admin_id, created_at)
- 每日统计表具备唯一联合索引：(stat_date, user_id, chat_id, model_id)
- 分销等级日志表具备 user_id、order_sn 单列索引
- item 模块升级脚本会检查并添加 idx_operator 复合索引
查询与索引匹配度：
- 高选择性单列（如 user_id、status、vote_id）适合单列索引
- 多条件+排序场景适合复合索引，字段顺序应遵循“等值=范围>排序”的原则
- 模糊匹配（LIKE '%...%'）通常不走普通索引，需评估全文索引或搜索引擎

graph LR
Q1["售后列表"] --> I1["created_at 范围"]
Q1 --> I2["aftersale_sn 模糊"]
Q2["案例列表"] --> I3["title 模糊"]
Q2 --> I4["sort, id 排序"]
Q3["知识库"] --> I5["category_id, status"]
Q3 --> I6["title/question 模糊"]
Q4["订单"] --> I7["user_id, status"]
Q5["预约"] --> I8["status, book_date"]
Q6["投票选项"] --> I9["vote_id, sort, number, id"]

图表来源

系统表结构.sql:150-171
系统表结构.sql:663-681
distribution_level_log.sql:5-16
upgrade.php:28-31

章节来源

系统表结构.sql:150-171
系统表结构.sql:663-681
distribution_level_log.sql:5-16
upgrade.php:28-31

性能考虑与调优建议

单列索引
- 适用：高选择性等值查询（如 user_id、status、vote_id）
- 注意：避免过多单列索引导致写入放大
复合索引
- 适用：多条件+排序（如 vote_option 的 (vote_id, sort, number, id)）
- 原则：等值在前，范围次之，排序最后；尽量覆盖查询所需字段
全文索引
- 适用：频繁模糊匹配（title、question、aftersale_sn）
- 注意：中文分词需配置合适的分词器；大数据量下建索引耗时较长
排序优化
- 将排序字段纳入复合索引末尾，避免 filesort
- 默认 id DESC 通常高效，但多键排序需覆盖索引
范围查询
- 时间范围（created_at、book_date）建议单独索引或与等值条件组成复合索引
分页优化
- 大偏移分页建议使用延迟关联或基于游标的方式减少扫描行数
统计信息与碎片
- 定期 ANALYZE TABLE 更新统计信息
- 定期 OPTIMIZE TABLE 或在线重建索引以减少碎片

故障排查指南

使用 EXPLAIN 分析查询计划
- 关注 type（ALL/INDEX/RANGE/REF/const）、key（使用的索引）、rows（预估行数）、Extra（Using filesort/Using temporary）
- 针对慢查询逐条优化，优先解决 filesort 与全表扫描
慢查询日志
- 开启 slow_query_log，设置 long_query_time 阈值
- 定期导出与分析 top N 慢查询，结合 EXPLAIN 定位问题
索引诊断
- 使用 SHOW INDEX FROM table_name 查看现有索引使用情况
- 使用 INFORMATION_SCHEMA.STATISTICS 检查索引命中率与重复索引
常见问题
- LIKE '%...%' 无法使用普通索引：考虑全文索引或搜索引擎
- OR 条件导致索引失效：尝试拆分查询或使用 UNION ALL
- 多条件范围查询：调整复合索引字段顺序，确保等值在前

章节来源

Aftersale.php:54-100
ChatKnowledge.php:79-107
Order.php:45-99
Book.php:127-168
VoteService.php:137-143

结论

通过对DouPHP项目中常见查询模式的梳理，可以明确索引优化的重点在于：

为高频筛选与排序建立合适的复合索引
对模糊匹配场景评估全文索引或外部搜索引擎
利用EXPLAIN与慢查询日志持续监控与调优
定期维护索引与统计信息，保证查询计划最优

附录：常用SQL与工具清单

EXPLAIN 示例
- EXPLAIN SELECT ... WHERE ... ORDER BY ... LIMIT ...;
慢查询日志
- SET GLOBAL slow_query_log = 'ON';
- SET GLOBAL long_query_time = 1;
索引查看与维护
- SHOW INDEX FROM table_name;
- ANALYZE TABLE table_name;
- OPTIMIZE TABLE table_name;
统计信息查询
- SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table';

添加日期：2026-10-05