数据库服务器配置指南

发表于 更新于 分类于

数据库服务器配置指南

AI生成：本文由人工智能辅助生成，内容已经过人工审核。

本文详细介绍了MySQL、PostgreSQL和Redis数据库的安装、配置、优化、备份和安全加固方法，帮助你构建高性能、安全可靠的数据库服务器。

1. MySQL数据库配置

MySQL是目前最流行的开源关系型数据库之一，下面详细介绍MySQL的安装配置和优化方法。

1.1 MySQL安装

Ubuntu/Debian安装

1
2
3
4
5
6
7
8
# 更新包索引
apt update

# 安装MySQL服务器
apt install -y mysql-server

# 运行安全配置脚本
mysql_secure_installation

CentOS/RHEL安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 安装MySQL仓库
rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

# 安装MySQL服务器
yum install -y mysql-community-server

# 启动MySQL并设置开机自启
systemctl start mysqld
systemctl enable mysqld

# 获取临时密码
grep 'temporary password' /var/log/mysqld.log

# 运行安全配置脚本
mysql_secure_installation

1.2 MySQL基础配置

MySQL的主配置文件通常位于:

  • Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
  • CentOS/RHEL: /etc/my.cnf

基本配置示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[mysqld]
# 基本设置
user                   = mysql
pid-file               = /var/run/mysqld/mysqld.pid
socket                 = /var/run/mysqld/mysqld.sock
datadir                = /var/lib/mysql
log-error              = /var/log/mysql/error.log

# 字符集和排序规则
character-set-server   = utf8mb4
collation-server       = utf8mb4_unicode_ci

# 连接设置
max_connections        = 150
max_allowed_packet     = 16M
wait_timeout           = 600
interactive_timeout    = 600

# InnoDB设置
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method     = O_DIRECT

# 慢查询日志
slow_query_log         = 1
slow_query_log_file    = /var/log/mysql/mysql-slow.log
long_query_time        = 2

应用配置并重启MySQL：

1
2
3
# 重启MySQL服务
systemctl restart mysql  # Ubuntu/Debian
systemctl restart mysqld # CentOS/RHEL

1.3 MySQL用户和权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 登录MySQL
mysql -u root -p

-- 创建新用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';  -- 允许远程访问

-- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'newuser'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';

-- 删除用户
DROP USER 'newuser'@'localhost';

1.4 MySQL性能优化

内存配置优化

1
2
3
4
5
6
7
8
9
10
11
12
# 为中小型服务器(4-8GB内存)
innodb_buffer_pool_size = 2G       # 总内存的50-70%
innodb_log_file_size = 512M        # buffer_pool的25%左右
innodb_buffer_pool_instances = 4   # 每个实例不少于1GB

# 查询缓存（MySQL 8.0已移除）
query_cache_size = 64M             # MySQL 5.7及更早版本
query_cache_type = 1

# 连接缓冲区
max_connections = 200
thread_cache_size = 16

存储引擎优化

1
2
3
4
5
# InnoDB优化
innodb_flush_log_at_trx_commit = 2  # 性能与安全的折中
innodb_flush_method = O_DIRECT      # 避免双重缓冲
innodb_file_per_table = 1           # 每个表单独存储
innodb_io_capacity = 1000           # SSD磁盘可以设置更高

查询优化

1
2
3
4
5
6
7
8
-- 添加索引
CREATE INDEX idx_name ON table_name (column_name);

-- 优化表
OPTIMIZE TABLE table_name;

-- 分析表
ANALYZE TABLE table_name;

1.5 MySQL备份与恢复

使用mysqldump备份

1
2
3
4
5
6
7
8
9
10
11
# 备份单个数据库
mysqldump -u root -p database_name > database_name.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > databases.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql

# 只备份结构不备份数据
mysqldump -u root -p --no-data database_name > schema.sql

恢复数据

1
2
3
4
5
# 恢复数据库
mysql -u root -p database_name < database_name.sql

# 恢复多个或所有数据库
mysql -u root -p < databases.sql

使用Percona XtraBackup进行物理备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 安装Percona XtraBackup
apt install -y percona-xtrabackup-80  # Ubuntu/Debian
yum install -y percona-xtrabackup-80  # CentOS/RHEL

