logo标签列表

MySQL存储过程while循环：在一次迭代后卡住 - 缓存清理

mysqlcachingjoinwhile-loop
4

我正在查询一些非常大的表格（TargetTable），并且有一个特定的过程，在其第二次迭代中被卡住，永远不会完成也不会崩溃。无论范围的开始（loopIndex）还是范围的大小（loopStepShort）如何，第一次迭代总是在几分钟内完成。

期待听到您的想法和建议。

[更新1] 如果我执行以下操作之一，问题就会消失：

  • 删除内部连接的嵌套部分；
  • 使用内存临时表格来处理内部连接的嵌套部分（感谢@SashaPachev）；
  • 在while循环之外运行每个循环迭代；
  • 使用更小的TargetTable。

[更新2] 已解决！ 我认为问题可能出现在将一些数据库索引复制到数据库转换过程中时未被复制。因为当我尝试重现一个例子时，它仅发生在未索引的表上（高CPU使用率和几乎无限的循环步骤）MariaDB Server，JIRA。

MySQL InnoDB引擎（10.0.21-MariaDB Server，Linux x86_64，Fedora v.21）的自定义配置如下：

innodb_buffer_pool_size = 2G
net_write_timeout = 1800
net_read_timeout = 1800
join_buffer_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
max_allowed_packet = 4G
key_buffer = 2G
sort_buffer_size = 512K

程序主体如下所示：

SET loopIndex = 0;
SET loopMax = 20000000;
SET loopStepShort = 10000;
WHILE loopIndex < loopMax do    
    UPDATE TargetTable AS t0,
        (SELECT __index, sessionStartAge
        FROM SubjectTable AS t0
        INNER JOIN (SELECT t0.id, t0.admission, 
                    if(t0.startage is null and t0.endage is null, 21, 
                    if(least(t0.startage, t0.endage) <= 1, 1, 
                    if(least(t0.startage, t0.endage) <= 4, 2, 
                    if(least(t0.startage, t0.endage) <= 9, 3, 
                    if(least(t0.startage, t0.endage) <= 14, 4, 
                    if(least(t0.startage, t0.endage) <= 19, 5, 
                    if(least(t0.startage, t0.endage) <= 24, 6, 
                    if(least(t0.startage, t0.endage) <= 29, 7, 
                    if(least(t0.startage, t0.endage) <= 34, 8, 
                    if(least(t0.startage, t0.endage) <= 39, 9, 
                    if(least(t0.startage, t0.endage) <= 44, 10, 
                    if(least(t0.startage, t0.endage) <= 49, 11, 
                    if(least(t0.startage, t0.endage) <= 54, 12, 
                    if(least(t0.startage, t0.endage) <= 59, 13, 
                    if(least(t0.startage, t0.endage) <= 64, 14, 
                    if(least(t0.startage, t0.endage) <= 69, 15, 
                    if(least(t0.startage, t0.endage) <= 74, 16, 
                    if(least(t0.startage, t0.endage) <= 79, 17, 
                    if(least(t0.startage, t0.endage) <= 84, 18, 
                    if(least(t0.startage, t0.endage) <= 89, 19, 
                    if(least(t0.startage, t0.endage) <= 120, 20, 21))))))))))))))))))))) AS sessionStartAge
            FROM SubjectTable AS t0
            INNER JOIN ids AS t1 ON t0.id = t1.id 
                AND t1.id >= loopIndex 
                AND t1.id < (loopIndex + loopStepShort)
            GROUP BY t0.id, t0.admission) AS t1 
        ON t0.id = t1.id AND t0.admission = t1.admission) AS t1
    SET t0.sessionStartAge = t1.sessionStartAge
    WHERE t0.__index = t1.__index;

    SET loopIndex = loopIndex + loopStepShort;
END WHILE;

最后，以下是表格的大致尺寸：

  • 表格：ids：
    • 表行数：约1,500,000条记录，
    • 数据长度：约250 MB，
    • 索引长度：约140 MB，
    • 表格大小：约400 MB
  • 表格：TargetTable：
    • 表行数：约6,500,000条记录，
    • 数据长度：约4 GB，
    • 索引长度：约350 MB，
    • 表格大小：约4.35 MB
  • 表格：SubjectTable：
    • 表行数：约6,500,000条记录，
    • 数据长度：约550 MB，
    • 索引长度：N/A，
    • 表格大小：约550 MB

非常感谢您的帮助。

