处理海量MySQL表？

Question

我刚被要求修复我们工作中的地址数据库，因为它非常缓慢。我通常不会接触它，因为另一个人负责，但他现在已经离开了，所以这是我的任务。

问题在于，数据库包含5个表，并且每个表中都有很多重复信息。应该有2700万行，但实际上有3000万行，因此有超过300万个重复行。我们以前的IT人员设置查询时会搜索所有5个表，并使用PHP脚本筛选出重复的行，以便只显示一次信息。这使我们的服务器明显变慢，因此我编写了一个PHP脚本来将每一行与其他3000万行进行比较，如果有重复项，则删除它。但是，在我开始2分钟后，服务器崩溃了，所以我尝试了几个其他使用PHP的脚本，但每次运行复杂的MySQL查询时，服务器都会崩溃。

有没有一种简单的方法可以合并所有表并删除所有重复的条目，而不会导致服务器崩溃？

数据库副本

post1           10,044,279   MyISAM    latin1_german2_ci   758.1 MiB   -
post2            8,328,333   MyISAM    latin1_german2_ci   624.7 MiB   -
postcode         9,344,317   MyISAM    latin1_german2_ci   703.8 MiB   -
postcode_nw      1,157,217   InnoDB    utf8_unicode_ci      97.6 MiB   -
postcode_tmp     1,749,650   MyISAM    latin1_german2_ci    50.5 MiB   -

- Cookiimonstar

---

你的描述显示数据库设计相当糟糕。 - dynamic

我们需要表模式才能帮助您，您有更改它们的余地吗？就我个人而言，我甚至不会尝试在原地完成此操作，我会创建一个新的数据库，设置一些表，然后开始移动数据。 - Tony Hopkinson

如果您只需要执行一次此操作，可以创建第6个带有适当索引的表来防止重复 - 然后将其他5个表中的记录逐个插入到第6个表中。只需将记录插入到第6个表中不存在的位置即可。您可能希望在5个原始表中创建与第6个表相同的索引，但不要将其设置为唯一。 - gangreen

你知道为什么你的服务器会崩溃吗？我猜你指的是数据库服务器。我会先解决这个问题 - 虽然听起来你的数据库设计可能需要改进，但你需要先让现有的工作起来，即使只是为了比较改进后的设计前后的差异。 - halfer

（顺便说一句 - 问题通过短小易读的句子和分段获益匪浅。第二段可以加入一些停顿来呼吸一下;-)） - halfer

Answer 1

PHP开发人员常见的问题是他们忘记了计算机中有内存这个东西。
它“闻起来”好像你试图将所有东西都加载到内存中。
你的方法实际上是正确的，虽然速度很慢，但是安全。 如果 你正确地实现了它。
你不关心速度，因为这只是一次性的事情。

1. 设计你的代码以处理小批量数据。
2. 手动清理（在需要的地方分配null，使GC工作，在每次迭代之间等待GC完成它的工作）。
3. 考虑将其作为SP执行，并在SP中使用游标（谷歌它）。
4. 在清理数据后，我建议分析您的数据库结构并将其迁移到更强大的结构（即规范化）。
5. 考虑其他存储某些数据的方法，例如将大文本存储为文件，并仅在DB中存储指针。如果这些文本需要进行搜索（即全文检索），则MySql不是最好的工具（例如Google SOLR）。
6. 哦...InnoDB比旧的、几乎不再开发的MyIssam更好。

- Itay Moav -Malimovka

Answer 2

我认为我不能给出一个明确的答案，但我可以给你一些建议：

1. 服务器可能会因为内存不足而崩溃。增加内存或者将数据库上传到一个临时的AWS服务器上，以便运行复杂的查询，可能会解决问题。

2. 您可以考虑使用简单的查询来获取数据，并在远程计算机上进行比较，而不是让服务器做重活。这样做需要更长的时间，但最终可以完成任务。

3. 也许您可以更改旧的php脚本，使其除了不显示它们之外，还能删除重复项。虽然不会遍历所有数据，但最终会减少重复项。然后可以修改此方法，使得循环遍历所有数据时可以调用此旧的php脚本，以便捕获未被正常操作清理代码捕获的重复项。

- Correa