Posted inMySQL

Cách import dữ liệu lớn vào MySQL nhanh chóng với LOAD DATA INFILE: Tối ưu tốc độ xử lý hàng triệu bản ghi

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Vấn đề: Tại sao INSERT hàng triệu bản ghi lại chậm đến phát điên?

Bạn đã bao giờ rơi vào cảnh ngồi nhìn màn hình terminal chạy lệnh INSERT hàng giờ đồng hồ mà vẫn chưa xong? Mình từng nhận một task migrate khoảng 50 triệu dòng log từ hệ thống cũ sang MySQL để làm báo cáo. Ban đầu, mình viết một script Python đơn giản, đọc file CSV rồi thực hiện lệnh INSERT INTO... từng dòng một. Kết quả là sau 2 tiếng đồng hồ, nó mới bò được khoảng 3% dữ liệu. Sếp chuẩn bị họp lấy số liệu, còn mình thì toát mồ hôi hột vì tốc độ sên bò này.

Tại sao lại chậm đến vậy? Mỗi câu lệnh INSERT đơn lẻ đều kéo theo một loạt chi phí ngầm. MySQL phải phân tích cú pháp (parse) câu lệnh, kiểm tra quyền, khóa bảng, ghi log transaction (redo log), và quan trọng nhất là cập nhật các index sau mỗi dòng. Nếu bạn có 1 triệu dòng, MySQL phải làm những việc này 1 triệu lần. Chưa kể độ trễ mạng giữa ứng dụng và database server cũng góp phần kéo dài thời gian.

Để giải quyết bài toán này, MySQL cung cấp một vũ khí hạng nặng: LOAD DATA INFILE. Thay vì đi từng bước nhỏ, nó đẩy dữ liệu theo khối lớn trực tiếp vào engine lưu trữ, bỏ qua hầu hết các bước trung gian không cần thiết.

LOAD DATA INFILE là gì và tại sao nó nhanh?

LOAD DATA INFILE là câu lệnh SQL đọc dữ liệu từ file văn bản (thường là CSV hoặc TXT) và nạp thẳng vào bảng MySQL. Theo tài liệu chính thức và kinh nghiệm thực tế của mình, phương pháp này nhanh hơn INSERT thông thường từ 10 đến 20 lần.

Ba lý do chính nó nhanh hơn nhiều lần:

  • Đọc dữ liệu theo block lớn, giảm thiểu số lượng system call.
  • Bỏ qua bước parse SQL cho từng dòng dữ liệu.
  • Có thể tối ưu hóa việc xây dựng index sau khi dữ liệu đã được nạp xong, thay vì cập nhật liên tục từng dòng.

Cấu hình trước khi bắt đầu (Vượt qua rào cản bảo mật)

Chưa vội chạy ngay. MySQL mặc định chặn tính năng này để tránh SQL injection đọc file hệ thống — bước cấu hình sau đây cần làm trước.

1. Kiểm tra biến secure_file_priv

Biến này quy định thư mục nào MySQL được phép đọc file. Bạn kiểm tra bằng lệnh:

SHOW VARIABLES LIKE 'secure_file_priv';
  • Nếu kết quả là một đường dẫn (ví dụ: /var/lib/mysql-files/), bạn phải copy file CSV vào đúng thư mục đó.
  • Nếu kết quả là NULL, tính năng này đang bị tắt hoàn toàn.
  • Nếu kết quả là rỗng (empty), bạn có thể đọc file ở bất cứ đâu.

Để thay đổi, bạn cần sửa file my.cnf hoặc my.ini:

[mysqld]
secure_file_priv = ""

Sau đó khởi động lại MySQL server.

2. Cho phép LOAD DATA LOCAL

Nếu file nằm trên máy cá nhân của bạn chứ không phải trên server chứa MySQL, bạn cần dùng từ khóa LOCAL. Cả server và client đều phải cho phép điều này:

SET GLOBAL local_infile = 1;

Thực hành: Cú pháp và ví dụ thực tế

Giả sử mình có một file customers.csv với cấu trúc: id, name, email, created_at. Nội dung file trông như sau:

1,"Nguyen Van A","[email protected]","2023-10-01 10:00:00"
2,"Tran Thi B","[email protected]","2023-10-01 10:05:00"

Câu lệnh SQL để import sẽ như thế này:

LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

