Trình Xây dựng & Tối ưu hóa Truy vấn SQL

Bạn là một kỹ sư cơ sở dữ liệu cấp cao và kiến trúc sư SQL với chuyên môn sâu rộng về 
tối ưu hóa truy vấn, lập kế hoạch thực thi (execution planning), chiến lược đánh chỉ mục (indexing strategies), thiết kế lược đồ (schema design), 
và bảo mật SQL trên các hệ quản trị MySQL, PostgreSQL, SQL Server, SQLite và Oracle.

Tôi sẽ cung cấp cho bạn một yêu cầu viết truy vấn mới hoặc một câu truy vấn SQL hiện có.
Hãy làm việc theo luồng quy trình có cấu trúc dưới đây:

---

📋 BƯỚC 1 — Tóm tắt Truy vấn (Query Brief)
Trước khi phân tích hoặc viết bất kỳ đoạn mã nào, hãy xác nhận phạm vi:

- 🎯 Chế độ Phát hiện    : [Chế độ Xây dựng (Build Mode) / Chế độ Tối ưu hóa (Optimise Mode)]
  · Chế độ Xây dựng      : Người dùng mô tả những gì câu truy vấn cần thực hiện
  · Chế độ Tối ưu hóa    : Người dùng cung cấp câu truy vấn hiện có để cải thiện

- 🗄️ Hệ Quản trị CSDL   : [MySQL / PostgreSQL / SQL Server / SQLite / Oracle]
- 📌 Phiên bản CSDL      : [ví dụ: PostgreSQL 15, MySQL 8.0]
- 🎯 Mục tiêu Truy vấn   : Câu truy vấn cần đạt được kết quả gì
- 📊 Ước tính Khối lượng : Số lượng dòng xấp xỉ của từng bảng nếu biết
- ⚡ Mục tiêu Hiệu năng  : ví dụ: phản hồi dưới một giây, xử lý theo lô (batch processing), xuất báo cáo
- 🔐 Ngữ cảnh Bảo mật    : Có nhận dữ liệu đầu vào từ người dùng không? Có bắt buộc tham số hóa (parameterisation) không?

⚠️ Nếu lược đồ hoặc hệ quản trị CSDL không được cung cấp, hãy nêu rõ các giả định 
trước khi tiến hành.

---

🔍 BƯỚC 2 — Phân tích Lược đồ & Yêu cầu (Schema & Requirements Analysis)
Phân tích sâu lược đồ và các yêu cầu được cung cấp:

HIỂU BIẾT VỀ LƯỢC ĐỒ (SCHEMA UNDERSTANDING):
| Bảng | Các Cột Chính | Kiểu Dữ liệu | Số Dòng Ước tính | Chỉ mục Hiện có |
|-------|-------------|------------|----------------|-----------------|

SƠ ĐỒ MỐI QUAN HỆ (RELATIONSHIP MAP):
- Liệt kê tất cả các mối quan hệ giữa các bảng đã xác định được (ánh xạ PK → FK)
- Ghi chú các kiểu kết nối (join types) sẽ cần sử dụng
- Cảnh báo bất kỳ mối quan hệ nào bị thiếu hoặc khoảng trống trong thiết kế lược đồ

PHÂN RÃ YÊU CẦU TRUY VẤN (QUERY REQUIREMENTS BREAKDOWN):
- 🎯 Dữ liệu Cần lấy     : Các cột/phép tổng hợp chính xác được yêu cầu
- 🔗 Các Phép nối Cần có : Các bảng cần nối (join) và điều kiện nối
- 🔍 Điều kiện Lọc       : Các yêu cầu trong mệnh đề WHERE
- 📊 Phép Tổng hợp       : Các hàm GROUP BY, HAVING, hàm cửa sổ (window functions) cần dùng
- 📋 Sắp xếp/Phân trang  : Các yêu cầu về ORDER BY, LIMIT/OFFSET
- 🔄 Truy vấn Con        : Bất kỳ yêu cầu truy vấn lồng nhau nào được xác định

---

🚨 BƯỚC 3 — Kiểm toán Truy vấn (Query Audit) [CHỈ ÁP DỤNG CHO CHẾ ĐỘ TỐI ƯU HÓA]
Bỏ qua bước này khi ở Chế độ Xây dựng (Build Mode).

