1. Giới thiệu về tối ưu hóa

Không có quy tắc tuyệt đối trong tối ưu hóa MySQL. Mỗi giải pháp phải được kiểm chứng qua thử nghiệm trong bối cảnh nghiệp vụ cụ thể. Bài viết này cung cấp định hướng và phương pháp luận, không phải công thức chung.

1.1 Vai trò của MySQL

MySQL là hệ quản trị cơ sở dữ liệu quan hệ phổ biến nhờ hiệu năng cao, chi phí thấp và cộng đồng hỗ trợ lớn. Hầu hết các nền tảng lớn như Google, Facebook, Alibaba đều sử dụng MySQL làm hệ thống lưu trữ chính. Trong các ứng dụng thực tế, tỷ lệ đọc/ghi thường là 10:1, do đó việc tối ưu truy vấn phức tạp trở thành ưu tiên hàng đầu.

1.2 Lưu ý khi tối ưu

• Rủi ro: Tối ưu trên hệ thống đang vận hành có thể gây gián đoạn. Mọi thay đổi cần được đánh giá kỹ lưỡng.
• Mục tiêu: Ổn định hệ thống quan trọng hơn cải thiện hiệu năng. Tối ưu là nỗ lực tập thể từ DBA, developer đến quản trị hệ thống.

2. Các chiều hướng tối ưu

Tối ưu MySQL được chia thành 4 lớp:

1. Phần cứng: CPU, RAM, SSD, mạng
2. Cấu hình hệ thống: Tham số hệ điều hành và MySQL
3. Cấu trúc bảng: Thiết kế schema, phân vùng, engine
4. Truy vấn và chỉ mục: SQL, index (hiệu quả nhất về mặt chi phí)

2.1 Công cụ phân tích

-- Kiểm tra hoạt động
SHOW PROCESSLIST;
EXPLAIN SELECT ...;
SHOW INDEX FROM table_name;
SHOW STATUS LIKE '%slow%';

-- Phân tích log chậm
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- Giám sát chuyên sâu
pt-query-digest slow.log
sysbench --test=oltp run

3. Tối ưu truy vấn

3.1 Cơ chế thực thi truy vấn

1. Client gửi truy vấn đến server
2. Kiểm tra Query Cache (nếu bật)
3. Phân tích cú pháp và tiền xử lý
4. Tối ưu hóa truy vấn
5. Thực thi qua Storage Engine
6. Trả kết quả về client

3.2 Phân tích truy vấn chậm

Bật slow query log trong my.cnf:

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

3.3 Sử dụng EXPLAIN

Phân tích kế hoạch thực thi:

EXPLAIN FORMAT=JSON 
SELECT u.name, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'completed';

Các chỉ số quan trọng:

• type: ALL → index → range → ref → eq_ref → const (tốt nhất)
• rows: Số dòng dự kiến quét
• Extra: Cảnh báo "Using filesort", "Using temporary"

4. Tối ưu chỉ mục

4.1 Các loại chỉ mục

4.2 Nguyên tắc sử dụng

• Chỉ mục tổ hợp tuân theo nguyên tắc tiền tố trái
• Tránh chỉ mục trên cột có độ phân tán thấp (giới tính, trạng thái)
• Ưu tiên chỉ mục cho cột trong WHERE, JOIN, ORDER BY

5. Tối ưu Storage Engine

5.1 So sánh InnoDB vs MyISAM

5.2 Mẹo tối ưu nhập liệu hàng loạt

-- Tắt kiểm tra để tăng tốc
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

-- Chèn dữ liệu
INSERT INTO logs (...) VALUES (...), (...), ...;

-- Bật lại kiểm tra
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;
SET autocommit = 1;

6. Tối ưu cấu trúc bảng

• Sử dụng kiểu dữ liệu nhỏ nhất phù hợp (TINYINT thay INT)
• Tránh NULL, dùng DEFAULT thay thế
• Giới hạn số cột (<20 cột/bảng)
• Phân vùng bảng theo thời gian hoặc hash

7. Tối ưu phần cứng

• RAM: Tăng innodb_buffer_pool_size lên 70-80% RAM vật lý
• SSD: Ưu tiên PCIe NVMe cho IOPS cao
• CPU: Tắt chế độ tiết kiệm năng lượng
• Mạng: Dùng card 10GbE cho cluster

8. Tối ưu bộ nhớ đệm

8.1 Cấu hình quan trọng

[mysqld]
# Bộ đệm InnoDB
innodb_buffer_pool_size = 12G

# Bộ đệm MyISAM
key_buffer_size = 512M

# Kết nối
thread_cache_size = 100
table_open_cache = 4000

# Tắt DNS lookup
skip-name-resolve

8.2 Tối ưu hệ điều hành

• Mount ổ đĩa với tùy chọn noatime,nodirtime
• Sử dụng filesystem XFS thay ext4
• Đặt vm.swappiness=1 để tránh swap