1. Mối quan hệ giữa Page và Row trong InnoDB

1.1. Page

• Page là đơn vị lưu trữ cơ bản trong InnoDB.
• Mỗi page có một kích thước cố định, và tất cả dữ liệu trong bảng sẽ được lưu trữ trong các page.
• Kích thước page mặc định của InnoDB là 16KB, nhưng bạn cũng có thể thay đổi kích thước này thành 4KB, 8KB, 32KB, hoặc 64KB nếu cần thiết.

1.2. Row

• Một table bao gồm nhiều row. Mỗi row sẽ được lưu trữ trong một page.
• Một page có thể chứa nhiều row.

Lưu ý:

• MySQL làm việc với page chứ không làm việc với row. VD khi anh em select dữ liệu, bản chất MySQL sẽ lấy các page chứa các row dữ liệu để xử lý.
• Với các kích thước page khác nhau (4KB, 8KB, 16KB,...), InnoDB sẽ quy định độ dài tối đa của từng row.
• Độ dài tối đa của một row không vượt quá 1/2 kích thước của một page. Ví dụ MySQL quy định:
  • Với page kích thước 16KB (mặc định), row dài nhất có thể lưu trữ sẽ là xấp xỉ 8KB.
  • Với page kích thước 64KB, row dài nhất sẽ là gần 16KB.

Tóm lại: page giống như một tờ giấy A4, còn row là những dòng dữ liệu trên tờ giấy A4 đó. Độ dài của row sẽ không vượt quá một nửa kích thước page để đảm bảo hiệu suất lưu trữ và truy xuất dữ liệu.

Ví dụ cụ thể

Giả sử bạn có một bảng với cấu trúc sau:

CREATE TABLE employee ( id INT, name VARCHAR(255), address VARCHAR(255), description TEXT
);

• Nếu page có kích thước 16KB, và row của bảng employee có dung lượng xấp xỉ 8KB, thì mỗi page có thể chứa được 2 rows.
• Nếu page có kích thước 64KB, một page có thể chứa khoảng 4 rows (tương ứng với mỗi row dài tối đa 16KB).

Việc hạn chế độ dài của row (tối đa 1/2 page) giúp tránh tình trạng lãng phí không gian và tăng hiệu quả truy xuất dữ liệu khi thực hiện các thao tác trên cơ sở dữ liệu.

2. Khi dữ liệu Row vượt quá dung lượng tối đa

Khi 1 row vượt quá dung lượng tối đa cho phép, các cột có định dạng VARCHAR, VARBINARY, BLOB hoặc TEXT (variable-length column) sẽ được chọn để lưu trữ bên ngoài page (off-page storage).

Có hai loại định dạng off-page storage: COMPACT/REDUNDANT và DYNAMIC/COMPRESSED

1. COMPACT/REDUNDANT

InnoDB sẽ lưu trữ một phần của dữ liệu của variable-length column trong page chính, phần còn lại sẽ được lưu trong các overflow pages (page tràn).

• Cụ thể:
  • InnoDB sẽ lưu 768 byte đầu tiên của cột ngay trong row, trên page chính (page chứa row gốc).
  • Nếu dữ liệu của cột dài hơn 768 byte, phần còn lại sẽ được lưu trữ trong các overflow pages. Ví dụ, nếu cột có 1500 byte, thì 768 byte đầu sẽ nằm trên page chính, và 732 byte còn lại sẽ được lưu trong overflow pages.
  • Phía trước 768 byte, được gắn thêm 20 byte để lưu trữ độ dài thực tế của cột và trỏ đến danh sách overflow pages. Con trỏ này giúp MySQL biết được vị trí của dữ liệu còn lại của cột.

Tóm tắt: Trong định dạng COMPACT và REDUNDANT, 768 byte đầu tiên của cột được lưu trực tiếp trong row trên page chính, và phần còn lại được lưu ngoài page. Thêm một con trỏ 20 byte để trỏ đến overflow pages.

2. DYNAMIC/COMPRESSED

Khi một cột có độ dài thay đổi cần lưu trữ ngoài page, InnoDB không lưu trữ 768 byte đầu tiên trên page chính như ở COMPACT/REDUNDANT. Thay vào đó, toàn bộ dữ liệu sẽ được chuyển sang các overflow pages ngay từ đầu.

• Cụ thể:
  • InnoDB sẽ lưu 20 byte đóng vai trò con trỏ ngay trong row, trên page chính. Con trỏ này sẽ trỏ đến overflow pages nơi lưu trữ toàn bộ dữ liệu của cột.
  • Phương pháp này giúp giảm không gian cần thiết trên page chính và tăng hiệu quả lưu trữ cho row.

Tóm tắt: Trong định dạng DYNAMIC và COMPRESSED, chỉ có con trỏ 20 byte được lưu trên page chính, và toàn bộ dữ liệu cột được lưu trong overflow pages. Điều này tiết kiệm không gian trên page chính.

Khi nào nên dùng loại nào?

• COMPACT / REDUNDANT: Tốt khi dữ liệu của các cột thường không quá lớn (dưới 768 byte), vì định dạng này lưu 768 byte đầu tiên trong page chính, giúp truy cập nhanh hơn.
• DYNAMIC / COMPRESSED: Phù hợp khi có nhiều cột lớn, vì toàn bộ dữ liệu được lưu ngoài page, giúp tiết kiệm không gian trên page chính, tăng khả năng chứa nhiều row hơn trong một page.

Tham khảo

1. Nguyên lý 3+2 hiệu quả cho mọi database - anh Trần Quốc Huy
2. https://dev.mysql.com/doc/refman/8.4/en/innodb-file-space.html