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

Xin chào mọi người!!! MayFest đã trở lại, và mình cũng trở lại rồi đây.

Chuyện là thời gian vừa rồi mình có quay trở lại làm việc với 1 dự án sử dụng CSDL dạng SQL (PostgreSQL, MySQL). Và 1 lần nữa lại dính ngay bài toán tối ưu hệ thống bằng cách tối ưu câu query. Có chủ đề để viết cho các bạn đọc rồi đây. Cũng khoảng 2-3 bài cho phần này theo những gì mình làm nhé. Nào bắt đầu thôi.

Dự án mà mình cần optimize có chứa 1 bảng dữ liệu để lưu log. Chưa nói đến vấn đề thiết kế, chọn CSDL của các lập trình viên trước mình có tốt hay không, mà vấn đề ở chỗ bảng này có hàng triệu và sẽ sớm lên hàng chục triệu records sớm thôi các bạn ạ. Khi mà các doanh nghiệp và tổ chức tạo ra lượng dữ liệu ngày càng tăng, nhu cầu lưu trữ và truy xuất dữ liệu đó một cách hiệu quả trở nên cấp bách hơn. Tuy nhiên, việc hiển thị các bảng dữ liệu lớn có thể là một thách thức, đặc biệt là khi các bảng chứa hàng triệu hàng. Thời gian truy vấn chậm và hiệu suất kém có thể ảnh hưởng đến trải nghiệm người dùng và dẫn đến giảm năng suất. Trong bài viết này, chúng ta sẽ khám phá các kỹ thuật để tối ưu hóa hiệu suất của các bảng dữ liệu lớn, với hơn 5 triệu hàng và giảm thời gian truy vấn đã giảm từ 12 phút xuống chỉ còn 30 mili giây.

Lưu ý rằng, những cách sử dụng ở dưới đây là nền tảng, chưa phải là cách sử dụng tốt nhất mà mình ứng dụng trong dự án (có áp dụng thêm 1 số công nghệ/công cụ khác). Nhưng về độ hữu ích để các bạn áp dụng là không tồi chút nào đâu nhé.

Giả sử chúng ta có 1 bảng có tên event_data với hơn 5 triệu hàng dữ liệu theo cấu trúc:

| Column | Type |
| --- | --- |
| id | INT|
| user_id | INT|
| event_name | VARCHAR(50) |
| time | DateTime |

1. Khởi động

Đầu tiên, khi mới bắt đầu dự án, hầu hết chúng ta đều sẽ viết câu truy vấn như thế này để lấy danh sách dữ liệu

SELECT * FROM event_data ORDER BY user_id

Câu truy vấn trên sẽ hoàn toàn ổn với lượng dữ liệu nhỏ. Tuy nhiên, với hơn 5 triệu hàng, thì nó không tốt chút nào. Tất nhiên là nó vẫn hoạt động, nhưng hiệu suất thì đáng phải lo ngại:

Total rows: 5742635 Query complete: 00:11:41.805

Như các bạn thấy, 11 phút 41 giây để hoàn thành. Chưa nói đến tăng tải cho hệ thống, chắc cũng chẳng user nào đủ kiên nhẫn để truy vấn một danh sách mà mất hơn 11 phút cho mỗi lần tải.

2. Phân trang với OFFSET/LIMIT

Nếu trong đầu bạn đang nghĩ đến phân trang (Pagination) thì chính xác rồi đấy! Đây gần như là kĩ thuật bắt buộc mà chúng ta cần phải biết. Nó thường được áp dụng để hiển thị lượng lớn dữ liệu theo từng trang với số lượng có giới hạn, giúp phân nhỏ dữ liệu, từ đó truy vấn nhanh hơn

Với phân trang offset/limit, chúng ta đang tận dụng hai từ khóa SQL: OFFSET và LIMIT. Từ khóa LIMIT cho SQL biết có bao nhiêu hàng cần trả về, trong khi OFFSET cho SQL biết có bao nhiêu hàng cần bỏ qua.

Ví dụ bạn muốn thiết lập 20 hàng cho mỗi trang, thì LIMIT của chúng ta là 20. Khi đó, OFFSET sẽ được tính như sau:

OFFSET = (PAGE-1) * LIMIT

Trong đó PAGE là trang hiện tại bạn muốn hiển thị. Giá trị nhỏ nhất của PAGE là 1.

