Hướng dẫn mysql update large number of rows - cập nhật mysql số lượng lớn hàng

Ngày 30 tháng 1 năm 2021 · 7 phút Đọc

• Giới thiệu
• Thành lập
• Các vấn đề với một bản cập nhật lớn duy nhất
• Cập nhật theo đợt
• Sự kết luận
• đọc thêm

Giới thiệu

Thành lập

Các vấn đề với một bản cập nhật lớn duy nhất

Cập nhật theo đợt

Sự kết luận

đọc thêm

Thành lập

Các vấn đề với một bản cập nhật lớn duy nhất

1. Cập nhật theo đợt
2. Sự kết luận
3. đọc thêm

Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa (cập nhật hoặc xóa) bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.

mkdir lock_update && cd lock_update

Nếu bạn đang tự hỏi

#!/usr/bin/env python3
import argparse
import random

from faker import Faker


def gen_user_data(file_name: str, num_records: int, seed: int = 1) -> None:
    fake = Faker("en_US")
    with open(file_name, "w") as file1:
        for i in range(1, num_records + 1):
            file1.write(
                f"{seed + i},{fake.name()},{random.randint(0,1)},{fake.state()},{fake.country()}\n"
            )


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Generate some fake data")
    parser.add_argument(
        "--file-name",
        type=str,
        default="fake_user.csv",
        help="file name to store fake data",
    )
    parser.add_argument(
        "--num-records", type=int, default=100, help="Num of records to generate"
    )

    parser.add_argument("--seed", type=int, default=0, help="seed")
    args = parser.parse_args()

    gen_user_data(
        file_name=args.file_name, num_records=args.num_records, seed=args.seed
    )

Làm thế nào để cập nhật hàng triệu hồ sơ mà không ảnh hưởng đáng kể đến trải nghiệm người dùng?

chmod u+rwx gen_fake.py
# run 5 parallel processes, each generating 2million records as shown below
./gen_fake.py --file-name user_data_1.csv --num-records 2000000 --seed 0 & ./gen_fake.py --file-name user_data_2.csv --num-records 2000000 --seed 2000000 & ./gen_fake.py --file-name user_data_3.csv --num-records 2000000 --seed 4000000 & ./gen_fake.py --file-name user_data_4.csv --num-records 2000000 --seed 6000000 & ./gen_fake.py --file-name user_data_5.csv --num-records 2000000 --seed 8000000 
mkdir data
cat user_data_1.csv user_data_2.csv user_data_3.csv user_data_4.csv user_data_5.csv >> ./data/user_data_fin.csv # combine data
rm user_data_1* user_data_2* user_data_3* user_data_4* user_data_5*

Làm thế nào để bản ghi lệnh khóa cập nhật?

docker run -d -p 3306:3306 --name mysql-updates -v "$(pwd)"/data:/var/lib/data -e MYSQL_ROOT_PASSWORD=Password1234 mysql:8.0
docker exec -it mysql-updates bash # open docker shell
mysql -h 127.0.0.1 -P 3306 -u root --local-infile=1 -p # password is Password1234

Sau đó, bài viết này là dành cho bạn. Lưu ý rằng đây không phải là cách duy nhất. Có các cách tiếp cận khác như hoán đổi bảng, chạy bản cập nhật tiêu chuẩn tùy thuộc vào mức cách ly giao dịch của bạn, v.v ... Việc sử dụng các phương pháp này phụ thuộc vào trường hợp sử dụng của bạn. Đối với trường hợp sử dụng của chúng tôi, hãy để giả sử chúng tôi đang cập nhật bảng người dùng, nếu bị khóa trong một khoảng thời gian đáng kể (giả sử> 10s), có thể ảnh hưởng đáng kể đến trải nghiệm người dùng của chúng tôi và không lý tưởng.

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 million

Các vấn đề với một bản cập nhật lớn duy nhất

Cập nhật theo đợt

Sự kết luận

đọc thêm

update user set st = 'NY' where user_id between 3000000 and 8000000;
-- 2 min 13.46 sec

Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa (cập nhật hoặc xóa) bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234

update updates.user set name = 'Amos Burton' where user_id = 3300000;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Nếu bạn đang tự hỏi

Làm thế nào để cập nhật hàng triệu hồ sơ mà không ảnh hưởng đáng kể đến trải nghiệm người dùng?

show variables like 'innodb_lock_wait_timeout';

Làm thế nào để bản ghi lệnh khóa cập nhật?

Cập nhật theo đợt

Sự kết luận

đọc thêm

Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa (cập nhật hoặc xóa) bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.

Nếu bạn đang tự hỏi

USE updates;
DROP PROCEDURE IF EXISTS BatchUpdate;

DELIMITER $$
CREATE PROCEDURE BatchUpdate(
    start_id INT,
    end_id INT,
    batch_size INT)
