DELIMITER $$
-- 创建或替换存储过程，优化视图数据迁移到目标表的性能
CREATE PROCEDURE OptimizedMigrateView1ToTable1()
BEGIN
-- 声明变量
DECLARE batch_size INT DEFAULT 100000; -- 调整批处理大小为 100000
DECLARE max_id_main BIGINT DEFAULT 0;
DECLARE min_id_main BIGINT DEFAULT 0;
DECLARE current_id_main BIGINT DEFAULT 0;
DECLARE main_count INT DEFAULT 0;
DECLARE total_main_inserted INT DEFAULT 0;
DECLARE max_id_detail BIGINT DEFAULT 0;
DECLARE min_id_detail BIGINT DEFAULT 0;
DECLARE current_id_detail BIGINT DEFAULT 0;
DECLARE detail_count INT DEFAULT 0;
DECLARE total_detail_inserted INT DEFAULT 0;
DECLARE last_migration_time DATETIME;
DECLARE batch_start_time TIMESTAMP;
DECLARE batch_end_time TIMESTAMP;
DECLARE elapsed_time FLOAT;
-- 获取上次迁移的时间
SELECT MAX(migration_time) INTO last_migration_time FROM migration_log WHERE view_name IN ('mr_project_one_month', 'mr_project_item_one_month') AND status = 'SUCCESS';
-- 如果找不到上次迁移时间，则默认迁移 30 天内的数据
IF last_migration_time IS NULL THEN
SET last_migration_time = DATE_SUB(NOW(), INTERVAL 30 DAY);
END IF;
-- 确保索引用于后续时间过滤查询
-- 假设 mr_project_one_month 和 mr_project_item_one_month 表的 create_time 列有索引
-- 创建索引示例：CREATE INDEX idx_create_time_project ON mr_project_one_month(create_time);
-- CREATE INDEX idx_create_time_item ON mr_project_item_one_month(create_time);
-- 临时关闭唯一约束检查和外键约束检查
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 一次性获取主表数据的 ID 范围和总记录数（只获取上次迁移后的新数据）
SELECT MIN(id), MAX(id), COUNT(*) INTO min_id_main, max_id_main, main_count FROM mr_project_one_month WHERE create_time >= last_migration_time;
-- 使用更高效的分批插入方式（增量插入）
IF main_count > 0 THEN
SET current_id_main = min_id_main;
WHILE current_id_main <= max_id_main DO
START TRANSACTION;
SET batch_start_time = NOW();
INSERT IGNORE INTO mr_project_month_job_data
SELECT *
FROM mr_project_one_month
WHERE id BETWEEN current_id_main AND LEAST(current_id_main + batch_size - 1, max_id_main)
AND create_time >= last_migration_time;
SET batch_end_time = NOW();
SET elapsed_time = TIMESTAMPDIFF(MICROSECOND, batch_start_time, batch_end_time) / 1000000;
IF elapsed_time > 10 THEN
-- 如果一批插入时间超过 10 秒，记录警告日志
INSERT INTO migration_log (migration_time, view_name, target_table, record_count, status, message)
VALUES (NOW(), 'mr_project_one_month', 'mr_project_month_job_data', batch_size, 'WARNING', CONCAT('Batch insertion took ', elapsed_time, ' seconds'));
END IF;
-- 记录实际插入的数量
SET total_main_inserted = total_main_inserted + ROW_COUNT();
SET current_id_main = current_id_main + batch_size;
-- 较小事务，每批插入后提交
COMMIT;
END WHILE;
END IF;
-- 重置变量，准备迁移明细表数据
SET min_id_detail = 0;
SET max_id_detail = 0;
SET current_id_detail = 0;
-- 一次性获取明细表数据的 ID 范围和总记录数（只获取上次迁移后的新数据）
SELECT MIN(id), MAX(id), COUNT(*) INTO min_id_detail, max_id_detail, detail_count FROM mr_project_item_one_month WHERE create_time >= last_migration_time;
-- 迁移明细表数据（增量插入）
IF detail_count > 0 THEN
SET current_id_detail = min_id_detail;
WHILE current_id_detail <= max_id_detail DO
START TRANSACTION;
SET batch_start_time = NOW();
INSERT IGNORE INTO mr_project_item_month_job_data
SELECT *
FROM mr_project_item_one_month
WHERE id BETWEEN current_id_detail AND LEAST(current_id_detail + batch_size - 1, max_id_detail)
AND create_time >= last_migration_time;
SET batch_end_time = NOW();
SET elapsed_time = TIMESTAMPDIFF(MICROSECOND, batch_start_time, batch_end_time) / 1000000;
IF elapsed_time > 10 THEN
-- 如果一批插入时间超过 10 秒，记录警告日志
INSERT INTO migration_log (migration_time, view_name, target_table, record_count, status, message)
VALUES (NOW(), 'mr_project_item_one_month', 'mr_project_item_month_job_data', batch_size, 'WARNING', CONCAT('Batch insertion took ', elapsed_time, ' seconds'));
END IF;
-- 记录实际插入的数量
SET total_detail_inserted = total_detail_inserted + ROW_COUNT();
SET current_id_detail = current_id_detail + batch_size;
-- 较小事务，每批插入后提交
COMMIT;
END WHILE;
END IF;
-- 恢复唯一约束检查和外键约束检查
SET unique_checks = 1;
SET foreign_key_checks = 1;
-- 删除目标表中 30 天前的旧数据（使用更高效的删除方式）
DELETE FROM mr_project_month_job_data WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY);
DELETE FROM mr_project_item_month_job_data WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY);
-- 记录迁移日志（使用实际插入的数量）
INSERT INTO migration_log (migration_time, view_name, target_table, record_count, status)
VALUES (NOW(), 'mr_project_one_month', 'mr_project_month_job_data', total_main_inserted, 'SUCCESS');
INSERT INTO migration_log (migration_time, view_name, target_table, record_count, status)
VALUES (NOW(), 'mr_project_item_one_month', 'mr_project_item_month_job_data', total_detail_inserted, 'SUCCESS');
END$$
DELIMITER ;
这个调度创建后,执行开始是日志写入条数为100000条,但是实际只写入了一条,有时连一条都没有,日志中的状态全都是WARNING ,这个是什么情况,该怎么去解决,帮我处理一下
最新发布
本文介绍了如何使用voidset_time_limit(int seconds)函数来设定PHP程序的执行时间限制。默认情况下，PHP程序的执行时间限制为30秒，可以通过此函数进行调整。如果设置的时间为0，则表示取消执行时间的限制。
扫一扫
280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
