5.5K 265 25

Đã đăng vào thg 5 4, 2023 3:41 CH 10 phút đọc

3.4K

Phân tích, xử lý bài toán thực tế trên bảng SQL với hơn 5 triệu hàng

Editors' Choice Mayfest2023

Chào mọi người,

Trước khi đọc bài viết này, các bạn vui lòng đọc bài viết dưới trước để đảm bảo có thể hiểu bài này dễ dàng hơn nhé:

Cách hiển thị hiệu quả các bảng dữ liệu lớn: Tối ưu hóa hiệu suất từ 12 phút đến 300 mili giây

Ở bài viết trước, chúng ta đã đạt được mục tiêu của bài toán là giảm thời gian query từ 12 phút về 300 mili giây. Ơ thế bài viết hôm nay là để làm gì nhỉ?

Ở bài hôm nay, chúng ta sẽ cùng nhau xét thêm 1 bài toán mới và phân tích nhiều hơn về:

Dữ liệu được lưu ở bảng event_data
Ngữ cảnh, mục đích sử dụng
Tần suất lưu, sử dụng dữ liệu

Từ những yếu tố trên, chúng ta sẽ đưa ra thêm giải pháp để tối ưu hơn nữa cho bài toán nhé.

1. Bài toán mới

Dựa vào bảng event_data, thực hiện tính toán điểm số và cập nhật bảng xếp hạng cho toàn bộ người dùng hệ thống trong ngày. Công việc tính toán được thực hiện 1 giờ / lần.

Do 1 số lý do không tiện nói ở đây, giả sử chúng ta có 1 danh sách N người dùng, hệ thống sẽ bắt buộc phải gửi N request lên API, với mỗi request sẽ dùng để tính toán số điểm của người dùng đó trong ngày và lưu vào 1 DB khác.

2. Khởi đầu

Lúc này, với mỗi lần gọi API, câu truy vấn của chúng ta sẽ như thế này:

SELECT * FROM public.event_data WHERE user_id=XXX

Trong đó, XXX là ID của user chúng ta cần lấy kết quả. Mình chọn 1 userID chứa nhiều dữ liệu nhất thì sẽ có kết quả như này:

Total rows: 330220
Query complete 00:00:54.058

Như vậy, ta mất khoảng 54s để hoàn thành câu truy vấn trên

Okay, hệ thống hiện tại có 225 người dùng, trong trường hợp xấu nhất, mỗi người dùng đều có số dữ liệu như trên (mà thực tế thì nó vậy luôn), và mỗi lần call API, ta chỉ được xử lý 1 người dùng, tổng thời gian thực hiện là:

TOTAL_TIME = 54(s)*225 = 12150(s) = 3 giờ 22 phút 30 giây

Thôi thì các bạn hiểu rồi đúng không nào. Hơn 3 giờ để hoàn thành, mà yêu cầu bài toán là tính toán 1 giờ / 1 lần. Rồi làm sao tính? Đấy là chúng ta đã bỏ qua thời gian tính toán số điểm từ dữ liệu trên nhé. À thì nó có chạy được đâu. Dữ liệu xếp hạng không bao giờ đúng 😄

3. Truy vấn theo ngày, giờ

Như yêu cầu bài toán đề cập, chúng ta sẽ tính toán bảng xếp hạng trong ngày. Hừm! Các bạn đã thấy có gì đó sai sai với câu query ở trên chưa nhỉ?

Tại sao ta phải lấy hết toàn bộ dữ liệu của 1 người dùng (bao gồm nhiều ngày) trong khi ta chỉ cần tính bảng xếp hạng trong ngày hôm nay?

Ok, bây giờ hãy thử cập nhật câu truy vấn nhé. Giả sử ngày hiện tại là 04/05/2023:

SELECT * FROM public.event_data 
WHERE user_id='8ec35c4e-9c00-40ef-81d9-d919b65f1696' 
AND datetime > '2023-05-04 00:00:00'

Và đây là thống kê:

Total rows: 6230
Query complete 00:00:00.901

Oh khá hơn nhiều rồi đấy. Giờ còn chưa tới 1 giây. Lúc này, tổng thời gian thực hiện cho 225 người dùng sẽ rơi vào khoảng:

TOTAL_TIME = 0.91(s)*225 = 203(s) = 3 phút 23 giây

Con số này là chấp nhận được rồi các bạn nhỉ. Tuy nhiên, giả sử 1 thời gian nữa, số lượng người dùng tăng lên gấp 5 lần, khoảng 1125 người, thì thời gian ước tính cũng tăng lên hơn 15 phút. Như vậy vẫn chưa ổn lắm. Tất nhiên là người dùng tăng thì thời gian sẽ tiếp tục tăng lên như vậy.

4. Lọc người dùng

Sau 1 hồi suy nghĩ, ta lại phải đọc lại bài toán lần nữa. Hừm, việc tính toán sẽ được thực hiện lại mỗi 1 giờ. Nhưng đâu có nghĩa, trong vòng 1 giờ đó, toàn bộ tất cả người dùng đều thực hiện thay đổi dữ liệu. Và thế là phải theo dõi và phân tích thôi.

Và sau hơn 9981 ngày phân tích (chém gió thế thôi 😂, nhưng cũng nhiều ngày đấy), mình nhận thấy rằng cứ mỗi 1 giờ, số lượng người dùng thật sự thay đổi dữ liệu nó chiếm chưa đến 20% số người dùng tổng, tức là chỉ khoảng 45/225 người dùng. Tín hiệu tốt đấy. Vậy ý tưởng tiếp theo là gì?

Không gì khác, đó chính là chúng ta chỉ thực hiện lại việc tính toán cho những người dùng có sự thay đổi dữ liệu kể từ lần tính toán cuối cùng. Việc này đơn giản là chúng ta chỉ cần gắn 1 cờ là true khi xác định người dùng có cập nhật dữ liệu mới. Sau khi tính toán xong, ta lại set cờ về false. Như vậy, về bản chất, thời gian để truy vấn cho mỗi người dùng là không thay đổi, nhưng tổng thời gian lúc này chỉ còn khoảng 20% so với trước đó

TOTAL_TIME = 203(s) * 20(%) = 40,6(s)

Lúc này, giả sử thời gian chúng ta chấp nhận cho hệ thống chạy việc tính toán tối đa là 20 phút. Thì số người dùng có thể đáp ứng được sẽ vào khoảng:

TOTAL_USERS = 2060/40,6225 = 6650 người

Và cũng bằng cách theo dõi, phân tích chiến lược,… thì để số người dùng tăng đến 6650 người thì cũng vài năm đấy. Thế nên là cách này tạm thời vẫn ổn rồi.

5. Caching

Tuy nhiên, mình vẫn muốn mọi thứ nó được tối ưu thêm nữa. Thế là sau bữa ăn tối vỏn vẹn 10 phút, lại lao vào suy nghĩ tiếp.

Giờ mình sẽ phân tích dữ liệu, và mình nhận thấy như sau:

Dữ liệu thuộc dạng dữ liệu “Write One”, nghĩa là chỉ ghi vào Database 1 lần, không có sự thay đổi sau này. Do vậy, mình có thể coi nó như là “Static Data” (dữ liệu tĩnh) luôn.
Tần suất ghi là nhiều, nhưng dữ liệu chỉ được sử dụng cho việc tính toán điểm và xếp hạng
Khách hàng không yêu cầu dữ liệu phải real-time
Việc thay đổi dữ liệu (thêm mới), chỉ thực hiện ở ngày hiện tại. Những dữ liệu của những ngày trước đó chỉ nhằm mục đích lưu trữ, chưa sử dụng phân tích hay làm gì khác

Từ đó, mình nghĩ ra thêm 1 ý tưởng như thế này:

Phân tách dữ liệu thành 2 phần là “current” & “past” (Hiện tại & Quá khứ). Dữ liệu hiện tại chính là dữ liệu của ngày hôm nay, dữ liệu của quá khứ là dữ liệu của những ngày trước đó
Thay vì ghi trực tiếp dữ liệu lên Database, ta sẽ sử dụng 1 service Cache trung gian. Nhiệm vụ của con Cache này là lưu dữ liệu cho phần “current”.
Vì việc tính toán BXH chỉ thực hiện trên phần “current”, nên ta sẽ đọc dữ liệu từ cache luôn. Mình quyết định sử dụng Redis, với key là userID, và value là mảng các event_data trong ngày của người dùng tương ứng
Cuối mỗi ngày, thực hiện lưu dữ liệu từ Redis lên Database, clear các dữ liệu dư thừa. Công việc của hôm sau lặp lại như vậy

Okay, sau khi thực hiện giải pháp trên, thống kê sẽ có như thế này:

Thời gian lấy dữ liệu cho mỗi người dùng: 56ms

Như vậy, chúng ta đã giảm thời gian thêm 90% nữa. Với khoảng 6650 người ở trên, ta chỉ mất khoảng hơn 6 phút để thực hiện

6. Áp tờ cờ re đuýt

Như vậy, qua bài toán thực tế trên, ta đã giảm thời gian thực hiện công việc từ khoảng 3 giờ xuống còn khoảng 12,6 giây Từ đó, ta thấy rằng, để thực hiện việc optimize truy vấn dữ liệu, ta không chỉ phải chăm chú vào câu truy vấn, mà cần kết hợp thêm những công cụ khác cũng như cần nắm rõ tính năng, trường hợp sử dụng dữ liệu, loại dữ liệu,… từ đó tìm kiếm thêm những giải pháp phù hợp

Bài viết này chỉ là 1 ví dụ từ dự án thực tế mình đã làm, không “vơ đũa cả nắm” cho toàn bộ các hệ thống. Trên thực tế, còn có nhiều hệ thống phức tạp hơn thế này nhiều nữa, và có thêm nhiều cách khác để áp dụng nữa. Nhưng mà đến lúc nào mình thực hành trên các hệ thống đó, thì sẽ viết tiếp nhé

Cám ơn mọi người đã đọc đến đây. Đừng quên upvote, bookmark và để lại comment để trao đổi và học hỏi cùng nhau thêm nhé. Chúc mọi người ngủ ngon và hẹn gặp lại!!!

BestPerformance SQL