# IO参数优化
1. innodb_io_capacity：设置InnoDB的IO容量，建议根据磁盘性能设置，SSD建议设置为2000-4000
2. innodb_io_capacity_max：设置InnoDB的最大IO容量，建议为innodb_io_capacity的2倍
3. innodb_flush_log_at_trx_commit：设置日志刷新策略，1为最安全，0为最高性能，2为折中
4. innodb_log_file_size：设置重做日志文件大小，建议为256MB-1GB
5. innodb_log_files_in_group：设置重做日志文件数量，建议为2
6. innodb_doublewrite：开启双写缓冲，提高数据可靠性
7. innodb_flush_method：设置InnoDB的刷新方法，建议使用O_DIRECT
8. innodb_file_per_table：开启独立表空间，便于管理
9. innodb_autoextend_increment：设置表空间自动扩展的大小，建议为64MB
10. innodb_page_size：设置InnoDB页大小，默认为16KB，SSD可以考虑使用4KB
11. innodb_read_io_threads：设置InnoDB读取IO线程数，建议为CPU核心数
12. innodb_write_io_threads：设置InnoDB写入IO线程数，建议为CPU核心数
13. innodb_purge_threads：设置InnoDB清理线程数，建议为4
14. innodb_page_cleaners：设置InnoDB页面清理线程数，建议为CPU核心数
15. sync_binlog：设置二进制日志同步策略，1为最安全，0为最高性能

# IO参数调优建议
1. 对于SSD存储：
– innodb_io_capacity = 4000
– innodb_io_capacity_max = 8000
– innodb_flush_method = O_DIRECT
– innodb_page_size = 4KB（可选）

2. 对于HDD存储：
– innodb_io_capacity = 200
– innodb_io_capacity_max = 400
– innodb_flush_method = O_DSYNC
– innodb_page_size = 16KB

3. 对于混合存储（数据在HDD，日志在SSD）：
– 数据文件使用HDD的参数
– 日志文件使用SSD的参数

4. 对于高可靠性要求：
– innodb_flush_log_at_trx_commit = 1
– sync_binlog = 1

5. 对于高性能要求：
– innodb_flush_log_at_trx_commit = 0
– sync_binlog = 0

6. 对于折中方案：
– innodb_flush_log_at_trx_commit = 2
– sync_binlog = 100

# 并发参数优化
1. max_connections：设置最大连接数，建议根据服务器性能和业务需求设置，一般为1000-2000
2. max_connect_errors：设置最大连接错误数，建议为10000
3. wait_timeout：设置非活动连接超时时间，建议为8小时（28800秒）
4. interactive_timeout：设置交互式连接超时时间，建议为8小时（28800秒）
5. innodb_thread_concurrency：设置InnoDB并发线程数，建议为CPU核心数的2-4倍
6. innodb_concurrency_tickets：设置InnoDB并发 tickets 数，建议为5000
7. innodb_lock_wait_timeout：设置InnoDB锁等待超时时间，建议为30-60秒
8. thread_cache_size：设置线程缓存大小，建议为max_connections的10%
9. table_open_cache：设置表缓存大小，建议为2000-4000
10. table_definition_cache：设置表定义缓存大小，建议为1000-2000
11. max_heap_table_size：设置内存表最大大小，建议为64MB-256MB
12. tmp_table_size：设置临时表最大大小，建议与max_heap_table_size相同
13. sort_buffer_size：设置排序缓冲区大小，建议为256KB-2MB
14. join_buffer_size：设置连接缓冲区大小，建议为256KB-2MB
15. read_buffer_size：设置顺序读取缓冲区大小，建议为128KB-1MB
16. read_rnd_buffer_size：设置随机读取缓冲区大小，建议为128KB-1MB

# 并发参数调优建议
1. 对于高并发场景：
– max_connections = 2000
– thread_cache_size = 200
– table_open_cache = 4000
– innodb_thread_concurrency = 16（8核CPU）

