zyt/APPOINTMENT_INDEX_OPTIMIZATION.md

预约列表查询性能优化 - 索引方案

问题分析

预约列表查询涉及多表关联和复杂条件筛选，主要性能瓶颈：

1. 多表 LEFT JOIN（预约表、诊断表、管理员表）
2. 多条件筛选（状态、日期范围、患者姓名、医生姓名）
3. 权限过滤（医生角色、医助角色）
4. 复杂排序（状态 + 日期 + 时间）
5. 子查询（确认诊单、开方状态）

索引优化方案

1. 预约表 (zyt_appointment)

单列索引

-- 医生ID索引（医生角色查询）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_doctor_id` (`doctor_id`);

-- 患者ID索引（关联查询）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_patient_id` (`patient_id`);

-- 状态索引（状态筛选）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_status` (`status`);

-- 预约日期索引（日期范围查询）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_appointment_date` (`appointment_date`);

组合索引（重点优化）

-- 状态+日期+时间（用于排序，覆盖 ORDER BY）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_status_date_time` (`status`, `appointment_date`, `appointment_time`);

-- 医生+状态+日期（医生角色常用查询）
ALTER TABLE `zyt_appointment` ADD INDEX `idx_doctor_status_date` (`doctor_id`, `status`, `appointment_date`);

优化效果：

• idx_status_date_time 可以直接用于排序，避免 filesort
• idx_doctor_status_date 可以快速过滤医生的预约记录

2. 诊断表 (zyt_tcm_diagnosis)

-- 患者姓名索引（模糊搜索，前缀索引）
ALTER TABLE `zyt_tcm_diagnosis` ADD INDEX `idx_patient_name` (`patient_name`(20));

-- 医助ID索引（医助角色查询）
ALTER TABLE `zyt_tcm_diagnosis` ADD INDEX `idx_assistant_id` (`assistant_id`);

说明：

• patient_name 使用前缀索引(20)，节省空间且支持 LIKE 'xxx%' 查询
• 不支持 LIKE '%xxx%' 的优化，但可以减少扫描行数

3. 管理员表 (zyt_admin)

-- 管理员姓名索引（医生姓名搜索）
ALTER TABLE `zyt_admin` ADD INDEX `idx_name` (`name`(20));

4. 诊断查看记录表 (zyt_diagnosis_view_records)

-- 诊断ID+确认状态+删除时间（用于确认诊单查询）
ALTER TABLE `zyt_diagnosis_view_records` ADD INDEX `idx_diagnosis_confirmed` (`diagnosis_id`, `is_confirmed`, `delete_time`);

优化效果：

• 加速 WHERE diagnosis_id IN (...) AND is_confirmed = 1 AND delete_time IS NULL 查询
• 覆盖索引，无需回表

5. 处方表 (zyt_prescription)

-- 诊断ID+删除时间（用于开方状态查询）
ALTER TABLE `zyt_prescription` ADD INDEX `idx_diagnosis_delete` (`diagnosis_id`, `delete_time`);

优化效果：

• 加速 WHERE diagnosis_id IN (...) AND delete_time IS NULL 查询

6. 管理员角色表 (zyt_admin_role)

-- 管理员ID+角色ID（用于权限判断）
ALTER TABLE `zyt_admin_role` ADD INDEX `idx_admin_role` (`admin_id`, `role_id`);

优化效果：

• 加速角色查询，快速判断用户权限

执行步骤

方式一：直接执行（推荐）

# 连接数据库
mysql -h 39.97.232.35 -u cs_zyt -p cs_zyt

# 执行索引创建脚本
source optimize_appointment_indexes.sql

方式二：安全执行（检查后添加）

# 执行安全脚本（会先检查索引是否存在）
source check_and_add_indexes.sql

方式三：phpMyAdmin

1. 登录 phpMyAdmin
2. 选择数据库 cs_zyt
3. 点击 SQL 标签
4. 复制 optimize_appointment_indexes.sql 内容
5. 点击执行

预期效果

优化前

• 查询时间：500ms - 2000ms
• 扫描行数：全表扫描
• 使用索引：PRIMARY 或无

优化后

• 查询时间：50ms - 200ms（提升 5-10 倍）
• 扫描行数：索引范围扫描
• 使用索引：组合索引

验证方法

1. 查看执行计划

EXPLAIN SELECT 
    a.*, 
    u.patient_name, 
    u.phone as patient_phone,
    ad.name as doctor_name
FROM zyt_appointment a
LEFT JOIN zyt_tcm_diagnosis u ON a.patient_id = u.id
LEFT JOIN zyt_admin ad ON a.doctor_id = ad.id
WHERE a.doctor_id = 1
  AND a.status = 1
  AND a.appointment_date >= '2024-03-01'
ORDER BY a.status ASC, a.appointment_date ASC, a.appointment_time ASC
LIMIT 20;

期望结果：

• type: ref 或 range（不是 ALL）
• key: idx_doctor_status_date 或 idx_status_date_time
• rows: 较小的数字（不是全表行数）

2. 查看索引使用情况

-- 查看索引统计
SHOW INDEX FROM zyt_appointment;

-- 查看索引使用情况（需要开启慢查询日志）
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'cs_zyt';

注意事项

1. 索引维护成本：索引会增加 INSERT/UPDATE/DELETE 的开销，但对于查询为主的场景，收益远大于成本

2. 索引选择性：

   • 高选择性字段（如 ID、日期）适合建索引
   • 低选择性字段（如性别、状态）单独建索引效果不明显，但可以作为组合索引的一部分

3. 前缀索引：

   • 对于长字符串字段（如姓名），使用前缀索引可以节省空间
   • 前缀长度选择：能区分大部分数据即可（通常 10-20 个字符）

4. 组合索引顺序：

   • 遵循"最左前缀"原则
   • 将选择性高的字段放在前面
   • 考虑查询条件的组合频率

5. 定期维护：

   -- 优化表（重建索引）
   OPTIMIZE TABLE zyt_appointment;
   OPTIMIZE TABLE zyt_tcm_diagnosis;
   

进一步优化建议

1. 查询优化

// 避免 SELECT *，只查询需要的字段
$query->field('a.id, a.patient_id, a.doctor_id, a.status, ...');

// 使用 whereIn 代替多个 OR
$query->whereIn('a.status', [1, 3]);

2. 分页优化

// 使用延迟关联优化深分页
$subQuery = Appointment::where($conditions)
    ->field('id')
    ->limit($offset, $limit)
    ->buildSql();

$list = Appointment::alias('a')
    ->join([$subQuery => 'sub'], 'a.id = sub.id')
    ->with(['patient', 'doctor'])
    ->select();

3. 缓存优化

// 缓存医生列表、角色信息等不常变化的数据
$roleIds = Cache::remember('admin_role_' . $adminId, function() {
    return AdminRole::where('admin_id', $adminId)->column('role_id');
}, 3600);

监控指标

定期检查以下指标：

1. 慢查询日志（> 1s 的查询）
2. 索引使用率
3. 表大小和索引大小
4. 查询响应时间

-- 查看表和索引大小
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'cs_zyt'
  AND table_name IN ('zyt_appointment', 'zyt_tcm_diagnosis', 'zyt_admin')
ORDER BY (data_length + index_length) DESC;