How to insert into a database at lightning speed?

Trong quá trình thực hiện dự án cho công ty, một trong những yêu cầu đặt ra là import dữ liệu từ file CSV (chứa dữ liệu từ hệ thống cũ) vào cơ sở dữ liệu MySQL của hệ thống mới.

Do sự thay đổi cấu trúc dữ liệu trong hệ thống mới, việc xử lý dữ liệu trước khi import vào trở thành bước quan trọng.

Tuy nhiên, thách thức đặt ra là kích thước lớn của dữ liệu từ hệ thống cũ, yêu cầu quá trình import phải diễn ra một cách nhanh chóng.

Sau quá trình nghiên cứu và thảo luận với đồng đội trong công ty, mình đã đề xuất một phương pháp xử lý hiệu quả như sau:

Trong bài viết này, mình sử dụng Laravel Framework phiên bản 10.

Tạo Command trong Laravel

php artisan make:command ImportCSV

Đọc và Xử lý Dữ liệu từ File CSV

 Đọc dữ liệu từ file CSV bằng phương pháp Chunk.

Sau đó sẽ xử lý dữ liệu cũ để tương thích với hệ thống mới và ghi kết quả xử lý thành CSV mới.

public function handle()
{
    $filename     = storage_path('transfers/data.csv');
    $filetransfer = storage_path('transfers/data-transfer.csv');
    $chunk_size   = 10000;
    
    if (($handle = fopen($filename, "r")) !== FALSE) {
        $header    = fgetcsv($handle);
        $now       = now();
        $row_count = 0;
        $chunk     = [];
        $transfer  = fopen($filetransfer, 'w');
        
        while (($row = fgetcsv($handle)) !== FALSE) {
            $row_count++;
            $chunk[] = $row;
            
            if ($row_count >= $chunk_size) {
                $this->_writeCSV($header, $chunk, $now, $transfer);
                $chunk     = [];
                $row_count = 0;
            }
        }
        
        if (!empty($chunk)) {
            $this->_writeCSV($header, $chunk, $now, $transfer);
        }
        
        fclose($handle);
        fclose($transfer);
        $this->_loadDataLocalInFile($filetransfer);
    } else {
        echo "Cannot open file $filename.";
    }
}

private function _writeCSV($header, $chunk, $now, $transfer)
{
    foreach ($chunk as $row) {
        fputcsv($transfer, [
            # Xử lý dữ liệu cũ và ghi thành file CSV
        ]);
    }
}

Import Dữ liệu vào Cơ sở Dữ liệu mới

Mình sẽ sử dụng LOAD DATA LOCAL INFILE đây một câu lệnh trong MySQL được sử dụng để nạp dữ liệu từ một file local vào một bảng trong cơ sở dữ liệu.

Đây là một cách hiệu quả để import dữ liệu từ các tệp CSV hoặc văn bản vào MySQL mà không cần phải truyền qua ứng dụng trung gian.

private function _loadDataLocalInFile($file)
{
    $table = 'name_table';
    $query = "
        LOAD DATA LOCAL INFILE '{$file}'
        INTO TABLE {$table}
        FIELDS TERMINATED BY ','
        ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        (table_columns)
    ";
    DB::connection()->getPdo()->exec($query);
}

Cấu hình Laravel cho LOAD DATA LOCAL INFILE

Để thực hiện được lệnh LOAD DATA LOCAL INFILE trong Laravel.

Cần phải thêm option MYSQL_ATTR_LOCAL_INFILE vào MySQL trong config/database.php

'mysql' => [
    ...
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        ...
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    ]) : [],
],

Hy vọng rằng, với cách tiếp cận này, quá trình import dữ liệu sẽ diễn ra một cách hiệu quả và nhanh chóng, đồng thời đảm bảo tính chính xác và sự đồng bộ giữa hệ thống cũ và mới trong dự án của bạn.