mysql update 几万 非常慢_mysql进行update操作速度慢,如何解决

PHP 默认是同步无并发请求操作，如果使用 for 循环更新数据效率很慢，正常每秒处理 30 条 SQL 语句，对于几百万数据来说简直是灾难。而改造后速度在每秒 200 条更新数据。

旧版逻辑

$mysql = new \Tool\MysqlQuery();

$sql = "SELECT count(*) FROM `big_data` where 1";

$count = $mysql->getOne($sql);

$limit = 1000;

$start = time();

for ($i = 1; $i < ($count / $limit) + 1; $i++) {

$sql = "SELECT * FROM `big_data` where 1 limit ".(($i-1)*$limit).', '.$limit;

echo $sql.PHP_EOL;

$avgs = $mysql->getAll($sql);

foreach ($avgs as $avg) {

$sql1 = "UPDATE `big_data` SET mobile = '".readSafeData($avg['mobile'])."' where id = {$avg['id']}";

$rs = $mysql->query($sql1);

if (!$rs) {

echo 'Error: '.$sql1.PHP_EOL;

}

// var_dump($rs);

usleep(10000);

}

}

$end = time();

$time = $end - $start;

echo 'Finish ! Cost '.$time.' S '.PHP_EOL;

改造批量处理

/**

* MySQL 大数据批量更新操作

*

* User: lisgroup

* Date: 2019-01-22

* Time: 16:03

*/

/**

* 生成最终 SQL 类似结构：

*

* UPDATE `big_data` SET

* realname = CASE id

* WHEN 1 THEN 'val1'

* WHEN 2 THEN 'val2'

* WHEN 3 THEN 'val3'

* END,

* idcard = CASE id

* WHEN 1 THEN 'val1'

* WHEN 2 THEN 'val2'

* WHEN 3 THEN 'val3'

* END,

* mobile = CASE id

* WHEN 1 THEN 'val1'

* WHEN 2 THEN 'val2'

* WHEN 3 THEN 'val3'

* END

* WHERE id IN (1, 2, 3)

*/

require_once __DIR__.'/../Application.php';

$config = ['DB_HOST' => 'localhost', 'DB_PORT' => '3306', 'DB_USER' => 'root', 'DB_PASS' => 'root', 'DB_NAME' => 'test', 'DB_CHARSET' => 'utf8'];

$mysql = new \Tool\MysqlQuery($config);

$sql = "SELECT count(*) FROM `big_data` limit 1000";

$count = $mysql->getOne($sql);

// $count = 10000;

$limit = 1000;

$start = time();

for ($i = 1; $i < ($count / $limit) + 1; $i++) {

$sql = "SELECT * FROM `big_data` limit ".(($i - 1) * $limit).', '.$limit;

echo $sql.PHP_EOL;

$avgs = $mysql->getAll($sql);

// 数据拼接

$ids = '';

// 批量更新 sql 语句

$sql = "UPDATE `big_data` SET ";

$sql_name = ' realname = CASE id ';

$sql_idcard = ' idcard = CASE id ';

$sql_mobile = ' mobile = CASE id ';

foreach ($avgs as $avgValue) {

$sql_name .= sprintf("WHEN %d THEN '%s' ", $avgValue['id'], randData($avgValue['realname']));

$sql_idcard .= sprintf("WHEN %d THEN '%s' ", $avgValue['id'], randData($avgValue['idcard']));

$sql_mobile .= sprintf("WHEN %d THEN '%s' ", $avgValue['id'], randData($avgValue['mobile']));

// 1. 拼接 where 条件

$ids .= $avgValue['id'].',';

}

$sql = $sql.$sql_name.' END, '.$sql_idcard.' END, '.$sql_mobile.' END';

$ids = rtrim($ids, ',');

// 拼接条件

$sql .= " WHERE id IN ({$ids})";

$res = $mysql->query($sql);

if(!$res) {

echo $sql.PHP_EOL;

}

}

$end = time();

$time = $end - $start;

$min = floor($time / 60);

$second = $time % 60;

echo 'Finish ! Cost '.$min.':'.$second.' S '.PHP_EOL;

function randData($data)

{

return hash('sha256', md5($data)).mt_rand(0, 9);

}