在编写存储过程或脚本时，WHILE、LOOP 等循环结构几乎无法完全回避。它们常被用来处理需要逐行计算的复杂业务逻辑，例如累计积分、生成序列、批量更新依赖前置条件的记录等。但很多开发者发现，一个看似简单的循环在数据量稍大时就变得异常缓慢，甚至导致服务器资源耗尽。问题的根源往往并非循环语法本身，而是循环体内执行的操作缺乏优化，以及事务与锁的管理不当。接下来我们将深入剖析 SQL 循环的性能短板，并给出针对 WHILE 和 LOOP 的优化技巧。
循环的性能瓶颈究竟在哪里
要优化 SQL 循环，必须先理解它消耗时间的几个关键点。每次进入循环体，数据库引擎需要解析并执行其中的语句，这通常包含一次或多次数据读取。如果循环体内有类似 SELECT ... FROM 大表 WHERE 条件 的操作，而条件列没有合适的索引，那么每次迭代都会触发一次全表扫描或范围扫描，逻辑读取次数将随着循环次数线性增长，这是最常见的性能杀手。即使有索引，如果循环次数达到十万甚至百万级，索引查找的累积成本也会变得非常可观。
另一个容易被忽视的瓶颈是事务和锁。在循环中进行 INSERT、UPDATE 或 DELETE 时，如果没有明确控制事务边界，数据库会为每一条语句开启一个隐式事务并立即提交。这意味着每一次写入都要同步等待日志刷盘，磁盘 I/O 压力剧增。此外，循环体可能长时间持有行锁或表锁，阻塞其他会话，甚至引发锁升级和死锁。例如在一个 WHILE 循环中逐行更新某张表的余额字段，每更新一行就提交一次，不仅慢，还会因频繁的锁获取和释放消耗大量 CPU。
网络往返也是不可忽视的因素，尤其是当循环逻辑写在客户端代码中时。即便使用服务器端的存储过程，如果循环体内包含对远程链接服务器或外部资源的调用，延迟也会成倍放大。因此，优化的核心思路在于：减少循环体内的逻辑读取次数、降低写入频次、缩短锁持有时间，以及尽可能用批量操作替代逐行处理。
WHILE 循环的层层优化策略
WHILE 循环在 SQL Server、MySQL 等数据库中都有支持，其基本结构是一个条件判断加一个语句块。优化 WHILE 循环的第一步，是重新审视是否真的需要它。很多原本用游标和 WHILE 实现的行级操作，都可以通过集思广益改写为基于集合的 SQL。例如，对一列数据进行逐行累加计算，完全可以用窗口函数 SUM() OVER(ORDER BY ...) 代替。不要着急写循环，先尝试用 UPDATE ... FROM ... JOIN、INSERT ... SELECT 或 MERGE 语句来完成同样的工作。只有当你面对的逻辑必须依赖上一行的计算结果分步进行，且无法通过递归 CTE 实现时，才考虑保留循环。
如果循环不可避免，首要优化手段是缩小循环体的粒度。不要将 SELECT 语句直接放在循环内每次执行，而应事先将需要处理的数据集、配置参数等一次性加载到临时表或表变量中。这样循环内只需对临时表进行键值查找，逻辑读取成本大幅降低。同时，务必为临时表建立合适的索引，尤其是用作连接条件或过滤条件的列。另一个重要技巧是用批处理代替逐行提交。在循环内部，每执行一定数量（比如 1000 条）的修改后才手动发出一次 COMMIT（如果数据库支持在存储过程中显示事务控制），或者将多条数据拼接成批量 INSERT 语句一次执行，可以显著减少日志写入和锁开销。
变量计算优化也不能忽略。在循环体中反复声明的变量和复杂的标量函数调用会让 CPU 负载居高不下。尽量把计算移到循环外，或者在循环外完成表达式求值再赋值给变量。例如，不要在每次循环时都调用 GETDATE() 来获取当前时间，而是在循环前获取一次存入变量，循环内直接使用变量。如果是基于游标的 WHILE 循环，还需要注意游标类型的选择。默认的动态游标会消耗大量 tempdb 资源，改用 FAST_FORWARD 或 STATIC 游标并配合 READ_ONLY 属性，能提升读取效率。一个简洁的批处理 WHILE 示例（以 SQL Server 为例）：
-- 事先将待处理主键存入临时表，并加索引
CREATE TABLE #queue (id INT PRIMARY KEY, processed BIT DEFAULT 0);
INSERT INTO #queue(id) SELECT id FROM orders WHERE status = 'pending';
DECLARE @batchSize INT = 500;
DECLARE @rows INT = 1;
WHILE @rows > 0
BEGIN
BEGIN TRANSACTION;
UPDATE TOP(@batchSize) o
SET o.status = 'done', o.updated = GETDATE()
FROM orders o
INNER JOIN #queue q ON o.id = q.id AND q.processed = 0;
SET @rows = @@ROWCOUNT;
UPDATE #queue SET processed = 1 WHERE processed = 0 AND id IN (
SELECT TOP(@batchSize) id FROM #queue WHERE processed = 0 ORDER BY id
);
COMMIT TRANSACTION;
-- 可选：稍作等待，减少资源争用
WAITFOR DELAY '00:00:00.05';
END;
上面的代码通过分段事务和临时表索引，将原本可能需要几十万次提交的操作转变为每次几百行的批量处理，性能提升可达几十倍。
LOOP 循环的优化思路与替代方案
MySQL 中通常使用 LOOP ... LEAVE ... END LOOP 结构配合游标来遍历结果集。与 WHILE 类似，优化的首要原则依然是“能不循环就不循环”。MySQL 8.0 引入了递归 CTE，可以优雅地解决一部分需要循环的场景，例如生成连续日期序列或组织树形数据。如果你还在用 LOOP 逐行拼接报表，不妨尝试用公共表表达式改写。
当必须使用 LOOP 时，尽可能将循环体中的 DML 操作替换为一次性的多行操作。MySQL 支持批量 INSERT 的 VALUES 语法和 INSERT INTO ... SELECT，也支持多表联合 UPDATE。例如，原本需要用游标逐行计算佣金并更新订单表的操作，可以改用一条 UPDATE orders JOIN ... 语句，通过 CASE 表达式和聚合函数一次完成。如果业务规则极其复杂，确实需要逐行判断，那么请参照 WHILE 的优化思路：先把需要计算的数据导入一张临时表，并减少临时表与主表的交互频率。
还有一条容易被忽略的原则：不要在循环内执行可能锁表的结构变更或不必要的数据查询。有些开发者喜欢在循环中用 SELECT COUNT(*) 来记录进度，这在生产环境是灾难性的。如果需要进度监控，可以通过一个轻量级的计数器变量在循环内自增，并在条件满足时输出一条信息（如存储过程的 SELECT 返回）。同时，务必注意游标的关闭和释放，未关闭的游标会一直占用内存和锁资源。一个典型的 MySQL LOOP 优化写法如下：
CREATE PROCEDURE process_orders()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_order_id INT;
DECLARE cur CURSOR FOR SELECT order_id FROM temp_orders;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 开启事务，循环内多次更新但只提交一次
START TRANSACTION;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_order_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 这里的更新操作尽量基于索引，且不要额外查询
UPDATE orders SET status = 'processed' WHERE order_id = v_order_id;
-- 计数器直接累加，无需 select count(*)
SET @counter = @counter + 1;
END LOOP;
CLOSE cur;
COMMIT;
END;
上述过程中事务包裹整个游标遍历，一次提交，避免了逐条提交的日志开销。但注意，如果数据量极大，长事务可能导致undo膨胀和锁持有时间过长。这时可以像WHILE优化中那样，在循环内每处理一定行数就提交一次，并重置计数器。
最后，无论使用哪种循环，记得用数据库提供的工具（如 SQL Server Profiler、MySQL 的 Performance Schema）监控逻辑读取和等待事件，量化优化效果。循环优化不是魔法，而是通过减少不必要数据访问和分批处理，让每次迭代的成本逼近理论最小值。当你把循环体内的逻辑读取从百万次降到几千次，执行时间自然会从小时级缩短到秒级。