VPS 上的 MySQL/MariaDB 调优 — 数据库性能指南

MySQL 和 MariaDB 驱动着全球数以百万计的网站和应用，但使用默认配置值的全新安装往往并不理想——尤其是在内存和 CPU 资源有限的 VPS 上。针对可用资源和应用实际负载，调整 my.cnf 中的正确参数，可将查询吞吐量提升 2 倍至 10 倍以上。本指南涵盖 VPS 服务器上 MySQL/MariaDB 的实用调优方法，包含真实命令、推荐参数值以及可直接用于生产环境的配置示例。

数据库调优最重要的原则是：不存在放之四海而皆准的"最优"配置。正确的设置取决于总内存大小、并发连接数，以及工作负载是读密集型（如博客或内容站点）还是写密集型（如电商平台或数据分析系统）。本指南的建议适用于运行标准 LAMP 堆栈（配合 WordPress 或类似 PHP 应用）的中端 VPS 服务器（2–8GB 内存）。

开始前先了解 my.cnf

MySQL/MariaDB 的主配置文件通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf。在 Ubuntu 和 Debian 系统上，也可能位于 /etc/mysql/mysql.conf.d/mysqld.cnf。请始终确认实际加载的是哪个文件：

mysql --verbose --help | grep "my.cnf"
# Or check directly
mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options"

在进行任何更改之前，请先为当前配置创建一个带日期的备份：

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

配置文件分为多个区块。[mysqld] 区块控制服务器守护进程的行为，大多数调优参数都应放在这里。[mysql] 和 [client] 区块用于配置命令行客户端工具。将参数放错区块将不会生效——编辑时请仔细核对区块标题。

调优前收集基准数据

良好的调优始于数据测量。收集基准数据，以便对比更改前后的性能差异。以下命令可获取关键指标：

# Show current variable values
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb%';"
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'key_buffer%';"

# Show global status counters
mysql -u root -p -e "SHOW GLOBAL STATUS;"

# Show currently running queries
mysql -u root -p -e "SHOW PROCESSLIST;"

# Show detailed InnoDB engine status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

同时检查操作系统层面的资源使用情况：

# Available and used RAM
free -h

# Top processes by memory consumption
ps aux --sort=-%mem | head -10

