我请求PHP解决以下问题:
我在一个数据库中有大约15个表，每个表有1000 - 5000万行，总计2亿行，列userID, B, C, d
我有另外9个表，列userID, fbID。每个表有大约200万行。从userID到fbID有一个一对一的映射。
我的目标是输出一个包含2亿行、列为fbID、B、C、d的文件
为了做到这一点，我必须搜索包含userID和fbID列的所有9个表，因为userID可能在一个表中找到，而在其他表中找不到。只要在其中任何一个表中找到userID，就可以停止。这一部分我使用SQL和PHP。SQL查询包含LIMIT 1，因此每当我找到userID时，我只返回1行，因为这些表可以包含具有相同userID的多行。
不幸的是，这个算法需要~60s/1k行，这将花费我~130天来完成。
有更有效的方法吗?
我不是数据库计算时间如何工作的专家，但是我想到了一些想法:
-查询所有9个表，并使用userID键和fBID值创建一个查找表。
-使用这9个表在数据库中创建一个新表，每个表userID一行，以及相应的FBID，并通过这个表进行搜索。
这里有更多关于表的具体信息:
总计2亿行的表(每个表看起来像这样):
Column Type Null Default
dtLogTime datetime Yes NULL
iUin int(10) No
B int(10) No
C int(10) No
D int(10) No
索引:
Keyname Type Unique Packed Column Cardinality Collation Null Comment
dtLogTime BTREE No No dtLogTime 323542 A YES
iUin BTREE No No iUin 323542 A
其他9个表之一:
Column Type Null Default Comments
dtLogTime datetime Yes NULL
iUin int(10) No
vFBID varchar(48) No
索引:
Keyname Type Unique Packed Column Cardinality Collation Null Comment
dtLogTime BTREE No No dtLogTime 2179789 A YES
iUin BTREE No No iUin 2179789 A
示例代码我试过了:
// returns FBID of iuin
function getFBID($iuin){
$query = sprintf("SELECT vFBID FROM `tbReg` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbOnline` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbConsumeFBC` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbFeed` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbInvite` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbFreeGift` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbUninstall` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
$query = sprintf("SELECT vFBID FROM `tbDownload` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
$query = sprintf("SELECT vFBID FROM `tbIUserSource` WHERE iuin = " . $iuin . " LIMIT 1");
$result = mysql_query($query);
if(mysql_num_rows($result) != 0){
$row = mysql_fetch_assoc($result);
return $row['vFBID'];
}
mysql_free_result($result);
}
fwrite($handle, '"Time","FBID","Action","ActionID"' . "'n");
$query = sprintf("SELECT count(dtLogTime) AS length
FROM `tbActionWeeding`");
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$length = ceil($row['length'] * 0.0001);
$start = 0;
$i = 0;
while($i++ < 10000)
$query = sprintf("SELECT dtLogTime, iuin, iWeedID
FROM `tbActionWeeding`
LIMIT " . $start . "," . $length);
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "'n";
$message .= 'Whole query: ' . $query . "'n";
die($message);
}
while($row = mysql_fetch_assoc($result))
fwrite($handle, '"' . $row['dtLogTime'] . '","' . getFBID($row['iuin']) .
'","0","' . $row['iWeedID'] . "'"'n");
mysql_free_result($result);
$start += $length;
}
我有其他9个表，列userID, fbID
和
其他9个表每个表有~ 200万行
这种数据结构的低效率不能仅仅通过聪明的代码轻易地克服。因为你需要处理大量的冗余数据，在这种架构下，最有效的算法也会运行缓慢。
你需要的是标准化。您应该更改表的结构以删除冗余数据。这样就不需要搜索9个独立的表2亿次，从而大大提高了效率。
现在这可以工作了，尽管就像其他人在评论中说的那样，如果您有适当的索引，那将是很好的。
SELECT
u.fbID, t.B, t.C, t.d
FROM
veryLargeTable AS t
CROSS JOIN (
SELECT userId, fbID FROM
smallerTable1
UNION SELECT userId, fbID FROM
smallerTable2
...
UNION SELECT userId, fbID FROM
smallerTable9
) AS u USING (userId)
您可能想先在较小的数据集上运行它，看看它的执行情况。
请理解，由于行数的原因，最有效的方法可能仍然需要一些时间。
第一个真正的问题是，您需要在PHP中。这有多绝对?如果有可能对数据库本身进行操作，您希望这样做:
--
-- Index all 9 tables on userid,fbId
select UserId,fbId
into WorkingTable_UserId_to_fbId
from table1Of9
union all
select UserId,fbId
from table_2_of_9
--
-- repeat the UNION all clause up to:
UNION ALL
select UserId,fbId
from table_9_of_9
GROUP BY 1,2
-- Index resulting table on userId,fbId
这给了你一个工作表，使基本查询如下:
select Linker.Fbid,main.b,main.c.,main.d
from mainTable main
JOIN WorkingTable_UserId_to_FbId linker on main.userId = linker.userId
如果绝对不可能创建那个表，那么你必须使用相同的代码并将其插入到上面的查询中，而且它不会那么快。应该是:
select Linker.Fbid,main.b,main.c.,main.d
from mainTable main
JOIN ( select UserId,fbId
from table1Of9
union all
select UserId,fbId
from table_2_of_9
-- etc, etc.
) Linker on main.userId = linker.userId
但是，当服务器试图收集2亿行以准备返回PHP时，这可能会中断。因此，您需要将其分成块，一次获取大约10000行。添加OFFSET可能很诱人…限制上述查询，但这仍然给服务器带来沉重的负载。最好在PHP中处理，比如:
# Very sloppy code off the top of my head,
# modify this loop based on what you know of the
# userId values
$id = 1;
while($id <= 200000000) {
$topId = $id + 9999;
$sql="select Linker.Fbid,main.b,main.c.,main.d
from mainTable main
JOIN WorkingTable_UserId_to_FbId linker on main.userId = linker.userId
WHERE main.userId between $id and $topId";
# Note: don't freak out about SQL injection in the above code,
# you are hardcoding the values of ID, not getting them from a user
#
# Execute query, retrieve rows, output
# then up the counter:
$id+=1000;
}