Top50 truy vấn đến cơ sở dữ liệu

Sau khi bạn đã nối hết các bảng trong database lại với nhau, giờ là lúc viết vài cái query. Mà vài cái thì dành cho newbie thôi. Bạn giờ là pro rồi, nên sẽ cần viết tận 50(!) truy vấn cho database của mình. Và đây mới chỉ là những cái cần thiết nhất thôi nha.

Truy vấn đến cơ sở dữ liệu

1. Lấy danh sách sản phẩm cho trang chủ

Query này trả về tất cả sản phẩm đang active với giá chính và hình ảnh chính để show lên trang chủ và catalog. Nhờ vậy mà trang chủ load nhanh và thông tin sản phẩm luôn mới nhất.

2. Tìm kiếm sản phẩm theo từ khóa

Cho phép user tìm sản phẩm họ quan tâm bằng cách match tên hoặc mô tả. Đây là tính năng quan trọng để user tìm nhanh trong catalog.

3. Thẻ sản phẩm theo ID

Trả về thông tin chi tiết về một sản phẩm cụ thể, gồm brand và category. Cần thiết để hiển thị trang chi tiết sản phẩm.

4. Danh sách các phiên bản sản phẩm

Hiển thị tất cả các phiên bản (SKU) của sản phẩm: size, màu, tồn kho, giá. Dùng để chọn phiên bản phù hợp trên trang sản phẩm.

5. Gallery hình ảnh sản phẩm

Để hiển thị đầy đủ thẻ sản phẩm thì cần tất cả hình ảnh của nó. Query trả về các hình này kèm hình chính.

6. Đánh giá trung bình và số lượng review của sản phẩm

Dùng để hiển thị điểm đánh giá và số review, rất quan trọng cho uy tín và niềm tin của khách.

7. Danh sách chi tiết review cho sản phẩm

Cho phần review trong thẻ sản phẩm: điểm, nội dung, tác giả và ngày review. Giúp khách mới quyết định mua dễ hơn.

8. Hỏi đáp về sản phẩm

Query lấy các câu hỏi và trả lời cho từng sản phẩm, quan trọng cho block FAQ trên thẻ sản phẩm.

9. Danh mục sản phẩm có phân cấp

Cho phép visualize cấu trúc catalog, xây dựng cây điều hướng cho filter và menu.

10. Sản phẩm theo danh mục và danh mục con

Giúp hiển thị tất cả sản phẩm trong một category hoặc các category con (có phân cấp).

11. Danh sách brand

Dùng để filter theo brand, tạo brand-listing và landing page.

12. Tag phổ biến và số lượng sản phẩm theo tag

Phân tích tag được dùng nhiều nhất để show sản phẩm trending và xây cloud tag.

13. Lịch sử thay đổi giá sản phẩm

Cho analytic và hiển thị biến động giá (giá cũ/mới, khuyến mãi).

14. Lịch sử thay đổi trạng thái sản phẩm

Cho phép theo dõi vòng đời sản phẩm, lý do biến mất khỏi trang chủ hoặc trả lại.

15. Tìm kiếm theo chứng chỉ và giấy phép

Cực kỳ quan trọng cho khách chuyên nghiệp và B2B (chất lượng và hợp pháp sản phẩm).

16. Dữ liệu về nhà cung cấp sản phẩm

Quan trọng cho admin, kiểm soát chất lượng và liên hệ nhà cung cấp.

17. Tồn kho sản phẩm theo kho hàng

Kiểm soát và quản lý tồn kho thực tế theo từng kho. Cần thiết cho logistics và tránh "out of stock".

18. Sản phẩm có tồn kho dưới ngưỡng

Tự động bổ sung kho, tránh mất doanh thu vì hết hàng.

19. Dòng di chuyển sản phẩm trong kho (audit)

Theo dõi mọi di chuyển sản phẩm trong khoảng thời gian chọn: nhập, xuất, điều chỉnh, quan trọng cho kiểm kê và tránh thất thoát.

20. Lịch sử di chuyển giữa các kho

Cho phép xem lịch sử và trạng thái di chuyển nội bộ giữa các trung tâm logistics.

21. Giao hàng: phương thức và bảng giá

Để tính phí giao hàng và báo cho user khi đặt hàng.

22. Lịch sử đơn hàng của user

Phần quan trọng nhất của tài khoản cá nhân — tất cả đơn đã đặt, trạng thái và tổng tiền.

23. Chi tiết đơn hàng với các item

Cho phép lấy cấu trúc đầy đủ của đơn — thành phần, giá, số lượng — để show lên frontend hoặc hỗ trợ.

24. Báo cáo đơn hàng theo thời gian và trạng thái

Phân tích và báo cáo doanh số, trả về đơn theo thời gian và trạng thái cần (ví dụ "hoàn thành").

25. "Giỏ hàng bị bỏ quên"

Phân tích cho marketing: giỏ hàng mà user chưa đặt — tiềm năng cho retargeting.

26. Top bán chạy

Phân tích cho block "Bán chạy" và các bộ sưu tập marketing: sản phẩm nào bán nhiều nhất.

27. Doanh số theo ngày (cho biểu đồ)

Báo cáo doanh thu theo ngày — nền tảng để phân tích động lực kinh doanh và vẽ biểu đồ.

28. Danh sách hoàn trả

Hiển thị hoàn trả của tất cả đơn với lý do và trạng thái, giúp phân tích nguyên nhân hoàn trả.

29. Danh sách đơn bị hủy

Kiểm soát thất thoát và lý do hủy: hiển thị hủy với lý do, ai hủy và khi nào.

30. Đơn hàng chờ gửi

Cho kho và bộ phận giao hàng — đơn cần đóng gói và gửi, kèm chi tiết giao hàng.

31. Giá trị trung bình đơn hàng

Chỉ số "Average Order Value" — metric then chốt để đánh giá hiệu quả marketing và assortment.

32. Đơn hàng dùng mã giảm giá

Phân tích hiệu quả campaign: mã nào được dùng và tần suất.

33. Sử dụng giảm giá theo category và brand

Cho phép đánh giá campaign nào hiệu quả, theo dõi độ hot của giảm giá theo category và brand.

34. Mã giảm giá đã dùng và user sử dụng

Kiểm soát việc dùng mã giảm giá, phát hiện bất thường và lạm dụng.

35. Lịch sử thanh toán của đơn hàng

Cho hỗ trợ và kế toán: hiển thị tất cả giao dịch thanh toán, trạng thái và phương thức thanh toán.

36. Đơn hàng hoàn tiền

Cho phân tích hoàn trả, tạo báo cáo kế toán và phòng chống gian lận.

37. Số dư ví user và lịch sử giao dịch

Kiểm soát và hiển thị điểm thưởng hoặc cashback của user, lịch sử biến động.

38. Yêu cầu hỗ trợ của user

Cho phép user xem các yêu cầu và trạng thái xử lý.

39. SLA-analytic cho ticket hỗ trợ

Phân tích thời gian phản hồi và xử lý trung bình theo từng mức ưu tiên, quan trọng cho kiểm soát SLA.

40. Tin nhắn trong ticket hỗ trợ

Cho phép xem toàn bộ trao đổi trong ticket, quan trọng cho user và bộ phận hỗ trợ.

41. FAQ đang active theo category

Hiển thị câu hỏi thường gặp cho knowledge base của khách, giúp giảm tải cho support.

42. Campaign marketing và banner đang active

Để show các offer quảng cáo mới nhất trên site.

43. Sản phẩm nổi bật trên trang chủ

Cho block "Yêu thích": sản phẩm cần nhấn mạnh trên trang chủ.

44. Lịch sử A/B test

Phân tích các experiment đã chạy để tối ưu UX và marketing.

45. Lịch sử xem sản phẩm của user cụ thể

Hiển thị "Bạn đã xem" hoặc dùng cho gợi ý cá nhân hóa.

46. Truy vấn tìm kiếm phổ biến của user

Phân tích nhu cầu user, giúp tối ưu search và gợi ý.

47. Phân tích nguồn traffic

Cho phép đánh giá kênh quảng cáo nào mang lại traffic và chuyển đổi.

48. Giữ chân user theo cohort

Metric then chốt để đánh giá loyalty và mua lại.

49. Tin tức/bài viết cho trang chủ

Để show tin tức và bài blog, tăng engagement của user.

50. Trang site đang active và các block nội dung liên quan

Để kiểm tra tính toàn vẹn nội dung site, hoạt động của CMS và hiển thị dữ liệu trên trang.

Thêm index

