[Phần 1] Tối ưu MySql query dùng tư tưởng của Data Engineers

Phần 1: nói về việc làm sao có thể query dữ liệu làm báo cáo với các bảng hàng tỉ records trong vài milli giây bằng ...mysql.

Phần 2: Các hạn chế và giải pháp xử lý các hạn chế.

Đã bao giờ trên đường đời tấp nập

Bạn va phải slow mysql query
- thơ ai đó giấu tên

Ở một công ty bán sách giả tưởng nọ. Có 1 dev mới vào làm được giao làm báo cáo dựa trên DB công ty.

Trông cũng đơn giản, 1 tác giả có thể có nhiều tác phẩm và 1 tác phẩm có thể được đồng sáng tạo bởi nhiều tác giả. Công ty giả tưởng nên cũng mạnh dạn giả tưởng giá sách không bao giờ thay đổi và nằm trong bảng book luôn cho nó tiện.

Dữ liệu cũng không có gì nhiều. Tầm nửa triệu đơn, 10 000 tác giả và 43 000 đầu sách

book.2023 <-- file dump DB không lại bảo tôi nói phét

Cậu háo hức vào query DB, vừa làm vừa nghĩ

Ui đơn giản, join tí là xong, học ở trường còn phức tạp hơn nhiều
- cậu dev

Mất 2.5 giây ra báo cáo, chuyện quá là nhỏ luôn. 1 thời gian sau cậu quên bẵng đi cho tới khi:

Sao dạo này báo cáo chạy chán thế nhở
- đồng nghiệp than

Cậu ngó qua thì hóa ra phần cậu làm rất nhỏ trong hàng chục báo cáo khác công ty đang dùng. Mà các phòng ban lại có các loại báo cáo khác nữa.

Cậu nghĩ hay là mình dùng dạng DB khác làm báo cáo cho nó chuẩn chỉ. Đọc đủ các thể loại DB chuyên dùng cho báo cáo, demo dựng, fix bug, tối ưu performance. Nhưng tự nhiên sau cả tháng đánh vật chưa đâu vào đâu, cậu chột dạ khi thấy các bảng biểu của mình bị chậm còn chart từ từ ông anh dev đẹp trai tên Hào giấu tên, vốn vài năm làm Data Engineer làm sao nó toàn có mấy milli giây, lại query đơn giản, cũng chỉ dùng MySql như cậu.

Cậu quyết tâm đọc code xem ông anh làm gì thì thấy lão query từ các bảng toàn có đuôi _olap. Cậu google tìm hiểu xem OLAP là gì:

Cơ sở dữ liệu Xử lý Phân tích Trực tuyến (OLAP) tạo điều kiện cho truy vấn nghiệp vụ thông minh. OLAP là một công nghệ cơ sở dữ liệu đã được tối ưu hóa cho truy vấn và báo cáo, thay vì xử lý các giao dịch. Dữ liệu nguồn cho OLAP là cơ sở dữ liệu Xử lý Giao dịch Trực tuyến (OLTP) vốn thường được lưu trữ trong các kho dữ liệu. Dữ liệu OLAP lấy từ dữ liệu lịch sử này và được tổng hợp thành các cấu trúc cho phép phân tích tinh vi. Dữ liệu OLAP cũng được sắp xếp theo cấu trúc phân cấp và lưu trữ trong cube thay vì bảng. Đây là một công nghệ tinh vi sử dụng cấu trúc đa chiều để cung cấp quyền truy nhập nhanh vào dữ liệu phục vụ cho việc phân tích.
- https://support.microsoft.com/vi-vn/office/t%E1%BB%95ng-quan-v%E1%BB%81-x%E1%BB%AD-l%C3%BD-ph%C3%A2n-t%C3%ADch-tr%E1%BB%B1c-tuy%E1%BA%BFn-olap-15d2cdde-f70b-4277-b009-ed732b75fdd6

Nôm na lại là ông anh đó đang dùng dữ liệu kiểu dựng cho riêng báo cáo đó luôn, cache sẵn trong 1 thứ gọi là OLAP cube.

Cậu search trong source code PHP xem ổng làm sao đẻ ra được đám olap cube vì cả 2 cùng chỉ làm việc với repo này của công ty thôi. Cậu ngạc nhiên khi không hề có chỗ nào bảng này được ghi, chỉ có đọc để ra dữ liệu. Cậu thử thay đổi chút dữ liệu trên môi trường dev thì thấy bảng biểu vẫn update theo

Má ơi lão làm kiểu gì mà hay vậy
- cậu dev nghĩ

Cơ hội cuối cùng cũng tới khi người anh tên Hào cuối năm rảnh việc, cậu rủ lão ra trà đá trước cổng công ty ở số 1B Yết Kiêu - Hoàn Kiếm - Hà Nội rồi hỏi vài ba câu bâng quơ

Em thấy anh vừa đẹp trai vừa query nhanh, anh có thể cho người em này được đẹp trai như anh không ?
- cậu dev nói

người anh tên Hào cười khẩy rất đểu và hỏi

Em đã tối ưu bằng những cách gì rồi ?
- anh Hào đẹp trai

Em đánh index này, tách read/ write này, partition by date như trên mạng họ nói này
- cậu dev nói

Vẫn chậm đúng không ? Hãy quay lại bản chất vấn đề

Đánh index là đúng nhưng Index không làm việc với các agg như SUM/ COUNT/ MIN/ MAX/ AVG. Và khi join e không thể đánh index cho các trường ở các bảng khác nhau.

Tách read/ write là đúng nên query của em mới hết có 2.x giây.

Partition by date không có nhiều tác dụng khi e muốn select all.

- anh Hào đẹp trai

Bấm xong điện thoại mua vé số, lão dùng quyền lực ép cậu trả tiền nước rồi rủ cậu dev lên máy tính ở công ty hướng dẫn. Hóa ra cậu tra code sai chỗ. Lão vẽ trên bảng đen 1 sơ đồ đơn giản như sau

Giản đồ

Đáng ra chỗ cậu nên tra là thư mục Trigger của bảng sale và thư mục Procedures.

Nôm na là mỗi lần có thêm/ sửa/ xóa gì về dữ liệu thì DB sẽ trigger như sau

Các trigger gọi lại procedure proc_top_revenue_olap sửa bảng top_revenue_olap như sau.

Lúc này query dài ngoằng lúc đầu của cậu chỉ còn rất ngắn gọn và chạy hết 16ms. Nhanh hơn ...150 lần. Cách này sẽ đảm bảo dù cậu có hàng tỉ records trong bảng sale đi nữa thì vẫn chỉ chạy mất từng đó thời gian.