Quản trị và Tối ưu Hệ thống Cơ sở Dữ liệu với MySQL

Hiệu năng và tối ưu hoá

🧭 1️⃣ Tổng quan về hiệu năng trong MySQL

Hiệu năng (performance) trong MySQL là khả năng phản hồi nhanh, ổn định và sử dụng tài nguyên hợp lý.
Ba trụ cột ảnh hưởng chính là:

Nhóm yếu tố	Ví dụ	Ghi chú
Cấu trúc dữ liệu	Index, kiểu dữ liệu, schema design	Thiết kế sai là nguyên nhân gốc gây chậm
Cấu hình server	Buffer Pool, Query Cache, Thread Cache	Ảnh hưởng trực tiếp đến tốc độ
Truy vấn & ứng dụng	SQL, join, subquery, I/O patterns	Có thể tối ưu mà không thay code server
Tải hệ thống (Workload)	Số kết nối, song song, batch job	DBA cần theo dõi thường xuyên

⚙️ 2️⃣ Công cụ trong MySQL Workbench hỗ trợ tối ưu

Workbench cung cấp 3 nhóm công cụ chính cho hiệu năng:

🧩 A. Performance Dashboard

Truy cập: Workbench → Performance Reports → Dashboard
Hiển thị các thông số real-time:
- CPU, Memory usage
- InnoDB Buffer Pool hit ratio
- Connections, Threads, Queries/s
Mục tiêu: phát hiện nhanh điểm nghẽn (bottleneck).

🧩 B. Performance Schema Reports

Truy cập: Performance Reports → performance_schema → [category]
Tận dụng dữ liệu từ performance_schema:
- Top I/O by table
- Top SQL by latency
- File I/O summary
- Events waits, locks, memory usage
Có thể click drill-down vào từng query để xem thời gian trung bình, max, số lần gọi.

🧩 C. Query Profiler / Query Analyzer

Truy cập: tab “Query” → “Explain Plan” → “Visual Explain”
Cho phép phân tích cách MySQL thực thi truy vấn:
- Sử dụng index nào?
- Có full table scan không?
- Bao nhiêu rows được truy xuất thực tế?
Kết hợp với:

EXPLAIN ANALYZE SELECT ...;

để xem thời gian thực tế từng bước thực hiện (MySQL 8.0+).

🔍 3️⃣ Các bước DBA thường làm trong Workbench để tối ưu

Bước	Mục tiêu	Công cụ / Lệnh hỗ trợ
1. Xác định truy vấn chậm (slow query)	Phát hiện điểm nghẽn	`Performance Reports → Top SQL by latency` hoặc `slow_query_log`
2. Phân tích kế hoạch thực thi (EXPLAIN)	Kiểm tra index, join type	`EXPLAIN` / `EXPLAIN ANALYZE`
3. Xem tần suất & thời gian chạy	Ưu tiên query tốn CPU/I/O nhiều nhất	`events_statements_summary_by_digest`
4. Kiểm tra cấu hình InnoDB	Buffer Pool, log file size	`SHOW VARIABLES LIKE 'innodb%';`
5. Tối ưu index	Tránh full scan, thêm composite index	Workbench → “Table → Indexes” tab
6. Theo dõi session / lock	Phát hiện deadlock / blocking	`Performance Schema → Threads`, `Events Waits`
7. Đánh giá cấu hình server	Xem khuyến nghị	Workbench → `Performance Reports → Server Status` hoặc MySQL Tuner script (ngoài Workbench)

⚡ 4️⃣ Các kỹ thuật tối ưu cụ thể

🧮 A. Tối ưu câu lệnh SQL

Dùng EXPLAIN ANALYZE để đo chi tiết thời gian từng bước.
Ưu tiên sử dụng index (avoid LIKE '%abc' hoặc OR nhiều điều kiện).
Giảm JOIN không cần thiết, chỉ lấy cột cần thiết.
Sử dụng LIMIT, covering index, CTE hợp lý (với MySQL 8).

💾 B. Tối ưu cấu hình hệ thống

Một số biến hệ thống quan trọng:

Biến	Vai trò	Gợi ý tối ưu
`innodb_buffer_pool_size`	Cache data/index	60–70% RAM nếu DB chính là MySQL
`query_cache_size`	Cache kết quả query (MySQL < 8)	Tắt trong MySQL 8 (đã loại bỏ)
`innodb_log_file_size`	Ghi log redo	Cỡ 256MB–1GB tùy workload
`max_connections`	Số session đồng thời	Giới hạn hợp lý để tránh OOM
`tmp_table_size` / `max_heap_table_size`	Kích thước bảng tạm trong RAM	Tăng nếu query phức tạp

🧠 C. Tối ưu thiết kế CSDL

Chuẩn hóa (Normalization) để tránh trùng dữ liệu, sau đó phi chuẩn hóa hợp lý khi cần performance.
Chọn kiểu dữ liệu nhỏ nhất đủ dùng (INT vs BIGINT).
Sử dụng phân vùng (Partitioning) cho bảng lớn (theo thời gian, ID).

📈 5️⃣ Theo dõi và đánh giá sau tối ưu

Sau khi tối ưu, DBA nên:

So sánh kết quả trước/sau bằng Workbench Performance Reports.
→ So sánh tổng thời gian query, CPU usage.
Lưu snapshot hiệu năng bằng performance_schema hoặc sys schema.
Tạo biểu đồ theo dõi (Dashboard) trong Workbench hoặc Grafana (qua exporter).
Ghi lại thay đổi cấu hình / query / index trong ADMIN_DB.job_log để có lịch sử audit.

🧩 6️⃣ Gợi ý thực hành (lab nhanh)

Mục tiêu: Phát hiện và tối ưu truy vấn chậm trong Workbench.

Bước thực hiện:

Mở Workbench → Performance Reports → Top SQL by latency
Chọn 1 truy vấn tốn thời gian nhất.
Click → “Visual Explain” để xem kế hoạch thực thi.
Nếu thấy type=ALL (full scan), tạo index phù hợp:

CREATE INDEX idx_customer_email ON customers(email);
Rerun và so sánh thời gian thực hiện.

Prev An toàn và toàn vẹn dữ liệu – Bảo mật – Backup

Next Đồng bộ và phục hồi

Giới thiệu và khởi tạo môi trường MySQL

Quản trị dữ liệu trong doanh nghiệp

Chiến lược quản trị dữ liệu