跳到主要内容

索引设计策略

引用文件

本文档引用的文件

目录​

  1. 简介
  2. 项目结构
  3. 核心组件
  4. 架构概览
  5. 详细组件分析
  6. 依赖分析
  7. 性能考虑
  8. 故障排除指南
  9. 结论
  10. 附录

简介​

本文件旨在为Yudao Cloud项目的数据库索引设计提供全面的策略指导。通过对现有数据库结构、查询模式和性能监控实践的深入分析，总结出一套适用于高并发场景下的索引设计原则和实施方案。

Yudao Cloud是一个基于Spring Boot和MyBatis的企业级应用框架，采用多模块架构设计。在数据库层面，项目包含了丰富的业务表结构，如用户管理、设备管理、任务调度等模块，这些都对数据库性能提出了较高要求。

项目结构​

项目采用典型的分层架构，数据库相关的核心文件主要分布在以下位置：

Mermaid Diagram Code:

graph TB
subgraph "数据库层"
SQLFiles[SQL文件]
Schema[数据库架构]
Indexes[索引设计]
end
subgraph "业务模块"
Device[设备管理模块]
System[系统管理模块]
Task[任务管理模块]
Infra[基础设施模块]
end
subgraph "监控分析"
Druid[Druid监控]
Performance[性能分析]
Metrics[监控指标]
end
SQLFiles --> Schema
Schema --> Indexes
Device --> Infra
System --> Infra
Task --> Infra
Druid --> Performance
Performance --> Metrics

图表来源

章节来源

核心组件​

主要数据库表结构​

项目中包含多个关键业务表，每个表都有其特定的查询模式和性能需求：

系统管理表​

  • system_dept: 部门管理表，包含层级关系查询需求
  • system_dict_data: 字典数据表，支持多维度查询和过滤

基础设施表​

  • infra_api_access_log: API访问日志表，支持时间范围查询和用户过滤
  • infra_job: 定时任务表，支持状态管理和调度查询

设备管理表​

  • app_install_device: 设备安装应用记录，支持设备维度的统计分析
  • app_install_package: 应用包安装记录，支持包名和版本查询

章节来源

架构概览​

Mermaid Diagram Code:

graph LR
subgraph "应用层"
Web[Web应用]
API[API网关]
Task[任务调度]
end
subgraph "业务逻辑层"
DeviceBiz[设备业务]
SystemBiz[系统业务]
TaskBiz[任务业务]
end
subgraph "数据访问层"
MyBatis[MyBatis映射]
Druid[Druid监控]
end
subgraph "数据存储层"
MySQL[MySQL数据库]
Partition[分区表]
Index[索引优化]
end
Web --> DeviceBiz
API --> SystemBiz
Task --> TaskBiz
DeviceBiz --> MyBatis
SystemBiz --> MyBatis
TaskBiz --> MyBatis
MyBatis --> Druid
Druid --> MySQL
MySQL --> Partition
Partition --> Index

图表来源

详细组件分析​

设备查询场景索引设计​

设备活跃度分析表​

针对设备活跃度分析场景，项目采用了分区表设计来优化查询性能：

Mermaid Diagram Code:

erDiagram
APP_INSTALL_DEVICE {
bigint id PK
string device_id
string package_name
datetime install_time
datetime uninstall_time
int status
datetime create_time
datetime update_time
}
APP_INSTALL_PACKAGE {
bigint id PK
string device_id
string package_name
string version_code
datetime install_time
datetime uninstall_time
int status
}
DEVICE_MODEL_ACTIVITY_DETAIL {
bigint id PK
string device_model
date statis_date
int device_count
int new_active_count
int dead_count
int revive_count
}

图表来源

索引设计策略​

对于设备查询场景，建议采用以下索引策略：

  1. 复合索引设计：

    • (device_id, package_name) - 支持设备维度的应用查询
    • (package_name, version_code) - 支持应用包维度的版本查询
    • (install_time, device_id) - 支持时间范围内的设备查询

  2. 分区表优化：

    • 按设备ID进行哈希分区，确保相同设备的数据分布均匀
    • 支持分区裁剪，减少扫描范围

章节来源

用户认证场景索引设计​

系统用户表​

针对用户认证场景，系统表结构提供了基础的认证支持：

