1.为所有的表创建索引
ADD INDEX `QunNum_index` (`QunNum`);
ADD INDEX `QQNum_index` (`QQNum`) ;
2.创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchByQQ`(IN `qq` int)
BEGIN
DECLARE i int;
DECLARE tblname VARCHAR(10);
DECLARE sql_text VARCHAR(1000);
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp`(QQNum int,nick varchar(100),QunNum int) ENGINE=MEMORY;
SET i=1;
WHILE i<1100 DO
SET tblname=CONCAT('group',i);
SET sql_text=concat('insert into `temp` select QQNum,nick,QunNum from ' , tblname , ' where QQNum=' , qq , ';');
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
SELECT * FROM `temp`;
END
3.编写PHP
赶时间，瞎写的。
<?php
set_time_limit(60);
?>
<!DOCTYPE html>
<html>
<head>
<title>QQGroup Search System v1.0</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-with,initial-scal=1">
<link href="bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div style='width:75%;margin:0 auto;'>
<form class="form-inline" role="form" action="" method="get" style="margin-bottom:10px;">
<div class="form-group">
<input type="input" class="form-control" name="qun" id="qun" placeholder="请输入QQ群号码" value=""> <input name="sbmt" type="submit" class="btn btn-primary">
</div>
</form>
<?php
if(intval($_GET['qun'])>100000)
{
@mysql_connect("localhost", "root", "123456");
@mysql_select_db("QQGroupInfo");
mysql_query("set names utf8");
$qun=intval($_GET['qun']);
$sql="select * from QQGroupInfo.qunlist".CEIL($qun/1000000)." where QunNum=$qun";
$rs=mysql_query($sql) or die(mysql_error());
$r=mysql_fetch_assoc($rs);
if($r)
{
echo "<table class='table table-striped table-bordered'>";
echo "<tr><td colspan='10'>QQ群号：{$r['QunNum']}<br>\r\n";
echo "QQ名称：{$r['Title']}<br>\r\n";
echo "QQ简介：{$r['QunText']}<br>\r\n";
$sql="select * from QQGroupDetail.group".CEIL($qun/100000)." where QunNum=$qun";
$rs=mysql_query($sql) or die(mysql_error());
echo "群 人 数：".mysql_num_rows($rs);
echo "</td></tr><tr>";
while($r=mysql_fetch_assoc($rs))
{
$i++;
echo "<td align='center'><a href='?qq={$r['QQNum']}'>{$r['QQNum']}</a><br>{$r['Nick']}</td>";
if($i%10==0) echo "</tr>\r\n\t<tr>";
}
echo "\t</tr>\r\n</table>";
}
}
elseif(intval($_GET['qq'])>10000)
{
define('CLIENT_MULTI_RESULTS', 131072);
@mysql_connect("localhost", "root", "123456",true,CLIENT_MULTI_RESULTS);
@mysql_select_db("QQGroupDetail");
mysql_query("set names utf8");
$qq=intval($_GET['qq']);
$rs=mysql_query("call SearchByQQ($qq);") or die(mysql_error());
echo "<table class='table table-striped table-bordered table-hover'>";
echo "<tr><td>QQ号码</td><td>昵称</td><td>群号码</td></tr>";
while($r=mysql_fetch_assoc($rs))
{
echo "<tr><td>{$r['QQNum']}</td><td>{$r['nick']}</td><td><a href='?qun={$r['QunNum']}'>{$r['QunNum']}</a></td></tr>";
}
echo "</table>";
}
?>
</div>
</body>
</html>
4.总结
在VPS上，在1100个表上查找QQ加入的QQ，大约需要20s，如果是SSD应该在几秒内。
尝试使用Sphinx全文索引，发现需要50多G……放弃了。
还有一种方法，空间换效率，提前把每个QQ号加入的群查出来放到一个表里。
