1. Disk, page và buffer pool

Gần như mọi quy tắc tối ưu MySQL quy về hai câu hỏi: phải đọc bao nhiêu page, và các page đó đã nằm sẵn trong RAM chưa. Chương này giải thích những khái niệm đó trước khi đi vào kiểu dữ liệu, index hay query.

RAM nhanh hơn disk bao nhiêu

Database lưu dữ liệu trên disk để không mất khi tắt máy, nhưng chỉ xử lý được dữ liệu khi nó đã nằm trong RAM. Khoảng cách tốc độ giữa hai nơi rất lớn:

Thang logarit: mỗi vạch chia gấp 10 lần vạch trước. Cột phải quy đổi theo tỷ lệ “nếu đọc RAM mất 1 giây”. Con số là độ lớn điển hình, tuỳ phần cứng.

Hai hệ quả xuyên suốt các chương sau:

• Giữ phần dữ liệu hay dùng trong RAM là mục tiêu số một. Một query đọc 100 page có sẵn trong RAM vẫn nhanh hơn query đọc 5 page phải lấy từ disk.
• Đọc tuần tự rẻ hơn đọc ngẫu nhiên. Các page nằm liền nhau được đọc trước theo lô (read-ahead), ít lần gọi I/O hơn. Trên HDD chênh lệch tới hàng trăm lần; SSD thu hẹp khoảng cách nhưng không xoá được. Đây là lý do optimizer đôi khi chọn quét cả bảng thay vì nhảy qua index.

Page: đơn vị đọc ghi của InnoDB

InnoDB không đọc từng dòng từ disk. Nó chia mọi dữ liệu (bảng, index, undo) thành các khối cố định 16KB gọi là page, và luôn đọc, ghi nguyên page. Giống một kho hàng: muốn lấy một món thì phải bê cả thùng chứa nó ra.

Muốn đọc một dòng 200 byte cũng phải đọc cả page 16KB chứa nó. Nếu 70 dòng cần đọc nằm chung một page thì chỉ tốn 1 lần đọc; nếu chúng nằm rải rác ở 70 page thì tốn 70 lần. Vì vậy cách dữ liệu được xếp vào page quan trọng ngang với lượng dữ liệu.

Một data page. Các dòng trong page nối theo thứ tự khoá; Page Directory giữ vài con trỏ rải đều để tìm nhị phân thay vì duyệt từng dòng. Con trỏ page trước/sau trong File Header nối các page cùng tầng thành danh sách.

Từ file tới dòng: mỗi bảng (khi innodb_file_per_table=ON, mặc định) là một file .ibd gọi là tablespace → chia thành các extent 1MB, mỗi extent gồm 64 page liền nhau → mỗi page 16KB chứa nhiều dòng. Cấp phát theo extent giúp các page của cùng một index nằm gần nhau trên disk, để đọc tuần tự được.

Một dòng phải nằm vừa khoảng nửa page (gần 8KB). Cột VARCHAR, TEXT, BLOB dài hơn sẽ được đẩy sang page tràn (overflow page) riêng, trong dòng chỉ còn con trỏ 20 byte.

Từ page tới B+Tree

Các page chứa dòng (page lá) được xếp theo khoá chính và nối với nhau. Để không phải duyệt hết các page lá khi tìm một dòng, InnoDB dựng thêm các page “mục lục” ở tầng trên: mỗi entry gồm một giá trị khoá và số hiệu của page con. Tầng trên nữa làm mục lục cho tầng mục lục, cho tới khi chỉ còn một page gốc (root). Cấu trúc đó là B+Tree, nội dung chính của chương Index.

Mỗi page mục lục chứa được khoảng 900–1000 entry với khoá BIGINT, nên mỗi tầng nhân số page lên chừng ấy lần:

Thử: B+Tree cao bao nhiêu tầng

Số dòng: 100 triệu

Kích thước một dòng: 200 byte

Kiểu khoá chínhINT (4 byte)BIGINT (8 byte)BINARY(16) (16 byte)CHAR(36) (36 byte)

Giả định: page dùng được 15/16 (15.360 byte); mỗi dòng thêm 18 byte (header, trx_id, roll_ptr); mỗi entry mục lục = khoá + 4 byte số page + 5 byte header.

70dòng / page lá

903entry / page mục lục

4tầng

Tầng	Số page	Dung lượng
Root	1	16,4 KB
Mục lục 1	2	32,8 KB
Mục lục 2	1.583	25,9 MB
Lá (chứa dòng)	1.428.572	23,4 GB

Tra một dòng theo khoá chính: đọc 4 page, mỗi tầng một page. Các tầng phía trên lá chỉ có tổng 1.586 page (26,0 MB) nên gần như luôn nằm sẵn trong buffer pool; thường chỉ page lá có thể phải đọc từ disk.

