存储过程的三种方式

期望实现下面功能：
//编写一个存储过程 proc_tj_merchant_balance_day

//遍历 merchant //select id,name,balance from merchant where ext1 = ‘1’;

//select sum(amount-merchant_fee)unsettleAmount from payment_flow where create_time>DATE_ADD(now(), INTERVAL -3 DAY) and status=’SUCCESS’ and merchant_id=#merchantId# and length(settlement_id)<10;

//将结果插入merchant_balance_day //insert merchant_balance_day(tj_day,merchant_id,merchant_name,balance,unsettle_amount) values(DATE(DATE_SUB(NOW(), INTERVAL 1 HOUR), id,name,balance, unsettleAmount);

第一种方式：

编写存储过程 proc_tj_merchant_balance_day。这个存储过程将遍历 merchant 表中的所有商户，并计算每个商户在最近3天内未结算的金额，然后将结果插入到 merchant_balance_day 表中。

在编写过程中，会用到游标来遍历 merchant 表中的记录，并在每次游标迭代中进行相应的计算和插入操作。

以下是完整的存储过程代码：

DELIMITER $$

DROP PROCEDURE IF EXISTS proc_tj_merchant_balance_day$$

CREATE PROCEDURE proc_tj_merchant_balance_day()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE merchantId INT;
    DECLARE merchantName VARCHAR(255);
    DECLARE merchantBalance DECIMAL(10, 2);
    DECLARE unsettleAmount DECIMAL(10, 2);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT id, name, balance
        FROM merchant
        WHERE ext1 = '1';

    -- 声明处理程序用于游标结束
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 打开游标
    OPEN cur;

    read_loop: LOOP
        -- 获取游标中的数据
        FETCH cur INTO merchantId, merchantName, merchantBalance;

        -- 检查游标是否已经读取完毕
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 计算未结算金额
        SELECT SUM(amount - merchant_fee)
        INTO unsettleAmount
        FROM payment_flow
        WHERE create_time > DATE_ADD(NOW(), INTERVAL -3 DAY)
              AND status = 'success'
              AND merchant_id = merchantId
              AND LENGTH(settlement_id) < 10;

        -- 如果没有找到记录，将 unsettleAmount 设为 0
        IF unsettleAmount IS NULL THEN
            SET unsettleAmount = 0;
        END IF;

        -- 将结果插入到 merchant_balance_day 表
        INSERT INTO merchant_balance_day (tj_day, merchant_id, merchant_name, balance, unsettle_amount)
        VALUES (DATE(DATE_SUB(NOW(), INTERVAL 1 HOUR)), merchantId, merchantName, merchantBalance, unsettleAmount);
    
    END LOOP;

    -- 关闭游标
    CLOSE cur;
END$$

DELIMITER ;

解释：

声明部分：
- done 用于指示游标读取的结束。
- merchantId、merchantName、merchantBalance 用于存储游标中读取的值。
- unsettleAmount 用于存储未结算金额的计算结果。
游标声明：
- 声明一个游标 cur 用于遍历 merchant 表中满足条件（ext1 = '1'）的记录。
处理程序声明：
- 声明一个处理程序，遇到 NOT FOUND 条件（游标读取完毕）时，将 done 设置为 1。
游标打开和读取：
- 打开游标。
- 在循环中逐条读取商户记录。如果 done 标志被设置为 1（即读取完毕），则离开循环。
- 在每次循环中，计算最近3天内未结算的金额。
未结算金额计算：
- 使用 SELECT SUM(amount - merchant_fee) 来计算最近3天内的未结算金额。如果没有记录符合条件，unsettleAmount 将被设置为 NULL，在这种情况下我们将其设置为 0。
插入结果：
- 将计算结果插入到 merchant_balance_day 表中。

通过以上步骤，我们创建了一个存储过程 proc_tj_merchant_balance_day，该过程会遍历 merchant 表中的所有商户，计算每个商户的未结算金额，并将结果插入到 merchant_balance_day 表中。

第二种方式：

对于优化和简化存储过程的编写，我们可以考虑以下方面：

减少游标的使用：游标在某些情况下效率不高，特别是在大数据量下。尽可能使用单个查询来替代游标。
并行处理：如果数据库支持并行处理，可以上线并行插入操作。
简化逻辑：确保代码清晰易读，并使用适当的变量和聚合函数。

在这个场景中，我们可以尝试通过使用子查询的方式，避免游标的使用，并直接通过一条插入语句完成所需的操作。

以下是简化后的存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS proc_tj_merchant_balance_day$$

