分析CSV文件时超时

Question

我有一个.csv文件，大约5mb(大约45000行(。我需要做的是遍历文件的每一行，并检查每一行中的ID是否已经在数据库中的表中。如果是，我可以从文件中删除该行。

我对最节省内存的方法做了大量的研究，所以我一直在使用一种方法，将不需要删除的行写入临时文件，然后将该文件重命名为原始文件。以下代码：

$file= fopen($filename, 'r');
$temp = fopen($tempFilename, 'w');
while(($row = fgetcsv($file)) != FALSE){
// id is the 7th value in the row
$id = $row[6];

// check table to see if id exists
$sql = "SELECT id FROM table WHERE id = $id";
$result = mysqli_query($conn, $sql);

// if id is in the database, skip to next row
if(mysqli_num_rows($result) > 0){
continue;
}

// else write line to temp file
fputcsv($temp, $row);
}
fclose($file);
fclose($temp);
// overwrite original file
rename($tempFilename, $filename);

问题是，我在执行这段代码时遇到了超时。我能做些什么来提高代码的效率？

Answer 1

每行触发一个数据库查询，即45.000个查询。。。这需要太多时间。

最好在循环之前进行查询，并将现有id读取到查找数组中，然后只在循环中检查此数组。

伪代码：

$st = query('SELECT id FROM table');
while ($row = $st->fetch()) {
$lookup[ $row['id'] ] = $row['id'];
}
// now read CSV
while($row = fgetcsv($h)) {
$id = $row[6];
if (isset($lookup[ $id ])) {
// exist...
continue;
}
// write the non-existing id to different file...
}

编辑：假设内存不足以容纳数据库中的100万个整数。如何才能有效地完成？

将CSV中的ID收集到数组中。编写一个查询，在数据库中查找所有这些id并收集(它可以是CSV中的最大数量(。现在array_diff()文件中的ID和数据库中的ID——这些ID仍然存在于CSV中，但不在数据库中。

伪代码：

$ids_csv = [];
while($row = fgetcsv($h)) {
$id = row[6];
$ids_csv[] = intval($id);
}
$sql = sprintf('SELECT id FROM table WHERE id IN(%s)', implode(',', $ids_csv));
$ids_db = [];
$st = query($sql);
while ($row = $st->fetch()) {
$ids_db[] = $row['id'];
}
$missing_in_db = array_diff($ids_csv, $ids_db);

Answer 2

1. 我会使用LOAD DATA INFILE：https://dev.mysql.com/doc/refman/8.0/en/load-data.html
   您的数据库用户需要在数据库上拥有FILE权限才能使用。将csv文件读取到单独的表中
2. 然后，您可以运行一个查询来删除已经存在的id(从联接中删除…(
3. 并导出保持不变的行

其他选项是使用循环将csv文件插入到一个单独的表中，然后继续执行步骤2。

更新：我对多达200万行的csv文件使用LOAD DATA INFILE(目前(，并对大型查询进行一些批量数据操作，速度非常快，我建议对包含>100k条线路。