# 创建完整备份
xtrabackup --backup --target-dir=/backup/full

# 准备备份用于恢复
xtrabackup --prepare --target-dir=/backup/full

# 恢复备份
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

1.6 MySQL监控与维护

设置监控脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/bin/bash
# /usr/local/bin/mysql_monitor.sh

# 检查MySQL状态
mysqladmin -u monitor_user -p'password' ping &>/dev/null
if [ $? -ne 0 ]; then
    echo "MySQL is down!" | mail -s "MySQL Alert" admin@example.com
    systemctl restart mysql
else
    echo "MySQL is running"
fi

# 检查连接数
CONN_COUNT=$(mysqladmin -u monitor_user -p'password' status | awk '{print $4}')
if [ $CONN_COUNT -gt 100 ]; then
    echo "High connection count: $CONN_COUNT" | mail -s "MySQL Connection Alert" admin@example.com
fi

# 检查慢查询
SLOW_QUERIES=$(mysqladmin -u monitor_user -p'password' extended-status | grep "Slow_queries" | awk '{print $4}')
if [ $SLOW_QUERIES -gt 10 ]; then
    echo "Slow queries detected: $SLOW_QUERIES" | mail -s "MySQL Performance Alert" admin@example.com
fi

设置定时任务：

1
2
chmod +x /usr/local/bin/mysql_monitor.sh
echo "*/5 * * * * root /usr/local/bin/mysql_monitor.sh" > /etc/cron.d/mysql_monitor

MySQL日志轮转

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 配置logrotate
cat > /etc/logrotate.d/mysql << EOF
/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    delaycompress
    sharedscripts
    postrotate
        if [ -x /usr/bin/mysqladmin ] && [ -r /etc/mysql/debian.cnf ]; then
            /usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
        fi
    endscript
}
EOF

1.7 MySQL安全加固

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 禁用本地文件加载
local_infile = 0

# 限制登录失败次数
max_connect_errors = 10

# 开启SSL连接
ssl_cert = /etc/mysql/server-cert.pem
ssl_key = /etc/mysql/server-key.pem
ssl_ca = /etc/mysql/ca.pem

# 启用审计日志（需要插件）
audit_log = FORCE_PLUS_PERMANENT
audit_log_file = /var/log/mysql/audit.log

配置防火墙只允许特定IP访问MySQL：

1
2
3
4
5
6
# UFW配置
ufw allow from 192.168.1.0/24 to any port 3306

# firewalld配置
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --reload

2. PostgreSQL数据库配置

PostgreSQL是一个功能强大的开源关系型数据库系统，以其可靠性、稳定性和丰富的功能著称。

2.1 PostgreSQL安装

Ubuntu/Debian安装

1
2
3
4
5
6
7
8
# 更新包索引
apt update

# 安装PostgreSQL
apt install -y postgresql postgresql-contrib

# 登录到PostgreSQL
sudo -u postgres psql

CentOS/RHEL安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 安装PostgreSQL仓库
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 禁用内置PostgreSQL模块
dnf -qy module disable postgresql

# 安装PostgreSQL
dnf install -y postgresql13-server postgresql13-contrib

# 初始化数据库
/usr/pgsql-13/bin/postgresql-13-setup initdb

# 启动并设置开机自启
systemctl enable postgresql-13
systemctl start postgresql-13

2.2 PostgreSQL基础配置

PostgreSQL的主要配置文件位于数据目录中：

  • Ubuntu/Debian: /etc/postgresql/13/main/postgresql.conf和pg_hba.conf
  • CentOS/RHEL: /var/lib/pgsql/13/data/postgresql.conf和pg_hba.conf

基本配置：

1
2
3
4
5
6
7
8
9
10
11
# postgresql.conf基本设置
listen_addresses = 'localhost'           # 默认只监听本地
max_connections = 100                   # 最大连接数
shared_buffers = 1GB                    # 共享内存缓冲区
work_mem = 16MB                         # 每个连接的工作内存
maintenance_work_mem = 256MB            # 维护操作使用的内存
effective_cache_size = 3GB              # 可用于磁盘缓存的内存估计
wal_buffers = 16MB                      # WAL缓冲区大小
synchronous_commit = off                # 高性能但降低持久性保证
max_wal_size = 1GB                      # WAL最大大小
checkpoint_timeout = 15min              # 检查点之间的最大时间

