php - 每 1 小时更新 MySQL 表中约 100 万行

Question

我使用 Codeigniter 3.1.11 并有一个问题。我需要通过 Cron 每 1 小时更新 MySQL 表中大约 100 万(将来会更多)的行。但问题是，如果我使用此代码更新超过 200-300 行，我的服务器 CPU 已 100% 加载并且表在大约 200-300 行后停止更新。而且我什至必须重新启动服务器上的 PHP 才能使服务器恢复正常。
我做错了什么？
如何正确地完成这个任务，使得对数据库的查询执行得很快，服务器上没有很重的负载。
这是 Controller 的代码:

function cron_rows_update() {
$this->members_model->rows_update();
} 

这是模型中的代码:

function rows_update() {
   $currency_numbers_after_dot = $this->currencies_model->get_currency('ONE', 'My currencty')['numbers_after_dot'];
   $game_currency_percentage_max = $this->settings_model->get_settings_details('game_rating_percentage_max')['value'];
   $game_currency_speed_in_hour = $this->settings_model->get_settings_details('game_currency_speed_in_hour')['value'];
   $this->db->from('members');
   $query = $this->db->get();
   if($query->num_rows() > 0) {
    foreach($query->result_array() as $row) {
        $game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);

        // Game Rating Calculate
        // Rating Part1
        // Rating Part1_1
        if ($row['game_vault_balance'] == '0') {
            $rating_part1_1 = '0'; 
        }
        if ($row['game_vault_balance'] > '0' AND $row['game_vault_balance'] < '20') {
            $rating_part1_1 = '0.1'; 
        }
        if ($row['game_vault_balance'] > '20' 
            AND $row['game_vault_balance'] < '2000') {
        
            $max_game_vault_balance = '2000';
            $percent = floor($row['game_vault_balance'] * 100 / $max_game_vault_balance);
            $additional_rating = '0.05' * $percent / 100;
               
            $rating_part1_1 = round('0.1' + $additional_rating, 2); 
    
        }
        if ($row['game_vault_balance'] >= '2000') {
            $rating_part1_1 = '0.15'; 
        }
   
        // Rating Part1_2
        if ($game_total_balance == '0') {
            $PER_part1_2 = '0'; 
        }
        if ($game_total_balance > '0' AND $game_total_balance < '20') {
            $rating_part1_2 = '0.1'; 
        }
        if ($game_total_balance > '20' AND $game_total_balance < '2000') {
            $max_game_total_balance = '2000';
            $percent = floor($game_total_balance * 100 / $max_game_total_balance);
            $additional_rating = '0.05' * $percent / 100;
            $rating_part1_2 = round('0.1' + $additional_rating, 2); 
        }
        if ($game_total_balance >= '2000') {
            $rating_part1_2 = '0.15';
        }
        // Rating part1_3
        $rating_part1_3 = '0';
        // Rating part1_4
        $PER_part1_4 = '0';
        // Rating part2
        $PER_part2 = '0';
        // Rating part3
        $PER_part3 = '0';
        // Calculate all rating
        $rating = round($rating_part1_1 + $rating_part1_2 + $rating_part1_3 + $rating_part1_4 + $rating_part2 + $rating_part3, 2);
   
        if ($rating <= '1') {
            $rating_member = $rating;
        }
        if ($rating > '1') {
            $rating_member = floor($rating);
        }
        // Game balance calculate
        $amount_from_game_vault_in_hour = $game_currency_speed_in_hour / '100' * $row['game_vault_balance'];
        $new_balance_in_hour = ($game_currency_percentage_max / '100') * $row['rating'] * $amount_from_game_vault_in_hour;
        $game_balance_in_hour_amount = $amount_from_game_vault_in_hour + $new_balance_in_hour;

        // Update row in members table

        if ($game_total_balance > '0') {
            $this->db->where("UserID", $row['UserID']);
            $this->db->set("game_vault_balance", "game_vault_balance - " . $amount_from_game_vault_in_hour, FALSE);
            $this->db->set("game_available_balance", "game_available_balance + " . $game_balance_in_hour_amount, FALSE);
            $this->db->set("rating", $rating_member, FALSE);
            $this->db->set("game_rating_and_balance_update_last_time", 'NOW()', FALSE);
            $this->db->update("members");
        }
    } 
}
return;    
}

Answer 1

尝试限制更新而不是一次更新。分块更新。

function cron_rows_update() {
   $num_of_members = $this->members_model->get_num_of_members();
   $limit_per_run = 150;
   $total_run = (int)$num_of_members/$per_run;
   
   for( $i = 0; $i <= $total_run; $i++ ) {
      $offset = $i * $limit_per_run;
      $this->members_model->rows_update($offset, $limit_per_run);
   }

}

并在 rows_update()

function rows_update($offset, $limit_per_run) {
    
    **

    $this->db->limit($offset, $limit_per_run);
    $query = $this->db->get();
    
    $arrUpdateBatchData = [];
    
    while ($row = $query->unbuffered_row('array'))
    {
        // calculations and create array for batch update
    }

    // update in batch limiting to selected number of records
    if (count($arrUpdateBatchData) > 0)
    {
        $this->db->update_batch('members', $arrUpdateBatchData, 'UserID');
    }
}

您还可以尝试在 Controller 中使用计时器功能在一定时间间隔后触发更新并增加评论中提到的服务器容量。
阅读更多 here有关尽可能快地进行大量记录更新并减少服务器负载的进一步说明。