php - 如何遍历一个3500万行的表——Mysql

Question

我有一个非常大的 mysql 表，用于存储域和子域。其创建语法如下所示

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 的子域，因此在名为 parent_domain_id 的新列中，我将设置 example.com 的 domain_id。 (如果域是顶级域，它的 parent_domain_id 将为 0 )

我使用 PHP 和 mysql，我的机器有 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 作为父域，依此类推。