MySQL教程 / 第 200 节

第20章：MySQL服务器优化

合理的参数配置可以让MySQL性能提升数倍

20.1 MySQL配置文件

20.1.1 配置文件位置

# Linux配置文件位置（按优先级）
/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf
--defaults-file指定的文件

# 查看配置文件位置
mysql --help | grep my.cnf

# 查看当前使用的配置
mysql -u root -p -e "SHOW VARIABLES"

# 查看配置文件
cat /etc/my.cnf

20.1.2 配置文件结构

# /etc/my.cnf

[client]
# 客户端配置
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
# mysql命令行工具配置
no-auto-rehash
prompt = "\\u@\\h [\\d]> "

[mysqld]
# 服务器配置（最重要）
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接配置
max_connections = 1000
max_connect_errors = 100000

# InnoDB配置
innodb_buffer_pool_size = 8G
innodb_log_file_size = 512M

[mysqldump]
# mysqldump工具配置
quick
max_allowed_packet = 64M

20.2 内存配置优化 ⭐⭐⭐⭐⭐

20.2.1 InnoDB Buffer Pool

# InnoDB Buffer Pool：最重要的内存配置
# 作用：缓存数据页和索引页

[mysqld]
# 大小设置（建议物理内存的50%-70%）
innodb_buffer_pool_size = 8G

# 实例数量（MySQL 5.5+）
# 建议：每个实例1GB，总大小/1GB
innodb_buffer_pool_instances = 8

# 预热Buffer Pool（MySQL 5.6+）
# 关闭时保存Buffer Pool状态，启动时加载
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

# 查看Buffer Pool使用情况
SHOW ENGINE INNODB STATUS\G

# 查看Buffer Pool命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

# 计算命中率
# 命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
# 建议：命中率 > 99%

20.2.2 查询缓存（MySQL 5.7）

# 查询缓存（MySQL 8.0已移除）
[mysqld]
# 是否开启（建议关闭）
query_cache_type = 0

# 缓存大小
query_cache_size = 0

# 为什么建议关闭？
# 1. 任何表的更新都会清空该表的所有缓存
# 2. 并发性能差（全局锁）
# 3. 命中率低
# 4. MySQL 8.0已移除

# 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

20.2.3 线程缓存

# 线程缓存：缓存空闲的连接线程
[mysqld]
# 缓存的线程数
thread_cache_size = 64

# 查看线程缓存状态
SHOW STATUS LIKE 'Threads%';

# Threads_created：创建的线程数
# Threads_cached：缓存的线程数
# Threads_connected：当前连接数
# Threads_running：正在运行的线程数

# 如果Threads_created持续增长，说明thread_cache_size太小

20.2.4 表缓存

# 表缓存：缓存打开的表
[mysqld]
# 缓存的表数量
table_open_cache = 4096

# 每个连接的表缓存
table_open_cache_instances = 16

# 查看表缓存状态
SHOW STATUS LIKE 'Open%';

# Open_tables：当前打开的表数量
# Opened_tables：历史打开的表数量

# 如果Opened_tables持续增长，说明table_open_cache太小

20.2.5 排序缓冲区

# 排序缓冲区：ORDER BY、GROUP BY使用
[mysqld]
# 每个连接的排序缓冲区大小
sort_buffer_size = 2M

# 不要设置太大（会占用大量内存）
# 建议：256K - 4M

# 查看排序状态
SHOW STATUS LIKE 'Sort%';

# Sort_merge_passes：多次排序的次数
# 如果该值很大，说明sort_buffer_size太小

20.2.6 连接缓冲区

# 连接缓冲区：JOIN使用
[mysqld]
# 每个连接的JOIN缓冲区大小
join_buffer_size = 2M

# 建议：256K - 4M

# 读缓冲区
read_buffer_size = 1M
read_rnd_buffer_size = 2M

20.3 连接配置优化

20.3.1 最大连接数

