SQLite ANALYZE và VACUUM: thống kê và thu hồi dung lượng

Hai việc bảo trì khác nhau

ANALYZE và VACUUM thường được nhắc đến cùng lúc, nhưng chúng sửa những vấn đề khác nhau.

• ANALYZE thu thập số liệu thống kê về dữ liệu để query planner đưa ra lựa chọn thông minh hơn. Lệnh này ghi vào một bảng tên là sqlite_stat1 và không đụng đến các hàng dữ liệu thật.
• VACUUM dựng lại chính tệp cơ sở dữ liệu để thu hồi các trang không dùng đến và chống phân mảnh. Lệnh này không trực tiếp thay đổi kế hoạch truy vấn.

Nếu truy vấn chọn sai chỉ mục, bạn cần ANALYZE. Nếu tệp lớn hơn mức cần thiết sau rất nhiều lần xóa, bạn cần VACUUM. Nhầm lẫn giữa hai lệnh sẽ khiến bạn tốn rất nhiều thời gian bảo trì vô ích.

ANALYZE thực sự làm gì

Query planner phải phỏng đoán. Khi thấy WHERE status = 'active', nó cần ước lượng có bao nhiêu hàng khớp, một hàng hay một triệu hàng, để quyết định nên dùng chỉ mục hay quét cả bảng. Không có số liệu thống kê, nó đành dùng những quy tắc ước lượng thô sơ.

ANALYZE duyệt qua từng chỉ mục và ghi lại thông tin tóm tắt về cách các giá trị phân bố:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

Hàng trong sqlite_stat1 cho planner biết đại khái chỉ mục có bao nhiêu hàng và một khóa điển hình có bao nhiêu bản trùng. Lần sau khi bạn truy vấn WHERE status = 'pending', nó biết pending hiếm gặp và chọn dùng chỉ mục; với WHERE status = 'shipped', nó có thể quyết định quét bảng rẻ hơn.

Bạn có thể phân tích một bảng hoặc một chỉ mục riêng thay vì cả cơ sở dữ liệu:

ANALYZE orders;
ANALYZE idx_orders_status;

Hãy chạy ANALYZE sau khi nạp dữ liệu hàng loạt, sau những thay đổi lớn về schema, hoặc khi bạn nhận thấy planner chọn kế hoạch kém trên những bảng mà phân bố dữ liệu đã thay đổi.

PRAGMA optimize: lựa chọn mặc định hiện nay

Chạy ANALYZE một cách mù quáng mỗi lần đóng kết nối là lãng phí: phần lớn thời gian, chẳng có gì thay đổi đủ nhiều để đáng kể. SQLite có sẵn một lệnh bọc thông minh hơn:

PRAGMA optimize;

PRAGMA optimize kiểm tra cơ sở dữ liệu đã thay đổi thế nào kể từ lần phân tích trước và chỉ chạy ANALYZE trên những bảng cần đến. Khuyến nghị chính thức là gọi nó trên mỗi kết nối tồn tại lâu ngay trước khi đóng, và gọi định kỳ trên những kết nối mở suốt nhiều giờ.

Lệnh này rẻ khi không có gì thay đổi và hiệu quả khi có thay đổi. Hãy dùng optimize trước; chỉ dùng ANALYZE trực tiếp khi bạn cần buộc làm mới số liệu.

VACUUM thực sự làm gì

Khi bạn xóa hàng hoặc xóa một bảng, SQLite đánh dấu các trang đó là trống nhưng không thu nhỏ tệp. Các trang trống sẽ được dùng lại cho những lần chèn sau, nên phần lớn thời gian chuyện này không sao. Nhưng có hai thứ tích tụ dần qua thời gian dài thêm xóa liên tục:

1. Dung lượng trống mà hệ điều hành không thấy. Tệp .db vẫn nặng 2 GB dù chỉ có 800 MB là dữ liệu đang dùng.
2. Phân mảnh. Các hàng của cùng một bảng nằm rải rác trên những trang không liền nhau, làm chậm việc quét bảng.

VACUUM khắc phục cả hai bằng cách sao chép toàn bộ cơ sở dữ liệu sang một tệp mới, xếp chặt lại, rồi thay thế tệp gốc:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Sau VACUUM, tệp có kích thước như thể bạn chỉ chèn 100 hàng còn lại từ đầu. Một hệ quả phụ là mọi rowid vẫn giữ nguyên nhưng cách bố trí trên đĩa đã liền mạch trở lại.

Vài điều cần biết trước khi chạy lệnh này:

• Nó cần khóa độc quyền trên cơ sở dữ liệu trong suốt thời gian chạy. Không kết nối nào khác ghi được.
• Nó cần dung lượng đĩa trống khoảng gấp đôi kích thước cơ sở dữ liệu, vì nó dựng tệp mới bên cạnh tệp cũ.
• Nó không chạy được bên trong transaction, và sẽ báo lỗi nếu có transaction nào đang mở.
• Trên cơ sở dữ liệu nặng nhiều GB, nó có thể mất rất lâu. Hãy lên kế hoạch trước.

Khi nào thực sự nên chạy VACUUM

Với phần lớn ứng dụng: đừng chạy, trừ khi có thay đổi cụ thể nào đó.

Lý do chính đáng để chạy VACUUM:

