写这篇东西之前我先说下背景。去年下半年接了一个老项目的季度迭代业务方提了一大堆报表需求月度订单汇总、用户消费排名、结算金额复核、定时对账……一套组合拳下来接口层直接开始堆SQL各种子查询套子查询日志里偶尔还出现slow query告警。后来实在顶不住我把几个核心聚合逻辑挪进了存储过程配合MyBatis-Plus把调用链路打通效果立竿见影。今天就把这轮的沉淀整理成文——前半部分是MySQL存储过程绕不开的知识点后半部分是从Mapper接口到XML配置的完整调用写法附带我在真实项目里踩过的几个坑。这篇内容适合谁看正在用Spring Boot MyBatis-Plus做业务开发、被复杂SQL和报表查询折磨的朋友以及想把存储过程从听说过变成敢上手用的同学。读完你能直接照搬调用模板也能避开我在调试、性能、事务上踩过的那些效率陷阱。1. 为什么我在业务系统里重新捡起存储过程这个话题得从旧系统的痛点说起。我的直观感受是很多互联网团队对存储过程避之不及理由是不好维护难以调试数据库压力大。这些话有道理但不分场景一刀切就会像我之前那样得不偿失——明明一条复杂聚合能在数据库端几秒算完偏要在Java内存里拆成七八次查询再手动merge结果接口超时SQL日志刷屏数据库连接被长时间占用。1.1 被报表和批量更新折磨之后项目里最典型的场景是月度对账需要先查订单主表再关联支付流水按用户分组后统计金额最后还要把结果写回一张统计表。按传统Java做法你得写两三个方法循环里再发查询一组数据跑下来接口耗时经常超过3秒这还是数据量只有几十万的情况下。如果把这段逻辑写进存储过程数据不离开数据库聚合、分组、写回一步到位接口只管调用一次耗时直接压到几百毫秒。再一个是批量更新。比如每天凌晨要把超过30天未支付的订单统一标记为已取消同时生成一条操作流水。用Java处理要先查出来、再循环更新、再插流水表事务还容易拆散。存储过程里一条UPDATE加一条INSERT就能搞定配合事务边界要么全成要么全毁。1.2 存储过程和普通SQL各自的边界我的经验是简单查询永远直接写SQL让索引和优化器说话但遇到下面这些情况存储过程确实更能打数据需要按多步骤、多临时结果的顺序处理中间结果还要多次复用。一个业务动作需要连续执行多条DML且对原子性要求高希望整块逻辑在一个事务里提交。复杂的对账、结存、排行榜计算需要大量游标遍历和条件分支。希望把业务规则收敛在数据库层多个应用端比如后端服务和批处理任务能复用同一段逻辑。但也要泼一盆冷水存储过程解决不了索引设计问题。你在过程里写的每一条SELECT、UPDATE仍然要遵守数据库的索引规则。把慢SQL藏进存储过程只会让慢查询更难被发现和优化。1.3 为什么不是所有东西都塞进数据库我在项目里定了一个内部规矩凡是要在代码里频繁拼接、反复变更的过滤条件不要写进存储过程因为动态SQL虽然能做但可读性和可维护性断崖式下跌。凡是涉及外部服务调用、消息队列的流程也不要往里塞。存储过程最舒服的区间就是纯数据库内的数据加工任务配合定时器或消息触发能拿回一大半性能又不至于把业务逻辑全绑死在数据库上。2. 存储过程不变量核心语法与参数模型真正把存储过程用起来之前有几个语法点是绕不过去的。下面这些内容不是我翻文档抄的是这几个月里反复写错、反复纠正后留下的精华。2.1 存储过程的整体结构与参数模式一个基本的存储过程框架长这样DELIMITER $$ CREATE PROCEDURE p_demo( IN p_shop_id BIGINT, OUT p_total INT, INOUT p_extra DECIMAL(10,2) ) BEGIN -- 变量、游标、异常处理都写在这里 SET p_total 0; SELECT COUNT(*) INTO p_total FROM t_order WHERE shop_id p_shop_id; END$$ DELIMITER ;这里的DELIMITER不是存储过程语法的一部分它只是告诉MySQL客户端后面的分号不要立刻执行等END之后再一起提交。很多人第一次写存储过程报语法错误八成是把DELIMITER这行忘了。参数模式有三种必须分清楚模式语义常见使用场景IN入参过程内不能修改传入时间范围、用户ID、状态值OUT出参调用方在过程结束后读取返回统计个数、累计金额、结果状态INOUT既能传进去也能把结果带出来累加器、计数器的场景写调用方的时候IN参数要填值OUT参数通常填一个占位变量或者直接在Java侧声明过程执行完再取值。INOUT参数比较容易被忽略的是它在进入过程之前就必须有一个初始值。2.2 变量、控制流与SELECT INTO过程体内用DECLARE声明局部变量用SET赋值。最实用的赋值方式是SELECT ... INTO它能把查询结果直接塞进变量DECLARE v_cnt INT DEFAULT 0; DECLARE v_amount DECIMAL(12,2) DEFAULT 0; SELECT COUNT(*), COALESCE(SUM(pay_amount), 0) INTO v_cnt, v_amount FROM t_payment WHERE order_id p_order_id;注意SELECT INTO要求查询结果必须是一行多行会报错零行时变量会保持默认值不变。做聚合统计时推荐用COALESCE包一层避免SUM结果为空导致变量被置NULL。条件分支上我常用的两种写法。判断类用IF ... ELSEIF ... ELSE ... END IFIF p_only_paid 1 THEN -- 只统计已支付 ELSEIF p_only_paid 2 THEN -- 统计已支付已退款 ELSE -- 全部 END IF;循环类最常用WHILE ... DO ... END WHILE适合先判断后执行WHILE v_i 12 DO SET v_total v_total v_i; SET v_i v_i 1; END WHILE;2.3 游标的本质与遍历方式游标是对结果集的逐行访问机制。拿业务打比方普通SQL像一次搬一车砖游标则是一块一块搬慢但灵活。我通常在需要逐行读取结果集并对每行做不同处理时用游标比如给用户排名、逐项生成明细。游标的使用是一个固定的五步流程声明、打开、循环取数、关闭。核心难点在循环的终止条件社区里最常见的写法是用一个done标志DECLARE v_done INT DEFAULT 0; DECLARE cur_top CURSOR FOR SELECT user_id, COUNT(*) FROM t_order GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 10; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur_top; read_loop: LOOP FETCH cur_top INTO v_user_id, v_cnt; IF v_done 1 THEN LEAVE read_loop; END IF; -- 对当前行做处理 END LOOP; CLOSE cur_top;这里有个非常隐蔽的坑CONTINUE HANDLER需要在变量声明之后、游标使用之前声明顺序错了直接报错。另外游标一旦打开迟迟不关闭会占用连接资源务必在循环结束后执行CLOSE。如果存储过程里有多个游标建议统一遵循打开立即配一个出口关闭的规范。2.4 异常处理不只是TRY-CATCH存储过程的异常机制在MySQL里叫HANDLER声明格式是DECLARE ... HANDLER FOR ...。我平时最常用两种-- 遇到任何SQL异常回滚并重新抛出 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; -- 查询没有数据时不中断置一个标志继续走 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1;EXIT表示发生异常后立刻跳出当前BEGIN块CONTINUE表示继续执行下一句。做数据加工时我喜欢把每个子步骤包在单独的BEGIN ... END块里这样能精确控制哪一步出问题就回滚到哪一步而不是整个过程折叠成一个大事务出了问题反而不好定位。如果想把自定义错误抛给调用方用SIGNALSIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 月份参数不能为空;45000是用户自定义异常的约定SQLSTATE值调用端会收到一条异常信息排错效率比自己翻日志高很多。2.5 动态SQL灵活性的代价存储过程需要动态拼接表名、字段名或排序规则时就得用到PREPAREEXECUTE。举个最典型的例子按月份查询不同分区的表或者排序字段由参数控制。SET sql CONCAT( SELECT * FROM t_order_, p_month, WHERE status ? ORDER BY create_time LIMIT ? ); PREPARE stmt FROM sql; SET status 2; SET limit 100; EXECUTE stmt USING status, limit; DEALLOCATE PREPARE stmt;两个经验第一动态SQL无法使用存储过程的局部变量必须用符号开头的用户变量第二动态SQL是SQL注入的重灾区即使是内部系统也要对传入的字符串做白名单校验。能用静态SQL解决就不要动态拼付出的维护成本往往比省下的几行代码贵得多。3. 订单月度统计一个完整的存储过程实战纸上谈兵到这里必须上真案例。我模拟了一个订单统计需求流程完整覆盖了IN/OUT参数、变量、分支、临时表和游标而且和后面的MyBatis-Plus调用直接对应。3.1 业务需求描述与表结构需求一句话入参是月份和统计范围输出该月的总订单数、总支付金额并把消费金额最高的前10个用户写入排行表同时返回排行结果集。表结构简化如下CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, pay_amount DECIMAL(12,2) DEFAULT 0, status TINYINT NOT NULL COMMENT 1未支付 2已支付 3已取消, create_time DATETIME NOT NULL ); CREATE TABLE t_user_rank ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, stat_month VARCHAR(7) NOT NULL, order_count INT DEFAULT 0, total_amount DECIMAL(12,2) DEFAULT 0 );这是一个非常典型的数据加工模型主表只存流水统计结果单独落一张排行表报表直接查排行表避免每次打开报表都全表扫一次订单。3.2 存储过程代码逐段解析下面是完整实现我把关键注释直接写进代码里DELIMITER $$ CREATE PROCEDURE p_order_monthly_stats( IN p_month VARCHAR(7), IN p_only_paid TINYINT, OUT p_total_orders INT, OUT p_total_amount DECIMAL(12,2) ) BEGIN -- 1. 变量与临时表声明区 DECLARE v_user_id BIGINT; DECLARE v_amount DECIMAL(12,2); -- 2. 异常处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; -- 3. 临时表存储排名结果方便后续输出结果集 DROP TEMPORARY TABLE IF EXISTS tmp_rank_result; CREATE TEMPORARY TABLE tmp_rank_result ( user_id BIGINT, order_count INT, total_amount DECIMAL(12,2) ) ENGINE InnoDB; -- 4. 主统计注意用时间范围替代DATE_FORMAT保住索引 SELECT COUNT(*), COALESCE(SUM(pay_amount), 0) INTO p_total_orders, p_total_amount FROM t_order WHERE create_time CONCAT(p_month, -01) AND create_time DATE_ADD(CONCAT(p_month, -01), INTERVAL 1 MONTH) AND (p_only_paid 0 OR status 2); -- 5. 排行榜写入 INSERT INTO t_user_rank (user_id, stat_month, order_count, total_amount) SELECT user_id, p_month, COUNT(*), SUM(pay_amount) FROM t_order WHERE create_time CONCAT(p_month, -01) AND create_time DATE_ADD(CONCAT(p_month, -01), INTERVAL 1 MONTH) AND (p_only_paid 0 OR status 2) GROUP BY user_id ORDER BY SUM(pay_amount) DESC LIMIT 10; -- 6. 把相同的结果集放入临时表作为额外结果集返回给调用方 INSERT INTO tmp_rank_result (user_id, order_count, total_amount) SELECT user_id, order_count, total_amount FROM t_user_rank WHERE stat_month p_month; -- 7. 直接把临时表内容作为结果集返回 SELECT user_id, order_count, total_amount FROM tmp_rank_result; DROP TEMPORARY TABLE IF EXISTS tmp_rank_result; END$$ DELIMITER ;这里有一个我花了很长时间才悟透的设计输出参数只承载聚合值排行榜通过第二结果集返回。因为MyBatis-Plus在调用存储过程时对SELECT结果集和OUT参数的读取路径不同——结果集走resultType映射OUT参数走modeOUT绑定两者分开处理反而最干净。3.3 数据验证两个容易踩的坑第一个坑是月份过滤。我最初写的是WHERE DATE_FORMAT(create_time, %Y-%m) p_month功能上完全正确但在create_time上有索引的业务表里这个写法会让索引失效因为函数包裹了字段。改成 月初 AND 下月初的范围写法后执行计划立刻变好。第二个坑是临时表的重复创建。存储过程被连续调用时上次的临时表可能还没被自动清理干净所以我在创建之前显式执行DROP TEMPORARY TABLE IF EXISTS。MySQL的临时表是会话级隔离的同一个连接下上次调用残留的临时表如果不主动清理第二次创建同名表会报错。4. MyBatis-Plus集成从Mapper接口到XML参数绑定存储过程写好只是第一步真正让项目跑起来的是应用层的调用。这节给完整链路从环境依赖到Service层取值都覆盖。4.1 环境准备与依赖我用的技术栈是Spring Boot 2.7 MyBatis-Plus 3.5.x MySQL 8.0。依赖只需要在原有基础上确保有mybatis-plus-boot-starter即可调用存储过程不需要额外引入任何包MyBatis原生支持。关键配置在application.yml里mybatis-plus: mapper-locations: classpath:/mapper/**/*.xml configuration: map-underscore-to-camel-case: truemapper-locations这个配置经常被遗漏。Mapper接口方法如果没有对应的XML实现运行时会报Invalid bound statement原因就是XML没被扫描到。4.2 Mapper接口定义为什么推荐Map参数模型MyBatis-Plus的BaseMapper内置方法都是针对单表CRUD的调用存储过程属于自定义SQL直接在Mapper里扩展方法即可。我的推荐写法是用MapString, Object作为入参和出参载体原因是存储过程的IN/OUT参数混在一起用实体类接收时每次调用都要新建DTO而Map在参数较多时更直观Mapper public interface OrderStatsMapper { MapString, Object callMonthlyStats(MapString, Object params); }如果项目规范要求参数强类型也可以定义DTO但要保证字段名和XML里的modeOUT参数名能一一对应。这个映射关系非常隐晦DTO字段名和存储过程OUT参数名只要有一个字母对不上结果就是NullPointer排查起来很费劲。Map模型没有这个问题。4.3 XML映射CALLABLE类型才是关键Mapper接口的XML对应写法如下核心是statementTypeCALLABLE。这行配置决定了MyBatis用CallableStatement的方式执行而不是普通的PreparedStatement?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.mapper.OrderStatsMapper select idcallMonthlyStats statementTypeCALLABLE parameterTypemap resultTypemap {call p_order_monthly_stats( #{month, modeIN, jdbcTypeVARCHAR}, #{onlyPaid, modeIN, jdbcTypeINTEGER}, #{totalOrders, modeOUT, jdbcTypeINTEGER}, #{totalAmount, modeOUT, jdbcTypeDECIMAL} )} /select /mapper几个参数细节按优先级列一下jdbcType必须写。尤其是DECIMAL如果省略MySQL驱动在某些版本下会把金额识别歪导致精度丢失。modeIN和modeOUT要严格对应存储过程的形参定义不能调换位置。只读结果集用resultTypemap框架会把存储过程的第一个结果集映射成List of Map返回。另外我的存储过程实际上返回了排行结果集所以这个select执行完会拿到一个结果列表而OUT参数totalOrders和totalAmount则会被塞进返回的Map里。因此Mapper方法返回MapString, Object既要装OUT参数同时也因为MyBatis的返回值约定最终返回的其实是一个结果上下文。具体怎么拆看Service层就清楚了。4.4 Service层怎么把OUT参数和结果集都取出来调用方的完整写法如下Service public class OrderStatsService { private final OrderStatsMapper orderStatsMapper; public OrderStatsService(OrderStatsMapper orderStatsMapper) { this.orderStatsMapper orderStatsMapper; } public void monthlyStats(String month, Integer onlyPaid) { MapString, Object params new HashMap(); params.put(month, month); params.put(onlyPaid, onlyPaid); // 调用存储过程执行后OUT参数会回写到返回的Map里 MapString, Object result orderStatsMapper.callMonthlyStats(params); // 读取OUT参数 Integer totalOrders ((Number) result.get(totalOrders)).intValue(); BigDecimal totalAmount (BigDecimal) result.get(totalAmount); // 读取排行榜结果集第一条结果集 SuppressWarnings(unchecked) ListMapString, Object rankList (ListMapString, Object) result.get(rankList); System.out.println(总订单数 totalOrders); System.out.println(总金额 totalAmount); System.out.println(排行榜 rankList); } }这里需要说明一个容易混淆的点如果存储过程内有SELECT结果集MyBatis会为每个结果集生成一个key放入返回Map中。不同版本MyBatis的默认key不同有的叫resultSet、有的带下标我列一下常见情况。# 常见结果集key表现 # 单结果集resultList # 多结果集resultSet0、resultSet1 ...3.5.x常见 # OUT参数直接以参数名为key为了稳定最省心的方案是让存储过程只返回一个结果集多的需求用OUT参数代替。所以上面Service里我读rankList的写法你在自己的项目里需要先打印result.keySet()确认实际key名再取值。这个排查过程在5.2节会展开讲。5. 跑通之后才遇到的问题调试、性能与维护功能跑通通常只需要半天后面真正折磨人的是上线后的疑难杂症。这节把我在实战里遇到的高频问题都过一遍每条都是花过时间换来的。5.1 存储过程执行慢到底谁背锅现象是明明单条SQL很快组合成存储过程后整体变慢。排查套路分三步先定位是哪个子语句慢。MySQL没有直接输出每个子语句耗时的开关我的土办法是在关键步骤前后往日志表写入当前时间戳算差值。看有没有跨表访问或隐式类型转换。比如t_order.user_id是VARCHAR但你传入的变量是BIGINT索引就用不上。检查存储过程的权柄冲突。排名需求里我用了ORDER BY SUM(pay_amount) DESC统计表数据量一大这个排序是内存排序还是磁盘排序直接决定性能天花板。加索引的作用有限更重要的思路是缩小扫描范围。5.2 游标循环里写单条UPDATE的教训我在排行榜排名需求里最初用游标逐行更新排行表10个用户感觉不出来慢但同样的逻辑推广到几千个SKU时循环里的单条UPDATE直接在锁竞争上把存储过程拖垮。后来改成临时表 一次INSERT INTO ... SELECT批量写入性能几十倍提升。具体的性能对比如下方案数据量耗时表现结论游标循环单条UPDATE1000行800ms左右慢且锁竞争严重先写临时表再批量INSERT1000行50ms左右快适合中大批量纯INSERT INTO ... SELECT1000行30ms左右最快但要求逻辑能一条SQL写完所以存储过程里遇到循环先问一句这个循环能不能变成一条集合操作不要下意识用游标。游标是最后手段不是默认手段。5.3 连接池超时与事务边界存储过程的执行时间比较长时要注意Spring默认事务管理和连接池的配合。我在项目中遇到过Connection is not available, request timed out原因是某个带排名计算的存储过程执行超过10秒把HikariCP连接池的connection-timeout给击穿了。应对办法是分层处理统计类存储过程用单独的只读配置设置较小的连接池并发数、较大的超时时间写数据的存储过程保持默认。另外存储过程中的事务边界要和Spring事务理清楚——如果Service方法上有Transactional存储过程内的COMMIT和ROLLBACK会打乱Spring对事务的控制预期最好保证只有一端在管事务。我的习惯是存储过程自己管事务Service层就不加事务注解Service层需要管事务存储过程就只做数据加工不显式COMMIT。5.4 调试存储过程的土办法存储过程没有断点调试但有几招非常稳把中间变量SELECT出来人工观察。调试完可以临时注释掉不要留着污染线上结果集。用日志表记录步骤。我在库里专门建了一张t_proc_log存储过程每个关键节点INSERT一行记录出问题直接看日志表比看异常堆栈高效得多。单独用数据库客户端测试。先用CALL p_order_monthly_stats(2026-03, 1, total_orders, total_amount);跑一遍确认逻辑没问题再对接应用层。应用层报错时也先用客户端重放能快速决定问题是出在存储过程本身还是MyBatis参数绑定上。5.5 维护性比炫技更重要最后说点得罪人的话。存储过程好用但也很容易写出只有天知道的代码。我在项目里明确了三条规范第一所有存储过程命名带前缀p_统一放在迁移脚本里管理并且注释里写清楚入参含义、输出含义、依赖表清单和兼容性说明。第二过程体内禁止出现裸的SELECT *要显式列出字段防止上游表结构变更后存储过程静默拿到错位数据。第三结构变更涉及的存储过程必须一起走版本发布流程不能只改表结构不更新过程。把这些规范立起来之后存储过程不再是团队里人人躲着走的东西。上面那个订单统计案例上线后运行了几个月凌晨跑批稳定接口加载报表时完全无感我自己重新Review的过程中也确实感受到存储过程用对了场景它就是一个很好用的工具。之前有人问我既然MyBatis-Plus这么方便为什么还要绕一圈学存储过程我的答案是工具没有高低之分只有匹配不匹配。MyBatis-Plus解决的是单表CRUD和简单查询的快速开发问题而存储过程解决的是数据库内数据加工的复杂编排问题。两者在我的项目里并存得很好以后遇到对账、批量状态流转、榜单计算这类需求我还是会优先考虑存储过程这条路。