我想我需要向Oracle/MariaDB提出一个错误报告，并更新这篇文章。

像这样在循环中更新似乎不太有效率。您能否解释一下您想通过上述代码实现什么目标，以防我们可以建议另一个解决方案。 - Shadow
你什么时候提交更改？ - Shadow
为了简化，此过程的目的是查找每个会话客户（id）的最小年龄（sessionStartAge）。 每个客户都有多次访问，可以分类到会话中，每个会话可能包含一个或多个具有相似入院日期（admission）的访问。 另外，请注意，此过程在循环之外工作正常，这与 while 循环有关。 谢谢。 - Mohsen Mesgarpour
我已经在这里的查询中添加了离散化（if语句），这是最初我的查询的一部分。我相信在运行几次后，它是主要的贡献因素。 - Mohsen Mesgarpour
此外，关于提交(commit)： “默认情况下，MySQL运行时启用自动提交模式。这意味着一旦您执行更新（修改）表的语句，MySQL会将更新存储在磁盘上以使其永久化。更改无法回滚。” 来源 - Mohsen Mesgarpour
我之前对于if语句的影响有所错误，它们对于减速的贡献非常小。关于原因的更新：只要我执行以下操作之一，这个问题就会消失：[1] 删除嵌套的内部连接；[2] 在while循环外运行每个循环迭代；[3] 使用较小的(TargetTable)。 - Mohsen Mesgarpour
1个回答
1

尝试使用以下代码（免责声明 - 未经测试，可能包含语法错误或错误）：

DROP TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 (key(id)) ENGINE=MEMORY SELECT t0.id, t0.admission, 
                    if(t0.startage is null and t0.endage is null, 21, 
                    if(least(t0.startage, t0.endage) <= 1, 1, 
                    if(least(t0.startage, t0.endage) <= 4, 2, 
                    if(least(t0.startage, t0.endage) <= 9, 3, 
                    if(least(t0.startage, t0.endage) <= 14, 4, 
                    if(least(t0.startage, t0.endage) <= 19, 5, 
                    if(least(t0.startage, t0.endage) <= 24, 6, 
                    if(least(t0.startage, t0.endage) <= 29, 7, 
                    if(least(t0.startage, t0.endage) <= 34, 8, 
                    if(least(t0.startage, t0.endage) <= 39, 9, 
                    if(least(t0.startage, t0.endage) <= 44, 10, 
                    if(least(t0.startage, t0.endage) <= 49, 11, 
                    if(least(t0.startage, t0.endage) <= 54, 12, 
                    if(least(t0.startage, t0.endage) <= 59, 13, 
                    if(least(t0.startage, t0.endage) <= 64, 14, 
                    if(least(t0.startage, t0.endage) <= 69, 15, 
                    if(least(t0.startage, t0.endage) <= 74, 16, 
                    if(least(t0.startage, t0.endage) <= 79, 17, 
                    if(least(t0.startage, t0.endage) <= 84, 18, 
                    if(least(t0.startage, t0.endage) <= 89, 19, 
                    if(least(t0.startage, t0.endage) <= 120, 20, 21)))))))))))))))))))) as sessionStartAge,
FROM SubjectTable AS t0
            INNER JOIN ids AS t1 ON t0.id = t1.id 
                AND t1.id >= loopIndex 
                AND t1.id < (loopIndex + loopStepShort)
            GROUP BY t0.id, t0.admission;
UPDATE TargetTable AS t0,
        (SELECT __index, sessionStartAge
        FROM SubjectTable AS t0
        INNER JOIN t1 ON t0.id = t1.id AND t0.admission = t1.admission) AS t2
    SET t0.sessionStartAge = t1.sessionStartAge
    WHERE t0.__index = t2.__index;

这个想法是用一个带有键的临时表来替换内部子查询，以便外部连接可以使用该键。

嗨，感谢@Sasha-Pachev。您提供的解决方案有效。我认为我需要将其发送给Oracle/MariaDB作为错误报告。 - Mohsen Mesgarpour
MySQL中一个长期的问题是，如果创建了一个临时表来存储子查询的输出，那么就没有办法预测哪些键可能对其有好处。这个问题可能已经在最近的MySQL/MariaDB版本中得到解决，但我认为在5.5版本中仍然无法解决。我的方法是假设子查询优化器受限，并在怀疑优化器无法正确处理子查询时手动创建带有正确键的临时表。 - Sasha Pachev
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接