mysql - 从更新表列的另一个表的数据更新表中的多个列的最快方法是什么？

Question

我有这两个有效的查询，但它们很慢。什么是更快或最快的方法？

方法一)

$query = "
    UPDATE list_data_extra 
    INNER JOIN list_data 
    ON (list_data_extra.serial_no = list_data.serial_no) 
    SET 
    list_data_extra.id = list_data.id, 
    list_data_extra.cid = list_data.cid, 
    list_data_extra.first = list_data.first, 
    list_data_extra.last = list_data.last, 
    list_data_extra.tracking_number = list_data.tracking_number 
    WHERE list_data_extra.id='0' AND list_data_extra.cid='0'
    ";

方法二)

$query = "UPDATE list_data_extra
      INNER JOIN list_data USING (serial_no)
      SET list_data_extra.id = list_data.id,
          list_data_extra.cid = list_data.cid,
          list_data_extra.first = list_data.first, 
          list_data_extra.last = list_data.last, 
          list_data_extra.tracking_number = list_data.tracking_number
     WHERE list_data_extra.id='0'
       AND list_data_extra.cid='0'";

不确定其他方法会更快:

方法三)

$query="SELECT * FROM list_data_extra WHERE id='0' AND cid='0'";
$result=mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
    while($row=mysql_fetch_array($result)) {
        $querytwo = mysql_fetch_array(mysql_query(
      "SELECT id, cid, first, last, tracking_number
         FROM list_data
        WHERE serial_no='".$row['serial_no']."'"), MYSQL_ASSOC);
        $querythree = "UPDATE list_data_extra
               SET id='".$querytwo["id"]."', cid='".$querytwo["cid"]."',
                   first='".$querytwo["first"]."', last='".$querytwo["last"]."',
                   tracking_number='".$querytwo["tracking_number"]."'";
        mysql_query($querythree);
    }
}

我尝试的另一件事是构建整个查询然后一次执行所有查询，这比上面快一点，但仍然像垃圾一样慢。上面是每 1000 条记录 9 分钟，下面是每 1000 条记录 5 分钟。

方法四)

$query="SELECT * FROM list_data_extra WHERE id='0' AND cid='0'";
$result=mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
    $id_loop = "";
    $cid_loop = "";
    $first_loop = "";
    $last_loop = "";
    $trackingnumber_loop = "";
    $listids = "";
    while($row=mysql_fetch_array($result)) {
        $querytwo = mysql_fetch_array(mysql_query("SELECT id, cid, first, last, tracking_number FROM list_data WHERE serial_no='".$row['serial_no']."'"), MYSQL_ASSOC);
        $id_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["id"]."' ";
        $cid_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["cid"]."' ";
        $first_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["first"]."' ";
        $last_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["last"]."' ";
        $trackingnumber_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["tracking_number"]."' ";
        $listids .= ", ".$row['listid'];
    }
    $listidsb = substr($listids, 2);
    $querythree = "UPDATE list_data_extra
    SET 
    id = CASE listid
        ".$id_loop."
    END,
    cid = CASE listid
        ".$cid_loop."
    END,
    first = CASE listid
        ".$first_loop."
    END,
    last = CASE listid
        ".$last_loop."
    END,
    tracking_number = CASE listid
        ".$trackingnumber_loop."
    END
    WHERE listid IN (".$listidsb.")";
    mysql_query($querythree) or die(mysql_error());
}

有没有更好更快的方法来使用另一个表中的数据更新一个表中许多记录中的多个列？

CREATE TABLE list_data (
    id int(11) NOT NULL AUTO_INCREMENT, 
    cid int(11) NOT NULL, 
    first varchar(255) NOT NULL, 
    last varchar(255) NOT NULL, 
    tracking_number varchar(255) NOT NULL, 
    serial_no varchar(9) NOT NULL, 
    PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=555555 DEFAULT CHARSET=latin1

Answer 1

未索引的 JOIN 和 WHERE 条件可能很慢，尤其是当它们涉及字符串数据时；尝试运行这两个(如果表很大，它们会花费一些时间来运行)，然后再次尝试您的原始查询。

ALTER TABLE list_data 
ADD INDEX serial_idx (serial_no);

ALTER TABLE list_data_extra 
ADD INDEX serial_idx (serial_no);