[mysqld]
# 最大连接数
max_connections = 1000

# 最大错误连接数
max_connect_errors = 100000

# 连接超时时间
connect_timeout = 10

# 交互式连接超时时间
interactive_timeout = 28800  # 8小时

# 非交互式连接超时时间
wait_timeout = 28800  # 8小时

# 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

# 查看连接详情
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;

# 计算合理的max_connections
# max_connections = (可用内存 - 系统内存 - Buffer Pool) / 单个连接内存
# 单个连接内存 ≈ sort_buffer_size + join_buffer_size + read_buffer_size + ...

20.3.2 连接池配置

# 线程池（MySQL Enterprise或Percona Server）
[mysqld]
thread_handling = pool-of-threads
thread_pool_size = 16  # CPU核心数
thread_pool_max_threads = 1000

# 优势：
# ✅ 减少线程创建开销
# ✅ 提高并发性能
# ✅ 减少上下文切换

20.4 InnoDB配置优化 ⭐⭐⭐⭐⭐

20.4.1 Redo Log配置

[mysqld]
# Redo Log文件大小
innodb_log_file_size = 512M

# Redo Log文件数量
innodb_log_files_in_group = 2

# Redo Log缓冲区大小
innodb_log_buffer_size = 16M

# Redo Log刷新策略（最重要）
innodb_flush_log_at_trx_commit = 1

# innodb_flush_log_at_trx_commit的值：
# 0：每秒刷新一次（性能最好，可能丢失1秒数据）
# 1：每次事务提交刷新（最安全，性能最差）⭐ 推荐
# 2：每次事务提交写入OS缓存，每秒刷新（折中）

# 性能对比：
# 0: 1000 TPS
# 1: 100 TPS
# 2: 500 TPS

# 建议：
# 生产环境：1（数据安全第一）
# 开发环境：2（性能和安全平衡）
# 测试环境：0（性能优先）

20.4.2 Binlog配置

[mysqld]
# 开启binlog
log_bin = mysql-bin

# binlog格式
binlog_format = ROW  # ROW/STATEMENT/MIXED

# binlog缓冲区大小
binlog_cache_size = 4M

# binlog刷新策略
sync_binlog = 1

# sync_binlog的值：
# 0：不主动刷新，由OS决定（性能最好，可能丢失数据）
# 1：每次事务提交刷新（最安全，性能最差）⭐ 推荐
# N：每N次事务提交刷新（折中）

# binlog过期时间
expire_logs_days = 7  # MySQL 5.7
binlog_expire_logs_seconds = 604800  # MySQL 8.0（7天）

# 建议：
# 生产环境：sync_binlog = 1
# 开发环境：sync_binlog = 0

20.4.3 双1配置

# 双1配置：保证数据安全
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# 优点：
# ✅ 数据绝对安全
# ✅ 不会丢失任何已提交的事务

# 缺点：
# ❌ 性能下降（每次提交都要刷盘）

# 性能优化：
# 1. 使用SSD（减少I/O延迟）
# 2. 使用RAID卡缓存（带电池）
# 3. 批量提交事务

20.4.4 刷新策略

[mysqld]
# 脏页刷新策略
innodb_flush_method = O_DIRECT

# innodb_flush_method的值：
# fsync：默认值
# O_DIRECT：绕过OS缓存，直接写磁盘（推荐）⭐
# O_DSYNC：同步写数据文件

# O_DIRECT的优势：
# ✅ 避免双重缓冲（OS缓存 + Buffer Pool）
# ✅ 减少内存占用
# ✅ 提高性能

# 脏页刷新比例
innodb_max_dirty_pages_pct = 75  # 脏页达到75%时开始刷新

# 自适应刷新
innodb_adaptive_flushing = 1

# 查看脏页比例
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';

20.4.5 I/O配置

[mysqld]
# I/O线程数
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 建议：
# SSD：8-16
# HDD：4-8

