MySQL 网络优化

网络基础配置

操作系统网络优化

TCP 参数优化

bash

# 调整 TCP 缓冲区大小
sysctl -w net.core.rmem_default=262144
sysctl -w net.core.wmem_default=262144
sysctl -w net.core.rmem_max=16777216
sysctl -w net.core.wmem_max=16777216
sysctl -w net.ipv4.tcp_rmem="4096 87380 16777216"
sysctl -w net.ipv4.tcp_wmem="4096 65536 16777216"

# 启用 TCP 窗口缩放
sysctl -w net.ipv4.tcp_window_scaling=1

# 启用时间戳
sysctl -w net.ipv4.tcp_timestamps=1

# 启用选择性确认
sysctl -w net.ipv4.tcp_sack=1

# 调整 SYN 队列大小
sysctl -w net.ipv4.tcp_max_syn_backlog=4096
sysctl -w net.ipv4.tcp_synack_retries=2

# 启用 SYN cookies
sysctl -w net.ipv4.tcp_syncookies=1

# 调整 TIME_WAIT 相关参数
sysctl -w net.ipv4.tcp_fin_timeout=30
sysctl -w net.ipv4.tcp_tw_reuse=1
sysctl -w net.ipv4.tcp_tw_recycle=0

# 调整 keepalive 参数
sysctl -w net.ipv4.tcp_keepalive_time=600
sysctl -w net.ipv4.tcp_keepalive_probes=3
sysctl -w net.ipv4.tcp_keepalive_intvl=15

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

持久化配置

bash

# 将优化后的参数保存到 /etc/sysctl.conf
echo "net.core.rmem_default=262144" >> /etc/sysctl.conf
echo "net.core.wmem_default=262144" >> /etc/sysctl.conf
echo "net.core.rmem_max=16777216" >> /etc/sysctl.conf
echo "net.core.wmem_max=16777216" >> /etc/sysctl.conf
echo "net.ipv4.tcp_rmem=4096 87380 16777216" >> /etc/sysctl.conf
echo "net.ipv4.tcp_wmem=4096 65536 16777216" >> /etc/sysctl.conf
echo "net.ipv4.tcp_window_scaling=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_timestamps=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_sack=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_max_syn_backlog=4096" >> /etc/sysctl.conf
echo "net.ipv4.tcp_synack_retries=2" >> /etc/sysctl.conf
echo "net.ipv4.tcp_syncookies=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_fin_timeout=30" >> /etc/sysctl.conf
echo "net.ipv4.tcp_tw_reuse=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_tw_recycle=0" >> /etc/sysctl.conf
echo "net.ipv4.tcp_keepalive_time=600" >> /etc/sysctl.conf
echo "net.ipv4.tcp_keepalive_probes=3" >> /etc/sysctl.conf
echo "net.ipv4.tcp_keepalive_intvl=15" >> /etc/sysctl.conf

# 重新加载配置
sysctl -p

MySQL 网络配置

基本网络参数

ini

# 监听地址，0.0.0.0 表示监听所有地址
bind-address = 0.0.0.0

# 监听端口
port = 3306

# 最大连接数
max_connections = 1000

# 最大错误连接数
tmax_connect_errors = 10000

# 连接超时时间（秒）
connect_timeout = 10

# 握手超时时间（秒）
# MySQL 5.7+ 新增
mysqlx_connect_timeout = 30

连接优化参数

ini

# 跳过 DNS 反向解析
skip-name-resolve

# DNS 解析超时时间（秒）
# 如果不跳过 DNS 解析，建议设置合理的超时时间
dns-cache-ttl = 3600

# 启用 TCP Keepalive
tcp_keepalive_time = 600
tcp_keepalive_intvl = 75
tcp_keepalive_probes = 9

# 启用压缩传输
# 默认关闭，需要客户端支持
# 建议在网络带宽有限时启用
compression-algorithms = zlib,zstd

