软件开发 --- 高效处理数据库表中平均值列
我要更新一亿条表数据,但是服务器内存不知10g,一次用临时表更新所有数据会导致mysql会话内存不足导致的断开连接。编写一个高效处理数据的方法。
-- 删除已存在的同名存储过程,如果存在的话 DROP PROCEDURE IF EXISTS update_monthly_avg_by_province; -- 更改语句结束符为 // 而非; 让 MySQL 将整个代码块视为一个完整的语句。 DELIMITER // -- 创建名为 update_monthly_avg_by_province 的存储过程 -- IN p_province VARCHAR(100): 输入参数,表示省份名称,最大长度100 CREATE PROCEDURE update_monthly_avg_by_province( IN p_province VARCHAR(100) ) BEGIN -- 声明变量 batch_size,并设置默认值为 50000,用于批量处理数据 DECLARE batch_size INT DEFAULT 50000; -- 声明变量 max_id,并设置默认值为 0,用于存储 agp_product_price 表中符合条件的最大 ID DECLARE max_id INT DEFAULT 0; -- 声明变量 processed,并设置默认值为 0,用于记录已处理的记录 ID 的起始点 DECLARE processed INT DEFAULT 0; -- 声明变量 start_time,用于记录存储过程开始执行的时间 DECLARE start_time DATETIME; -- 声明变量 end_time,用于记录存储过程结束执行或批次结束的时间 DECLARE end_time DATETIME; -- 声明变量 province_condition,用于存储动态生成的省份过滤条件SQL语句 DECLARE province_condition TEXT; -- 声明变量 sql_text,用于存储动态构建的SQL查询语句 DECLARE sql_text TEXT; -- 记录存储过程开始执行的时间 SET start_time = NOW(); -- 构建省份过滤条件 -- 如果 p_province 为 NULL 或者(忽略大小写)为 'ALL' IF p_province IS NULL OR UPPER(p_province) = 'ALL' THEN -- 设置省份条件为 '1=1',表示查询所有省份 SET province_condition = '1=1'; -- 输出日志:针对所有省份的存储过程已开始 SELECT CONCAT('Procedure started for ALL provinces at: ', start_time) AS log; ELSE -- 否则,构建特定省份的过滤条件,例如 'province = "四川省"' -- REPLACE(p_province, '"', '\"') 用于转义省份名称中的双引号,防止SQL注入或语法错误 SET province_condition = CONCAT('province = "', REPLACE(p_province, '"', '\"'), '"'); -- 输出日志:针对指定省份的存储过程已开始 SELECT CONCAT('Procedure started for province: ', p_province, ' at: ', start_time) AS log; END IF; -- 创建临时表用于存储月平均价格 -- 如果存在名为 temp_monthly_avg 的临时表,则删除它 DROP TEMPORARY TABLE IF EXISTS temp_monthly_avg; -- 创建名为 temp_monthly_avg 的临时表 CREATE TEMPORARY TABLE temp_monthly_avg ( city VARCHAR(64), -- 城市 province VARCHAR(64), -- 省份 product_name VARCHAR(64), -- 产品名称 region VARCHAR(64), -- 区域 stat_date VARCHAR(7), -- 统计月份,格式为 'YYYY-MM' avg_price DECIMAL(10,2), -- 月平均价格,保留两位小数 -- 设置联合主键,确保每个城市、省份、产品、区域、月份的组合是唯一的 PRIMARY KEY (city, province, product_name, region, stat_date) ) ENGINE=InnoDB; -- 使用 InnoDB 存储引擎 -- 动态构建SQL语句,用于计算月平均价格并插入到临时表 temp_monthly_avg -- 省份过滤:如果指定具体省份(如 p_province='四川省'),临时表数据量会显著减少。 -- InnoDB缓冲池:临时表数据可能部分缓存在内存中,部分写入磁盘临时文件(取决于 tmp_table_size 和 max_heap_table_size 参数)。 SET @sql_text = CONCAT( 'INSERT INTO temp_monthly_avg ', 'SELECT ', ' city, ', ' province, ', ' product_name, ', ' region, ', ' DATE_FORMAT(date, ''%Y-%m'') AS stat_date, ', -- 将日期格式化为 'YYYY-MM' 作为统计月份 ' ROUND(AVG(price_value), 2) AS avg_price ', -- 计算平均价格并四舍五入到两位小数 'FROM ', ' agp_product_price ', -- 从 agp_product_price 表中查询 'WHERE ', ' ', province_condition, ' ', -- 应用之前构建的省份过滤条件 'GROUP BY ', ' city, province, product_name, region, DATE_FORMAT(date, ''%Y-%m'')' -- 按城市、省份、产品、区域、统计月份分组 ); -- 准备执行动态SQL语句 PREPARE stmt FROM @sql_text; -- 执行动态SQL语句 EXECUTE stmt; -- 释放预处理语句 DEALLOCATE PREPARE stmt; -- 输出日志:月平均价格计算完成,并显示插入到临时表的行数 SELECT CONCAT('Monthly averages calculated. Row count: ', ROW_COUNT()) AS log; -- 获取符合省份条件的 agp_product_price 表中的最大 ID,用于后续批量更新 -- 如果存在名为 temp_result 的临时表,则删除它 DROP TEMPORARY TABLE IF EXISTS temp_result; -- 创建名为 temp_result 的临时表,只包含一个 INT 类型的列 val CREATE TEMPORARY TABLE temp_result(val INT); -- 动态构建SQL语句,用于查询最大 ID SET @sql_text = CONCAT( 'INSERT INTO temp_result(val) SELECT MAX(id) FROM agp_product_price WHERE ', province_condition ); -- 准备执行动态SQL语句 PREPARE stmt FROM @sql_text; -- 执行动态SQL语句 EXECUTE stmt; -- 释放预处理语句 DEALLOCATE PREPARE stmt; -- 从 temp_result 表中获取查询到的最大 ID 并赋值给 max_id 变量 SELECT val INTO max_id FROM temp_result; -- 删除临时表 temp_result DROP TEMPORARY TABLE temp_result; -- 输出日志:找到的最大 ID SELECT CONCAT('Max ID found: ', max_id) AS log; -- 开始批量更新 agp_product_price 表的 average_value 字段 SELECT 'Starting batch updates...' AS log; -- 当已处理的记录 ID 小于等于最大 ID 时,循环执行批量更新 WHILE processed <= max_id DO -- 动态构建批量更新的SQL语句 SET @sql_text = CONCAT( 'UPDATE agp_product_price t ', -- t 是 agp_product_price 表的别名 'JOIN temp_monthly_avg a ON ', -- a 是 temp_monthly_avg 表的别名 ' t.city = a.city AND ', ' t.province = a.province AND ', ' t.product_name = a.product_name AND ', ' t.region = a.region AND ', ' DATE_FORMAT(t.date, ''%Y-%m'') = a.stat_date ', -- 连接条件:城市、省份、产品、区域、统计月份均匹配 'SET ', ' t.average_value = a.avg_price ', -- 将临时表中的月平均价格更新到主表的 average_value 字段 'WHERE ', ' t.id BETWEEN ', processed, ' AND ', processed + batch_size - 1, ' ', -- 当前批次处理的 ID 范围 ' AND ', province_condition -- 应用省份过滤条件 ); -- 准备执行动态SQL语句 PREPARE stmt FROM @sql_text; -- 执行动态SQL语句 EXECUTE stmt; -- 释放预处理语句 DEALLOCATE PREPARE stmt; -- 输出当前批次更新的进度 SET end_time = NOW(); -- 获取当前时间作为批次结束时间 SELECT CONCAT( 'Batch ', processed, '-', LEAST(processed + batch_size - 1, max_id), -- 显示当前批次的 ID 范围 ' updated. Time elapsed: ', TIMESTAMPDIFF(SECOND, start_time, end_time), ' sec' -- 显示从过程开始到当前批次结束所经过的秒数 ) AS progress; -- 更新 processed 变量,使其指向下一个批次的起始 ID SET processed = processed + batch_size; END WHILE; -- 结束批量更新循环 -- 清理:删除临时表 temp_monthly_avg DROP TEMPORARY TABLE IF EXISTS temp_monthly_avg; -- 记录存储过程完成的时间 SET end_time = NOW(); -- 输出日志:存储过程执行完毕,并显示总耗时 SELECT CONCAT( 'Procedure completed! Total time: ', TIMESTAMPDIFF(SECOND, start_time, end_time), ' seconds' ) AS final_log; -- 更新统计信息:查询更新后符合条件的记录总数和不同的组数 SET @sql_text = CONCAT( 'SELECT ', ' COUNT(*) AS total_updated_records, ', -- 计算 average_value 不为空的总记录数 ' COUNT(DISTINCT CONCAT(city, ''|'', DATE_FORMAT(date, ''%Y-%m''))) AS distinct_groups_updated ', -- 计算更新过的不同城市和月份组合的数量 'FROM agp_product_price ', 'WHERE average_value IS NOT NULL AND ', province_condition -- 过滤条件:average_value 不为空且符合省份条件 ); -- 准备执行动态SQL语句 PREPARE stmt FROM @sql_text; -- 执行动态SQL语句 EXECUTE stmt; -- 释放预处理语句 DEALLOCATE PREPARE stmt; END // -- 存储过程定义结束 -- 将语句结束符改回默认的 ; DELIMITER ;