Google BigQuery - Bài học 1: OLTP vs OLAP – Tại sao MySQL/PostgreSQL "khóc thét" khi chạy báo cáo hàng trăm triệu dòng?

Chào anh em! Nếu bạn là một Backend Developer đã quen ăn ngủ cùng MySQL hay PostgreSQL, chắc hẳn bạn đã từng ít nhất một lần trải qua cảm giác "tim đập chân run" khi sếp hoặc đội vận hành yêu cầu: "Xuất cho anh báo cáo tổng doanh thu theo từng danh mục sản phẩm, phân tách theo khu vực giao hàng của 3 năm gần nhất nhé!"

Bạn tự tin gõ một câu lệnh SELECT ... JOIN ... GROUP BY cực kỳ chuẩn sách giáo khoa. Nhưng khi bấm chạy trên bảng orders có khoảng 200 triệu dòng, điều gì xảy ra?

• Câu lệnh quay đều suốt 15 phút chưa xong.

• CPU của Database Server vọt lên 100%.

• Hàng loạt API đặt hàng, thanh toán của khách hàng ở phía ứng dụng chính bị Timeout hoặc lỗi Connection Pool Exhausted.

Tại sao một hệ quản trị cơ sở dữ liệu mạnh mẽ, được đánh Index kỹ càng như PostgreSQL hay MySQL lại "ngã gục" trước một câu truy vấn thống kê tưởng chừng đơn giản như vậy? Câu trả lời nằm ở sự khác biệt cốt lõi giữa hai thế giới: OLTP và OLAP.

1. Hai thế giới đối lập: OLTP và OLAP là gì?

Trong kỹ thuật dữ liệu, không có một loại Database nào sinh ra để giải quyết tốt mọi bài toán. Người ta chia cơ sở dữ liệu thành hai trường phái chính dựa trên mục đích sử dụng:

OLTP (Online Transaction Processing) – Xử lý giao dịch trực tuyến

Đây chính là MySQL, PostgreSQL, SQL Server, Oracle mà anh em Backend vẫn dùng hàng ngày.

• Nhiệm vụ chính: Xử lý hàng ngàn giao dịch nhỏ lẻ, diễn ra liên tục ở thời gian thực (Real-time). Ví dụ: Khách bấm mua hàng $\rightarrow$ Tạo đơn hàng (INSERT), trừ tồn kho (UPDATE), kiểm tra thông tin tài khoản của 1 user (SELECT ... WHERE user_id = 123).

• Đặc điểm truy vấn: Đọc/Ghi một lượng bản ghi rất nhỏ (thường chỉ 1 hoặc vài dòng), nhưng tần suất cực cao và đòi hỏi độ trễ tính bằng mili-giây (ms) cùng tính toàn vẹn dữ liệu tuyệt đối (ACID).

OLAP (Online Analytical Processing) – Xử lý phân tích trực tuyến

Đây là sân nhà của các Cloud Data Warehouse như Google BigQuery, Snowflake, Amazon Redshift, ClickHouse.

• Nhiệm vụ chính: Tổng hợp, phân tích xu hướng, chạy báo cáo Business Intelligence (BI) hoặc làm đầu vào cho Machine Learning trên tập dữ liệu lịch sử khổng lồ (từ hàng trăm Gigabyte đến hàng Petabyte).

• Đặc điểm truy vấn: Ít khi cập nhật từng dòng nhỏ lẻ, nhưng mỗi lần chạy câu lệnh là quét qua hàng triệu đến hàng tỷ dòng dữ liệu để tính SUM(), COUNT(), AVG().

2. Giải phẫu kỹ thuật: Tại sao MySQL/PostgreSQL lại "khóc thét" khi làm việc của OLAP?

Nhiều bạn sẽ thắc mắc: "Tôi đã đánh B-Tree Index cho cột created_at và status rồi mà, sao vẫn chậm?"

Hãy nhìn sâu xuống tầng vật lý (Storage Engine) như InnoDB của MySQL để thấy 3 nút thắt cổ chai chí mạng:

Nút thắt 1: Cơ chế lưu trữ theo dòng (Row-oriented Storage)

Giả sử bảng orders của bạn có 30 cột (từ id, user_id, shipping_address, note, device_info... cho đến total_amount) và nặng 500 GB.

Khi bạn viết câu lệnh tính tổng doanh thu:

SQL

SELECT status, SUM(total_amount) 
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY status;

Thực chất bạn chỉ cần dữ liệu của đúng 3 cột: status, total_amount, và created_at.

Tuy nhiên, MySQL/PostgreSQL lưu trữ dữ liệu trên ổ đĩa nối tiếp nhau theo từng dòng (Row-by-row) внутри các Data Page (thường có kích thước 16KB hoặc 8KB). Để lấy được con số total_amount của dòng thứ nhất, ổ cứng và RAM bắt buộc phải đọc toàn bộ 30 cột của dòng đó lên bộ nhớ (Buffer Pool).

[Cách MySQL/PostgreSQL lưu trên đĩa - Row-oriented]
Row 1: | id | user_id | address (dài) | note (dài) | status | total_amount | ...
Row 2: | id | user_id | address (dài) | note (dài) | status | total_amount | ...
--> Muốn lấy "total_amount", máy chủ phải đọc xuyên qua toàn bộ "address", "note"...

