PHP 100k 行查询超出了 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 循环似乎不是必需的。

主查询使用 AND APP_STATUS != 'DRAFT' not in (SELECT ，它不检查值/列是否在子查询的结果集中。怀疑这只会检查 true 或 false(APP_STATUS != 'DRAFT' 的结果)是否是子查询的返回值。查看您的代码，我认为您的意思是 AND APP_STATUS != ' DRAFT' AND APP_NUMBER 不在 (SELECT 中。但是 NOT 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";

不过，建议您转义数据!