Sphinx+MySQL+PHP 12亿DNS数据秒查

391次阅读

共计 3383 个字符，预计需要花费 9 分钟才能阅读完成。

最近得到一个接近 12 亿的全球 ns 节点的数据，本来想用来做一个全国通过 dns 反查域名然后进行全国范围的网站收集和扫描的，后来发现网站的数量不是很准确，加上一个人的精力和财力实在难以完成这样一个庞大的任务，就没有做下去，只留下了这个搭建的笔记。

文本格式，简单的文本搜索，速度太慢，一次搜索接近花掉 5 -10 分钟时间，决定将其倒入数据库进行一次优化，速度应该能提升不到，电脑上只有 AMP 的环境，那么就决定将其倒入到 MySQL 中，

一开始使用 Navicat 进行倒入，刚好数据的格式是 ip,ns 这样的格式，倒入了接近 5 个小时发现还没有倒入到百分之一，这可是纯文本格式化的时候大小为 54G 的数据文件啊！

后来发现用 mysql 自带的 load data local infile 只话了 30 分钟左右，第一次导入的时候忘记新建键了，只好重新导入一次

mysql> load data local infile ‘E:\\dns\\rite\\20141217-rdns.txt’ into table dns
fields terminated by ‘,’;
Query OK, 1194674130 rows affected, 1700 warnings (29 min 26.65 sec)
Records: 1194674130 Deleted: 0 Skipped: 0 Warnings: 1700

因为添加了一个 id 字段，所以导入速度明显下降，不过大概也只花了 1 个半小时左右的时间就完成了 55G 数据的导入。
接着是建立索引，因为我需要的模糊查询，所以在这里建立的是 Full Text+Btree，差不多花了 3 天时间索引才建立完成，期间因为一不小心把 mysql 的执行窗口关闭了，以为就这么完蛋了，最后发现其实 mysql 还在后台默默的建立索引。
建立了索引之后发现查询速度也就比没有建立索引快那么一点，执行了一条

select * from ns where ns like ‘%weibo.com’

花掉了 210 秒的时间，还是太慢了。
然后就开始使用 SPhinx 来做索引提升速度，
从官方下载了 64 位的 SPHINX MYSQL SUPPORT 的包下载地址
接着配置配置文件,src 里配置要 mysql 的账号密码

source src1
{
sql_host = localhost
sql_user = root
sql_pass = root
sql_db = ns
sql_port = 3306
sql_query = \
SELECT id,ip,ns from ns // 这里写上查询语句
sql_attr_uint = id

然后 searchd 里也需要配置一下, 端口和日志，pid 文件的路径配置好即可

searchd
{
listen = 9312
listen = 9306:mysql41
log = E:/phpStudy/splinx/file/log.log
query_log = E:/phpStudy/splinx/file/query.log
pid_file = E:/phpStudy/splinx/file/searchd.pid

然后切换到 sphinx 的 bin 目录进行建立索引，执行

searchd test1 #test1 是你 source 的名称

我大概建立了不到 2 个小时的时间就建立完成了，
然后切换到 api 目录下执行

E:\phpStudy\splinx\api>test.py asd
DEPRECATED: Do not call this method or, even better, use SphinxQL instead of an
API
Query ‘asd ‘ retrieved 1000 of 209273 matches in 0.007 sec
Query stats:
‘asd’ found 209291 times in 209273 documents
Matches:
1. doc_id=20830, weight=1
2. doc_id=63547, weight=1
3. doc_id=96147, weight=1
4. doc_id=1717000, weight=1
5. doc_id=2213385, weight=1
6. doc_id=3916825, weight=1
7. doc_id=3981791, weight=1
8. doc_id=5489598, weight=1
9. doc_id=9348383, weight=1
10. doc_id=18194414, weight=1
11. doc_id=18194415, weight=1
12. doc_id=18195126, weight=1
13. doc_id=18195517, weight=1
14. doc_id=18195518, weight=1
15. doc_id=18195519, weight=1
16. doc_id=18195520, weight=1
17. doc_id=18195781, weight=1
18. doc_id=18195782, weight=1
19. doc_id=18200301, weight=1
20. doc_id=18200303, weight=1

进行了测试，发现速度真的很快，写了一个 PHP 脚本进行调用

<?php
include ‘sphinxapi.php’;
$conn=mysql_connect(‘127.0.0.1′,’root’,’root’);
mysql_select_db(‘ns’,$conn);
$sphinx = new SphinxClient();
$now=time();
$sphinx->SetServer (‘127.0.0.1’, 9312);
$result = $sphinx->query (‘weibo.com’, ‘test1’);
foreach($result[‘matches’] as $key => $val){
$sql=”select * from ns where id='{$key}'”;
$res=mysql_query($sql);
$res=mysql_fetch_array($res);
echo “{$res[‘ip’]}:{$res[‘ns’]}”;

}
echo time()-$now;
?>

基本实现了秒查！, 最后输出的时间只花掉了 0！

123.125.104.176:w-176.service.weibo.com
123.125.104.178:w-178.service.weibo.com
123.125.104.179:w-179.service.weibo.com
123.125.104.207:w-207.service.weibo.com
123.125.104.208:w-208.service.weibo.com
123.125.104.209:w-209.service.weibo.com
123.125.104.210:w-210.service.weibo.com
202.106.169.235:staff.weibo.com
210.242.10.56:weibo.com.tw
218.30.114.174:w114-174.service.weibo.com
219.142.118.228:staff.weibo.com
60.28.2.221:w-221.hao.weibo.com
60.28.2.222:w-222.hao.weibo.com
60.28.2.250:w-222.hao.weibo.com
61.135.152.194:sina152-194.staff.weibo.com
61.135.152.212:sina152-212.staff.weibo.com
65.111.180.3:pr1.cn-weibo.com
160.34.0.155:srm-weibo.us2.cloud.Oracle.com
202.126.57.40:w1.weibo.vip.hk3.tvb.com
202.126.57.41:w1.weibo.hk3.tvb.com
0