mysql 设置默认值_MySQL参数设置01

innodb_buffer_pool_size InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。

innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%.脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';

select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';

select @a/@b;

innodb_flush_neighbors 参数就是用来控制刷脏页时是否刷相邻的数据页脏页，值为 1 的时候会有“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。 找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

innodb_file_per_table  表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

sort_buffer_size MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。MySQL为每个线程分配sort_buffer_size。

max_length_for_sort_data MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法(不使用全字段排序)。

tmp_table_size 内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

sys.schema_table_lock_waits 直接找出造成阻塞的 process id 表级锁？

sys.innodb_lock_waits 谁占着这个写锁 行锁

innodb_lock_wait_timeout 死锁等待超时时间，InnoDB引擎默认值是50s。

innodb_deadlock_detect 设置死锁自动检测是否开启，on为开启

binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小(默认32K)，就要暂存到磁盘。

sync_binlog 控制 binlog 的写入策略

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

innodb_flush_log_at_trx_commit 控制 redo log 的写入策略

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

innodb_log_file_size ：该参数决定着mysql事务日志文件( redo log)(ib_logfile0)的大小；

设置的太小：当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点(Checkpoint)，这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。由于日志切换更频繁，也就直接导致更多的BUFFER FLUSH，由于日志切换的时候是不能BUFFER FLUSH的， BUFFER写不下去，导致没有多余的buffer 写redo， 那么整个MYSQL就HANG住，还有一种情况是如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换(因为实例恢复还需要，不能被循环复写)这样mysql就hang住了。可以根据文件修改时间来判断日志文件的旋转频率，旋转频率太频繁，说明日志文件太小了。

设置的太大：设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务(也就是实例恢复中的前滚, 利用redo从演变化来恢复buffer cache中的数据)，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。

如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要

一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。

具体依据如下：我经常设置为 64-512MB

首先在业务高峰期，计算出1分钟写入的redo量，然后评估出一个小时的redo量；

innodb_log_files_in_group 控制事务日志(redo log)文件数。默认值为2。mysql 事务日志文件是循环覆写的。需要注意的是：innodb_log_files_in_group是静态的变量，需要以“干净”的方式更改并重新启动，否则mysql启动不起来。也就是说如果想把原来是2的修改成3，这样的话你需要先关闭mysql服务，把原来的ib_logfile0和ib_logfile1文件删掉，然后启动mysql，否则报错

innodb_log_buffer_size 确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。

binlog_group_commit_sync_delay 表示延迟多少微秒后才组提交 fsync binlog ( 由文件系统的page cache 永久化到磁盘)

binlog_group_commit_sync_no_delay_count 表示累积多少次以后才组提交 fsync binlog ( 由文件系统的page cache 永久化到磁盘)

innodb_thread_concurrency 控制 InnoDB 的并发线程上限。也就是说，一旦并发线程数达到这个值，InnoDB 在接收到新请求的时候，就会进入等待状态，直到有线程退出。通常情况下，我们建议把 innodb_thread_concurrency 设置为 64~128 之间的值。在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁(也包括间隙锁)的线程是不算在里面的。MySQL 这样设计是非常有意义的。因为，进入锁等待的线程已经不吃 CPU 了；更重要的是，必须这么设计，才能避免整个系统锁死。

net_buffer_length 控制net_buffer大小，默认是 16k。MySQL 是“边读边发的”，获取一行，写到 net_buffer 中。直到 net_buffer 写满，调用网络接口发出去。

read_rnd_buffer_size Multi-Range Read，回表过程是一行行地查数据，会出现随机访问，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：

1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

2. 将 read_rnd_buffer 中的 id 进行递增排序；

3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。