2. 对于低并发场景：
– max_connections = 500
– thread_cache_size = 50
– table_open_cache = 2000
– innodb_thread_concurrency = 8（4核CPU）

3. 对于内存充足的服务器：
– sort_buffer_size = 2MB
– join_buffer_size = 2MB
– read_buffer_size = 1MB
– read_rnd_buffer_size = 1MB

4. 对于内存有限的服务器：
– sort_buffer_size = 256KB
– join_buffer_size = 256KB
– read_buffer_size = 128KB
– read_rnd_buffer_size = 128KB

5. 对于长时间运行的连接：
– wait_timeout = 86400（24小时）
– interactive_timeout = 86400（24小时）

6. 对于频繁短连接：
– wait_timeout = 3600（1小时）
– interactive_timeout = 3600（1小时）
– thread_cache_size = max_connections * 0.1

# 其他参数优化
1. 字符集参数：
– character_set_server：设置服务器默认字符集，建议为utf8mb4
– collation_server：设置服务器默认校对规则，建议为utf8mb4_0900_ai_ci
– init_connect：设置连接初始化语句，如SET NAMES utf8mb4

2. 日志参数：
– log_bin：开启二进制日志，用于复制和恢复
– binlog_format：设置二进制日志格式，建议为ROW
– binlog_row_image：设置二进制日志行镜像，建议为FULL
– slow_query_log：开启慢查询日志，用于性能分析
– long_query_time：设置慢查询阈值，建议为1秒
– log_queries_not_using_indexes：记录未使用索引的查询

3. 安全参数：
– skip_networking：禁用网络连接，只允许本地连接
– bind_address：绑定IP地址，限制连接来源
– ssl_cert：设置SSL证书文件
– ssl_key：设置SSL密钥文件
– require_secure_transport：要求安全传输

4. 存储引擎参数：
– default_storage_engine：设置默认存储引擎，建议为InnoDB
– innodb_file_per_table：开启独立表空间
– innodb_autoinc_lock_mode：设置自增锁模式，建议为2（交错）
– innodb_stats_on_metadata：关闭元数据统计，提高性能

5. 复制参数：
– server_id：设置服务器ID，用于复制
– log_slave_updates：开启从服务器更新日志
– relay_log_recovery：开启中继日志恢复
– slave_parallel_workers：设置从服务器并行工作线程数，建议为CPU核心数

6. 性能参数：
– performance_schema：开启性能模式，用于监控
– innodb_adaptive_hash_index：开启自适应哈希索引，提高查询性能
– innodb_flush_neighbors：设置刷新邻居页，SSD建议关闭
– innodb_lru_scan_depth：设置LRU扫描深度，建议为1024

7. 其他优化参数：
– max_allowed_packet：设置最大数据包大小，建议为64MB
– net_buffer_length：设置网络缓冲区大小，建议为16KB
– back_log：设置连接队列大小，建议为50-100
– max_connect_errors：设置最大连接错误数，建议为10000
– open_files_limit：设置打开文件数限制，建议为65535

# 其他参数调优建议
1. 对于生产环境：
– 开启二进制日志：log_bin = ON
– 开启慢查询日志：slow_query_log = ON
– 设置合理的字符集：character_set_server = utf8mb4
– 开启性能模式：performance_schema = ON

2. 对于开发环境：
– 关闭二进制日志：log_bin = OFF
– 开启慢查询日志：slow_query_log = ON
– 设置long_query_time = 0.1，捕获更多慢查询

3. 对于高安全性要求：
– skip_networking = ON（如果只需要本地连接）
– require_secure_transport = ON
– 启用SSL：ssl_cert和ssl_key设置

4. 对于高性能要求：
– innodb_adaptive_hash_index = ON
– innodb_flush_neighbors = 0（SSD）
– innodb_stats_on_metadata = OFF

# 参数调优前的准备
# 步骤1：分析服务器硬件配置
# 查看服务器CPU
nproc

# 查看服务器内存
free -m

# 查看服务器磁盘
lsblk

