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

Question

我使用 Codeigniter 3.1.11 并有一个问题。我需要通过 Cron 每 1 小时更新 MySQL 表中大约 100 万行（将来会更多）。但问题是，如果我使用此代码更新超过约 200-300 行，我的服务器 CPU 已 100% 加载，并且表在约 200-300 行后停止更新。我什至必须重新启动服务器上的 PHP 才能使服务器恢复正常。我做错了什么？如何正确地完成这个任务，使得对数据库的查询快速执行并且不会给服务器带来沉重的负载。这是来自控制器的代码：function cron_rows_update() {$this->members_model->rows_update();} 

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');

    }

}

您还可以尝试使用控制器中的计时器功能在一定时间间隔后触发更新并增加服务器容量，如评论中所述。

Answer 2

除了您的代码有点令人困惑（$PER_part2并且$PER_part3未使用）这一事实之外，我还会执行以下操作：

该脚本将无法运行result_array超过 100 万次迭代。原因是，result_array将所有数据存储在数组中 - 迟早你会遇到内存限制问题。

为了避免这种情况，你必须使用unbuffered_row. 此方法返回单个结果行，而不在内存中预取整个结果。

接下来我要改变的是你的 if 块 - 我将使用if/else这里的语法。（差别不是很大，但考虑到你的行数 - 它可能会有所帮助）

另外，我外包了您使用相同逻辑两次计算评级的块。

基本上以下应该有效：

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();

    

    $arrUpdateBatchData = [];

    

    while ($row = $query->unbuffered_row('array'))

    {

        $game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);

        if ($game_total_balance > 0) {

            // Game Rating Calculate

            // Rating Part1

            // Rating Part1_1

            $rating_part1_1 = $this->getRatingPart($row['game_vault_balance']);

            $rating_part1_2 = $this->getRatingPart($game_total_balance);

            // Rating part1_3

            $rating_part1_3 = 0;

            // Rating part1_4

            $rating_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;

            }

            elseif ($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;

            

            $arrUpdateData = [

                'UserID' => $row['UserID'], 

                'game_vault_balance' => ($row['game_vault_balance'] - $amount_from_game_vault_in_hour),

                'game_available_balance' => ($row['game_available_balance'] - $game_balance_in_hour_amount),

                'rating' => $rating_member,

                'game_rating_and_balance_update_last_time' => date('Y-m-d H:i:s')

            ];

            

            $arrUpdateBatchData[] = $arrUpdateData;

            

        }

        

        if (count($arrUpdateBatchData) > 500)

        {

            $this->db->update_batch('members', $arrUpdateBatchData, 'UserID');

            $arrUpdateBatchData = [];

        }       

    }

    

    //update last items

    if (count($arrUpdateBatchData) > 0)

    {

        $this->db->update_batch('members', $arrUpdateBatchData, 'UserID');

        $arrUpdateBatchData = [];

    }

    return;    

}

function getRatingPart($val)

{

    if ($val == 0) {

        $rating_part = 0; 

    }

    elseif ($val > 0 AND $val < 20) 

    {

        $rating_part = '0.1'; 

    }

    elseif ($val > 20 AND $val < 2000) 

    {

    

        $max_game_vault_balance = 2000;

        $percent = floor($val * 100 / $max_game_vault_balance);

        $additional_rating = 0.05 * $percent / 100;

           

        $rating_part = round(0.1 + $additional_rating, 2); 

    }

    else {

        $rating_part = 0.15; 

    }

    

    return $rating_part;

}