php - 如何提高使用 MySQL 的 PHP 脚本的速度？

Question

我编写了一个 PHP 脚本，使用 this library 从 excel 文件中读取一些数据.

然后按照我的任意顺序插入MySQL数据库中的一些表。

此 Excel 文件有 200 行 50 列，通常执行时间超过 60 秒。因为我的 PHP 托管不允许我使用 set_time_limit(0); 在你看来我怎样才能提高我的脚本的速度？

我读取每一行的脚本:

 function read_row($excelFile, $r){
    require_once './inc/excel_reader2.php';
    $data = new Spreadsheet_Excel_Reader($excelFile, FALSE);
    $col[0] = $data->val($r, 'E');
    $col[1] = $data->val($r, 'F');
    ...
    $col[49] = $data->val($r, 'BZ');
    return $col;
 }

我将数据插入 MySQL 的脚本:

$count = $user->rowcount($sheet=0); 
    if($final>0){
        $count = $final;
    }
    $users = 0;
    for($i = 4; $i <= $count; $i++){
        $f = $user->val($i,'D');
        $c = $user->val($i,'B');
        $l = $user->val($i,'C');
        $query = "INSERT INTO users(fullname ,ncode, location, createdtime) VALUES('$f','$c','$l', now());";
        if(mysql_query($query)){
            $users++;
        }
        $row = read_row($excelFile, $i);
        $query = "INSERT INTO `payments` (`id`, `year`, `month`, `user`, `p1`, `p2`,... `p50`) VALUES"
                . " (NULL, '$y', '$m', '$c', '$row[0]', '$row[1]', ..., '$row[49]');";
        mysql_query($query);
    } 

Answer 1

这里有几件事:

使用 microtime(true) 对您的脚本进行基准测试。了解脚本在何处花费的时间最长对您很有用。

例如

$read_file_time = microtime(true); //true makes function return value in seconds
$data = new Spreadsheet_Excel_Reader($excelFile, FALSE);
$col[0] = $data->val($r, 'E');
$col[1] = $data->val($r, 'F');
...
$col[49] = $data->val($r, 'BZ');

//Output this to screen, or log it, or whatever    
$read_file_time = microtime(true) - $read_file_time; //end time - start time

return $col;

对您的数据库插入执行相同的操作。

接下来，我建议使用批量插入。

生成行数组并一次插入所有行，而不是您现在使用的一次插入一行的方法。

所以你的数组看起来像

$rows = array(
  array('value for col-0', 'value for col-1' ... 'col-49'), //row 1
  array('value for col-0', 'value for col-1' ... 'col-49'), //row 2, etc.
);

你的查询看起来像

INSERT INTO `payments` (`id`, `year`, `month`, `user`, `p1`, `p2`,... `p50`) VALUES
(NULL, '$y', '$m', '$c', '$row[0][0]', '$row[0][1]', ..., '$row[0][49]'),
(NULL, '$y', '$m', '$c', '$row[1][0]', '$row[1][1]', ..., '$row[1][49]'),
...
(NULL, '$y', '$m', '$c', '$row[101][0]', '$row[101][1]', ..., '$row[101][49]')

根据文件的大小，您可能还需要分批执行此操作。

最后，您(正如@mellamokb 指出的那样)在每个循环中打开文件。相反，您应该尝试类似的方法:

function read_row($ExcelFile, $r){

    /*$col[0] = $ExcelFile->val($r, 'E');
    $col[1] = $ExcelFile->val($r, 'F');*/

    //Instead of the above, use a loop
    $col = array();        
    $num_cols = $ExcelFile->colcount();        
    for($i = 0; $i < $num_cols; $i++) {
      $col[] = $ExcelFile->val($r, $i);
    }

    //Or you can use column names if you really want
    //Pass the `$col_names` as a function parameter tho
    $col_names = array('E', 'F', 'BZ');
    $num_cols = count($col_names);
    for($i = 0; $i < $num_cols; $i++) {
      $col[] = $ExcelFile->val($r, $col_names[$i]);
    }


    return $col;
 }

 require_once './inc/excel_reader2.php';
 $DataFile = new Spreadsheet_Excel_Reader('path to your file', FALSE);
 for($i = 4; $i <= $count; $i++) {
   $row = read_row($DataFile, $i);
 }

因此，为了采纳我的上述建议，您可以执行以下操作:

//@param $ExcelFile : excel reader2 file object
//@param $start_row : Row to start reading from, 0 based
//@param $num_rows  : Number of rows to read
function read_n_rows($ExcelFile, $start_row, $num_rows) {
   $rows = array();
   for($i = $start_row; $i < $num_rows; $i++) {
      $rows[] = read_row($ExcelFile, $i)
   }

   return $rows;
}