Query thì ngon rồi, nhưng chỉ khi nó chạy nhanh thôi. Nên bạn cần thêm tí index cho database. Bạn nên thêm vào các bảng chính của project 40 index để tăng hiệu suất query và dễ vận hành.

1. Index trên product.product(status)

Hầu hết query đến sản phẩm đều filter theo status (ví dụ sản phẩm active cho trang chủ, tìm kiếm, v.v.). Index này tăng tốc lấy sản phẩm theo status, giảm scan toàn bảng.

2. Index trên product.variant(product_id, is_active)

Query đến variant (SKU) và trang chủ dùng filter theo product và trạng thái active. Index này giúp lấy nhanh tất cả variant active của một sản phẩm.

3. Index trên product.image(product_id, is_main DESC)

Để lấy hình chính của sản phẩm (hoặc toàn bộ hình), dùng filter theo product và sort theo "is_main". Index này tăng tốc lấy dữ liệu cho gallery.

4. Index trên product.product(name text_pattern_ops)

Để tìm nhanh sản phẩm theo từ khóa trong tên qua ILIKE '%...%'. Index chuyên biệt này giúp search substring nhanh, nhất là với bảng lớn.

5. Index trên product.product(description gin_trgm_ops)

Tương tự trên — tìm theo mô tả sản phẩm (ILIKE hoặc fulltext). GIN index với trigram tăng tốc filter trường text.

6. Index trên product.product(category_id)

Thường xuyên lấy theo category hoặc category con (xem query filter theo category catalog). Index này giúp tìm nhanh sản phẩm theo category.

7. Index trên product.category(parent_id)

Để xây dựng phân cấp category và visualize cây điều hướng, thường lấy theo parent_id. Index này tăng tốc query phân cấp.

8. Index trên product.review(product_id)

Tất cả truy vấn review sản phẩm đều filter theo product_id (cả tính điểm trung bình lẫn lấy danh sách review). Index này giúp aggregate và lấy review nhanh hơn nhiều.

9. Index trên product.review(product_id, created_at DESC)

Để lấy nhanh review mới nhất của sản phẩm (ORDER BY createdat DESC), nhất là khi filter theo productid, cần index tổng hợp này.

10. Index trên product.question(product_id, created_at DESC)

Query phổ biến lấy Q&A theo sản phẩm, sort theo thời gian tạo. Index này cover cả hai điều kiện và tăng tốc lấy Q&A trên thẻ sản phẩm.

11. Index trên product.answer(question_id, created_at)

Để tìm câu trả lời cho câu hỏi sản phẩm cần truy cập nhanh theo question_id, thường sort theo ngày. Index này giảm delay khi tạo Q&A.

12. Index trên product.price_history(variant_id, changed_at DESC)

Lịch sử thay đổi giá lấy nhanh theo variant và thay đổi gần nhất. Index này tăng tốc analytic về giá cũ/mới.

13. Index trên product.status_history(product_id, changed_at DESC)

Lấy lịch sử thay đổi trạng thái sản phẩm sort theo thời gian rất cần cho audit và kiểm soát vòng đời. Index tổng hợp này tăng tốc query này nhiều.

14. Index trên product.certificate(product_id)

Tìm chứng chỉ sản phẩm theo id — thao tác phổ biến cho B2B và trang chủ có chứng nhận. Index này tăng tốc kiểm tra.

15. Index trên product.license(product_id)

Để tìm license theo sản phẩm, nhất là khi filter theo loại license.

16. Index trên product.product_tag(tag_id)

Query phổ biến — lấy tất cả sản phẩm theo tag (và ngược lại). Index này giúp intersect sản phẩm và tag nhanh cho cloud tag hoặc filter.

17. Index trên product.product_tag(product_id)

Cho phép xác định nhanh tag nào gắn với sản phẩm, tăng tốc filter theo tag.

18. Index trên logistics.inventory(product_id, warehouse_id)

Để truy cập tồn kho sản phẩm theo kho ngay lập tức (hoặc tính tổng tồn kho) — cực kỳ quan trọng cho logistics, kiểm tra stock level và trang chủ realtime.

19. Index trên logistics.inventory(variant_id)

Để quản lý tồn kho theo variant (màu/size) và cho báo cáo xuyên suốt.

20. Index trên logistics.stock_level(product_id, warehouse_id)

