Cách Tìm và Phân Tích Các Câu Lệnh SQL Chậm trong MariaDB

Việc tìm ra và tối ưu hóa các câu lệnh SQL chạy chậm là một trong những nhiệm vụ quan trọng nhất để duy trì hiệu năng cao cho ứng dụng. Các câu lệnh chưa được tối ưu có thể tiêu tốn nhiều tài nguyên CPU, bộ nhớ và I/O, dẫn đến trải nghiệm người dùng kém. MariaDB cung cấp hai công cụ chính cực kỳ mạnh mẽ để giải quyết vấn đề này: Slow Query Log và Performance Schema.

Phương pháp 1: Sử dụng Slow Query Log (Phổ biến và Hiệu quả)

Đây là một file log đặc biệt, chỉ ghi lại những câu lệnh nào chạy vượt quá một khoảng thời gian do bạn định nghĩa. Đây là cách tiếp cận truyền thống và rất hiệu quả.

Bước 1: Kích hoạt và Cấu hình Slow Query Log

Đầu tiên, hãy kiểm tra xem tính năng này đã được bật hay chưa:

SHOW VARIABLES LIKE 'slow_query_log';

Nếu giá trị là OFF, bạn có thể bật tạm thời (cho đến khi restart server) bằng các lệnh sau:

-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';

-- Định nghĩa câu lệnh chạy lâu hơn 2 giây là "chậm"
SET GLOBAL long_query_time = 2; 

-- (Khuyến khích) Ghi lại cả những câu lệnh không sử dụng index
SET GLOBAL log_queries_not_using_indexes = 'ON';

[mysqld]
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log

Bước 2: Phân tích file Slow Query Log

Sau khi bật và để hệ thống chạy một thời gian, file log sẽ chứa đầy thông tin quý giá. Thay vì đọc trực tiếp, hãy dùng công cụ mysqldumpslow được cài đặt sẵn cùng MariaDB.

Liệt kê 10 câu lệnh tốn nhiều thời gian nhất:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

Liệt kê 10 câu lệnh được chạy thường xuyên nhất (gây chậm):

sudo mysqldumpslow -s c -t 10 /var/log/mysql/mariadb-slow.log

Liệt kê các câu lệnh quét nhiều dòng nhất (dấu hiệu của full-scan):

sudo mysqldumpslow -s r -t 10 /var/log/mysql/mariadb-slow.log

Phương pháp 2: Sử dụng Performance Schema (Hiện đại và Mạnh mẽ)

Performance Schema là một engine lưu trữ dữ liệu hiệu năng của server trong bộ nhớ. Nó cung cấp thông tin chi tiết hơn Slow Query Log và không cần ghi ra file.

Bước 1: Đảm bảo Performance Schema được bật

Hầu hết các phiên bản MariaDB mới đều bật sẵn. Nếu chưa, hãy thêm performance_schema = ON vào file my.cnf và khởi động lại.

Bước 2: Kích hoạt các "Consumers" cần thiết

Chúng ta cần yêu cầu Performance Schema lưu lại thông tin tóm tắt về các câu lệnh.

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements_%';

Bước 3: Truy vấn để tìm các câu lệnh tốn tài nguyên

Chạy câu lệnh SQL sau để lấy ra danh sách các câu lệnh tốn kém nhất, sắp xếp theo tổng thời gian chạy.

SELECT
    (SUM_TIMER_WAIT / 1000000000) / COUNT_STAR AS avg_latency_ms,
    COUNT_STAR AS exec_count,
    SUM_ROWS_SENT AS total_rows_sent,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    (SUM_TIMER_WAIT / 1000000000) AS total_latency_s,
    DIGEST_TEXT AS query_sample
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME != 'performance_schema'
ORDER BY
    total_latency_s DESC
LIMIT 20;

Bước Tiếp Theo: Phân Tích Chuyên Sâu Bằng EXPLAIN

Sau khi đã xác định được các câu lệnh "thủ phạm", bước tiếp theo là hiểu tại sao nó lại chạy chậm. Hãy dùng lệnh EXPLAIN để xem kế hoạch thực thi của MariaDB.

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01' ORDER BY last_name;

Những dấu hiệu cần chú ý trong kết quả của EXPLAIN:

• type: ALL: Cực kỳ tệ! Có nghĩa là MariaDB đang phải quét toàn bộ bảng (full table scan).
• key: NULL: Không có index nào được sử dụng.
• Extra: Using filesort: Phải dùng thuật toán sắp xếp chậm trên đĩa thay vì dùng index.
• Extra: Using temporary: Phải tạo bảng tạm để xử lý, rất tốn tài nguyên.
• rows: Số lượng dòng ước tính phải quét. Nếu con số này rất lớn, hãy xem xét lại.

Dựa vào kết quả của EXPLAIN, bạn sẽ biết cần phải làm gì, thường là thêm index vào các cột trong mệnh đề WHERE, JOIN, và ORDER BY.