Vấn đề
Kế toán và quản lý cửa hàng ở 50 cửa hàng của TTMI chạy báo cáo trên khoảng thời gian dài: sổ cái, xuất nhập kho, công nợ phải thu và doanh số. Đây là những báo cáo rộng trên các bảng giao dịch lớn, và nhiều cái chạy chậm.
Khi xem kỹ các query, nguyên nhân chủ yếu nằm ở cấu trúc:
- Query do ORM sinh ra lấy nhiều cột và nhiều dòng hơn hẳn những gì báo cáo hiển thị.
- Một trang báo cáo cần cả số tổng lẫn một trang dữ liệu. Tính riêng từng phần nghĩa là chạy query lọc đắt đỏ nhiều hơn một lần mỗi request, cộng thêm một lần count.
- Với vài dạng query, planner của PostgreSQL ước lượng sai nặng số dòng một bước trả về và chọn chiến lược join chậm.
Tôi làm phần này cùng một đồng đội backend. Phần của tôi là refactor materialise-rồi-phân-trang và một số endpoint báo cáo viết bằng raw SQL.
Tôi đã làm gì
Giữ raw SQL trong một lớp riêng
Raw SQL nằm trong một query layer riêng, không bao giờ nằm trong view hay serializer. View gọi lớp đó với tham số đã parameterise. Nhờ vậy code nhạy về hiệu năng nằm một chỗ, test và review riêng được.
Cái giá là mất bớt sự tiện lợi của ORM. Một quy tắc phân lớp rõ ràng giúp chấp nhận được điều đó, và sau này cả team áp dụng nó làm quy ước.
Materialise một lần, rồi phân trang
Trong một transaction, endpoint chạy phần lọc và join đắt đỏ đúng một lần và ghi kết quả vào bảng tạm. Nó refresh thống kê của bảng để planner biết kích thước thật, tính mọi số tổng trong một lượt, đếm số dòng, rồi trả về trang được yêu cầu. Transaction kết thúc thì bảng tạm cũng biến mất.
Mỗi request giờ tốn thêm một lần ghi. Đổi lại, phần đắt chỉ chạy một lần, và tổng, số dòng, trang dữ liệu đều lấy từ cùng một snapshot nên luôn khớp nhau.
Chỉ lấy những gì báo cáo hiển thị
Mỗi query báo cáo chỉ project các cột nó hiển thị. Trên bảng rộng, việc này giảm lượng dữ liệu database phải đọc và gửi về API.
Một planner hint có phạm vi, dùng đúng một chỗ
Với một dạng query, ước lượng số dòng của planner vẫn sai nặng. Tôi đặt một planner setting chỉ có hiệu lực trong transaction đó để nó không lan sang query khác, và ghi lại lý do. Hint dễ gãy khi dữ liệu thay đổi, nên tôi giữ nó ở đúng một chỗ và viết lý do ngay cạnh.
Index build mà không chặn ghi
Ở những chỗ query plan cho thấy thực sự cần, chúng tôi thêm index, build concurrently để bảng lớn vẫn ghi được trong lúc build. Chúng tôi cũng viết migration sửa các index có định nghĩa đã lệch so với những gì code mong đợi.
Kết quả
Báo cáo giờ tính tổng, số dòng và trang được yêu cầu từ một lượt chạy nặng duy nhất thay vì lặp lại. Response chỉ mang những cột mỗi báo cáo cần. Việc giữ raw SQL trong query layer riêng thành quy tắc của team, giúp các lần tối ưu sau dễ review hơn.
Tôi không có số liệu độ trễ trước và sau có thể công bố cho các endpoint này, nên tôi mô tả kết quả bằng việc mỗi request giờ làm ít việc hơn ra sao.
Nếu làm lại
Phần lớn báo cáo chậm hóa ra là do làm phần việc đắt nhiều hơn một lần, còn thiếu index chỉ là phần nhỏ của câu chuyện. Nếu làm lại, tôi sẽ bắt đầu mỗi lần điều tra báo cáo bằng cách đọc query plan và ghi lại thời gian, để mỗi cải thiện đều có con số đi kèm. Tôi cũng sẽ thêm một bài kiểm tra regression về số query mỗi endpoint, để bắt được báo cáo nào lại quay về chạy query nặng hai lần.