-- =====================================================
-- 内存优化相关数据库索引优化脚本
-- 用于提升GPS相关查询性能，减少慢查询导致的内存占用
-- =====================================================
-- 执行前请先备份数据库！
-- 执行方式: mysql -u root -p 数据库名 < optimize_memory_indexes.sql
-- =====================================================

USE `966120`;

-- =====================================================
-- 1. GPS数据表索引优化
-- =====================================================

-- 检查 tb_vehicle_gps 表的现有索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '966120' 
  AND TABLE_NAME = 'tb_vehicle_gps'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 添加车辆ID和采集时间的组合索引（用于GPS分段查询）
-- 此索引可大幅提升 selectActiveVehicleIds 和 selectGpsDataByTimeRange 查询性能
ALTER TABLE tb_vehicle_gps 
ADD INDEX idx_vehicle_collect_time (vehicle_id, collect_time)
COMMENT 'GPS分段查询优化索引';

-- 添加采集时间单独索引（用于时间范围查询）
ALTER TABLE tb_vehicle_gps 
ADD INDEX idx_collect_time (collect_time)
COMMENT 'GPS时间范围查询索引';

-- 添加设备ID索引（用于GPS同步查询）
ALTER TABLE tb_vehicle_gps 
ADD INDEX idx_device_id (device_id)
COMMENT 'GPS设备ID查询索引';

-- =====================================================
-- 2. GPS分段里程表索引优化
-- =====================================================

-- 检查 vehicle_gps_segment_mileage 表的现有索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '966120' 
  AND TABLE_NAME = 'vehicle_gps_segment_mileage'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 添加车辆ID和分段开始时间的组合索引
ALTER TABLE vehicle_gps_segment_mileage 
ADD INDEX idx_vehicle_segment_start (vehicle_id, segment_start_time)
COMMENT '车辆分段查询索引';

-- 添加任务ID索引（用于任务关联查询）
ALTER TABLE vehicle_gps_segment_mileage 
ADD INDEX idx_task_id (task_id)
COMMENT '任务关联查询索引';

-- 添加分段时间范围索引（用于统计汇总）
ALTER TABLE vehicle_gps_segment_mileage 
ADD INDEX idx_segment_time_range (segment_start_time, segment_end_time)
COMMENT '分段时间范围查询索引';

-- =====================================================
-- 3. GPS已计算记录表索引优化
-- =====================================================

-- 检查 vehicle_gps_calculated 表是否存在
SELECT COUNT(*) as table_exists
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '966120' 
  AND TABLE_NAME = 'vehicle_gps_calculated';

-- 如果表存在，添加索引
ALTER TABLE vehicle_gps_calculated 
ADD INDEX idx_gps_id (gps_id)
COMMENT 'GPS点查询索引';

ALTER TABLE vehicle_gps_calculated 
ADD INDEX idx_vehicle_segment (vehicle_id, segment_id)
COMMENT '车辆分段关联索引';

-- =====================================================
-- 4. 任务表索引优化
-- =====================================================

-- 添加车辆ID和时间范围的组合索引（用于关联任务查询）
ALTER TABLE sys_task 
ADD INDEX idx_vehicle_time_range (
    vehicle_id, 
    actual_start_time, 
    actual_end_time
) COMMENT '车辆任务时间范围查询索引';

-- 添加任务状态和计划时间索引（用于定时任务查询）
ALTER TABLE sys_task 
ADD INDEX idx_status_planned_time (
    task_status, 
    planned_start_time
) COMMENT '任务状态时间查询索引';

-- =====================================================
-- 5. 急救转运任务表索引优化
-- =====================================================

-- 添加同步状态索引（用于旧系统同步任务）
ALTER TABLE sys_task_emergency 
ADD INDEX idx_sync_status (sync_status)
COMMENT '同步状态查询索引';

-- 添加调度单同步状态索引
ALTER TABLE sys_task_emergency 
ADD INDEX idx_dispatch_sync_status (dispatch_sync_status)
COMMENT '调度单同步状态查询索引';

-- 添加旧系统ID组合索引（用于双向同步查询）
ALTER TABLE sys_task_emergency 
ADD INDEX idx_legacy_ids (
    legacy_service_ord_id, 
    legacy_dispatch_ord_id
) COMMENT '旧系统ID查询索引';

-- =====================================================
-- 6. 车辆信息表索引优化
-- =====================================================

-- 添加设备ID索引（用于GPS关联查询）
ALTER TABLE tb_vehicle_info 
ADD INDEX idx_device_id (device_id)
COMMENT '设备ID查询索引';

-- 添加车牌号索引（用于车辆查询）
ALTER TABLE tb_vehicle_info 
ADD INDEX idx_vehicle_no (vehicle_no)
COMMENT '车牌号查询索引';

