Posted inMySQL

Dọn dẹp bảng MySQL triệu dòng: Đừng để lệnh DELETE làm sập hệ thống của bạn

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Cơn ác mộng mang tên DELETE FROM table_name

Năm 2018, mình từng “suýt bay màu” vì một task tưởng như dễ ăn: dọn dẹp đơn hàng cũ để giải phóng dung lượng. Lúc đó, bảng orders đã chạm mốc 40 triệu dòng. Mình tự tin thực thi một câu lệnh duy nhất:

DELETE FROM orders WHERE created_at < '2022-01-01';

Chỉ sau 10 giây, thảm họa ập đến. Latency hệ thống vọt từ 50ms lên tận 10s. CPU server chạm ngưỡng 100%, hàng đợi connection dài dằng dặc và sếp gọi điện hỏi tại sao web không vào được. Đó là bài học xương máu về việc xử lý dữ liệu lớn trong MySQL.

Vấn đề nằm ở cơ chế vận hành. Khi thực hiện lệnh DELETE lớn, MySQL phải khóa (lock) các dòng dữ liệu và ghi log vào Undo Log để phục vụ rollback. Nó cũng đồng thời cập nhật lại toàn bộ Index liên quan. Với hàng triệu dòng, combo này sẽ vắt kiệt I/O và gây nghẽn cổ chai hệ thống ngay lập tức.

Chiến lược dọn dẹp: Đừng xóa cụt lủn, hãy nghĩ đến đường lui

Trong môi trường Production, dữ liệu cũ vẫn có thể cần thiết để đối soát hoặc làm báo cáo sau này. Thay vì xóa sổ hoàn toàn, mình thường áp dụng quy trình 3 bước an toàn:

  1. Sao lưu (Archive): Chép dữ liệu cũ sang một bảng trung gian hoặc database dự phòng.
  2. Xác minh (Verify): Đảm bảo số lượng bản ghi ở hai bên khớp nhau.
  3. Xóa (Purge): Tiến hành xóa ở bảng chính theo từng đợt nhỏ (Batching).

Kỹ thuật 1: Batching – Chia nhỏ để trị

Đây là cách đơn giản nhất để cứu vãn tình hình. Thay vì xóa 1 triệu dòng một lúc, mình chia nhỏ ra mỗi lần xử lý khoảng 5.000 dòng. Giữa các nhịp, mình cho script nghỉ (sleep) khoảng 1-2 giây. Việc này giúp server có khoảng thở để xử lý các request thực tế từ người dùng.

Dưới đây là procedure mình thường dùng để dọn dẹp tự động:

DELIMITER //

CREATE PROCEDURE PurgeOldOrders()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    
    WHILE rows_affected > 0 DO
        -- Xóa từng đợt nhỏ để tránh lock bảng quá lâu
        DELETE FROM orders 
        WHERE created_at < '2022-01-01' 
        LIMIT 5000;
        
        SET rows_affected = ROW_COUNT();
        
        -- Nghỉ 1 giây để giảm tải cho I/O và Replication
        SELECT SLEEP(1);
    END WHILE;
END //

DELIMITER ;

Lưu ý: Nếu bạn dùng MySQL Replication, việc xóa theo batch là bắt buộc. Nếu xóa quá tay trên Master, Slave sẽ bị lag hàng giờ đồng hồ do phải thực thi lại câu lệnh khổng lồ đó.

Kỹ thuật 2: Partitioning – Xóa dữ liệu trong chớp mắt

Với các bảng dữ liệu vượt ngưỡng 100 triệu record, mình ưu tiên dùng Table Partitioning. Thay vì coi bảng là một khối lớn, MySQL chia nó thành các file vật lý nhỏ dựa trên cột thời gian.

Ví dụ, chia bảng logs theo từng năm:

ALTER TABLE logs PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

Khi cần dọn dẹp dữ liệu năm 2022, bạn không cần dùng lệnh DELETE. Chỉ cần một lệnh DROP duy nhất:

ALTER TABLE logs DROP PARTITION p2022;

Thao tác này gần như tức thời. MySQL chỉ việc xóa file .ibd tương ứng trên ổ cứng, không tốn tài nguyên CPU hay gây lock dòng.

Kỹ thuật 3: Vũ khí hạng nặng pt-archiver

Nếu không muốn tự viết script, hãy dùng pt-archiver trong bộ Percona Toolkit. Đây là công cụ tiêu chuẩn của các DBA chuyên nghiệp.

Nó tự động hóa việc copy dữ liệu sang bảng archive và xóa ở bảng gốc theo cơ chế “gặm nhấm” (nibbling). Một câu lệnh thực tế mình hay dùng:

pt-archiver --source h=localhost,D=my_db,t=orders \
            --dest h=archive_server,D=archive_db,t=orders_history \
            --where "created_at < '2022-01-01'" \
            --limit 1000 --commit-each --sleep 1

Điểm thông minh của pt-archiver là nó sẽ tự dừng lại nếu phát hiện Slave lag hoặc server quá tải.

Ba lưu ý sống còn để tránh treo server

1. Luôn ưu tiên Index

Trước khi chạy lệnh xóa theo cột created_at, hãy chắc chắn cột đó đã có Index. Nếu thiếu Index, MySQL sẽ quét toàn bộ bảng (Full Table Scan). Khi đó, server của bạn chắc chắn sẽ sập trước khi lệnh xóa kịp hoàn tất.

2. Giải quyết nghịch lý dung lượng ổ cứng

Trong MySQL (InnoDB), lệnh DELETE không giúp file .ibd nhỏ đi ngay lập tức. Nó tạo ra các khoảng trống (Fragmented space). Để thu hồi dung lượng, bạn cần chạy OPTIMIZE TABLE. Tuy nhiên, lệnh này sẽ khóa bảng hoàn toàn, nên chỉ thực hiện vào khung giờ ít người truy cập nhất.

3. Kiểm tra ràng buộc khóa ngoại

Hãy cẩn thận với ON DELETE CASCADE. Việc xóa một dòng ở bảng cha có thể kích hoạt chuỗi xóa hàng nghìn dòng ở bảng con. Hiệu ứng domino này thường là nguyên nhân chính khiến I/O vọt lên 100% mà dev không rõ lý do.

Kinh nghiệm rút ra

Xử lý dữ liệu lớn không cần nhanh, mà cần sự ổn định. Thay vì cố gắng quét sạch mọi thứ trong một lần, hãy chọn cách tiếp cận an toàn nhất. Luôn chia nhỏ dữ liệu, theo dõi sát sao biểu đồ monitoring và luôn có bản backup trước khi thực hiện bất kỳ thao tác dọn dẹp nào.

Hy vọng những chia sẻ này giúp bạn tự tin hơn khi xử lý các bảng MySQL khổng lồ. Chúc các bạn tối ưu hệ thống mượt mà!

Share:
Tags: