hospital_performance/.qoder/repowiki/zh/content/数据库设计/索引与约束设计.md

索引与约束设计

**本文引用的文件** - [models.py](file://backend/app/models/models.py) - [finance.py](file://backend/app/models/finance.py) - [001_initial.py](file://backend/alembic/versions/001_initial.py) - [002_template.py](file://backend/alembic/versions/002_template.py) - [database.py](file://backend/app/core/database.py) - [env.py](file://backend/alembic/env.py) - [init_db.py](file://backend/init_db.py) - [database.md](file://docs/database.md)

目录

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

简介

本文件聚焦于该医院绩效系统的数据库索引与约束设计，系统性梳理各表的索引策略（单列、复合、唯一）、约束实现（主键、外键、检查、唯一），并结合业务场景给出性能分析、查询优化建议、约束对数据完整性的作用以及索引维护与监控建议。内容严格基于仓库中的模型定义、迁移脚本与文档，避免臆测。

项目结构

系统采用 SQLAlchemy ORM + Alembic 迁移的典型后端架构，数据库层通过异步引擎连接，模型文件集中定义表结构、索引与约束，迁移脚本负责版本演进。

graph TB
subgraph "数据库层"
Engine["异步引擎<br/>engine"]
Session["会话工厂<br/>async_session_maker"]
Base["ORM基类<br/>Base"]
end
subgraph "模型层"
Models["模型定义<br/>models.py"]
Finance["财务模型<br/>finance.py"]
end
subgraph "迁移层"
Env["Alembic环境<br/>env.py"]
V001["初始迁移<br/>001_initial.py"]
V002["模板迁移<br/>002_template.py"]
end
Engine --> Session
Session --> Base
Base --> Models
Base --> Finance
Env --> V001
Env --> V002

图表来源

• database.py
• models.py
• finance.py
• env.py
• 001_initial.py
• 002_template.py

章节来源

• database.py
• env.py

核心组件

• 异步数据库引擎与会话工厂：负责连接与事务管理，支持并发读写。
• ORM模型基类：统一模型继承，承载索引与约束声明。
• 模型定义：集中定义表结构、字段类型、索引与约束。
• Alembic迁移：版本化管理数据库结构变更，确保团队协作一致性。

章节来源

• database.py
• models.py
• finance.py
• env.py

架构概览

系统通过 Alembic 将模型定义映射为数据库表结构，并在迁移脚本中显式声明索引与约束。模型层使用 SQLAlchemy 的 Index 与 CheckConstraint 等机制，迁移层通过 op.create_index/op.create_table 等操作同步到数据库。

sequenceDiagram
participant Dev as "开发者"
participant Alembic as "Alembic"
participant Env as "env.py"
participant Conn as "数据库连接"
participant DB as "数据库"
Dev->>Alembic : 生成/执行迁移
Alembic->>Env : 加载目标元数据
Env->>Conn : 获取异步连接
Conn->>DB : 执行DDL建表/索引/约束
DB-->>Conn : 返回结果
Conn-->>Alembic : 提交/回滚
Alembic-->>Dev : 迁移完成

图表来源

• env.py
• 001_initial.py
• 002_template.py

详细组件分析

员工信息表（staff）

• 主键：id（自增）
• 唯一约束：employee_id（工号唯一）
• 外键：department_id → departments.id
• 单列索引：idx_staff_dept（department_id）、idx_staff_status（status）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 员工工号唯一，便于跨模块引用与去重。
  • 员工状态与所属科室是高频过滤条件，分别建立单列索引以提升 WHERE 查询效率。
  • 与部门的多对一关系通过外键约束保证引用完整性。

章节来源

• models.py
• 001_initial.py

科室信息表（departments）

• 主键：id（自增）
• 唯一约束：code（科室编码唯一）
• 外键：parent_id → departments.id（自关联，树形结构）
• 单列索引：idx_dept_type（dept_type）、idx_dept_parent（parent_id）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 科室编码唯一，支撑业务识别与报表统计。
  • 科室类型与层级关系常用于筛选与聚合，单列索引覆盖常见查询模式。
  • 自关联外键支持组织架构树的层次遍历与父子关系校验。

章节来源

• models.py
• 001_initial.py

考核记录表（assessments）

• 主键：id（自增）
• 外键：staff_id → staff.id；assessor_id/reviewer_id → staff.id（自关联）
• 单列索引：idx_assessment_staff（staff_id）、idx_assessment_status（status）
• 复合索引：idx_assessment_period（period_year, period_month）
• 检查约束：无
• 设计要点
  • 考核周期复合索引覆盖“按年月统计”等典型查询。
  • 员工与状态索引满足“某员工某状态”的快速检索。
  • 多个自关联外键确保考核流程的完整性。

章节来源

• models.py
• 001_initial.py

考核明细表（assessment_details）

• 主键：id（自增）
• 外键：assessment_id → assessments.id；indicator_id → indicators.id
• 单列索引：idx_detail_assessment（assessment_id）、idx_detail_indicator（indicator_id）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 明细表按考核记录与指标分别建立单列索引，支撑“查看某考核的所有指标”和“查看某指标在所有考核中的表现”。

章节来源

• models.py
• 001_initial.py

工资核算记录表（salary_records）

• 主键：id（自增）
• 外键：staff_id → staff.id
• 单列索引：idx_salary_staff（staff_id）
• 复合索引：idx_salary_period（period_year, period_month）
• 检查约束：无
• 设计要点
  • 工资按员工与周期查询频繁，分别建立单列与复合索引以优化检索与聚合。

章节来源

• models.py
• 001_initial.py

系统用户表（users）

• 主键：id（自增）
• 唯一约束：username（用户名唯一）
• 外键：staff_id → staff.id（可选关联）
• 单列索引：idx_user_username（username）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 登录凭据唯一性由用户名唯一约束保证，索引加速登录与鉴权查询。

章节来源

• models.py
• 001_initial.py

绩效计划表（performance_plans）

• 主键：id（自增）
• 唯一约束：plan_code（计划编码唯一）
• 外键：department_id → departments.id；staff_id → staff.id；submitter_id/approver_id → users.id；parent_plan_id → performance_plans.id（自关联）
• 单列索引：idx_plan_level（plan_level）、idx_plan_year（plan_year）、idx_plan_department（department_id）、idx_plan_status（status）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 计划层级、年份、状态与部门是常见的过滤维度，单列索引覆盖主要查询场景。

章节来源

• models.py

计划-指标关联表（plan_kpi_relations）

• 主键：id（自增）
• 外键：plan_id → performance_plans.id；indicator_id → indicators.id
• 单列索引：idx_relation_plan（plan_id）、idx_relation_indicator（indicator_id）
• 复合索引：idx_relation_unique（plan_id, indicator_id, unique=True）
• 检查约束：无
• 设计要点
  • 关联表的复合唯一索引确保“同一计划下的同一指标仅出现一次”，避免重复绑定。

章节来源

• models.py

指标模板表（indicator_templates）

• 主键：id（自增）
• 唯一约束：template_code（模板编码唯一）
• 单列索引：idx_template_type（template_type）、idx_template_active（is_active）
• 复合索引：无
• 检查约束：无
• 设计要点
  • 模板类型与启用状态是筛选热点，单列索引满足模板选择与激活状态查询。

章节来源

• models.py
• 002_template.py

模板-指标关联表（template_indicators）

• 主键：id（自增）
• 外键：template_id → indicator_templates.id；indicator_id → indicators.id
• 单列索引：idx_ti_template（template_id）、idx_ti_indicator（indicator_id）
• 复合索引：idx_ti_unique（template_id, indicator_id, unique=True）
• 检查约束：无
• 设计要点
  • 关联表的复合唯一索引确保“同一模板下的同一指标仅出现一次”，避免重复绑定。

章节来源

• models.py
• 002_template.py

财务记录表（department_finances）

• 主键：id（自增）
• 外键：department_id → departments.id
• 单列索引：idx_finance_dept（department_id）、idx_finance_period（period_year, period_month）、idx_finance_type（finance_type）、idx_finance_category（category）
• 复合索引：无
• 检查约束：ck_finance_amount（amount >= 0）
• 设计要点
  • 财务按科室、周期、类型与类别查询频繁，单列索引覆盖这些过滤维度。
  • 金额非负的检查约束保证财务数据的数值正确性。

章节来源

• finance.py

考核指标表（indicators）

• 主键：id（自增）
• 唯一约束：code（指标编码唯一）
• 单列索引：idx_indicator_type（indicator_type）
• 复合索引：无
• 检查约束：ck_indicator_weight（weight > 0）
• 设计要点
  • 指标类型索引满足按类型筛选与统计。
  • 权重正数检查约束确保指标权重的合理性。

章节来源

• models.py
• 001_initial.py

依赖关系分析

erDiagram
DEPARTMENTS {
int id PK
string code UK
string dept_type
int parent_id FK
}
STAFF {
int id PK
string employee_id UK
int department_id FK
string status
}
ASSESSMENTS {
int id PK
int staff_id FK
int period_year
int period_month
string status
}
ASSESSMENT_DETAILS {
int id PK
int assessment_id FK
int indicator_id FK
}
SALARY_RECORDS {
int id PK
int staff_id FK
int period_year
int period_month
}
USERS {
int id PK
string username UK
}
PERFORMANCE_PLANS {
int id PK
string plan_code UK
string plan_level
int department_id FK
string status
}
PLAN_KPI_RELATIONS {
int id PK
int plan_id FK
int indicator_id FK
}
INDICATOR_TEMPLATES {
int id PK
string template_code UK
string template_type
boolean is_active
}
TEMPLATE_INDICATORS {
int id PK
int template_id FK
int indicator_id FK
}
DEPARTMENT_FINANCES {
int id PK
int department_id FK
int period_year
int period_month
string finance_type
string category
}
INDICATORS {
int id PK
string code UK
string indicator_type
numeric weight
}
DEPARTMENTS ||--o{ STAFF : "1:N"
STAFF ||--o{ ASSESSMENTS : "1:N"
ASSESSMENTS ||--o{ ASSESSMENT_DETAILS : "1:N"
STAFF ||--o{ SALARY_RECORDS : "1:N"
DEPARTMENTS ||--o{ DEPARTMENT_FINANCES : "1:N"
INDICATORS ||--o{ ASSESSMENT_DETAILS : "1:N"
INDICATORS ||--o{ PLAN_KPI_RELATIONS : "1:N"
INDICATORS ||--o{ TEMPLATE_INDICATORS : "1:N"
INDICATOR_TEMPLATES ||--o{ TEMPLATE_INDICATORS : "1:N"
PERFORMANCE_PLANS ||--o{ PLAN_KPI_RELATIONS : "1:N"
USERS ||--o{ PERFORMANCE_PLANS : "1:N"

图表来源

• models.py
• finance.py
• 001_initial.py
• 002_template.py

性能考量

• 索引策略与查询模式匹配

  • 员工与科室：按部门与状态过滤频繁，单列索引 idx_staff_dept、idx_staff_status、idx_dept_type、idx_dept_parent 能有效降低扫描范围。
  • 考核与工资：按员工与周期查询最常见，单列索引 idx_assessment_staff、idx_salary_staff，以及复合索引 idx_assessment_period、idx_salary_period 覆盖“某员工某周期”的查询。
  • 模板与计划：按类型、状态、启用状态过滤，单列索引 idx_template_type、idx_template_active、idx_plan_level、idx_plan_year、idx_plan_department、idx_plan_status。
  • 财务：按科室、周期、类型、类别过滤，单列索引 idx_finance_dept、idx_finance_period、idx_finance_type、idx_finance_category。

• 复合索引选择

  • 考核与工资的“年+月”组合索引能显著提升范围查询与分组统计性能。
  • 关联表的“计划+指标”复合唯一索引避免重复绑定，同时作为唯一约束提升查询稳定性。

• 检查约束

  • 指标权重与财务金额的非负检查约束在写入阶段即拦截异常数据，减少后续修复成本。

• 查询优化建议

  • 对高频过滤字段（如 status、dept_type、finance_type）优先使用单列索引。
  • 对范围查询（如 period_year/period_month）优先使用复合索引。
  • 避免 SELECT *，明确字段以减少 IO。
  • 对大表分页查询使用 LIMIT/OFFSET 或基于游标的分页策略，避免深度分页导致的性能退化。

• 索引维护与监控

  • 定期分析表与索引统计信息，识别未使用或冗余索引。
  • 监控慢查询日志，定位缺失索引的查询模式并补充索引。
  • 在高并发写入场景下，评估索引数量对写入性能的影响，必要时调整索引策略。

故障排查指南

• 索引缺失导致的慢查询

  • 症状：WHERE/JOIN/ORDER BY/聚合查询耗时长。
  • 排查：确认相关字段是否具备单列或复合索引；检查查询执行计划。
  • 处置：根据查询模式新增索引或调整现有索引顺序。

• 唯一约束冲突

  • 症状：插入/更新时报唯一约束冲突。
  • 排查：确认唯一字段（如 employee_id、code、username、plan_code、template_code）是否重复。
  • 处置：修正数据或调整业务逻辑，避免重复提交。

• 外键约束失败

  • 症状：插入/更新时报外键约束错误。
  • 排查：确认被引用表是否存在对应记录；确认字段类型与长度一致。
  • 处置：先创建被引用记录，再进行关联写入。

• 检查约束触发

  • 症状：插入/更新时报检查约束错误（如权重非正、金额为负）。
  • 排查：核对业务输入是否符合约束条件。
  • 处置：修正业务逻辑或前端校验，确保数据合法。

• 迁移与版本问题

  • 症状：迁移失败或版本不一致。
  • 排查：检查 Alembic 版本历史与当前数据库状态；确认迁移脚本语法正确。
  • 处置：使用 Alembic 命令检查/回滚/升级至目标版本。

章节来源

• env.py
• 001_initial.py
• 002_template.py

结论

本系统在模型层与迁移层均明确声明了索引与约束，覆盖了业务高频查询与数据完整性需求。通过单列与复合索引的合理布局，配合外键与检查约束，既保证了查询性能，也强化了数据一致性。建议持续监控查询性能与索引使用情况，动态优化索引策略以适应业务增长。

附录

索引与约束清单（按表）

• departments

  • 唯一：code
  • 外键：parent_id → departments.id
  • 单列索引：idx_dept_type、idx_dept_parent

• staff

  • 唯一：employee_id
  • 外键：department_id → departments.id
  • 单列索引：idx_staff_dept、idx_staff_status

• indicators

  • 唯一：code
  • 单列索引：idx_indicator_type
  • 检查约束：weight > 0

• assessments

  • 外键：staff_id → staff.id；assessor_id/reviewer_id → staff.id
  • 单列索引：idx_assessment_staff、idx_assessment_status
  • 复合索引：idx_assessment_period

• assessment_details

  • 外键：assessment_id → assessments.id；indicator_id → indicators.id
  • 单列索引：idx_detail_assessment、idx_detail_indicator

• salary_records

  • 外键：staff_id → staff.id
  • 单列索引：idx_salary_staff
  • 复合索引：idx_salary_period

• users

  • 唯一：username
  • 外键：staff_id → staff.id
  • 单列索引：idx_user_username

• performance_plans

  • 唯一：plan_code
  • 外键：department_id → departments.id；staff_id → staff.id；submitter_id/approver_id → users.id；parent_plan_id → performance_plans.id
  • 单列索引：idx_plan_level、idx_plan_year、idx_plan_department、idx_plan_status

• plan_kpi_relations

  • 外键：plan_id → performance_plans.id；indicator_id → indicators.id
  • 单列索引：idx_relation_plan、idx_relation_indicator
  • 复合唯一索引：idx_relation_unique

• indicator_templates

  • 唯一：template_code
  • 单列索引：idx_template_type、idx_template_active

• template_indicators

  • 外键：template_id → indicator_templates.id；indicator_id → indicators.id
  • 单列索引：idx_ti_template、idx_ti_indicator
  • 复合唯一索引：idx_ti_unique

• department_finances

  • 外键：department_id → departments.id
  • 单列索引：idx_finance_dept、idx_finance_period、idx_finance_type、idx_finance_category
  • 检查约束：amount >= 0

章节来源

• models.py
• finance.py
• 001_initial.py
• 002_template.py