Đã đăng vào Thứ Năm, 3:35 SA 3 phút đọc

224

Các kỹ thuật SQL nâng cao dành cho Senior

1. Tối ưu hóa truy vấn (Query Optimization)

1.1. Sử dụng `EXPLAIN ANALYZE`

Giúp kiểm tra kế hoạch thực thi của truy vấn, xác định các vấn đề như full table scan, nested loops, hoặc index miss.
Ví dụ:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

Phân tích kết quả

Seq Scan (Sequential Scan): Nếu thấy Seq Scan, tức là truy vấn quét toàn bộ bảng → Cần index.
Index Scan: Nếu có chỉ mục, PostgreSQL sẽ dùng Index Scan để tìm nhanh hơn.
Nested Loop / Hash Join / Merge Join: Xác định kiểu join tối ưu.

💡 Cách khắc phục nếu thấy Seq Scan không mong muốn:

Thêm index:

CREATE INDEX idx_customer_id ON orders(customer_id);

Dùng ANALYZE để cập nhật thống kê dữ liệu:
```
ANALYZE orders;
```

1.2. Indexing nâng cao

Index giúp tăng tốc truy vấn, nhưng có nhiều loại index tối ưu cho từng tình huống.

1.2.1. Partial Index (Chỉ mục một phần)

Chỉ tạo index cho dữ liệu thường xuyên được truy vấn.

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

🔹 Lợi ích: Tiết kiệm bộ nhớ và tối ưu hóa hiệu suất cho truy vấn có điều kiện.

1.2.2. Covering Index (Index Only Scan)

Giúp tránh đọc lại dữ liệu từ bảng.

CREATE INDEX idx_orders ON orders(customer_id, created_at);

Khi truy vấn:

SELECT customer_id, created_at FROM orders WHERE customer_id = 123;

👉 PostgreSQL có thể trả về kết quả chỉ từ index mà không cần truy cập bảng.

1.2.3. GIN Index (Tối ưu JSON & Full-text Search)

Được dùng cho dữ liệu JSON, mảng, hoặc tìm kiếm văn bản.

CREATE INDEX idx_json ON products USING gin(data_column);

Truy vấn nhanh hơn:

SELECT * FROM products WHERE data_column @> '{"category": "Electronics"}';

1.3. Materialized Views

Lưu trữ kết quả truy vấn phức tạp để tái sử dụng.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
FROM orders
GROUP BY 1;

Cập nhật dữ liệu:

REFRESH MATERIALIZED VIEW monthly_sales;

🔹 Lợi ích: Giảm thời gian xử lý nếu truy vấn phải chạy nhiều lần.

2. Kỹ thuật truy vấn nâng cao

2.1. Common Table Expressions (CTE)

CTE giúp viết truy vấn dễ đọc hơn, đặc biệt với đệ quy.

Ví dụ: Truy vấn cây phân cấp nhân viên

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

👉 Trả về tất cả nhân viên cấp dưới của một quản lý.

2.2. Window Functions

Window Functions giúp tính toán trên nhiều dòng mà không cần GROUP BY.

Ví dụ: Xếp hạng đơn hàng theo tổng tiền

SELECT id, customer_id, total_price,
       RANK() OVER (PARTITION BY customer_id ORDER BY total_price DESC) AS rank
FROM orders;

✅ RANK() OVER (PARTITION BY customer_id ORDER BY total_price DESC)
→ Xếp hạng đơn hàng theo khách.

Các hàm phổ biến:

ROW_NUMBER(): Gán số thứ tự duy nhất cho mỗi dòng.
LAG(), LEAD(): Truy vấn dữ liệu trước/sau một dòng.
SUM() OVER(), AVG() OVER(): Tổng và trung bình cộng.

2.3. Pivot và Unpivot (Dynamic Pivot)

Dùng để chuyển đổi hàng thành cột.

Ví dụ: Doanh thu từng loại sản phẩm theo năm

SELECT * FROM (
  SELECT year, category, revenue FROM sales
) s
PIVOT (
  SUM(revenue) FOR category IN ('Food', 'Clothes', 'Electronics')
) p;

3. Tối ưu hóa giao dịch (Transactions & Concurrency)

3.1. Transaction Savepoints

Dùng SAVEPOINT để rollback từng phần.

BEGIN;
INSERT INTO orders(customer_id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO payments(order_id, amount) VALUES (currval('orders_id_seq'), 100);
ROLLBACK TO sp1;  -- Hủy phần thanh toán nhưng giữ đơn hàng
COMMIT;

3.2. Isolation Levels & Locking

Chọn mức độ cô lập giao dịch để tránh xung đột.

Ví dụ: Cấp độ `REPEATABLE READ` trong PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

👉 FOR UPDATE giúp khóa hàng để tránh update đồng thời.

3.3. Partitioning Tables

Chia bảng theo thời gian hoặc danh mục để tăng hiệu suất.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

4. Tích hợp SQL với hệ thống khác

4.1. PostgreSQL Foreign Data Wrapper (FDW)

Kết nối đến database khác.

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'testdb');
CREATE USER MAPPING FOR current_user SERVER remote_db OPTIONS (user 'dbuser', password 'secret');
IMPORT FOREIGN SCHEMA public FROM SERVER remote_db INTO local_schema;

4.2. JSON & JSONB Query

SELECT data->>'name' AS name FROM products WHERE data->>'category' = 'Electronics';

4.3. Full-Text Search

CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database & performance');

5. Tự động hóa & Monitoring

5.1. Event-Driven SQL với Triggers

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

5.2. Logging & Query Monitoring

SET log_statement = 'all';
SELECT * FROM pg_stat_activity;

Tóm tắt

✅ Tối ưu truy vấn với Indexing, EXPLAIN ANALYZE, Materialized Views
✅ Truy vấn nâng cao với CTE, Window Functions, Pivot
✅ Kiểm soát giao dịch với Savepoints, Partitioning, Isolation Levels
✅ Tích hợp FDW, JSONB, Full-Text Search
✅ Tự động hóa với Triggers, Logging

SQL