Tối Ưu MySQL/MariaDB Trên VPS: Database Đang Chậm Như Rùa — Tôi Đã Tăng Tốc 8 Lần Chỉ Bằng Vài Dòng Config

1. Trang Checkout Mất 8 Giây Để Load — Khách Bỏ Đi Hết

Tháng trước, khách hàng của tôi gọi hoảng: "Website bán hàng tự nhiên chậm kinh khủng, trang checkout mất 8 giây mới load xong!" Tôi SSH vào VPS, gõ htop — CPU 20%, RAM 40%. Ổn. Gõ SHOW PROCESSLIST trong MySQL — 47 connections, 12 cái đang chạy query mất >5 giây. Mở slow query log — 3,847 queries chậm trong 24 giờ qua.

Nguyên nhân: database 300MB, bảng wp_postmeta 2.3 triệu dòng, không có một cái index nào ngoài PRIMARY KEY. Mỗi lần WooCommerce query biến thể sản phẩm, MySQL phải scan toàn bộ 2.3 triệu dòng — gọi là full table scan. Trên cái VPS 4GB RAM của tôi, việc này mất 4-8 giây.

Tôi thêm 3 indexes + tune my.cnf. Thời gian query giảm từ 8 giây xuống còn 0.9 giây — nhanh hơn gần 9 lần. Không cần nâng cấp VPS, không cần mua server mới. Bài viết này là toàn bộ quy trình tôi dùng — từ tìm query chậm, đánh index, đến cấu hình my.cnf tối ưu cho từng mức RAM.

2. Index Là Gì Và Tại Sao Thiếu Index Là Database Chết?

Index trong database giống như mục lục của cuốn sách. Không có mục lục, bạn phải lật từng trang để tìm một chương. Có mục lục, bạn mở đúng trang trong 1 giây. MySQL cũng vậy: không có index = full table scan (đọc từng dòng). Có index = B-tree lookup (tìm trong log₂(n) bước).

3. Cách Tìm Query Chậm Với Slow Query Log

Bật slow query log để MySQL ghi lại mọi query chạy quá N giây:

-- Kiểm tra trạng thái hiện tại
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Bật slow query log (tạm thời, mất sau restart)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- Ghi query >1 giây
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 1000;

Để cấu hình vĩnh viễn, thêm vào /etc/mysql/mariadb.conf.d/50-server.cnf:

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

Phân tích slow query log với pt-query-digest (Percona Toolkit):

# Cài Percona Toolkit
sudo apt install -y percona-toolkit

# Phân tích slow log
pt-query-digest /var/log/mysql/mariadb-slow.log

Output sẽ hiển thị top query chậm nhất, số lần chạy, thời gian trung bình — bạn biết chính xác query nào cần tối ưu.

4. Đánh Index Đúng Cách — Nghệ Thuật Hơn Khoa Học

4.1. Quy Tắc Vàng Khi Tạo Index

1. Index cột trong WHERE, JOIN, ORDER BY, GROUP BY. Đừng index cột chỉ xuất hiện trong SELECT.
2. Thứ tự cột trong composite index quan trọng. Index (A, B, C) dùng được cho query filter A, A+B, A+B+C — nhưng KHÔNG dùng được cho query chỉ filter B hoặc C.
3. Cardinality cao = index hiệu quả. Cột có 2 giá trị (yes/no) — index gần như vô dụng. Cột có 10,000 giá trị unique — index rất hiệu quả.
4. Dùng EXPLAIN để kiểm tra. EXPLAIN cho bạn biết MySQL dùng index nào, scan bao nhiêu dòng.
5. Không index mọi thứ. Mỗi index tốn ổ cứng và làm chậm INSERT/UPDATE/DELETE.

4.2. Ví Dụ Thực Tế: Tối Ưu Query WooCommerce

-- Query chậm (3-8 giây với 2.3M dòng)
SELECT meta_key, meta_value 
FROM wp_postmeta 
WHERE post_id = 12345 AND meta_key LIKE '_attribute%';

-- EXPLAIN cho thấy: type=ALL (full table scan), rows=2,300,000

Thêm index:

ALTER TABLE wp_postmeta ADD INDEX idx_post_meta (post_id, meta_key(50));

Sau khi thêm:

-- EXPLAIN: type=ref, rows=15, key=idx_post_meta
-- Thời gian: 0.009 giây (nhanh hơn 300-800 lần)

5. Cấu Hình my.cnf Theo RAM — Tận Dụng Từng MB

90% hiệu năng MySQL đến từ config, không phải từ hardware. MySQL mặc định được cấu hình cho server 512MB RAM từ thời 2005 — nếu bạn không tune, bạn đang dùng config cho Pentium 4.

5.1. VPS 2GB RAM

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 75
table_open_cache = 1024
table_definition_cache = 1024
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0

5.2. VPS 4GB RAM

[mysqld]
innodb_buffer_pool_size = 2.5G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 2
max_connections = 150
table_open_cache = 2048
table_definition_cache = 2048
tmp_table_size = 128M
max_heap_table_size = 128M
thread_cache_size = 16
query_cache_type = 0

5.3. VPS 8GB RAM (Web Bán Hàng)

[mysqld]
innodb_buffer_pool_size = 5G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 4
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
max_connections = 250
table_open_cache = 4096
table_definition_cache = 4096
tmp_table_size = 256M
max_heap_table_size = 256M
thread_cache_size = 32
join_buffer_size = 4M
sort_buffer_size = 4M
query_cache_type = 0

6. Query Optimization — Viết SQL Thông Minh Hơn

6.1. SELECT * Là Kẻ Thù Số 1

-- Tệ: đọc tất cả cột, tốn băng thông + memory
SELECT * FROM orders WHERE status = 'pending';

-- Tốt: chỉ đọc cột cần thiết
SELECT id, customer_name, total FROM orders WHERE status = 'pending';

6.2. Dùng LIMIT Khi Chỉ Cần Vài Dòng

-- Tệ: trả về 500,000 dòng, app chỉ cần 10
SELECT * FROM products WHERE category = 'phone';

-- Tốt
SELECT * FROM products WHERE category = 'phone' LIMIT 10;

6.3. EXISTS Thay Vì IN (Với Subquery Lớn)

-- Chậm: IN phải chạy subquery trước, lưu kết quả vào temp table
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE status = 'active'
);

-- Nhanh: EXISTS dừng ngay khi tìm thấy 1 match
SELECT * FROM orders o WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'active'
);

6.4. Tránh Hàm Trên Cột Trong WHERE

-- Tệ: không dùng được index (phải tính hàm trên từng dòng)
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-01';

-- Tốt: dùng được index
SELECT * FROM orders WHERE created_at >= '2026-07-01' 
  AND created_at < '2026-07-02';

6.5. JOIN Đúng Cách

-- Luôn JOIN trên cột đã có index
ALTER TABLE order_items ADD INDEX idx_order (order_id);

SELECT o.id, o.total, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id  -- order_items.order_id cần index
WHERE o.status = 'completed';

7. Theo Dõi Hiệu Năng Database Hằng Ngày

-- Tỉ lệ query dùng index
SHOW STATUS LIKE 'Handler_read_rnd_next';
-- Handler_read_rnd_next / Uptime = số lần đọc tuần tự/giây
-- Nếu con số này cao (>1000/s) => thiếu index

-- Tỉ lệ temporary table ghi ra disk
SHOW STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables / Created_tmp_tables > 25% => tăng tmp_table_size

-- Connection sử dụng
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- InnoDB buffer pool hit ratio (tỉ lệ cache hit)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests: tổng số lần đọc
-- Innodb_buffer_pool_reads: số lần phải đọc từ disk (không tìm thấy trong cache)
-- Hit ratio = 1 - (reads / read_requests), mục tiêu > 99%

8. So Sánh MySQL vs MariaDB vs PostgreSQL

Với WordPress/WooCommerce: MariaDB là lựa chọn tốt nhất cho VPS. Nhanh hơn MySQL 5.7, miễn phí, không bị Oracle kiểm soát. Nếu bạn đang dùng MySQL 5.7 trên Ubuntu cũ — migrate sang MariaDB 11.x ngay (nhanh hơn 20-30% với cùng config).

9. Kết Luận

Database chậm là vấn đề phổ biến nhất trên VPS — và cũng là vấn đề dễ fix nhất nếu bạn biết cách. Ba bước đơn giản tôi làm mỗi khi nhận VPS mới: (1) Tune my.cnf theo RAM; (2) Bật slow query log, tìm top 10 query chậm nhất; (3) Đánh index cho những query đó. 90% trường hợp, database tăng tốc 2-10 lần chỉ với 3 bước này.

Đừng vội nâng cấp VPS khi thấy website chậm. Mở slow query log trước đã. Có khi bạn đang trả 500K/tháng cho VPS 8GB mà database vẫn chậm vì thiếu 1 cái index 50KB.