Phần trước thấy trang lá của cây B-tree bảng sắp xếp các dòng theo rowid. Nhưng rowid là gì, nó đến từ đâu, và chuyện gì xảy ra khi khóa chính của bạn không phải một số nguyên? Câu trả lời định hình trực tiếp dung lượng file và tốc độ tra cứu — và SQLite cho một lựa chọn, WITHOUT ROWID, mà khi dùng đúng chỗ cắt một nửa dung lượng. Mọi con số dưới đây đo trong container sqlite 3.45.3.
Mỗi bảng thường có một rowid ẩn
Theo mặc định, mọi bảng trong SQLite là một rowid table: nó có một khóa số nguyên 64-bit ẩn tên rowid, và chính rowid này là khóa sắp xếp của cây B-tree. Bạn không khai nó, nhưng nó luôn ở đó:
CREATE TABLE a(id INTEGER PRIMARY KEY, v TEXT);
INSERT INTO a(id, v) VALUES (7, 'x');
SELECT rowid, id FROM a; -- 7 | 7
rowid và id trả về cùng một giá trị. Đó không phải trùng hợp: khi bạn khai một cột là INTEGER PRIMARY KEY, SQLite không tạo cột mới mà đặt bí danh cho rowid — cột id chính là rowid, chỉ với một cái tên khác. Không có lưu trữ phụ, không có index phụ. Đây là kiểu khóa chính rẻ nhất và nhanh nhất SQLite có: tra cứu theo id chính là đi thẳng vào cây B-tree của bảng.
Khóa chính không phải số nguyên sinh thêm một cây B-tree
Giờ đổi kiểu khóa chính sang TEXT và xem sqlite_master:
CREATE TABLE a(ma TEXT PRIMARY KEY, v TEXT);
SELECT type, name, tbl_name FROM sqlite_master;
-- table | a | a
-- index | sqlite_autoindex_a_1 | a ← SQLite tự tạo
Một khóa chính không phải INTEGER không thành bí danh của rowid được — bảng vẫn có rowid ẩn làm khóa cây, còn ma được cưỡng chế duy nhất bằng một index tự động (sqlite_autoindex_a_1), tức một cây B-tree thứ hai. Lúc này dữ liệu nằm trong cây bảng (keyed theo rowid), và một bản sao của ma cộng rowid nằm trong cây index. Hai cây cho một bảng.
Đo cái giá: index tự động gần như nhân đôi file
Hãy để con số nói. Một bảng kho với khóa chính TEXT (mã SKU), 500 nghìn dòng:
CREATE TABLE kho(ma TEXT PRIMARY KEY, soluong INTEGER);
-- nạp 500.000 dòng: ma = 'SKU-00000001', 'SKU-00000002', ...
rowid table: file = 24,06 MB page_count = 5874
cây bảng kho : 8 trang trong + 2 930 trang lá (dữ liệu, keyed rowid)
sqlite_autoindex_kho_1 : 21 trang trong + 2 914 trang lá (index ma → rowid)
Nhìn kỹ: cây index tự động chiếm 2 935 trang — gần bằng chính cây dữ liệu (2 938 trang). Nói cách khác, hơn một nửa file không phải dữ liệu mà là một cấu trúc phụ chỉ để cưỡng chế và tra cứu khóa chính TEXT. Với một khóa chính rộng (chuỗi 12 ký tự ở đây), cái giá đó rất thật.
Hình 1: Ba cách khai khóa chính — INTEGER PRIMARY KEY (bí danh rowid, một cây), khóa TEXT trên bảng rowid (hai cây), và WITHOUT ROWID (một cây keyed theo chính khóa chính).
WITHOUT ROWID: để chính khóa chính làm khóa cây
Nếu khóa chính TEXT đằng nào cũng là thứ ta tra cứu, tại sao phải giữ một rowid ẩn và một index riêng? Đó chính là điều WITHOUT ROWID loại bỏ. Khai cùng bảng kho đó với WITHOUT ROWID, cây B-tree của bảng được keyed trực tiếp theo ma — dữ liệu nằm ngay trong lá, sắp theo thứ tự ma, không có rowid, không có index phụ:
CREATE TABLE kho(ma TEXT PRIMARY KEY, soluong INTEGER) WITHOUT ROWID;
WITHOUT ROWID: file = 12,03 MB page_count = 2936
cây bảng kho : 21 trang trong + 2 914 trang lá (chính là dữ liệu, keyed theo ma)
(không còn sqlite_autoindex)
24,06 MB → 12,03 MB: đúng một nửa. Index tự động biến mất hoàn toàn vì không còn cần nó — cây bảng giờ chính là cấu trúc tra cứu theo ma. Để ý số trang lá của bảng WITHOUT ROWID (2 914) trùng khít với số trang lá của index tự động ở phương án rowid: cùng một cây tra cứu theo ma, chỉ khác là ở WITHOUT ROWID nó mang luôn dữ liệu, còn ở rowid table nó chỉ mang con trỏ rowid và ta phải trả thêm cả cây dữ liệu nữa.
Hình 2: Cùng 500 nghìn dòng, WITHOUT ROWID cắt file từ 24,06 MB xuống 12,03 MB bằng cách bỏ hẳn index tự động — đo bằng dbstat trong container sqlite 3.45.3.
Cái giá của WITHOUT ROWID
Không có bữa trưa miễn phí. WITHOUT ROWID thắng khi khóa chính là một giá trị tự nhiên không phải số nguyên mà bạn thường tra cứu theo nó, và mỗi dòng tương đối nhỏ. Nhưng nó có hai cái giá cần đo trước khi dùng. Thứ nhất, mọi index thứ cấp trên bảng WITHOUT ROWID phải lưu trọn vẹn khóa chính để trỏ ngược về dòng (thay vì một rowid 64-bit gọn) — nếu khóa chính rộng, các index phụ phình to, có thể ăn lại phần dung lượng vừa tiết kiệm. Thứ hai, SQLite khuyến nghị chỉ dùng khi dòng đủ nhỏ để nằm gọn trong trang; dòng lớn bị tách ra trang tràn (overflow) làm mất lợi thế. Quy tắc thực dụng: WITHOUT ROWID cho bảng tra cứu khóa–giá trị với khóa chuỗi/ghép và ít index phụ; còn lại cứ để mặc định INTEGER PRIMARY KEY, kiểu khóa rẻ nhất mà SQLite có.
Vì sao điều này quan trọng
Lựa chọn khóa chính không phải chuyện thẩm mỹ — nó quyết định bạn trả một cây B-tree hay hai, và ta vừa đo chênh lệch đó là gấp đôi dung lượng trên nửa triệu dòng. INTEGER PRIMARY KEY là bí danh rowid gần như miễn phí; khóa không-integer kéo theo một index tự động; WITHOUT ROWID gộp dữ liệu và khóa làm một. Nhưng dung lượng mới là nửa câu chuyện — nửa còn lại là tốc độ truy vấn, và nó phụ thuộc vào việc SQLite có dùng được những cây index này hay không. Bài sau mở EXPLAIN QUERY PLAN ra: cho xem SQLite chọn quét toàn bảng hay tìm theo index, và đo chênh lệch thời gian thật giữa hai con đường.