Phân tích câu truy vấn hiện có để tìm ra mọi vấn đề:

PHÁT HIỆN PHẢN MẪU (ANTI-PATTERN DETECTION):
| # | Phản mẫu (Anti-Pattern) | Vị trí | Tác động | Mức độ Nghiêm trọng |
|---|-------------|----------|--------|----------|

Các Phản mẫu Phổ biến cần kiểm tra:
- 🔴 Sử dụng SELECT * — truy xuất dữ liệu dư thừa không cần thiết
- 🔴 Truy vấn con tương quan (Correlated subqueries) — phải thực thi lặp lại trên từng dòng
- 🔴 Áp dụng hàm lên các cột đã đánh chỉ mục — làm vô hiệu hóa chỉ mục (index bypass)
  (ví dụ: WHERE YEAR(created_at) = 2023)
- 🔴 Chuyển đổi kiểu dữ liệu ngầm định (Implicit type conversions) — âm thầm bỏ qua chỉ mục
- 🟠 Mệnh đề WHERE không thỏa mãn tính SARGable — tận dụng chỉ mục kém hiệu quả
- 🟠 Thiếu điều kiện JOIN — vô tình tạo ra tích Đề-các (cartesian products)
- 🟠 Lạm dụng DISTINCT — che đậy logic nối bảng (join) bị sai
- 🟡 Truy vấn con dư thừa — có thể thay thế bằng JOIN hoặc CTE
- 🟡 Dùng ORDER BY trong truy vấn con — xử lý sắp xếp vô ích
- 🔵 Ký tự đại diện đặt ở đầu từ khóa LIKE — ví dụ: WHERE name LIKE '%john'
- 🔵 Thiếu mệnh đề LIMIT trên các tập kết quả lớn
- 🔵 Lạm dụng toán tử OR — có thể thay thế bằng IN hoặc UNION

Mức độ Nghiêm trọng:
- 🔴 [Nghiêm trọng / Critical] — Kẻ hủy diệt hiệu năng hoặc rủi ro bảo mật lớn
- 🟠 [Cao / High]              — Tác động đáng kể đến hiệu năng
- 🟡 [Trung bình / Medium]     — Tác động vừa phải, vi phạm thực hành tốt nhất
- 🔵 [Thấp / Low]              — Cơ hội tối ưu hóa nhỏ

KIỂM TOÁN BẢO MẬT (SECURITY AUDIT):
| # | Rủi ro | Vị trí | Mức độ Nghiêm trọng | Cách Khắc phục Bắt buộc |
|---|------|----------|----------|-------------|

Các mục kiểm tra bảo mật:
- Lỗ hổng SQL injection do nối chuỗi trực tiếp hoặc đầu vào chưa được tham số hóa
- Câu truy vấn cấp quyền quá rộng làm lộ các cột dữ liệu nhạy cảm
- Thiếu cân nhắc về bảo mật cấp độ dòng (row-level security)
- Làm lộ dữ liệu nhạy cảm mà không có cơ chế che dấu (masking)

---

📊 BƯỚC 4 — Mô phỏng Kế hoạch Thực thi (Execution Plan Simulation)
Mô phỏng cách bộ máy cơ sở dữ liệu (database engine) sẽ xử lý câu truy vấn:

THỨ TỰ THỰC THI TRUY VẤN:
1. FROM & JOINs   : [Các bảng được truy cập, dự đoán chiến lược nối bảng]
2. WHERE          : [Các bộ lọc được áp dụng, dự đoán việc sử dụng chỉ mục]
3. GROUP BY       : [Chiến lược gom nhóm, có cần thao tác sắp xếp không?]
4. HAVING         : [Bộ lọc sau khi tổng hợp dữ liệu]
5. SELECT         : [Phân giải cột, tính toán các biểu thức]
6. ORDER BY       : [Thao tác sắp xếp, có nguy cơ xảy ra filesort không?]
7. LIMIT/OFFSET   : [Áp dụng giới hạn số dòng trả về]

PHÂN TÍCH CHI PHÍ THAO TÁC (OPERATION COST ANALYSIS):
| Thao tác | Loại | Chỉ mục Sử dụng | Chi phí Ước tính | Rủi ro |
|-----------|------|------------|---------------|------|

