-- =====================================================
|
-- 内存优化相关数据库索引优化脚本
|
-- 用于提升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;
|
*/
|