Kiểm tra nhanh ngưỡng tối thiểu sản phẩm trong kho (ví dụ tự động đặt hàng hoặc cảnh báo hết hàng). Index này cần để so với inventory.

21. Index trên logistics.inventory_movement(product_id, changed_at DESC)

Cho phép lấy nhanh lịch sử di chuyển sản phẩm (audit) trong thời gian gần — hữu ích để tránh lỗi, phân tích thất thoát và kiểm soát nhập hàng.

22. Index trên logistics.transfer(product_id, requested_at DESC)

Cho analytic di chuyển giữa kho, filter theo sản phẩm và sort theo ngày yêu cầu.

23. Index trên logistics.shipping_rate(shipping_method_id, destination_zone)

Khi tính phí giao hàng thường lấy theo id phương thức và vùng đích. Index này tăng tốc tính phí cho khách khi đặt hàng.

24. Index trên "order".order(user_id, placed_at DESC)

Tất cả truy vấn lịch sử đơn hàng user đều filter theo user_id và sort theo ngày đặt. Index tổng hợp này giúp trả về lịch sử đơn cực nhanh cho tài khoản cá nhân.

25. Index trên "order".order(status, placed_at)

Cho analytic và báo cáo đơn theo thời gian, cũng như tìm theo status (ví dụ "đang xử lý"/"hoàn thành").

26. Index trên "order".order_item(order_id)

Lấy tất cả item của đơn theo id đơn — thao tác cực kỳ phổ biến để xem chi tiết đơn.

27. Index trên "order".order_item(product_id)

Phân tích bán hàng và thống kê theo sản phẩm cần lấy nhanh item đơn theo id sản phẩm.

28. Index trên "order".return(order_id)

Liên kết hoàn trả với đơn dùng cho hỗ trợ và analytic hoàn trả. Index này tăng tốc tìm hoàn trả theo số đơn.

29. Index trên "order".cancellation(order_id)

Tương tự hoàn trả — tăng tốc phát hiện đơn bị hủy cho analytic và hỗ trợ.

30. Index trên "order".cart(user_id, updated_at DESC)

Để tìm giỏ hàng gần nhất của user (ví dụ tìm "giỏ bỏ quên"), nên có index theo user_id và sort theo ngày cập nhật.

31. Index trên payment.payment_transaction(order_id)

Hầu hết query lịch sử thanh toán đều filter theo đơn. Index này cho phép truy cập giao dịch đơn ngay lập tức.

32. Index trên payment.refund(transaction_id)

Cho phép tìm hoàn tiền theo giao dịch nhanh cho hỗ trợ, báo cáo và kiểm soát gian lận.

33. Index trên payment.wallet(user_id)

Truy cập ví user nhanh để kiểm tra số dư và lịch sử giao dịch.

34. Index trên payment.wallet_transaction(wallet_id, created_at DESC)

Lấy giao dịch ví user sort theo ngày (ví dụ hiển thị lịch sử giao dịch).

35. Index trên support.support_ticket(user_id, created_at DESC)

Lịch sử yêu cầu hỗ trợ của user (tài khoản cá nhân/dịch vụ khách hàng). Index tổng hợp này tối ưu query này.

36. Index trên support.ticket_message(ticket_id, sent_at)

Để hiển thị toàn bộ trao đổi trong ticket nên có index theo ticket và ngày — tăng tốc sort tin nhắn theo thời gian.

37. Index trên support.ticket_sla_tracking(ticket_id)

Cho analytic SLA và kiểm soát từng ticket, truy cập nhanh dữ liệu SLA nhờ index theo ticket_id.

38. Index trên marketing.promo_usage(user_id, used_at DESC)

Cho analytic hoạt động user với mã giảm giá (cả analytic lẫn chống lạm dụng), cần tìm nhanh theo user_id và sort theo thời gian.

39. Index trên analytics.product_view(user_id, viewed_at DESC)

Lưu và phân tích lịch sử xem sản phẩm của user (cá nhân hóa, gợi ý) cần truy cập nhanh theo user_id và sort theo thời gian xem.

40. Index trên analytics.search_query_log(query_text)

Truy vấn phổ biến và tần suất dùng — analytic then chốt cho search. Index này tăng tốc aggregate và đếm theo text query.

Lưu ý

