怎样才能使一条涉及大约300,000行的update语句更快？

Question

我有一个Laravel应用程序，它有一个常见的操作，用户从一个名为Sign的数据库表中提取大约300,000行，其中有3列。表列的说明如下：id(int-10), sign(varchar-16), status (int-10)

该表有大约3亿个条目。当用户获取某些条目时，这些行的状态列将更改为用户的id。请注意，用户总是一次获取大约300,000个条目。

我已经将innodb_buffer_pool_size和innodb_log_file_size分别增加到2 2GB和1 2GB。系统具有3.75 of的RAM。

这是代码-

$collection = Sign::select('sign')
                    ->where('status', 0)
                    ->where(DB::raw('CHAR_LENGTH(sign)'), '=', 7)
                    ->take(300000);

//write the the signs in $collection in a file here

$collection->update(['status' => $user->id]);

在我的例子中，表数据可以在不到1秒的时间内轻松获取。update语句以前大约需要100-200秒，但最近我将我的操作系统从Ubuntu 14升级到了16，在这个update语句之后大约需要500-600秒。有什么方法可以让这个过程更快吗？我应该增加内存吗？

Answer 1

与select和update不同，您只需像laravel eloquent那样直接点击update查询，

$collection = Sign::where('status', 0)
                ->whereRaw('CHAR_LENGTH(sign) = 7')
                ->update(['status' => $user->id]);

在任何数据库中，select操作总是很快的，因为它只涉及表的扫描，你可能会得到更多的细节，例如EXPLAIN SELECT * FROM sign;

Answer 2

为了克服不使用索引的CHAR_LENGTH(sign)速度慢的问题，generated columns提供了一种解决方案。

在这里，我们创建一个作为列的符号长度计算的sign_length：

ALTER TABLE sign ADD sign_length INT UNSIGNED AS (CHAR_LENGTH(sign))
, ADD INDEX status_sign_length(status,sign_length)

然后使用：

$collection = Sign::where('status', 0)
                ->where('sign_length', 7)
                ->update(['status' => $user->id])
                ->take(300000);

注意: larvel不是我的强项，欢迎更正。

Answer 3

对于只有3.75 of内存的buffer_pool来说，2G的内存太高了。系统是否在换用？如果是，请降低buffer_pool或增加内存。交换对MySQL来说太可怕了。

由于新的操作系统可能会占用更多的RAM，上面的陈述可以解释速度减慢的原因。

请提供由$collection->update(['status' => $user->id]);生成的SQL；它将是什么并不明显。据我所知，$collection保留了所有行的300K ids的列表，并为UPDATE创建了一个IN子句。

与SELECTing它们相比，UPDATEing行的开销要大得多。前者必须保留行的副本，以防发生崩溃而需要ROLLBACK。

什么版本的MySQL？针对UPDATE的优化器最近发生了一些变化。

如果SELECT和UPDATE之间有问题，您可能需要SELECT ... FOR UPDATE --否则另一个连接可能会占用相同的行，并弄乱数据！