# 查看磁盘IO性能
iostat -x

# 步骤2：分析MySQL性能状况
# 查看MySQL版本
SELECT VERSION();

# 查看MySQL状态
SHOW GLOBAL STATUS;

# 查看MySQL变量
SHOW GLOBAL VARIABLES;

# 查看慢查询日志
SHOW VARIABLES LIKE ‘%slow%’;

# 查看连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 步骤3：分析业务负载
# 查看当前查询
SHOW PROCESSLIST;

# 分析慢查询
pt-query-digest /var/log/mysql/slow.log

# 步骤4：备份配置文件
# 备份my.cnf文件
cp /etc/my.cnf /etc/my.cnf.bak

# 步骤5：制定调优计划
# 确定调优目标
# 选择需要调整的参数
# 制定详细的调优方案

# 准备工作最佳实践
1. 充分了解服务器硬件配置：根据硬件配置制定调优方案
2. 全面分析MySQL性能状况：了解当前性能瓶颈
3. 详细分析业务负载：根据业务特点调整参数
4. 备份配置文件：防止调优失败导致系统故障
5. 制定详细的调优计划：确保调优过程有序进行
6. 准备回滚方案：在调优失败时能够快速恢复
7. 建立监控机制：实时监控调优效果
8. 制定测试计划：验证调优效果

# 参数调优步骤
# 步骤1：调整内存参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 设置InnoDB缓冲池大小
innodb_buffer_pool_size = 8G

# 设置缓冲池实例数
innodb_buffer_pool_instances = 8

# 设置排序缓冲区大小
sort_buffer_size = 256K

# 设置连接缓冲区大小
join_buffer_size = 256K

# 步骤2：调整IO参数
# 设置IO容量
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 设置重做日志大小
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# 设置日志刷新策略
innodb_flush_log_at_trx_commit = 1

# 设置双写缓冲
innodb_doublewrite = 1

# 步骤3：调整并发参数
# 设置最大连接数
max_connections = 1000

# 设置线程缓存大小
thread_cache_size = 100

# 设置表缓存大小
table_open_cache = 2000

# 设置InnoDB并发线程数
innodb_thread_concurrency = 16

# 步骤4：调整其他参数
# 设置字符集
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

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

# 开启二进制日志
log_bin = /var/lib/mysql/binlog
binlog_format = ROW

# 步骤5：重启MySQL服务
systemctl restart mysqld

# 步骤6：验证参数调整
# 查看调整后的参数
SHOW GLOBAL VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW GLOBAL VARIABLES LIKE ‘innodb_io_capacity’;
SHOW GLOBAL VARIABLES LIKE ‘max_connections’;

# 步骤7：监控性能变化
# 监控缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 监控连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 监控慢查询
SELECT * FROM mysql.slow_log;

# 调优步骤最佳实践
1. 从内存参数开始：内存是MySQL性能的关键，先调整内存参数
2. 再调整IO参数：IO是MySQL性能的瓶颈，合理配置IO参数
3. 然后调整并发参数：根据业务负载调整并发参数
4. 最后调整其他参数：根据需要调整其他参数
5. 逐步调整：每次只调整一个参数，观察效果后再调整其他参数
6. 重启验证：调整参数后重启MySQL服务，验证参数生效
7. 监控效果：实时监控性能变化，评估调优效果
8. 固化配置：将调优后的参数配置固化到配置文件中

# 参数调优验证
# 步骤1：性能测试
# 使用sysbench进行性能测试
sysbench –db-driver=mysql –mysql-host=localhost –mysql-user=root –mysql-password=root –mysql-db=test –table-size=1000000 –threads=16 –time=60 –report-interval=10 oltp_read_write run

# 步骤2：查询测试
# 测试复杂查询
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;

# 测试连接性能
mysqlslap –user=root –password=root –host=localhost –concurrency=100 –iterations=10 –create-schema=test –query=”SELECT * FROM users WHERE id = 1″

# 步骤3：监控系统资源
# 监控CPU使用情况
top

