如何优化MySQL 8.0在2GB内存Linux服务器上的性能？

在2GB内存的服务器上优化MySQL 8.0需要谨慎配置，以下是一套完整的优化方案：

1. 内存相关配置

my.cnf 主要配置

[mysqld]
# 基础设置
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid

# 内存配置 - 关键部分
innodb_buffer_pool_size = 512M        # 总内存的25%
key_buffer_size = 64M                 # MyISAM索引缓存
query_cache_size = 32M                # 查询缓存
tmp_table_size = 64M                  # 临时表大小
max_heap_table_size = 64M             # 内存表最大大小
sort_buffer_size = 2M                 # 排序缓冲区
read_buffer_size = 1M                 # 顺序读取缓冲区
read_rnd_buffer_size = 1M             # 随机读取缓冲区
join_buffer_size = 1M                 # 连接缓冲区

# InnoDB配置
innodb_log_file_size = 128M           # 日志文件大小
innodb_log_buffer_size = 16M          # 日志缓冲区
innodb_flush_log_at_trx_commit = 2    # 平衡性能和安全
innodb_file_per_table = ON            # 每个表独立文件
innodb_flush_method = O_DIRECT        # 直接I/O

# 连接配置
max_connections = 100                 # 最大连接数
wait_timeout = 300                    # 空闲连接超时
interactive_timeout = 300             # 交互式连接超时

2. 系统级优化

创建swap空间（如果尚未创建）

# 创建2GB swap文件
sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

# 永久生效
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab

调整内核参数

# 编辑 /etc/sysctl.conf
vm.swappiness=10
vm.vfs_cache_pressure=50
net.core.somaxconn=1024

3. MySQL监控脚本

性能监控脚本

#!/bin/bash
# monitor_mysql.sh

echo "=== MySQL 内存使用情况 ==="
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';"
mysql -e "SHOW STATUS LIKE 'Qcache%';"
mysql -e "SHOW STATUS LIKE 'Created_tmp%';"

echo -e "n=== 连接状态 ==="
mysql -e "SHOW PROCESSLIST;"

echo -e "n=== 表统计 ==="
mysql -e "SELECT table_schema, table_name, data_length/1024/1024 as data_mb, index_length/1024/1024 as index_mb FROM information_schema.tables ORDER BY data_length DESC LIMIT 10;"

4. 定期维护脚本

优化表和清理

#!/bin/bash
# optimize_tables.sh

# 优化碎片化表
mysql -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') AND data_free > 0;" | mysql

# 清理二进制日志（如果启用）
mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

# 分析表统计信息
mysql -e "ANALYZE TABLE your_database.your_table;"

5. 应用层优化建议

PHP应用配置示例

<?php
// 使用持久连接减少开销
$pdo = new PDO(
    'mysql:host=localhost;dbname=your_db',
    'username', 
    'password',
    [
        PDO::ATTR_PERSISTENT => true,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
);

// 合理的查询优化
function getLimitedResults($limit = 50) {
    $stmt = $pdo->prepare("SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}
?>

6. 监控和告警

设置基本监控

# crontab 定时任务
*/5 * * * * /path/to/monitor_mysql.sh >> /var/log/mysql_monitor.log 2>&1

# 内存使用告警
*/10 * * * * free -m | awk 'NR==2{if($3*100/$2 > 80) print "警告: 内存使用超过80%"}' >> /var/log/memory_alert.log

7. 关键优化原则

1. 避免过度配置：不要将过多内存分配给MySQL
2. 定期清理：及时删除不必要的数据和日志
3. 查询优化：确保SQL语句高效，使用EXPLAIN分析
4. 索引优化：为常用查询字段创建适当索引
5. 连接池：使用连接池管理数据库连接

8. 故障排查

-- 检查慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看当前配置
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%size%';
SHOW STATUS LIKE 'Threads%';

这套配置可以在保证系统稳定的同时最大化MySQL性能。根据实际负载情况，可以进一步微调各个参数。