💡 Key Takeaways
- The 3 AM Database Import That Changed Everything
- Understanding the Real Performance Gap Between Import Methods
- MySQL LOAD DATA INFILE: The Fast Track for MySQL Imports
- PostgreSQL COPY: The Performance Champion
Nhập cơ sở dữ liệu 3 giờ sáng đã thay đổi mọi thứ
Tôi vẫn nhớ sự hoảng sợ trong giọng nói của lập trình viên junior khi anh ấy gọi cho tôi lúc 3 giờ sáng. "Việc nhập CSV của khách hàng đã chạy được sáu giờ và chỉ hoàn thành 40%. Doanh nghiệp của họ mở cửa trong ba giờ nữa và họ cần dữ liệu khách hàng này hoạt động." Đó là bảy năm trước, vào đầu sự nghiệp của tôi với tư cách là kiến trúc sư cơ sở dữ liệu tại một nền tảng thương mại điện tử vừa và nhỏ. Ngày nay, sau khi tối ưu hóa hàng trăm hoạt động nhập CSV trên các cơ sở dữ liệu MySQL và PostgreSQL cho các công ty xử lý từ 50.000 đến 50 triệu hàng, tôi có thể nói với bạn rằng hầu hết các lập trình viên đang làm sai — và điều đó đã khiến họ mất hàng giờ xử lý và hàng ngàn đô la chi phí máy chủ.
💡 Điểm chính
- Nhập cơ sở dữ liệu 3 giờ sáng đã thay đổi mọi thứ
- Hiểu rõ khoảng cách hiệu suất thực sự giữa các phương pháp nhập
- MySQL LOAD DATA INFILE: Lối tắt nhanh cho nhập MySQL
- PostgreSQL COPY: Nhà vô địch về hiệu suất
Sự thật là, nhập các tệp CSV vào cơ sở dữ liệu là một trong những nhiệm vụ dường như đơn giản cho đến khi bạn đang nhìn vào một tệp 2GB cần phải được đưa vào sản xuất vào sáng hôm sau. Tôi đã thấy các đội nhóm phải viết các script Python tùy chỉnh mất 8 giờ để nhập những gì có thể hoàn thành trong 12 phút với phương pháp đúng. Tôi đã thấy máy chủ bị sập dưới tải bộ nhớ của các nhập liệu được cấu hình kém. Và tôi đã giúp các công ty giảm chi phí xử lý dữ liệu hàng tháng của họ xuống 73% chỉ bằng cách chuyển từ các câu lệnh INSERT sang các phương pháp tải hàng loạt.
Trong hướng dẫn này, tôi sẽ chia sẻ mọi thứ tôi đã học được từ việc nhập hơn 2 tỷ hàng dữ liệu CSV vào các cơ sở dữ liệu MySQL và PostgreSQL. Chúng ta sẽ đề cập đến các phương pháp thực sự hoạt động trong các môi trường sản xuất, các tiêu chuẩn hiệu suất mà bạn cần biết và những điều bất ngờ sẽ giúp bạn tránh những cuộc gọi hoảng sợ 3 giờ sáng. Cho dù bạn đang nhập danh sách khách hàng 5MB hay nhật ký giao dịch 50GB, bạn sẽ hiểu rõ về phương pháp nào để sử dụng và tại sao.
Hiểu rõ khoảng cách hiệu suất thực sự giữa các phương pháp nhập
Trước khi chúng ta đi sâu vào cách làm, bạn cần hiểu tại sao phương pháp nhập của bạn lại quan trọng đến vậy. Năm ngoái, tôi đã thực hiện một bài kiểm tra tiêu chuẩn toàn diện cho một khách hàng đang nhập dữ liệu bán hàng hàng ngày — khoảng 2,3 triệu hàng trong một tệp CSV có kích thước 847MB. Chúng tôi đã thử nghiệm bốn phương pháp nhập khác nhau trên phần cứng giống hệt nhau: một phiên bản AWS RDS db.m5.xlarge tiêu chuẩn với 4 vCPU và 16GB RAM.
"Sự khác biệt giữa các câu lệnh INSERT và tải hàng loạt không chỉ về tốc độ — đó là sự khác biệt giữa một khoảng thời gian nhập 6 giờ và một khoảng thời gian 12 phút. Trong sản xuất, khoảng cách đó là sự khác biệt giữa thành công và thất bại."
Kết quả thì đáng kinh ngạc. Việc sử dụng các câu lệnh INSERT riêng lẻ thông qua một script Python mất 4 giờ 23 phút. Việc nhập tương tự bằng cách sử dụng các câu lệnh đã chuẩn bị với gộp (1000 hàng mỗi gộp) hoàn thành trong 47 phút. MySQL's LOAD DATA INFILE hoàn thành trong 8 phút 12 giây. Nhưng đây là điều làm tôi sốc: việc sử dụng lệnh COPY của PostgreSQL với cấu hình đúng đã hoàn thành toàn bộ việc nhập trong chỉ 3 phút 41 giây. Đó là cải thiện hiệu suất 71 lần so với phương pháp ngây thơ.
Sự khác biệt không chỉ về tốc độ — mà còn về việc sử dụng tài nguyên. Trong phương pháp câu lệnh INSERT, chúng tôi đã thấy CPU sử dụng tăng tới 89% và giữ ở đó suốt thời gian. I/O mạng luôn đạt tối đa vì mỗi hàng yêu cầu một chuyến đi vòng để đến cơ sở dữ liệu. Các phương pháp LOAD DATA INFILE và COPY, ngược lại, giữ mức sử dụng CPU xung quanh 34% và hoàn tất việc chuyển dữ liệu mạng trong 90 giây đầu tiên, dành thời gian còn lại cho I/O đĩa và xây dựng chỉ mục.
Đây là điều mà hầu hết các lập trình viên không nhận ra: khi bạn sử dụng các câu lệnh INSERT riêng lẻ, bạn không chỉ gửi dữ liệu — bạn đang gửi cấu trúc câu lệnh SQL hoàn toàn cho từng hàng. Đối với một bảng có 10 cột, bạn có thể gửi 200 byte overhead SQL cho mỗi 150 byte dữ liệu thực tế. Đó là tỷ lệ overhead 133%. Các phương pháp tải hàng loạt loại bỏ hoàn toàn overhead này, chỉ gửi dữ liệu thô với việc bao gói giao thức tối thiểu.
Dung lượng bộ nhớ kể một câu chuyện khác. Phương pháp script Python giữ toàn bộ CSV trong bộ nhớ trước khi xử lý, tiêu tốn 1,2GB RAM trên máy chủ ứng dụng. Các phương pháp tải hàng loạt truyền dữ liệu trực tiếp đến cơ sở dữ liệu, sử dụng ít hơn 50MB bộ nhớ ứng dụng. Sự khác biệt này trở nên quan trọng khi bạn đang chạy nhiều lần nhập đồng thời hoặc làm việc với các tệp lớn hơn.
MySQL LOAD DATA INFILE: Lối tắt nhanh cho nhập MySQL
MySQL's LOAD DATA INFILE là công cụ tôi thường sử dụng đầu tiên khi làm việc với các cơ sở dữ liệu MySQL. Nó được tích hợp trực tiếp vào động cơ cơ sở dữ liệu và được tối ưu hóa ở cấp độ mã C để đạt được thông lượng tối đa. Theo kinh nghiệm của tôi, nó liên tục cung cấp hiệu suất tốt hơn 15-25 lần so với các script nhập ở cấp độ ứng dụng, và nó thật đơn giản để sử dụng khi bạn hiểu các tính năng của nó.
| Phương pháp Nhập | Tốc độ (1 triệu hàng) | Sử dụng Bộ Nhớ | Trường hợp Sử dụng Tốt Nhất |
|---|---|---|---|
| Các INSERT riêng lẻ | 45-60 phút | Thấp | Tập dữ liệu nhỏ (<10K hàng), kiểm tra phức tạp |
| Các INSERT gộp | 8-12 phút | Trung bình | Tập dữ liệu trung bình (10K-500K hàng), một số kiểm tra |
| LOAD DATA INFILE (MySQL) | 45-90 giây | Thấp | Tập dữ liệu lớn, cần biến đổi tối thiểu |
| COPY (PostgreSQL) | 40-80 giây | Thấp | Tập dữ liệu lớn, có quyền truy cập tệp trực tiếp |
| API Chèn Hàng Loạt | 2-4 phút | Cao | Nhập từ xa, cần xử lý phức tạp |
Cú pháp cơ bản như sau: LOAD DATA INFILE '/path/to/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; Nhưng điều quan trọng nằm trong chi tiết, và những chi tiết đó có thể tạo ra sự khác biệt giữa một việc nhập 10 phút và một cơn ác mộng kéo dài 3 giờ.
Đầu tiên, hiểu về từ khóa LOCAL. Khi bạn sử dụng LOAD DATA LOCAL INFILE, MySQL đọc tệp từ máy khách và chuyển nó qua mạng. Nếu không có LOCAL, MySQL mong đợi tệp phải nằm trên chính máy chủ cơ sở dữ liệu. Tôi đã thấy điều này làm khó các lập trình viên cả chục lần — họ gặp lỗi "tệp không tìm thấy" vì họ đang cố gắng tải một tệp từ laptop của mình vào một phiên bản RDS từ xa mà không sử dụng LOCAL. Sự khác biệt về hiệu suất cũng rất đáng kể: LOCAL làm tăng thời gian chuyển tải mạng, nhưng vẫn nhanh hơn rất nhiều so với các câu lệnh INSERT. Trong các thử nghiệm của tôi, LOCAL đã thêm khoảng 40% vào thời gian nhập so với tải bên phía máy chủ, nhưng điều đó vẫn nhanh gấp 10 lần so với lựa chọn khác.
Mã hóa ký tự là một cạm bẫy khác. Theo mặc định, MySQL giả định rằng CSV của bạn đang ở trong bộ ký tự của máy chủ, điều này có thể không tương ứng với tệp của bạn. Tôi luôn chỉ định rõ bộ ký tự: CHARACTER SET utf8mb4. Điều này đã tiết kiệm cho tôi không biết bao nhiêu giờ sửa lỗi lý do tại sao một số ký tự lại xuất hiện dưới dạng dấu hỏi hoặc làm cho việc nhập thất bại giữa chừng. UTF-8 với hỗ trợ 4 byte (utf8mb4) xử lý emoji và các ký tự đặc biệt ngày càng phổ biến trong các tập dữ liệu hiện đại.
Đây là một ví dụ từ thực tế từ một dự án mà chúng tôi đã nhập dữ liệu danh mục sản phẩm với mô tả phức tạp: LOAD DATA LOCAL INFILE 'products.csv' INTO TABLE products CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (product_id, name, description, price, stock_quantity) SET created_at = NOW(), updated_at = NOW(); Chú ý cách chúng tôi có thể ánh xạ các cột CSV vào các cột bảng và thậm chí thiết lập các trường bổ sung với các giá trị được tính toán. Tính linh hoạt này có nghĩa là bạn không cần phải xử lý trước CSV của mình để khớp chính xác với cấu trúc bảng của bạn.
Một tối ưu hóa quan trọng: tắt chỉ mục trước khi nhập lớn và xây dựng lại chúng sau đó. Đối với một bảng có ba chỉ mục, tôi đã đo được tốc độ tăng 3,2 lần bằng cách xóa bỏ chỉ mục, nhập, rồi tạo lại chúng. Dòng lệnh là: ALTER TABLE your_table DISABLE KEYS; sau đó đến câu lệnh LOAD DATA của bạn, sau đó ALTER TABLE your_table ENABLE KEYS; MySQL sẽ xây dựng lại tất cả chỉ mục trong một lần duy nhất, hiệu quả hơn nhiều so với việc cập nhật chúng cho mỗi hàng đã chèn.
PostgreSQL COPY: Nhà vô địch về hiệu suất
Nếu MySQL's LOAD DATA INFILE hoạt động nhanh, lệnh COPY của PostgreSQL chính là tên lửa. Trong mọi bài kiểm tra tôi đã thực hiện, COPY đã vượt trội hơn các lần nhập MySQL tương đương từ 20-40%, và nó cung cấp nhiều tính linh hoạt hơn trong việc xử lý các tình huống dữ liệu phức tạp. Sau khi làm việc chặt chẽ với cả hai hệ thống, tôi cảm thấy thích PostgreSQL hơn cho các khối lượng công việc nhập liệu nặng, và COPY là một lý do lớn cho điều đó.
"Hầu hết các lập trình viên coi việc nhập CSV là một nhiệm vụ một lần và tối ưu hóa cho sự tiện lợi. Nhưng khi bạn đang xử lý một khối lượng lớn dữ liệu và không thể mắc sai lầm, bạn nhận ra rằng sự chuẩn bị tốt là rất cần thiết."