MySQL 过程 while 循环 : Gets stuck after one iteration - Cache Clean up

Question

我正在查询一些非常大的表 (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
• 表:目标表:
  • 表行:~6,500,000 条记录，
  • 数据长度:~4 GB，
  • 索引长度:~350 MB，
  • 表大小:~4.35 MB
• 表:主题表:
  • 表行:~6,500,000 条记录，
  • 数据长度:~550 MB，
  • 索引长度:不适用，
  • 表大小:~550 MB

非常感谢。

---

我想我必须向 Oracle/MariaDB 提交错误报告，并更新帖子。

Answer 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;

想法是将内部子查询替换为带键的临时表，以便外部连接可以使用该键。