LNMP环境下MySQL数据库优化指南

作为LNMP(Linux+Nginx+MySQL+PHP)架构的核心组件，MySQL的性能直接影响整个系统的响应速度。以下是一套全面的MySQL优化方案：

一、基础配置优化

1. my.cnf配置文件优化

[mysqld]
# 基础设置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# 连接设置
max_connections = 1000
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300

# 缓冲池设置 (根据服务器内存调整)
innodb_buffer_pool_size = 4G  # 建议为总内存的50-70%
innodb_buffer_pool_instances = 8  # 对于大内存服务器

# 日志设置
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

# 其他InnoDB设置
innodb_flush_log_at_trx_commit = 2  # 对数据安全性要求不高时可设为2
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_thread_concurrency = 0

# 查询缓存 (MySQL 8.0已移除)
# query_cache_size = 0

# 临时表设置
tmp_table_size = 64M
max_heap_table_size = 64M

# 其他优化
table_open_cache = 4000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

2. 系统层面优化

# 调整文件描述符限制
echo "* soft nofile 65535" >> /etc/security/limits.conf
echo "* hard nofile 65535" >> /etc/security/limits.conf

# 调整内核参数
echo "vm.swappiness = 10" >> /etc/sysctl.conf
sysctl -p

二、数据库结构与查询优化

1. 表结构优化

-- 使用合适的数据类型
ALTER TABLE users MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;

-- 添加适当的索引
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
ALTER TABLE products ADD INDEX idx_category_status (category_id, status);

-- 定期优化表
OPTIMIZE TABLE large_table;

2. SQL查询优化

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = 'admin';

-- 避免SELECT *
SELECT id, username, email FROM users WHERE status = 1;

-- 使用JOIN替代子查询
SELECT u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1;

-- 使用LIMIT分页
SELECT * FROM products WHERE category_id = 5 LIMIT 20 OFFSET 40;

三、高级优化技术

1. 主从复制配置

# 主服务器my.cnf
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL

# 从服务器my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read_only = ON

2. 分区表

-- 按范围分区
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

四、监控与维护

1. 监控工具

# 安装Percona监控工具
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

2. 常用监控命令

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看状态变量
SHOW GLOBAL STATUS;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

3. 定期维护脚本

#!/bin/bash
# 每日数据库备份
mysqldump -u root -p[password] --all-databases --single-transaction | gzip > /backup/mysql/db_$(date +%Y%m%d).sql.gz

# 保留最近7天备份
find /backup/mysql/ -type f -mtime +7 -exec rm {} \;

# 分析所有表
mysqlcheck -u root -p[password] --analyze --all-databases

五、安全优化

-- 创建专用应用用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

-- 移除匿名用户
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

-- 启用SSL连接
ALTER USER 'app_user'@'localhost' REQUIRE SSL;

通过以上优化措施，可以显著提升LNMP环境下MySQL数据库的性能和稳定性。建议根据实际业务需求和服务器配置进行适当调整，并定期监控数据库性能指标。