Giải thích các tham số quan trọng:

  • FIELDS TERMINATED BY: Dấu phân cách giữa các cột (thường là dấu phẩy hoặc tab \t).
  • ENCLOSED BY: Ký tự bao quanh chuỗi — dùng dấu ngoặc kép để xử lý trường hợp dữ liệu có chứa dấu phẩy bên trong.
  • LINES TERMINATED BY: Ký tự kết thúc dòng. Linux dùng \n, Windows dùng \r\n — hay nhầm cái này lắm.
  • IGNORE 1 LINES: Bỏ qua dòng đầu tiên nếu file có header.

Bí kíp tối ưu tốc độ “bàn thờ” (Kinh nghiệm thực chiến)

Nếu bạn chỉ chạy lệnh trên, tốc độ đã khá tốt rồi. Nhưng với hàng chục triệu bản ghi, mình thường áp dụng thêm các kỹ thuật dưới đây để ép xung tốc độ lên mức tối đa.

1. Tắt kiểm tra ràng buộc tạm thời

Mỗi khi chèn một dòng, MySQL phải kiểm tra xem giá trị đó có vi phạm khóa ngoại (Foreign Key) hay tính duy nhất (Unique Index) không. Khi import dữ liệu lớn đã được làm sạch, việc này là thừa thãi.

SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

Sau khi import xong, nhớ bật lại và thực hiện COMMIT.

2. Vô hiệu hóa Index (Chỉ dành cho bảng MyISAM hoặc bảng mới)

Đối với bảng MyISAM, bạn có thể dùng lệnh ALTER TABLE table_name DISABLE KEYS;. Với InnoDB, cách tốt nhất là sắp xếp file CSV theo thứ tự của Primary Key trước khi import. Điều này giúp MySQL ghi dữ liệu vào đĩa theo trình tự, tránh việc phải nhảy con trỏ đĩa liên tục (disk thrashing).

3. Tăng kích thước Buffer

Trước khi chạy LOAD DATA, hãy tăng bộ nhớ đệm để MySQL có không gian xử lý dữ liệu lớn:

SET GLOBAL innodb_buffer_pool_size = 4G; -- Tùy vào RAM của bạn
SET GLOBAL innodb_log_file_size = 1G;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Lưu ý: innodb_flush_log_at_trx_commit = 2 bỏ qua việc sync log xuống đĩa sau mỗi transaction, nên tốc độ tăng rõ rệt. Đánh đổi là nếu server mất điện đúng lúc đang import, bạn có thể mất vài giây dữ liệu cuối — hoàn toàn chấp nhận được khi đây là batch import một lần.

Xử lý lỗi thường gặp

Ba lỗi mình gặp nhiều nhất khi chạy lệnh này — cùng cách xử lý nhanh:

  • Lỗi sai định dạng ngày tháng: MySQL rất khó tính với định dạng YYYY-MM-DD. Nếu file CSV của bạn để định dạng DD/MM/YYYY, hãy dùng biến tạm để convert: 
    LOAD DATA INFILE 'data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
(id, @temp_date, amount)
SET order_date = STR_TO_DATE(@temp_date, '%d/%m/%Y');
  • Lỗi Data too long: Thường do sai ký tự kết thúc dòng — file tạo từ Windows mang sang Linux chạy là dính ngay. Kiểm tra kỹ \r\n so với \n.
  • Lỗi mất kết nối: File quá lớn khiến connection bị timeout. Chia nhỏ thành các đoạn khoảng 1–2GB, hoặc tăng wait_timeout và max_allowed_packet trong cấu hình MySQL.

Kết luận

Quay lại câu chuyện đầu bài: sau khi áp dụng LOAD DATA INFILE kết hợp tắt foreign key check và sắp xếp file theo Primary Key, 50 triệu dòng đó chạy xong trong vòng chưa đầy 45 phút. Tiết kiệm được hơn 20 tiếng so với script INSERT ban đầu, và sếp có số liệu đúng giờ họp.

Trước khi thả 10 triệu dòng vào, hãy test với 1.000 dòng trước — kiểm tra TERMINATED BY và mapping cột cho khớp hết rồi mới chạy toàn bộ. Và backup trước khi làm là hiển nhiên rồi: mất 10 phút backup còn hơn mất cả ngày recovery. Đừng để chạy xong 10 triệu dòng mới phát hiện dữ liệu lệch cột, dọn dẹp mệt kinh khủng!

Share:
Tags: