MySQL实战之MySQL调优

三人行 | Dylen

已于 2022-05-22 14:50:07 修改

阅读量1.1k

点赞数

分类专栏： java高级文章标签：数据库 mysql java

于 2022-05-03 13:31:57 首次发布

本文链接：https://blog.csdn.net/zwq56693/article/details/124550503

版权

本文介绍了MySQL调优的重要性，包括优化服务器硬件配置、调整MySQL配置参数以提高吞吐量，以及针对不同环境的配置示例。此外，提到了结合分库分表和使用中间映射表等水平扩展策略解决高并发问题，并建议参考MySQL官网进行详细配置参数设置。

摘要由CSDN通过智能技术生成

MySQL实战之MySQL调优

前言

前边写了一篇博客介绍MySQL的压测，想不想让一台MySQL有更大的吞吐量？想不想根据机器的实际配置调整一下默认参数？比如：Buffer Poll默认是128M,这怎么抗的住10K+的并发？还有配置是不是固定不变的？是不是要结合压测动态调整？
大家也知道除了优化一台MySQL服务器，还要结合分库分表，加中间映射表，ES等水平扩展来解决高并发，高可用的问题

MySQL调优

如果对配置有疑惑，希望到官网查看具体的参数配置：MySQL官网配置详解
首先确保服务器是SSD，以确保顺序读写和随机读写的能力
结合项目需要配置对应的带宽，CPU，内存，要不巧妇难为无米之炊
首先需要修改mysql可以操作的文件句柄数
1. 在/etc/security/limits.conf添加
```
mysql hard nofile 65535
mysql soft nofile 65535
```
2. 在/usr/lib/systemd/system/mysqld.service添加
```
LimitNOFILE=65535
LimitNPROC=65535
```
3. 创建目录赋予权限
  1. 创建目录: mkdir -p /var/log/mysql/{redolog,undolog,mysqld}
  2. 创建慢sql文件: touch /var/log/mysql/mysqld/slow.log
  3. 权限: chown -R mysql:mysql /var/log/mysql

开发环境配置-v5.7(4C8G)

# 数据库配置
[mysqld]
port=3306	# 默认端口号，也可自定义
bind_address=0.0.0.0	# 运行远程访问
character_set_server=utf8mb4	# 因为有手机端，可能会存emoji表情
autocommit=1	# 事务自动提交
skip_name_resolve=1	# 忽略反向dns解析
lower_case_table_names=1 # 忽略数据库大小写
max_connections=60	# 用户的最大连接数
max_connect_errors=100	# 密码错误超过10次会阻止连接,
innodb_flush_log_at_trx_commit=2	# redo log和bin log先写入os cache再写入日志，可能会丢失1秒的数据，数据安全要求高的使用默认的1	
transaction_isolation=READ_COMMITTED	# 设置事务隔离级别是读已提交
explicit_defaults_for_timestamp=1	# timestamp类型字段不更新为当前时间
join_buffer_size=8m	# 优化联表查询效率
tmp_table_size=32m	# 超过该值会利用磁盘文件进行操作，如group by操作
max_allowed_packet=512m	# 导入数据的限制值
interactive_timeout=600	# 剔除超过10分钟的连接
wait_timeout=600 # 等待超时时间10分钟
read_buffer_size=2m	# 读入缓冲区大小，可以优化全表扫描效率
read_rnd_buffer_size=8m	# 超过该值会采用随机读取
sort_buffer_size=4m	# 提高排序和分组的效率
innodb_buffer_pool_size=256m	# buffer pool的大小
innodb_buffer_pool_instances=1	# buffer pool的实例数 buffer pool总大小=(chunk大小（默认128m） * buffer pool数量)的倍数	
innodb_buffer_pool_load_at_startup=0	# 不使用预热
innodb_buffer_pool_dump_at_shutdown=0	# 关机不用把内存中数据存档
innodb_lru_scan_depth=500	# lru链表的深度，增加吞吐量
innodb_lock_wait_timeout=60 # 事务锁超时时间
innodb_io_capacity_max=1000	# 决定mysql的tps
innodb_io_capacity=500	# 最大值的一半
innodb_flush_method=O_DIRECT # innodb缓存直接向磁盘上写
innodb_file_format=Barracuda	# 兼容性较好
innodb_file_format_max=Barracuda # 同上
innodb_flush_neighbors=1	# 快速刷新脏页
innodb_log_file_size=64m	# 日志文件大小
innodb_log_buffer_size=16m	# 日志缓存区的大小
innodb_large_prefix=1	# utf8mb4需要开启
innodb_thread_concurrency=2 # 和cpu核数相同
innodb_print_all_deadlocks=1	# 记录死锁信息
innodb_strict_mode=1	# 严格模式
slow_query_log=1	# 慢查询
log_queries_not_using_indexes=1	# 没有走索引的语句
log_slow_admin_statements=1 # 写入慢查询日志的语句中包含慢速管理语句
log_throttle_queries_not_using_indexes=50 # 记录的条数
expire_logs_days=30 # bin log的保留时间
long_query_time=10 # 超过10秒的会被记录为慢日志
min_examined_row_limit=1 # 超过100行且大于10s才是慢sql
master_info_repository=TABLE # 从机保留主节点信息的方式
relay_log_info_repository=TABLE # 从机保留relay log信息的方式
innodb_buffer_pool_dump_pct=25 # 保留使用最频繁的数据25%
innodb_page_cleaners=2 # CPU核数
innodb_undo_log_truncate=1 # 在线回收undo log
innodb_max_undo_log_size=1G # undo log的最大值
log_timestamps=system # 日期格式
show_compatibility_56=on # 兼容mysql5