TRUNCATE — Xóa hàng loạt cực nhanh và High Water Mark

Đoạn mã trong bài này dành cho RDBMS production

Cú pháp TRUNCATE TABLE trong bài này là dạng được dùng bởi các RDBMS production như MySQL / PostgreSQL / Oracle / SQL Server. Console trên trình duyệt cho khóa học này (dùng SQLite) không chấp nhận trực tiếp từ khóa TRUNCATE. Trong SQLite, DELETE FROM table; cho cùng hiệu ứng, và một truncate optimization nội bộ làm nó chạy với tốc độ tương đương TRUNCATE.

TRUNCATE — xóa sạch mọi hàng, cực nhanh

Có hai cách để xóa mọi hàng trong một bảng cùng lúc. Một là DELETE FROM table; (không có WHERE), đã được trình bày trong bài viết trước. Cách kia là `TRUNCATE TABLE table;`, chủ đề của bài này. Cả hai đều để bảng trống, nhưng cách chúng đạt đến đó và tốc độ chúng chạy hoàn toàn khác nhau.

DELETE là một lệnh DML (Data Manipulation Language) xóa hàng từng cái một, để lại một bản ghi log cho mỗi lần xóa. TRUNCATE, ngược lại, nghiêng về DDL (Data Definition Language) — nó đặt lại toàn bộ bảng mà không có sổ sách trên từng hàng, gần như thể định nghĩa bảng được tạo lại từ đầu.

-- 1) DELETE mọi thứ (log xóa từng hàng, có thể rollback)
DELETE FROM customer;

-- 2) TRUNCATE mọi thứ (đặt lại một lần, không log từng hàng, thường commit ngay)
TRUNCATE TABLE customer;

-- Lưu ý: console của khóa học này (SQLite) không chấp nhận TRUNCATE.
--   DELETE FROM customer; nhận cùng truncate optimization nội bộ và chạy nhanh.

Khác biệt giữa MySQL / PostgreSQL / Oracle

Cú pháp TRUNCATE TABLE table; được dùng chung trên MySQL / PostgreSQL / Oracle / SQL Server, nhưng hành vi chi tiết khác nhau theo DB.

- MySQL: Coi là DDL, không thể rollback, AUTO_INCREMENT được đặt lại

- PostgreSQL: Có thể rollback, RESTART IDENTITY đặt lại cả sequence

- Oracle: Coi là DDL, không thể rollback, REUSE STORAGE / DROP STORAGE cho phép kiểm soát cách trả lại bộ nhớ

- SQL Server: Có thể rollback

High Water Mark — đằng sau khoảng cách tốc độ

Chìa khóa để hiểu khoảng cách tốc độ giữa DELETE và TRUNCATE là khái niệm High Water Mark (HWM). HWM là một dấu chỉ tới block lưu trữ cao nhất mà bảng từng dùng. Thuật ngữ đến từ Oracle, nhưng PostgreSQL / SQL Server / MySQL (InnoDB) đều có các tối ưu tương đương chạy nội bộ.

Khi DB thực hiện full scan (quét từng hàng như SELECT * FROM table), nó đọc mọi block từ đầu bảng cho đến HWM theo thứ tự. HWM càng cao, càng nhiều block phải đọc — và vì nó không bỏ qua các block trống, một scan có thể chậm kể cả trên bảng trống nếu HWM cao.

Hành vi quan trọng ở đây là DELETE không hạ HWM. Khi bạn chạy DELETE FROM table; để xóa mọi hàng, nội dung bảng trở nên trống, nhưng bản thân các block vẫn nằm trong segment. HWM vẫn ở block 5. Chạy tiếp SELECT COUNT(*) FROM table;, và mặc dù số hàng là 0, DB đọc mọi block tại hoặc dưới HWM đến hết — mất thời gian gần bằng trước khi xóa.

TRUNCATE TABLE table; là lệnh đặt lại HWM về đầu bảng, ngay lập tức thu hẹp đích full scan xuống 0 block. Đó là lý do thực sự khiến TRUNCATE được mô tả là "tức thì kể cả trên bảng cực lớn."

DELETE không thu nhỏ dữ liệu?

Tôi đã gặp tình huống trong production khi chạy DELETE để giảm kích thước dữ liệu, nhưng mức dùng bộ lưu trữ hầu như không giảm.

Nguyên nhân chính là: HWM không hạ xuống. DELETE chỉ xóa hàng về mặt logic, trong khi các block (page) mà bảng đã reserved vẫn nằm yên. File trên đĩa vẫn giữ vùng đó, nên từ góc nhìn của OS kích thước dữ liệu chưa thay đổi.

Khi bạn thực sự muốn thu nhỏ bộ lưu trữ, hãy chạy lệnh tổ chức lại theo từng DB (VACUUM FULL trên PostgreSQL, OPTIMIZE TABLE trên MySQL InnoDB, ALTER TABLE ... SHRINK SPACE trên Oracle), hoặc dùng TRUNCATE cho xóa toàn bộ.

Chọn giữa DELETE và TRUNCATE

Trong thực tế, lựa chọn không đơn giản là "TRUNCATE cho mọi thứ, DELETE khi cần điều kiện." Các yếu tố thực sự là bạn có cần rollback không, trigger có phải kích hoạt không, và tốc độ xử lý quan trọng đến đâu. Bảng so sánh dưới đây là checklist tiêu chuẩn.

Cạm bẫy của TRUNCATE

TRUNCATE nhanh và tiện, nhưng nó có xu hướng là thao tác bạn không thể lấy lại. Cần lưu ý:

- Một số DB commit khi chạy (Oracle / MySQL). Trong production, hãy chạy SELECT COUNT(*) trước để xác nhận số hàng trước khi kích hoạt nó

- `AUTO_INCREMENT` bị đặt lại (tùy DB). Nếu các id được lộ ra với hệ thống ngoài hoặc URL, hãy cẩn thận với va chạm sau khi đặt lại

- Ràng buộc khóa ngoại có thể chặn nó (hầu hết DB). Nếu subscription tham chiếu customer, bạn cần làm trống bảng con trước để TRUNCATE customer

- Trigger thường không kích hoạt. Một trigger là cơ chế DB tự động chạy SQL khi có insert / update / delete hàng — ví dụ, "ghi một entry audit log vào bảng khác mỗi khi một hàng bị xóa." DELETE kích hoạt trigger này theo từng hàng, nhưng TRUNCATE không hoạt động ở mức hàng, nên audit log và các bản ghi tương tự sẽ không được tạo

Dùng TRUNCATE khi bạn cần tốc độ tối đa cho xóa hàng loạt dữ liệu khổng lồ; còn lại DELETE WHERE hoặc DELETE có thể rollback là lựa chọn an toàn hơn.