CHECK gần như miễn phí, nhưng xoá một dòng cha mà quên chỉ mục thì chậm 120 lần

Cắm một biển "đường một chiều" từ khu con sang khu cha, bạn tưởng đã nối hai khu với nhau. Nhưng khi cần đi ngược lại — từ cha tìm về mọi đứa con, đúng lúc xoá một dòng cha — thì không có đường nào cả, và xe phải rà từng nhà. Một tham chiếu khai theo một chiều là một con đường một chiều, còn chuyến đi ngược là một cuộc quét toàn bảng nếu bạn không xây luôn con đường kia. Không biết điều này là lý do một câu DELETE tưởng chừng tức thì lại mất hàng giây. Ràng buộc là cách rẻ nhất để dữ liệu không bị hỏng, nhưng "rẻ" là một từ định tính; phần này gán số cho nó.

Chèn một triệu dòng

Cùng một bảng, cùng dữ liệu, mỗi lần thêm đúng một loại ràng buộc:

Ba nhóm rõ rệt.

NOT NULL và CHECK miễn phí. Chênh lệch 0,89–0,96 giây nằm trong dải nhiễu của lần đo không ràng buộc. Chúng chỉ là một phép kiểm trên giá trị đã nằm sẵn trong bộ nhớ, không đọc thêm gì, không ghi thêm gì.

Đây là điều đáng nhớ nhất trong bài: nếu bạn từng ngần ngại thêm CHECK vì sợ chậm, đừng. Không có lý do hiệu năng nào để bỏ qua CHECK và NOT NULL.

PRIMARY KEY và UNIQUE đắt vừa phải, và cái giá của chúng là cái giá của một chỉ mục — 21 MB và 50 MB. Con số này không có gì bất ngờ, và nó là chi phí bạn thường sẵn sàng trả vì chỉ mục đó cũng phục vụ truy vấn.

FOREIGN KEY đắt nhất mà không sinh ra chỉ mục nào. 5,67 giây so với 0,92 — chậm 6,2 lần. Với mỗi dòng chèn vào, PostgreSQL phải tra bảng cha xem giá trị đó có tồn tại không. Một triệu dòng là một triệu lần tra.

Chú ý WAL của khoá ngoại chỉ 94 MB — gần bằng bảng không ràng buộc. Chi phí của nó nằm ở đọc, không nằm ở ghi. Đó là lý do nó không hiện ra trong các chỉ số theo dõi ghi.

Cái bẫy lớn nhất: PostgreSQL không tự dựng chỉ mục cho khoá ngoại

Khi bạn khai references kh(id), PostgreSQL đòi bảng cha phải có chỉ mục duy nhất trên id — nhưng nó không dựng chỉ mục nào trên kh_id của bảng con.

Hậu quả lộ ra khi xoá dòng cha. Bảng con 2.000.000 dòng, on delete cascade:

Nhanh khoảng 120 lần.

Không có chỉ mục, mỗi lần xoá một dòng cha là một lần quét toàn bộ bảng con để tìm dòng liên quan. Với bảng con vài chục triệu dòng, một câu delete from kh where id = ? tưởng chừng tức thì sẽ mất hàng giây — và nếu bạn xoá 1.000 dòng cha thì đó là 1.000 lần quét toàn bảng.

Điều tương tự áp cho UPDATE trên khoá chính của bảng cha, và cho ON DELETE SET NULL.

Tìm những khoá ngoại đang thiếu chỉ mục:

select c.conrelid::regclass as bang_con,
       a.attname as cot,
       c.confrelid::regclass as bang_cha
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.contype = 'f'
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid and i.indkey[0] = c.conkey[1]
  );

Mỗi dòng trả về là một khoá ngoại chưa có chỉ mục. Không phải cái nào cũng cần — nếu bảng cha không bao giờ bị xoá dòng thì không sao — nhưng bạn nên biết chúng tồn tại.

CHECK không chặn NULL

create table n(id int, gia numeric, check (gia >= 0));

insert into n values (1, -5);      -- ERROR: violates check constraint
insert into n values (2, null);    -- thành công

Dòng thứ hai lọt qua.

null >= 0 cho ra UNKNOWN, không phải FALSE. Và CHECK chỉ từ chối khi biểu thức cho ra FALSE — UNKNOWN được coi là chấp nhận được. Đây là hệ quả trực tiếp của logic ba trị đã đo ở phần 9.

Nghĩa là check (gia >= 0) không bảo đảm "mọi giá đều không âm". Nó bảo đảm "không có giá nào âm, nhưng có thể có giá không xác định".

Muốn chặn cả hai thì phải viết cả hai:

create table n(id int, gia numeric not null, check (gia >= 0));

Hoặc gộp vào chính CHECK:

check (gia is not null and gia >= 0)

Cách thứ nhất tốt hơn vì NOT NULL được bộ lập lịch dùng để tối ưu, còn CHECK thì không.

Thêm ràng buộc vào bảng đang chạy

ALTER TABLE ADD CONSTRAINT phải quét toàn bảng để kiểm dữ liệu cũ, và nó giữ khoá ACCESS EXCLUSIVE suốt thời gian đó — mọi truy vấn chạm vào bảng đều phải đợi.

