# 1. 系统级调优
# 调整内核参数
vi /etc/sysctl.conf
# 添加以下参数
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmax = 17179869184
kernel.shmall = 4194304
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15

# 应用参数
sysctl -p

# 调整文件描述符
vi /etc/security/limits.conf
# 添加以下参数
* soft nofile 65536
* hard nofile 65536

# 2. 数据库级调优
# 修改my.cnf
[mysqld]
# 内存参数
innodb_buffer_pool_size = 8G
key_buffer_size = 256M
query_cache_size = 0
query_cache_type = 0

# 连接参数
max_connections = 1000
max_connect_errors = 10000

# 日志参数
slow_query_log = 1
slow_query_log_file = /mariadb/logs/slow_query.log
long_query_time = 1

# 存储引擎参数
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_io_capacity = 2000

# 3. SQL级调优
# 优化查询语句
EXPLAIN SELECT * FROM fgedu_users WHERE username = ‘fgedu01’;

# 创建合适的索引
CREATE INDEX idx_username ON fgedu_users(username);

# 避免全表扫描
SELECT * FROM fgedu_users WHERE user_id = 1;

# 避免在WHERE子句中使用函数
SELECT * FROM fgedu_users WHERE created_at BETWEEN ‘2026-01-01’ AND ‘2026-12-31’;