Hệ thống Buffer Pool và Query Cache trong MySQL đóng vai trò quan trọng trong việc cải thiện hiệu suất cơ sở dữ liệu thông qua các cơ chế khác nhau:

1. Buffer Pool (Bộ đệm)

• Mục đích chính: Là khu vực bộ nhớ cache cho động cơ InnoDB, lưu trữ các trang dữ liệu nóng (bao gồm cả chỉ mục và hàng dữ liệu), giảm thiểu I/O đĩa để tăng hiệu suất.

• Cách ảnh hưởng đến hiệu suất:

• Tăng tốc đọc: 95% truy cập dữ liệu có thể trực tiếp từ bộ nhớ (trong lý tưởng, tỷ lệ trúng bộ đệm nên >99%).

• Tối ưu ghi: Sử dụng chiến lược "Write-Back", các trang thô được ghi vào đĩa bởi các luồng nền, kết hợp nhiều lần ghi lại.

• Cải tiến thuật toán LRU: Ngăn chặn việc quét toàn bộ bảng làm ô nhiễm dữ liệu nóng, phân chia chuỗi LRU thành các khu vực young/sublist.

• Tham số cấu hình quan trọng:

innodb_buffer_pool_size = 50%-80% của RAM vật lý
innodb_buffer_pool_instances = 8-16 (để tránh xung đột khóa đơn thực thể)

• Chỉ số giám sát:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Công thức tính tỷ lệ trúng:
-- 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

2. Query Cache (Bộ đệm Truy vấn)

• Nguyên lý hoạt động: Lưu trữ toàn bộ tập kết quả của câu lệnh SELECT, Key là giá trị băm của câu lệnh SQL, Value là dữ liệu nhị phân của tập kết quả.

• Hiệu ứng hai lưỡi dao về hiệu suất:

• Lợi ích: Trong các tình huống dữ liệu tĩnh, nâng cao hiệu suất truy vấn lặp đi lặp lại (TPS có thể tăng lên tới 10 lần).

• Nhược điểm:

• Các thao tác ghi khiến bộ đệm không còn trúng (bất kỳ câu lệnh DML nào cũng khiến bộ đệm liên quan mất hiệu lực).

• Xung đột khóa toàn cục (QCACHE_mutex) nghiêm trọng trong môi trường đa luồng.

• So sánh bộ nhớ cache yêu cầu so sánh từng byte của câu lệnh SQL (kể cả sự khác biệt về khoảng trắng).

• Lý do bị loại bỏ: MySQL 8.0 đã chính thức loại bỏ tính năng này, chủ yếu vì:

• SSD hiện đại giảm đáng kể độ trễ đọc ngẫu nhiên.

• Trong các tình huống OLTP, các thao tác ghi thường xuyên dẫn đến tỷ lệ trúng bộ nhớ thấp (tỷ lệ trúng thực tế thường <20%).

• Giải pháp bộ nhớ cache ở tầng ứng dụng tốt hơn trong kiến trúc phân tán.

3. Ma trận so sánh

4. Đề xuất tối ưu hóa hiện đại

• Tối ưu Buffer Pool
• Mở rộng dữ liệu: Khởi động lại sau đó tải chủ động các dữ liệu nóng

SELECT COUNT(*) FROM bang_du_lieu FORCE INDEX(PRIMARY);

• Giám sát điều chỉnh: Kiểm tra định kỳ INNODB_BUFFER_POOL_STATS.

• Sử dụng khu vực Keep-alive (tính năng nhánh Percona).

• Thay thế cho Query Cache

• Bộ nhớ cache ở tầng ứng dụng (Redis/Memcached).

• Bộ nhớ cache kết quả truy vấn của ProxySQL.

• Bộ nhớ cache kết nối khách hàng (như HikariCP).

• Sử dụng View vật lý.

• Tích hợp với phần cứng hiện đại

• Cấu hình bộ đệm PMEM-aware bằng bộ nhớ Optane.

• Cấu hình bộ đệm BP với tỷ lệ cao hơn trên NVMe SSD.

Bằng cách điều chỉnh Buffer Pool một cách hợp lý và thiết kế kiến trúc bộ nhớ hiện đại, bạn có thể đạt được thời gian phản hồi dưới mili giây mà vẫn duy trì khả năng mở rộng cao của hệ thống.