BEGIN

    DECLARE batch_start_id INT DEFAULT start_id;
    DECLARE batch_end_id INT DEFAULT start_id + batch_size;
    DECLARE loop_counter INT DEFAULT 0;

    WHILE batch_end_id <= end_id DO

        SELECT CONCAT('UPDATING FROM ', batch_start_id, ' TO: ', batch_end_id) as log;
        UPDATE user SET st = 'NJ' WHERE user_id BETWEEN batch_start_id and batch_end_id;

        SET batch_start_id = batch_start_id + batch_size;
        SET batch_end_id = batch_end_id + batch_size;
        SET loop_counter = loop_counter + 1;

    END WHILE;

    SELECT CONCAT('UPDATING FROM ', batch_end_id - batch_size, ' TO: ', end_id) as log, loop_counter;
    UPDATE user SET is_active = true WHERE user_id BETWEEN (batch_end_id - batch_size) and end_id;

END$$
DELIMITER ;

call BatchUpdate(3000000, 8000000, 50000);
-- takes a total of 4 min 43.49 sec

Làm thế nào để cập nhật hàng triệu hồ sơ mà không ảnh hưởng đáng kể đến trải nghiệm người dùng?

Làm thế nào để bản ghi lệnh khóa cập nhật?

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234

#!/usr/bin/env python3
import argparse
import random

from faker import Faker


def gen_user_data(file_name: str, num_records: int, seed: int = 1) -> None:
    fake = Faker("en_US")
    with open(file_name, "w") as file1:
        for i in range(1, num_records + 1):
            file1.write(
                f"{seed + i},{fake.name()},{random.randint(0,1)},{fake.state()},{fake.country()}\n"
            )


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Generate some fake data")
    parser.add_argument(
        "--file-name",
        type=str,
        default="fake_user.csv",
        help="file name to store fake data",
    )
    parser.add_argument(
        "--num-records", type=int, default=100, help="Num of records to generate"
    )

    parser.add_argument("--seed", type=int, default=0, help="seed")
    args = parser.parse_args()

    gen_user_data(
        file_name=args.file_name, num_records=args.num_records, seed=args.seed
    )

Bản cập nhật này thực hiện nhanh chóng. Mặc dù bản cập nhật lớn mất nhiều thời gian hơn vì chúng tôi đang thực hiện theo các đợt, chúng tôi có thể thấy cách tiếp cận này cho phép các giao dịch khác chỉnh sửa dữ liệu.

Sự kết luận

Bạn có thể phá bỏ các thùng chứa Docker của bạn bằng các lệnh sau.

#!/usr/bin/env python3
import argparse
import random

from faker import Faker


def gen_user_data(file_name: str, num_records: int, seed: int = 1) -> None:
    fake = Faker("en_US")
    with open(file_name, "w") as file1:
        for i in range(1, num_records + 1):
            file1.write(
                f"{seed + i},{fake.name()},{random.randint(0,1)},{fake.state()},{fake.country()}\n"
            )


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Generate some fake data")
    parser.add_argument(
        "--file-name",
        type=str,
        default="fake_user.csv",
        help="file name to store fake data",
    )
    parser.add_argument(
        "--num-records", type=int, default=100, help="Num of records to generate"
    )

    parser.add_argument("--seed", type=int, default=0, help="seed")
    args = parser.parse_args()

    gen_user_data(
        file_name=args.file_name, num_records=args.num_records, seed=args.seed
    )

Tóm lại, chúng tôi đã thấy

1. Làm thế nào một bản cập nhật lớn có thể khóa các bản cập nhật khác và xóa các hoạt động và gây ra lỗi

   create database updates;
   use updates;
   drop table if exists user;
   CREATE TABLE user (
       user_id int,
       name varchar(100),
       is_active boolean,
       st varchar(100),
       country varchar(100),
       PRIMARY KEY (user_id)
   );
   SET GLOBAL local_infile=1;
   LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
   -- time taken to load 1 min 22.36 sec
   select count(*) from user;
   -- should be 10 million
   

   0.
2. Làm thế nào việc cập nhật theo các lô nhỏ hơn thực hiện nhanh hơn mỗi đợt và do đó khóa sẽ chỉ phải đợi một vài giây, nếu có.

Một điểm quan trọng cần lưu ý ở đây là thứ tự cập nhật. Khi chạy các bản cập nhật hàng loạt và cập nhật đơn trên cùng một cột của bản ghi, bản ghi mới nhất sẽ được áp dụng. Bạn cũng có thể thực hiện các bản cập nhật được ghép đôi này song song, nhưng bạn phải cẩn thận không gặp bất kỳ bế tắc nào.

Nếu logic cập nhật phức tạp hơn và bảng của bạn có chỉ mục bạn, có thể sử dụng

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 million

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 million

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 million

Lần tới khi bạn thực hiện một bản cập nhật lớn trên bảng, hãy xem xét chạy các bản cập nhật trong các phần để giữ cho các giao dịch khác không bị ảnh hưởng bởi các lỗi thời gian chờ khóa.

đọc thêm

1. Lập chỉ mục ảnh hưởng đến cập nhật

References:

1. Mức cách cô lập giao dịch
2. Thủ tục lưu trữ
3. Cập nhật với sự bỏ qua
4. Khóa mysql

Làm cách nào để cập nhật MySQL nhanh hơn?

Giới hạn có hoạt động khi cập nhật MySQL không?

Cập nhật trong SQL Cập nhật nhiều hàng?

Chúng ta có thể cập nhật nhiều hàng trong một câu lệnh cập nhật không?