CREATE PROCEDURE proc_tj_merchant_balance_day()
BEGIN
    -- 通过一个 INSERT INTO ... SELECT 语句来完成插入操作
    INSERT INTO merchant_balance_day (tj_day, merchant_id, merchant_name, balance, unsettle_amount)
    SELECT
        DATE(DATE_SUB(NOW(), INTERVAL 1 HOUR)),
        m.id,
        m.name,
        m.balance,
        COALESCE((
            SELECT SUM(pf.amount - pf.merchant_fee)
            FROM payment_flow pf
            WHERE pf.create_time > DATE_ADD(NOW(), INTERVAL -3 DAY)
              AND pf.status = 'SUCCESS'
              AND pf.merchant_id = m.id
              AND LENGTH(pf.settlement_id) < 10
        ), 0) AS unsettleAmount
    FROM
        merchant m
    WHERE
        m.ext1 = '1';
END$$

DELIMITER ;

解释：

INSERT INTO … SELECT：
- 通过一个 INSERT INTO ... SELECT 语句直接将结果插入到 merchant_balance_day 表中，避免了游标的使用。
- tj_day 是通过 DATE(DATE_SUB(NOW(), INTERVAL 1 HOUR)) 计算得出，即为当前时间减去一个小时，并格式化为日期部分。
子查询计算未结算金额：
- 利用子查询来计算最近3天内未结算的金额。通过 SUM(pf.amount - pf.merchant_fee) 得到总的未结算金额，如果没有记录符合条件，则用 COALESCE 函数让其返回 0。
主查询：
- 主查询从 merchant 表中选择需要处理的商户（ext1 = '1'），并加入子查询的结果。

通过这种方法，我们简化了存储过程的逻辑，避免了游标的使用，并利用单个查询实现了所需的功能。这不仅提高了代码的可读性，而且在大数据量下的性能也有所提升。

第三种方式：

============================================================
=================临时表提供性能================================
============================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS proc_tj_merchant_balance_day$$

CREATE PROCEDURE proc_tj_merchant_balance_day()
BEGIN
    -- 插入计算结果到临时表中，用于优化性能
    CREATE TEMPORARY TABLE temp_unsettle_amount AS
    SELECT
        pf.merchant_id,
        SUM(pf.amount - pf.merchant_fee) AS unsettleAmount
    FROM
        payment_flow pf
    WHERE
        pf.create_time > DATE_ADD(NOW(), INTERVAL -3 DAY) AND
        pf.status = 'SUCCESS' AND
        LENGTH(pf.settlement_id) < 10
    GROUP BY
        pf.merchant_id;

    -- 插入最终结果到目标表中
    INSERT INTO merchant_balance_day (tj_day, merchant_id, merchant_name, balance, unsettle_amount)
    SELECT
        DATE(DATE_SUB(NOW(), INTERVAL 1 HOUR)),
        m.id,
        m.name,
        m.balance,
        COALESCE(tua.unsettleAmount, 0) AS unsettleAmount
    FROM
        merchant m
    LEFT JOIN
        temp_unsettle_amount tua ON m.id = tua.merchant_id
    WHERE
        m.ext1 = '1';

    -- 清理临时表
    DROP TEMPORARY TABLE temp_unsettle_amount;
END$$

DELIMITER ;

解释：

临时表：
- 创建临时表 temp_unsettle_amount 来存储未结算金额。这优化了查询性能，因为大表扫描和计算会在创建临时表时一次性完成，而主查询只需要访问临时表。
插入操作：
- 插入操作使用 LEFT JOIN 来将商户信息与临时表中存储的未结算金额关联起来。COALESCE 函数确保即使没有找到未结算金额，unsettle_amount 也会设置为 0。
清理：
- 最后删除临时表，确保不会占用临时存储空间。

其他优化建议

监控和分析：使用工具如 MySQL 的 EXPLAIN 命令来分析查询计划，识别性能瓶颈，并根据分析结果进行优化。
硬件资源：确保硬件资源（如CPU、内存、I/O）充足，数据库性能常常会受到硬件限制影响。
分库分表（Sharding）：如果单表数据量过大，考虑进行分库分表，将数据分布在多台数据库服务器上，提高并发处理能力。

通过这个优化过程，应该能够显著提升存储过程 proc_tj_merchant_balance_day 的执行效率。

解释：

解释：

解释：

其他优化建议

发表评论取消回复

发布者

cfyme

解释：

解释：

解释：

其他优化建议

发表评论 取消回复

发布者

cfyme

发表评论取消回复