第20章：MySQL服务器优化

MySQL教程 / 性能优化

第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章：硬件与操作系统优化

第20章：MySQL服务器优化 - MySQL教程 - jackssybin 的个人博客

第20章：MySQL服务器优化

第20章：MySQL服务器优化

20.1 MySQL配置文件

20.1.1 配置文件位置

20.1.2 配置文件结构

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

20.2.1 InnoDB Buffer Pool

20.2.2 查询缓存（MySQL 5.7）

20.2.3 线程缓存

20.2.4 表缓存

20.2.5 排序缓冲区

20.2.6 连接缓冲区

20.3 连接配置优化

20.3.1 最大连接数

20.3.2 连接池配置

20.4 InnoDB配置优化 ⭐⭐⭐⭐⭐

20.4.1 Redo Log配置

20.4.2 Binlog配置

20.4.3 双1配置

20.4.4 刷新策略

20.4.5 I/O配置

20.4.6 其他InnoDB配置

20.5 慢查询日志配置

20.5.1 开启慢查询日志

20.5.2 分析慢查询日志

20.6 其他重要配置

20.6.1 SQL模式

20.6.2 字符集配置

20.6.3 时区配置

20.6.4 其他配置

20.7 性能监控

20.7.1 查看系统状态

20.7.2 性能指标

20.7.3 性能诊断

20.8 配置优化实战

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

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

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

20.9 本章总结

练习题