CentOS下数据库如何优化

在CentOS环境下优化数据库，主要目标是提升数据库的性能、稳定性与响应速度。无论你使用的是MySQL、MariaDB、PostgreSQL还是其他数据库系统，优化都包括系统层面、数据库配置层面以及数据结构层面。以下是一套通用但系统性的优化策略。

系统层优化

调整内核参数：修改 /etc/sysctl.conf，增加以下配置：

vm.swappiness = 10
fs.file-max = 100000
net.core.somaxconn = 1024
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1

应用修改：

sudo sysctl -p

最大文件数限制：编辑 /etc/security/limits.conf：
```
mysql soft nofile 65535
mysql hard nofile 65535
```
确保 MySQL 启动脚本或 systemd 中也包含：
```
LimitNOFILE 65535
```

关闭不必要服务：减少系统负载：

sudo systemctl disable firewalld
sudo systemctl disable postfix

数据库配置优化

合理配置 MySQL 参数：修改 /etc/my.cnf，根据服务器内存和业务量调整如下（仅参考）：

[mysqld]
max_connections = 200
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size = 2G  # 通常为物理内存的 50%-80%
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

建议根据 mysqltuner 工具建议进一步微调。

启用慢查询日志并分析：

mysql SHOW VARIABLES LIKE 'slow_query_log%';

安装分析工具：

yum install perl-DBI
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

表结构和索引优化

选择合适的数据类型：使用 INT 代替 BIGINT；使用 VARCHAR(255) 而不是 TEXT，除非必要；避免使用过大的字段组合索引；时间字段建议用 DATETIME 而不是字符串。
建立合理的索引：经常用于 WHERE、JOIN、ORDER BY、GROUP BY 的字段必须建立索引；使用 EXPLAIN 分析 SQL 查询执行计划；不要在索引字段上使用函数（如 YEAR(date_column) 会导致索引失效）。

SQL 语句层优化

**避免 SELECT ***：改为明确字段：
```
SELECT id, name FROM users;
```

分页优化：避免深分页：

-- 慎用 SELECT * FROM table LIMIT 100000, 10;
-- 优化方案(使用 ID 过滤)
SELECT * FROM table WHERE id > 100000 LIMIT 10;

使用预处理语句（prepared statements）：提高性能；减少 SQL 注入风险。

服务与安全相关优化

使用 unix_socket 登录（本地安全）：
```
mysql -u root -p --protocol=socket
```
限制远程访问：修改 /etc/my.cnf：
```
bind-address = 127.0.0.1
```
如需远程开放，则配置防火墙和用户权限限制。

监控与维护

定期清理临时表、冗余索引：
```
SHOW ENGINE INNODB STATUS;
```

备份与自动化脚本：使用 mysqldump 定期备份：

0 2 * * * /usr/bin/mysqldump -u root -pPASS dbname > /backup/db_(date \%F).sql

查看性能状态：

SHOW STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Uptime';