php - 将许多 MySQL 查询与逻辑合并到数据文件中

Question

背景:

我正在从控制台使用 PHP 脚本将一个 330 兆的 xml 文件解析为一个数据库(netflix 目录)。

我可以每 3 秒成功添加大约 1,500 个标题直到我添加了添加 Actor 、流派和格式的逻辑。这些是由关联表链接的单独表。

现在我必须按此顺序为每个标题运行很多很多查询(我首先截断所有表格，以消除旧标题、流派等)

1. 向“标题”添加新标题并捕获插入 ID
2. 检查现有类型转换
3. 如果存在，获取id，如果不存在则插入 Actor 并获得插入ID
4. 将标题 ID 和 Actor ID 插入关联表

(也针对流派重复步骤 2-4)

这会使我的速度下降到每 3 秒 10 左右。这将需要永恒才能添加 ~250,00 个标题。

那么我如何将 4 个查询组合成一个查询，而不添加重复的 Actor 或流派

我的目标是将所有查询写入数据文件，然后进行批量插入。

我首先将所有关联查询写入一个数据文件，但这对性能没有多大帮助。

---

我首先插入标题并保存 ID

function insertTitle($nfid, $title, $year){
    $query="INSERT INTO ".$this->titles_table." (nf_id, title, year ) VALUES ('$nfid','$title','$year')";
    mysql_query($query);
    $this->updatedTitleCount++;
    return mysql_insert_id();
}

然后将其与每个 Actor 的名字结合使用以创建关联

function linkActor($value, $title_id){
    //check if we already know value
    $query="SELECT * FROM ".$this->persons_table." WHERE person = '$value' LIMIT 0,1";
    //echo "<br>".$query."<br>";
    $result=mysql_query($query);
    if($result && mysql_num_rows($result) != 0){
        while ($row = mysql_fetch_assoc($result)) {
            $value_id=$row['id'];
        }
    }else{
        //no value known, add to persons table
        $query="INSERT INTO ".$this->persons_table." (person) VALUES ('$value')";
        mysql_query($query);
        $value_id=mysql_insert_id();

    }   
    //echo "linking title:".$title_id." with rel:".$value_id;
    $query = " INSERT INTO ".$this->title_persons_table." (title_id,person_id) VALUE ('$title_id','$value_id');";
    //mysql_query($query);
    //write query to data file to be read in bulk style
    fwrite($this->fh, $query);
}

Answer 1

这是使用 prepared statements 的绝好机会.
另请查看 http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html 中的提示，例如

To speed up INSERT operations that are performed with multiple statements for nontransactional tables, lock your tables

您还可以减少查询的数量。例如。您可以使用 INSERT...ON DUPLICATE KEY UPDATE 和 LAST_INSERT_ID(expr) 消除 SELECT...FROM persons_table 以获取 ID .

(抱歉，没有时间进行冗长的描述，但我在注意到时间之前写了一个示例；-)如果这个答案没有被否决太多，我可以稍后提交。 )

class Foo {
  protected $persons_table='personsTemp';
  protected $pdo;
  protected $stmts = array();

  public function __construct($pdo) {
    $this->pdo = $pdo;
    $this->stmts['InsertPersons'] = $pdo->prepare('
      INSERT INTO
        '.$this->persons_table.'
        (person)
      VALUES
        (:person)
      ON DUPLICATE KEY UPDATE
        id=LAST_INSERT_ID(id)
    ');
  }

  public function getActorId($name) {
    $this->stmts['InsertPersons']->execute(array(':person'=>$name));
    return $this->pdo->lastInsertId('id');
  }
}

$pdo = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// create a temporary/test table
$pdo->exec('CREATE TEMPORARY TABLE personsTemp (id int auto_increment, person varchar(32), primary key(id), unique key idxPerson(person))');
// and fill in some data
foreach(range('A', 'D') as $p) {
  $pdo->exec("INSERT INTO personsTemp (person) VALUES ('Person $p')");
}

$foo = new Foo($pdo);
foreach( array('Person A', 'Person C', 'Person Z', 'Person B', 'Person Y', 'Person A', 'Person Z', 'Person A') as $name) {
  echo $name, ' -> ', $foo->getActorId($name), "\n";
}

打印

Person A -> 1
Person C -> 3
Person Z -> 5
Person B -> 2
Person Y -> 6
Person A -> 1
Person Z -> 5
Person A -> 1

(有人可能想开始讨论 getXYZ() 函数是否应该执行 INSERT ......但不是我，不是现在......)