Mermaid Diagram Code:

classDiagram
class SystemDept {
+bigint id
+string name
+bigint parent_id
+int sort
+bigint leader_user_id
+string phone
+string email
+tinyint status
+tenant_id
+PRIMARY KEY(id)
}
class SystemDictData {
+bigint id
+int sort
+string label
+string value
+string dict_type
+tinyint status
+tenant_id
}
SystemDept --> SystemDictData : "支持字典查询"

图表来源

认证查询优化​

  1. 用户维度索引：

    • (username, tenant_id) - 支持多租户环境下的用户查询
    • (email, tenant_id) - 支持邮箱认证查询

  2. 权限相关索引：

    • (role_id, tenant_id) - 支持角色权限查询
    • (dept_id, tenant_id) - 支持部门权限查询

章节来源

任务检索场景索引设计​

定时任务表​

任务管理模块提供了完整的任务调度功能：

Mermaid Diagram Code:

sequenceDiagram
participant Client as "客户端"
participant TaskBiz as "任务业务层"
participant TaskDAO as "任务数据访问"
participant MySQL as "MySQL数据库"
Client->>TaskBiz : 查询任务列表
TaskBiz->>TaskDAO : 构建查询条件
TaskDAO->>MySQL : 执行带索引的查询
MySQL-->>TaskDAO : 返回查询结果
TaskDAO-->>TaskBiz : 任务数据
TaskBiz-->>Client : 任务列表

图表来源

任务查询优化策略​

  1. 状态查询索引：

    • (status, create_time) - 支持状态筛选和时间排序
    • (handler_name, status) - 支持处理器类型的任务查询

  2. 时间范围查询索引：

    • (create_time, handler_name) - 支持时间范围内的任务查询

章节来源

依赖分析​

查询性能监控体系​

项目建立了完善的查询性能监控体系，通过Druid监控实现对数据库查询的实时监控：

Mermaid Diagram Code:

flowchart TD
subgraph "监控数据收集"
SQLMonitor[SQL执行监控]
PerformanceMetrics[性能指标收集]
SlowSQLDetection[慢SQL检测]
end
subgraph "数据分析处理"
DataAggregation[数据聚合分析]
TrendAnalysis[趋势分析]
Alerting[告警机制]
end
subgraph "可视化展示"
Dashboard[监控仪表板]
Reports[性能报告]
Alerts[告警通知]
end
SQLMonitor --> DataAggregation
PerformanceMetrics --> DataAggregation
SlowSQLDetection --> DataAggregation
DataAggregation --> TrendAnalysis
TrendAnalysis --> Alerting
Alerting --> Dashboard
TrendAnalysis --> Reports
Alerting --> Alerts

图表来源

数据权限过滤机制​

系统实现了基于租户的数据权限控制，确保查询的安全性和准确性：

Mermaid Diagram Code:

sequenceDiagram
participant User as "用户请求"
participant Permission as "权限拦截器"
participant SQLBuilder as "SQL构建器"
participant DB as "数据库"
User->>Permission : 发起查询请求
Permission->>Permission : 校验用户权限
Permission->>SQLBuilder : 添加租户过滤条件
SQLBuilder->>DB : 执行带权限过滤的SQL
DB-->>SQLBuilder : 返回查询结果
SQLBuilder-->>Permission : 结果集
Permission-->>User : 返回安全的查询结果

图表来源

章节来源

性能考虑​

索引选择原则​

基于项目实践，总结出以下索引选择原则：

1. 查询频率优先级​

  • 高频查询：建立复合索引，支持多条件查询
  • 中频查询：建立单列索引，优化单一条件查询
  • 低频查询：谨慎建立索引，避免影响写入性能

2. 选择性评估​

  • 高选择性字段：优先建立索引，如用户ID、设备ID
  • 低选择性字段：考虑组合索引，避免索引失效
  • 常用于过滤的字段：优先建立索引，提升查询效率

3. 区分度考量​

  • 唯一性约束：使用唯一索引保证数据完整性
  • 重复值较多：考虑前缀索引或部分索引
  • 字符串类型：注意字符集和排序规则的影响

热点数据优化策略​

1. 缓存层优化​

  • 热点数据缓存：将频繁访问的数据缓存到Redis
  • 读写分离：实现读写分离，减轻主库压力
  • 批量查询优化：支持批量查询，减少网络开销

