将数据缓慢插入mysql数据库

Question

我正在尝试为正在构建的应用程序制作csv上传页面。它需要能够在几秒钟内上传数千行数据，每行包括名字、姓氏和电话号码。数据正在上传到运行ubuntu服务器的vm。当我运行脚本上传数据时，上传1500行几乎需要2分钟。该脚本使用PDO，我还在python中制作了一个测试脚本，看看这是否是php问题，python脚本也同样缓慢。我过去制作过csv上传脚本，这些脚本与几秒钟内上传数千行的脚本完全相同。我们已经将问题缩小到脚本，因为我们已经在离我们更近的其他vm上测试了它，但问题仍然存在。脚本或PDO是否存在明显的问题，可能会减慢速度？下面是脚本的代码。

<?php
$servername =[Redacted];
$username = [Redacted];
$password = [Redacted];
try {
$conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
echo print_r($_FILES);
$fileTmpPath = $_FILES['fileToUpload']['tmp_name'];
$fileName = $_FILES['fileToUpload']['name'];
$fileSize = $_FILES['fileToUpload']['size'];
$fileType = $_FILES['fileToUpload']['type'];
$CSVfp = fopen($fileTmpPath, "r");
$final_array = [];
while (($data = fgetcsv($CSVfp)) !== false) {
$recived = [];
foreach ($data as $i) {
array_push($recived, $i );
}
array_push($final_array, $recived);
}
echo print_r($final_array);
fclose($CSVfp);
$non_compliant_rows = [];
foreach ($final_array as $key => $row){
$fname = preg_replace('/[^A-Za-z0-9]/', "", $row[0]);
$lname = preg_replace('/[^A-Za-z0-9]/', "", $row[1]);
$mobileNumber = preg_replace( '/[^0-9]/i', '', $row[2]);
$sanatized_row = array($fname, $lname, $mobileNumber);
$recived[$key] = $sanatized_row;
if (strlen($mobileNumber) > 10 or strlen($mobileNumber) < 9){
array_push($non_compliant_rows, $final_array[$key]);
unset($final_array[$key]);
}
}
$final_array = array_values($final_array);
echo print_r($final_array);
foreach($final_array as $item){
try{
$stmt = $conn->prepare("INSERT INTO bulk_sms_list(fname, lname, pn, message, send) VALUES (?, ?, ?, 'EMPTY', 1) ;");
$stmt->execute($item);
}catch(PDOException $e){
echo $e;
}
}
echo "done";

电话号码列有一个唯一的约束，以防止我们有重复的电话号码。我们已经尝试过使用批量插入，但如果一行不符合约束，那么所有插入都会失败。

下面是表格的模式：

+---------+------+------+-----+---------+----------------+
| Field   | Type | Null | Key | Default | Extra          |
+---------+------+------+-----+---------+----------------+
| id      | int  | NO   | PRI | NULL    | auto_increment |
| fname   | text | NO   |     | NULL    |                |
| lname   | text | NO   |     | NULL    |                |
| pn      | text | NO   | UNI | NULL    |                |
| message | text | YES  |     | NULL    |                |
| send    | int  | NO   |     | 1       |                |
+---------+------+------+-----+---------+----------------

编辑：应@aynber的要求，我已对脚本的清理部分进行了计时。清理时间为0.24208784103394秒。执行sql部分所花费的时间为108.2597219944秒

Answer 1

最快的解决方案应该是使用LOAD DATA LOCAL INFILE。由于您在评论中回答了重复的电话号码会导致跳过一行，因此您可以使用IGNORE选项。

直接从文件中加载，而不是使用PHP进行处理。您可以在LOADDATA语句中执行一些转换。

例如：

LOAD DATA INFILE ? IGNORE INTO TABLE bulk_sms_list
FIELDS TERMINATED BY ','
(@fname, @lname, @mobile)
SET fname = REGEXP_REPLACE(@fname, '[^A-Za-z0-9]', ''),
lname = REGEXP_REPLACE(@lname, '[^A-Za-z0-9]', ''),
pn = IF(LENGTH(@mobile) BETWEEN 9 AND 10, @mobile, NULL),
message = 'EMPTY', 
send = 1;

然后在导入之后进行一些清理，以清除任何带有无效电话号码的行：

DELETE FROM bulk_sms_list WHERE pn IS NULL;

读取https://dev.mysql.com/doc/refman/8.0/en/load-data.html了解更多信息。