# 监控内存使用情况
free -m

# 监控磁盘IO
iostat -x

# 步骤4：监控MySQL状态
# 查看连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 查看缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 查看慢查询数量
SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

# 步骤5：分析调优效果
# 对比调优前后的性能指标
# 调优前：QPS = 1000, 响应时间 = 0.1秒
# 调优后：QPS = 2000, 响应时间 = 0.05秒

# 分析系统稳定性
# 检查错误日志
cat /var/log/mysql/error.log

# 检查死锁情况
SHOW ENGINE INNODB STATUS\G

# 步骤6：调整调优方案
# 根据验证结果调整调优方案
# 如性能未达到预期，继续调整参数

# 步骤7：固化调优结果
# 将调优后的参数配置固化到配置文件中
# 备份调优后的配置文件
cp /etc/my.cnf /etc/my.cnf.optimized

# 验证最佳实践
1. 进行全面的性能测试：使用sysbench等工具进行综合性能测试
2. 测试实际业务查询：测试实际业务中的复杂查询
3. 监控系统资源：确保系统资源使用合理
4. 监控MySQL状态：确保MySQL运行正常
5. 对比调优前后的性能：评估调优效果
6. 分析系统稳定性：确保调优后系统稳定运行
7. 调整调优方案：根据验证结果调整参数
8. 固化调优结果：将调优后的参数配置固化到配置文件中

# 参数调优最佳实践
1. 基于硬件配置：
– 内存：根据服务器内存大小设置innodb_buffer_pool_size，通常为内存的50-80%
– CPU：根据CPU核心数设置innodb_thread_concurrency、innodb_read_io_threads等参数
– 磁盘：根据磁盘类型（SSD/HDD）设置innodb_io_capacity、innodb_flush_method等参数

2. 基于业务需求：
– 读密集型：增加innodb_buffer_pool_size，优化索引
– 写密集型：调整innodb_io_capacity，优化日志参数
– 高并发：增加max_connections，优化线程参数

3. 逐步调整：
– 每次只调整一个参数，观察效果后再调整其他参数
– 从小值开始调整，逐渐增加到最佳值
– 记录每次调整的参数值和效果，便于回滚

4. 监控验证：
– 实时监控系统资源使用情况
– 监控MySQL状态和性能指标
– 定期分析慢查询日志
– 进行性能测试，验证调优效果

5. 备份配置：
– 在调整参数前，备份原有的配置文件
– 记录每次调整的参数值，便于回滚
– 定期备份调优后的配置文件

6. 遵循官方建议：
– 风哥教程参考MySQL官方文档的参数建议
– 参考MySQL性能调优最佳实践
– 关注MySQL新版本的参数变化

7. 考虑系统整体：
– 平衡内存、IO和并发参数
– 避免过度调优，保持系统稳定
– 考虑操作系统对MySQL的限制

8. 定期维护：
– 定期检查参数配置，适应业务需求的变化
– 定期优化表和索引
– 定期清理日志和临时文件

9. 培训和文档：
– 培训运维人员，提高参数调优技能
– 建立参数调优文档，记录调优过程和结果
– 分享调优经验，提高团队整体水平

10. 持续优化：
– 监控业务需求的变化，及时调整参数
– 关注MySQL新版本的性能改进
– 持续学习和实践，不断提高调优能力

# 生产环境参数调优建议
1. 内存参数：
– innodb_buffer_pool_size = 内存的50-80%
– innodb_buffer_pool_instances = CPU核心数
– sort_buffer_size = 256KB-2MB
– join_buffer_size = 256KB-2MB

2. IO参数：
– innodb_io_capacity = SSD: 2000-4000, HDD: 200
– innodb_log_file_size = 256MB-1GB
– innodb_flush_log_at_trx_commit = 1（安全）或2（折中）
– innodb_flush_method = O_DIRECT（SSD）或O_DSYNC（HDD）

