-- ========================================
-- GPS分段里程表分区优化方案
-- ========================================
-- 功能说明：
-- 1. 将 tb_vehicle_gps_segment_mileage 表按月进行分区
-- 2. 提高大数据量下的查询性能
-- 3. 方便历史数据归档和删除
-- 
-- 注意事项：
-- 1. 执行此脚本前请备份数据库！
-- 2. 数据量大时转换过程较慢，建议在业务低峰期执行
-- 3. 分区后主键和唯一键必须包含分区键（segment_start_time）
-- ========================================

-- 步骤1：备份原表数据
-- 建议先手动执行：mysqldump -u用户名 -p 数据库名 tb_vehicle_gps_segment_mileage > backup_segment_mileage.sql

-- 步骤2：创建新的分区表
CREATE TABLE `tb_vehicle_gps_segment_mileage_new` (
  `segment_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分段ID',
  `vehicle_id` bigint(20) NOT NULL COMMENT '车辆ID',
  `vehicle_no` varchar(20) DEFAULT NULL COMMENT '车牌号',
  `segment_start_time` datetime NOT NULL COMMENT '时间段开始时间',
  `segment_end_time` datetime NOT NULL COMMENT '时间段结束时间',
  `start_longitude` decimal(10,7) DEFAULT NULL COMMENT '起点经度',
  `start_latitude` decimal(10,7) DEFAULT NULL COMMENT '起点纬度',
  `end_longitude` decimal(10,7) DEFAULT NULL COMMENT '终点经度',
  `end_latitude` decimal(10,7) DEFAULT NULL COMMENT '终点纬度',
  `segment_distance` decimal(10,3) DEFAULT 0.000 COMMENT '段距离(公里)',
  `gps_point_count` int(11) DEFAULT 0 COMMENT 'GPS点数量',
  `gps_ids` text COMMENT '关联的GPS记录ID列表（逗号分隔）',
  `task_id` bigint(20) DEFAULT NULL COMMENT '关联任务ID',
  `task_code` varchar(50) DEFAULT NULL COMMENT '任务编号',
  `calculate_method` varchar(20) DEFAULT 'tianditu' COMMENT '计算方式(tianditu-天地图/haversine-球面距离)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  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`),
  KEY `idx_vehicle_date` (`vehicle_id`, `segment_start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆GPS分段里程表（分区版）'
PARTITION BY RANGE (TO_DAYS(segment_start_time)) (
    -- 2024年分区
    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')),
    
    -- 2025年分区
    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')),
    
    -- 2026年分区
    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')),
    
    -- 未来数据分区（可以容纳2027年及以后的数据）
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 步骤3：迁移数据到新表
-- 方式1：一次性迁移（适用于数据量较小，如100万以下）
-- INSERT INTO tb_vehicle_gps_segment_mileage_new SELECT * FROM tb_vehicle_gps_segment_mileage;

-- 方式2：分批迁移（适用于大数据量，推荐）
-- 按月份分批迁移，减少锁表时间
-- 2024年1月
INSERT INTO tb_vehicle_gps_segment_mileage_new 
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE segment_start_time >= '2024-01-01' AND segment_start_time < '2024-02-01';

-- 2024年2月
INSERT INTO tb_vehicle_gps_segment_mileage_new 
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE segment_start_time >= '2024-02-01' AND segment_start_time < '2024-03-01';

-- 继续按月迁移...（根据实际数据情况调整）
-- 2024年3月至12月
INSERT INTO tb_vehicle_gps_segment_mileage_new 
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE segment_start_time >= '2024-03-01' AND segment_start_time < '2025-01-01';

-- 2025年数据
INSERT INTO tb_vehicle_gps_segment_mileage_new 
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE segment_start_time >= '2025-01-01' AND segment_start_time < '2026-01-01';

-- 2026年数据
INSERT INTO tb_vehicle_gps_segment_mileage_new 
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE segment_start_time >= '2026-01-01';

-- 步骤4：验证数据一致性
-- 检查原表和新表的记录数
SELECT 'Original Table' as table_name, COUNT(*) as record_count FROM tb_vehicle_gps_segment_mileage
UNION ALL
SELECT 'New Table' as table_name, COUNT(*) as record_count FROM tb_vehicle_gps_segment_mileage_new;

-- 检查各分区的数据量
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH,
    CREATE_TIME
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage_new'
ORDER BY PARTITION_NAME;

-- 步骤5：切换表名（谨慎操作！）
-- 建议在业务低峰期执行，整个操作应在事务中完成
-- START TRANSACTION;

-- 重命名原表为备份表
RENAME TABLE tb_vehicle_gps_segment_mileage TO tb_vehicle_gps_segment_mileage_backup;

-- 将新表重命名为正式表
RENAME TABLE tb_vehicle_gps_segment_mileage_new TO tb_vehicle_gps_segment_mileage;

-- 如果一切正常，提交事务
-- COMMIT;

-- 如果出现问题，回滚
-- ROLLBACK;

-- 步骤6：验证应用正常运行
-- 请在应用层测试以下功能：
-- 1. GPS里程计算功能
-- 2. 车辆里程查询
-- 3. 任务里程统计
-- 4. 相关报表功能

-- 步骤7：确认无误后删除备份表（可选，建议保留一段时间）
-- DROP TABLE tb_vehicle_gps_segment_mileage_backup;

-- ========================================
-- 分区维护操作（定期执行）
-- ========================================

-- 添加新月份的分区（每月或每季度执行一次）
-- 例如：添加2027年1月的分区
-- 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
-- );

-- 删除历史分区（归档旧数据，释放空间）
-- 例如：删除2024年1月的数据（删除前请确保已备份）
-- ALTER TABLE tb_vehicle_gps_segment_mileage DROP PARTITION p202401;

-- 或者清空分区数据但保留分区结构
-- ALTER TABLE tb_vehicle_gps_segment_mileage TRUNCATE PARTITION p202401;

-- ========================================
-- 性能优化建议
-- ========================================

-- 1. 查询时尽量带上时间范围条件，以充分利用分区裁剪
-- 示例：
-- SELECT * FROM tb_vehicle_gps_segment_mileage 
-- WHERE segment_start_time >= '2025-01-01' 
--   AND segment_start_time < '2025-02-01'
--   AND vehicle_id = 123;

-- 2. 定期分析表以优化查询计划
-- ANALYZE TABLE tb_vehicle_gps_segment_mileage;

-- 3. 定期优化表以回收空间
-- OPTIMIZE TABLE tb_vehicle_gps_segment_mileage;

-- 4. 查看分区使用情况
SELECT 
    PARTITION_NAME as '分区名',
    PARTITION_METHOD as '分区方式',
    PARTITION_EXPRESSION as '分区表达式',
    TABLE_ROWS as '记录数',
    ROUND(DATA_LENGTH/1024/1024, 2) as '数据大小(MB)',
    ROUND(INDEX_LENGTH/1024/1024, 2) as '索引大小(MB)',
    PARTITION_COMMENT as '备注'
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage'
ORDER BY PARTITION_ORDINAL_POSITION;

-- ========================================
-- 分区自动维护脚本（建议配置定时任务）
-- ========================================

DELIMITER $$

CREATE PROCEDURE add_gps_segment_partition()
BEGIN
    DECLARE next_month_date DATE;
    DECLARE partition_name VARCHAR(20);
    DECLARE next_partition_date DATE;
    
    -- 计算下个月的日期
    SET next_month_date = DATE_ADD(CURDATE(), INTERVAL 2 MONTH);
    SET next_month_date = DATE_FORMAT(next_month_date, '%Y-%m-01');
    
    -- 生成分区名称（例如：p202701）
    SET partition_name = CONCAT('p', DATE_FORMAT(next_month_date, '%Y%m'));
    
    -- 计算下一个分区的边界日期
    SET next_partition_date = DATE_ADD(next_month_date, INTERVAL 1 MONTH);
    
    -- 动态添加分区
    SET @sql = CONCAT(
        'ALTER TABLE tb_vehicle_gps_segment_mileage ',
        'REORGANIZE PARTITION pfuture INTO (',
        'PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS(''', next_partition_date, ''')),',
        'PARTITION pfuture VALUES LESS THAN MAXVALUE',
        ')'
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SELECT CONCAT('成功添加分区: ', partition_name, ', 边界日期: ', next_partition_date) as result;
END$$

DELIMITER ;

-- 使用方法：每月执行一次
-- CALL add_gps_segment_partition();

-- ========================================
-- 历史数据归档策略（可选）
-- ========================================

-- 方案1：导出历史分区到归档表
-- CREATE TABLE tb_vehicle_gps_segment_mileage_archive LIKE tb_vehicle_gps_segment_mileage;
-- ALTER TABLE tb_vehicle_gps_segment_mileage_archive REMOVE PARTITIONING;
-- INSERT INTO tb_vehicle_gps_segment_mileage_archive 
-- SELECT * FROM tb_vehicle_gps_segment_mileage PARTITION(p202401);

-- 方案2：导出到文件
-- SELECT * INTO OUTFILE '/tmp/gps_segment_202401.csv'
-- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-- LINES TERMINATED BY '\n'
-- FROM tb_vehicle_gps_segment_mileage PARTITION(p202401);

-- 方案3：定期删除超过N个月的历史数据
-- 例如：删除12个月之前的数据
-- ALTER TABLE tb_vehicle_gps_segment_mileage 
-- DROP PARTITION p202401;

-- ========================================
-- 监控和告警（建议）
-- ========================================

-- 监控各分区的数据量增长
CREATE VIEW v_gps_segment_partition_stats AS
SELECT 
    PARTITION_NAME as partition_name,
    TABLE_ROWS as row_count,
    ROUND(DATA_LENGTH/1024/1024, 2) as data_size_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) as index_size_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) as total_size_mb,
    CREATE_TIME as create_time,
    UPDATE_TIME as update_time
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage'
ORDER BY PARTITION_ORDINAL_POSITION;

-- 查看分区统计
-- SELECT * FROM v_gps_segment_partition_stats;