# 压缩级别，1-9，默认 6
# 仅在启用压缩时有效
# 级别越高，压缩率越高，但 CPU 消耗越大
compression-level = 6

# 最小压缩长度，默认 1024 字节
# 仅当数据包大小超过此值时才压缩
min-compress-length = 1024

传输优化参数

ini

# 最大允许的数据包大小
# 建议根据实际需求设置，不宜过大
max_allowed_packet = 64M

# 网络缓冲区大小
# 建议设置为系统 TCP 缓冲区的一半
net_buffer_length = 16K

# 读取缓冲区大小
read_buffer_size = 128K

# 随机读取缓冲区大小
read_rnd_buffer_size = 256K

# 写入缓冲区大小
write_buffer_size = 256K

# 排序缓冲区大小
sort_buffer_size = 512K

# 连接读取缓冲区大小
net_read_timeout = 30

# 连接写入缓冲区大小
net_write_timeout = 60

连接优化

连接池优化

应用层连接池

推荐使用：HikariCP（Java）、DBCP2（Java）、pymysqlpool（Python）、pgbouncer（通用）
关键配置：
- 最小连接数：根据最小负载设置
- 最大连接数：根据最大负载和服务器资源设置
- 连接超时：合理设置，避免长时间等待
- 最大空闲时间：避免连接长时间空闲
- 验证查询：定期验证连接有效性

MySQL 连接池配置示例（HikariCP）

java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMinimumIdle(5);
config.setMaximumPoolSize(20);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setValidationTimeout(5000);
config.setConnectionTestQuery("SELECT 1");

HikariDataSource dataSource = new HikariDataSource(config);

连接管理

连接复用

避免频繁创建和关闭连接：使用连接池复用连接
合理设置连接超时：避免连接长时间占用
及时释放连接：应用程序使用完连接后及时关闭
使用长连接：对于频繁访问数据库的应用，使用长连接

连接监控

sql

-- 查看当前连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 查看连接状态
SHOW PROCESSLIST;

-- 查看连接详细信息
SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep';

-- 查看连接统计信息
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Aborted_clients';

传输优化

压缩传输

启用压缩

ini

# 服务器端启用压缩
# MySQL 8.0.19+ 支持的语法
compression-algorithms = zlib,zstd

# 客户端连接时启用压缩
# 使用命令行参数
mysql --compress -u user -p

# 使用连接字符串参数
jdbc:mysql://localhost:3306/mydb?useCompression=true

# 设置压缩级别
# MySQL 8.0.19+ 支持
compression-level = 6

压缩效果评估

sql

-- 查看压缩统计信息
SHOW GLOBAL STATUS LIKE 'Compression%';

-- 压缩连接数
SHOW GLOBAL STATUS LIKE 'Compression_connections';

-- 压缩的数据量
SHOW GLOBAL STATUS LIKE 'Compression_bytes_sent';
SHOW GLOBAL STATUS LIKE 'Compression_bytes_received';
SHOW GLOBAL STATUS LIKE 'Compression_bytes_uncompressed_sent';
SHOW GLOBAL STATUS LIKE 'Compression_bytes_uncompressed_received';

数据传输优化

只选择需要的列

sql

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

-- 不要在 SELECT 中使用函数或表达式，除非必要
SELECT id, name, DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date FROM users;

限制结果集大小

sql

-- 使用 LIMIT 限制返回行数
SELECT * FROM users LIMIT 100;

-- 使用分页查询
SELECT * FROM users ORDER BY id LIMIT 0, 100;

使用批量操作

sql

-- 批量插入
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'), ('user3', 'user3@example.com');

-- 批量更新
UPDATE users SET status = 1 WHERE id IN (1, 2, 3);

-- 批量删除
DELETE FROM users WHERE id IN (1, 2, 3);

安全连接优化

SSL/TLS 配置

生成 SSL 证书

bash

