大规模MySQL更新的最佳方法是什么？

Question

我需要从一个CSV文件每天更新我的MySQL数据库中的股票水平3次。

CSV中有超过27,000个产品需要更新，正如你可以想象的那样，这需要一段时间。

我目前有一个php脚本，它运行以下内容：

select * from products where product_code = "xxxxxxx";
if num_rows > 0
    if new_stock_level = 0
        UPDATE products SET `stock` = 0, `price` = 9.99 where product_code = "xxxxxxx";
    else
        UPDATE products SET `stock` = 50, `price` = 9.99, `stock_date` = now() where product_code = "xxxxxxx";

如果你更新的项目少于50个，而不是27,000个，这是很好的！

更新这个量表最好的方法是什么？

我一直在做一些研究，从我所看到的情况来看，mysqli准备好的语句似乎是我应该去的地方。

在尝试了下面提到的一些内容和我在网上读到的内容后，我得到了以下结果，并进行了250次更新。

从InnoDB更改为MyISAM平均每秒的订阅次数从7次增加到27次，这从一开始就是一个巨大的增长。

使用case 9-10秒准备语句

## Prepare the statment.
$stmt = $mysqli->prepare("UPDATE products SET stock = case ? when 0 then 0 else ? end, price = ?, stock_date = case ? when 0 then stock_date else now() end WHERE product_code = ?");
$stmt->bind_param('dddds', $stock, $stock, $price, $stock, $prod);
$stmt->execute();

未准备的语句9-10秒

$sql = "UPDATE products SET stock = case " . $stock . " when 0 then 0 else " . $stock . " end, price = " . $price . ", stock_date = case " . $stock . " when 0 then stock_date else now() end WHERE product_code = \"" . $prod . "\";\n";
$mysqli->query($sql);

在50年代对语句进行分组，并使用multi_query执行9-10秒

$mysqli->multi_query($sql);

未准备，有两个单独的查询，取决于im是否更新库存日期。8-9秒

if($stock > 0)
{
    $sql = "UPDATE products SET stock = " . $stock . ", price = " . $price . ", stock_date = now() WHERE product_code = \"" . $prod . "\";\n";
}
else
{   
    $sql = "UPDATE products SET stock = " . $stock . ", price = " . $price . " WHERE product_code = \"" . $prod . "\";\n";
}
$mysqli->query($sql);

相同版本的准备版本8-9秒

## Prepare statments
$stmt1 = $mysqli->prepare("UPDATE products SET stock = ?, price = ?, stock_date = now() WHERE product_code = ?;");
$stmt1->bind_param('dds',$stock, $price, $prod);
$stmt2 = $mysqli->prepare("UPDATE products SET stock = ?, price = ? WHERE product_code = ?;");
$stmt2->bind_param('dds', $stock, $price, $prod);

if($stock > 0)
{
    $stmt1->execute();
}
else
{   
    $stmt2->execute();
}

我还尝试在VPS中添加一个额外的处理器，它使查询速度提高了大约4秒。

Answer 1

关于这个有几件事..。

1. you can do this with one sql statement 
UPDATE products 
SET stock = case new_stock_level when 0 then 0 else new_stock_level end, 
    price = 9.99,
    stock_date = case new_stock_level when 0 then stock_date else now() end
WHERE product_code = "xxxxxxx";

2. you might want to try wrapping the statements inside of a transaction:
e.g.
START TRANSACTION
UPDATE products ...;
UPDATE products ...;
... ;
COMMIT TRANSACTION

这两件事应该会加快它的速度。

Answer 2

您可以使用MySQL的CSV storage engine创建一个表来直接访问您的CSV文件。不需要导入它。

然后，您可以使用multi-table UPDATE syntax通过product_code列将CSV表直接连接到products表。然后，您可以基于从CSV表中读取的列来更新products的列。

Answer 3

就我个人而言，我会将更新上传到一个临时表中，在product_code字段上创建一个唯一的键，然后像这样运行更新……

UPDATE tmptable p, products pp 
SET pp.stock = p.stock,
    pp.price = p.price,
    pp.stock_date = if(p.stock == 0, now(), pp.stock_date)
WHERE pp.product_code = p.product_code