Tìm hiểu về row by row update, batch update và bulk update với Mysql và Laravel.

Trong bài viết này mình muốn giới thiệu và phân tích những giải pháp cho bài toàn update nhiều records

1. Row by Row Update

for ($i = 1; $i < 100; $i++) {
    factory(Card::class)->create();
}

Row by row (slow by slow) update là cách update nhiều records hay được sử dụng =)).

Tất nhiên cũng là cách chậm nhất, vì sao, vì nó lãng phí nhiều tài nguyên nhất.

Mỗi câu query ở đây sẽ được gửi lên database 1 cách lần lượt, vậy là sẽ có 99 queries được tạo ra và 99 requests được gửi tới database.

Rất may là nếu làm việc với Laravel thì Connection chỉ được tạo 1 lần ngay câu query đầu tiên được thực hiện, còn sau đó connection này sẽ được sử dụng lại nên chúng ta không cần lo lắng vì sẽ có 99 connections được tạo ra.

Kết quả Benchmark là 281.94ms.

2. Batch update

Định nghĩa (theo mình thôi nhé) : Batch update là trường hợp những câu lệnh update được chạy trong 1 transaction, tương tự với insert hay delete.

DB::beginTransaction();
try {
        for ($i = 1; $i < 100; $i++) {
            factory(Card::class)->create();
        }
         DB::commit();
}
catch() {
        DB::rollback();
}

Dùng cách này thì tất cả query sẽ được chạy trong 1 anonymus block, và nó sẽ chỉ cần commit 1 lần.

Khác với ví dụ bên trên, sẽ có 99 transaction được commit (do trong mysql thì mỗi statement là 1 transaction và ở chế độ autocommit = 1 thì nó sẽ tự động commit sau mỗi statement, khuyên các bạn đừng đặt nó bằng 0 ). Đồng thời cả block này sẽ được gửi lên database trong 1 request.

Vì commit 1 lần tức là tất cả các câu lệnh được thực thi cùng 1 "đợt", dữ liệu được đưa vào ram và ghi vào disk 1 lần nên tốc độ thực thi sẽ tăng lên đáng kể ở đây là gần 8 lần.

Thời gian Benchmark là 38.26ms

Trong trường hợp bạn update mà không cần đưa số lượng lớn data tương ứng với mỗi câu lệnh update mà theo điều kiện của 1 cột khác trong hàng, ví dụ bạn muốn cuối mỗi ngày thì kiểm tra tất cả các mẫu card mà người dùng tạo ra trong vòng 3 ngày mà vẫn chưa tạo tag và không cho hiển thị chúng nữa.

if (card.tag == '' && minus(card.created_at, now()) >=3) {
        card.status = 'draft';
}

Trong trường hợp này bạn hoàn toàn có thể viết proceduce trong mysql và chỉ cần call nó trong Laravel, bạn có thể update tất cả records trong bảng cards với 1 request gọn nhẹ, tất cả statement sẽ được sinh ra trong mysql, bạn không cần gửi chúng đi và có thể kết hợp thêm với transaction để tối ưu hơn.

Dùng load data infile Dùng để update số lượng record vô cùng lớn

Bước 1: Đầu tiên tạo 1 table tương tự table cần update

CREATE TABLE cards_import LIKE cards;

Bước 2: Load data từ file csv

LOAD DATA INFILE 'somefile.csv' INTO TABLE cards_import

Bước 3: Update bảng cards

UPDATE cards A
INNER JOIN cards_import B USING (id)
SET A.name = B.name;
... (columns bạn muốn update)

3 Bulk update

Định nghĩa: Bulk update là trường hợp update nhiều records chỉ bằng 1 query, tương tự với insert hay delete.

  • Bulk update có thể biểu diễn điều kiện bằng logic

Ví dụ bạn muốn cuối mỗi ngày thì kiểm tra tất cả các mẫu card mà người dùng tạo ra trong vòng 3 ngày mà vẫn chưa tạo tag và không cho hiển thị chúng nữa

Chúng ta hoàn toàn có thể thực hiện yêu cầu bên trên với chỉ 1 câu query.

UPDATE table cards set cards.status = 'draft' WHERE cards.tag = '' AND DATEDIFF(cards.created_at, NOW())

Đây là một câu lệnh bulk update và tất nhiên là nó sẽ nhanh hơn batch update kể cả khi chúng ta dùng kết hợp proceduce.

Tuy nhiên bulk update có một nhược điểm là nó sẽ khóa column mà chúng ta update trong suốt thời gian chạy câu lệnh này.

Ví dụ như trong batch update mất 20s mà mỗi statement sẽ mất thời gian thực hiện là khoảng 2ms và những cột được cập nhật cũng chỉ bị khóa trong 2ms.

Còn bulk update mất 3s nhưng tất cả column được update sẽ bị khóa trong suốt 3s này.

Rất may là innoDb không bao giờ khóa reader nên đối với những table mà chỉ phía quản trị viên mới được quyền ghi thì bạn cứ yên tâm mà dùng bulk update.

Thời gian Benchmark là 6.58ms kết quả đối với 100 records

  • Bulk update với các giá trị khác nhau không thể biểu diễn theo điều kiện logic

Ở trường hợp này chúng ta dùng cú pháp case when

update cards
set cards.name = (CASE CardId WHEN 1 THEN 'greeting'
when 2 THEN 'Mysql deep dive'
...
END)
Where CardId IN(1,2,...,100);

Bất cứ trường hợp update nhiều records nào ta cũng có thể viết theo cách này, tuy nhiên khi số lượng record cần update tăng cao thì độ dài câu query này cũng sẽ trở lên siêu to khổng lồ =))

Để tránh trường họp tràn Ram bạn nên chia ra thành nhiều câu lệnh.

Tổng kết

Trên đây là 1 số cách để giải quyết bài toán update nhiều records với mysql, tất cả kết quả benchmark đều được test trên localhost, mysql innoDB, PDO.


All Rights Reserved