本文档风哥主要介绍MySQL参数优化的实战技巧，包括内存参数、InnoDB参数、连接参数等内容，风哥教程参考MySQL官方文档，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 参数优化概述
MySQL参数优化是提升数据库性能的关键手段：
1. 参数优化定义
参数优化特点：
– 调整MySQL配置参数
– 适应硬件和业务需求
– 提升数据库性能
– 优化资源使用
参数优化目标：
– 提高查询性能
– 优化内存使用
– 提升并发能力
– 增强稳定性
参数优化原则：
– 基于实际需求
– 逐步调整验证
– 监控优化效果
– 保持配置可追溯
2. 查看参数
查看所有参数：
mysql> SHOW VARIABLES;
输出示例：
+—————————————–+——————+
| Variable_name | Value |
+—————————————–+——————+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| … | … |
+—————————————–+——————+
查看特定参数：
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
输出示例：
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 |
+————————-+———–+
使用LIKE模糊查询：
mysql> SHOW VARIABLES LIKE ‘innodb%’;
输出示例：
+——————————————+————————+
| Variable_name | Value |
+——————————————+————————+
| innodb_buffer_pool_size | 134217728 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_log_file_size | 50331648 |
+——————————————+————————+
3. 设置参数
会话级别设置：
mysql> SET SESSION sort_buffer_size = 2097152;
输出示例：
Query OK, 0 rows affected (0.00 sec)
全局级别设置：
mysql> SET GLOBAL max_connections = 500;
输出示例：
Query OK, 0 rows affected (0.00 sec)
永久设置（配置文件）：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 500
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
4. 参数分类
按作用域分类：
+——————-+——————+——————+
| 类型 | 作用域 | 设置方式 |
+——————-+——————+——————+
| 全局参数 | 整个服务器 | SET GLOBAL |
| 会话参数 | 当前连接 | SET SESSION |
| 只读参数 | 配置文件 | 重启生效 |
+——————-+——————+——————+
按功能分类：
+——————-+——————+——————+
| 类别 | 参数示例 | 作用 |
+——————-+——————+——————+
| 内存参数 | buffer_pool_size | 内存分配 |
| 连接参数 | max_connections | 连接控制 |
| InnoDB参数 | log_file_size | 存储引擎配置 |
| 日志参数 | log_error | 日志配置 |
+——————-+——————+——————+
5. 参数状态监控
查看状态变量：
mysql> SHOW STATUS LIKE ‘Innodb%’;
输出示例：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_reads | 1000 |
| Innodb_row_lock_waits | 50 |
+—————————————+————-+
计算缓存命中率：
mysql> SELECT
VARIABLE_VALUE AS read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’;
输出示例：
+—————+
| read_requests |
+—————+
| 1000000 |
+—————+
1.2 参数类型分类
MySQL参数按功能可分为多个类别：
1. 内存参数
主要内存参数：
+—————————+———-+——————+
| 参数名 | 默认值 | 说明 |
+—————————+———-+——————+
| innodb_buffer_pool_size | 128MB | InnoDB缓冲池 |
| key_buffer_size | 8MB | MyISAM键缓冲 |
| query_cache_size | 0 | 查询缓存（8.0废弃）|
| tmp_table_size | 16MB | 临时表大小 |
| max_heap_table_size | 16MB | 内存表大小 |
| sort_buffer_size | 256KB | 排序缓冲 |
| join_buffer_size | 256KB | 连接缓冲 |
| read_buffer_size | 128KB | 读缓冲 |
+—————————+———-+——————+
查看内存参数：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE / 1024 / 1024 AS value_mb
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
‘innodb_buffer_pool_size’,
‘key_buffer_size’,
‘tmp_table_size’,
‘max_heap_table_size’
);
输出示例：
+————————-+———–+
| VARIABLE_NAME | value_mb |
+————————-+———–+
| innodb_buffer_pool_size | 128.0000 |
| key_buffer_size | 8.0000 |
| tmp_table_size | 16.0000 |
| max_heap_table_size | 16.0000 |
+————————-+———–+
2. 连接参数
主要连接参数：
+—————————+———-+——————+
| 参数名 | 默认值 | 说明 |
+—————————+———-+——————+
| max_connections | 151 | 最大连接数 |
| max_connect_errors | 100 | 最大错误数 |
| wait_timeout | 28800 | 连接超时 |
| interactive_timeout | 28800 | 交互超时 |
| max_user_connections | 0 | 用户最大连接 |
| thread_cache_size | -1 | 线程缓存 |
+—————————+———-+——————+
查看连接参数：
mysql> SHOW VARIABLES LIKE ‘%connect%’;
输出示例：
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
+————————–+——-+
3. InnoDB参数
主要InnoDB参数：
+——————————-+———-+——————+
| 参数名 | 默认值 | 说明 |
+——————————-+———-+——————+
| innodb_buffer_pool_size | 128MB | 缓冲池大小 |
| innodb_log_file_size | 48MB | 日志文件大小 |
| innodb_log_buffer_size | 16MB | 日志缓冲大小 |
| innodb_flush_log_at_trx_commit| 1 | 日志刷新策略 |
| innodb_lock_wait_timeout | 50 | 锁等待超时 |
| innodb_thread_concurrency | 0 | 并发线程数 |
+——————————-+———-+——————+
查看InnoDB参数：
mysql> SHOW VARIABLES LIKE ‘innodb%’;
输出示例：
+——————————————+————————+
| Variable_name | Value |
+——————————————+————————+
| innodb_buffer_pool_size | 134217728 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_lock_wait_timeout | 50 |
+——————————————+————————+
4. 日志参数
主要日志参数：
+—————————+———-+——————+
| 参数名 | 默认值 | 说明 |
+—————————+———-+——————+
| log_error | stderr | 错误日志位置 |
| slow_query_log | OFF | 慢查询日志开关 |
| long_query_time | 10 | 慢查询阈值 |
| log_queries_not_using_indexes| OFF | 记录无索引查询 |
| general_log | OFF | 通用日志开关 |
| binlog_format | ROW | 二进制日志格式 |
+—————————+———-+——————+
查看日志参数：
mysql> SHOW VARIABLES LIKE ‘%log%’;
输出示例：
+—————————————-+———————————–+
| Variable_name | Value |
+—————————————-+———————————–+
| log_error | /var/log/mysql/error.log |
| slow_query_log | OFF |
| long_query_time | 10.000000 |
+—————————————-+———————————–+
5. 查询优化参数
主要查询参数：
+—————————+———-+——————+
| 参数名 | 默认值 | 说明 |
+—————————+———-+——————+
| sort_buffer_size | 256KB | 排序缓冲 |
| join_buffer_size | 256KB | 连接缓冲 |
| read_buffer_size | 128KB | 顺序读缓冲 |
| read_rnd_buffer_size | 256KB | 随机读缓冲 |
| tmp_table_size | 16MB | 内存临时表大小 |
| max_seeks_for_key | 4G | 索引查找限制 |
+—————————+———-+——————+
查看查询参数：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE / 1024 AS value_kb
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
‘sort_buffer_size’,
‘join_buffer_size’,
‘read_buffer_size’
);
输出示例：
+——————-+———–+
| VARIABLE_NAME | value_kb |
+——————-+———–+
| sort_buffer_size | 256.0000 |
| join_buffer_size | 256.0000 |
| read_buffer_size | 128.0000 |
+——————-+———–+
1.3 参数特性
MySQL参数具有不同的特性需要了解：
1. 动态参数与静态参数
动态参数（运行时可修改）：
mysql> SET GLOBAL max_connections = 500;
输出示例：
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.max_connections;
输出示例：
+———————–+
| @@global.max_connections|
+———————–+
| 500 |
+———————–+
静态参数（需要重启）：
mysql> SET GLOBAL innodb_log_file_size = 256*1024*1024;
输出示例：
ERROR 1238 (HY000): Variable ‘innodb_log_file_size’ is a read only variable
静态参数修改方式：
vim /etc/my.cnf
[mysqld]
innodb_log_file_size = 256M
systemctl restart mysqld
输出示例：
MySQL service restarted.
2. 全局参数与会话参数
全局参数设置：
mysql> SET GLOBAL sort_buffer_size = 2097152;
输出示例：
Query OK, 0 rows affected (0.00 sec)
会话参数设置：
mysql> SET SESSION sort_buffer_size = 4194304;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看参数值：
mysql> SELECT
@@global.sort_buffer_size / 1024 AS global_kb,
@@session.sort_buffer_size / 1024 AS session_kb;
输出示例：
+———–+————+
| global_kb | session_kb |
+———–+————+
| 2048.00 | 4096.00 |
+———–+————+
3. 参数作用域
参数作用域分类：
+——————-+——————+——————+
| 作用域 | 示例参数 | 影响范围 |
+——————-+——————+——————+
| 全局 | max_connections | 所有连接 |
| 会话 | sort_buffer_size | 当前连接 |
| 全局+会话 | wait_timeout | 可分别设置 |
+——————-+——————+——————+
查看参数作用域：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_SCOPE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN (‘max_connections’, ‘sort_buffer_size’);
输出示例：
+——————-+—————-+
| VARIABLE_NAME | VARIABLE_SCOPE |
+——————-+—————-+
| max_connections | GLOBAL |
| sort_buffer_size | SESSION |
+——————-+—————-+
4. 参数默认值
查看参数默认值：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
DEFAULT_VALUE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = ‘innodb_buffer_pool_size’;
输出示例：
+————————-+—————-+—————+
| VARIABLE_NAME | VARIABLE_VALUE | DEFAULT_VALUE |
+————————-+—————-+—————+
| innodb_buffer_pool_size | 134217728 | 134217728 |
+————————-+—————-+—————+
5. 参数依赖关系
参数之间的依赖：
— tmp_table_size和max_heap_table_size共同决定内存临时表大小
mysql> SELECT
@@tmp_table_size / 1024 / 1024 AS tmp_mb,
@@max_heap_table_size / 1024 / 1024 AS heap_mb;
输出示例：
+———-+———+
| tmp_mb | heap_mb |
+———-+———+
| 16.0000 | 16.0000 |
+———-+———+
— innodb_buffer_pool_instances依赖buffer_pool_size
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_instances’;
输出示例：
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| innodb_buffer_pool_instances | 1 |
+——————————-+——-+
Part02-生产环境规划与建议
2.1 内存参数优化
内存参数是MySQL性能优化的核心：
1. InnoDB缓冲池优化
缓冲池大小设置原则：
– 专用服务器：物理内存的70-80%
– 共享服务器：物理内存的50-60%
– 最小建议：1GB以上
查看当前设置：
mysql> SELECT
@@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb,
@@innodb_buffer_pool_instances;
输出示例：
+——————+——————————+
| buffer_pool_gb | @@innodb_buffer_pool_instances|
+——————+——————————+
| 0.1250 | 1 |
+——————+——————————+
设置缓冲池大小：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
验证设置：
mysql> SELECT
@@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;
输出示例：
+——————+
| buffer_pool_gb |
+——————+
| 4.0000 |
+——————+
2. 缓冲池命中率监控
查看缓冲池状态：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool%’;
输出示例：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_reads | 1000 |
| Innodb_buffer_pool_wait_free | 0 |
+—————————————+————-+
计算命中率：
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’) AS read_requests,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) AS disk_reads,
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)
) * 100, 2) AS hit_ratio;
输出示例：
+—————+————+———–+
| read_requests | disk_reads | hit_ratio |
+—————+————+———–+
| 1000000 | 1000 | 99.90 |
+—————+————+———–+
3. MyISAM键缓冲优化
查看键缓冲设置：
mysql> SHOW VARIABLES LIKE ‘key_buffer_size’;
输出示例：
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| key_buffer_size | 8388608 |
+—————–+———-+
设置键缓冲：
mysql> SET GLOBAL key_buffer_size = 256*1024*1024;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看键缓冲使用：
mysql> SHOW STATUS LIKE ‘Key%’;
输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107163|
| Key_blocks_used | 100 |
| Key_read_requests | 50000 |
| Key_reads | 100 |
+————————+——-+
4. 临时表内存优化
查看临时表设置：
mysql> SELECT
@@tmp_table_size / 1024 / 1024 AS tmp_mb,
@@max_heap_table_size / 1024 / 1024 AS heap_mb;
输出示例：
+———-+———+
| tmp_mb | heap_mb |
+———-+———+
| 16.0000 | 16.0000 |
+———-+———+
设置临时表大小：
vim /etc/my.cnf
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
监控临时表使用：
mysql> SHOW STATUS LIKE ‘Created_tmp%’;
输出示例：
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 50 |
| Created_tmp_tables | 500 |
+————————-+——-+
5. 排序和连接缓冲优化
查看缓冲设置：
mysql> SELECT
@@sort_buffer_size / 1024 AS sort_kb,
@@join_buffer_size / 1024 AS join_kb,
@@read_buffer_size / 1024 AS read_kb,
@@read_rnd_buffer_size / 1024 AS rnd_kb;
输出示例：
+———-+———+———-+———+
| sort_kb | join_kb | read_kb | rnd_kb |
+———-+———+———-+———+
| 256.00 | 256.00 | 128.00 | 256.00 |
+———-+———+———-+———+
设置缓冲大小：
vim /etc/my.cnf
[mysqld]
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
2.2 InnoDB参数优化
InnoDB参数优化对性能影响重大：
1. 日志文件优化
查看日志设置：
mysql> SELECT
@@innodb_log_file_size / 1024 / 1024 AS log_file_mb,
@@innodb_log_buffer_size / 1024 / 1024 AS log_buffer_mb,
@@innodb_log_files_in_group;
输出示例：
+————-+—————+————————–+
| log_file_mb | log_buffer_mb | @@innodb_log_files_in_group|
+————-+—————+————————–+
| 48.00 | 16.00 | 2 |
+————-+—————+————————–+
设置日志文件大小：
vim /etc/my.cnf
[mysqld]
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 2
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
日志文件大小建议：
– 小型系统：64MB-128MB
– 中型系统：256MB-512MB
– 大型系统：1GB-2GB
2. 日志刷新策略
查看刷新策略：
mysql> SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’;
输出示例：
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
刷新策略说明：
+——-+——————+——————+
| 值 | 性能 | 安全性 |
+——-+——————+——————+
| 0 | 最高 | 最低（可能丢失1秒数据）|
| 1 | 最低 | 最高（ACID完全保证）|
| 2 | 中等 | 中等（可能丢失1秒数据）|
+——-+——————+——————+
设置刷新策略：
mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;
输出示例：
Query OK, 0 rows affected (0.00 sec)
3. 并发控制优化
查看并发设置：
mysql> SELECT
@@innodb_thread_concurrency,
@@innodb_concurrency_tickets,
@@innodb_thread_sleep_delay;
输出示例：
+—————————–+——————————+————————-+
| @@innodb_thread_concurrency | @@innodb_concurrency_tickets | @@innodb_thread_sleep_delay|
+—————————–+——————————+————————-+
| 0 | 500| 10000|
+—————————–+——————————+————————-+
设置并发参数：
vim /etc/my.cnf
[mysqld]
innodb_thread_concurrency = 0
innodb_concurrency_tickets = 5000
innodb_thread_sleep_delay = 10000
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
4. IO优化
查看IO设置：
mysql> SELECT
@@innodb_io_capacity,
@@innodb_io_capacity_max,
@@innodb_flush_method;
输出示例：
+——————-+———————–+——————-+
| @@innodb_io_capacity | @@innodb_io_capacity_max | @@innodb_flush_method|
+——————-+———————–+——————-+
| 200 | 2000 | fsync |
+——————-+———————–+——————-+
设置IO参数：
vim /etc/my.cnf
[mysqld]
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_flush_method = O_DIRECT
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
IO容量建议：
+——————-+——————+——————+
| 存储类型 | io_capacity | io_capacity_max |
+——————-+——————+——————+
| HDD | 200 | 2000 |
| SSD | 1000-2000 | 2000-4000 |
| NVMe | 2000-4000 | 4000-8000 |
+——————-+——————+——————+
5. 锁优化
查看锁设置：
mysql> SELECT
@@innodb_lock_wait_timeout,
@@innodb_deadlock_detect,
@@innodb_locks_unsafe_for_binlog;
输出示例：
+—————————+———————–+——————————-+
| @@innodb_lock_wait_timeout| @@innodb_deadlock_detect| @@innodb_locks_unsafe_for_binlog|
+—————————+———————–+——————————-+
| 50 | ON | OFF |
+—————————+———————–+——————————-+
设置锁参数：
mysql> SET GLOBAL innodb_lock_wait_timeout = 30;
输出示例：
Query OK, 0 rows affected (0.00 sec)
监控锁等待：
mysql> SHOW STATUS LIKE ‘Innodb_row_lock%’;
输出示例：
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 5000 |
| Innodb_row_lock_time_avg | 100 |
| Innodb_row_lock_time_max | 1000 |
| Innodb_row_lock_waits | 50 |
+——————————-+——-+
2.3 参数监控
建立完善的参数监控体系：
1. 参数变更监控
查看参数变更历史：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
SET_TIME,
SET_USER
FROM performance_schema.variables_info
WHERE SET_TIME IS NOT NULL
ORDER BY SET_TIME DESC
LIMIT 10;
输出示例：
+——————-+—————-+———————+———+
| VARIABLE_NAME | VARIABLE_VALUE | SET_TIME | SET_USER|
+——————-+—————-+———————+———+
| max_connections | 500 | 2026-04-01 10:00:00 | root |
| sort_buffer_size | 2097152 | 2026-04-01 09:00:00 | root |
+——————-+—————-+———————+———+
2. 内存使用监控
查看内存使用：
mysql> SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS used_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;
输出示例：
+—————————+———–+
| EVENT_NAME | used_mb |
+—————————+———–+
| memory/innodb/buf_buf_pool| 128.00 |
| memory/innodb/log_buffer | 16.00 |
+—————————+———–+
3. 状态变量监控
查看关键状态：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
‘Innodb_buffer_pool_read_requests’,
‘Innodb_buffer_pool_reads’,
‘Innodb_row_lock_waits’,
‘Created_tmp_disk_tables’,
‘Created_tmp_tables’
);
输出示例：
+——————————-+—————+
| VARIABLE_NAME | VARIABLE_VALUE|
+——————————-+—————+
| Innodb_buffer_pool_read_requests| 1000000 |
| Innodb_buffer_pool_reads | 1000 |
| Innodb_row_lock_waits | 50 |
| Created_tmp_disk_tables | 100 |
| Created_tmp_tables | 1000 |
+——————————-+—————+
4. 创建监控视图
创建参数监控视图：
mysql> CREATE VIEW v_param_monitor AS
SELECT
‘Buffer Pool Hit Ratio’ AS metric,
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)
) * 100, 2) AS value,
‘%’ AS unit,
CASE
WHEN (SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’) < 0.01
THEN 'OK' ELSE 'Warning' END AS status
UNION ALL
SELECT
'Disk Temp Table Ratio' AS metric,
ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Created_tmp_tables') * 100, 2) AS value,
'%' AS unit,
CASE
WHEN (SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Created_tmp_tables') < 0.1
THEN 'OK' ELSE 'Warning' END AS status;
查询监控数据：
mysql> SELECT * FROM v_param_monitor;
输出示例：
+———————-+——–+——+———+
| metric | value | unit | status |
+———————-+——–+——+———+
| Buffer Pool Hit Ratio| 99.90 | % | OK |
| Disk Temp Table Ratio| 10.00 | % | Warning |
+———————-+——–+——+———+
5. 告警配置
创建告警存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_param_health()
BEGIN
DECLARE buffer_hit_ratio DECIMAL(5,2);
DECLARE disk_temp_ratio DECIMAL(5,2);
SET buffer_hit_ratio = (
SELECT ROUND(100 – (VARIABLE_VALUE /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)) * 100, 2)
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’
);
IF buffer_hit_ratio < 95 THEN SELECT CONCAT('Warning: Buffer pool hit ratio is ', buffer_hit_ratio, '%') AS alert; END IF; END // mysql> DELIMITER ;
执行检查：
mysql> CALL check_param_health();
输出示例：
Empty set (0.00 sec)
Part03-生产环境项目实施方案
3.1 参数实施规范
制定参数实施规范确保一致性：
1. 参数配置规范
标准配置模板：
vim /etc/my.cnf
[mysqld]
# 基础配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
# 内存配置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
tmp_table_size = 64M
max_heap_table_size = 64M
# 连接配置
max_connections = 500
thread_cache_size = 100
wait_timeout = 28800
# InnoDB配置
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
# 日志配置
log_error = /var/log/mysql/error.log
slow_query_log = 1
long_query_time = 2
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
2. 参数变更流程
变更流程：
1. 评估变更影响
2. 在测试环境验证
3. 准备回滚方案
4. 选择低峰期执行
5. 监控变更效果
变更记录：
mysql> CREATE TABLE param_change_log (
id INT AUTO_INCREMENT PRIMARY KEY,
param_name VARCHAR(100),
old_value VARCHAR(200),
new_value VARCHAR(200),
change_reason TEXT,
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
输出示例：
Query OK, 0 rows affected (0.02 sec)
3. 参数验证规范
验证参数生效：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
‘innodb_buffer_pool_size’,
‘max_connections’,
‘innodb_log_file_size’
);
输出示例：
+————————-+—————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+————————-+—————-+
| innodb_buffer_pool_size | 4294967296 |
| max_connections | 500 |
| innodb_log_file_size | 268435456 |
+————————-+—————-+
4. 参数备份规范
导出当前配置：
mysql -e “SHOW VARIABLES” > mysql_variables_$(date +%Y%m%d).txt
输出示例：
Variables exported to file.
备份配置文件：
cp /etc/my.cnf /etc/my.cnf.bak.$(date +%Y%m%d)
输出示例：
Configuration file backed up.
5. 参数文档规范
创建参数文档表：
mysql> CREATE TABLE param_documentation (
param_name VARCHAR(100) PRIMARY KEY,
current_value VARCHAR(200),
description TEXT,
recommendation VARCHAR(200),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
输出示例：
Query OK, 0 rows affected (0.02 sec)
插入文档：
mysql> INSERT INTO param_documentation (param_name, current_value, description, recommendation)
VALUES (
‘innodb_buffer_pool_size’,
‘4G’,
‘InnoDB缓冲池大小，影响数据缓存性能’,
‘设置为物理内存的70-80%’
);
输出示例：
Query OK, 1 row affected (0.01 sec)
3.2 参数优化场景
不同场景下的参数优化策略：
1. 高并发场景
高并发配置：
vim /etc/my.cnf
[mysqld]
max_connections = 1000
thread_cache_size = 200
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 30
back_log = 500
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
验证配置：
mysql> SELECT @@max_connections, @@thread_cache_size;
输出示例：
+——————-+———————+
| @@max_connections | @@thread_cache_size |
+——————-+———————+
| 1000 | 200 |
+——————-+———————+
2. 大数据量场景
大数据量配置：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
3. 内存受限场景
小内存配置：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_file_size = 64M
key_buffer_size = 32M
tmp_table_size = 16M
max_heap_table_size = 16M
max_connections = 100
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
4. SSD存储场景
SSD优化配置：
vim /etc/my.cnf
[mysqld]
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
5. 复制场景
复制优化配置：
vim /etc/my.cnf
[mysqld]
# 主库配置
binlog_format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
binlog_cache_size = 1M
max_binlog_size = 100M
# 从库配置
relay_log_recovery = 1
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
3.3 参数维护
参数维护是长期运营的重要工作：
1. 定期检查
检查参数状态：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
‘innodb_buffer_pool_size’,
‘max_connections’,
‘innodb_log_file_size’
);
输出示例：
+————————-+—————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+————————-+—————-+
| innodb_buffer_pool_size | 4294967296 |
| max_connections | 500 |
+————————-+—————-+
2. 性能监控
监控关键指标：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
‘Innodb_buffer_pool_read_requests’,
‘Innodb_buffer_pool_reads’,
‘Threads_connected’,
‘Threads_running’
);
输出示例：
+——————————-+—————+
| VARIABLE_NAME | VARIABLE_VALUE|
+——————————-+—————+
| Innodb_buffer_pool_read_requests| 1000000 |
| Innodb_buffer_pool_reads | 1000 |
| Threads_connected | 50 |
| Threads_running | 5 |
+——————————-+—————+
3. 参数调优
根据监控结果调优：
mysql> — 缓冲池命中率低，增加缓冲池
mysql> SET GLOBAL innodb_buffer_pool_size = 8589934592;
输出示例：
Query OK, 0 rows affected (0.00 sec)
— 注意：部分参数需要重启才能生效
4. 配置更新
更新配置文件：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 8G
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
5. 文档更新
更新参数文档：
mysql> UPDATE param_documentation
SET current_value = ‘8G’,
last_updated = NOW()
WHERE param_name = ‘innodb_buffer_pool_size’;
输出示例：
Query OK, 1 row affected (0.01 sec)
Part04-生产案例与实战讲解
4.1 内存优化案例
以下是内存参数优化的实战案例：
# 案例：缓冲池命中率优化
# 问题描述：
# 缓冲池命中率只有85%，需要优化
# 步骤1：分析当前状态
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) AS disk_reads,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’) AS read_requests,
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)
) * 100, 2) AS hit_ratio;
# 输出示例：
# +————+—————+———–+
# | disk_reads | read_requests | hit_ratio |
# +————+—————+———–+
# | 150000 | 1000000 | 85.00 |
# +————+—————+———–+
# 步骤2：检查当前缓冲池大小
mysql> SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;
# 输出示例：
# +——————+
# | buffer_pool_gb |
# +——————+
# | 1.0000 |
# +——————+
# 步骤3：检查系统内存
free -g
# 输出示例：
# total used free
# Mem: 16 8 8
# 步骤4：增加缓冲池大小
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 8G
systemctl restart mysqld
# 输出示例：
# MySQL service restarted.
# 步骤5：验证优化效果
mysql> SELECT
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)
) * 100, 2) AS hit_ratio;
# 输出示例：
# +———–+
# | hit_ratio |
# +———–+
# | 99.50 |
# +———–+
# 性能提升：
# +——————+————+————+
# | 指标 | 优化前 | 优化后 |
# +——————+————+————+
# | 缓冲池命中率 | 85% | 99.5% |
# | 平均查询时间 | 100ms | 20ms |
# +——————+————+————+
4.2 InnoDB优化案例
以下是InnoDB参数优化的实战案例：
# 案例：写入性能优化
# 问题描述：
# 写入性能较慢，需要优化
# 步骤1：分析当前配置
mysql> SELECT
@@innodb_flush_log_at_trx_commit AS flush_policy,
@@innodb_log_file_size / 1024 / 1024 AS log_file_mb,
@@innodb_io_capacity;
# 输出示例：
# +————–+————-+——————-+
# | flush_policy | log_file_mb | @@innodb_io_capacity|
# +————–+————-+——————-+
# | 1 | 48.00 | 200 |
# +————–+————-+——————-+
# 步骤2：优化配置
vim /etc/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
systemctl restart mysqld
# 输出示例：
# MySQL service restarted.
# 步骤3：测试写入性能
mysql> INSERT INTO test_table (data)
SELECT REPEAT(‘a’, 1000) FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3) t;
# 输出示例：
# Query OK, 3 rows affected (0.01 sec)
# 性能对比：
# +——————+————+————+
# | 配置 | 写入TPS | 安全性 |
# +——————+————+————+
# | flush=1 | 1000 | 最高 |
# | flush=2 | 5000 | 中等 |
# +——————+————+————+
4.3 性能优化案例
以下是综合性能优化的实战案例：
# 案例：综合性能优化
# 问题描述：
# 数据库整体性能较差，需要全面优化
# 步骤1：收集系统信息
free -g
df -h /var/lib/mysql
cat /proc/cpuinfo | grep processor | wc -l
# 输出示例：
# 内存：16GB
# 磁盘：SSD 500GB
# CPU：8核
# 步骤2：制定优化方案
vim /etc/my.cnf
[mysqld]
# 基础配置
port = 3306
max_connections = 500
# 内存配置（16GB内存）
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 4
tmp_table_size = 64M
max_heap_table_size = 64M
# InnoDB配置
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 连接配置
thread_cache_size = 100
wait_timeout = 28800
# 日志配置
slow_query_log = 1
long_query_time = 2
systemctl restart mysqld
# 输出示例：
# MySQL service restarted.
# 步骤3：验证优化效果
mysql> SELECT
@@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_gb,
@@max_connections,
@@innodb_log_file_size / 1024 / 1024 AS log_mb;
# 输出示例：
# +————+——————-+——–+
# | buffer_gb | @@max_connections | log_mb |
# +————+——————-+——–+
# | 10.0000 | 500 | 512.00 |
# +————+——————-+——–+
# 性能提升：
# +——————+————+————+
# | 指标 | 优化前 | 优化后 |
# +——————+————+————+
# | QPS | 1000 | 5000 |
# | 缓冲池命中率 | 85% | 99% |
# | 平均响应时间 | 100ms | 20ms |
# +——————+————+————+
Part05-风哥经验总结与分享
5.1 参数优化最佳实践
以下是MySQL参数优化的最佳实践：
1. 内存优化原则
– 缓冲池设置为物理内存的70-80%
– 专用服务器可以更高
– 预留内存给操作系统
2. InnoDB优化原则
– 日志文件大小为缓冲池的25%
– 生产环境flush_log_at_trx_commit=1
– SSD使用O_DIRECT刷新方式
3. 连接优化原则
– max_connections根据业务需求设置
– thread_cache_size设置为max_connections的20%
– 合理设置超时时间
4. 日志优化原则
– 开启慢查询日志
– 合理设置long_query_time
– 定期分析慢查询
5. 变更管理原则
– 测试环境先验证
– 准备回滚方案
– 选择低峰期执行
– 监控变更效果
5.2 参数优化限制
以下是MySQL参数优化的主要限制：
1. 硬件限制
– 内存大小限制缓冲池
– 磁盘IO限制写入性能
– CPU限制并发处理
2. 参数依赖限制
– 部分参数需要重启
– 参数之间有依赖关系
– 动态修改有范围限制
3. 业务限制
– 业务特点影响参数选择
– 数据量影响内存需求
– 并发量影响连接设置
4. 兼容性限制
– 版本差异影响参数
– 存储引擎影响配置
– 操作系统影响设置
5. 安全性限制
– 性能与安全需要平衡
– 数据安全优先级高
– 复制配置影响安全
5.3 参数优化检查清单
以下是MySQL参数优化的检查清单：
1. 评估阶段检查
[ ] 是否评估了硬件资源
[ ] 是否分析了业务需求
[ ] 是否了解了数据特点
[ ] 是否确定了优化目标
2. 设计阶段检查
[ ] 是否制定了优化方案
[ ] 是否准备了回滚方案
[ ] 是否在测试环境验证
[ ] 是否记录了原始配置
3. 实施阶段检查
[ ] 是否选择了合适时间
[ ] 是否备份了配置文件
[ ] 是否按步骤执行
[ ] 是否验证了参数生效
4. 监控阶段检查
[ ] 是否监控了性能指标
[ ] 是否对比了优化效果
[ ] 是否记录了优化结果
[ ] 是否更新了文档
风哥提示：MySQL参数优化是提升数据库性能的关键手段，但需要根据实际环境和业务需求进行调整。InnoDB缓冲池是最重要的内存参数，建议设置为物理内存的70-80%。生产环境中innodb_flush_log_at_trx_commit建议设置为1以保证数据安全。参数优化是一个持续的过程，需要监控效果并根据实际情况调整。对于生产环境的参数变更，务必在测试环境验证后再执行。更多视频教程请访问www.fgedu.net.cn
注意：本文档内容基于MySQL 8.4官方文档编写，适合DBA人员在学习和测试中使用。在生产环境中应用时，请务必进行充分的测试和验证。参数优化需要根据实际硬件配置和业务需求进行调整，建议逐步优化并监控效果。文档中的命令和配置可能因MySQL版本不同而有所差异，请根据实际情况进行调整。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
