php - 提高 `Update` 性能(行锁定问题)

Question

我在 Linux 上运行 30 个脚本 (PHP CLI)，每个脚本都在更新(循环)MySQL 数据库中的数据。

当我在终端中输入“mysqladmin proc”时，我可以看到很多行已被锁定 10-30 秒。主要是 Update 队列。如何更快地提高性能？我正在使用 InnoDB 引擎。

PHP 脚本看起来像这样:

//status and process are indexed.
$SQL = "SELECT * FROM data WHERE status = 0 AND process = '1'";
$query = $db->prepare($SQL);
$query->execute();

//about 100,000+ rows for each script
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        checking($row);
        sleep(2);
}

function checking($data) {

  $error = errorCheck($data['number']);

  if ($error) {
     //number indexed
     $SQLUpdate = "UPDATE data SET status = 2, error='$error' WHERE number = " . $data['number'];
     $update = $db->prepare($SQLUpdate);
     $update->execute();
     return false
   }


     //good?
     $SQLUpdate = "UPDATE data SET status = 1 WHERE number = " . $data['number'];
     $update = $db->prepare($SQLUpdate);
     $update->execute();


    $SQLInsert = "INSERT INTO tbl_done .....";
    $SQLInsert = $db->prepare($SQLInsert);
    $SQLInsert->execute();
}

top 命令:

top - 10:48:54 up 17 days, 10:30,  2 users,  load average: 1.06, 1.05, 1.01
Tasks: 188 total,   1 running, 187 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.8%us,  0.1%sy,  0.0%ni, 74.1%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   4138464k total,  1908724k used,  2229740k free,   316224k buffers
Swap:  2096440k total,       16k used,  2096424k free,   592384k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
32183 mysql     15   0  903m 459m 4800 S 101.8 11.4 876:53.66 mysqld

-

/etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database
max_user_connections=200
key_buffer_size = 16M
query_cache_size = 350M
tmp_table_size = 200M
max_heap_table_size  = 200M
thread_cache_size = 4
table_cache = 800
thread_concurrency = 8
innodb_buffer_pool_size = 400M
innodb_log_file_size = 128M
query_cache_limit = 500M
innodb_flush_log_at_trx_commit = 2

服务器规范:Intel Core 2 Quad Q8300，​​2.5 GHz，4GB 内存。

'mysqladmin proc':

+------+-----------------+-----------+----------------+---------+------+----------+-------------------------------------------------------------------------------
| Id   | User            | Host      | db             | Command | Time | State    | Info                                                                          
+------+-----------------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------
|  265 | user            | localhost | xxxxxxxxxxxxxx | Query   |   15 | Updating | UPDATE data SET status = '2', error = 'Unknown error'  WHERE number= 0xxxxx    
|  269 | user            | localhost | xxxxxxxxxxxxxx | Query   |   17 | Updating | UPDATE data SET status = '2', error = 'Invalid ....'  WHERE number= 0xxx 
|  280 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = 1  WHERE f = 0xxxx                                           
|  300 | user            | localhost | xxxxxxxxxxxxxx | Query   |    1 | Updating | UPDATE data SET status = '2', error = 'Unknown ....'  WHERE number= 0xx             
|  314 | user            | localhost | xxxxxxxxxxxxxx | Query   |   13 | Updating | UPDATE data SET status = '2', error = 'Invalid....'  WHERE number= 0xxxx
|  327 | user            | localhost | xxxxxxxxxxxxxx | Query   |   11 | Updating | UPDATE data SET status = '2', error = 'Unknown ....'  WHERE number= 0xxxx               
|  341 | user            | localhost | xxxxxxxxxxxxxx | Sleep   |    2 |          | NULL                                                                                      
|  350 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = '2', error = 'Unknown ....'  WHERE number= 0xxx                
|  360 | user            | localhost | xxxxxxxxxxxxxx | Query   |    5 | Updating | UPDATE data SET status = 1  WHERE number = 0xxxx     

解释:

+----+-------------+-------+-------------+----------------+----------------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type        | possible_keys  | key            | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+-------------+----------------+----------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | data  | index_merge | process,status | process,status | 52,1    | NULL | 16439 | Using intersect(process,status); Using where |
+----+-------------+-------+-------------+----------------+----------------+---------+------+-------+----------------------------------------------+

Answer 1

当您执行select 查询时，您正在获取正在读取的行的读取锁。在检查方法中，您正在尝试更新当前正在读取(=锁定)的行。因此，一旦选择查询释放读锁，MySQL 就会将更新查询排队执行。但是由于您将每行的执行暂停两秒钟，因此您增加了释放锁的延迟，这反过来又延迟了等待更新队列的每个查询。您可以阅读更多关于 innodb lock modes 的信息.

我建议这样修改代码:

• 将您的选择查询限制为仅返回有限数量的行，并确保它将在下一次迭代期间选择剩余的行。您可以通过在选择查询中使用 offset 和 limit 语句来实现这一点。
• 将选择查询中的所有行读入变量数组并释放查询，这样读锁也将被释放
• 遍历数字数组，更新每一行
• 从中断处继续第一步。

更新

您正在使用 fetch 从结果集中检索行。根据documentation :

Fetches a row from a result set associated with a PDOStatement object

为了一次检索所有行，您应该使用fetchAll 但要注意性能问题，因为 documentation状态:

Using this method to fetch large result sets will result in a heavy demand on system and possibly network resources.

这就是为什么我建议限制查询以检索特定数量的行，而不是整个结果集(由 100.000 多行组成)。您可以通过修改查询来限制返回的行数:

SELECT * FROM data WHERE status = 0 AND process = '1' LIMIT 10000 OFFSET 0

然后当您第二次运行查询时，运行查询如下:

SELECT * FROM data WHERE status = 0 AND process = '1' LIMIT 10000 OFFSET 10000

您可以继续这样，直到没有返回结果为止。