mysql - 执行更新选择时，MySQL超出系统内存

Question

我在Mac Pro，64GB内存，6核的Mac Pro上运行mysql服务器。我的架构中的Table1有3.3亿行。 Table2有65,000行。 （我还有其他几张表，它们合计约有15亿行，但我尝试执行的操作未使用它们，所以我认为它们不相关）。

我正在尝试做我认为是相对简单的更新语句（请参见下文），以将一些数据从Table2导入Table1。但是，我在使用mysql穿越系统内存时经历了一段糟糕的时光，迫使我进行交换，最终冻结了整个系统，以使mysql无法响应，因此我需要重新启动计算机。我的更新声明如下：

UPDATE Table1, Table2 
SET 
  Table1.Column1 = Table2.Column1, 
  Table1.Column2 = Table2.Column2, 
  Table1.Column3 = Table2.Column3, 
  Table1.Column4 = Table2.Column4 
WHERE 
  (Table1.Column5 = Table2.Column5) AND 
  (Table1.Column6 = Table2.Column6) AND 
  (Table1.Column7 = Table2.Column7) AND 
  (Table1.id between 0 AND 5000000);

最终，我想对表1中的所有3.3亿行执行此更新。我决定将其分成500万行，因为


（a）我遇到了超出锁大小的问题，并且
（b）我认为这可能有助于解决我撞入公羊的问题。


以下是有关情况的一些更相关的详细信息：


我已经通过Column5，Column6，Column7（我在上面匹配其值的列）的组合为Table1和Table2创建了索引。
表1有50列，总共约60 GB。
表2有8列，总计3.5 MB。
我知道有些人可能会在这种情况下建议使用外键，而不是用table2中的信息更新table1，但是（a）我有足够的磁盘空间，并且不太在意使用它以实现最大效率（b）没有这些表中的任何一个的值都将随时间而变化，并且（c）我最担心在table1上运行的查询的速度，如果花很长时间才能将信息从table2传递到table1，我当然不想重复我在table1上运行的每个查询的过程。
为了解决超出最大锁定表大小的问题，我尝试了增加innodb_buffer_pool_size的方法。我尝试了许多值。即使是低至8 GB的内存（即计算机内存的1/8，在执行此操作时，我在其上几乎没有运行任何其他东西），我仍然遇到mysqld进程占用了所有内存的问题在系统上可用，然后开始从操作系统中提取内存分配（即，我的kernel_task开始显示为使用30GB内存，而通常使用2GB左右）。
最大锁的问题似乎已经得到了解决。我不再遇到这个错误，尽管那可能只是因为现在我在内存中飞速崩溃并崩溃了，然后才能到达那里。
我尝试了较小的批处理大小（100万行，100,000行）。这些似乎比500万行的批处理要好一些，但它们通常仍存在相同的问题，也许开发速度稍慢。而且，性能似乎很糟糕-例如，以我要处理的100,000个批处理的速度来看，执行此更新大约需要7天。
这些表都使用InnoDB
我通常将SET SESSION TRANSACTION ISOLATION LEVEL READ设为COMMIT。尽管我不知道它是否真的有帮助（我是以任何方式访问此数据库的唯一用户，所以我不太在意锁定，并且在可能的情况下会完全取消它）
我注意到批量运行的时间存在很多可变性。例如，在100万行的批次中，我会观察到45秒到20分钟之间的时间。
当我尝试运行只找到匹配行的东西，然后只将它们的两个列值放到新表中时，我得到的一致性时间要长得多（每百万行约2.5分钟）。因此，似乎我的问题可能源于以下事实：我正在更新要进行匹配的表中的值，即使我要更新的列与我要匹配的列不同。
我要匹配和更新的列仅包含INT和CHAR类型，没有超过7个字符的字符。
我运行了CHECK TABLE诊断程序，然后恢复正常。
总体而言，我感到非常困惑，为什么这样做会如此困难。我是mysql和数据库的新手。由于Table2很小，我可以使用字典查找在python中完成相同的任务，而且速度更快。我本以为数据库可以更好地处理此问题，因为处理和更新大型数据集是它们的设计目的。
我使用Mysql工作台对查询运行了一些诊断，并确认没有执行全表扫描。
看来，这里确实确实出了问题。如果系统具有64 GB的内存，并且大于两个表的总和（尽管计算索引大小，则两个表的总和大于64 GB），并且该操作仅适用于一次只有3.3亿行中有500万行，应该将其浪费掉是没有意义的。


因此，我想知道：


我编写此更新语句的语法是否有点糟糕和低效，从而可以解释可怕的性能和问题？
除了应配置的innodb_buffer_pool_size以外，是否还有其他一些参数可以使ram mysql使用的范围更牢固，或者使其更有效地使用资源？
我还应该运行其他类型的诊断程序来尝试检测表，架构等问题吗？
期望进行这样的更新需要多少“合理”时间？

Answer 1

因此，在与一些熟悉此类问题的人士进行咨询之后，以下是我想出的解决方案：


我将innodb_buffer_pool_size减小至4GB，即系统总内存的1/16。最终，这似乎足以可靠地阻止MySQL耗尽我的64GB RAM。
我简化了索引，以便它们仅包含我需要的列，并确保我正在使用的所有索引都足够小以适合RAM（还有足够的空间来腾出空间供MySQL用于RAM的其他用途）。
我学会了接受的一点，就是MySQL似乎并不是为特别大的数据集而构建的（或者至少不是在单台机器上构建的，即使是像我所拥有的相对大的机器也是如此）。因此，我接受了通常需要手动将我的工作分成几批的情况，因为显然，MySQL的机器并没有做出正确决定如何自行分解工作的能力。认真对待RAM等系统资源。
有时，当按照这种方式进行工作时，或者通常在我的较大数据集上进行工作时，我将使用MySQL进行更新和联接。其他时候，我只是将数据分解成块，然后在另一个程序中进行联接或其他此类操作，例如R（通常使用像data.table这样的程序包，可相对高效地处理较大的数据）。
还建议我，也可以在Hadoop群集上使用诸如Pig of Hive之类的东西，它应该能够更好地处理这种大小的数据。