Hiệu suất chèn hàng loạt MySQL

Để tối ưu hóa tốc độ chèn, hãy kết hợp nhiều thao tác nhỏ thành một thao tác lớn duy nhất. Lý tưởng nhất là bạn tạo một kết nối duy nhất, gửi dữ liệu cho nhiều hàng mới cùng một lúc và trì hoãn tất cả các cập nhật chỉ mục và kiểm tra tính nhất quán cho đến khi kết thúc

Thời gian cần thiết để chèn một hàng được xác định bởi các yếu tố sau, trong đó các con số biểu thị tỷ lệ gần đúng

• Đang kết nối. (3)

• Gửi truy vấn đến máy chủ. (2)

• Phân tích cú pháp truy vấn. (2)

• Chèn hàng. (1 × kích thước của hàng)

• Chèn chỉ mục. (1 × số chỉ mục)

• Đóng cửa. (1)

Điều này không tính đến chi phí ban đầu để mở bảng, được thực hiện một lần cho mỗi truy vấn chạy đồng thời

Tôi đang gặp sự cố khi muốn chèn hàng triệu bản ghi vào cơ sở dữ liệu, bản ghi này cần được nhập từ tệp

Vì vậy, tôi đã thực hiện một số nghiên cứu về vấn đề này và tôi muốn chia sẻ với bạn những gì tôi tìm thấy đã giúp tôi cải thiện thông lượng bản ghi phụ trang lên gần 100 lần

Ban đầu, khi tôi chỉ cố gắng thực hiện thao tác chèn số lượng lớn bằng phương pháp saveAll của spring JPA, tôi đã nhận được hiệu suất khoảng 185 giây trên 10.000 bản ghi. Sau khi thực hiện các thay đổi bên dưới, hiệu suất để chèn 10.000 bản ghi chỉ trong 4. 3 giây

Có, 4. 3 giây cho 10 nghìn bản ghi

Vì vậy, để đạt được điều này, tôi phải thay đổi cách chèn dữ liệu

1. Thay đổi số lượng bản ghi khi chèn

Khi tôi đang chèn ban đầu, tôi đã đẩy trực tiếp tất cả 10 nghìn bản ghi từ danh sách bằng cách gọi phương thức saveAll. Tôi đã thay đổi điều này thành kích thước lô là 30. Bạn cũng có thể tăng kích thước lô lên thậm chí 60, nhưng không mất một nửa thời gian để chèn bản ghi. Xem bảng bên dưới

Đối với điều này, bạn cần đặt thuộc tính ngủ đông

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

spring.jpa.properties.hibernate.jdbc.batch_size=30

Sau đó, tôi đã thêm các thuộc tính chuỗi kết nối sau

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

2. Gửi bản ghi hàng loạt

Tiếp theo, tôi đã thay đổi mã để chèn, sao cho các phương thức saveAll có kích thước lô là 30 để chèn theo những gì chúng tôi cũng đặt trong tệp thuộc tính. Một triển khai rất thô sơ của một cái gì đó như thế này

for (int i = 0; i < totalObjects; i = i + batchSize) {
    if( i+ batchSize > totalObjects){
        List<Book> books1 = books.subList(i, totalObjects - 1);
        repository.saveAll(books1);
        break;
    }
    List<Book> books1 = books.subList(i, i + batchSize);
    repository.saveAll(books1);
}

Điều này làm giảm thời gian một chút; . Đó là một sự cải thiện khoảng 18%

3. Thay đổi chiến lược tạo ID

Điều này đã gây ảnh hưởng lớn

Ban đầu, tôi đang sử dụng chú thích

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

Hibernate có bản cập nhật hàng loạt bị vô hiệu hóa với chiến lược này vì nó phải thực hiện cuộc gọi chọn để lấy id từ cơ sở dữ liệu để chèn từng hàng. Bạn có thể đọc thêm về nó ở đây

Tôi đã thay đổi chiến lược thành

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

public class Book {
    @Id
    @GeneratedValue(strategy = SEQUENCE, generator = "seqGen")
    @SequenceGenerator(name = "seqGen", sequenceName = "seq", initialValue = 1)
    private Long id;

Điều này đã thay đổi đáng kể hiệu suất của phần chèn, vì Hibernate có thể tận dụng phần chèn số lượng lớn

Từ lần cải thiện hiệu suất trước đó là 153 giây, thời gian để chèn 10 nghìn bản ghi giảm xuống chỉ còn 9 giây. Đó là sự gia tăng hiệu suất gần 95%

Ghi chú. MySQL không hỗ trợ tạo trình tự

Để giải quyết vấn đề này, tôi đã tạo một bảng có tên của dãy có một trường duy nhất tên là

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC
                                        &cachePrepStmts=true
                                        &useServerPrepStmts=true
                                        &rewriteBatchedStatements=true

Đối với trình tự trên, tôi đã tạo như sau

CREATE TABLE `seq` (
  `next_val` bigint(20) DEFAULT NULL
);

INSERT INTO `seq` (`next_val`) VALUES(1);

Hibernate sau đó đã sử dụng bảng bên dưới làm trình tạo trình tự

Kích thước lô tối ưu nhất cho trường hợp của tôi là 1.000, mất khoảng 4. 39 giây cho 10 nghìn bản ghi. Sau đó, tôi thấy hiệu suất giảm xuống, như bạn có thể thấy trong biểu đồ bên dưới

Chèn hàng loạt có nhanh hơn không?

Làm cách nào để tăng tốc độ chèn MySQL?

Làm cách nào để chèn 1000 bản ghi cùng một lúc trong MySQL?

MySQL có thể xử lý bao nhiêu phần chèn?