首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL选择百万条记录来生成urls

问MySQL选择百万条记录来生成urls
EN

Stack Overflow用户
提问于 2013-10-01 04:03:08
回答 1查看 356关注 0票数 0

目前,我从不同的表中获得200万条记录,以生成一个url来创建一个站点地图。脚本占用了太多的资源,并且使用了100%的服务器性能。

查询

代码语言:javascript
复制
 SELECT CONCAT("/url/profile/id/",u.id,"/",nickname) as url FROM users AS u
    UNION ALL
    Select CONCAT("url/city/", c.id, "/paramId/",p.id,"/",Replace(p.title, " ", "+"),"/",r.region_Name,"/",c.city_Name) AS url
    From city c 
    Join region r On r.id = c.id_region 
    Join country country On country.id = c.id_country
    cross join param p
    Where country.used = 1
    And p.active = 1 

//我将其存储在数组$url_list上,然后处理创建sitemap..but,这需要时间和资源

//我试图使用LIMIT 0,50000批量获取数据,但是获取分页的最大行需要时间。而且,代码看起来不太好,因为我必须运行一个包含大量数据的两个查询

代码语言:javascript
复制
$url_list = array();


$maxrow = SELECT COUNT(*) AS max from (
 SELECT CONCAT("/url/profile/id/",u.id,"/",nickname) as url FROM users AS u
        UNION ALL
        Select CONCAT("url/city/", c.id, "/paramId/",p.id,"/",Replace(p.title, " ", "+"),"/",r.region_Name,"/",c.city_Name) AS url
        From city c 
        Join region r On r.id = c.id_region 
        Join country country On country.id = c.id_country
        cross join param p
        Where country.used = 1
        And p.active = 1) as tmp

$limit = 50,000;
$bybatch = ceil($maxrow/$limit);
$start = 0;
for($i = 0;$i < $bybatch; $i++){
   // run query and store to $result
       (SELECT CONCAT("/url/profile/id/",u.id,"/",nickname) as url FROM users AS u
        UNION ALL
        Select CONCAT("url/city/", c.id, "/paramId/",p.id,"/",Replace(p.title, " ", "+"),"/",r.region_Name,"/",c.city_Name) AS url
        From city c 
        Join region r On r.id = c.id_region 
        Join country country On country.id = c.id_country
        cross join param p
        Where country.used = 1
        And p.active = 1 LIMIT $start,$limit); 

     $start += $limit;
     //push to $url_list
     $url_list = array_push($result);
}

//完成后,我使用它创建站点地图

代码语言:javascript
复制
$linkCount = 1;
        $fileNomb = 1;
        $i = 0;
foreach ($url_list as $ul) { 

            $i += 1; 
            if ($linkCount == 1) {
                $doc  = new DOMDocument('1.0', 'utf-8');
                $doc->formatOutput = true;
                $root = $doc->createElementNS('http://www.sitemaps.org/schemas/sitemap/0.9', 'urlset');
                $doc->appendChild($root);
            }


            $url= $doc->createElement("url");
            $loc= $doc->createElement("loc", $ul['url']); 
            $url->appendChild($loc);
            $priority= $doc->createElement("priority",1); 
            $url->appendChild($priority);


            $root->appendChild($url);

            $linkCount += 1;

            if ($linkCount == 49999) { 
                $f = fopen($this->siteMapMulti . $fileNomb .'.xml', "w");
                fwrite($f,$doc->saveXML());
                fclose($f);

                $linkCount = 1;
                $fileNomb += 1;
            }

        }

有什么更好的方法吗?还是为了加速表演?

添加了

为什么这比sql查询更快,却消耗了100%的服务器资源和性能?

代码语言:javascript
复制
$this->db->query('SELECT c.id, c.city_name, r.region_name, cr.country_name FROM city AS c, region AS r, country AS cr  WHERE r.id = c.id_region AND cr.id = c.id_country AND cr.id IN (SELECT id FROM country WHERE use = 1)');

$arrayCity = $this->db->recordsArray(MYSQL_ASSOC);

 $this->db->query('SELECT id, title FROM param WHERE active = 1');

$arrayParam = $this->db->recordsArray(MYSQL_ASSOC);

foreach ($arrayCity as $city) {
        foreach ($arrayParam as $param) {
          $paramTitle = str_replace(' ', '+', $param['title']);
          $url = 'url/city/'. $city['id'] .'/paramId/'. $param['id'] .'/'. $paramTitle .'/'. $city['region_name'] .'/'. $city['city_name'];
          $this->addChild($url);
        }
}
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-10-01 04:12:16

我建议您不要使用UNION,只需发出两个分开的查询。它将加速查询本身。同样,正如您前面提到的,按批获取数据是个好主意。

最后,不要收集内存中的所有数据。立即将其写入循环中的文件中。

开始时只打开文件,循环中写每个URL条目,最后关闭文件。

-打开书写档案

-计数查询用户表

-在循环中使用LIMIT执行几个选择(就像您已经做的那样)

-就在循环while ($row = mysql_fetch_array())中,将每一行写入文件

而不是对另一个表重复这样的算法。实现将数据写入文件的函数将非常有用,因此您可以调用该函数并遵守干的原则。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/19107743

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档