-- ========================================
|
-- 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;
|
