mysqlupdate⼏万⾮常慢_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语句
$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);
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。