• Bạn vừa xóa một bảng lớn hoặc xóa một loạt hàng khổng lồ và muốn lấy lại dung lượng đĩa.
• Cơ sở dữ liệu đã được thêm xóa liên tục nhiều năm và các truy vấn quét bảng có vẻ chậm hơn trước.
• Bạn phát hành tệp cơ sở dữ liệu kèm theo một bản release và muốn nó nhỏ nhất có thể.

Lý do không chính đáng:

• "Cho chắc ăn." Lệnh này ghi lại toàn bộ tệp mỗi lần chạy. Làm vậy trên một hệ thống đang hoạt động chẳng có gì là chắc ăn cả.
• Sau mỗi đợt xóa. Các trang được giải phóng đằng nào cũng sẽ được dùng lại.

auto_vacuum và VACUUM tăng dần

Nếu muốn SQLite tự động quản lý các trang trống, bạn đặt auto_vacuum khi tạo cơ sở dữ liệu, vì sau này không thể đổi được nếu không chạy một lần vacuum đầy đủ:

PRAGMA auto_vacuum = INCREMENTAL;

Có ba chế độ:

• NONE (mặc định): các trang trống vẫn nằm trong tệp, được dùng lại cho những lần chèn sau.
• FULL: mỗi lần commit giải phóng trang cũng cắt bớt tệp. Tiện lợi, nhưng transaction nào cũng phải chịu chi phí đó.
• INCREMENTAL: SQLite theo dõi các trang trống nhưng chỉ giải phóng chúng khi bạn yêu cầu:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) trả tối đa N trang trống về cho hệ điều hành: nhanh, không giữ khóa độc quyền lâu, và bạn có thể chạy theo lịch. Đây là điểm cân bằng lý tưởng cho những cơ sở dữ liệu ghi nhiều cần luôn gọn nhẹ mà không phải trả chi phí của một lần VACUUM đầy đủ.

VACUUM INTO: xuất một bản sao gọn nhẹ

VACUUM INTO ghi một bản sao mới, gọn nhẹ sang một tệp khác mà không đụng đến tệp gốc:

VACUUM INTO 'backup.db';

Lệnh này thật sự hữu ích:

• Sao lưu. Kết quả là một bản chụp nhất quán, đã được vacuum hoàn toàn: không có trang ghi dở, không phải bận tâm đến tệp .wal. Tốt hơn việc sao chép tệp bằng cp.
• Thu nhỏ mà không chặn các tiến trình ghi quá lâu. Bạn vacuum ra một tệp phụ, rồi hoán đổi một cách nguyên tử. Các tiến trình ghi không bị chặn trong suốt quá trình vacuum.
• Phân phối. Gửi đi một bản sao nhỏ gọn, đã chống phân mảnh của cơ sở dữ liệu dùng khi phát triển.

Tệp đích không được tồn tại sẵn. Nếu đã tồn tại, bạn sẽ nhận một lỗi.

Quy trình bảo trì thực tế

Với một cơ sở dữ liệu ứng dụng điển hình:

-- On each long-lived connection, before close:
PRAGMA optimize;

-- After a large bulk load or schema change:
ANALYZE;

-- After you delete a lot of data and want disk back:
VACUUM;

-- For backups:
VACUUM INTO '/backups/app-2026-04-23.db';

Nếu cơ sở dữ liệu chủ yếu ghi và xóa dữ liệu, và luôn hoạt động 24/7, hãy đặt auto_vacuum = INCREMENTAL khi tạo và chạy PRAGMA incremental_vacuum(N) định kỳ, có thể mỗi ngày một lần vào lúc ít truy cập.

Chẩn đoán "Vì sao tệp của mình nặng thế?"

Hai pragma cho bạn biết chuyện gì đang xảy ra:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = kích thước tệp hiện tại.
• freelist_count × page_size = số byte bị lãng phí cho các trang không dùng đến.

Nếu freelist_count chiếm một phần lớn của page_count, một lần VACUUM (hoặc incremental_vacuum) sẽ thu nhỏ tệp rõ rệt. Nếu con số đó nhỏ, tệp đã được xếp gọn hiệu quả và VACUUM sẽ không giúp được gì.

Những lỗi thường gặp

• Chạy VACUUM bên trong transaction. Không được. Hãy commit trước.
• Quên rằng VACUUM cần dung lượng đĩa trống. Một cơ sở dữ liệu 10 GB cần thêm khoảng 10 GB trống để vacuum.
• Đặt auto_vacuum khi đã có dữ liệu. Lệnh này không có tác dụng cho đến lần VACUUM đầy đủ tiếp theo. Nếu cần, hãy đặt nó khi tạo cơ sở dữ liệu.
• Chạy ANALYZE và mong tệp nhỏ đi. Đó là việc của VACUUM.
• Chạy VACUUM và mong kế hoạch truy vấn tốt hơn. Đó là việc của ANALYZE.

Hai lệnh bổ sung cho nhau; không lệnh nào thay thế được lệnh kia.

Tiếp theo: transaction

Những lệnh bảo trì như VACUUM làm nổi bật một điều mà ta vẫn coi là hiển nhiên: mô hình transaction của SQLite và việc nó khóa cái gì, vào lúc nào. Chương tiếp theo bắt đầu từ đó: transaction hoạt động ra sao, BEGIN / COMMIT / ROLLBACK thực sự đảm bảo điều gì, và cách dùng chúng để giữ cho một nhóm câu lệnh được thực thi nguyên vẹn.