mysql Innodb参数配置

一时失言乱红尘 2023-01-10 05:24 165阅读 0赞

## 前言 ##

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

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

**1、innodb\_buffer\_pool\_size**

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

show global VARIABLES like 'innodb_buffer_pool_size'

![在这里插入图片描述][2021012310251216.png]  
换算成M就是128M，即Innodb引擎默认开辟了128M的空间作为优化查询性能使用，需要将这个和query cache区别开来，两者是在不同的层级上进行优化的

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

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

> show global status like ‘innodb\_buffer\_pool\_pages\_data’

innodb在读取数据从磁盘到内存时是以Page为单位进行读取的  
![在这里插入图片描述][20210123104351287.png]  
**Innodb全部缓存页的数量**

> show global status like ‘innodb\_buffer\_pool\_pages\_total’;

![在这里插入图片描述][20210123104654663.png]  
**Innodb缓存页的每一页的长度**

> show global status like ‘Innodb\_page\_size’

![在这里插入图片描述][20210123104845131.png]  
**页面使用率**

> 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’;

![在这里插入图片描述][20210123193804473.png]  
**4、innodb\_file\_per\_table=1**

设置独立的表空间文件，在存储上看到的那些 xxx.idb文件，通过设置这个参数，可以让Innodb引擎对于表的数据维护性更好，同时也更方便后续对表的数据拆分与迁移  
![在这里插入图片描述][20210123194204637.png]  
**5、innodb\_thread\_concurrency**

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

> show global variables like ‘innodb\_thread\_concurrency’;

![在这里插入图片描述][20210123194731596.png]  
这个参数在实际开发中建议可以根据自身的服务器好好配置下参数，以便提升并发环境下的mysql服务器的利用率

[2021012310251216.png]: /images/20221119/46c03eacff064d74b3b8282e5e0b6095.png
[20210123104351287.png]: /images/20221119/04315b93efe149ecb41833c047b650aa.png
[20210123104654663.png]: /images/20221119/b513ab7aa60f428188e18a32942276c2.png
[20210123104845131.png]: /images/20221119/fb6c758b9d514de9bbc665e59103826e.png
[20210123193804473.png]: https://img-blog.csdnimg.cn/20210123193804473.png
[20210123194204637.png]: https://img-blog.csdnimg.cn/20210123194204637.png
[20210123194731596.png]: https://img-blog.csdnimg.cn/20210123194731596.png