慢复合mysql更新查询

Question

我们正在两个数据库表之间执行一个更新查询，而且速度太慢了。如下所示:执行查询需要30天。

一个表lab.list包含约94万条记录，另一个表mind.list大约370万条(370万条)，当满足两个条件时，更新设置一个字段。这是一个查询：

UPDATE lab.list L , mind.list M SET L.locId = M.locId  WHERE L.longip BETWEEN M.startIpNum AND M.endIpNum AND L.date BETWEEN "20100301" AND "20100401" AND L.locId = 0

与现在一样，该查询每8秒执行一次更新。

我们还在同一个数据库中对mind.list表进行了尝试，但这与查询时间无关。

UPDATE lab.list L, lab.mind M  SET L.locId = M.locId  WHERE  longip BETWEEN M.startIpNum AND M.endIpNum AND date BETWEEN "20100301" AND "20100401" AND L.locId = 0;

有办法加快查询速度吗？基本上，它应该建立两个数据库子集: mind.list.longip在M.startIpNum之间，M.endIpNum lab.list.date在"20100301“和"20100401”之间。

然后更新这些子集的值。我想我犯了个错误，但是在哪里？也许有一个更快的查询可能？

我们尝试了log_slow_queries，但这表明它确实在检查数以百万计的行中的一千万行，可能一直上升到3331千兆。

技术信息：

• 服务器版本: 5.5.22-0ubuntu1-log (Ubuntu)
• lab.list有关于locId，longip，date的索引
• lab.mind有关于locId、startIpNum和M.endIpNum的索引
• 硬件:2xXeon3.4GHz，4GB内存，128个GB (所以这不应该是一个问题！)

Answer 1

最后，对于mysql来说，查询太大或太麻烦了。即使在索引之后。在高端Sybase服务器上用相同的数据测试相同的查询，也需要3个小时。

因此，我们放弃了数据库服务器的所有操作，转而使用脚本语言。

我们在python中做了以下工作：

1. 加载370万条记录中的100000条记录，并遍历行
2. 对于每一行，设置locId并填写其余的列

所有这些更新一起大约需要5分钟，所以一个巨大的改进！

结论：

跳出数据库框！

Answer 2

首先，我将尝试按这个顺序对startIpNum、endIpNum、locId进行索引。即使locId用于更新，也不会在SELECTing中使用。

出于同样的原因，我会在locId、date和longip (它不在第一个分块中使用，应该按日期运行)上索引这个命令。

那么startIpNum和endIpNum分配了什么样的数据类型呢？对于IPv4，最好将其转换为整数，并为用户I/O使用INET_ATON和INET_NTOA。我假设您已经这样做了。

要运行更新，可以尝试使用临时表对M数据库进行分段。这就是：

* select all records of lab in the given range of dates with locId = 0 into a temporary table TABLE1.
* run an analysis on TABLE1 grouping IP addresses by their first N bits (using AND with a suitable mask: 0x80000000, 0xC0000000, ... 0xF8000000... and so on, until you find  that you have divided into a "suitable" number of IP "families". These will, by and large, match with startIpNum (but that's not strictly necessary).
* say that you have divided in 1000 families of IP.
* For each family:
*    select those IPs from TABLE1 to TABLE3.
*    select the IPs matching that family from mind to TABLE2.
*    run the update of the matching records between TABLE3 and TABLE2. This should take place in about one hundred thousandth of the time of the big query.
*    copy-update TABLE3 into lab, discard TABLE3 and TABLE2.
* Repeat with next "family".

这并不是真正的理想，但如果稍微改进的索引没有帮助，我真的看不到那么多的选择。