Một bài đang được chú ý trên Juejin đo một việc rất quen: chèn 100.000 dòng vào MySQL/MariaDB theo năm cách khác nhau, với dữ liệu và schema giống hệt nhau. Kết quả tác giả công bố:
| Cách ghi | Thời gian | Dòng/giây | So với chậm nhất |
|---|---|---|---|
| Từng INSERT, mỗi câu một transaction | 52,54 s | 1.903 | 1x |
| Từng INSERT, chung một transaction | 16,06 s | 6.228 | 3,3x |
| Batch INSERT, 100 dòng mỗi batch | 1,49 s | 66.951 | 35,2x |
| Batch INSERT, 1000 dòng mỗi batch | 1,42 s | 70.179 | 36,9x |
LOAD DATA LOCAL INFILE | 0,51 s | 197.028 | 103,5x |
Số tuyệt đối sẽ khác trên máy bạn, nên điều đáng giữ lại là tỷ lệ và lý do đằng sau. Đây còn là kết nối local qua Unix socket. Môi trường production có thêm network round-trip nên khoảng cách ở dòng đầu tiên thường còn lớn hơn: thêm 1 ms RTT cho mỗi câu lệnh nghĩa là thêm khoảng 100 giây cho 100.000 câu, gấp đôi toàn bộ thời gian đo được ở trên.
Có thể tách chi phí thành ba lớp.
Lớp 1: chi phí commit
Với autocommit=1 mặc định, mỗi INSERT là một transaction hoàn chỉnh: ghi undo log, redo log, commit rồi fsync. Phần fsync phải đợi đĩa xác nhận nên rất đắt.
Tác giả thử nới innodb_flush_log_at_trx_commit và đo được:
| Giá trị | Dòng/giây | Ý nghĩa |
|---|---|---|
| 1 (mặc định) | 2.380 | fsync mỗi lần commit, an toàn nhất |
| 2 | 4.124 | Ghi vào OS cache, fsync mỗi giây |
| 0 | 6.005 | Ghi và fsync mỗi giây |
Đừng vội đổi cấu hình này để lấy tốc độ. Với giá trị 0, crash của mysqld có thể làm mất khoảng một giây transaction đã báo commit thành công. Với giá trị 2, sự cố tương tự xảy ra khi OS crash hoặc mất điện. Với tiền hoặc đơn hàng, đó thường không phải đánh đổi chấp nhận được. Chỉ gom nhiều dòng vào một transaction đã xử lý phần lớn chi phí này mà không phải đánh đổi độ bền.
Lớp 2: chi phí cố định của mỗi câu SQL
Nhiều người nghĩ "bọc trong transaction là xong". Nhưng nhìn bảng đầu tiên: bỏ được fsync mới đạt 6.228 dòng/giây, trong khi batch INSERT đạt khoảng 70.000. Vẫn còn khoảng cách 11 lần.
Phần đó đến từ chi phí mà mọi câu SQL đều phải trả: round-trip giữa client và server, parse câu lệnh, đi qua optimizer, kiểm tra quyền, tra metadata của bảng. Tất cả đều không liên quan gì đến việc ghi một dòng.
Multi-row INSERT gộp các chi phí này lại:
INSERT INTO orders (uid, city, amount) VALUES
(1, 'Ha Noi', 100),
(2, 'Da Nang', 200),
(3, 'Hue', 150);
Parse một lần, tối ưu một lần, round-trip một lần, rồi ghi nhiều dòng liên tiếp.
Batch bao nhiêu là đủ
Đây là phần thực dụng nhất của bài đo:
| Số dòng mỗi batch | Dòng/giây | Tăng so với từng dòng |
|---|---|---|
| 1 | 6.389 | 1x |
| 10 | 35.970 | 5,6x |
| 100 | 65.270 | 10,2x |
| 500 | 71.591 | 11,2x |
| 1000 | 69.952 | 10,9x |
| 20000 | 70.692 | 11,1x |
Bước nhảy lớn nhất là từ 1 lên 10. Đến 100 đã gần chạm trần, sau 500 thì đi ngang. Vì vậy kích thước trong khoảng 100 đến 1000 là đủ, không cần tinh chỉnh thêm.
Batch quá lớn còn có rủi ro:
- Vượt
max_allowed_packetvà gặp lỗiPacket too large. Tác giả nêu mặc định là 16 MB với MariaDB và 64 MB với MySQL 8. - Transaction lớn làm undo log phình to, giữ lock lâu và làm tăng độ trễ replication.
- Lỗi một dòng có thể khiến cả batch rollback và chi phí retry cao.
Một mẫu dùng mysql2 trên Node.js, cắt theo batch 500 dòng:
const BATCH = 500;
for (let i = 0; i < rows.length; i += BATCH) {
const chunk = rows.slice(i, i + BATCH);
// mysql2 mở rộng mảng lồng nhau thành nhiều bộ VALUES
await pool.query("INSERT INTO orders (uid, city, amount) VALUES ?", [chunk]);
}
Lưu ý dùng query chứ không phải execute ở đây, vì prepared statement không mở rộng placeholder ? thành nhiều dòng theo cách này.
Lớp 3: LOAD DATA đi đường khác
LOAD DATA nhanh hơn batch INSERT khoảng 2,8 lần vì không cần phân tích cú pháp SQL cho dữ liệu: nó tách theo dấu phân cách, đọc dạng stream và có thể cập nhật index hiệu quả hơn.
LOAD DATA LOCAL INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(uid, city, amount);
Đổi lại nó khá thô: định dạng file phải khớp chặt với bảng, xử lý lỗi kém, một dòng sai có thể làm hỏng cả lô hoặc âm thầm biến thành giá trị mặc định. Ngoài ra LOCAL cho phép server yêu cầu client đọc file nên có rủi ro bảo mật, MySQL 8 tắt local_infile mặc định và phải bật tường minh. Vì thế công cụ này phù hợp với import, migration và ETL, không phải đường ghi online.
Chọn cách nào
| Tình huống | Nên dùng | Thông lượng tham khảo |
|---|---|---|
| Ghi online từng bản ghi (đặt hàng, đăng ký) | INSERT đơn | Không cần tối ưu, bản chất đã là một dòng |
| Batch job, consumer của message queue | Batch INSERT 100 đến 1000 dòng | Vài chục nghìn dòng/giây |
| Import, migration, backfill | LOAD DATA | Hơn trăm nghìn dòng/giây |
| Upsert theo khóa đã biết | Batch INSERT ... ON DUPLICATE KEY UPDATE | Gần bằng batch INSERT |
Những cái bẫy hay gặp
ORM "bulk" có thể không bulk. Tác giả cảnh báo nhiều bulk_create hay saveAll chỉ là vòng lặp INSERT từng dòng trong một transaction, chỉ được khoảng 3,3 lần thay vì 36 lần. Đừng đoán, hãy bật SQL log hoặc xem general log để chắc chắn một multi-row INSERT thực sự được gửi đi.
JDBC không tự gộp batch. Connector/J mặc định không gộp addBatch thành multi-row INSERT cho đến khi bạn thêm tham số:
jdbc:mysql://db:3306/shop?rewriteBatchedStatements=true
Nếu dùng Spring Data JPA, còn phải đặt hibernate.jdbc.batch_size, và biết rằng Hibernate không batch INSERT cho entity dùng GenerationType.IDENTITY, là kiểu cấu hình rất phổ biến với MySQL. Khi đó dùng JdbcTemplate.batchUpdate cho đường ghi lớn thường đơn giản hơn.
Index phụ làm chậm import lớn. Duy trì nhiều secondary index theo từng dòng tốn kém. Với import rất lớn vào bảng chưa phục vụ traffic, có thể DROP INDEX, nạp xong rồi ADD INDEX một lần. Trên bảng đang phục vụ traffic thì không nên làm vì truy vấn đang cần các index đó, và việc tạo lại index cũng tốn tài nguyên.
Thứ tự primary key quan trọng. Tác giả dẫn bài đo trước đó của mình, trong đó primary key ngẫu nhiên khiến insert chậm 11,8 lần. Sắp xếp theo khóa trước khi import là một tối ưu rẻ. Nếu đang dùng UUIDv4 làm khóa chính trong InnoDB, đây cũng là lúc nên xem lại các định dạng có thứ tự thời gian như UUIDv7.
Kiểm tra lại ở dự án của bạn
Muốn áp dụng ngay, hãy tìm trong code các vòng lặp gọi INSERT từng dòng, nhất là trong cron job, script backfill và consumer. Sau đó đo một lô thật với dữ liệu thật, vì schema nhiều index và nhiều cột text sẽ cho con số khác bài đo này.
Với job theo batch, nhớ giới hạn kích thước transaction, ghi lại tiến độ để chạy tiếp được khi lỗi, và theo dõi replication lag nếu có replica. Nhanh hơn trên primary nhưng khiến replica trễ vài phút thì có thể tạo ra lỗi khác ở phía đọc.
Tác giả kết bài bằng một ý dễ nhớ: khoảng cách giữa 52 giây và 0,51 giây không nằm ở database hay ổ đĩa, mà ở chỗ bạn đã nói chuyện với database 100.000 lần hay một lần.
Bài viết được AI của DevViet viết lại bằng tiếng Việt từ một thảo luận/bài viết tiếng Trung trên Juejin, có thể chưa chính xác tuyệt đối. Nguồn: 同样是插 10 万行，最快的写法比最慢的快 103 倍