VPS上的MySQL/MariaDB优化指南 — 加速数据库

MySQL和MariaDB是VPS服务器上使用最广泛的数据库，但其默认配置是为内存较少的机器设计的，不适合现代云VPS环境。本指南将引导您逐步完成最有影响力的调优步骤，以最大化数据库性能。

MySQL为什么慢

在调优之前，了解根本原因至关重要。大多数默认MySQL安装存在以下常见问题：

调优InnoDB缓冲池

InnoDB缓冲池是最重要的MySQL调优参数。它将表数据和索引缓存在RAM中，避免昂贵的磁盘读取。在RAM允许的范围内，越大越好。

检查当前缓冲池使用情况：

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"

关键my.cnf设置

MySQL/MariaDB的主配置文件位于 /etc/mysql/my.cnf 或 /etc/my.cnf，具体取决于您的发行版。以下是经过良好调优的基础配置：

[mysqld]
# 缓冲池 — 根据可用RAM调整
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# 日志文件 — 更大 = 更少检查点
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# I/O设置
innodb_flush_log_at_trx_commit = 2    # 减少I/O（崩溃损失约1秒数据）
innodb_flush_method = O_DIRECT        # 避免OS双重缓冲

# 连接
max_connections = 150                  # 从默认151-200减少
thread_cache_size = 16

# 查询缓存（仅MariaDB — MySQL 8已移除）
# query_cache_type = 1
# query_cache_size = 64M
# query_cache_limit = 2M

# 临时表 — 保持在RAM中
tmp_table_size = 64M
max_heap_table_size = 64M

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 二进制日志 — 如不使用复制则禁用
# skip-log-bin

修改后重启MySQL：

sudo systemctl restart mysql
# 或MariaDB
sudo systemctl restart mariadb

启用慢查询日志

慢查询日志是识别瓶颈查询最强大的工具。开始调优前启用它，然后在几小时的生产流量后进行分析。

# 检查当前状态
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

# 无需重启即可启用
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

使用 mysqldumpslow 分析：

# 显示按总时间排序的10个最慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 显示执行最频繁的慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

索引和查询优化

良好的索引可以将查询速度提升100-1000倍。使用 EXPLAIN 验证查询是否有效使用索引。

-- 检查查询执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- 显示现有索引
SHOW INDEX FROM orders;

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 删除未使用的索引
DROP INDEX old_index_name ON orders;

查找未使用的索引

-- MySQL 8 / MariaDB 10.6+
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema','mysql','sys','information_schema');

使用MySQLTuner

MySQLTuner自动分析您的MySQL/MariaDB安装并推荐调优值。运行前至少需要24小时的生产流量数据，以获得有意义的建议。

# 下载
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# 运行
perl mysqltuner.pl --user root --pass your_password

输出会标注：

• [!!] — 需要立即处理的严重问题
• [OK] — 已正确配置的参数
• Variables to adjust — 带有具体数值的推荐调优值

MariaDB与MySQL比较

对于新的VPS部署，MariaDB值得考虑作为MySQL的替代品：

性能监控

调优后，持续监控以验证改进效果：

# 实时状态
mysqladmin -u root -p status

# 活跃进程
SHOW PROCESSLIST;

# 关键全局状态计数器
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

计算缓冲池命中率

-- 应该超过99%
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
  AS buffer_pool_hit_rate
FROM (
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

优化清单

常见问题