Khi page đầy: page split

• Insert tăng dần (AUTO_INCREMENT): dòng mới luôn vào page cuối. InnoDB lấp page tới khoảng 15/16 rồi mở page mới phía sau. Page gần như đầy, nằm liền nhau.
• Insert ngẫu nhiên (UUIDv4, email): dòng mới phải vào giữa một page đã đầy. InnoDB tách page đó thành hai, mỗi bên khoảng một nửa, rồi mới chèn. Lâu dần page trung bình chỉ lấp khoảng 69%, bảng phình ra, các page kề nhau theo khoá lại nằm xa nhau trên disk.
• Xoá dòng để lại chỗ trống. Page vơi dưới MERGE_THRESHOLD (mặc định 50%) có thể được gộp với page bên cạnh. OPTIMIZE TABLE dựng lại bảng để nén chặt.

Buffer pool: bộ nhớ đệm của InnoDB

Buffer pool là vùng RAM lớn nhất của MySQL, giữ bản sao của các page đã đọc từ disk. Mọi lệnh đọc và ghi đều đi qua đây, không lệnh nào làm việc thẳng với file:

• Đọc: page có sẵn trong buffer pool (hit) thì dùng ngay. Không có (miss) thì đọc từ disk vào một ô trống; hết ô trống thì đuổi page ít dùng nhất ra.
• Ghi: sửa page ngay trong buffer pool. Page đó thành dirty page (khác với bản trên disk) và được ghi xuống disk sau, theo lô. Thay đổi không bị mất khi sập máy nhờ redo log (chương InnoDB).
• Kích thước: innodb_buffer_pool_size, thường đặt 50–75% RAM trên máy chỉ chạy MySQL. Giá trị mặc định chỉ 128MB, luôn phải chỉnh khi lên production.
• Đo hit rate: 1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests. Hệ thống khoẻ thường trên 99%. Tỷ lệ tụt nghĩa là working set đã vượt quá RAM.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- read_requests: số lần cần một page (logical read)
-- reads:         số lần phải đọc từ disk (miss)

Ai bị đuổi khỏi buffer pool

LRU thông thường: page vừa dùng lên đầu danh sách, page ở cuối bị đuổi khi cần chỗ. Vấn đề: một lần quét bảng lớn (báo cáo, mysqldump) đọc hàng nghìn page chỉ dùng một lần, đẩy sạch các page nóng mà mọi request khác đang cần.

InnoDB chia danh sách thành hai vùng: young (5/8, page nóng) và old (3/8). Page mới đọc vào được đặt ở đầu vùng old, không phải đầu danh sách. Nó chỉ được lên vùng young nếu được dùng lại sau ít nhất innodb_old_blocks_time (1 giây). Page của một lần quét chỉ dùng trong chớp mắt, nên trôi ra khỏi vùng old mà không chạm tới page nóng.

Thử: đọc page nóng để làm ấm, quét bảng, rồi đọc lại page nóng. So sánh hai chế độ.

Vùng young (page nóng)

Vùng old (page mới đọc vào đây trước)

page nóng (H1–H5)page của lần quétô trống

—hit rate từ đầu

0 / 0hit / miss

Buffer pool đang trống. Bấm “Đọc page nóng ×10”.

Nối vào các chương sau

Nền tảng	Hệ quả	Chương
Đọc ghi theo page, RAM có hạn	Kiểu dữ liệu nhỏ → nhiều dòng mỗi page → ít page, dễ nằm vừa buffer pool	Kiểu dữ liệu
B+Tree 3–4 tầng, tầng trên luôn trong RAM	Tra một dòng theo khoá tốn tối đa khoảng 1 lần đọc disk	Index
Đọc ngẫu nhiên đắt hơn tuần tự	Đi qua secondary index cho quá nhiều dòng thua quét cả bảng	Tối ưu query
Page split khi insert ngẫu nhiên	Khoá chính nên tăng dần	Kiểu dữ liệu
Dirty page ghi xuống disk sau	Commit chỉ cần ghi tuần tự redo log; cần doublewrite chống page ghi dở	InnoDB

Ghi nhớ

1. RAM nhanh hơn SSD cỡ hàng trăm lần, HDD cỡ chục nghìn lần; đọc tuần tự rẻ hơn đọc ngẫu nhiên.
2. InnoDB đọc ghi theo page 16KB; số page phải đọc là thước đo chi phí.
3. Buffer pool cache page trong RAM; đặt 50–75% RAM, theo dõi hit rate.
4. LRU chia young/old để một lần quét bảng không đẩy hết page nóng.