Bây giờ, hãy thử lấy 20 hàng đầu tiên nhé (PAGE = 1, LIMIT = 20 ⇒ OFFSET = 0)

SELECT * FROM event_data ORDER BY user_id LIMIT 20 OFFSET 0

Kết quả sẽ trả về 20 hàng đầu tiên của bảng. Thống kê sẽ như sau:

Total rows: 20 Query complete 00:00:04.549

Ủa, có 4 giây là xong rồi. Vậy tuyệt quá rồi. Mỗi trang 4s như thế này thì đâu cần làm gì tiếp nữa, chừng này thời gian thì user cũng sẵn lòng đợi.

Nhưng đời thì không như mơ các bạn ạ. Giờ hãy thử lấy 20 hàng ở trang 25001. Lúc này ta có:

OFFSET = (25001 - 1) * 20 = 500000

Câu truy vấn sẽ là:

SELECT * FROM event_data ORDER BY user_id LIMIT 20 OFFSET 500000

Và đây là kết quả:

Total rows: 20 Query complete 00:00:40.252

Các bạn thấy rồi đấy, khi chúng ta truy vấn càng về các trang phía sau, thời gian truy vấn sẽ càng nhiều. Như truy vấn trên cũng mất đến 40s để hoàn thành. Vẫn là “rất lâu” đúng không nào.

Một trong những thách thức chính khi sử dụng phân trang với OFFSET/LIMIT là nó có thể chậm và không hiệu quả khi xử lý các tập dữ liệu lớn.

Lý do cho điều này là phân trang OFFSET/LIMIT yêu cầu cơ sở dữ liệu quét qua toàn bộ tập dữ liệu, ngay cả khi chỉ một phần nhỏ dữ liệu được hiển thị trên một trang cụ thể. Điều này có thể đặc biệt khó khăn nếu tập dữ liệu rất lớn, vì cơ sở dữ liệu có thể cần truy xuất hàng triệu hàng và sau đó loại bỏ tất cả trừ một phần nhỏ trong số đó. Vậy làm sao để khắc phục nhược điểm này? Hãy xem tiếp 2 phần kế bên dưới nhé

3. Phân trang với DEFERRED JOIN

Deferred join là một cách để tối ưu hóa phân trang trong SQL. Thay vì thực hiện phân trang trên toàn bộ bảng, nó tạo một tập hợp con dữ liệu bằng truy vấn con. Sau đó, truy vấn con được nối với bảng gốc, nhưng hoạt động nối thực tế bị hoãn lại cho đến khi quá trình phân trang hoàn tất. Kỹ thuật này được gọi là phép nối "deferred" vì nó trì hoãn thao tác nối cho đến khi thực sự cần thiết. Bằng cách sử dụng kỹ thuật này, bạn có thể cải thiện hiệu suất của truy vấn phân trang và làm cho chúng hiệu quả hơn.

Bây giờ, hãy xem câu query sử dụng deferred join:

SELECT * FROM public.event_data 
INNER JOIN (
	SELECT id FROM public.event_data ORDER BY user_id LIMIT 20 OFFSET 500000 
) AS tmp USING (id)
ORDER BY user_id

Các bạn có thể thấy, thay vì thực hiện phân trang và sắp xếp trên toàn bộ dữ liệu, chúng ta tạo ra 1 tập dữ liệu con của chính bảng event_data (chỉ SELECT cột id) và thực hiện phân trang, sắp xếp trên đó. Sau đó ta tiến hành thực hiện nối với bảng dữ liệu gốc dựa trên trường id

Mặc dù truy vấn con đã sắp xếp các hàng theo thứ tự tăng dần dựa trên "user_id", tuy nhiên vì phép nối không giữ nguyên thứ tự ban đầu của truy vấn con. Do đó, nếu chúng ta muốn tập hợp kết quả cuối cùng được sắp xếp theo thứ tự tăng dần dựa trên "user_id", thì chúng ta cần đưa mệnh đề ORDER BY user_id vào cuối truy vấn.

Và đây là kết quả khi thực thi:

Total rows: 20 Query complete 00:00:18.376

Good! Vậy là chúng ta đã tiếp tục giảm được thời gian truy vấn đi khoảng 50%. Kỹ thuật này đã được áp dụng rộng rãi và có sẵn các thư viện cho các framework web phổ biến như Rails (FastPage) và Laravel (Fast Paginate).

