怎么优化 这个执行函数
CREATE DEFINER=`root`@`%` PROCEDURE `sync_customer_reconstruction`()
BEGIN
#Routine body goes here...
-- 定义批次大小和起始点
SET @batch_size = 10000;
SET @min_id = (SELECT MIN(customer_id) FROM t_customer_info where delmark = 1);
SET @max_id = (SELECT MAX(customer_id) FROM t_customer_info where delmark = 1);
-- 循环处理每个批次
WHILE @min_id <= @max_id DO
START TRANSACTION;
-- 复制当前批次数据到新表
INSERT INTO customer_basic_info_sync1(
id,
customer_name,
customer_tel,
customer_num,
is_box,
open_id,
first_customer_num,
shop_id,
private_customer_id,
platform,
first_arrival_date,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
customer_id,
customer_name,
customer_tel,
customer_num,
is_box,
open_id,
first_customer_num,
agent_id,
case when hq_agent_id =0 then null else hq_agent_id end as private_customer_id,
platform,
customer_date,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_stat_info_sync1 (
customer_id,
upgrade_plan,
rendering_count,
order_count,
order_price,
consume_price,
arrival_count,
first_buy_time,
last_buy_time,
last_active_time,
refund_exists,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
customer_id,
upgrade_plan,
rendering_count,
order_count,
order_price,
consume_price,
arrival_count,
first_buy_time,
last_buy_time,
last_active_time,
refund_exists,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_product_branch_stats_sync1 (
customer_id,
type,
count,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
CUSTOMER_ID AS CUSTOMER_ID,
types.type,
CASE types.type
WHEN '1' THEN wuze_branch_count
WHEN '2' THEN fangtuo_branch_count
WHEN '3' THEN donggan_branch_count
WHEN '4' THEN cc_branch_count
END AS count,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info
CROSS JOIN (
-- 构建类型映射表
SELECT '1' AS type
UNION ALL SELECT '2'
UNION ALL SELECT '3'
UNION ALL SELECT '4'
) AS types
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_hair_profile_sync1 (
customer_id,
question,
hair_volume,
hair_condition,
willingness_to_improve,
white_hair_count,
frequency,
hair_loss_count,
grey_time,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
customer_id,
question,
hair_volume,
hair_condition,
willingness_to_improve,
white_hair_count,
frequency,
hair_loss_count,
grey_time,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_status_info_sync1 (
customer_id,
major,
activate,
major_date,
is_scan,
customer_level,
old_customer_time,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
t.customer_id,
t.major,
t.activate,
t.major_date,
is_scan,
customer_level,
old_customer_time,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info t
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_follow_info_sync1 (
customer_id,
follow_user_id,
check_user_id,
voice_record_user_id,
voice_record_date,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
customer_id,
follow_user_id,
check_user_id,
voice_record_user_id,
voice_record_date,
create_time,
create_by,
update_time,
update_by,
delmark
FROM t_customer_info
WHERE customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and delmark = 1;
INSERT INTO customer_ext_info (
customer_id,
sex,
customer_age,
birthday_type,
birthday,
travel_mode,
car_model,
marital_status,
physiology,
hobby,
home_info,
beauty_items,
care_preference,
taboo,
address,
occupation,
improvement_rate,
remark,
create_time,
create_by,
update_time,
update_by,
delmark
)
SELECT
t.customer_id,
sex,
customer_age,
birthday_type,
birthday,
travel_mode,
car_model,
marital_status,
physiology,
hobby,
home_info,
beauty_items,
care_preference,
taboo,
address,
occupation,
improvement_rate,
c.remark,
c.create_time,
c.create_by,
c.update_time,
c.update_by,
c.delmark
FROM t_customer_info t
LEFT JOIN t_customer_ext_info c on c.customer_id = t.customer_id and c.delmark = 1
WHERE t.customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 and t.delmark = 1;
-- 数据一致性校验：检查行数是否匹配
SET @basic_info_count = (SELECT COUNT(1) FROM customer_basic_info_sync1 WHERE delmark = 1 AND id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @stat_info_count = (SELECT COUNT(1) FROM customer_stat_info_sync1 WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @product_branch_stats_count = (SELECT count(1) from ( select customer_id FROM customer_product_branch_stats_sync1 WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1 GROUP BY customer_id) c );
SET @hair_profile_count = (SELECT COUNT(1) FROM customer_hair_profile_sync1 WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @status_info_count = (SELECT COUNT(1) FROM customer_status_info_sync1 WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @follow_info_count = (SELECT COUNT(1) FROM customer_follow_info_sync1 WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @ext_info_count = (SELECT COUNT(*) FROM customer_ext_info WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
SET @original_count = (SELECT COUNT(*) FROM t_customer_info WHERE delmark = 1 AND customer_id BETWEEN @min_id AND @min_id + @batch_size - 1);
-- 如果校验失败，回滚事务
IF
@basic_count != @original_count OR
@stat_info_count != @original_count OR
@product_branch_stats_count != @original_count OR
@hair_profile_count != @original_count OR
@status_info_count != @original_count OR
@ext_info_count != @original_count OR
@follow_info_count != @original_count
THEN ROLLBACK;
SELECT @min_id,@min_id + @batch_size - 1,'This is a log error message' AS LogMessage;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Data inconsistency detected, rolled back batch';
ELSE
COMMIT;
END IF;
-- 更新批次起始点
SET @min_id = @min_id + @batch_size;
END WHILE;
-- 释放表锁
-- UNLOCK TABLES;
END
本文介绍如何通过命令行创建卷组，包括创建目录、控制文件、指定物理卷等步骤，并验证创建结果。
扫一扫
2754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