# 生成 CA 私钥
openssl genrsa 2048 > ca-key.pem

# 生成 CA 证书
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# 生成服务器私钥
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem

# 生成服务器证书
openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# 生成客户端私钥
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem

# 生成客户端证书
openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 > client-cert.pem

# 验证证书
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

配置 SSL

ini

# 启用 SSL
ssl = ON

# SSL 证书路径
ssl_ca = /path/to/ca-cert.pem
ssl_cert = /path/to/server-cert.pem
ssl_key = /path/to/server-key.pem

# 要求所有连接使用 SSL
# 生产环境建议启用
require_secure_transport = ON

# 允许的 SSL 协议
# MySQL 8.0+ 支持的协议
tls_version = TLSv1.2,TLSv1.3

# 加密算法
# 推荐使用强加密算法
ssl_cipher = 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384'

客户端使用 SSL 连接

bash

# 使用 SSL 连接
mysql --ssl-ca=/path/to/ca-cert.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem -u user -p

# 验证 SSL 连接
mysql> SHOW STATUS LIKE 'Ssl_cipher';

连接安全

限制连接来源

ini

# 使用 bind-address 限制监听地址
bind-address = 192.168.1.100

# 使用防火墙限制连接来源
# iptables 示例
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

限制用户连接权限

sql

-- 只允许从特定 IP 连接
CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';

-- 只允许本地连接
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

-- 授予最小必要权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user'@'192.168.1.%';

复制网络优化

主从复制网络优化

二进制日志优化

ini

# 使用 row 格式，减少网络传输
# 虽然 row 格式的二进制日志更大，但复制更可靠
# 适合网络带宽充足的场景
binlog_format = row

# 二进制日志压缩
# MySQL 8.0+ 支持
# 减少网络传输量
binlog_row_image = minimal

# 二进制日志缓存大小
# 提高写入性能
binlog_cache_size = 32M
max_binlog_cache_size = 512M

# 单个二进制日志大小
# 避免过大的日志文件
max_binlog_size = 1G

# 二进制日志过期时间
# 避免占用过多磁盘空间
binlog_expire_logs_seconds = 604800

复制线程优化

ini

# 从库 I/O 线程数量
# MySQL 8.0+ 支持多线程复制
# 默认为 1
slave_parallel_workers = 4

# 并行复制类型
# LOGICAL_CLOCK：基于逻辑时钟的并行复制
# DATABASE：基于数据库的并行复制
slave_parallel_type = LOGICAL_CLOCK

# 从库 SQL 线程延迟阈值
# 用于监控复制延迟
slave_transaction_retries = 10

# 从库心跳间隔（秒）
# 用于检测主从连接状态
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON

半同步复制优化

ini

# 启用半同步复制
plugin_load_add = 'rpl_semi_sync_master=semisync_master.so'
plugin_load_add = 'rpl_semi_sync_slave=semisync_slave.so'

# 主库启用半同步复制
rpl_semi_sync_master_enabled = 1

# 主库等待确认的超时时间（毫秒）
# 超时后降级为异步复制
rpl_semi_sync_master_timeout = 10000

# 从库启用半同步复制
rpl_semi_sync_slave_enabled = 1

# 主库至少需要多少个从库确认
# MySQL 5.7+ 支持
rpl_semi_sync_master_wait_for_slave_count = 1

# 主库是否等待从库的确认
# ON：等待所有从库确认
# OFF：等待至少一个从库确认
rpl_semi_sync_master_wait_no_slave = OFF

组复制网络优化

组复制参数

ini

# 组复制本地地址
loose-group_replication_local_address = "192.168.1.100:33061"

# 组复制种子地址
loose-group_replication_group_seeds = "192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"

# 组复制通信超时时间（毫秒）
loose-group_replication_member_expel_timeout = 5000

# 组复制网络重试次数
loose-group_replication_transaction_size_limit = 150000000

