最全mysql之my(2)，2024年最新值得一看

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
flush_time=0
join_buffer_size=256K
max_connect_errors=100
max_allowed_packet = 16M
open_files_limit=4161
table_definition_cache=1400
binlog_row_event_max_size=8K

#二进制的类型，这个有很大学问，稍候我也会告诉你的。
binlog-format = MIXED

#事务锁时间，这个同样学问很大。
innodb_lock_wait_timeout = 20

#事务锁级别，这个学问同样很大很大啊
transaction-isolation = REPEATABLE-READ

binlog_cache_size = 1M

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

#这个参数就是设置二进制文件的路径的，注意啊，注意啊！
log_bin=mysql-bin

server_id = 1

[mysqldump]
max_allowed_packet = 16M

#### 二、innodb\_flush\_log\_at\_trx\_commit



> 
> Controls the balance between strict ACID compliance for commit operations, and higher performance   
>  that is possible when commit-related I/O operations are rearranged and done in batches. You can   
>  achieve better performance by changing the default value, but then you can lose up to a second of   
>  transactions in a crash.   
>  • The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB   
>  log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.   
>  • With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once   
>  per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed   
>  at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second,   
>  due to process scheduling issues. Because the flush to disk operation only occurs approximately once   
>  per second, you can lose up to a second of transactions with any mysqld process crash.   
>  • With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction   
>  commit and the log file is flushed to disk approximately once per second. Once-per-second flushing   
>  is not 100% guaranteed to happen every second, due to process scheduling issues. Because the   
>  flush to disk operation only occurs approximately once per second.
> 
> 
> 


1. innodb\_flush\_log\_at\_trx\_commit=1，innodb的缓存会在事务提交或者每秒钟时都会进行磁盘的刷新操作，默认值。
2. innodb\_flush\_log\_at\_trx\_commit=2，innodb缓存会在提交事务时写入到事务日志但不会刷新磁盘，然后在每秒钟时进行磁盘刷新操作。
3. innodb\_flush\_log\_at\_trx\_commit=0，每秒钟时缓存写入日志，同时刷新磁盘。


根据我们的性能测试，发现在innodb\_flush\_log\_at\_trx\_commit=2，数据的读写速度和1的时候有明显的提升。


#### 三、innodb\_buffer\_pool\_size、innodb\_buffer\_pool\_instances


这两个参数，你必须得看看这个[mysql：提升性能的最关键参数](https://bbs.csdn.net/topics/618668825)


#### 四、binlog-format = MIXED


该种二进制日志模式是statement和row模式的结合体，不过需要注意的是mysql 5.7中的默认格式是STATEMENT，这会影响到服务端代码如java对事务的处理，非常关键，没有调试好的话，会导致事务不回滚。



> 
> In MySQL 5.7, the default format is STATEMENT.   
>  You must have the SUPER privilege to set either the global or session binlog\_format value.   
>  The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.   
>  When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.
> 
> 
> 


#### 五、innodb\_lock\_wait\_timeout = 20


事务回滚的时间间隔，定为20秒，是我们项目认为比较合理的值，另外可参见[Transactional和mysql究竟有什么关系](https://bbs.csdn.net/topics/618668825)。


#### 六、transaction-isolation = REPEATABLE-READ


事务的隔离级别，对多事务的读写也有着关键的作用，可参见[高性能mysql札记：事务](https://bbs.csdn.net/topics/618668825)。



> 
> 笑对现实的无奈，不能后退的时候，不再傍徨的时候，永远向前　路一直都在──陈奕迅《路一直都在》   
>  本文出自:【[沉默王二的博客](https://bbs.csdn.net/topics/618668825)】


![img](https://img-blog.csdnimg.cn/img_convert/0d0f755ce8a79c7f242e9706fd41b2f3.png)
![img](https://img-blog.csdnimg.cn/img_convert/87d85c1165922b4890fc497660e3a2cd.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上C C++开发知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[如果你需要这些资料，可以戳这里获取](https://bbs.csdn.net/topics/618668825)**

于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[如果你需要这些资料，可以戳这里获取](https://bbs.csdn.net/topics/618668825)**