客户端认证配置 (pg_hba.conf):

1
2
3
4
5
6
7
8
9
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow remote connections from trusted network:
host    all             all             192.168.1.0/24          md5

应用配置更改：

1
2
3
# 重启PostgreSQL服务
systemctl restart postgresql         # Ubuntu/Debian
systemctl restart postgresql-13      # CentOS/RHEL

2.3 PostgreSQL用户和数据库管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 登录PostgreSQL
sudo -u postgres psql

-- 创建新用户
CREATE USER dbuser WITH ENCRYPTED PASSWORD 'password';

-- 创建数据库
CREATE DATABASE mydb;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO dbuser;

-- 为用户创建Schema
\c mydb
CREATE SCHEMA IF NOT EXISTS myschema AUTHORIZATION dbuser;

-- 更改数据库所有者
ALTER DATABASE mydb OWNER TO dbuser;

-- 撤销权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM dbuser;

-- 删除用户
DROP USER dbuser;

2.4 PostgreSQL性能优化

内存配置

1
2
3
4
5
6
# 4GB内存服务器的配置
shared_buffers = 1GB                     # 内存的25%
work_mem = 16MB                          # 连接级内存
maintenance_work_mem = 256MB             # 维护操作内存
effective_cache_size = 3GB               # 内存的75%
temp_buffers = 16MB                      # 临时表内存

写入性能

1
2
3
4
5
6
# 写入优化
wal_buffers = 16MB                       # 16MB通常足够
checkpoint_timeout = 15min               # 减少检查点频率
max_wal_size = 1GB                       # 增加WAL大小
checkpoint_completion_target = 0.9       # 延长检查点完成时间
synchronous_commit = off                 # 提高性能但有丢失事务风险

查询优化

1
2
3
4
5
6
7
8
9
10
11
-- 分析表
ANALYZE table_name;

-- 创建索引
CREATE INDEX idx_name ON table_name (column_name);

-- 查看查询计划
EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

-- 设置表统计目标
ALTER TABLE table_name SET STATISTICS 1000;

2.5 PostgreSQL备份与恢复

使用pg_dump和pg_dumpall备份

1
2
3
4
5
6
7
8
9
10
11
# 备份单个数据库
pg_dump -U postgres -d mydb -f mydb_backup.sql

# 备份为压缩文件
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 备份为自定义格式(支持并行和选择性恢复)
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

恢复数据

1
2
3
4
5
6
7
8
# 恢复SQL格式备份
psql -U postgres -d mydb < mydb_backup.sql

# 恢复压缩备份
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

# 恢复自定义格式
pg_restore -U postgres -d mydb mydb_backup.dump

使用pg_basebackup物理备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 配置远程连接和复制权限
# 编辑pg_hba.conf添加:
# host    replication     postgres        192.168.1.0/24          md5

# 编辑postgresql.conf:
# wal_level = replica
# max_wal_senders = 10
# wal_keep_size = 64

# 进行物理备份
pg_basebackup -h localhost -U postgres -D /backup/postgresql -Fp -Xs -P

# 恢复物理备份
systemctl stop postgresql
rm -rf /var/lib/postgresql/13/main/*
cp -R /backup/postgresql/* /var/lib/postgresql/13/main/
chown -R postgres:postgres /var/lib/postgresql/13/main
chmod 700 /var/lib/postgresql/13/main
systemctl start postgresql

2.6 PostgreSQL监控和维护

设置监控脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/bin/bash
# /usr/local/bin/pgsql_monitor.sh

# 检查PostgreSQL状态
sudo -u postgres psql -c "SELECT 1" >/dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "PostgreSQL is down!" | mail -s "PostgreSQL Alert" admin@example.com
    systemctl restart postgresql
else
    echo "PostgreSQL is running"
fi

# 检查连接数
CONN_COUNT=$(sudo -u postgres psql -t -c "SELECT count(*) FROM pg_stat_activity")
if [ $CONN_COUNT -gt 50 ]; then
    echo "High connection count: $CONN_COUNT" | mail -s "PostgreSQL Connection Alert" admin@example.com
fi

# 检查长时间运行的查询
sudo -u postgres psql -t -c "SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > '5 minutes'::interval" | while read line
do
    if [ ! -z "$line" ]; then
        echo "Long running query: $line" | mail -s "PostgreSQL Performance Alert" admin@example.com
    fi
done

设置定时任务：

1
2
chmod +x /usr/local/bin/pgsql_monitor.sh
echo "*/5 * * * * root /usr/local/bin/pgsql_monitor.sh" > /etc/cron.d/pgsql_monitor

