mysql - 慢复合mysql更新查询

Question

我们正在两个数据库表之间进行更新查询，速度慢得离谱。如:执行查询需要 30 天。

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

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 秒大约执行 1 次更新。

我们也在同一数据库中对 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 的索引
• 硬件:2x xeon 3.4 GHz、4GB RAM、128GB SSD(所以这应该不是问题!)

Answer 1

我会首先尝试按顺序在 startIpNum、endIpNum、locId 上索引头脑。 locId 不用于 SELECTing from mind，即使它用于更新。

出于同样的原因，我会在 locId、date 和 longip(第一次分块中未使用，应该在 date 上运行)这个顺序上索引 lab。

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

要运行更新，您可以尝试使用临时表对 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".

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