我试图更新数据库中的100.000行，下面的代码应该可以做到，但我总是收到一个错误：
错误：命令不同步；你现在不能运行这个命令
因为这是一个更新，我不需要结果，只想去掉它们。使用$count变量是为了让我的数据库得到大块的更新，而不是一个大的更新。（由于数据库的某些限制，一个大的更新不起作用）。
我尝试了很多不同的东西，比如mysqli_free_result等等……都没用。
global $mysqliObject;
$count = 0;
$statement = "";
foreach ($songsArray as $song) {
$id = $song->getId();
$treepath = $song->getTreepath();
$statement = $statement."UPDATE songs SET treepath='".$treepath."' WHERE id=".$id."; ";
$count++;
if ($count > 10000){
$result = mysqli_multi_query($mysqliObject, $statement);
if(!$result) {
die('<br/><br/>Error1: ' . mysqli_error($mysqliObject));
}
$count = 0;
$statement = "";
}
}
正如DaveRandom和StevenVI所建议的那样，使用准备好的查询将减少mysqld进程中的CPU负载。然而，在这种情况下，我怀疑使用准备好的查询是否会对运行时产生实质性影响。您面临的挑战是，您正试图更新songs表中的100K行，这将涉及到物理磁盘子系统上的大量物理I/O。正是这些物理延迟（比如每个PIO约10毫秒）将主导运行时间。每一行中包含的内容、表中使用的索引数量（尤其是那些涉及树路径的索引）等因素都会融入到这种混合中。
准备像这样的简单语句的实际CPU成本
UPDATE songs SET treepath="some treepath" WHERE id=12345;
将在整个物理I/O延迟中丢失，其相对大小在很大程度上取决于存储数据的物理子系统的性质：单个SATA磁盘；SSD；一些具有大型缓存和SSD支持的NAS。。。
在这里，您需要重新思考您的总体策略，特别是如果您同时使用songs表作为通过web前端进行交互式请求的资源。更新100K行将需要一些时间——如果您按存储顺序更新100K中的100K行，则需要更少的时间，因为这将更符合MYD组织，并且通过写入缓存将更好；如果你在1M行中以随机顺序更新100K行，那么PIO的数量会多得多。
当您这样做时，D/B的整体性能将严重下降。
-
您是想最大限度地减少对数据库并行使用的影响，还是只是想将其作为离线其他服务的专用批处理操作？
-
你的目标是尽量减少总耗时，还是在受到一些总体影响限制的情况下保持合理的短时间，甚至只是在不死亡的情况下完成。
我建议您有两种明智的方法：（I）将D/B离线到其他服务作为一种适当的批处理活动来执行。在这种情况下，您可能希望取出表上的锁，并使用ALTER table将更新括起来。。。禁用/启用按键。（ii）将其作为具有小得多的更新集和每组之间的延迟的涓流更新来进行，以允许D/B刷新到磁盘。
不管怎样，我都会减少批量。multi_query本质上优化了调用进程外mysqld所涉及的RPC overheads。一批10个说削减了90%。在这之后，您会得到递减的回报——尤其是说更新将是物理I/O密集型的。
使用准备好的语句尝试此代码：
// Create a prepared statement
$query = "
UPDATE `songs`
SET `treepath` = ?
WHERE `id` = ?
";
$stmt = $GLOBALS['mysqliObject']->prepare($query); // Global variables = bad
// Loop over the array
foreach ($songsArray as $key => $song) {
// Get data about this song
$id = $song->getId();
$treepath = $song->getTreepath();
// Bind data to the statement
$stmt->bind_param('si', $treepath, $id);
// Execute the statement
$stmt->execute();
// Check for errors
if ($stmt->errno) {
echo '<br/><br/>Error: Key ' . $key . ': ' . $stmt->error;
break;
} else if ($stmt->affected_rows < 1) {
echo '<br/><br/>Warning: No rows affected by object at key ' . $key;
}
// Reset the statment
$stmt->reset();
}
// We're done, close the statement
$stmt->close();
我会这样做：
$link = mysqli_connect('host');
if ( $stmt = mysqli_prepare($link, "UPDATE songs SET treepath=? WHERE id=?") ) {
foreach ($songsArray as $song) {
$id = $song->getId();
$treepath = $song->getTreepath();
mysqli_stmt_bind_param($stmt, 's', $treepath); // Assuming it's a string...
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
}
mysqli_stmt_close($stmt);
}
mysqli_close($link);
或者当然是普通的mysql_query，但包含在事务中。
我找到了另一种方法。。。
由于这不是一个生产服务器，更新100k行的最快方法是删除所有行，然后用新计算的值从头开始插入100k。删除所有内容并插入所有内容而不是更新似乎有点奇怪，但速度要快WAYYY。
之前：小时现在：秒！
我建议在执行多次更新之前锁定表并禁用键。这将避免数据库引擎停止（至少在我的300000行更新的情况下）。
LOCK TABLES `TBL_RAW_DATA` WRITE;
/*!40000 ALTER TABLE `TBL_RAW_DATA` DISABLE KEYS */;
UPDATE TBL_RAW_DATA SET CREATION_DATE = ADDTIME(CREATION_DATE,'01:00:00') WHERE ID_DATA >= 1359711;
/*!40000 ALTER TABLE `TBL_RAW_DATA` ENABLE KEYS */;
UNLOCK TABLES;