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