# 组复制消息压缩
# MySQL 8.0+ 支持
# 减少网络传输量
loose-group_replication_compression_threshold = 1024

# 组复制压缩算法
# MySQL 8.0+ 支持
# 可选：none, zlib, lz4, zstd
loose-group_replication_message_compression_algorithms = "zlib,zstd,lz4"

监控与诊断

网络监控

系统级监控

bash

# 监控网络连接
netstat -an | grep 3306 | wc -l

# 监控网络流量
iftop -i eth0

# 监控网络连接状态
ss -s

# 监控 TCP 连接
ss -t state ESTABLISHED '( dport = :3306 or sport = :3306 )'

# 监控 UDP 连接
ss -u state ESTABLISHED '( dport = :3306 or sport = :3306 )'

MySQL 级监控

sql

-- 查看连接状态
SHOW GLOBAL STATUS LIKE 'Threads%';

-- 查看连接错误
SHOW GLOBAL STATUS LIKE 'Aborted%';

-- 查看网络统计信息
SHOW GLOBAL STATUS LIKE 'Bytes%';

-- 查看复制状态
SHOW SLAVE STATUS\G;

-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;

网络诊断

连接测试

bash

# 使用 telnet 测试连接
telnet mysql-server.example.com 3306

# 使用 mysql 客户端测试连接
mysql -h mysql-server.example.com -u user -p

# 使用 ping 测试网络延迟
ping -c 10 mysql-server.example.com

# 使用 traceroute 测试网络路径
traceroute mysql-server.example.com

# 使用 mtr 测试网络质量
mtr mysql-server.example.com

性能测试

bash

# 使用 sysbench 测试网络性能
sysbench --db-driver=mysql --mysql-host=mysql-server.example.com --mysql-port=3306 --mysql-user=user --mysql-password=password --mysql-db=test --table_size=1000000 --threads=16 --time=60 --events=0 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run

# 使用 mysqlslap 测试性能
mysqlslap --host=mysql-server.example.com --user=user --password=password --concurrency=16 --iterations=5 --create-schema=test --query="SELECT * FROM test.users LIMIT 100" --delimiter=";

最佳实践

网络配置最佳实践

根据实际需求调整参数：不要盲目复制配置，根据实际负载和服务器资源调整
监控关键指标：定期监控网络连接数、流量、延迟等指标
使用连接池：所有应用程序都应使用连接池
启用压缩：在网络带宽有限的场景下启用压缩
使用 SSL/TLS：在生产环境中启用 SSL/TLS 加密
限制连接来源：使用防火墙和用户权限限制连接来源

连接管理最佳实践

合理设置连接数：避免设置过大的最大连接数
定期清理空闲连接：使用连接池的最大空闲时间参数
验证连接有效性：使用连接池的验证查询
及时释放连接：应用程序使用完连接后及时关闭
监控连接泄漏：定期检查长时间运行的连接

复制网络最佳实践

使用可靠的网络：主从复制应使用可靠的网络连接
调整复制线程数：根据服务器CPU核数调整并行复制线程数
使用半同步复制：在对数据一致性要求高的场景下使用
监控复制延迟：定期监控复制延迟，及时发现问题
备份二进制日志：定期备份二进制日志，用于灾难恢复

常见问题（FAQ）

Q1: 如何优化 MySQL 网络性能？

A1: 可以从以下几个方面优化：

优化操作系统网络参数
优化 MySQL 网络配置
使用连接池
启用压缩传输
优化数据传输
使用 SSL/TLS 加密
优化复制网络

Q2: 何时启用压缩传输？

A2: 建议在以下场景启用压缩传输：

网络带宽有限
传输的数据量较大
客户端与服务器之间的网络延迟较高
应用程序对响应时间不敏感

Q3: 如何调整最大连接数？

A3: 应根据以下因素调整最大连接数：

