OLTP và OLAP: vì sao cùng một dữ liệu cần hai thiết kế

Ứng dụng bán hàng của GDev Mart ghi một đơn hàng chỉ trong 5 mili giây và chạy mượt cho 40 cửa hàng cùng lúc. Nhưng khi chị phụ trách phân tích muốn biết "doanh thu theo thành phố và theo nhóm sản phẩm trong quý 3", truy vấn chạy trên cùng cơ sở dữ liệu đó mất hơn 4 phút và còn làm chậm máy tính tiền ở cửa hàng. Cùng một dữ liệu, cùng một hệ quản trị, vậy vì sao một việc nhanh còn việc kia lại phá hệ thống?

Hãy dự đoán trước khi đọc tiếp: câu truy vấn của chị phân tích cần nối bao nhiêu bảng nếu cơ sở dữ liệu được thiết kế "chuẩn" cho ứng dụng bán hàng (các bảng orders, order_items, products, categories, customers)? Đếm số bảng và số phép nối, rồi suy nghĩ về việc nó thay đổi thế nào nếu bạn được thiết kế riêng cho việc phân tích.

Sau bài này, bạn có thể

• Phân biệt khối lượng công việc OLTP và OLAP theo số dòng mỗi thao tác, độ trễ mong muốn và kiểu thiết kế bảng.
• Đếm số bảng và phép nối của một truy vấn phân tích trên thiết kế chuẩn hóa, rồi so với star schema.
• Giải thích vì sao phải tách kho dữ liệu khỏi cơ sở dữ liệu ứng dụng và pipeline nằm ở giữa làm việc gì.

Cần biết trước: JOIN và GROUP BY cơ bản, cùng vòng đời dữ liệu trong bài "Vòng đời dữ liệu và kiến trúc nền tảng tổng thể"; không cần cài thêm công cụ.

Mô hình tư duy 1: hai loại khối lượng công việc đòi hỏi hai thiết kế

OLTP (Online Transaction Processing) phục vụ ứng dụng: rất nhiều thao tác nhỏ, mỗi thao tác đọc hoặc ghi vài dòng theo khóa. OLAP (Online Analytical Processing) phục vụ phân tích: ít truy vấn hơn nhưng mỗi truy vấn quét hàng triệu dòng và tổng hợp.

Cách đọc hình: ứng dụng chỉ nói chuyện với OLTP; analyst chỉ nói chuyện với kho dữ liệu. Giữa hai nơi là một pipeline sao chép và biến đổi dữ liệu, chính là việc của DE. Tách như vậy bảo vệ ứng dụng khỏi các truy vấn nặng và cho phép mỗi bên chọn thiết kế tối ưu cho công việc của mình.

Vì sao OLTP chuẩn hóa

Chuẩn hóa (normalization) là chia dữ liệu thành nhiều bảng sao cho mỗi sự thật chỉ lưu một lần. Ví dụ, tên sản phẩm chỉ nằm trong bảng products, còn order_items chỉ lưu product_id. Lợi ích là tránh bất thường khi cập nhật: đổi tên sản phẩm một chỗ, mọi đơn đều thấy tên mới; nếu mỗi dòng đơn hàng lưu sẵn tên sản phẩm, bạn phải sửa hàng triệu dòng và dễ sót.

Cách đọc hình: đây là thiết kế chuẩn hóa của GDev Mart với năm bảng. Giải đáp cho dự đoán đầu bài: để tính doanh thu theo thành phố (nằm ở customers) và theo nhóm sản phẩm (nằm ở categories), truy vấn phải đi từ orders sang order_items, rồi sang products và categories, đồng thời nối orders với customers: 5 bảng, 4 phép nối. Với bảng hàng trăm triệu dòng, mỗi phép nối là một chi phí đáng kể.

Vì sao OLAP phi chuẩn hóa

Phân tích hiếm khi sửa dữ liệu cũ nhưng liên tục đọc và nhóm. Vì vậy ta chấp nhận lặp lại một phần thông tin để giảm số phép nối: kho dữ liệu đặt các thuộc tính hay dùng để nhóm (thành phố, nhóm sản phẩm) ngay cạnh nhau trong các bảng "rộng". Với thiết kế star schema ở bài sau, cùng truy vấn chỉ cần 3 bảng, 2 phép nối (bảng fact nối với dimension khách hàng và dimension sản phẩm, trong đó nhóm sản phẩm là một cột của dimension); bộ lọc quý 3 thêm một phép nối tới dim_date, nhưng phép nối đó rẻ vì dim_date chỉ có vài nghìn dòng.

Đổi lại, dữ liệu lặp lại nghĩa là khi tên sản phẩm đổi, bạn phải xử lý ở pipeline chứ không còn tự động. Đó là sự đánh đổi có chủ đích: kho dữ liệu được nạp theo lô có kiểm soát nên bất thường cập nhật là việc DE quản lý, không phải rủi ro hằng ngày như ở ứng dụng.

Hai đại lượng giải thích sự chênh lệch hiệu năng

Một truy vấn phân tích chỉ cần vài cột trong bảng có 20 cột. Nếu dữ liệu lưu theo dòng (row-oriented, như PostgreSQL), để đọc 3 cột bạn vẫn phải đọc cả dòng 20 cột. Nếu lưu theo cột (column-oriented, như ClickHouse, Parquet), bạn chỉ đọc đúng 3 cột, tức khoảng 15% dung lượng, và dữ liệu cùng cột nén tốt hơn. Phần này được đào sâu ở tuần 5.

Những hiểu lầm thường gặp

• "Phi chuẩn hóa là thiết kế tồi." Với OLTP thì đúng là rủi ro; với OLAP đó là lựa chọn có chủ đích để giảm nối bảng.
• "OLAP chỉ là OLTP có nhiều dữ liệu hơn." Khác về khối lượng công việc: đọc nhiều quét lớn so với ghi nhiều thao tác nhỏ, nên lưu trữ và thiết kế khác nhau.
• "Cho analyst truy vấn thẳng vào OLTP cho nhanh." Truy vấn nặng cạnh tranh tài nguyên với ứng dụng; một phân tích 4 phút có thể làm chậm máy tính tiền.

Tự kiểm tra (có đáp án)

Câu 1. Vì sao tên sản phẩm chỉ nên lưu trong products ở thiết kế OLTP? Đáp án: để tránh bất thường cập nhật: đổi tên một chỗ là mọi đơn hàng thấy tên mới, không phải sửa hàng triệu dòng và không thể sót.

Câu 2. Một truy vấn đọc 3 trong 20 cột của bảng 100 triệu dòng. Lưu theo cột thay vì theo dòng giúp gì? Đáp án: chỉ đọc 3 cột (khoảng 15% dữ liệu) thay vì cả dòng, và dữ liệu cùng cột nén tốt hơn nên đọc ít byte hơn.

Câu 3. Pipeline giữa OLTP và kho dữ liệu cần làm những việc gì? Đáp án: sao chép dữ liệu (theo lô hoặc CDC), làm sạch, chuyển sang mô hình phi chuẩn hóa như star schema, và đảm bảo kết quả có thể chạy lại mà không trùng.

Nếu chỉ nhớ 3 điều

1. OLTP phục vụ nhiều thao tác nhỏ nên chuẩn hóa; OLAP phục vụ ít truy vấn quét lớn nên phi chuẩn hóa.
2. Truy vấn phân tích trên thiết kế chuẩn hóa cần nhiều phép nối (ở ví dụ là 4); star schema giảm còn 2.
3. Tách OLTP khỏi kho dữ liệu bảo vệ ứng dụng, và pipeline giữa hai bên là việc của Data Engineer.