【参数汇总】mysql服务端/客户端常见优化参数

mysql服务端参数

1、innodb_buffer_pool_size （innodb索引buffer pool缓冲区大小）

默认大小为128M， 官方推荐其配置为系统内存的 50% 到 75% 。

一般innodb_buffer_pool_size要结合以下两个参数来设置：

1. innodb_buffer_pool_chunk_size

从 MySQL 5.7.5 开始，我们可以动态修改 InnoDB Buffer Pool 的大小。这个新特性同时也引入了一个参数 innodb_buffer_pool_chunk_size，buffer pool 会根据这个参数值的整数倍增加或减小。增大或减小缓冲池大小时，将以 chunk（块）的形式执行操作。chunk 大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为 128 MB。

2. innodb_buffer_pool_instances

一般地，mysql 5.7、8.0 下 INNODB_BUFFER_POOL_INSTANCES 默认为1（单实例），若 mysql 存在高并发和高负载访问，设置为1则会造成大量线程对 BUFFER_POOL 的单实例互斥锁竞争，这样会存在一定量的性能问题， 该参数可以设置为服务器 CPU 核心数，这样可在一定程度上提供并发性能。

该参数仅在你设置innodb_buffer_pool_size 等于1GB或大于1GB时才生效， 该参数的主要作用在于，对缓冲池在数 GB 范围内的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面时的锁竞争，以此来提高 MySQL 的并发性（相当于多实例）

2、innodb_log_file_size （innodb索引redolog大小）

2.3 常用操作
1、查看 innodb_log_file_size 大小

root@mysql 16:14:  [(none)]>show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 134217728 |
+----------------------+-----------+
1 row in set (0.06 sec)

2、实时观察 Redo Log 写入量

a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res

用于监控是很有用的，随时查看参数值的变化情况。

2.4 什么情况下调整该参数
innodb_log_file_size 值越大，在缓冲池中需要检查点刷新的行为就越少，因此也越节约磁盘I/O，但更大的日志文件也意味着在崩溃时恢复得更慢。建议将日志文件的大小设置为 256MB 或更大，这样可以满足一般情况下的需要。

当然，大家要根据实际情况，结合 innodb_log_files_in_group、innodb_buffer_pool_size 的值来确定最合适当前服务的 innodb_log_file_size 值。

3、innodb_flush_log_at_trx_commit （redolog持久化策略）

默认值为 1：在每次事务提交时写入日志并刷新到磁盘。
设置为 0 时：每秒写入日志并刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
设置为 2 时：每次事务提交后写入日志，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

优化：https://blog.csdn.net/jnrjian/article/details/129704893

4、sync_binlog （binlog持久化策略）

sync_binlog = 0 （mysql默认值 ）由文件系统决定将 binlog 同步到硬盘。
sync_binlog = 1 每提交一次事务，写一次 binlog，并使用 fsync() 同步到硬盘（mysql 在 autocommit模式下）。
sync_binlog = n 每提交一次事务，写一次 binlog，达到 n 次后，调用 fsync() 同步到硬盘。

————————————————
版权声明：本文为CSDN博主「云计算-Security」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/IT_ZRS/article/details/120622265

5、sort_buffer_size （单个链接 排序缓冲区）

–表示每个 需要进行排序的线程分配的缓冲区的大小
增加这个参数的值可以提高 ORDER BY 或 GROUP BY 操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB
左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB

6、join_buffer_size （单个链接联合查询 缓冲区）

默认8m，表示 联合查询操作所能使用的缓冲区大小 ，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

7、read_buffer_size （单个链接 读取单个表数据缓冲区）

表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节） 。
当线程从表中连续读取记录时需要用到这个缓冲区。
SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。

8、max_connections （最大链接数）

–表示 允许连接到MySQL数据库的最大数量 (默认值是 151)
如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数 不是越大 越好 ，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。

9、back_log （请求积压数）

高并发要求下适当增大
用于 控制MySQL监听TCP端口时设置的积压请求栈大小 。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 对于Linux系统推荐设置为小于512的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

10、thread_cache_size （空闲线程大小）

–线程池缓存线程数量的大小
当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。
那么为了提高性能可以增大该参数的值。默认为’60’，可以设置为120。
可以通过如下几个MySQL状态值来适当调整线程池的大小：
mysql> show global status like ‘Thread%’;

当 Threads_cached 越来越少，但 Threads_connected 始终不降，且 Threads_created 持续升高，可
适当增加 thread_cache_size 的大小。

11、wait_timeout （链接超时时长）

指定 一个请求的最大连接时间 ，对于4GB左右内存的服务器可以设置为5-10。
wait_timeout=28800
interactive_timeout=28800
connect_timeout=10

12、锁超时时间

lock_wait_timeout=30

13、推荐参数设置

[mysqld]
port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部锁定，减少出错几率增强稳定性。 
skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ 
back_log = 384 
key_buffer_size = 256M 
max_allowed_packet = 4M 
thread_stack = 256K 
table_cache = 128K 
sort_buffer_size = 6M 
read_buffer_size = 4M 
read_rnd_buffer_size=16M 
join_buffer_size = 8M
myisam_sort_buffer_size = 64M 
table_cache = 512
thread_cache_size = 64 
query_cache_size = 64M 
tmp_table_size = 256M
max_connections = 768 
max_connect_errors = 10000000
wait_timeout = 10 
thread_concurrency = 8 #该参数取值为服务器逻辑CPU数量*2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8 
skip- networking #开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！ 
table_cache=1024 
innodb_additional_mem_pool_size=4M #默认为2M 
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M #默认为1M 
innodb_thread_concurrency=8 #你的服务器CPU有几个就设置为几。建议用默认一般为8 
tmp_table_size=64M #默认为16M，调到64-256最挂
thread_cache_size=120 query_cache_size=32M

14、数据类型优化

对于 非负型 的数据（如自增ID、整型IP）来说，要优先使用无符号整型 UNSIGNED 来存储
遇到整数类型的字段可以用 INT 型
使用TIMESTAMP存储时间
用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
避免使用ENUM类型

mysql客户端参数