软件开发 --- 高效处理数据库表中平均值列

我要更新一亿条表数据，但是服务器内存不知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 ;