# I/O容量
innodb_io_capacity = 200  # HDD
innodb_io_capacity = 2000  # SSD

# 最大I/O容量
innodb_io_capacity_max = 2000  # HDD
innodb_io_capacity_max = 4000  # SSD

# 查看I/O状态
SHOW ENGINE INNODB STATUS\G

20.4.6 其他InnoDB配置

[mysqld]
# 每表一个文件
innodb_file_per_table = 1  # 推荐开启

# 自动扩展
innodb_autoextend_increment = 64  # 每次扩展64MB

# 锁等待超时
innodb_lock_wait_timeout = 50  # 50秒

# 死锁检测
innodb_deadlock_detect = 1

# 在线DDL
innodb_online_alter_log_max_size = 128M

# 统计信息
innodb_stats_persistent = 1  # 持久化统计信息
innodb_stats_auto_recalc = 1  # 自动更新统计信息

20.5 慢查询日志配置

20.5.1 开启慢查询日志

[mysqld]
# 开启慢查询日志
slow_query_log = 1

# 慢查询日志文件
slow_query_log_file = /var/lib/mysql/slow.log

# 慢查询阈值（秒）
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# 限制每分钟记录的未使用索引的查询数量
log_throttle_queries_not_using_indexes = 10

# 记录慢的管理语句（ALTER TABLE等）
log_slow_admin_statements = 1

# 最小扫描行数
min_examined_row_limit = 1000

# 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW STATUS LIKE 'Slow_queries';

20.5.2 分析慢查询日志

# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# -s t：按查询时间排序
# -t 10：显示前10条

# 使用pt-query-digest分析（推荐）
pt-query-digest /var/lib/mysql/slow.log

# 输出到文件
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

# 只分析指定时间范围
pt-query-digest --since '2024-11-10 00:00:00' \
  --until '2024-11-10 23:59:59' \
  /var/lib/mysql/slow.log

20.6 其他重要配置

20.6.1 SQL模式

[mysqld]
# SQL模式（推荐严格模式）
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 常用SQL模式：
# STRICT_TRANS_TABLES：严格模式
# NO_ZERO_IN_DATE：不允许日期中有0
# NO_ZERO_DATE：不允许0000-00-00
# ERROR_FOR_DIVISION_BY_ZERO：除以0报错
# NO_ENGINE_SUBSTITUTION：不自动替换存储引擎

# 查看SQL模式
SHOW VARIABLES LIKE 'sql_mode';

# 临时修改
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

20.6.2 字符集配置

[mysqld]
# 服务器字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 初始化连接字符集
init_connect = 'SET NAMES utf8mb4'

[client]
# 客户端字符集
default-character-set = utf8mb4

# 查看字符集
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

20.6.3 时区配置

[mysqld]
# 时区设置
default-time-zone = '+08:00'  # 东八区

# 查看时区
SHOW VARIABLES LIKE 'time_zone';

# 临时修改
SET time_zone = '+08:00';
SET GLOBAL time_zone = '+08:00';

20.6.4 其他配置

[mysqld]
# 最大包大小
max_allowed_packet = 64M

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 打开文件数限制
open_files_limit = 65535

# 表定义缓存
table_definition_cache = 4096

# 事务隔离级别
transaction_isolation = REPEATABLE-READ

# 自动提交
autocommit = 1

# 跳过域名解析
skip-name-resolve = 1  # 推荐开启，提高连接速度

# 小写表名
lower_case_table_names = 1  # Windows默认1，Linux默认0

20.7 性能监控

20.7.1 查看系统状态

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

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

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 查看慢查询
SHOW STATUS LIKE 'Slow_queries';

-- 查看表锁
SHOW STATUS LIKE 'Table_locks%';

-- 查看临时表
SHOW STATUS LIKE 'Created_tmp%';

20.7.2 性能指标

-- 1. QPS（每秒查询数）
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';
-- QPS = Questions / Uptime

