php - 优化mysql查询-foreach循环

Question

我需要使用 php 将大型 XML 文件解析到 mysql(500.000 行 xml)。但使用下面的代码，一个 xml 文件需要几个小时。这如何优化？ (我想也许制作一个数组来立即解析到 mysql，而不是一次解析每个变量？)

foreach ($data as $dat) {
        $object = $dat;
        $UID = $object['id'];
        $test = mysql_query("SELECT * FROM reports WHERE UID = '$UID'");
        if ($test['UNIQUEID'] ==null) {

            $temp = array("MEDIA" => "{$name}");
            foreach ($object as $obj){
                mysql_query("INSERT INTO reports
                    (MEDIA, UID)
                    VALUES
                    ('$name', '$UID')");
                foreach ($obj as $ats) {
                    $attname = $ats['name'];

                    mysql_query("UPDATE reports 
                                SET $attname = '$ats'
                                WHERE UID = '$UID'
                                ");

                } 

            }
        }
        echo "Done";

    }

编辑:XML:

    <object id="382177">
    <attributes>
      <attribute kind="number" name="REVNO">1</attribute>
      <attribute kind="string" name="UNIQUEID">XXX</attribute>
      <attribute kind="number" name="EVENTVERSION">1</attribute>
      <attribute kind="string" name="EVENTASSOCID">4568190</attribute>
      <attribute kind="number" name="EVENTASSOCRELNO">2</attribute>
      <attribute kind="string" name="EVENTTYPE">PageFlow</attribute>
      <attribute format="%Y-%m-%d %H:%M:%S" kind="time" name="EVENTTIME">2014-02-09 09:40:52</attribute>
      <attribute kind="string" name="EVENTMSG">PageLocked=1</attribute>
      <attribute kind="string" name="EVENTUSER">XXX</attribute>
      <attribute kind="string" name="EVENTAPPL">XXX</attribute>
      <attribute kind="string" name="NAME">XXX</attribute>
      <attribute kind="string" name="NEWSROOM">XXX</attribute>
      <attribute kind="string" name="PRODUCT">XXX</attribute>
      <attribute kind="string" name="PUBDATE">11-02-2014</attribute>
      <attribute kind="string" name="ZONE">XXX</attribute>
      <attribute kind="string" name="EDITION">1</attribute>
      <attribute kind="string" name="PAGENAME">XXX</attribute>
      <attribute kind="number" name="PAGENO">1</attribute>
      <attribute kind="string" name="ARTICLE"></attribute>
     </attributes>
  </object>

编辑 2:感谢 Mike，这段代码极大地提高了性能:

        foreach ($data as $key) {

        $lat = array();
        $lat = $key->attributes;

        $UID = $key['id'];

        mysql_query("INSERT INTO reports
                    (MEDIA, UID, REVNO, UNIQUEID, EVENTVERSION, EVENTASSOCID, EVENTASSOCRELNO, EVENTTYPE, EVENTTIME, EVENTMSG, EVENTUSER, EVENTAPPL, NAME, NEWSROOM, PRODUCT, PUBDATE, ZONE, EDITION, PAGENAME, PAGENO, ARTICLE, LAYOUTDESK, LAYOUTSTATE, RUNNINGPAGENO, SECTIONNAME, SECTIONNO, LASTOPERATOR, LASTREV, LASTDATAOPERATOR, LASTDATAREV, TYPE, SUBTYPE, LAYOUTTEMPLATE, EDITORIALSOURCEUID)
                    VALUES
                    ('$name', '$UID', '{$lat->attribute[0]}', '{$lat->attribute[1]}', '{$lat->attribute[2]}', '{$lat->attribute[3]}', '{$lat->attribute[4]}', '{$lat->attribute[5]}', '{$lat->attribute[6]}', '{$lat->attribute[7]}', '{$lat->attribute[8]}', '{$lat->attribute[9]}', '{$lat->attribute[10]}', '{$lat->attribute[11]}', '{$lat->attribute[12]}', '{$lat->attribute[13]}', '{$lat->attribute[14]}', '{$lat->attribute[15]}', '{$lat->attribute[16]}', '{$lat->attribute[17]}', '{$lat->attribute[18]}', '{$lat->attribute[19]}', '{$lat->attribute[20]}', '{$lat->attribute[21]}', '{$lat->attribute[22]}', '{$lat->attribute[23]}', '{$lat->attribute[24]}', '{$lat->attribute[25]}', '{$lat->attribute[26]}', '{$lat->attribute[27]}', '{$lat->attribute[28]}', '{$lat->attribute[29]}', '{$lat->attribute[30]}', '{$lat->attribute[31]}')");


    }

Answer 1

插入一条记录，然后一次更新多个列将带来可怕的性能。您应该从 XML 中解析每条记录的所有数据，并将其全部插入到一个 INSERT 中。这会给你带来性能上的巨大提升

此外，如果您在插入之前检查每个插入的唯一性，您应该使用唯一索引修改架构并使用“INSERT...IGNORE...”，检查受影响的行数以检查每行是否已插入(如果您需要检查)。

您可以使用INSERT的批处理形式一次插入一批记录。

如果这还不够，请尝试先将 XML 解析为 CSV，然后使用 LOAD DATA INFILE 批量插入所有数据。