-- =====================================================
-- 7. 车辆里程统计表索引优化
-- =====================================================

-- 检查 vehicle_mileage_stats 表的现有索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '966120' 
  AND TABLE_NAME = 'vehicle_mileage_stats'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 添加车辆ID和统计日期的组合索引
ALTER TABLE vehicle_mileage_stats 
ADD INDEX idx_vehicle_stat_date (vehicle_id, stat_date)
COMMENT '车辆统计日期查询索引';

-- 添加统计日期索引（用于批量查询）
ALTER TABLE vehicle_mileage_stats 
ADD INDEX idx_stat_date (stat_date)
COMMENT '统计日期查询索引';

-- =====================================================
-- 8. 验证索引创建结果
-- =====================================================

-- 查看所有新增索引
SELECT 
    TABLE_NAME as '表名',
    INDEX_NAME as '索引名',
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as '索引列',
    INDEX_TYPE as '索引类型',
    COMMENT as '备注'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '966120' 
  AND TABLE_NAME IN (
      'tb_vehicle_gps',
      'vehicle_gps_segment_mileage',
      'vehicle_gps_calculated',
      'sys_task',
      'sys_task_emergency',
      'tb_vehicle_info',
      'vehicle_mileage_stats'
  )
  AND INDEX_NAME LIKE 'idx_%'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

-- =====================================================
-- 9. 查询性能测试
-- =====================================================

-- 测试GPS分段查询性能
EXPLAIN SELECT vehicle_id 
FROM tb_vehicle_gps 
WHERE collect_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY vehicle_id;

-- 测试车辆分段里程查询性能
EXPLAIN SELECT * 
FROM vehicle_gps_segment_mileage 
WHERE vehicle_id = 1 
  AND segment_start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 测试任务关联查询性能
EXPLAIN SELECT t.* 
FROM sys_task t
WHERE t.vehicle_id = 1 
  AND t.actual_start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
  AND t.actual_end_time <= NOW();

-- =====================================================
-- 10. 索引使用统计（可选）
-- =====================================================

-- 启用性能统计（MySQL 8.0+）
-- UPDATE performance_schema.setup_instruments 
-- SET ENABLED = 'YES', TIMED = 'YES' 
-- WHERE NAME LIKE '%statement/%';

-- 查看慢查询统计
SELECT 
    SCHEMA_NAME as '数据库',
    DIGEST_TEXT as '查询语句',
    COUNT_STAR as '执行次数',
    AVG_TIMER_WAIT/1000000000 as '平均耗时(秒)',
    SUM_ROWS_EXAMINED as '扫描行数'
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = '966120'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- =====================================================
-- 完成提示
-- =====================================================

SELECT '====================================' as '';
SELECT '索引优化脚本执行完成！' as '提示';
SELECT '请检查上述验证结果，确认索引已正确创建' as '说明';
SELECT '建议重启应用服务，让新索引生效' as '建议';
SELECT '====================================' as '';

-- =====================================================
-- 回滚脚本（如需删除索引）
-- =====================================================
/*
-- 如果需要回滚，执行以下语句：

ALTER TABLE tb_vehicle_gps DROP INDEX idx_vehicle_collect_time;
ALTER TABLE tb_vehicle_gps DROP INDEX idx_collect_time;
ALTER TABLE tb_vehicle_gps DROP INDEX idx_device_id;

ALTER TABLE vehicle_gps_segment_mileage DROP INDEX idx_vehicle_segment_start;
ALTER TABLE vehicle_gps_segment_mileage DROP INDEX idx_task_id;
ALTER TABLE vehicle_gps_segment_mileage DROP INDEX idx_segment_time_range;

ALTER TABLE vehicle_gps_calculated DROP INDEX idx_gps_id;
ALTER TABLE vehicle_gps_calculated DROP INDEX idx_vehicle_segment;

ALTER TABLE sys_task DROP INDEX idx_vehicle_time_range;
ALTER TABLE sys_task DROP INDEX idx_status_planned_time;

ALTER TABLE sys_task_emergency DROP INDEX idx_sync_status;
ALTER TABLE sys_task_emergency DROP INDEX idx_dispatch_sync_status;
ALTER TABLE sys_task_emergency DROP INDEX idx_legacy_ids;

ALTER TABLE tb_vehicle_info DROP INDEX idx_device_id;
ALTER TABLE tb_vehicle_info DROP INDEX idx_vehicle_no;

ALTER TABLE vehicle_mileage_stats DROP INDEX idx_vehicle_stat_date;
ALTER TABLE vehicle_mileage_stats DROP INDEX idx_stat_date;
*/