一次搞懂存储过程的高效编写与性能优化

项目初期的技术选型

这个系统是个后台数据统计平台，主要用来跑一些复杂的用户行为分析。前端是 Vue，后端是 Node.js，数据库用的是 MySQL。本来一开始我打算把所有逻辑都放在后端服务里，毕竟写 SQL 也不是我的强项，能躲就躲。

但后来发现，有些报表查询特别慢，一次请求要 join 六七张表，还要做聚合、分组、时间窗口计算，Node.js 层拼 SQL 字符串拼得我都想辞职。而且每次改个条件，代码就得重写一遍 where 子句，维护起来简直是噩梦。

后来 DBA 老哥建议我试试存储过程。他说：“你这需求就是典型的 OLAP 场景，放数据库里算比你在应用层拼快多了。” 我将信将疑，但实在被性能问题逼得没招了，干脆咬牙上了存储过程。

第一版：简单粗暴的实现

最开始写的那个存储过程特别 naive，就是一个输入参数，输出一个结果集。大概长这样：

DELIMITER //

CREATE PROCEDURE GetUserActivitySummary(
    IN start_date DATE,
    IN end_date DATE,
    IN user_type INT
)
BEGIN
    SELECT 
        u.id,
        u.name,
        COUNT(a.id) as action_count,
        SUM(a.duration) as total_duration,
        MAX(a.created_at) as last_active
    FROM users u
    LEFT JOIN user_actions a ON u.id = a.user_id 
        AND a.created_at BETWEEN start_date AND end_date
    WHERE u.type = user_type
    GROUP BY u.id, u.name
    ORDER BY action_count DESC;
END //

DELIMITER ;

看起来没问题对吧？本地测试跑得也挺快。但我忽略了一个关键点：这个过程会返回一个结果集，而 Node.js 的 mysql2 库默认不支持多结果集处理，尤其是当存储过程中有临时表、多个 SELECT 或者 SIGNAL 报错的时候。

我当时用的是 mysql2/promise，调用方式是这样的：

const conn = await pool.getConnection();
const [rows] = await conn.execute('CALL GetUserActivitySummary(?, ?, ?)', [startDate, endDate, userType]);
conn.release();

结果线上一跑，偶尔报错：Error: Result set not initialized。折腾了半天才发现，某些情况下存储过程执行路径不同，会导致结果集数量不稳定。

最大的坑：结果集和游标的混乱

真正让我头皮发麻的是第二个需求：要做一个分页的深度分析报告，每页显示 50 条用户，每条用户下面还要查他们最近 7 天的行为明细。我一开始想着用游标，在存储过程中循环处理每个用户，把明细拼成 JSON 返回。

代码大概是这样的：

-- 伪代码示意，实际更复杂
DECLARE done INT DEFAULT FALSE;
DECLARE uid INT;
DECLARE cur CURSOR FOR SELECT id FROM users WHERE ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

CREATE TEMPORARY TABLE _result (
    user_id INT,
    summary_data JSON
);

OPEN cur;
read_loop: LOOP
    FETCH cur INTO uid;
    IF done THEN
        LEAVE read_loop;
    END IF;

    -- 查询该用户的明细并构造成 JSON
    INSERT INTO _result (user_id, summary_data)
    SELECT uid, JSON_ARRAYAGG(JSON_OBJECT(...))
    FROM user_actions 
    WHERE user_id = uid AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
END LOOP;
CLOSE cur;

SELECT * FROM _result;

理想很丰满，现实很骨感。这种写法在并发稍微高一点的情况下，直接把数据库连接池打满了。原因有两个：

• 游标是阻塞的，每个连接只能串行处理
• 临时表在会话结束前不会释放，内存占用飙升

更惨的是，MySQL 的游标不支持动态 SQL，我想根据不同的参数调整查询结构，结果根本做不到。最后这个方案被彻底废弃了，整整三天白干。

最终的解决方案：退一步海阔天空

痛定思痛，我放弃了“全都在数据库里搞定”的执念。改成只用存储过程做核心聚合计算，返回主表数据，明细由前端按需懒加载。

新版本去掉了游标和临时表，改为纯 SELECT + 预计算字段：

DELIMITER $$

