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

这是来自模型的代码：

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

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

请在此处阅读更多信息，了解有关尽可能快地进行大量记录更新并降低服务器负载的更多说明。

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;
}