MySQL性能优化与安全配置指南

---

MySQL默认配置是为通用场景设计的，在VPS环境下需要根据实际硬件资源和业务需求进行针对性调优。本文从my.cnf参数优化、慢查询分析、索引优化和安全加固四个方面，提供一套适用于搬瓦工VPS的MySQL优化方案。

Tip： 如果还没有安装MySQL，请先参考VPS安装MySQL 8.0教程。

一、my.cnf核心参数调优

MySQL的配置文件路径通常为/etc/mysql/mysql.conf.d/mysqld.cnf（Ubuntu）或/etc/my.cnf（CentOS）。以下是针对不同内存规格的推荐配置：

1GB内存VPS配置：

[mysqld]
# InnoDB引擎配置
innodb_buffer_pool_size = 384M
innodb_log_file_size = 48M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# 连接管理
max_connections = 50
max_connect_errors = 100
wait_timeout = 300
interactive_timeout = 300
thread_cache_size = 8

# 查询缓存（MySQL 8.0已移除，以下适用于5.7）
# query_cache_type = 1
# query_cache_size = 32M

# 临时表与排序
tmp_table_size = 32M
max_heap_table_size = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2GB内存VPS配置：

[mysqld]
innodb_buffer_pool_size = 768M
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

max_connections = 100
thread_cache_size = 16
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M

二、关键参数解析

• innodb_buffer_pool_size：最重要的参数，建议设置为可用内存的50-70%。这个缓冲池缓存表数据和索引，越大查询越快
• innodb_flush_log_at_trx_commit：设为2可以显著提升写入性能，代价是宕机时可能丢失最近1秒的事务
• innodb_flush_method：O_DIRECT避免双重缓存，减少内存浪费
• max_connections：不要设太大，每个连接消耗内存。VPS环境50-100足够
• wait_timeout：空闲连接超时时间，设短一些可以及时释放资源

三、开启慢查询日志

慢查询日志是发现性能瓶颈的最有效工具：

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

重启MySQL后生效。使用mysqldumpslow分析慢查询：

# 查看最慢的10条查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看出现次数最多的10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看返回行数最多的查询
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

也可以使用pt-query-digest进行更详细的分析：

# 安装percona-toolkit
apt install percona-toolkit -y

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

四、索引优化

索引是数据库性能优化的核心。使用EXPLAIN分析查询执行计划：

# 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

# 查看详细执行信息
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

EXPLAIN输出中需要关注的字段：

• type：ALL表示全表扫描（需优化），ref/eq_ref/const表示使用了索引
• possible_keys：可能用到的索引
• key：实际使用的索引
• rows：预估扫描行数，越少越好
• Extra：Using filesort和Using temporary需要优化

索引创建原则：

# WHERE条件中的高频字段建索引
CREATE INDEX idx_email ON users(email);

# 复合索引遵循最左前缀原则
CREATE INDEX idx_user_status ON orders(user_id, status);

# 覆盖索引减少回表查询
CREATE INDEX idx_cover ON orders(user_id, status, amount, created_at);

# 查看表的索引情况
SHOW INDEX FROM users;

# 查看索引使用统计
SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'myapp_db';

五、查询优化技巧

# 避免SELECT *，只查询需要的字段
SELECT id, name, email FROM users WHERE status = 1;

# 分页优化：使用游标分页替代OFFSET
# 慢：
SELECT * FROM posts ORDER BY id DESC LIMIT 10000, 20;
# 快：
SELECT * FROM posts WHERE id < 50000 ORDER BY id DESC LIMIT 20;

# 避免在索引列上使用函数
# 慢：
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
# 快：
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

# 批量INSERT替代逐条插入
INSERT INTO logs (msg, level, time) VALUES
  ('msg1', 'info', NOW()),
  ('msg2', 'warn', NOW()),
  ('msg3', 'error', NOW());

六、安全加固配置

数据库安全同样重要，以下是必要的安全配置：

[mysqld]
# 禁止通过LOCAL加载文件
local_infile = 0

# 禁止符号链接
symbolic-links = 0

# 设置错误日志
log_error = /var/log/mysql/error.log

# 启用二进制日志（用于恢复和复制）
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

用户权限最小化原则：

# 查看所有用户及其主机
SELECT user, host FROM mysql.user;

# 删除不必要的用户
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

# 为应用创建最小权限用户
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'localhost';

# 不要给应用用户DROP、ALTER等权限
# GRANT ALL 只用于管理员

FLUSH PRIVILEGES;

七、监控与诊断

定期检查MySQL运行状态：

# 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

# 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
# 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
# 应大于99%

# 查看连接使用情况
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';

# 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp%';

# 查看表锁等待
SHOW STATUS LIKE 'Table_locks%';

修改配置后重启MySQL：

systemctl restart mysql

数据库备份方案请参考VPS数据库自动备份教程，更多VPS性能监控工具请参考VPS性能监控工具推荐。

Tip： 更多教程请查看新手教程。