-- 2. TPS（每秒事务数）
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
-- TPS = (Com_commit + Com_rollback) / Uptime

-- 3. Buffer Pool命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- 命中率 = (1 - reads / read_requests) * 100%

-- 4. 线程缓存命中率
SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Connections';
-- 命中率 = (1 - Threads_created / Connections) * 100%

-- 5. 表缓存命中率
SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Open_tables';
-- 如果Opened_tables持续增长，说明缓存不足

20.7.3 性能诊断

-- 查看当前正在执行的查询
SHOW PROCESSLIST;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看表统计信息
SELECT * FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

-- 查看索引统计信息
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb';

20.8 配置优化实战

20.8.1 小型服务器配置（2GB内存）

[mysqld]
# 基本配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接配置
max_connections = 200
max_connect_errors = 100000
connect_timeout = 10
wait_timeout = 28800

# InnoDB配置
innodb_buffer_pool_size = 1G  # 50%内存
innodb_buffer_pool_instances = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Binlog配置
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7

# 其他配置
thread_cache_size = 32
table_open_cache = 2048
sort_buffer_size = 1M
join_buffer_size = 1M
max_allowed_packet = 64M
tmp_table_size = 32M
max_heap_table_size = 32M

# 慢查询
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

# 其他
skip-name-resolve = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE

20.8.2 中型服务器配置（16GB内存）

[mysqld]
# InnoDB配置
innodb_buffer_pool_size = 10G  # 60%内存
innodb_buffer_pool_instances = 10
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000  # SSD
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 连接配置
max_connections = 500
thread_cache_size = 64

# 缓存配置
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

# 其他配置同小型服务器

20.8.3 大型服务器配置（64GB内存）

[mysqld]
# InnoDB配置
innodb_buffer_pool_size = 40G  # 60%内存
innodb_buffer_pool_instances = 16
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 4000  # SSD
innodb_io_capacity_max = 8000
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# 连接配置
max_connections = 1000
thread_cache_size = 128

# 缓存配置
table_open_cache = 8192
table_definition_cache = 8192
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 128M
max_heap_table_size = 128M

# 其他配置同小型服务器

20.9 本章总结

本章学习内容：

✅ MySQL配置文件
✅ 内存配置优化（Buffer Pool、线程缓存、表缓存）⭐⭐⭐⭐⭐
✅ 连接配置优化
✅ InnoDB配置优化（Redo Log、Binlog、双1配置）⭐⭐⭐⭐⭐
✅ 慢查询日志配置
✅ 其他重要配置
✅ 性能监控
✅ 配置优化实战（小中大型服务器）⭐⭐⭐⭐⭐

重点掌握：

innodb_buffer_pool_size = 物理内存的50%-70%
双1配置保证数据安全
innodb_flush_method = O_DIRECT
开启慢查询日志
skip-name-resolve提高连接速度
根据服务器规模调整参数

核心参数：

innodb_buffer_pool_size：最重要
innodb_flush_log_at_trx_commit：数据安全
sync_binlog：数据安全
max_connections：连接数
innodb_io_capacity：I/O性能

双1配置：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

性能监控指标：

QPS：每秒查询数
TPS：每秒事务数
Buffer Pool命中率
慢查询数量

面试重点：

innodb_buffer_pool_size如何设置
双1配置的作用
innodb_flush_log_at_trx_commit的值
如何优化MySQL性能
慢查询日志的作用

下一章预告： 硬件与操作系统优化

练习题

innodb_buffer_pool_size应该设置为多少？
什么是双1配置？
innodb_flush_log_at_trx_commit有哪些值？各有什么区别？
innodb_flush_method推荐设置为什么？
如何开启慢查询日志？
如何查看Buffer Pool命中率？
如何计算QPS和TPS？
skip-name-resolve的作用是什么？
为什么建议关闭查询缓存？
设计一个16GB内存服务器的MySQL配置

继续学习： 第21章：硬件与操作系统优化