-- ========================================
-- GPS分段里程表分区优化 - 快速执行版
-- ========================================
-- 适用场景：数据量适中（100万-500万），可以接受短暂停机
-- 执行时间：根据数据量，预计5-30分钟
-- ========================================

-- 第一步：检查当前数据状态
-- ========================================
USE your_database_name; -- 请修改为实际的数据库名

SELECT '=== 当前表信息 ===' as info;
SELECT 
    TABLE_NAME as '表名',
    TABLE_ROWS as '记录数(估算)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage';

SELECT '=== 数据时间范围 ===' as info;
SELECT 
    MIN(segment_start_time) as '最早数据',
    MAX(segment_start_time) as '最新数据',
    DATEDIFF(MAX(segment_start_time), MIN(segment_start_time)) as '数据跨度(天)'
FROM tb_vehicle_gps_segment_mileage;

SELECT '=== 按月数据分布 ===' as info;
SELECT 
    DATE_FORMAT(segment_start_time, '%Y-%m') as '月份',
    COUNT(*) as '记录数',
    ROUND(SUM(segment_distance), 2) as '总里程(km)'
FROM tb_vehicle_gps_segment_mileage
GROUP BY DATE_FORMAT(segment_start_time, '%Y-%m')
ORDER BY 1 DESC
LIMIT 12;

-- 暂停！请检查以上信息，确认数据量和时间范围
-- 按回车继续...

-- ========================================
-- 第二步：创建分区表
-- ========================================

