MySQL 性能调优

一、性能调优概述

1.1 调优层次

1.2 调优流程

二、系统层优化

2.1 操作系统配置

# /etc/sysctl.conf

# 网络优化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_keepalive_time = 120

# 内存优化
vm.swappiness = 1                    # 尽量不使用 swap
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5

# 文件句柄
fs.file-max = 65535
fs.aio-max-nr = 1048576

# 应用配置
sysctl -p

2.2 文件系统选择

# 推荐使用 XFS 或 ext4

# XFS 挂载选项（推荐用于 MySQL）
/dev/sda1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

# ext4 挂载选项
/dev/sda1 /data ext4 defaults,noatime,nodiratime,barrier=0 0 0

# 关键参数说明：
# noatime: 不更新文件访问时间
# nodiratime: 不更新目录访问时间
# nobarrier/barrier=0: 关闭写屏障（需要 RAID 卡有 BBU）

2.3 I/O 调度器

# 查看当前调度器
cat /sys/block/sda/queue/scheduler

# SSD 推荐使用 noop 或 deadline
echo noop > /sys/block/sda/queue/scheduler

# HDD 推荐使用 deadline
echo deadline > /sys/block/sda/queue/scheduler

# 永久设置（GRUB）
# elevator=deadline

2.4 NUMA 配置

# 检查 NUMA 状态
numactl --hardware

# MySQL 启动时禁用 NUMA
numactl --interleave=all mysqld

# 或在系统层面关闭 NUMA
# 在 BIOS 中禁用，或在 GRUB 中添加 numa=off

三、MySQL 参数优化

3.1 连接相关参数

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

# 单用户最大连接数
max_user_connections = 400

# 连接超时
wait_timeout = 600
interactive_timeout = 600

# 连接错误限制
max_connect_errors = 100000

# 线程缓存
thread_cache_size = 64

# 连接处理方式（高并发推荐）
thread_handling = pool-of-threads  # Percona/MariaDB

-- 监控连接使用情况
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Connections';

-- 计算连接使用率
Max_used_connections / max_connections < 85%

3.2 Buffer Pool 配置

[mysqld]
# Buffer Pool 大小（最重要的参数）
# 建议设置为物理内存的 60-80%
innodb_buffer_pool_size = 8G

# Buffer Pool 实例数
# 建议设置为 CPU 核数
innodb_buffer_pool_instances = 8

# 每个 chunk 大小
innodb_buffer_pool_chunk_size = 128M

# 启动时加载 Buffer Pool
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25

-- 监控 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算命中率（应 > 99%）
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /
Innodb_buffer_pool_read_requests * 100

3.3 日志相关配置

[mysqld]
# Redo Log
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# 刷盘策略
innodb_flush_log_at_trx_commit = 1  # 安全优先
# innodb_flush_log_at_trx_commit = 2  # 性能优先

# Binary Log
sync_binlog = 1                      # 安全优先
# sync_binlog = 0                    # 性能优先

# Binlog 缓存
binlog_cache_size = 4M

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

3.4 I/O 相关配置

[mysqld]
# I/O 容量（根据磁盘类型设置）
innodb_io_capacity = 200          # HDD
innodb_io_capacity = 2000         # SSD
innodb_io_capacity_max = 4000

# I/O 线程
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 刷新方法
innodb_flush_method = O_DIRECT

# 预读
innodb_read_ahead_threshold = 56

# Doublewrite（SSD 可考虑关闭）
innodb_doublewrite = ON

3.5 查询缓存（已废弃）

# MySQL 8.0 已移除查询缓存
# MySQL 5.7 建议关闭
query_cache_type = 0
query_cache_size = 0

3.6 临时表配置

[mysqld]
# 内存临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 临时文件目录（建议放在 SSD）
tmpdir = /tmp/mysql

3.7 排序和连接配置

[mysqld]
# 排序缓冲（每个会话）
sort_buffer_size = 4M

# 连接缓冲（每个会话）
join_buffer_size = 4M

# 读取缓冲
read_buffer_size = 1M
read_rnd_buffer_size = 1M

四、配置模板

4.1 小型服务器（4 核 8G）

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

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

# 连接
max_connections = 200
thread_cache_size = 32
wait_timeout = 600

# InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200

# 日志
slow_query_log = 1
long_query_time = 2
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1

# 临时表
tmp_table_size = 32M
max_heap_table_size = 32M

4.2 中型服务器（16 核 64G）

[mysqld]
# 连接
max_connections = 500
thread_cache_size = 64
wait_timeout = 600

# InnoDB
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 日志
slow_query_log = 1
long_query_time = 1
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M

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

# 排序和连接
sort_buffer_size = 4M
join_buffer_size = 4M

4.3 大型服务器（32 核 128G+）

[mysqld]
# 连接
max_connections = 1000
thread_cache_size = 128
wait_timeout = 600

# InnoDB
innodb_buffer_pool_size = 96G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_page_cleaners = 8
innodb_purge_threads = 4

# 日志
slow_query_log = 1
long_query_time = 0.5
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 8M

# 临时表
tmp_table_size = 128M
max_heap_table_size = 128M

# 排序和连接
sort_buffer_size = 8M
join_buffer_size = 8M

五、性能监控

5.1 关键指标

-- QPS (每秒查询数)
SHOW GLOBAL STATUS LIKE 'Questions';
-- QPS = Questions / Uptime

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

-- 连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

-- Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 锁等待
SHOW STATUS LIKE 'Innodb_row_lock%';

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

5.2 Performance Schema

-- 开启 Performance Schema
SHOW VARIABLES LIKE 'performance_schema';

-- 查看耗时最长的 SQL
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_ms,
    SUM_TIMER_WAIT/1000000000000 as total_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看等待事件
SELECT
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000000 as total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

5.3 sys Schema

-- 查看最消耗资源的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看 I/O 统计
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

-- 查看用户统计
SELECT * FROM sys.user_summary;

5.4 SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

-- 重要部分：
-- SEMAPHORES: 信号量等待
-- TRANSACTIONS: 事务信息
-- FILE I/O: I/O 信息
-- BUFFER POOL AND MEMORY: 内存使用
-- ROW OPERATIONS: 行操作统计

六、常见性能问题诊断

6.1 CPU 使用率高

-- 可能原因：
-- 1. 复杂 SQL 查询
-- 2. 全表扫描
-- 3. 排序操作
-- 4. 锁竞争

-- 诊断步骤：
-- 1. 查看正在执行的 SQL
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

-- 2. 查看慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 3. 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

6.2 内存使用高

-- 可能原因：
-- 1. Buffer Pool 设置过大
-- 2. 连接数过多
-- 3. 临时表占用
-- 4. 排序缓冲

-- 诊断步骤：
-- 1. 查看 Buffer Pool 使用
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 2. 查看连接内存
SELECT * FROM sys.memory_by_thread_by_current_bytes LIMIT 10;

-- 3. 查看全局内存
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

6.3 I/O 等待高

-- 可能原因：
-- 1. Buffer Pool 太小
-- 2. 脏页刷新频繁
-- 3. 日志写入瓶颈
-- 4. 随机 I/O 过多

-- 诊断步骤：
-- 1. 查看 I/O 状态
SHOW STATUS LIKE 'Innodb_data%';
SHOW STATUS LIKE 'Innodb_os_log%';

-- 2. 查看 I/O 等待
SELECT * FROM sys.io_global_by_wait_by_bytes LIMIT 10;

-- 3. 查看 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

6.4 锁等待严重

-- 可能原因：
-- 1. 长事务
-- 2. 锁范围过大
-- 3. 索引缺失
-- 4. 死锁

-- 诊断步骤：
-- 1. 查看当前锁
SELECT * FROM performance_schema.data_locks;

-- 2. 查看锁等待
SELECT * FROM sys.innodb_lock_waits\G

-- 3. 查看长事务
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- 4. 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- 查看 LATEST DETECTED DEADLOCK 部分

七、调优案例

7.1 案例一：Buffer Pool 优化

问题：Buffer Pool 命中率只有 95%

分析：
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_read_requests: 10000000
-- Innodb_buffer_pool_reads: 500000
-- 命中率 = (10000000 - 500000) / 10000000 = 95%

解决方案：
1. 增加 Buffer Pool 大小
   SET GLOBAL innodb_buffer_pool_size = 16G;

2. 增加 Buffer Pool 实例
   修改配置文件，重启

效果：
命中率提升到 99.5%

7.2 案例二：连接数优化

问题：经常出现 "Too many connections" 错误

分析：
SHOW VARIABLES LIKE 'max_connections';  -- 151
SHOW STATUS LIKE 'Max_used_connections';  -- 150

解决方案：
1. 增加最大连接数
   SET GLOBAL max_connections = 500;

2. 优化应用连接池配置

3. 减少连接空闲时间
   SET GLOBAL wait_timeout = 300;

4. 增加线程缓存
   SET GLOBAL thread_cache_size = 64;

7.3 案例三：I/O 性能优化

问题：磁盘 I/O 等待高

分析：
iostat -x 1
-- await 高达 50ms+

SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
-- 脏页数量过高

解决方案：
1. 增加 I/O 容量
   SET GLOBAL innodb_io_capacity = 2000;
   SET GLOBAL innodb_io_capacity_max = 4000;

2. 调整脏页刷新
   SET GLOBAL innodb_max_dirty_pages_pct = 50;

3. 使用 O_DIRECT
   innodb_flush_method = O_DIRECT

4. 升级到 SSD

八、调优检查清单

8.1 配置检查

□ Buffer Pool 大小是否合适（60-80% 物理内存）
□ Buffer Pool 实例数是否等于 CPU 核数
□ Redo Log 大小是否足够（256M-1G）
□ 连接数配置是否合理
□ 超时参数是否设置
□ I/O 容量是否匹配磁盘类型

8.2 性能检查

□ Buffer Pool 命中率 > 99%
□ 慢查询数量是否正常
□ 锁等待是否严重
□ 临时表使用是否过多
□ 连接数使用率 < 85%

8.3 定期维护

□ 分析表统计信息
□ 优化表碎片
□ 清理慢查询日志
□ 检查索引使用情况
□ 审查慢查询

九、总结

调优原则

1. 先监控后优化：了解瓶颈再动手
2. 一次改一个：便于评估效果
3. 测试验证：在测试环境验证
4. 建立基线：记录优化前后对比

关键参数

参数	建议值	说明
innodb_buffer_pool_size	物理内存的 60-80%	最重要的参数
innodb_log_file_size	256M-1G	根据写入量调整
innodb_flush_log_at_trx_commit	1	安全优先
max_connections	根据业务	不要过大
innodb_io_capacity	根据磁盘	HDD:200, SSD:2000+

监控指标

1. QPS/TPS：整体负载
2. Buffer Pool 命中率：内存效率
3. 慢查询数：SQL 质量
4. 锁等待时间：并发问题
5. 连接数：应用压力