mysql Innodb参数配置

前言
作为mysql底层最重要的组件,了解Innodb的原理并掌握其基本的参数配置对于mysql性能调优具有非常重要的作用

下面结合工作中和Innodb部分常用的参数配置进行说明

1、innodb_buffer_pool_size

该参数表示mysql 的Innodb存储引擎为优化查询性能而开辟出的一块内存缓冲区

show global VARIABLES like 'innodb_buffer_pool_size'

 

 

换算成M就是128M，即Innodb引擎默认开辟了128M的空间作为优化查询性能使用，需要将这个和query cache区别开来，两者是在不同的层级上进行优化的

在理解这个参数时，需要结合下面几个参数一起理解，

Innodb已使用的缓存 “页Page”的数量

show global status like 'innodb_buffer_pool_pages_data'

 

innodb在读取数据从磁盘到内存时是以Page为单位进行读取的

Innodb全部缓存页的数量

show global status like 'innodb_buffer_pool_pages_total';

Innodb缓存页的每一页的长度 

show global status like 'Innodb_page_size'

页面使用率 

result = innodb_buffer_pool_pages_data / innodb_buffer_pool_pages_total * 100%

可以利用这个结果作为mysql服务器优化的参考指标

result > 95%，考虑增大 innodb_buffer_pool_size ，建议使用物理内存的75%
result < 95%，考虑减小 innodb_buffer_pool_size ，建议设置为：innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 （MB）
如果修改innodb_buffer_pool_size，使用如下命令即可：

set global innodb_buffer_pool_size = XXX

2、innodb_flush_log_at_trx_commit

控制Innodb事务日志写入的过程

在事务控制中，存在“事务区”来保证事务的完整性，在事务提交成功以后，这些事务区的数据会写入到硬盘上，同时事务操作日志(log)也需要向硬盘中写入，这个参数就是用来控制何时写入日志数据的

该参数有3个等级，分别为0，1，2

0：log buffer 将每秒一次的写入log file中，在模式在事务提交的时候，不会主动触发写入磁盘的操作
1：每次事务提交的时候，mysql都会把log buffer的数据写入到 log file，并且做flush操作(刷到磁盘)，该模式为系统默认
2：每次事务提交的时候都会把 log buffer的数据写入到log file，但是flush（刷到磁盘）的操作并不会同时进行，在该模式下，mysql会每秒执行一次flush的操作
三种模式下的数据安全性比较：

当设置为0时，速度最快，但不太安全，如果mysql进程崩溃会导致上一秒的所有事务数据丢失【适用于高并发场景下对于事务日志数据的读写不是很敏感的场景】
设置为1时，该模式是最安全的，但也是最慢的一种方式，在mysql服务进程崩溃或者主机宕掉时，最多丢失一个语句或者事务【适用于强事务的业务操作】
当设置为2时，该模式速度较快，相对0时也安全，在进程崩溃或者宕机时，上一秒的所有事务数据才会丢失
注意：对于事务的操作比较频繁的业务操作中，需要特别关注这个参数的配置，在实际测试时发现，该参数的设置对于插入数据的速度影响非常大，设为2的时候插入10000条数据需要2秒，设置为0的时候只需要1秒，设置为1的时候，则需要200多秒，因此，mysql官方运维手册也建议尽量将插入操作合并为一个事务，即将众多的小事务合并，以便可以大幅度提升速度

3、innodb_doublewrite

双写操作，即同一份数据写2份，保证数据存在一个副本，预防数据因为物理介质的存储因素造成数据丢失（默认开启）

show global variables like ‘innodb_doublewrite’;

4、innodb_file_per_table=1

设置独立的表空间文件，在存储上看到的那些 xxx.idb文件，通过设置这个参数，可以让Innodb引擎对于表的数据维护性更好，同时也更方便后续对表的数据拆分与迁移

5、innodb_thread_concurrency

设置Innodb线程的并发数，默认值为0时表示不被限制（即可以创建任意多个线程来处理请求），若要设置最好与服务器的CPU核心数相同或者CPU核心数的2倍，以最大化的提升Innodb的使用效率

show global variables like ‘innodb_thread_concurrency’;

这个参数在实际开发中建议可以根据自身的服务器好好配置下参数，以便提升并发环境下的mysql服务器的利用率
————————————————
版权声明：本文为CSDN博主「小码农叔叔」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhangcongyi420/article/details/113030932