-- 创建新的分区表
CREATE TABLE `tb_vehicle_gps_segment_mileage_partitioned` (
  `segment_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `vehicle_id` bigint(20) NOT NULL,
  `vehicle_no` varchar(20) DEFAULT NULL,
  `segment_start_time` datetime NOT NULL,
  `segment_end_time` datetime NOT NULL,
  `start_longitude` decimal(10,7) DEFAULT NULL,
  `start_latitude` decimal(10,7) DEFAULT NULL,
  `end_longitude` decimal(10,7) DEFAULT NULL,
  `end_latitude` decimal(10,7) DEFAULT NULL,
  `segment_distance` decimal(10,3) DEFAULT 0.000,
  `gps_point_count` int(11) DEFAULT 0,
  `gps_ids` text,
  `task_id` bigint(20) DEFAULT NULL,
  `task_code` varchar(50) DEFAULT NULL,
  `calculate_method` varchar(20) DEFAULT 'tianditu',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`segment_id`, `segment_start_time`),
  UNIQUE KEY `uk_vehicle_time` (`vehicle_id`, `segment_start_time`),
  KEY `idx_vehicle_id` (`vehicle_id`),
  KEY `idx_start_time` (`segment_start_time`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_vehicle_task` (`vehicle_id`, `task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
COMMENT='车辆GPS分段里程表（按月分区）'
PARTITION BY RANGE (TO_DAYS(segment_start_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
    PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
    PARTITION p202409 VALUES LESS THAN (TO_DAYS('2024-10-01')),
    PARTITION p202410 VALUES LESS THAN (TO_DAYS('2024-11-01')),
    PARTITION p202411 VALUES LESS THAN (TO_DAYS('2024-12-01')),
    PARTITION p202412 VALUES LESS THAN (TO_DAYS('2025-01-01')),
    PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')),
    PARTITION p202504 VALUES LESS THAN (TO_DAYS('2025-05-01')),
    PARTITION p202505 VALUES LESS THAN (TO_DAYS('2025-06-01')),
    PARTITION p202506 VALUES LESS THAN (TO_DAYS('2025-07-01')),
    PARTITION p202507 VALUES LESS THAN (TO_DAYS('2025-08-01')),
    PARTITION p202508 VALUES LESS THAN (TO_DAYS('2025-09-01')),
    PARTITION p202509 VALUES LESS THAN (TO_DAYS('2025-10-01')),
    PARTITION p202510 VALUES LESS THAN (TO_DAYS('2025-11-01')),
    PARTITION p202511 VALUES LESS THAN (TO_DAYS('2025-12-01')),
    PARTITION p202512 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
    PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION p202610 VALUES LESS THAN (TO_DAYS('2026-11-01')),
    PARTITION p202611 VALUES LESS THAN (TO_DAYS('2026-12-01')),
    PARTITION p202612 VALUES LESS THAN (TO_DAYS('2027-01-01')),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

SELECT '分区表创建成功' as result;

-- ========================================
-- 第三步：迁移数据
-- ========================================
SELECT '开始迁移数据...' as info, NOW() as start_time;

-- 一次性迁移（适用于数据量不超过500万）
INSERT INTO tb_vehicle_gps_segment_mileage_partitioned 
SELECT * FROM tb_vehicle_gps_segment_mileage;

SELECT '数据迁移完成' as info, NOW() as end_time;

-- ========================================
-- 第四步：验证数据
-- ========================================
SELECT '=== 数据验证 ===' as info;

-- 比较记录数
SELECT 
    '原表' as table_name, 
    COUNT(*) as record_count 
FROM tb_vehicle_gps_segment_mileage
UNION ALL
SELECT 
    '新表(分区)' as table_name, 
    COUNT(*) as record_count 
FROM tb_vehicle_gps_segment_mileage_partitioned;

-- 比较统计数据
SELECT 
    '原表' as table_name,
    SUM(segment_distance) as total_distance,
    MIN(segment_start_time) as min_time,
    MAX(segment_start_time) as max_time
FROM tb_vehicle_gps_segment_mileage
UNION ALL
SELECT 
    '新表(分区)' as table_name,
    SUM(segment_distance) as total_distance,
    MIN(segment_start_time) as min_time,
    MAX(segment_start_time) as max_time
FROM tb_vehicle_gps_segment_mileage_partitioned;

-- 查看分区分布
SELECT 
    PARTITION_NAME as '分区名',
    TABLE_ROWS as '记录数',
    ROUND(DATA_LENGTH/1024/1024, 2) as '数据(MB)',
    ROUND(INDEX_LENGTH/1024/1024, 2) as '索引(MB)'
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage_partitioned'
ORDER BY PARTITION_ORDINAL_POSITION;

-- 暂停！请检查数据是否一致
-- 如果数据一致，继续执行下一步
-- 如果不一致，请停止并检查问题

-- ========================================
-- 第五步：切换表名（谨慎！）
-- ========================================
-- 建议在业务停机窗口执行以下操作

-- START TRANSACTION;

SELECT '开始切换表名...' as info;

-- 重命名原表为备份表
RENAME TABLE 
    tb_vehicle_gps_segment_mileage TO tb_vehicle_gps_segment_mileage_old,
    tb_vehicle_gps_segment_mileage_partitioned TO tb_vehicle_gps_segment_mileage;

SELECT '表名切换完成，请立即验证应用功能！' as result;

-- 如果有问题，立即回滚：
-- RENAME TABLE 
--     tb_vehicle_gps_segment_mileage TO tb_vehicle_gps_segment_mileage_partitioned,
--     tb_vehicle_gps_segment_mileage_old TO tb_vehicle_gps_segment_mileage;

-- COMMIT;

-- ========================================
-- 第六步：验证分区效果
-- ========================================
SELECT '=== 测试查询性能 ===' as info;

-- 测试1：查询最近1月数据（应该只扫描1个分区）
EXPLAIN PARTITIONS
SELECT COUNT(*), SUM(segment_distance) 
FROM tb_vehicle_gps_segment_mileage
WHERE segment_start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

-- 测试2：按车辆ID查询最近1周数据
EXPLAIN PARTITIONS
SELECT * 
FROM tb_vehicle_gps_segment_mileage
WHERE vehicle_id = 1
  AND segment_start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
LIMIT 10;

-- 测试3：统计最近3个月的里程
SELECT 
    DATE_FORMAT(segment_start_time, '%Y-%m') as month,
    COUNT(*) as segments,
    ROUND(SUM(segment_distance), 2) as total_km
FROM tb_vehicle_gps_segment_mileage
WHERE segment_start_time >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY DATE_FORMAT(segment_start_time, '%Y-%m');

-- ========================================
-- 第七步：清理和优化（可选）
-- ========================================

-- 确认应用运行正常后，等待1-2周，然后删除备份表
-- DROP TABLE tb_vehicle_gps_segment_mileage_old;

-- 分析表，优化查询计划
ANALYZE TABLE tb_vehicle_gps_segment_mileage;

-- ========================================
-- 完成！
-- ========================================
SELECT '========================================' as info;
SELECT '分区优化完成！' as result;
SELECT '请注意：' as notice;
SELECT '1. 定期添加新月份的分区' as task1;
SELECT '2. 定期清理历史分区释放空间' as task2;
SELECT '3. 查询时尽量带上时间范围条件' as task3;
SELECT '========================================' as info;

-- ========================================
-- 日常维护命令参考
-- ========================================

-- 添加新分区（每月执行一次）
-- ALTER TABLE tb_vehicle_gps_segment_mileage
-- REORGANIZE PARTITION pfuture INTO (
--     PARTITION p202701 VALUES LESS THAN (TO_DAYS('2027-02-01')),
--     PARTITION pfuture VALUES LESS THAN MAXVALUE
-- );

-- 删除历史分区（释放空间）
-- ALTER TABLE tb_vehicle_gps_segment_mileage DROP PARTITION p202401;

-- 查看分区状态
-- SELECT * FROM information_schema.PARTITIONS 
-- WHERE TABLE_SCHEMA = DATABASE() 
--   AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage';