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
Bài đăng này đã không được cập nhật trong 2 năm
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!!!
All rights reserved
Bình luận
bản chất mình làm Front end 100% nên đọc vào hơi gây lú, nhưng ai lại chê kiến thức bao giờ. bài viết rất hữu ích, thanks thớt 😗
Biết đâu cơ duyên 1 ngày nào đó lại được làm Full-stack bạn nhỉ 😃
Đọc mấy bài tối ưu bị nhiễm phải dùng công nghệ A, công nghệ B, sử dụng quantumn machine, update ram, SSD để tăng tốc độ. Nhưng trước tiên là phải vắt câu query đến khô cằn như lượt bỏ data thừa, rồi cắm cờ, sắp xếp lại thứ tự các kiểu. Hay ác 👍️👍️👍️👍️👍️👍️
Đúng rồi bạn, mình thường xử lý trong code trước. Việc nâng thêm RAM hay ổ đĩa nó chỉ nên làm để hỗ trợ thôi. 😄
Thay đổi để tốt hơn. Good Job 💪
Bài viết rất hữu ích cho người làm BE như mình. Cảm ơn bạn về series optimize query này. Mong chờ thêm nhiều series bổ ích từ bạn 🤩
Cám ơn Sang. Nghe bài viết này hữu ích là mình vui lắm rồi
"3 giờ xuống còn khoảng 12,6 giây" Kinh hãi thật, mình mới đi thực tập backend được hơn hai tháng và không có để ý tới hiệu suất truy vấn khi thao tác với db cho lắm Cám ơn bài viết! 🔥🔥🔥
Đúng rồi bạn, ngày xưa mình cũng vậy, "code để chạy được thôi". Sau này thì yêu cầu dự án bắt buộc, học hỏi được rồi đam mê optimize luôn. Bạn mới đi thực tập thì mình nghĩ 1 thời gian nữa bạn sẽ làm việc với những bài toán này thôi. Hy vọng nó hữu ích
Bài viết rất hay nhưng do theo FE nên đọc nhiều chỗ hơi lú =))) lưu lại khi nào học BE rồi về đọc lại hehe
Cố gắng Full-stack bạn nha ✊
Rất thích phương án Caching của bạn. Mình sẽ áp dụng cách này 😉
Cám ơn bạn, áp dụng thành công thì quay lại đây cho mình biết với nhé 😆
Bài viết rất hay, cảm ơn tác giả. Và giả sử số lượng xếp hạng sẽ là khoảng trên vài triệu record, thì có thể đổi qua high read DB không, hay sql/nosql truyền thống vẫn xử lý được?
@NHDPhucIT Cách này hay, trong một dự án cũng team mình đã từng dùng cách này.
@josk2 Vậy là chung chí hướng rồi bạn nhỉ 😀
đọc những bài viết như này quá bánh cuốn, cảm ơn tác giả nhiều
Cám ơn bạn lần nữa
Áp tờ cờ re đuýt .... Nice sừ bro!!!!!! Ra thêm những bài viết ĐẠI LOẠI như thế này nữa nhé. Chúc sức khỏe.
Cám ơn bạn nhé! Hy vọng nó sẽ hữu ích
Bài viết rất hay
Bài viết rất bổ ích. Mình nhận ra rằng làm back-end cũng giống như làm thám tử vậy😂
Cám ơn bạn nha
Dữ liệu lớn luôn là trở ngại đối với mình, cám ơn bạn đã chia sẻ bí kíp võ công rất hay.
Cám ơn bạn. Hữu ích là mình vui rồi 😀
Minh thấy nên thiết kệ hệ thống theo kiểu event source, Update event-data thì cập nhật column điểm trong table user, từ đó xếp hạng.