GPS分段里程表分区优化方案

一、问题分析

当前情况

表名: tb_vehicle_gps_segment_mileage
数据特点: 按5分钟时间段统计GPS里程，数据量增长快
主要问题:
数据量大导致查询变慢
索引效率降低
历史数据难以清理

分区优化收益

✅ 查询性能提升: 通过分区裁剪，查询只扫描相关分区，性能提升50%-80%
✅ 维护简化: 按月分区，可以快速删除或归档历史数据
✅ 存储优化: 便于数据归档，释放磁盘空间
✅ 并发优化: 不同分区可以并行操作，减少锁冲突

二、分区方案设计

1. 分区策略

分区类型: RANGE 分区（按时间范围）
分区键: segment_start_time（时间段开始时间）
分区粒度: 按月分区
保留周期: 建议保留最近12-24个月数据

2. 分区结构

2024年: p202401, p202402, ..., p202412 (12个分区)
2025年: p202501, p202502, ..., p202512 (12个分区)
2026年: p202601, p202602, ..., p202612 (12个分区)
未来: pfuture (容纳所有未来数据)

3. 关键变更点

⚠️ 重要: 分区表的主键和唯一键必须包含分区键

原表结构:
sql PRIMARY KEY (`segment_id`), UNIQUE KEY `uk_vehicle_time` (`vehicle_id`, `segment_start_time`)

新表结构:
sql PRIMARY KEY (`segment_id`, `segment_start_time`), -- 主键包含分区键 UNIQUE KEY `uk_vehicle_time` (`vehicle_id`, `segment_start_time`) -- 已包含分区键

三、执行步骤

步骤1: 数据备份（必须！）

# 备份整个数据库
mysqldump -u用户名 -p 数据库名 > backup_$(date +%Y%m%d).sql

# 或只备份单表
mysqldump -u用户名 -p 数据库名 tb_vehicle_gps_segment_mileage > backup_segment_mileage_$(date +%Y%m%d).sql

步骤2: 查看当前数据量

-- 查看总记录数
SELECT COUNT(*) as total_records FROM tb_vehicle_gps_segment_mileage;

-- 查看按月分布
SELECT 
    DATE_FORMAT(segment_start_time, '%Y-%m') as month,
    COUNT(*) as record_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2) as percentage
FROM tb_vehicle_gps_segment_mileage
GROUP BY DATE_FORMAT(segment_start_time, '%Y-%m')
ORDER BY month;

-- 查看表大小
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_vehicle_gps_segment_mileage';

步骤3: 执行分区脚本

-- 在业务低峰期执行 partition_vehicle_gps_segment_mileage.sql
source d:/project/急救转运/code/Api/RuoYi-Vue-master/sql/partition_vehicle_gps_segment_mileage.sql

或分步执行:
1. 创建新分区表（tb_vehicle_gps_segment_mileage_new）
2. 分批迁移数据
3. 验证数据一致性
4. 切换表名

步骤4: 验证应用功能

测试以下功能是否正常：
- ✅ GPS里程计算任务
- ✅ 车辆里程查询
- ✅ 任务里程统计
- ✅ 里程报表

步骤5: 删除备份表（可选）

-- 确认运行正常后，可以删除备份表（建议保留1-2周）
DROP TABLE tb_vehicle_gps_segment_mileage_backup;

四、日常维护操作

1. 添加新月份分区（每月执行）

-- 手动添加2027年2月分区
ALTER TABLE tb_vehicle_gps_segment_mileage
REORGANIZE PARTITION pfuture INTO (
    PARTITION p202702 VALUES LESS THAN (TO_DAYS('2027-03-01')),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 或使用存储过程自动添加
CALL add_gps_segment_partition();

2. 删除历史分区（释放空间）

-- 方式1: 直接删除分区（数据不可恢复）
ALTER TABLE tb_vehicle_gps_segment_mileage DROP PARTITION p202401;

-- 方式2: 归档后删除
-- 先导出数据到归档表
CREATE TABLE tb_vehicle_gps_segment_mileage_archive_202401 
SELECT * FROM tb_vehicle_gps_segment_mileage PARTITION(p202401);

-- 然后删除分区
ALTER TABLE tb_vehicle_gps_segment_mileage DROP PARTITION p202401;

3. 查看分区状态

-- 查看所有分区信息
SELECT * FROM v_gps_segment_partition_stats;

-- 或直接查询
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'
ORDER BY PARTITION_ORDINAL_POSITION;

4. 优化分区

-- 分析表（更新统计信息）
ANALYZE TABLE tb_vehicle_gps_segment_mileage;

-- 优化表（回收碎片空间）
OPTIMIZE TABLE tb_vehicle_gps_segment_mileage;

五、查询优化建议

✅ 好的查询（利用分区裁剪）

-- 示例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: 按月统计
SELECT 
    DATE_FORMAT(segment_start_time, '%Y-%m') as month,
    SUM(segment_distance) as total_distance
FROM tb_vehicle_gps_segment_mileage
WHERE segment_start_time >= '2025-01-01' 
  AND segment_start_time < '2025-12-31'
GROUP BY DATE_FORMAT(segment_start_time, '%Y-%m');

❌ 不好的查询（全表扫描）

-- 缺少时间条件，会扫描所有分区
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE vehicle_id = 123;

-- 应该改为：
SELECT * FROM tb_vehicle_gps_segment_mileage 
WHERE vehicle_id = 123
  AND segment_start_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

六、监控和告警

1. 监控指标

各分区的数据量
表和索引的大小
最新分区是否接近满
查询性能对比（分区前后）

2. 定时任务建议

# 每月1号凌晨1点自动添加新分区
0 1 1 * * mysql -u用户名 -p密码 数据库名 -e "CALL add_gps_segment_partition();"

# 每季度删除12个月之前的历史分区
0 2 1 1,4,7,10 * /path/to/cleanup_old_partitions.sh

七、回滚方案

如果分区后出现问题，可以快速回滚：

-- 1. 停止应用写入

-- 2. 恢复原表
RENAME TABLE tb_vehicle_gps_segment_mileage TO tb_vehicle_gps_segment_mileage_failed;
RENAME TABLE tb_vehicle_gps_segment_mileage_backup TO tb_vehicle_gps_segment_mileage;

-- 3. 同步切换期间的新数据（如果有）
INSERT INTO tb_vehicle_gps_segment_mileage 
SELECT * FROM tb_vehicle_gps_segment_mileage_failed
WHERE create_time > (SELECT MAX(create_time) FROM tb_vehicle_gps_segment_mileage);

-- 4. 重启应用

八、性能对比（预期）

分区前

查询最近1月数据: ~5-10秒
查询最近3月数据: ~15-30秒
表大小: 持续增长，索引效率降低

分区后

查询最近1月数据: ~1-2秒（提升70%-80%）
查询最近3月数据: ~3-6秒（提升70%-80%）
表维护: 可按月清理，空间可控

九、常见问题

Q1: 分区会影响应用代码吗？

A: 不会。分区对应用透明，SQL语句不需要修改。

Q2: 可以在线转换吗？

A: MySQL 5.7+ 可以在线转换，但建议在低峰期执行，大表可能需要较长时间。

Q3: 分区后能回到非分区表吗？

A: 可以，使用 ALTER TABLE ... REMOVE PARTITIONING;

Q4: 主键必须包含分区键吗？

A: 是的，这是MySQL分区表的限制。

Q5: 如何确定保留多久的历史数据？

A: 根据业务需求和存储容量，建议保留12-24个月，更早的数据归档到冷存储。

十、联系支持

如果执行过程中遇到问题，请：
1. 检查错误日志: /var/log/mysql/error.log
2. 查看慢查询日志，对比性能
3. 确保有完整备份
4. 必要时联系DBA或技术支持