Hệ quả là: Chỉ cần lấy 2-3 cột nhẹ nhàng, nhưng Database phải thực hiện Disk I/O để kéo gần như toàn bộ 500 GB dữ liệu rác (27 cột không dùng đến) từ ổ cứng lên RAM. Bộ nhớ đệm (Buffer Pool) lập tức bị tràn, đẩy các dữ liệu nóng của khách hàng đang giao dịch ra ngoài!

Nút thắt 2: Khi B-Tree Index trở nên... vô dụng (thậm chí phản tác dụng)

B-Tree Index là "thần dược" khi bạn tìm kiếm một vài dòng cụ thể (Point Lookup) – ví dụ tìm đơn hàng có id = 99999 giữa 100 triệu dòng.

Nhưng với các câu lệnh báo cáo quét diện rộng (ví dụ lấy dữ liệu của cả năm 2024 và 2025, chiếm 60% tổng số dòng của bảng):

• Nếu dùng Index, Database phải nhảy cóc qua lại giữa cây Index và các Data Page gốc trên ổ đĩa hàng chục triệu lần (Random I/O).

• Chi phí đọc ngẫu nhiên (Random I/O) này còn đắt đỏ và chậm hơn cả việc quét tuần tự toàn bộ bảng (Full Table Scan - Sequential I/O). Đó là lý do Query Optimizer của MySQL/PostgreSQL thường tự động bỏ qua Index và chọn Seq Scan khi thấy phạm vi dữ liệu quá lớn, dẫn đến treo hệ thống.

Nút thắt 3: Kiến trúc Đơn khối (Monolithic) & Tranh chấp tài nguyên

MySQL hay PostgreSQL truyền thống chạy trên một máy chủ vật lý (hoặc 1 VM cố định số nhân CPU và RAM). Tài nguyên tính toán (Compute) và lưu trữ (Storage) gắn chặt với nhau.

Khi một câu truy vấn báo cáo ngốn hết 100% CPU và băng thông đọc ổ đĩa, các luồng xử lý giao dịch mua hàng (Write/Lock) sẽ phải xếp hàng chờ đợi.

3. Google BigQuery bước vào cuộc chơi như thế nào?

Google BigQuery không phải là một Database để thay thế MySQL/PostgreSQL trong việc lưu giỏ hàng hay trừ tiền tài khoản. Nó là một Serverless Cloud Data Warehouse (OLAP) được thiết kế từ đầu để đập tan 3 nút thắt trên:

1. Lưu trữ dạng cột (Columnar Storage): BigQuery cắt bảng của bạn ra và lưu theo từng cột riêng biệt. Câu lệnh SELECT status, SUM(total_amount) ở trên sẽ chỉ đọc đúng file chứa cột status và cột total_amount. 28 cột còn lại hoàn toàn không được chạm tới! Thay vì đọc 500 GB, BigQuery chỉ cần đọc khoảng 5 GB (nhờ cơ chế nén dữ liệu theo cột cực kỳ hiệu quả).

2. Xử lý phân tán quy mô lớn (MPP - Massively Parallel Processing): Thay vì dùng 1 máy chủ có 16 hay 32 CPU gồng mình tính toán, khi bạn bấm chạy query trên BigQuery, hệ thống tự động huy động hàng ngàn nhân CPU (được gọi là Slots) từ trung tâm dữ liệu của Google cùng nhảy vào chia nhỏ 200 triệu dòng đó ra để cộng cùng một lúc, sau đó trả kết quả về chỉ trong 2 - 3 giây.

3. Không cần quản trị hạ tầng (Serverless) & Không cần đánh Index: Bạn không cần khởi tạo VM, không cần cấu hình RAM, và đặc biệt: Trong BigQuery không có khái niệm CREATE INDEX truyền thống như B-Tree mà nó vẫn nhanh khủng khiếp!

4. Bảng so sánh tóm tắt cho anh em Kỹ sư

Tổng kết Bài học 1

Đừng bắt một chiếc xe đua F1 (MySQL/PostgreSQL - cực nhanh ở đường bằng, linh hoạt ở các khúc cua nhỏ) phải đi chở hàng trăm tấn quặng (chạy báo cáo dữ liệu lớn). Trong kiến trúc hệ thống hiện đại, chúng ta dùng MySQL/PostgreSQL ở tuyến đầu để phục vụ người dùng cuối, sau đó đồng bộ dữ liệu sang Google BigQuery ở tuyến sau để tha hồ mổ xẻ, phân tích hàng tỷ dòng mà không làm ảnh hưởng dù chỉ 1% CPU của hệ thống Production.

Vậy làm thế nào mà BigQuery có thể huy động hàng ngàn CPU trong chớp mắt và lưu trữ hàng Petabyte dữ liệu mà không cần bạn cấu hình bất kỳ con Server nào? Hẹn gặp lại anh em ở Bài học 2: Giải phẫu kiến trúc BigQuery – Tách biệt hoàn toàn Compute (Dremel) và Storage (Colossus)!