4. Phân trang với con trỏ (Cursor Pagination)

Phân trang dựa trên con trỏ là phương pháp phân trang sử dụng "con trỏ" để xác định trang kết quả tiếp theo. Ý tưởng đằng sau việc phân trang dựa trên con trỏ là bạn có một con trỏ trỏ đến bản ghi cuối cùng của trang hiện tại. Khi người dùng yêu cầu trang kết quả tiếp theo, họ phải gửi cùng với con trỏ đó để xác định vị trí bắt đầu trang kết quả tiếp theo.

Lúc này, thay vì sử dụng từ khóa OFFSET, chúng ta sẽ sử dụng con trỏ để xây dựng mệnh đề WHERE và lọc ra tất cả các hàng đã được trả về kết quả trước đó.

Bây giờ, hãy thử lấy trang đầu tiên với 20 hàng:

SELECT * FROM event_data ORDER BY user_id LIMIT 20 OFFSET 0

Giả sử danh sách là như sau:

id	user_id	event_name	time
1	1	add_data	null
2	1	remove_data	null
3	2	add_data	null
4	3	add_data	null
5	4	add_data	null
…	…	…	…
19	22	add_data	null
20	29	add_data	null

Vì truy vấn của chúng ta sẽ sắp xếp tăng dần theo user_id , do vậy, lúc này ta sẽ sử dụng nó làm con trỏ, và giá trị cho con trỏ cho trang đầu tiên sẽ là 29 (user_id cuối cùng của trang 1).

Bây giờ, ta sẽ sử dụng con trỏ user_id trên cho trang thứ 2 trong mệnh đề WHERE:

SELECT * FROM public.event_data WHERE user_id > 29 ORDER BY user_id LIMIT 20

Các bạn có thể thấy ta sẽ chỉ lấy dữ liệu với user_id lớn hơn 29 và lấy 20 hàng đầu tiên. Tương tự, câu truy vấn để bỏ qua 500000 user_id đầu tiên sẽ như thế này (trường hợp user_id được đánh số đầy đủ từ 1 đến 500000)

SELECT * FROM public.event_data WHERE user_id > 500000 ORDER BY user_id LIMIT 20

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

Total rows: 20 Query complete 00:00:00.768

Oh wow!!! Câu query trên chỉ tốn 768ms để hoàn thành. Nhanh hơn rất nhiều đúng không nào. Nếu so với bài toán ban đầu, ta đã khiến thời gian giảm đến 98%

5. Đánh INDEX cho bảng

Lợi ích của việc đánh INDEX chắc mình cũng không cần đề cập đến trong bài viết này nữa nhé. Đã có nhiều tác giả khác viết về nó quá là xịn xò rồi. Bây giờ, ta sẽ đánh INDEX cho trường user_id mà ta sử dụng ở trên ở kĩ thuật Cursor Pagination. Và đây là kết quả thống kê

Total rows: 20 Query complete 00:00:00.289

Tuyệt vời. Cuối cùng thì ta cũng đã đạt được mục tiêu mà tiêu đề bài viết đưa ra. Từ 12 phút giờ chỉ còn 289 mili giây. 😆

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

Nhìn chung, mặc dù phân trang sử dụng OFFSET/LIMIT, DEFERRED JOIN hay CURSOR có thể là một cách tiếp cận hữu ích và đơn giản để phân trang, nhưng nó cũng có thể là một thách thức để sử dụng hiệu quả trong các tập dữ liệu lớn và phức tạp. Điều quan trọng là phải xem xét cẩn thận và chọn phương pháp phân trang phù hợp cho trường hợp sử dụng cụ thể của bạn.

Như đã nói ở đầu bài, những kĩ thuật là hữu ích. Tuy nhiên, trong 1 số bài toán đặc thù, đòi hỏi dữ liệu lớn hơn, và tuỳ vào tính chất dữ liệu, ta sẽ có thêm những kĩ thuật khác để sử dụng.

Kĩ thuật đó là gì thì hãy cùng nhau phân tích ở bài sau nhé!

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

Chúc các bạn có 1 tuần mới ngập tràn năng lượng. Đừng quên upvote nếu bài này hữu ích cho bạn nhé. Cám ơn mọi người đã đọc đến đây. Hẹn gặp lại!!!