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