服务器CPU核数：一般为CPU核数的2-4倍
内存大小：每个连接大约需要 2-10MB 内存
应用程序负载：根据最大并发需求
其他服务占用的资源：考虑服务器上其他服务的资源占用

Q4: 如何减少连接泄漏？

A4: 可以通过以下方式减少连接泄漏：

使用连接池管理连接
确保应用程序使用完连接后及时关闭
设置合理的连接超时和最大空闲时间
定期监控长时间运行的连接
使用连接池的验证查询

Q5: 如何优化主从复制网络？

A5: 可以从以下几个方面优化：

使用 row 格式的二进制日志
调整二进制日志缓存大小
启用并行复制
调整复制线程数
使用半同步复制
监控复制延迟

Q6: 如何诊断网络问题？

A6: 可以使用以下工具诊断网络问题：

ping：测试网络延迟
traceroute：测试网络路径
mtr：测试网络质量
telnet：测试连接
mysql 客户端：测试数据库连接
netstat/ss：监控网络连接
iftop：监控网络流量

Q7: 如何选择合适的连接池？

A7: 选择连接池时应考虑：

性能：连接池的性能和资源消耗
可靠性：连接池的稳定性和容错能力
功能：支持的功能和配置选项
社区支持：社区活跃度和文档质量
与应用程序的兼容性：是否支持应用程序的开发语言和框架

MySQL 网络优化 ​

网络基础配置 ​

操作系统网络优化 ​

TCP 参数优化 ​

持久化配置 ​

MySQL 网络配置 ​

基本网络参数 ​

连接优化参数 ​

传输优化参数 ​

连接优化 ​

连接池优化 ​

应用层连接池 ​

MySQL 连接池配置示例（HikariCP） ​

连接管理 ​

连接复用 ​

连接监控 ​

传输优化 ​

压缩传输 ​

启用压缩 ​

压缩效果评估 ​

数据传输优化 ​

只选择需要的列 ​

限制结果集大小 ​

使用批量操作 ​

安全连接优化 ​

SSL/TLS 配置 ​

生成 SSL 证书 ​

配置 SSL ​

客户端使用 SSL 连接 ​

连接安全 ​

限制连接来源 ​

限制用户连接权限 ​

复制网络优化 ​

主从复制网络优化 ​

二进制日志优化 ​

复制线程优化 ​

半同步复制优化 ​

组复制网络优化 ​

组复制参数 ​

监控与诊断 ​

网络监控 ​

系统级监控 ​

MySQL 级监控 ​

网络诊断 ​

连接测试 ​

性能测试 ​

最佳实践 ​

网络配置最佳实践 ​

连接管理最佳实践 ​

复制网络最佳实践 ​

常见问题（FAQ） ​

Q1: 如何优化 MySQL 网络性能？ ​

Q2: 何时启用压缩传输？ ​

MySQL 网络优化

网络基础配置

操作系统网络优化

TCP 参数优化

持久化配置

MySQL 网络配置

基本网络参数

连接优化参数

传输优化参数

连接优化

连接池优化

应用层连接池

MySQL 连接池配置示例（HikariCP）

连接管理

连接复用

连接监控

传输优化

压缩传输

启用压缩

压缩效果评估

数据传输优化

只选择需要的列

限制结果集大小

使用批量操作

安全连接优化

SSL/TLS 配置

生成 SSL 证书

配置 SSL

客户端使用 SSL 连接

连接安全

限制连接来源

限制用户连接权限

复制网络优化

主从复制网络优化

二进制日志优化

复制线程优化

半同步复制优化

组复制网络优化

组复制参数

监控与诊断

网络监控

系统级监控

MySQL 级监控

网络诊断

连接测试

性能测试

最佳实践

网络配置最佳实践

连接管理最佳实践

复制网络最佳实践

常见问题（FAQ）

Q1: 如何优化 MySQL 网络性能？

Q2: 何时启用压缩传输？