CREATE PROCEDURE GetPaginatedUserReport(
    IN page_offset INT,
    IN page_size INT,
    IN filter_type INT,
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    -- 添加简单缓存标记（实际项目中可结合 MEMOIZED 视图或 Redis）
    SET @row_start = page_offset * page_size;
    SET @limit = page_size;

    SET @query = '
        SELECT 
            u.id,
            u.name,
            u.register_date,
            COALESCE(stat.action_count, 0) as recent_actions,
            COALESCE(stat.avg_duration, 0) as avg_duration,
            stat.last_active
        FROM users u
        LEFT JOIN (
            SELECT 
                user_id,
                COUNT(*) as action_count,
                AVG(duration) as avg_duration,
                MAX(created_at) as last_active
            FROM user_actions 
            WHERE created_at BETWEEN ? AND ?
            GROUP BY user_id
        ) stat ON u.id = stat.user_id
        WHERE u.status = 1';

    IF filter_type IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND u.type = ', filter_type);
    END IF;

    SET @query = CONCAT(@query, ' ORDER BY stat.last_active DESC LIMIT ?, ?');

    PREPARE stmt FROM @query;
    SET @start = start_date;
    SET @end = end_date;
    EXECUTE stmt USING @start, @end, @row_start, @limit;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

注意这里用了预处理语句来拼接条件，虽然有点风险，但因为参数都是后端传入、经过校验的，SQL 注入可能性很低。而且避免了游标带来的性能灾难。

Node.js 调用时加了个小封装：

async function callStoredProcedure(procName, params) {
    const conn = await pool.getConnection();
    try {
        const placeholders = params.map(() => '?').join(',');
        const sql = CALL ${procName}(${placeholders});
        const [results] = await conn.query(sql, params);
        return Array.isArray(results) ? results : [results];
    } catch (err) {
        console.error('SP Error:', err.message);
        throw err;
    } finally {
        conn.release();
    }
}

效果评估：有得有失

上线之后，接口平均响应时间从原来的 8s+ 降到 1.2s 左右，TPS 提升明显。数据库 CPU 使用率确实高了些，但可以通过读写分离缓解。

做得好的地方：

• 复杂查询逻辑下沉，业务代码清爽很多
• 执行计划稳定，不像动态拼接容易走错索引
• DBA 可以直接优化，不用动应用代码

还有能优化的地方：

• 存储过程本身难调试，日志只能靠 SELECT 到临时表再查
• 版本管理麻烦，现在是靠 Git 手动同步 .sql 文件
• 部分场景仍需要 fallback 到 ORM 查询，一致性靠自己保证

还有一个遗留问题：当日期范围太大时（比如查一年的数据），还是会慢。目前的 workaround 是前端限制最大查询跨度为 90 天，超出提示“请缩小时间范围”。不是最优解，但够用。

踩坑总结：几条血泪经验

回顾整个过程，有几个点一定要提醒自己和大家：

• 别迷信“全放数据库”，尤其是带游标的逻辑，很容易成为瓶颈
• Node.js 调用存储过程一定要捕获多结果集，最好用 query 而不是 execute
• IN 参数可以，OUT 参数慎用，处理起来麻烦
• 不要在 SP 里做太多业务判断，它适合计算，不适合流程控制
• 记得设置 max_sp_recursion_depth，否则递归调用可能把库搞崩

另外，jztheme.com 上某个接口曾因未加 LIMIT 导致全表扫描，报警邮件刷屏。后来加了强制分页限制才稳住。

结尾碎碎念

以上是我在这个项目里用存储过程的完整经历。从一开始抗拒，到盲目上马，再到踩坑收手，最后找到平衡点。说到底，技术没有银弹，存储过程也不是万能药，但在特定场景下，它真的能救命。

这个方案谈不上多优雅，甚至有些妥协，但它解决了最紧迫的性能问题，也让后续迭代变得可控。有时候我觉得，前端出身的我写这些数据库代码像在裸泳，但也正是这些实战倒逼我补了不少后端知识。

如果你也在考虑是否使用存储过程，我的建议是：先问清楚是不是必须高性能 + 复杂计算 + 固定模式。如果是，值得一试；如果只是简单 CRUD，别给自己找罪受。

以上是我的踩坑总结，希望对你有帮助。如果有更优的实现方式，欢迎交流。