维护任务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 清理死亡事务和回收空间
VACUUM;  

-- 分析表统计信息
ANALYZE;  

-- 完全清理并回收空间
VACUUM FULL;  -- 注意：会锁表

-- 自动清理配置
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;

-- 重新加载配置
SELECT pg_reload_conf();

2.7 PostgreSQL安全加固

1
2
3
4
5
6
7
8
# postgresql.conf安全设置
ssl = on                                 # 启用SSL
ssl_cert_file = 'server.crt'             # SSL证书
ssl_key_file = 'server.key'              # SSL私钥
password_encryption = scram-sha-256      # 使用更安全的密码哈希
log_connections = on                     # 记录连接
log_disconnections = on                  # 记录断开连接
log_statement = 'ddl'                    # 记录数据定义语言命令

配置防火墙只允许指定IP访问PostgreSQL：

1
2
3
4
5
6
# UFW配置
ufw allow from 192.168.1.0/24 to any port 5432

# firewalld配置
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=5432 protocol=tcp accept'
firewall-cmd --reload

3. 数据库常见问题排查

3.1 MySQL常见问题

连接问题

1
2
3
4
5
6
7
8
9
10
11
# 检查MySQL是否运行
systemctl status mysql

# 检查MySQL监听端口
netstat -tuln | grep 3306

# 检查防火墙规则
iptables -L -n | grep 3306

# 检查错误日志
tail -f /var/log/mysql/error.log

性能问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看当前进程
SHOW PROCESSLIST;

-- 查看表状态
SHOW TABLE STATUS FROM database_name;

-- 查看服务器状态变量
SHOW GLOBAL STATUS LIKE 'Com_%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
SHOW GLOBAL STATUS LIKE 'Threads_%';

-- 查看查询缓存统计
SHOW GLOBAL STATUS LIKE 'Qcache_%';  -- MySQL 5.7及更早版本

-- 显示索引使用情况
SHOW INDEX FROM table_name;

磁盘空间问题

1
2
3
4
5
6
7
8
# 检查MySQL数据目录大小
du -sh /var/lib/mysql/

# 检查单个数据库大小
du -sh /var/lib/mysql/database_name/

# 从MySQL内部查看数据库大小
SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

3.2 PostgreSQL常见问题

连接问题

1
2
3
4
5
6
7
8
9
10
11
# 检查PostgreSQL是否运行
systemctl status postgresql

# 检查监听端口
netstat -tuln | grep 5432

# 检查pg_hba.conf中的访问控制
grep -v "^#" /etc/postgresql/13/main/pg_hba.conf | grep -v "^$"

# 检查错误日志
tail -f /var/log/postgresql/postgresql-13-main.log

性能问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看当前活动连接
SELECT pid, usename, datname, state, query, now() - query_start AS duration
FROM pg_stat_activity WHERE state = 'active';

-- 查看慢查询
SELECT pid, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' AND now() - query_start > '1 minute'::interval;

-- 查看表访问统计
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables;

-- 查看索引使用情况
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

磁盘空间问题

1
2
3
4
5
6
7
8
9
10
11
12
# 检查数据目录大小
du -sh /var/lib/postgresql/13/main/

# 从PostgreSQL内部查看数据库大小
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

# 查看表大小
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i' AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

4. 高可用和水平扩展

4.1 MySQL主从复制

1
2
3
4
5
6
7
8
9
# 主服务器配置 (my.cnf)
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = database_name  # 可选，指定要复制的数据库

# 从服务器配置 (my.cnf)
server-id = 2
relay_log = mysql-relay-bin

在主服务器上创建复制用户：

1
2
3
4
5
6
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看主服务器状态
SHOW MASTER STATUS;

在从服务器上配置复制：

