mysql进行update操作速度慢,如何解决

Question

数据库有个items表，现在要update其中的userid字段、
语句是 UPDATE items SET userid = xxx WHERE userid = 0 limit 1;
已经开启事务来优化时间了，但感觉还是太慢了..更新3w条花费3分钟。
早上尝试建立个temp表，将userid->items数组存入，然后写php脚本不停的查询temp表，然后操作。
但是这样执行多个脚本的时候，只有一个能活下来...而且达不到监控的效果.
现在的想法是，将更新操作分成删除和插入..正在尝试
另外问一下，php开发的话，用什么做消息队列比较好..rabbitmq的php版，找不到文档。。。

Answer 1

可以用Replace into代替update，也可以用insert into ...on duplicate key update批量更新。

另，附上一个Rabbitmq PHP版本的demo：
https://github.com/yuansir/ra...

Answer 2

用 redis 做队列吧 一般都用这个。你这个sql 慢应该是因为limit 试着把所有 要更新的 数据 的id取出来 再去更新 会好很多把

Answer 3

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

旧版逻辑

<?php
 
$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;
 

改造批量处理

<?php
/**
 * 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);
}

参考1：https://blog.csdn.net/wuming1...

参考2：https://www.cnblogs.com/ldj3/...