Đối với search text bằng ILIKE nên dùng GIN index với extension pg_trgm, rất hiệu quả cho search substring và fuzzy search. Với bảng lớn cần aggregate hoặc sort theo ngày nên dùng index DESC trên ngày — tăng tốc lấy record mới nhất.

Nên tune index theo thực tế execution plan và thống kê tải, nhưng các index trên đã cover hầu hết scenario production cho marketplace của tụi mình.

Thêm function

Bạn chưa mệt đúng không? Vậy viết thêm vài function nữa để đơn giản hóa việc viết query hiện tại và sau này. Gọi là để tăng tốc triển khai query then chốt, giảm lặp code trong app và gom logic nghiệp vụ về phía database.

1. Tìm kiếm sản phẩm theo từ khóa kèm tag và brand

Tại sao cần:

Search thông thường theo tên và mô tả thì hạn chế. Nhiều khi cần tìm theo tag và brand nữa. Function này gom hết logic search nâng cao lại, giảm lặp code và dễ tích hợp frontend.

2. Lấy đầy đủ thẻ sản phẩm theo ID (tất cả dữ liệu cho thẻ)

Tại sao cần:

Frontend thường cần tất cả thông tin sản phẩm một lần: trường chính, brand, category, hình ảnh, tag, thuộc tính, điểm trung bình và số review. Function này trả về thẻ sản phẩm đầy đủ chỉ với một lần gọi, giảm số query đến DB.

3. Lấy phân cấp category với nested

Tại sao cần:

Xây dựng cây (hoặc path) category cần cho trang chủ, filter và breadcrumbs. Thay vì query đệ quy ở client, function này trả về toàn bộ phân cấp một lần.

4. Tính giá trung bình và min/max theo category

Tại sao cần:

Cho filter catalog và analytic tiện lấy thống kê tổng hợp sản phẩm trong category: range giá, giá trung bình. Function này loại bỏ subquery lặp lại.

5. Kiểm tra và tự động tính tồn kho sản phẩm trên tất cả kho

Tại sao cần:

Cho phép biết ngay tổng tồn kho sản phẩm (và từng variant), hữu ích cho trang chủ, kho và logistics. Gom tính toán lại, tránh lặp logic nghiệp vụ.

6. Lấy lịch sử đơn hàng của user kèm chi tiết

Tại sao cần:

Function trả về danh sách đơn của user, gồm cả item, tổng tiền, trạng thái, cho phép frontend lấy lịch sử chỉ với một lần gọi và dựng luôn tài khoản cá nhân.

7. Lấy điểm trung bình user với vai trò người bán/mua

Tại sao cần:

Để hiển thị độ tin cậy và uy tín user trên platform cần biết điểm trung bình với vai trò bán hoặc mua. Function này tính aggregate luôn.

8. Dùng mã giảm giá cho user (validator với đủ điều kiện)

Tại sao cần:

Tất cả logic kiểm tra và trừ mã giảm giá (active, limit, ngày, v.v.) gom lại một function. Dễ code app và tránh lỗi khi lặp điều kiện.

9. Function log sự kiện user universal

Tại sao cần:

Cho analytic xuyên suốt và audit, log sự kiện tập trung giảm lặp code và giảm nguy cơ mất dữ liệu hành động user.

10. Function lấy số dư ví thưởng và tổng cộng tích lũy

Tại sao cần:

Một lần gọi lấy ngay số dư hiện tại và tổng cộng tích lũy của user. Tiện show dashboard và giảm số query SQL.

11. Function đổi trạng thái đơn universal kèm log

Tại sao cần:

Đổi trạng thái đơn, thêm record vào log lịch sử trạng thái và giảm lỗi khi đổi trạng thái ở nhiều nơi trong app.

12. Lấy tất cả tin nhắn của ticket hỗ trợ (ticket + toàn bộ message)

Tại sao cần:

Function trả về toàn bộ trao đổi trong ticket, gồm chi tiết yêu cầu và từng message. Dễ dựng lịch sử ticket trên frontend.

13. Kiểm tra tồn tại user theo email hoặc số điện thoại

Tại sao cần:

Dùng cho đăng ký và quên mật khẩu, tránh lặp logic ở frontend và backend.

Lưu ý

Bộ function này cover các scenario then chốt của business, tăng tiện lợi khi làm việc với data, tối ưu logic và tăng tốc phát triển frontend và tích hợp. Hy vọng bạn thích nhé :)

Các tệp có giải pháp