Các Loại Thao tác:
- ✅ Index Seek      — Tra cứu trúng đích, hiệu quả cao
- ⚠️  Index Scan     — Duyệt qua toàn bộ cây chỉ mục
- 🔴 Full Table Scan — Không dùng được chỉ mục, chi phí đắt đỏ nhất
- 🔴 Filesort        — Sắp xếp trên bộ nhớ/ổ đĩa, rất tốn tài nguyên
- 🔴 Temp Table      — Phải tạo bảng tạm lưu kết quả trung gian

DỰ ĐOÁN CHIẾN LƯỢC NỐI BẢNG (JOIN STRATEGY PREDICTION):
| Phép nối | Các Bảng | Chiến lược Dự đoán | Hiệu quả |
|------|--------|--------------------|------------|

Các Chiến lược Nối bảng:
- Nested Loop Join  — Tốt nhất cho các bảng nhỏ hoặc cột đã được đánh chỉ mục
- Hash Join         — Tốt nhất cho các tập dữ liệu lớn chưa được sắp xếp
- Merge Join        — Tốt nhất cho các tập dữ liệu đã được sắp xếp sẵn

ĐỘ PHỨC TẠP TỔNG THỂ:
- Chi phí Truy vấn Hiện tại : [Chi phí tương đối ước tính]
- Điểm nghẽn Chính          : [Mối lo ngại lớn nhất về hiệu năng]
- Tiềm năng Tối ưu hóa      : [Thấp / Trung bình / Cao / Rất lớn]

---

🗂️ BƯỚC 5 — Chiến lược Đánh chỉ mục (Index Strategy)
Đề xuất chiến lược đánh chỉ mục toàn diện:

KHUYẾN NGHỊ CHỈ MỤC (INDEX RECOMMENDATIONS):
| # | Bảng | Các Cột | Loại Chỉ mục | Lý do | Tác động Kỳ vọng |
|---|-------|---------|------------|--------|-----------------|

Các Loại Chỉ mục:
- B-Tree Index    — Mặc định, tốt nhất cho các truy vấn so sánh bằng và tìm theo khoảng
- Composite Index — Chỉ mục đa cột, thứ tự sắp xếp các cột có ý nghĩa quyết định
- Covering Index  — Bao gồm toàn bộ các cột trong truy vấn, tránh phải đọc ngược lại bảng gốc (table lookup)
- Partial Index   — Chỉ đánh chỉ mục trên một tập con các dòng (PostgreSQL/SQLite)
- Full-Text Index — Dùng để tối ưu hóa tìm kiếm văn bản / LIKE

CÁC CÂU LỆNH DDL CHÍNH XÁC:
Cung cấp các câu lệnh CREATE INDEX sẵn sàng để chạy ngay:
```sql
-- [Reason for this index]
-- Expected impact: [e.g., converts full table scan to index seek]
CREATE INDEX idx_[table]_[columns] 
ON [table]([column1], [column2]);

-- [Additional indexes as needed]
```

CẢNH BÁO VỀ CHỈ MỤC:
- Gắn cờ bất kỳ chỉ mục hiện có nào bị dư thừa hoặc không bao giờ được sử dụng
- Lưu ý tác động làm giảm hiệu năng ghi (INSERT/UPDATE/DELETE) khi thêm các chỉ mục mới
- Khuyến nghị các chỉ mục cần xóa bỏ (DROP) nếu chúng gây phản tác dụng

---

🔧 BƯỚC 6 — Câu Truy vấn Production Hoàn chỉnh (Final Production Query)
Cung cấp mã SQL hoàn chỉnh đã được tối ưu hóa/xây dựng sẵn sàng cho môi trường production:

Yêu cầu đối với Câu Truy vấn:
- Được viết theo đúng cú pháp chính xác của hệ quản trị CSDL và phiên bản đã chỉ định
- Giải quyết triệt để toàn bộ các phản mẫu phát hiện ở Bước 3
- Được tối ưu hóa dựa trên phân tích kế hoạch thực thi ở Bước 4
- Các đầu vào được tham số hóa bằng cú pháp chuẩn xác:
  · MySQL/PostgreSQL : %s hoặc $1, $2...
  · SQL Server       : @param_name
  · SQLite           : ? hoặc :param_name
  · Oracle           : :param_name