# MySQL data directory size
du -sh /var/lib/mysql/*

最重要的设置 — InnoDB 缓冲池

对于任何使用 InnoDB 表的服务器（这是所有现代 MySQL/MariaDB 部署的推荐默认选项），innodb_buffer_pool_size 无疑是影响最大的配置参数。它决定了 MySQL 为在内存中缓存表数据和索引所保留的内存量。默认值 128MB 对于任何超过几百兆字节的数据库来说都远远不够。

设置 innodb_buffer_pool_size 的参考建议：

• MySQL 是 VPS 上唯一的主要服务 → 分配总内存的 70–80%
• 运行 LAMP 堆栈（Apache + PHP-FPM + MySQL）→ 分配总内存的 50–60%
• 内存为 1GB 或以下的 VPS → 限制在 256M–512M，以保证操作系统和其他服务正常运行

适用于运行 LAMP 的 4GB 内存 VPS 的完整 InnoDB 配置块：

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2   # 1 instance per 1GB of buffer pool is recommended
innodb_log_file_size = 256M        # Larger = better write performance, longer crash recovery
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 1=fully ACID, 2=faster (risk: lose last second on crash)
innodb_flush_method = O_DIRECT     # Bypass OS page cache to avoid double-buffering

连接设置 — 处理并发用户

MySQL 默认的 max_connections 值为 151，对于小型站点已够用，但对于高流量部署或托管多个 WordPress 站点的服务器则明显不足。然而，提高连接限制并非没有代价——每个空闲连接大约消耗 1–2MB 内存，而活跃连接根据每连接缓冲区大小可能消耗更多。在提高此值之前，请仔细估算内存预算。

[mysqld]
max_connections = 200              # Suitable for a 4GB RAM LAMP VPS
thread_cache_size = 50             # Reuse threads to reduce creation overhead
thread_stack = 192K
back_log = 128                     # Connection queue depth when all threads are busy

# Per-connection buffers (allocated on demand, multiplied by active connections)
sort_buffer_size = 2M              # Used when sorting result sets without an index
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

估算 MySQL 最大内存占用的粗略公式：

# Max memory ≈ innodb_buffer_pool_size + (max_connections × per_connection_memory)
# Example: 2048M + (200 × 7M) = ~3.4GB
# This must not exceed total VPS RAM minus ~1GB reserved for the OS

启用与分析慢查询日志

慢查询日志是发现问题查询最有效的工具。无论缓冲池调得多好，都无法弥补一条对百万行表做全表扫描的查询所带来的性能损耗。建议先启用慢查询日志，让它收集一两天数据，再进行分析，找出真正的瓶颈所在。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1               # Log queries that take longer than 1 second
log_queries_not_using_indexes = 1 # Also log queries that skip indexes
min_examined_row_limit = 100      # Skip logging queries that examine fewer than 100 rows

重启 MySQL 并让其收集一段时间数据后，使用 mysqldumpslow 分析日志：

# Top 10 queries by total execution time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 most frequently executed queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Top 10 queries by total lock time
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

找到慢查询后，使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM orders
  WHERE user_id = 123
  AND status = 'pending'
  ORDER BY created_at DESC;

-- If type=ALL or rows is very high, an index is missing.
-- Add an appropriate composite index:
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

按 VPS 内存大小推荐的参数值

下表提供了运行 LAMP 堆栈的常见 VPS 内存配置的初始参考值，请根据实际工作负载和监控数据进一步调整。

表缓存、临时表与查询缓存

每次 MySQL 打开一张表，都会消耗一个文件描述符并加载表定义。当 table_open_cache 设置过低时，MySQL 会频繁关闭和重新打开文件，在拥有大量表的数据库（如 WordPress 多站点）中，这会造成显著的 I/O 开销。

[mysqld]
table_open_cache = 800            # Increase if Opened_tables grows quickly in SHOW STATUS
table_definition_cache = 1400    # Cache for table schema definitions

# Temporary tables — used for GROUP BY / ORDER BY without covering index
tmp_table_size = 64M              # In-memory temp table size before spilling to disk
max_heap_table_size = 64M         # Must match tmp_table_size

# Detect if tmp_table_size needs to increase:
# SHOW GLOBAL STATUS LIKE 'Created_tmp%';
# If Created_tmp_disk_tables / Created_tmp_tables > 5%, increase both values

查询缓存说明：MySQL 8.0 已完全移除查询缓存。如果你使用的是 MariaDB，且工作负载以读取为主（SELECT 远多于 INSERT/UPDATE/DELETE），适当配置查询缓存会有所帮助。但对于像 WordPress 这样读写混合的应用，查询缓存往往因每次写入都需要使缓存失效而拖累性能：

[mysqld]
# MariaDB only — read-biased workloads
query_cache_type = 1
query_cache_size = 64M            # Keep below 128M to avoid fragmentation
query_cache_limit = 2M

# MySQL 8.0+ or write-heavy workloads — disable entirely
query_cache_type = 0
query_cache_size = 0

索引优化 — 回报率最高的调优手段

任何服务端配置调整都无法弥补缺失或设计不当的索引所带来的影响。为频繁查询的表添加合适的索引，带来的性能提升往往超过将内存翻倍。my.cnf 调优必须与全面的索引检查相结合。

找出需要优化索引的表

-- Tables getting the most full scans (requires sys schema)
SELECT * FROM sys.schema_tables_with_full_table_scans
ORDER BY rows_full_scanned DESC LIMIT 10;

-- Indexes that are never used
SELECT * FROM sys.schema_unused_indexes;

-- Redundant or duplicate indexes
SELECT * FROM sys.schema_redundant_indexes;

设计高效的复合索引

-- For the query: WHERE user_id=? AND status=? ORDER BY created_at DESC
-- Place equality filter columns first, then the sort column
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- Verify the index is being used
EXPLAIN SELECT * FROM orders
  WHERE user_id=1 AND status='pending'
  ORDER BY created_at DESC;
-- Look for type=ref or type=range and key=idx_user_status_date

请记住，每个索引都会给 INSERT、UPDATE 和 DELETE 操作带来额外开销，因为每次写入时 MySQL 都需要更新所有相关索引。请仅根据慢查询日志中的真实查询模式，创建确实必要的索引。

4GB 内存 VPS 的完整生产就绪 my.cnf 配置

以下配置适用于运行标准 LAMP 堆栈（Nginx 或 Apache + PHP-FPM + MySQL/MariaDB）、托管 WordPress 或类似 PHP 应用的 4GB 内存 VPS：

[mysqld]
# === Basic Settings ===
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 127.0.0.1

# === Character Set ===
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# === InnoDB Settings ===
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# === Connection Settings ===
max_connections = 200
max_allowed_packet = 64M
thread_cache_size = 50
thread_stack = 192K
back_log = 128

# === Cache Settings ===
table_open_cache = 800
table_definition_cache = 1400
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# === Logging ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# === Binary Log (disable if replication is not needed) ===
# skip-log-bin

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

重启后验证调优效果

使用新配置重启 MySQL 后，请在正常负载下至少等待 1–2 小时再下结论。InnoDB 缓冲池从空白开始，随着热点数据逐渐填入缓存，性能会持续提升。

# Buffer pool hit rate — target above 99%
mysql -u root -p -e "
SELECT
  (bpr.VARIABLE_VALUE /
   (bpr.VARIABLE_VALUE + bpd.VARIABLE_VALUE)) * 100 AS hit_rate_pct
FROM
  performance_schema.global_status bpr,
  performance_schema.global_status bpd
WHERE bpr.VARIABLE_NAME='Innodb_buffer_pool_read_requests'
  AND bpd.VARIABLE_NAME='Innodb_buffer_pool_reads';"

# Check thread cache effectiveness
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_created';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Connections';"
# thread_cache_hit_rate = 1 - (Threads_created / Connections)

# Run MySQLTuner for automated recommendations
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass yourpassword

常见问题

2GB 内存的 VPS 应将 innodb_buffer_pool_size 设为多少？

对于主要运行 InnoDB 的 VPS，建议将 innodb_buffer_pool_size 设为总内存的 50–70%。在仅运行 MySQL 的 2GB VPS 上，1G 是一个合适的起点。若 Apache/Nginx 和 PHP-FPM 也运行在同一台服务器上，则应降至 512M–768M，以留出空间给其他服务。若将此值设置超过可用物理内存，操作系统将被迫使用磁盘交换，速度会大幅慢于内存，性能反而可能不如默认配置。

什么是慢查询日志？是否应该永久开启？

慢查询日志会记录超过 long_query_time 阈值的查询（默认 10 秒，建议设为 1–2 秒以捕获真正的瓶颈）。在生产环境中持续开启的性能开销极低，通常完全可接受。建议保持开启状态以持续进行性能分析，待系统充分优化且运行稳定后再考虑关闭。它在应用更新引入新的未索引查询时尤为宝贵，能帮助快速发现性能退化。

MyISAM 和 InnoDB 有什么区别？应该用哪个？

InnoDB 支持事务、外键和行级锁，是电商、CMS 等 OLTP 工作负载的正确选择，同时提供自动崩溃恢复功能。MyISAM 在每次写入时执行全表锁，在高并发场景下会严重拖慢性能，且没有内置崩溃恢复机制。所有新建表均推荐使用 InnoDB 作为默认引擎，仅当你有遗留应用依赖当前 InnoDB 版本不支持的 MyISAM 特定全文搜索功能时，才考虑使用 MyISAM。

如何查看 MySQL 内存使用情况并验证调优效果？

使用 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%' 查看缓冲池使用情况。若 Innodb_buffer_pool_pages_free 持续接近零，说明缓冲池已满，需要扩大。比较 Innodb_buffer_pool_read_requests 与 Innodb_buffer_pool_reads 来计算缓存命中率——目标应在 99% 以上。使用 SHOW PROCESSLIST 查看当前正在运行的查询，使用 mysqladmin status 实时监控每秒查询数等指标。MySQLTuner 脚本可提供自动化分析并给出可操作的优化建议。