Trên bảng 3 triệu dòng thì chỉ 0,17 giây, không sao. Trên bảng 300 triệu dòng thì đó là nhiều phút ngừng dịch vụ.

Cách tách làm hai bước:

-- Bước 1: tức thì, chỉ áp cho dòng mới
alter table big add constraint c1 check (gia >= 0) not valid;   -- 0,072 s

-- Bước 2: quét bảng, nhưng khoá nhẹ hơn nhiều
alter table big validate constraint c1;                          -- 0,16 s

Bước 1 chỉ ghi định nghĩa ràng buộc vào catalog và không kiểm dữ liệu cũ. Từ giây đó, mọi dòng mới và mọi lần cập nhật đều phải thoả ràng buộc — nên dữ liệu không xấu thêm.

Bước 2 quét bảng để xác nhận dữ liệu cũ cũng thoả, nhưng nó chỉ giữ SHARE UPDATE EXCLUSIVE — đọc và ghi vẫn chạy bình thường.

Cách này áp được cho cả CHECK lẫn FOREIGN KEY, và nó là cách duy nhất thêm ràng buộc vào bảng lớn mà không ngừng dịch vụ.

Nếu bước 2 báo lỗi vì dữ liệu cũ vi phạm, ràng buộc vẫn ở trạng thái NOT VALID — vẫn chặn dòng mới. Bạn có thời gian dọn dữ liệu cũ rồi chạy lại VALIDATE.

Bảng chọn nhanh

Dòng cuối là dòng duy nhất cần cân nhắc. 516% nghe rất nhiều, nhưng con số tuyệt đối là 5,67 giây cho một triệu dòng — tức 5,7 micro giây mỗi dòng. Với ứng dụng chèn vài trăm dòng mỗi giây, chi phí đó vô hình.

Nó chỉ thành vấn đề ở hai chỗ: nạp dữ liệu hàng loạt (và khi ấy hãy bỏ khoá ngoại, nạp, rồi thêm lại bằng NOT VALID + VALIDATE), và bảng có tần suất ghi rất cao.

Phần 35 đã đo mặt còn lại của lựa chọn này: dùng mảng thay bảng phụ thì tiết kiệm được 4 lần dung lượng, nhưng mất hẳn khả năng đặt khoá ngoại — và dữ liệu mồ côi tích lại mà không có gì phát hiện.

Mẫu số chung

Cái bẫy khoá ngoại thiếu chỉ mục chỉ ra một sự thật hay bị bỏ qua: một quan hệ đối xứng về ý nghĩa nhưng bất đối xứng về máy móc. Khai "con trỏ tới cha" cho bạn đi nhanh đúng một chiều — chèn con thì tra được cha ngay — nhưng chiều ngược lại, "cha có những đứa con nào", không có con đường nào cả cho tới khi bạn tự xây. Và chiều ngược ấy đúng là chiều người ta hay quên, vì họ chỉ khai chiều xuôi; nó vô hình cho tới khi thao tác duyệt nó — xoá một dòng cha — chạy, rồi hoá thành một cú quét toàn bảng.

Đúng cái "mỗi chiều duyệt cần cấu trúc riêng" này lặp ở mọi nơi:

• Trong Java/ORM, một @ManyToOne ánh xạ khoá ngoại, nhưng cơ sở dữ liệu vẫn cần chỉ mục trên cột đó để đi ngược @OneToMany hay để cascade delete — Hibernate không tự thêm, đúng cái bẫy này ở tầng ORM. Xa hơn, một HashMap cho tra khoá→giá trị nhanh O(1), nhưng tra ngược giá trị→khoá là O(n) trừ khi bạn dựng thêm một map thứ hai (BiMap của Guava).
• Trong Go, một map[K]V cũng chỉ nhanh một chiều; muốn tra ngược phải giữ một map thứ hai, không có lối tắt.
• Trong đồ thị, một danh sách kề lưu cạnh-đi (out-edge); câu hỏi "ai trỏ tới tôi" (in-edge) cần một chỉ mục ngược riêng — và một chỉ mục ghép (a, b) không phục vụ được truy vấn lọc theo b.

Nên câu hỏi đúng khi tạo bất kỳ liên kết nào không phải "tôi đã nối hai thứ chưa" mà là "tôi sẽ đi qua liên kết này theo những chiều nào, và mỗi chiều đã có đường chưa". Đường xuôi thường tự có; đường ngược là đường bạn phải nhớ tự xây. Tự soát ngay bằng câu tìm khoá ngoại thiếu chỉ mục ở trên, rồi với mỗi kết quả hỏi: bảng cha này có bao giờ bị xoá dòng không? Nếu có, dựng chỉ mục — 15 MB đổi lấy 120 lần tốc độ là đánh đổi dễ. Và kiểm ràng buộc nào đang chưa xác nhận:

select conrelid::regclass, conname, contype
from pg_constraint where not convalidated;

Ràng buộc NOT VALID chặn được dòng mới nhưng bộ lập lịch không dùng nó để tối ưu truy vấn. Nếu bạn thêm nó rồi quên chạy VALIDATE, bạn mất một nửa lợi ích mà không biết.

Phần sau đo trigger: chi phí thật của mỗi lần kích hoạt, và ba chỗ nó gây bất ngờ.