17认证网MySQL参数调优实战：20个关键参数的最佳配置
一、概述
1.1 背景介绍
运维MySQL数据库十年有余，见过太多因为参数配置不当导致的性能问题。有的公司用着默认配置跑生产环境，128GB内存的服务器上InnoDB缓冲池只有128MB；有的把max_connections设成几万，结果OOM把数据库打挂；还有的sync_binlog设成1，却抱怨写入性能差。
MySQL的可配置参数有数百个，但真正影响性能的核心参数只有二三十个。本文将基于十年运维经验，筛选出20个最关键的参数，详细讲解每个参数的作用原理、取值依据和调优方法。这些参数经过多个大型生产环境的验证，能够覆盖90%以上的调优场景。
本文的目标不是列出一堆参数让人照抄，而是让读者理解每个参数背后的原理，能够根据自己的硬件配置和业务特点，做出正确的调优决策。
1.2 技术特点
MySQL架构与参数关系
┌─────────────────────────────────────────────┐
│ MySQL Server │
│ ┌─────────────────────────────────────┐ │
│ │ Connection Layer │ │
│ │ • max_connections │ │
│ │ • thread_cache_size │ │
│ │ • wait_timeout │ │
│ └─────────────────────────────────────┘ │
│ ┌─────────────────────────────────────┐ │
│ │ SQL Layer │ │
│ │ • sort_buffer_size │ │
│ │ • join_buffer_size │ │
│ │ • tmp_table_size │ │
│ └─────────────────────────────────────┘ │
│ ┌─────────────────────────────────────┐ │
│ │ Storage Engine (InnoDB) │ │
│ │ • innodb_buffer_pool_size │ │
│ │ • innodb_log_file_size │ │
│ │ • innodb_flush_log_at_trx_commit │ │
│ │ • innodb_io_capacity │ │
│ └─────────────────────────────────────┘ │
└─────────────────────────────────────────────┘
│
┌───────────────────┴───────────────────┐
│ Disk I/O │
│ • Data Files • Redo Log • Binlog │
└────────────────────────────────────────┘
参数分类
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
调优原则
-
理解为先：先理解参数作用，再调整数值 -
一次一个：每次只调整一个参数，观察效果 -
监控验证：调整后必须通过监控验证效果 -
压测确认：重大调整需在测试环境压测验证 -
记录变更：所有调整都要记录变更历史
1.3 适用场景
本文的调优建议适用于：
-
MySQL 8.0.35+ 或 8.4 LTS版本 -
专用数据库服务器（非混合部署） -
16GB以上内存的服务器 -
SSD/NVMe存储环境 -
OLTP为主的业务场景
对于以下场景需要特别调整：
-
OLAP分析型场景：需要增大排序和临时表相关缓冲区 -
混合部署场景：需要严格限制MySQL内存使用 -
HDD机械硬盘：IO相关参数需要保守设置
1.4 环境要求
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
二、详细步骤
2.1 准备工作
2.1.1 当前配置检查
调优之前，先了解当前配置状态：
-- 查看关键参数当前值
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
'innodb_buffer_pool_size',
'innodb_buffer_pool_instances',
'innodb_log_file_size',
'innodb_log_buffer_size',
'innodb_flush_log_at_trx_commit',
'innodb_flush_method',
'innodb_io_capacity',
'innodb_io_capacity_max',
'innodb_read_io_threads',
'innodb_write_io_threads',
'max_connections',
'thread_cache_size',
'table_open_cache',
'sort_buffer_size',
'join_buffer_size',
'tmp_table_size',
'max_heap_table_size',
'sync_binlog',
'binlog_cache_size'
)
ORDERBY VARIABLE_NAME;
2.1.2 硬件信息收集
#!/bin/bash
# collect_hardware_info.sh
# 收集服务器硬件信息
echo"=== MySQL调优信息收集 ==="
echo -e "\n[CPU信息]"
lscpu | grep -E "^(CPU\(s\)|Thread|Core|Socket|Model name)"
echo -e "\n[内存信息]"
free -h
cat /proc/meminfo | grep -E "^(MemTotal|MemFree|Cached|Buffers)"
echo -e "\n[磁盘信息]"
lsblk -d -o NAME,SIZE,ROTA,TYPE,MODEL
# ROTA=0表示SSD，ROTA=1表示HDD
echo -e "\n[磁盘IO能力测试]"
# 使用fio测试4K随机读写IOPS
ifcommand -v fio &> /dev/null; then
echo"测试4K随机读IOPS..."
fio --name=randread --ioengine=libaio --direct=1 --bs=4k \
--iodepth=64 --rw=randread --size=1G --runtime=30 \
--time_based --filename=/tmp/fio_test \
2>/dev/null | grep -E "(read:|IOPS)"
fi
echo -e "\n[MySQL数据目录磁盘使用]"
df -h /var/lib/mysql
echo -e "\n[当前MySQL进程内存使用]"
ps aux | grep mysqld | grep -v grep | awk '{print "RSS: " $6/1024 " MB"}'
2.1.3 性能基线记录
-- 记录调优前的性能基线
-- 1. InnoDB缓冲池命中率
SELECT
(1 - (
(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')
)) * 100AS buffer_pool_hit_rate;
-- 2. 临时表使用情况
SELECT
(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
AS tmp_disk_table_pct;
-- 3. 连接使用情况
SELECT
@@max_connections AS max_conn,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Max_used_connections') AS max_used,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Threads_connected') AS current_conn;
-- 4. 慢查询数量
SELECT VARIABLE_VALUE AS slow_queries
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Slow_queries';
-- 5. 排序和连接相关
SELECT
gs1.VARIABLE_VALUE AS sort_merge_passes,
gs2.VARIABLE_VALUE AS select_full_join
FROM performance_schema.global_status gs1,
performance_schema.global_status gs2
WHERE gs1.VARIABLE_NAME = 'Sort_merge_passes'
AND gs2.VARIABLE_NAME = 'Select_full_join';
2.2 核心配置
2.2.1 InnoDB核心参数（参数1-8）
参数1: innodb_buffer_pool_size
这是MySQL最重要的参数，没有之一。InnoDB缓冲池用于缓存数据页和索引页，缓冲池越大，能缓存的数据越多，磁盘IO越少，性能越好。
# 计算公式：物理内存 * 0.7 ~ 0.8（专用服务器）
# 示例：64GB内存服务器
innodb_buffer_pool_size = 48G
取值依据
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
验证效果
-- 查看缓冲池命中率（应 > 99%）
SHOWGLOBALSTATUSLIKE'Innodb_buffer_pool_read%';
-- 计算命中率
SELECT
ROUND((1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100, 2) AS hit_rate
FROM (
SELECT
SUM(CASEWHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads'THENCAST(VARIABLE_VALUE ASUNSIGNED) END) AS Innodb_buffer_pool_reads,
SUM(CASEWHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'THENCAST(VARIABLE_VALUE ASUNSIGNED) END) AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) AS stats;
参数2: innodb_buffer_pool_instances
将缓冲池分成多个实例，减少多线程并发访问时的锁竞争。
# 当buffer_pool_size > 1GB时设置
# 建议：每个实例1-2GB，最大不超过64
innodb_buffer_pool_instances = 8
# MySQL 8.0默认已经自动设置合理值
# 当buffer_pool_size < 1GB时，自动设为1
参数3: innodb_log_file_size
InnoDB重做日志文件大小。更大的日志文件可以减少checkpoint频率，提高写入性能，但会增加崩溃恢复时间。
# MySQL 8.0.30+ 使用 innodb_redo_log_capacity 替代
# innodb_redo_log_capacity = innodb_log_file_size * innodb_log_files_in_group * 2
# MySQL 8.0.30之前的配置
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# MySQL 8.0.30+的配置
innodb_redo_log_capacity = 8G # 相当于之前的4个2G日志文件
取值依据
-- 计算1小时内产生的日志量，以此为基准设置
SHOW ENGINE INNODB STATUS\G
-- 查看 Log sequence number 和 Log flushed up to
-- 一小时前后的差值就是每小时日志量
-- 建议设置为能容纳1小时日志量的2-4倍
-- 典型值范围：1GB - 8GB
参数4: innodb_log_buffer_size
重做日志缓冲区大小。事务在提交前先写入日志缓冲区，然后刷盘。
# 默认16MB，大事务场景可适当增大
innodb_log_buffer_size = 64M
什么时候需要增大
-- 查看日志缓冲区等待次数
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
-- 如果Innodb_log_waits > 0，说明日志缓冲区不够
-- 需要增大innodb_log_buffer_size
参数5: innodb_flush_log_at_trx_commit
这是性能与数据安全的平衡参数，控制redo log的刷盘策略。
# 取值说明：
# 0: 每秒刷盘，事务提交时不刷盘（最快，可能丢1秒数据）
# 1: 每次提交都刷盘（最安全，性能最差）
# 2: 每次提交写OS缓存，每秒刷盘（折中方案）
# 生产环境推荐
innodb_flush_log_at_trx_commit = 1 # 金融等对数据安全要求极高的场景
# 或
innodb_flush_log_at_trx_commit = 2 # 一般业务场景，OS崩溃时可能丢1秒数据
性能对比
|
|
|
|
|
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
参数6: innodb_flush_method
控制InnoDB数据文件和日志文件的刷盘方法。
# Linux环境推荐O_DIRECT
# 避免双重缓存（InnoDB缓冲池 + OS Page Cache）
innodb_flush_method = O_DIRECT
# MySQL 8.0默认值
# 如果使用SSD，O_DIRECT是最佳选择
取值说明
|
|
|
|
|
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
参数7: innodb_io_capacity
告诉InnoDB后台任务可用的IOPS能力。
# 根据磁盘类型设置
# HDD: 200-800
# SSD: 5000-20000
# NVMe: 20000-50000
innodb_io_capacity = 10000 # 普通SSD
innodb_io_capacity_max = 20000 # 最大值
如何确定正确的值
# 1. 使用fio测试磁盘IOPS
fio --name=randrw --ioengine=libaio --direct=1 --bs=16k \
--iodepth=64 --rw=randrw --rwmixread=70 --size=1G \
--runtime=60 --time_based --filename=/var/lib/mysql/fio_test
# 2. 设置io_capacity为测试结果的50-75%
# 3. 设置io_capacity_max为测试结果的100%
参数8: innodb_read_io_threads / innodb_write_io_threads
控制InnoDB后台IO线程数量。
# 默认值都是4
# SSD环境可以适当增加
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 计算依据：
# 读线程：处理预读和从磁盘读取数据
# 写线程：处理脏页刷盘
# 一般设置为CPU核心数的一半
2.2.2 连接与线程参数（参数9-12）
参数9: max_connections
允许的最大并发连接数。
# 不要设置过大！每个连接都会消耗内存
# 计算公式：预期并发用户数 * 1.5
# 根据应用规模设置
max_connections = 500 # 中小型应用
# max_connections = 2000 # 大型应用
设置过大的危害
-- 每个连接的内存消耗（近似）
-- 基础消耗 + sort_buffer + join_buffer + read_buffer + ...
-- 约 2-10MB per connection
-- 假设max_connections = 10000，sort_buffer = 4MB
-- 最坏情况内存消耗：10000 * 4MB = 40GB
-- 可能导致OOM
合理设置
-- 查看历史最大连接数
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections_time';
-- 建议设置为历史最大值的1.5-2倍
-- 但不要超过实际需要
参数10: thread_cache_size
线程缓存大小，用于复用已创建的线程。
# 计算公式：max_connections / 10，最大不超过100
thread_cache_size = 50
验证效果
-- 查看线程创建情况
SHOW GLOBAL STATUS LIKE 'Threads_%';
-- 线程缓存命中率计算
-- 命中率 = 1 - (Threads_created / Connections)
-- 应该 > 99%
参数11: wait_timeout / interactive_timeout
连接空闲超时时间。
# 非交互连接的超时时间（应用程序连接）
wait_timeout = 600 # 10分钟
# 交互连接的超时时间（mysql客户端）
interactive_timeout = 3600 # 1小时
设置依据
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
参数12: table_open_cache
表文件描述符缓存。
# 计算公式：max_connections * 每个连接平均使用表数
# 一般设置为：max_connections * 2 或更多
table_open_cache = 4000
table_open_cache_instances = 16 # 分成多个实例减少锁竞争
验证效果
-- 查看表缓存状态
SHOW GLOBAL STATUS LIKE 'Open%tables%';
SHOW GLOBAL STATUS LIKE 'Table_open_cache%';
-- Opened_tables 增长过快说明 table_open_cache 太小
2.2.3 内存相关参数（参数13-16）
参数13: sort_buffer_size
排序缓冲区大小，用于ORDER BY、GROUP BY操作。
# 这是per-session的参数，不要设置太大
# 每个需要排序的查询都会分配一个
sort_buffer_size = 2M # 推荐值
# 最大不要超过8M，否则可能触发swap
常见误区
-- 错误：设置太大
-- sort_buffer_size = 256M # 危险！
-- 假设同时有100个排序查询
-- 内存消耗：100 * 256M = 25.6GB
-- 可能导致OOM或大量swap
参数14: join_buffer_size
连接缓冲区大小，用于没有索引的JOIN操作。
# 同样是per-session参数
join_buffer_size = 2M
# 注意：如果JOIN有索引，这个参数基本不起作用
# 优化的方向应该是添加合适的索引
参数15: tmp_table_size / max_heap_table_size
内存临时表的最大大小。两个参数取较小值生效。
# 两个参数应该设置相同
tmp_table_size = 64M
max_heap_table_size = 64M
验证效果
-- 查看临时表使用情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- 磁盘临时表比例 = Created_tmp_disk_tables / Created_tmp_tables
-- 应该 < 25%，否则需要增大tmp_table_size
-- 或者优化查询，减少临时表使用
参数16: read_buffer_size / read_rnd_buffer_size
顺序读和随机读缓冲区。
# 顺序读缓冲区（全表扫描时使用）
read_buffer_size = 1M
# 随机读缓冲区（ORDER BY时使用）
read_rnd_buffer_size = 1M
# 这两个参数也是per-session的
# 默认值128K-256K对大多数场景足够
# 只有确认有大量全表扫描时才需要增大
2.2.4 日志与复制参数（参数17-20）
参数17: binlog_cache_size
二进制日志缓冲区大小。
# 用于缓存事务的binlog
# 事务过大时会使用临时文件
binlog_cache_size = 4M
# 查看是否需要增大
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
-- Binlog_cache_disk_use > 0 说明需要增大
参数18: sync_binlog
binlog刷盘策略。
# 0: 依赖OS刷盘（最快，不安全）
# 1: 每次提交都刷盘（最安全，较慢）
# N: 每N个事务刷盘一次
# 生产环境推荐
sync_binlog = 1
# 与 innodb_flush_log_at_trx_commit 配合
# 双1配置（sync_binlog=1, innodb_flush_log_at_trx_commit=1）
# 是最安全的配置，但性能较差
性能与安全的权衡
|
|
|
|
|
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
参数19: binlog_expire_logs_seconds
binlog自动清理时间（MySQL 8.0+替代expire_logs_days）。
# 保留7天的binlog
binlog_expire_logs_seconds = 604800 # 7 * 24 * 3600
# 考虑因素：
# 1. 从库可能的最大延迟时间
# 2. 数据恢复的时间点需求
# 3. 磁盘空间限制
参数20: replica_parallel_workers（MySQL 8.0.26+）
从库并行复制Worker数量。
# 启用并行复制
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16 # CPU核心数的1-2倍
replica_preserve_commit_order = ON
# 主库配合配置
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
2.3 启动和验证
2.3.1 配置文件示例
# /etc/mysql/mysql.conf.d/optimized.cnf
# MySQL 8.0 优化配置模板
# 适用于：64GB内存，NVMe SSD，16核CPU
[mysqld]
# ===================
# 基础配置
# ===================
server_id = 1
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
tmpdir = /tmp
# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
# ===================
# InnoDB核心参数
# ===================
# 参数1: 缓冲池 - 64GB * 0.75 ≈ 48GB
innodb_buffer_pool_size = 48G
# 参数2: 缓冲池实例 - 每实例约6GB
innodb_buffer_pool_instances = 8
# 参数3: 重做日志容量 (MySQL 8.0.30+)
innodb_redo_log_capacity = 8G
# 参数4: 日志缓冲区
innodb_log_buffer_size = 64M
# 参数5: 日志刷盘策略
innodb_flush_log_at_trx_commit = 2 # 生产可用1
# 参数6: 刷盘方法
innodb_flush_method = O_DIRECT
# 参数7: IO能力
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
# 参数8: IO线程
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# InnoDB其他优化
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
innodb_spin_wait_delay = 6
innodb_lock_wait_timeout = 50
innodb_print_all_deadlocks = ON
# ===================
# 连接与线程参数
# ===================
# 参数9: 最大连接数
max_connections = 500
# 参数10: 线程缓存
thread_cache_size = 50
# 参数11: 连接超时
wait_timeout = 600
interactive_timeout = 3600
# 参数12: 表缓存
table_open_cache = 4000
table_open_cache_instances = 16
table_definition_cache = 2000
# ===================
# 内存相关参数
# ===================
# 参数13: 排序缓冲区
sort_buffer_size = 2M
# 参数14: 连接缓冲区
join_buffer_size = 2M
# 参数15: 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M
# 参数16: 读缓冲区
read_buffer_size = 1M
read_rnd_buffer_size = 1M
# ===================
# 日志与复制参数
# ===================
# 参数17: binlog缓冲区
binlog_cache_size = 4M
# 参数18: binlog刷盘
sync_binlog = 1
# 参数19: binlog保留
binlog_expire_logs_seconds = 604800
# binlog配置
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
# 参数20: 并行复制（从库配置）
# replica_parallel_type = LOGICAL_CLOCK
# replica_parallel_workers = 16
# replica_preserve_commit_order = ON
# ===================
# 日志配置
# ===================
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
# ===================
# 性能Schema
# ===================
performance_schema = ON
performance_schema_instrument = '%=ON'
2.3.2 配置验证脚本
#!/bin/bash
# verify_mysql_config.sh
# MySQL配置验证脚本
MYSQL_USER="root"
MYSQL_PASS="your_password"
echo"=== MySQL配置验证 ==="
# 1. 验证InnoDB缓冲池
echo -e "\n[1] InnoDB缓冲池配置"
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT
CONCAT(ROUND(@@innodb_buffer_pool_size/1024/1024/1024, 1), 'G') AS buffer_pool_size,
@@innodb_buffer_pool_instances AS instances,
CONCAT(ROUND(@@innodb_buffer_pool_size/@@innodb_buffer_pool_instances/1024/1024/1024, 2), 'G') AS per_instance
"
# 2. 验证缓冲池命中率
echo -e "\n[2] 缓冲池命中率"
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT
CONCAT(
ROUND((1 - SUM(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads' THEN CAST(VARIABLE_VALUE AS DECIMAL) END) /
SUM(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' THEN CAST(VARIABLE_VALUE AS DECIMAL) END)) * 100, 2),
'%'
) AS hit_rate
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
"
# 3. 验证连接配置
echo -e "\n[3] 连接配置"
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT
@@max_connections AS max_conn,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Max_used_connections') AS max_used,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') AS current
"
# 4. 验证临时表情况
echo -e "\n[4] 临时表使用情况"
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables') AS tmp_tables,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS disk_tables,
CONCAT(
ROUND((SELECT CAST(VARIABLE_VALUE AS DECIMAL) FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') /
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables') * 100, 2),
'%'
) AS disk_pct
"
# 5. 验证IO配置
echo -e "\n[5] IO配置"
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT
@@innodb_io_capacity AS io_capacity,
@@innodb_io_capacity_max AS io_max,
@@innodb_read_io_threads AS read_threads,
@@innodb_write_io_threads AS write_threads
"
echo -e "\n验证完成!"
三、示例代码和配置
3.1 完整配置示例
3.1.1 不同规格服务器配置模板
小型服务器（8GB内存，4核CPU）
# /etc/mysql/mysql.conf.d/small.cnf
# 适用于：8GB内存，4核CPU，SSD
[mysqld]
# InnoDB
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
innodb_redo_log_capacity = 2G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
# 连接
max_connections = 200
thread_cache_size = 20
table_open_cache = 1000
# 内存
sort_buffer_size = 1M
join_buffer_size = 1M
tmp_table_size = 32M
max_heap_table_size = 32M
# 日志
binlog_cache_size = 2M
sync_binlog = 1
中型服务器（32GB内存，8核CPU）
# /etc/mysql/mysql.conf.d/medium.cnf
# 适用于：32GB内存，8核CPU，SSD
[mysqld]
# InnoDB
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_redo_log_capacity = 4G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 连接
max_connections = 500
thread_cache_size = 50
table_open_cache = 2000
# 内存
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
# 日志
binlog_cache_size = 4M
sync_binlog = 1
大型服务器（128GB内存，32核CPU）
# /etc/mysql/mysql.conf.d/large.cnf
# 适用于：128GB内存，32核CPU，NVMe SSD
[mysqld]
# InnoDB
innodb_buffer_pool_size = 96G
innodb_buffer_pool_instances = 16
innodb_redo_log_capacity = 16G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1 # 大型系统通常对数据安全要求高
innodb_flush_method = O_DIRECT
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
# 连接
max_connections = 2000
thread_cache_size = 100
table_open_cache = 8000
table_open_cache_instances = 32
# 内存
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 128M
max_heap_table_size = 128M
# 日志
binlog_cache_size = 8M
sync_binlog = 1
# 其他优化
innodb_page_cleaners = 8
innodb_purge_threads = 8
innodb_lru_scan_depth = 2048
3.1.2 动态参数调整脚本
#!/usr/bin/env python3
# dynamic_tuning.py
# MySQL动态参数调优脚本
import pymysql
from dataclasses import dataclass
from typing import Dict, List, Tuple
import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
@dataclass
class TuningRecommendation:
"""调优建议"""
parameter: str
current_value: str
recommended_value: str
reason: str
is_dynamic: bool
class MySQLTuningAdvisor:
"""MySQL调优顾问"""
def __init__(self, host: str, user: str, password: str, port: int = 3306):
self.connection = pymysql.connect(
host=host,
user=user,
password=password,
port=port,
cursorclass=pymysql.cursors.DictCursor
)
def analyze(self) -> List[TuningRecommendation]:
"""分析并生成调优建议"""
recommendations = []
# 分析各项指标
recommendations.extend(self._analyze_buffer_pool())
recommendations.extend(self._analyze_connections())
recommendations.extend(self._analyze_temp_tables())
recommendations.extend(self._analyze_threads())
recommendations.extend(self._analyze_io())
return recommendations
def _get_variable(self, name: str) -> str:
"""获取变量值"""
with self.connection.cursor() as cursor:
cursor.execute(f"SHOW VARIABLES LIKE '{name}'")
result = cursor.fetchone()
return result['Value'] if result elseNone
def _get_status(self, name: str) -> int:
"""获取状态值"""
with self.connection.cursor() as cursor:
cursor.execute(f"SHOW GLOBAL STATUS LIKE '{name}'")
result = cursor.fetchone()
return int(result['Value']) if result else0
def _analyze_buffer_pool(self) -> List[TuningRecommendation]:
"""分析缓冲池"""
recommendations = []
reads = self._get_status('Innodb_buffer_pool_reads')
read_requests = self._get_status('Innodb_buffer_pool_read_requests')
if read_requests > 0:
hit_rate = (1 - reads / read_requests) * 100
if hit_rate < 99:
current_size = int(self._get_variable('innodb_buffer_pool_size'))
recommended_size = int(current_size * 1.5)
recommendations.append(TuningRecommendation(
parameter='innodb_buffer_pool_size',
current_value=self._format_bytes(current_size),
recommended_value=self._format_bytes(recommended_size),
reason=f'缓冲池命中率 {hit_rate:.2f}% 低于99%',
is_dynamic=True# MySQL 8.0支持动态调整
))
return recommendations
def _analyze_connections(self) -> List[TuningRecommendation]:
"""分析连接数"""
recommendations = []
max_conn = int(self._get_variable('max_connections'))
max_used = self._get_status('Max_used_connections')
usage_rate = max_used / max_conn * 100
if usage_rate > 80:
recommendations.append(TuningRecommendation(
parameter='max_connections',
current_value=str(max_conn),
recommended_value=str(int(max_used * 1.5)),
reason=f'连接使用率 {usage_rate:.1f}% 超过80%',
is_dynamic=True
))
elif usage_rate < 20and max_conn > 200:
recommendations.append(TuningRecommendation(
parameter='max_connections',
current_value=str(max_conn),
recommended_value=str(max(200, int(max_used * 2))),
reason=f'连接使用率仅 {usage_rate:.1f}%，max_connections设置过大',
is_dynamic=True
))
return recommendations
def _analyze_temp_tables(self) -> List[TuningRecommendation]:
"""分析临时表"""
recommendations = []
tmp_tables = self._get_status('Created_tmp_tables')
tmp_disk_tables = self._get_status('Created_tmp_disk_tables')
if tmp_tables > 0:
disk_ratio = tmp_disk_tables / tmp_tables * 100
if disk_ratio > 25:
current_size = int(self._get_variable('tmp_table_size'))
recommended_size = min(current_size * 2, 256 * 1024 * 1024) # 最大256MB
recommendations.append(TuningRecommendation(
parameter='tmp_table_size',
current_value=self._format_bytes(current_size),
recommended_value=self._format_bytes(recommended_size),
reason=f'磁盘临时表比例 {disk_ratio:.1f}% 超过25%',
is_dynamic=True
))
return recommendations
def _analyze_threads(self) -> List[TuningRecommendation]:
"""分析线程缓存"""
recommendations = []
threads_created = self._get_status('Threads_created')
connections = self._get_status('Connections')
if connections > 0:
thread_cache_hit_rate = (1 - threads_created / connections) * 100
if thread_cache_hit_rate < 99:
current_cache = int(self._get_variable('thread_cache_size'))
recommendations.append(TuningRecommendation(
parameter='thread_cache_size',
current_value=str(current_cache),
recommended_value=str(min(current_cache + 20, 100)),
reason=f'线程缓存命中率 {thread_cache_hit_rate:.1f}% 低于99%',
is_dynamic=True
))
return recommendations
def _analyze_io(self) -> List[TuningRecommendation]:
"""分析IO配置"""
recommendations = []
# 检查日志等待
log_waits = self._get_status('Innodb_log_waits')
if log_waits > 0:
current_size = int(self._get_variable('innodb_log_buffer_size'))
recommendations.append(TuningRecommendation(
parameter='innodb_log_buffer_size',
current_value=self._format_bytes(current_size),
recommended_value=self._format_bytes(current_size * 2),
reason=f'日志缓冲区等待次数 {log_waits}',
is_dynamic=False# 需要重启
))
return recommendations
@staticmethod
def _format_bytes(size: int) -> str:
"""格式化字节数"""
for unit in ['B', 'K', 'M', 'G']:
if size < 1024:
returnf"{size}{unit}"
size //= 1024
returnf"{size}T"
def apply_recommendations(
self,
recommendations: List[TuningRecommendation],
dynamic_only: bool = True
) -> Dict[str, bool]:
"""应用调优建议"""
results = {}
for rec in recommendations:
if dynamic_only andnot rec.is_dynamic:
logger.info(f"跳过非动态参数: {rec.parameter}")
results[rec.parameter] = False
continue
try:
with self.connection.cursor() as cursor:
cursor.execute(
f"SET GLOBAL {rec.parameter} = {rec.recommended_value}"
)
self.connection.commit()
logger.info(
f"成功调整 {rec.parameter}: "
f"{rec.current_value} -> {rec.recommended_value}"
)
results[rec.parameter] = True
except Exception as e:
logger.error(f"调整 {rec.parameter} 失败: {e}")
results[rec.parameter] = False
return results
def generate_report(self, recommendations: List[TuningRecommendation]) -> str:
"""生成调优报告"""
report = []
report.append("=" * 60)
report.append("MySQL参数调优建议报告")
report.append("=" * 60)
ifnot recommendations:
report.append("当前配置良好，无需调整")
else:
report.append(f"发现 {len(recommendations)} 项调优建议:\n")
dynamic_params = [r for r in recommendations if r.is_dynamic]
static_params = [r for r in recommendations ifnot r.is_dynamic]
if dynamic_params:
report.append("【可动态调整的参数】")
for i, rec in enumerate(dynamic_params, 1):
report.append(f"\n{i}. {rec.parameter}")
report.append(f" 当前值: {rec.current_value}")
report.append(f" 建议值: {rec.recommended_value}")
report.append(f" 原因: {rec.reason}")
if static_params:
report.append("\n【需要重启生效的参数】")
for i, rec in enumerate(static_params, 1):
report.append(f"\n{i}. {rec.parameter}")
report.append(f" 当前值: {rec.current_value}")
report.append(f" 建议值: {rec.recommended_value}")
report.append(f" 原因: {rec.reason}")
return"\n".join(report)
if __name__ == "__main__":
advisor = MySQLTuningAdvisor(
host="127.0.0.1",
user="root",
password="your_password"
)
recommendations = advisor.analyze()
report = advisor.generate_report(recommendations)
print(report)
# 可选：应用动态参数调整
# results = advisor.apply_recommendations(recommendations, dynamic_only=True)
3.2 实际应用案例
3.2.1 案例一：电商大促前的参数调优
场景描述
某电商平台即将迎来双11大促，预计流量是平时的20倍。当前配置是根据日常负载设置的，需要针对大促场景进行调优。
当前配置
# 日常配置
innodb_buffer_pool_size = 24G
max_connections = 500
innodb_io_capacity = 2000
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
优化方案
# 大促期间配置
# 1. 增大缓冲池应对更多并发读取
innodb_buffer_pool_size = 48G # 从24G增加到48G
# 2. 增大连接数
max_connections = 2000 # 从500增加到2000
# 3. 提升IO能力
innodb_io_capacity = 10000 # 从2000增加到10000
innodb_io_capacity_max = 20000
# 4. 牺牲少量安全性换取性能
sync_binlog = 100 # 从1改为100
innodb_flush_log_at_trx_commit = 2 # 从1改为2
# 5. 增大临时表大小应对复杂查询
tmp_table_size = 128M
max_heap_table_size = 128M
# 6. 增大排序缓冲区
sort_buffer_size = 4M
join_buffer_size = 4M
动态调整脚本
#!/bin/bash
# promotion_tuning.sh
# 大促参数切换脚本
MYSQL_CMD="mysql -u root -p'password'"
echo"开始应用大促配置..."
# 动态调整参数
$MYSQL_CMD << EOF
-- 增大连接数
SET GLOBAL max_connections = 2000;
-- 调整IO参数
SET GLOBAL innodb_io_capacity = 10000;
SET GLOBAL innodb_io_capacity_max = 20000;
-- 放宽刷盘策略
SET GLOBAL sync_binlog = 100;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- 增大会话缓冲区
SET GLOBAL sort_buffer_size = 4*1024*1024;
SET GLOBAL join_buffer_size = 4*1024*1024;
SET GLOBAL tmp_table_size = 128*1024*1024;
SET GLOBAL max_heap_table_size = 128*1024*1024;
-- 验证
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
EOF
echo"大促配置应用完成!"
echo"注意：innodb_buffer_pool_size需要在线扩容或重启"
大促后恢复
#!/bin/bash
# restore_normal.sh
# 恢复日常配置
MYSQL_CMD="mysql -u root -p'password'"
echo"恢复日常配置..."
$MYSQL_CMD << EOF
SET GLOBAL max_connections = 500;
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL innodb_io_capacity_max = 4000;
SET GLOBAL sync_binlog = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sort_buffer_size = 2*1024*1024;
SET GLOBAL join_buffer_size = 2*1024*1024;
SET GLOBAL tmp_table_size = 64*1024*1024;
SET GLOBAL max_heap_table_size = 64*1024*1024;
EOF
echo"日常配置恢复完成!"
3.2.2 案例二：慢查询导致的性能问题排查
问题现象
数据库CPU持续在80%以上，慢查询数量增加，业务响应变慢。
排查步骤
-- 1. 查看当前运行的查询
SELECT
id,
user,
host,
db,
command,
time,
state,
LEFT(info, 100) ASquery
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDERBYtimeDESC;
-- 2. 分析慢查询日志
-- 使用pt-query-digest分析
-- 3. 查看资源消耗最高的语句
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
SUM_TIMER_WAIT/1000000000000AS total_sec,
AVG_TIMER_WAIT/1000000000AS avg_ms,
SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDERBY SUM_TIMER_WAIT DESC
LIMIT10;
发现问题
-- 发现大量全表扫描查询
SELECT * FROM orders WHERE status = 'pending'
-- 该表有500万行，status字段没有索引
优化方案
-- 1. 添加索引
CREATE INDEX idx_status ON orders(status);
-- 2. 临时增大sort_buffer_size应对积压
SET GLOBAL sort_buffer_size = 8*1024*1024;
-- 3. 增大read_buffer_size缓解全表扫描
SET GLOBAL read_buffer_size = 2*1024*1024;
参数调优建议
# 针对读多写少场景
read_buffer_size = 2M
read_rnd_buffer_size = 2M
# 针对复杂查询场景
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 128M
max_heap_table_size = 128M
3.2.3 案例三：从库延迟优化
问题现象
从库复制延迟持续在几百秒，影响读写分离架构。
排查命令
-- 查看从库状态
SHOW REPLICA STATUS\G
-- 关键指标
-- Seconds_Behind_Source: 延迟秒数
-- Relay_Log_Space: 中继日志大小
-- Replica_SQL_Running_State: SQL线程状态
发现问题
从库使用单线程复制，无法充分利用多核CPU。
优化方案
-- 停止复制
STOP REPLICA;
-- 配置并行复制
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 16;
SET GLOBAL replica_preserve_commit_order = ON;
-- 启动复制
START REPLICA;
从库专用配置
# /etc/mysql/mysql.conf.d/replica.cnf
# 从库专用优化配置
[mysqld]
# 并行复制
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON
# 从库可以放宽刷盘策略
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
# 增大IO能力加快回放
innodb_io_capacity = 15000
innodb_io_capacity_max = 30000
# 从库不需要二进制日志（除非级联复制）
# skip_log_bin
# 只读
read_only = ON
super_read_only = ON
四、最佳实践和注意事项
4.1 最佳实践
4.1.1 参数调优黄金法则
法则1：先测量后调优
# 使用sysbench进行基准测试
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=test \
--mysql-password=test \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=32 \
--time=300 \
--report-interval=10 \
prepare
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=test \
--mysql-password=test \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=32 \
--time=300 \
--report-interval=10 \
run
法则2：单参数调整
每次只调整一个参数，观察效果后再调整下一个。
法则3：记录所有变更
-- 创建参数变更记录表
CREATETABLE param_change_log (
idINT PRIMARY KEY AUTO_INCREMENT,
changed_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
parameter VARCHAR(100),
old_value VARCHAR(100),
new_value VARCHAR(100),
changed_by VARCHAR(50),
reason VARCHAR(500)
);
-- 记录变更
INSERTINTO param_change_log (parameter, old_value, new_value, changed_by, reason)
VALUES ('innodb_buffer_pool_size', '24G', '48G', 'dba_john', '大促扩容');
4.1.2 内存参数配置原则
总可用内存 = 物理内存 * 90%（留10%给OS）
内存分配 =
innodb_buffer_pool_size (60-70%)
+ max_connections * per_connection_memory (10-20%)
+ 其他缓冲区 (5-10%)
+ OS和其他进程 (10%)
per_connection_memory计算
-- 每个连接的最大内存消耗
SELECT
(@@sort_buffer_size + @@join_buffer_size + @@read_buffer_size +
@@read_rnd_buffer_size + @@binlog_cache_size) / 1024 / 1024 AS per_conn_mb;
-- 最坏情况总消耗
SELECT
@@max_connections *
(@@sort_buffer_size + @@join_buffer_size + @@read_buffer_size +
@@read_rnd_buffer_size + @@binlog_cache_size) / 1024 / 1024 / 1024 AS max_session_memory_gb;
4.1.3 IO参数配置原则
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4.2 注意事项
4.2.1 常见错误
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4.2.2 需要重启的参数
以下参数修改后需要重启MySQL：
# 需要重启的参数
innodb_buffer_pool_size # MySQL 8.0可在线调整
innodb_log_file_size # 8.0.30前需要重启
innodb_page_size # 初始化后无法修改
innodb_data_file_path # 无法在线修改
lower_case_table_names # 无法在线修改
4.2.3 动态参数调整注意事项
-- 在线调整buffer_pool_size（MySQL 8.0）
-- 可能需要几分钟完成
SET GLOBAL innodb_buffer_pool_size = 48*1024*1024*1024;
-- 查看调整进度
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
五、故障排查和监控
5.1 参数相关问题诊断
-- 内存不足诊断
SELECT
CONCAT(ROUND(@@innodb_buffer_pool_size/1024/1024/1024, 1), 'G') ASbuffer_pool,
CONCAT(@@max_connections, ' * ',
ROUND((@@sort_buffer_size + @@join_buffer_size)/1024/1024, 1), 'M = ',
ROUND(@@max_connections * (@@sort_buffer_size + @@join_buffer_size)/1024/1024/1024, 1), 'G'
) AS max_session_memory;
-- 连接数问题诊断
SELECT
@@max_connections AS max_conn,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Max_used_connections') AS max_used,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Threads_connected') AScurrent,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Aborted_connects') AS aborted;
5.2 关键监控指标
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5.3 监控脚本
#!/bin/bash
# mysql_parameter_monitor.sh
# MySQL参数效果监控脚本
MYSQL_CMD="mysql -u monitor -p'password' -N"
whiletrue; do
timestamp=$(date '+%Y-%m-%d %H:%M:%S')
# 收集指标
result=$($MYSQL_CMD << EOF
SELECT
ROUND((1 - bp_reads.val / bp_requests.val) * 100, 2) AS buffer_hit_rate,
ROUND(threads.val / max_conn.val * 100, 1) AS conn_usage,
ROUND(disk_tmp.val / all_tmp.val * 100, 1) AS disk_tmp_pct
FROM
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') bp_reads,
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') bp_requests,
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') threads,
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections') max_conn,
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') disk_tmp,
(SELECT CAST(VARIABLE_VALUE AS DECIMAL) AS val FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables') all_tmp;
EOF
)
echo"$timestamp | $result"
sleep 60
done
六、总结
6.1 技术要点回顾
本文详细介绍了MySQL 20个核心参数的调优方法，关键要点如下：
-
innodb_buffer_pool_size是最重要的参数 -
设置为物理内存的70-80% -
命中率应保持在99%以上
-
-
连接相关参数要平衡 -
max_connections不是越大越好 -
每个连接都会消耗内存
-
-
IO参数要匹配硬件能力 -
innodb_io_capacity根据磁盘类型设置 -
SSD和HDD差异巨大
-
-
刷盘策略要权衡安全与性能 -
双1配置最安全但性能较差 -
根据业务重要性选择合适配置
-
-
并行复制大幅提升从库性能 -
MySQL 8.0的LOGICAL_CLOCK配合WRITESET -
Worker数量设置为CPU核心数的1-2倍
-
6.2 20个参数速查表
|
|
|
|
|
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6.3 参考资料
-
MySQL 8.0 Reference Manual – Server System Variables https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html -
High Performance MySQL, 4th Edition – O’Reilly -
MySQL Performance Blog – Percona https://www.percona.com/blog/ -
MySQL Server Team Blog https://dev.mysql.com/blog-archive/
附录
A. 命令速查表
-- 查看所有变量
SHOWVARIABLES;
SHOWGLOBALVARIABLESLIKE'%innodb%';
-- 查看所有状态
SHOWGLOBALSTATUS;
SHOWGLOBALSTATUSLIKE'%Innodb%';
-- 动态修改参数
SETGLOBAL variable_name = value;
SETSESSION variable_name = value;
-- 查看参数是否可动态修改
SELECT * FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';
B. 配置参数快速对照
|
|
|
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C. 术语表
|
|
|
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
转自：马哥Linux运维
版权申明：内容来源网络，版权归原创者所有，如有侵权请联系删除
想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
