search
HomeBackend DevelopmentPHP TutorialSphinx+Mysql+Php 12亿DNS数据秒查

    最近得到一个接近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 dnsfields 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 asdDEPRECATED: Do not call this method or, even better, use SphinxQL instead of anAPIQuery 'asd ' retrieved 1000 of 209273 matches in 0.007 secQuery stats:        'asd' found 209291 times in 209273 documentsMatches:1. doc_id=20830, weight=12. doc_id=63547, weight=13. doc_id=96147, weight=14. doc_id=1717000, weight=15. doc_id=2213385, weight=16. doc_id=3916825, weight=17. doc_id=3981791, weight=18. doc_id=5489598, weight=19. doc_id=9348383, weight=110. doc_id=18194414, weight=111. doc_id=18194415, weight=112. doc_id=18195126, weight=113. doc_id=18195517, weight=114. doc_id=18195518, weight=115. doc_id=18195519, weight=116. doc_id=18195520, weight=117. doc_id=18195781, weight=118. doc_id=18195782, weight=119. doc_id=18200301, weight=120. doc_id=18200303, weight=1

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

<?phpinclude '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.com123.125.104.178:w-178.service.weibo.com123.125.104.179:w-179.service.weibo.com123.125.104.207:w-207.service.weibo.com123.125.104.208:w-208.service.weibo.com123.125.104.209:w-209.service.weibo.com123.125.104.210:w-210.service.weibo.com202.106.169.235:staff.weibo.com210.242.10.56:weibo.com.tw218.30.114.174:w114-174.service.weibo.com219.142.118.228:staff.weibo.com60.28.2.221:w-221.hao.weibo.com60.28.2.222:w-222.hao.weibo.com60.28.2.250:w-222.hao.weibo.com61.135.152.194:sina152-194.staff.weibo.com61.135.152.212:sina152-212.staff.weibo.com65.111.180.3:pr1.cn-weibo.com160.34.0.155:srm-weibo.us2.cloud.oracle.com202.126.57.40:w1.weibo.vip.hk3.tvb.com202.126.57.41:w1.weibo.hk3.tvb.com0
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
11 Best PHP URL Shortener Scripts (Free and Premium)11 Best PHP URL Shortener Scripts (Free and Premium)Mar 03, 2025 am 10:49 AM

Long URLs, often cluttered with keywords and tracking parameters, can deter visitors. A URL shortening script offers a solution, creating concise links ideal for social media and other platforms. These scripts are valuable for individual websites a

Working with Flash Session Data in LaravelWorking with Flash Session Data in LaravelMar 12, 2025 pm 05:08 PM

Laravel simplifies handling temporary session data using its intuitive flash methods. This is perfect for displaying brief messages, alerts, or notifications within your application. Data persists only for the subsequent request by default: $request-

Build a React App With a Laravel Back End: Part 2, ReactBuild a React App With a Laravel Back End: Part 2, ReactMar 04, 2025 am 09:33 AM

This is the second and final part of the series on building a React application with a Laravel back-end. In the first part of the series, we created a RESTful API using Laravel for a basic product-listing application. In this tutorial, we will be dev

Simplified HTTP Response Mocking in Laravel TestsSimplified HTTP Response Mocking in Laravel TestsMar 12, 2025 pm 05:09 PM

Laravel provides concise HTTP response simulation syntax, simplifying HTTP interaction testing. This approach significantly reduces code redundancy while making your test simulation more intuitive. The basic implementation provides a variety of response type shortcuts: use Illuminate\Support\Facades\Http; Http::fake([ 'google.com' => 'Hello World', 'github.com' => ['foo' => 'bar'], 'forge.laravel.com' =>

cURL in PHP: How to Use the PHP cURL Extension in REST APIscURL in PHP: How to Use the PHP cURL Extension in REST APIsMar 14, 2025 am 11:42 AM

The PHP Client URL (cURL) extension is a powerful tool for developers, enabling seamless interaction with remote servers and REST APIs. By leveraging libcurl, a well-respected multi-protocol file transfer library, PHP cURL facilitates efficient execution of various network protocols, including HTTP, HTTPS, and FTP. This extension offers granular control over HTTP requests, supports multiple concurrent operations, and provides built-in security features.

12 Best PHP Chat Scripts on CodeCanyon12 Best PHP Chat Scripts on CodeCanyonMar 13, 2025 pm 12:08 PM

Do you want to provide real-time, instant solutions to your customers' most pressing problems? Live chat lets you have real-time conversations with customers and resolve their problems instantly. It allows you to provide faster service to your custom

Announcement of 2025 PHP Situation SurveyAnnouncement of 2025 PHP Situation SurveyMar 03, 2025 pm 04:20 PM

The 2025 PHP Landscape Survey investigates current PHP development trends. It explores framework usage, deployment methods, and challenges, aiming to provide insights for developers and businesses. The survey anticipates growth in modern PHP versio

Notifications in LaravelNotifications in LaravelMar 04, 2025 am 09:22 AM

In this article, we're going to explore the notification system in the Laravel web framework. The notification system in Laravel allows you to send notifications to users over different channels. Today, we'll discuss how you can send notifications ov

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft