Thứ Bảy, 31 tháng 5, 2025

19.Tối ưu Truy vấn Chậm (SQL Tuning) trong PostgreSQL

✅ 1. Mục tiêu tối ưu truy vấn

Mục tiêuLý do
Rút ngắn thời gian truy vấnTăng trải nghiệm người dùng
Giảm tài nguyên tiêu thụGiảm CPU, RAM, Disk
Tăng thông lượngPhục vụ nhiều session hơn
Tránh full scanTận dụng chỉ mục, cache

🔍 2. Phát hiện truy vấn chậm

🔧 a. Bật log truy vấn chậm trong postgresql.conf

conf
log_min_duration_statement = 500    # log truy vấn > 500ms
log_statement = 'none'

🔧 b. Kích hoạt pg_stat_statements

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

📊 c. Xem truy vấn chậm nhất

sql
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 5;

📌 3. Dùng EXPLAIN để xem kế hoạch thực thi

🧪 Ví dụ:

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Kết quả bạn cần lưu ý:

Chỉ sốÝ nghĩa
Seq ScanĐọc toàn bộ bảng → cần index
Index ScanDùng index hiệu quả
Rows Removed by FilterHàng bị loại → chi phí
BuffersI/O sử dụng
Actual timeThời gian thực tế

🛠️ 4. Kỹ thuật tối ưu truy vấn phổ biến

Kỹ thuậtVí dụLợi ích
Tạo Index phù hợpCREATE INDEX idx_orders_cust ON orders(customer_id);Tránh Seq Scan
Partial IndexCREATE INDEX idx_active ON users(last_login) WHERE active=true;Index nhỏ, phù hợp truy vấn
Composite IndexCREATE INDEX idx_multi ON orders(cust_id, created_at);Phù hợp WHERE nhiều cột
Avoid SELECT *SELECT id, name thay vì SELECT *Giảm I/O
LIMIT với offset nhỏLIMIT 100 vs OFFSET 100000OFFSET lớn chậm
WITH (CTE) & subquery tối ưuTránh tính toán lại nhiều lầnHiệu quả xử lý
Avoid functions in WHEREWHERE created_at::date = '2024-01-01' ❌Tránh cản index

🧠 5. Cách kiểm tra hiệu quả của Index

sql
SELECT relname, idx_scan, seq_scan, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan;

Nếu seq_scan >> idx_scan → Cần xem lại indexing

🧾 6. Theo dõi truy vấn thường xuyên (bằng log hoặc dashboard)

Công cụMô tả
pg_stat_statementsTổng hợp truy vấn chậm
pgBadgerPhân tích file log thành HTML
EXPLAIN ANALYZEKế hoạch thực thi chi tiết
pg_hint_planGợi ý plan cụ thể (nâng cao)
auto_explainGhi tự động truy vấn chạy lâu

📋 7. Checklist tối ưu SQL định kỳ

✅ Hàng tuần

Công việcCông cụ
Xem top 5 truy vấn chậmpg_stat_statements
EXPLAIN truy vấn bất thườngEXPLAIN ANALYZE
Check log slow query/var/lib/pgsql/.../log/

✅ Hàng tháng

Công việcCông cụ
Review index không dùngpg_stat_user_indexes
Tuning query cho dashboardpgBadger, Grafana
Đề xuất thêm indexTheo seq_scan, rows removed by filter

📌 8. Template tối ưu hóa truy vấn (SQL Checklist)

Hạng mụcĐã làm (✅/❌)
Có index cho cột WHERE, JOIN?
Truy vấn có cần tất cả các cột (SELECT *)?
Tránh dùng hàm trong WHERE?
Đã EXPLAIN truy vấn trước khi triển khai?
Đã so sánh các chỉ số seq_scan, idx_scan?
Truy vấn có ORDER BY trên index đã có?

✅ Kết luận

  • Tối ưu truy vấn là công việc liên tục

  • Dễ thực hiện nếu:

    • Hiểu EXPLAIN

    • Biết chỉ mục đang dùng và không dùng

    • Theo dõi pg_stat_statements, log

  • Ưu tiên: Index đúng, cấu trúc SQL tốt, hạn chế thao tác trên bảng lớn không index

✅ 1. Mẫu báo cáo 10 truy vấn chậm nhất (từ pg_stat_statements)

📋 Câu lệnh SQL báo cáo:

sql
SELECT
  query,
  calls,
  total_time::numeric(12,2) AS total_ms,
  mean_time::numeric(10,2) AS avg_ms,
  rows,
  round((100.0 * total_time / sum(total_time) OVER ()), 2) AS pct_load
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
  AND query NOT ILIKE '%pg_%'
  AND query NOT ILIKE 'SET%'
ORDER BY total_time DESC
LIMIT 10;

✅ Ý nghĩa:

  • total_time: tổng thời gian hệ thống dành cho câu SQL đó

  • mean_time: thời gian trung bình/câu lệnh

  • pct_load: % tải CPU hệ thống do câu SQL đó gây ra

🔁 2. Script tự động phân tích top 10 truy vấn chậm hàng tuần

📜 File: report_top_query.sh

bash
#!/bin/bash

DATE=$(date +%F)
REPORT_DIR="/var/log/pg_reports"
REPORT_FILE="$REPORT_DIR/slow_queries_$DATE.txt"

mkdir -p $REPORT_DIR

psql -U postgres -d your_db_name -Atc "
SELECT
  '--- Top 10 slow queries ---' UNION ALL
SELECT
  'Query: ' || left(query, 100) ||
  ' | Calls: ' || calls ||
  ' | Avg Time: ' || round(mean_time,2) || 'ms'
FROM pg_stat_statements
WHERE mean_time > 100 AND query NOT ILIKE '%pg_%'
ORDER BY mean_time DESC
LIMIT 10;
" > $REPORT_FILE

🧪 Cron job chạy mỗi tuần (vào Chủ Nhật 23:00)

bash
crontab -e

0 23 * * 0 /path/to/report_top_query.sh

📨 Tùy chọn gửi báo cáo qua Telegram:

Thêm cuối file report_top_query.sh:

bash
TOKEN="123456789:ABCxyz..."
CHAT_ID="-1001234567890"
curl -s -X POST "https://api.telegram.org/bot$TOKEN/sendMessage" \
     -d chat_id="$CHAT_ID" \
     -d text="$(tail -n 12 $REPORT_FILE)"

✅ Kết quả đầu ra:

text
--- Top 10 slow queries ---
Query: SELECT * FROM orders WHERE customer_id = $1 LIMIT 100 | Calls: 125000 | Avg Time: 458.45ms
Query: UPDATE accounts SET ... | Calls: 134 | Avg Time: 812.17ms
...

✅ Hướng dẫn gửi email kèm file báo cáo


Bạn có thể dùng sendEmail hoặc mailx trong cronjob như sau (giả sử dùng mailx):


bash
echo "Báo cáo truy vấn chậm tuần này" | mailx -s "PostgreSQL Slow Queries Report" \
  -a /mnt/data/slow_queries_report_2025-05-20.xlsx \
  you@example.com
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master