1
2
3
4
5
6
7
8
9
10
11
12
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',  -- 从SHOW MASTER STATUS获取
  MASTER_LOG_POS=1234;                 -- 从SHOW MASTER STATUS获取

-- 启动复制
START SLAVE;

-- 检查复制状态
SHOW SLAVE STATUS\G

4.2 PostgreSQL主从复制

在主服务器上配置 (postgresql.conf):

1
2
3
4
5
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 64

在主服务器上编辑 pg_hba.conf:

1
host    replication     replica         replica_ip/32           md5

创建复制用户：

1
CREATE ROLE replica WITH REPLICATION LOGIN PASSWORD 'password';

在从服务器上进行基础备份：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 停止从服务器
systemctl stop postgresql

# 清空数据目录
rm -rf /var/lib/postgresql/13/main/*

# 基础备份
pg_basebackup -h master_ip -U replica -D /var/lib/postgresql/13/main -P -v -X stream

# 创建recovery.conf (PostgreSQL 12及以前)或standby.signal (PostgreSQL 13+)
# PostgreSQL 13+:
touch /var/lib/postgresql/13/main/standby.signal

# 编辑postgresql.conf
echo "primary_conninfo = 'host=master_ip port=5432 user=replica password=password'" >> /var/lib/postgresql/13/main/postgresql.conf
echo "recovery_target_timeline = 'latest'" >> /var/lib/postgresql/13/main/postgresql.conf

# 设置权限
chown -R postgres:postgres /var/lib/postgresql/13/main
chmod 700 /var/lib/postgresql/13/main

# 启动从服务器
systemctl start postgresql

5. 数据库性能调优最佳实践

5.1 硬件配置建议

  1. 存储：

    • 数据库文件使用SSD存储
    • 单独磁盘挂载点用于数据、日志和备份
    • RAID 10配置平衡性能和冗余

  2. 内存：

    • 足够大的内存以容纳活跃数据集
    • MySQL: 服务器内存的70-80%分配给InnoDB缓冲池
    • PostgreSQL: 服务器内存的25%分配给共享缓冲区，75%留给文件系统缓存

  3. CPU：

    • 多核CPU以支持并行查询和并发连接
    • 较高的单核性能有助于提高查询性能

5.2 索引优化

  1. 有效索引：

    • 为频繁出现在WHERE、JOIN、ORDER BY和GROUP BY子句中的列创建索引
    • 将选择性最高的列放在复合索引的最前面
    • 避免创建重叠的索引
    • 定期检查和删除未使用的索引

  2. 索引类型选择：

    • MySQL: 考虑使用哈希索引、全文索引或空间索引
    • PostgreSQL: 考虑B-tree、Hash、GiST、SP-GiST、GIN或BRIN索引

5.3 查询优化

  1. 查询重写：

    • 只选择必要的列（避免SELECT *）
    • 使用精确的WHERE条件减少扫描的数据量
    • 避免使用函数应用在索引列上
    • 考虑使用LIMIT限制结果集大小

  2. JOIN优化：

    • 选择正确的JOIN类型（INNER、LEFT、RIGHT）
    • 连接条件应该使用索引
    • 按从小到大的顺序连接表
    • 避免不必要的子查询，使用JOIN代替

5.4 连接池和应用侧优化

  1. 使用连接池：

    • 例如： PgBouncer (PostgreSQL)、ProxySQL (MySQL)
    • 减少频繁创建和销毁连接的开销
    • 合理设置最小和最大连接数

  2. 批处理操作：

    • 使用批量INSERT而不是多个单行INSERT
    • 对于大量数据加载，考虑使用LOAD DATA或COPY命令
    • 事务优化：批量提交多个更改

总结

本文详细介绍了MySQL和PostgreSQL两大主流开源数据库的安装配置、性能优化、备份恢复和安全加固。数据库作为应用程序的核心组件，其性能和可靠性对整个系统的影响至关重要。通过合理的配置优化、定期的维护管理和适当的监控机制，可以确保数据库系统稳定、高效地运行。

无论是选择MySQL还是PostgreSQL，都应该根据具体的应用需求和业务特点，选择合适的配置方案和优化策略。同时，保持良好的备份习惯和安全实践，可以有效防范数据丢失和安全风险。