如何遍历3500万行的表- Mysql

Question

我有一个非常大的mysql表，其中存储域和子域。及其create语法如下所示

CREATE TABLE `domain` (
  `domain_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`domain_id`),
  UNIQUE KEY `dn` (`domain_name`),
  KEY `ind_domain_name` (`domain_name`)
) ENGINE=InnoDB AUTO_INCREMENT=78364364 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;

并且它存储的值如下

domain_id | domain_name

1         | a.example.com

2         | b.example.com 

3         | example.com

4         | facebook.com

5         | a.facebook.com

6         | google.com

我想要找到任何顶级域名的子域，然后我会将子域与其父域进行匹配。

例如，a.example.com和b.example.com是example.com的子域，因此在我的名为example.com的新列中，我将设置Example.com的domain_id。(如果域名是顶级域名，则其parent_domain_id为0)

我使用PHP和mysql，我的机器有8 8GB的RAM，所以我有一些设备限制。用PHP逐行检查庞大的数据集有什么诀窍吗？

Answer 1

编辑:对于大多数域名来说，这应该是可行的。

您可以获得一个排序列表，其中包含所有域(最多固定长度)，按域和递增长度排序。

我会从@RyanVincent的评论开始。

select domain_id, domain_name, reverse(domain_name) as reversed
from domain
order by 
rpad(reverse(domain_name),130,' '),
length(domain_name),
reverse(domain_name), 
domain_id

目标是能够按长度逐行排序，然后按字母表排序。

您的示例将给出

        google.com -> moc.elgoog, 
      a.google.com -> moc.elgoog.a
   xy.a.google.com -> moc.elgoog.a.yx
      b.google.com -> moc.elgoog.a

在php中：

  $currentDomains = array(); 
  /*
     array domainpart => id
     moc     => 0
     elgoog  => id of google.com as long as we are in subdomains of google.com
     a       => id of a.google.com as long as we are in subdomains of a.google.com

     this gets never longer then the number of domainparts, so usually a very
     short array!
   */

  $sql = "select domain_id, domain_name, reverse(domain_name) as reversed\n"
      . " from domain \n"
      . " order by  \n"
      . " rpad(reverse(domain_name),130,' '), \n"
      . " length(domain_name), \n"
      . " reverse(domain_name),  \n"
      . " domain_id"
      ;

  doSelect($sql);

  while($row = getRow()){
    $parts = preg_split('/\./', $row["reversed"]);
    # print("parts = \n");print_r($parts);
    $rid = $row["domain_id"];

    $matchedDomains = array();
    $parentId = $rid; // if no parent, show to yourself

    $i = 0;
    // 1. match identical parts
    //     php is funny, the array is a key=>value but with 
    //     foreach it restores the key-values in the inserted order.
    foreach($currentDomains as $name => $cid){
      # print("> check $i '$name' '{$parts[$i]}'\n");
      if($parts[$i] == $name){
        $matchedDomains[$name] = $cid;
        if($cid > 0){
          $parentId = $cid;
        }
        $i++;
      }
      else{
        break;
      }
    }
    // 2.
    // new parts
    while ($i < count($parts)-1){
      # print("> store '{$parts[$i]}' \n");
      $matchedDomains[$parts[$i]] = 0; // no row matches those
      $i++;
    }
    $matchedDomains[$parts[count($parts)-1]] = $rid;
    $currentDomains = $matchedDomains;
    print(" update domain set parent_id = $parentId where id = $rid\n"); // use PDO
  }

所以google.com是它自己的父域，a.google.com有google.com作为父域，依此类推。