对于100 k行查询，PHP最大执行时间超过30秒

Question

是否可以编写php代码，该代码将获取序列化数据、取消序列化并在新的数据库架构上写入内容而没有

ini_set("max_execution_time"，0)；

我想我已经优化了我的代码，但这是无法避免的。

我在迭代中使用以下代码。

$queryCount ="SELECT count(*) FROM APPLICATION where PRO_UID='$PRO_UID' 
                AND APP_STATUS != 'DRAFT' 
                AND APP_STATUS != 'CANCELLED'";
    $resultCount = $mysqli->query($queryCount);
    $count = (mysqli_fetch_array($resultCount));

    for($x=0;$x < $count; $x += 1000){


        $queryData = "SELECT * FROM wf_workflow.APPLICATION 
                        where PRO_UID='$PRO_UID' 
                        AND APP_STATUS != 'CANCELLED' 
                        AND APP_STATUS != 'DRAFT' 
                        not in (SELECT APP_NUMBER FROM export_workflow.CARDS_CONTACT_DETAILS_LOOKUP) LIMIT 2000";


        $resultData = $mysqli->query($queryData);

        while($row = mysqli_fetch_array($resultData)){

            $data = unserialize($row['APP_DATA']);
            $APP_NUMBER = $row['APP_NUMBER'];
            $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
            $MOBILE = $data["ContactDetails"][1]["MobileNo"];
            $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
            $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

            $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                    VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

            if (mysqli_query($mysqli, $sql)) {
                echo "New record created successfully FROM dev";
            } else {
                echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
            }

        }

    echo "finished the dev";

    }

Answer 1

第一个查询似乎只获得用于for循环的计数。但是for循环似乎并不是必需的。

主查询是set，APP_STATUS !=‘草稿’不在其中(SELECT没有检查值/列在子查询的结果集中。怀疑这只会检查是否为true或false ( APP_STATUS !=‘草稿’的结果)是子查询返回的值。查看您的代码，我认为您的意思是让和APP_STATUS !=‘草稿’和APP_NUMBER不在(选择)。但是，IN不能很好地执行，所以最好将其重新编码为联接。

考虑到这一点，像这样的事情(未经测试)：-

<?php

    $queryData = "SELECT a.APP_NUMBER, a.APP_DATA 
                    FROM wf_workflow.APPLICATION a
                    LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
                    ON a.APP_NUMBER = b.APP_NUMBER
                    WHERE a.PRO_UID='$PRO_UID' 
                    AND a.APP_STATUS != 'CANCELLED' 
                    AND a.APP_STATUS != 'DRAFT' 
                    AND b.APP_NUMBER IS NULL";


    $resultData = $mysqli->query($queryData);

    while($row = mysqli_fetch_array($resultData))
    {

        $data = unserialize($row['APP_DATA']);
        $APP_NUMBER = $row['APP_NUMBER'];
        $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
        $MOBILE = $data["ContactDetails"][1]["MobileNo"];
        $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
        $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

        $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

        if (mysqli_query($mysqli, $sql)) 
        {
            echo "New record created successfully FROM dev";
        } 
        else 
        {
            echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
        }
    }

    echo "finished the dev";

您还可以通过分批插入来提高脚本的性能(例如，一次插入100行)。这可能不会对脚本超时产生太大影响(等待MySQL的时间不应影响脚本的时间限制)，但可能会加快速度。就像这样：-

$queryData = "SELECT a.APP_NUMBER, a.APP_DATA 
                FROM wf_workflow.APPLICATION a
                LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
                ON a.APP_NUMBER = b.APP_NUMBER
                WHERE a.PRO_UID='$PRO_UID' 
                AND a.APP_STATUS != 'CANCELLED' 
                AND a.APP_STATUS != 'DRAFT' 
                AND b.APP_NUMBER IS NULL";


$resultData = $mysqli->query($queryData);

$inserts = array();

while($row = mysqli_fetch_array($resultData))
{

    $data = unserialize($row['APP_DATA']);
    $APP_NUMBER = $row['APP_NUMBER'];
    $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
    $MOBILE = $data["ContactDetails"][1]["MobileNo"];
    $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
    $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

    $inserts[] =  "'$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

    if (count($inserts) > 100)
    {
        $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                VALUES ".implode(', ', $inserts);
        $inserts = array();
        if (mysqli_query($mysqli, $sql)) 
        {
            echo "New records created successfully FROM dev";
        } 
        else 
        {
            echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
        }
    }
}

if (count($inserts) > 0)
{
    $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
            VALUES ".implode(', ', $inserts);
    $inserts = array();
    if (mysqli_query($mysqli, $sql)) 
    {
        echo "New records created successfully FROM dev";
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
    }
}

echo "finished the dev";

不过，你最好还是逃离你的数据吧！