- Sử dụng biểu thức bảng chung (CTEs) thay cho các truy vấn con lồng nhau ở những nơi mang lại lợi ích
- Đặt tên bí danh (aliases) có ý nghĩa rõ ràng cho tất cả các bảng và cột
- Có chú thích nội tuyến giải thích các đoạn logic phức tạp không hiển nhiên
- Đưa vào mệnh đề LIMIT ở những nơi có khả năng trả về tập kết quả lớn

ĐỊNH DẠNG:
```sql
-- ============================================================
-- Query   : [Query Purpose]
-- Author  : Generated
-- DB      : [DB Flavor + Version]
-- Tables  : [Tables Used]
-- Indexes : [Indexes this query relies on]
-- Params  : [List of parameterised inputs]
-- ============================================================

[FULL OPTIMIZED SQL QUERY HERE]
```

---

📊 BƯỚC 7 — Thẻ Tóm tắt Truy vấn (Query Summary Card)

Tổng quan Truy vấn:
Chế độ               : [Xây dựng / Tối ưu hóa]
Cơ sở dữ liệu        : [Hệ quản trị + Phiên bản]
Số Bảng Liên quan    : [N]
Độ Phức tạp Truy vấn : [Đơn giản / Trung bình / Phức tạp]

SO SÁNH HIỆU NĂNG: [CHẾ ĐỘ TỐI ƯU HÓA]
| Chỉ số                  | Trước khi Tối ưu | Sau khi Tối ưu       |
|-------------------------|------------------|----------------------|
| Quét Toàn bộ Bảng       | ...              | ...                  |
| Sử dụng Chỉ mục         | ...              | ...                  |
| Chiến lược Nối bảng     | ...              | ...                  |
| Chi phí Ước tính        | ...              | ...                  |
| Số Phản mẫu Phát hiện   | ...              | ...                  |
| Vấn đề Bảo mật          | ...              | ...                  |

THẺ SỨC KHỎE TRUY VẤN: [CẢ HAI CHẾ ĐỘ]
| Hạng mục                | Trạng thái | Ghi chú                       |
|-------------------------|------------|-------------------------------|
| Độ bao phủ Chỉ mục      | ✅ / ⚠️ / ❌ | ...                       |
| Tham số hóa Đầu vào     | ✅ / ⚠️ / ❌ | ...                       |
| Xử lý Phản mẫu          | ✅ / ⚠️ / ❌ | ...                       |
| Hiệu quả Nối bảng       | ✅ / ⚠️ / ❌ | ...                       |
| An toàn SQL Injection   | ✅ / ⚠️ / ❌ | ...                       |
| Tối ưu theo Hệ CSDL     | ✅ / ⚠️ / ❌ | ...                       |
| Điểm Kế hoạch Thực thi  | ✅ / ⚠️ / ❌ | ...                       |

Chỉ mục Cần tạo mới : [N] — [liệt kê cụ thể]
Chỉ mục Cần xóa bỏ  : [N] — [liệt kê cụ thể]
Bản vá Bảo mật      : [N] — [liệt kê cụ thể]

Các Bước Tiếp theo Được Khuyến nghị:
- Chạy lệnh EXPLAIN / EXPLAIN ANALYZE để xác thực kế hoạch thực thi thực tế
- Giám sát hiệu năng truy vấn sau khi tạo chỉ mục
- Cân nhắc chiến lược lưu bộ nhớ đệm truy vấn (query caching) nếu được gọi với tần suất cao
- Câu lệnh dùng để phân tích: 
  · PostgreSQL : EXPLAIN ANALYZE [câu truy vấn của bạn];
  · MySQL      : EXPLAIN FORMAT=JSON [câu truy vấn của bạn];
  · SQL Server : SET STATISTICS IO, TIME ON;

---

🗄️ CHI TIẾT CƠ SỞ DỮ LIỆU CỦA TÔI:

Hệ Quản trị CSDL: [SPECIFY e.g., PostgreSQL 15]
Chế độ          : [Build Mode / Optimise Mode]

Lược đồ (dán các câu lệnh CREATE TABLE hoặc mô tả các bảng của bạn):
[PASTE SCHEMA HERE]

Yêu cầu Truy vấn hoặc Câu Truy vấn Hiện có:
[DESCRIBE WHAT YOU NEED OR PASTE EXISTING QUERY HERE]

Dữ liệu Mẫu (tùy chọn nhưng được khuyến nghị):
[PASTE SAMPLE ROWS IF AVAILABLE]