Khi bạn cần chèn hàng triệu bản ghi vào cơ sở dữ liệu MySQL, bạn sẽ sớm nhận ra rằng việc gửi từng câu lệnh INSERT
không phải là một giải pháp khả thi
Tài liệu MySQL có một số mẹo tối ưu hóa INSERT đáng để bắt đầu đọc
Tôi sẽ cố gắng tóm tắt ở đây hai kỹ thuật chính để tải dữ liệu vào cơ sở dữ liệu MySQL một cách hiệu quả
TẢI DỮ LIỆU VÀO TẬP TINNếu bạn đang tìm kiếm hiệu suất thô, đây chắc chắn là giải pháp bạn lựa chọn.
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
0 là một câu lệnh dành riêng cho MySQL, được tối ưu hóa cao để chèn trực tiếp dữ liệu vào bảng từ tệp CSV/TSVCó hai cách để sử dụng
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
0. Bạn có thể sao chép tệp dữ liệu vào thư mục dữ liệu của máy chủ [thường là LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
2] và chạyLOAD DATA INFILE '/path/to/products.csv' INTO TABLE products;
Điều này khá cồng kềnh vì nó yêu cầu bạn phải có quyền truy cập vào hệ thống tệp của máy chủ, đặt quyền thích hợp, v.v.
Tin vui là bạn cũng có thể lưu trữ tệp dữ liệu ở phía máy khách và sử dụng từ khóa
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
3LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
Trong trường hợp này, tệp được đọc từ hệ thống tệp của máy khách, được sao chép trong suốt vào thư mục tạm thời của máy chủ và được nhập từ đó. Nói chung, nó gần như nhanh như tải trực tiếp từ hệ thống tệp của máy chủ. Tuy nhiên, bạn cần đảm bảo rằng tùy chọn này được bật trên máy chủ của mình
Có nhiều tùy chọn để
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
0, chủ yếu liên quan đến cách cấu trúc tệp dữ liệu của bạn [dấu phân cách trường, bao vây, v.v. ]. Hãy xem tài liệu để xem tất cảMặc dù
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
0 là tùy chọn hiệu suất tốt nhất của bạn, nhưng nó yêu cầu bạn phải chuẩn bị sẵn dữ liệu của mình dưới dạng tệp văn bản được phân tách bằng dấu phân cách. Nếu bạn không có các tệp như vậy, bạn sẽ cần sử dụng thêm tài nguyên để tạo chúng và có thể sẽ tăng thêm mức độ phức tạp cho ứng dụng của bạn. May mắn thay, có một giải pháp thay thếphần chèn mở rộngMột câu lệnh SQL INSERT
điển hình trông giống như
INSERT INTO user [id, name] VALUES [1, 'Ben'];
Một INSERT
mở rộng nhóm một số bản ghi vào một truy vấn duy nhất
INSERT INTO user [id, name] VALUES [1, 'Ben'], [2, 'Bob'];
Chìa khóa ở đây là tìm số lần chèn tối ưu cho mỗi truy vấn để gửi. Không có con số chung nào phù hợp với tất cả, vì vậy bạn cần đánh giá chuẩn một mẫu dữ liệu của mình để tìm ra giá trị mang lại hiệu suất tối đa hoặc sự đánh đổi tốt nhất về hiệu suất/sử dụng bộ nhớ
Để tận dụng tối đa các phần chèn mở rộng, bạn cũng nên
- sử dụng báo cáo chuẩn bị
- chạy báo cáo trong một giao dịch
Tôi đang chèn 1. 2 triệu hàng, 6 cột thuộc nhiều loại khác nhau, trung bình ~26 byte mỗi hàng. Tôi đã thử nghiệm hai cấu hình phổ biến
- Máy khách và máy chủ trên cùng một máy, giao tiếp qua ổ cắm UNIX
- Máy khách và máy chủ trên các máy riêng biệt, với độ trễ rất thấp [