3. 并发参数：
– max_connections = 1000-2000
– thread_cache_size = max_connections的10%
– table_open_cache = 2000-4000
– innodb_thread_concurrency = CPU核心数的2-4倍

4. 其他参数：
– character_set_server = utf8mb4
– slow_query_log = 1
– long_query_time = 1
– log_bin = 1
– binlog_format = ROW

# 内存参数调优案例
# 环境说明
# MySQL 8.0.29
# 服务器：8核16GB内存
# 数据库：test
# 表：users（100万行数据）

# 问题描述
# 发现查询响应时间长，缓冲池命中率低

# 解决方案
## 步骤1：分析当前内存参数
# 查看缓冲池大小
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

# 预期输出：
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_buffer_pool_size | 134217728 |
+————————-+———+

# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 预期输出：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_reads | 100000 |
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_write_requests | 500000 |
| Innodb_buffer_pool_pages_data | 8000 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_pages_free | 192 |
+—————————————+————-+

## 步骤2：计算缓冲池命中率
# 计算缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 预期输出：
+———————+
| buffer_pool_hit_rate |
+———————+
| 90.0000 |
+———————+

## 步骤3：调整内存参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改内存参数
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
sort_buffer_size = 256K
join_buffer_size = 256K

# 步骤4：重启MySQL服务
systemctl restart mysqld

## 步骤5：验证内存参数调优效果
# 查看缓冲池大小
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 计算缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 预期输出：
+———————+
| buffer_pool_hit_rate |
+———————+
| 99.5000 |
+———————+

## 步骤6：测试查询响应时间
# 测试查询
SELECT * FROM users WHERE id = 1;
# 优化前：0.1秒
# 优化后：0.01秒

# 处理效果
# 成功优化了内存参数
# 缓冲池命中率从90%提高到99.5%
# 查询响应时间从0.1秒减少到0.01秒
# 提高了系统性能

# IO参数调优案例
# 环境说明
# MySQL 8.0.29
# 服务器：8核16GB内存
# 存储：SSD
# 数据库：test
# 表：orders（500万行数据）

# 问题描述
# 发现写入操作响应时间长，IO等待高

# 解决方案
## 步骤1：分析当前IO参数
# 查看IO参数
SHOW VARIABLES LIKE ‘innodb_io_capacity’;
SHOW VARIABLES LIKE ‘innodb_log_file_size’;
SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’;

# 预期输出：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_io_capacity | 200 |
+————————+
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_log_file_size | 50331648 |
+————————+
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+

## 步骤2：分析IO性能
# 查看IO等待情况
SHOW GLOBAL STATUS LIKE ‘Innodb_os%’;

# 预期输出：
+—————————-+———+
| Variable_name | Value |
+—————————-+———+
| Innodb_os_log_written | 1000000 |
| Innodb_os_log_pending_writes | 100 |
| Innodb_os_log_pending_fsyncs | 50 |
+—————————-+———+

## 步骤3：调整IO参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改IO参数
[mysqld]
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 步骤4：重启MySQL服务
systemctl restart mysqld

## 步骤5：验证IO参数调优效果
# 查看IO参数
SHOW VARIABLES LIKE ‘innodb_io_capacity’;
SHOW VARIABLES LIKE ‘innodb_log_file_size’;
SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’;

# 查看IO等待情况
SHOW GLOBAL STATUS LIKE ‘Innodb_os%’;

## 步骤6：测试写入性能
# 测试插入操作
INSERT INTO orders (order_date, amount) VALUES (NOW(), 100.00);
# 优化前：0.05秒
# 优化后：0.01秒

# 测试更新操作
UPDATE orders SET amount = 200.00 WHERE id = 1;
# 优化前：0.04秒
# 优化后：0.01秒

# 处理效果
# 成功优化了IO参数
# IO等待时间减少了80%
# 写入操作响应时间从0.05秒减少到0.01秒
# 提高了系统的写入性能

# 并发参数调优案例
# 环境说明
# MySQL 8.0.29
# 服务器：8核16GB内存
# 数据库：test
# 表：users（100万行数据）