2. 分区表策略​

  • 按时间分区：将历史数据分区，提升查询性能
  • 按业务分区：根据业务特征进行分区设计
  • 动态分区管理：实现分区的自动创建和删除

维护成本控制​

1. 索引维护策略​

  • 定期重建：定期重建碎片化的索引
  • 统计信息更新：及时更新表的统计信息
  • 索引监控：监控索引的使用效率

2. 成本效益分析​

  • 写入性能影响：评估索引对写入性能的影响
  • 存储空间消耗：控制索引占用的存储空间
  • 维护复杂度：平衡索引数量和维护复杂度

故障排除指南​

常见索引问题诊断​

1. 查询性能问题​

-- 检查慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看当前连接状态
SHOW PROCESSLIST;

-- 分析查询执行计划
EXPLAIN SELECT * FROM table WHERE condition;

2. 索引使用情况检查​

-- 查看表的索引信息
SHOW INDEX FROM table_name;

-- 分析索引使用率
SELECT 
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM information_schema.table_statistics 
WHERE table_schema = 'database_name';

3. 性能监控指标​

  • QPS（每秒查询数）：衡量系统的查询处理能力
  • 平均响应时间：反映查询的平均耗时
  • 错误率：监控查询的错误比例
  • 慢SQL数量：识别性能瓶颈的SQL语句

章节来源

索引优化建议​

1. 索引设计优化​

  • 避免冗余索引：删除重复或很少使用的索引
  • 合理设计复合索引：按照查询频率和选择性设计索引顺序
  • 考虑覆盖索引：减少回表查询的次数

2. 查询语句优化​

  • 使用合适的WHERE条件：确保WHERE条件能够利用索引
  • **避免SELECT ***：只查询需要的字段
  • 合理使用LIMIT：限制返回结果的数量

3. 数据库配置优化​

  • 缓冲池大小：根据内存大小合理配置缓冲池
  • 连接数限制：控制最大连接数，避免资源耗尽
  • 日志配置：合理配置二进制日志和慢查询日志

结论​

通过对Yudao Cloud项目数据库索引设计的深入分析，可以得出以下结论：

  1. 索引设计需要从业务场景出发：不同业务场景有不同的查询模式，需要针对性地设计索引策略。

  2. 性能监控是索引优化的基础：通过Druid监控等手段，可以实时了解数据库的性能状况，为索引优化提供数据支撑。

  3. 平衡查询性能和维护成本：索引虽然能提升查询性能，但也会增加维护成本，需要在两者之间找到平衡点。

  4. 持续优化是必要的：随着业务的发展和数据量的增长，索引策略也需要不断调整和优化。

建议在实际项目中，结合具体的业务场景和数据特点，制定适合的索引设计策略，并建立完善的监控和优化机制。

附录​

索引设计最佳实践清单​

设计阶段​

  • 分析业务查询模式和数据访问特征
  • 评估数据分布和选择性
  • 制定索引设计规范和命名约定
  • 评估索引对写入性能的影响

实施阶段​

  • 优先实现高频查询的索引
  • 使用分区表优化大数据量查询
  • 建立索引监控和告警机制
  • 制定索引维护和优化计划

运维阶段​

  • 定期分析索引使用情况
  • 监控查询性能指标变化
  • 及时调整和优化索引策略
  • 建立索引变更的审批流程

性能测试方法​

1. 基准测试​

  • 测试环境搭建：准备与生产环境相似的测试环境
  • 数据量准备：使用生产环境的数据量进行测试
  • 测试场景设计：覆盖主要的业务查询场景
  • 性能指标收集：记录查询响应时间、吞吐量等指标

2. 压力测试​

  • 并发测试：模拟高并发场景下的查询性能
  • 长时间稳定性测试：验证系统在长时间运行下的稳定性
  • 资源使用监控：监控CPU、内存、磁盘等资源使用情况

3. 回归测试​

  • 变更影响评估：评估索引变更对系统性能的影响
  • 兼容性测试：确保索引变更不影响现有功能
  • 性能回归测试：验证性能优化的效果
用户文档
AI 助手
Agent 列表
请选择一个 Agent 开始对话
AI 问答