php - 如何使用 mysqli 在事务中仅创建部分查询？

Question

我正在尝试创建一个脚本来将大约 10m 记录导入到 mysql 数据库。

当我使用单个查询进行循环时，导入 2000 条记录需要 20 分钟。

所以我尝试通过交易来做到这一点。问题是，在我的循环中，有一些选择查询需要立即触发才能获取一些值来创建插入。最后两个查询(插入和更新)可能位于事务中。

类似这样的事情:

foreach($record as $rec) {

   //select sth
   //do sth with result

   //second select sth 
   //do sth with second result

   //prepare values from above results and $rec 

   // below part I'd like to do with transaction

   //insert with new record    
   //update table
}

我知道这有点困惑而且不准确，但是这个函数更复杂，所以我决定只放一个“草稿”，我只需要建议，而不是完整的代码。

问候

Answer 1

事务适用于需要被视为完全成功或完全失败的单个组的多个语句。听起来您的问题与性能比交易有更多关系。除非您没有包含一些涉及“所有语句都必须同时成功”的语句组的信息，否则事务只是一种干扰。

有几种方法可以解决您的问题，具体取决于您的帖子中并非立即显而易见的一些事情。-如果 10M 记录的数据源是同一数据库中的一个表，您将用新记录填充该表(通过循环末尾的插入和更新)，那么您可能能够通过单个数据库查询。 SQL 非常具有表现力，通过连接和一些内置函数(SUBSTR()、UPPER()、REVERSE()、CASE...END 等)，您可能能够做您想做的一切。这需要阅读 SQL 并尝试根据您可以执行的集合操作重新构建您的目标。-如果您要插入来自数据库外部的记录(例如来自文件)，那么我会像这样组织您的代码

//select sth
//do sth with result

//second select sth 
//do sth with second result

//prepare values from above results so that $rec info can be added in later

foreach($record as $rec) {
   //construct a big insert statement
}

//insert the new records by running the big insert statement   
//update table

这里的优点是你只用几个查询来访问数据库，而不是每个 $rec 几个查询，所以你的性能会更好(因为数据库调用有开销)。对于 10M 行，您可能需要将上述内容分成几个 block ，因为单个插入的大小是有限制的(请参阅 max_allowed_pa​​cket)。我建议通过在上面添加另一个循环来将 10M 分成 5K 或 10K block ，将 block 与 10M 分开。

如果您添加有关数据源的详细信息、您想要对数据进行哪些转换、数据的用途是什么，则可以给出更清晰的答案

   //select sth
   //do sth with result

   //second select sth 
   //do sth with second result

部分是(在稍后如何将信息添加到插入语句的上下文中)，以及代码的准备值部分的作用。