# 问题描述
# 发现高并发场景下连接数不足，响应时间长

# 解决方案
## 步骤1：分析当前并发参数
# 查看并发参数
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘thread_cache_size’;
SHOW VARIABLES LIKE ‘innodb_thread_concurrency’;

# 预期输出：
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 9 |
+——————-+
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_thread_concurrency | 0 |
+————————–+

## 步骤2：分析连接情况
# 查看当前连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 预期输出：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 9 |
| Threads_connected | 150 |
| Threads_created | 1000 |
| Threads_running | 10 |
+——————-+

## 步骤3：调整并发参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改并发参数
[mysqld]
max_connections = 1000
thread_cache_size = 100
table_open_cache = 2000
innodb_thread_concurrency = 16

# 步骤4：重启MySQL服务
systemctl restart mysqld

## 步骤5：验证并发参数调优效果
# 查看并发参数
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘thread_cache_size’;
SHOW VARIABLES LIKE ‘innodb_thread_concurrency’;

# 查看连接情况
SHOW GLOBAL STATUS LIKE ‘Threads%’;

## 步骤6：测试并发性能
# 使用sysbench测试并发性能
sysbench –db-driver=mysql –mysql-host=localhost –mysql-user=root –mysql-password=root –mysql-db=test –table-size=1000000 –threads=100 –time=60 –report-interval=10 oltp_read_write run

# 预期结果：
# 优化前：QPS = 500, 响应时间 = 0.2秒
# 优化后：QPS = 1500, 响应时间 = 0.07秒

# 处理效果
# 成功优化了并发参数
# 并发QPS从500提高到1500
# 响应时间从0.2秒减少到0.07秒
# 提高了系统的并发处理能力

# 综合参数调优案例
# 环境说明
# MySQL 8.0.29
# 服务器：16核32GB内存
# 存储：SSD
# 数据库：test
# 表：users（100万行数据）
# 表：orders（500万行数据）

# 问题描述
# 发现系统整体性能不佳，查询和写入响应时间长

# 解决方案
## 步骤1：分析当前参数配置
# 查看关键参数
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_io_capacity’;
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘slow_query_log’;

# 预期输出：
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_buffer_pool_size | 134217728 |
+————————-+
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_io_capacity | 200 |
+————————+
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+
+—————-+——-+
| Variable_name | Value |
+—————-+——-+
| slow_query_log | OFF |
+—————-+

## 步骤2：分析系统性能
# 查看缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 查看连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 查看慢查询
SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

## 步骤3：调整综合参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改综合参数
[mysqld]
# 内存参数
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
sort_buffer_size = 256K
join_buffer_size = 256K

# IO参数
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 并发参数
max_connections = 2000
thread_cache_size = 200
table_open_cache = 4000
innodb_thread_concurrency = 32

# 其他参数
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_bin = /var/lib/mysql/binlog
binlog_format = ROW

# 步骤4：重启MySQL服务
systemctl restart mysqld

## 步骤5：验证综合参数调优效果
# 查看关键参数
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_io_capacity’;
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘slow_query_log’;

# 查看系统性能
# 查看缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 查看连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 查看慢查询
SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

## 步骤6：测试综合性能
# 使用sysbench测试综合性能
sysbench –db-driver=mysql –mysql-host=localhost –mysql-user=root –mysql-password=root –mysql-db=test –table-size=1000000 –threads=100 –time=60 –report-interval=10 oltp_read_write run

# 测试复杂查询
SELECT u.name, o.order_id, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18 ORDER BY o.amount DESC LIMIT 10;

# 预期结果：
# 优化前：QPS = 500, 复杂查询响应时间 = 2.0秒
# 优化后：QPS = 2000, 复杂查询响应时间 = 0.1秒

# 处理效果
# 成功优化了综合参数
# 系统QPS从500提高到2000
# 复杂查询响应时间从2.0秒减少到0.1秒
# 提高了系统的整体性能