Mysql配置文件 innodb引擎

[TOC]

## innodb参数
### innodb_buffer_pool_size
这个是Innodb最重要的参数，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值为128M。

如果是一个专用innodb引擎的服务器，那么它可以占到内存的70%-80%。并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M就够了

查询：
在线配置：
配置文件：`innodb_buffer_pool_size = 16G`

### innodb_read_io_threads|innodb_write_io_threads
在MySQL5.1.X版本中，innodb_file_io_threads参数默认是4，该参数在Linux系统上是不可更改的，但Windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入输出）请求的数量。

在MySQL5.5.X版本中，或者说是在InnoDB Plugin1.0.4以后，就用两个新的参数，即innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。

假如CPU是2颗8核的，共16核，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8

如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6

### innodb_open_files
限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。

但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

查询：
在线配置：
配置文件：`innodb_open_files = 1000`

### innodb_log_file_size
这个参数指定在一个日志组中，每个log的大小。

innodb的logfile就是事务日志，用来在mysql crash后的恢复。所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。

在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB。一般取256M可以兼顾性能和recovery的速度。

查询：`select @@innodb_log_file_size;`
在线配置：
配置文件：`innodb_log_file_size=256M`

### innodb_log_buffer_size
事务在内存中的缓冲，也就是日志缓冲区的大小，默认设置即可，具有大量事务的可以考虑设置为64-512MB。

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

查询：
在线配置：
配置文件：`innodb_log_buffer_size = 128M`

### innodb_flush_log_at_trx_commit
控制事务的提交方式,也就是控制log的刷新到磁盘的方式，这个参数只有3个值（0，1，2）.默认为1。

当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。

当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

查询：
在线配置：
配置文件：`innodb_flush_log_at_trx_commit = 1`

### innodb_flush_method
这个参数控制着innodb数据文件及redo log的打开、刷写模式，有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

fdatasync：调用fsync()去刷数据文件与redo log的buffer

O_DSYNC时：innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

O_DIRECT时：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。

在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的

查询：
在线配置：
配置文件：`innodb_flush_method=O_DIRECT`

### innodb_data_home_dir
innodb引擎的共享表空间数据文件根目录。如果未指定，默认会在datadir目录下创建ibdata1 作为innodb tablespace。

查询：
在线配置：
配置文件：`innodb_data_home_dir = /Data/data`

### innodb_data_file_path
它不仅指定了所有InnoDB数据文件的路径，还指定了初始大小分配，最大分配以及超出起始分配界线时是否应当增加文件的大小。

例如，假设希望创建一个数据文件sales，初始大小为100MB，并希望在每次达到当前大小限制时，自动增加8MB（8MB是指定autoextend时的默认扩展大小).但是，不希望此文件超过1GB，可以使用如下配置:
`innodb_data_home_dir =`
`innodb_data_file_path = /data/sales:100M:autoextend:8M:max:1GB`

如果此文件增加到预定的1G的限制，可以再增加另外一个数据文件,
`innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/sales2:100M:autoextend:8M: max:2GB`

要注意的是，在这些示例中，**inndb_data_home_dir**参数开始设置为空，因为最终数据文件位于单独的位置(/data/和/data2/）.如果希望所有 InnoDB数据文件都位于相同的位置，就可以使用innodb_data_home_dir来指定共同位置，然后在通过 **inndo_data_file_path**来指定文件名即可。如果没有定义这些值，将在datadir中创建一个sales。

查询：
在线配置：
配置文件：`innodb_data_file_path = ibdata1:10M:autoextend`

### innodb_purge_threads
由于每次DML操作都会生成Undo页，系统需要定期对这些undo页进行清理，也就是所谓purge操作。在5.5之前这些都是在master线程中完成，但5.5及之后的版本可以通过innodb_purge_threads来控制是否使用独立线程进行purge操作。

查询：
在线配置：
配置文件：`innodb_purge_threads = 1`

### innodb_log_group_home_dir
此参数确定日志文件组中的文件的位置，日志组中文件的个数由**innodb_log_files_in_group**确定，此位置设置默认为MySQL的datadir。

查询：
在线配置：
配置文件：`innodb_log_group_home_dir = /Data/data`

### innodb_autoinc_lock_mode
[参考](https://www.cnblogs.com/JiangLe/p/6362770.html "参考")

这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为。通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡

insert大致上可以分成三类：
1、simple insert 如insert into t(name) values('test')
2、bulk insert 如load data | insert into ... select .... from ....
3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

innodb_auto_lockmode有三个取值：
0 这个表示tradition 传统
1 这个表示consecutive 连续
2 这个表示interleaved 交错

tradition(innodb_autoinc_lock_mode=0) 模式:
1、它提供了一个向后兼容的能力
2、在这一模式下，所有的insert语句("insert like") 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

consecutive(innodb_autoinc_lock_mode=1) 模式:
1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的(它保证了基于语句复制的安全)
2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

interleaved(innodb_autoinc_lock_mode=2) 模式：
1、由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

总结：
如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可以得到更好的性能。

查询：
在线配置：
配置文件：`innodb_autoinc_lock_mode  = 2`

### innodb_buffer_pool_dump_at_shutdown
在之前的版本里，如果一台高负荷的机器重启后，内存中大量的热数据被清空，此时就会重新从磁盘加载到Buffer_Pool缓冲池里，这样当高峰期间，性能就会变得很差，连接数就会很高。

在MySQL5.6里，一个新特性避免的这种问题的出现。在关闭时把热数据dump到本地磁盘。

查询：
在线配置：
配置文件：`innodb_buffer_pool_dump_at_shutdown = 1`

### innodb_buffer_pool_load_at_startup
在启动时把热数据加载到内存。

查询：
在线配置：
配置文件：`innodb_buffer_pool_load_at_startup  = 1`

### innodb_file_per_table
可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

优点：
1．每个表都有自已独立的表空间。
2．每个表的数据和索引都会存在自已的表空间中。
3．可以实现单表在不同的数据库中移动。
4．空间可以回收（除drop table操作处，表空间不能自已回收）

缺点：
1．单表增加过大，如超过100个G。

结论：
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一 下：innodb_open_files 。

查询：`show variables like '%per_table%';`
在线配置：
配置文件：`innodb_file_per_table=1`

### innodb_support_xa
设置为1，标志支持分布式事物，主要保证binary log和其他引擎的主事务数据保持一致性，属于同步操作；

如果你设置0，就是异步操作，这样就会一定程度上减少磁盘的刷新次数和磁盘的竞争。

查询：
在线配置：
配置文件：`innodb_support_xa = 0`

### innodb_status_file
开启后，**SHOW INNODB STATUS** 的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid，其中pid是服务器进程ID。这个文件在MySQL数据目录里创建。

正常关机之时，InnoDB删除这个文件。如果发生不正常的关机，这些状态文件的实例可能被展示，而且必须被手动删除。在移除它们之前，你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时，innodb_status.pid文件被创建。

查询：
在线配置：
配置文件：`innodb_status_file  = 1`

### innodb_lock_wait_timeout
全局等待事务锁超时时间，在回滚(rooled back)之前，InnoDB事务将等待超时的时间(单位 秒)

查询：`SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';`
在线配置：`SET  GLOBAL innodb_lock_wait_timeout=100;`
配置文件：`innodb_lock_wait_timeout = 100`

### innodb_file_io_threads
此参数指定InnoDB表可用的文件I／O线程数，MySQL开发人员建议在非Windows平台中这个参数设置为4

查询：
在线配置：
配置文件：`innodb_file_io_threads  = 4`

### innodb_thread_concurrency
同一时刻能够进入innodb层次并发执行的线程数(注意是并发不是并行)，如果超过CPU核数，某些线程可能处于就绪态而没有获得CPU时间轮片。

如果SERVER层的线程大于这个值，那多余的线程将会被放到一个叫做wait queue的队列中，而不能进入INNODB层次，进不到innodb层当然也就不能干活了，谈不上获得CPU。

既然是一个队列那么它必然满足先进入先出的原则。这也是前面说的长痛不如短痛，与其让你不断的进行上文切换还不如把你处于睡眠态放弃CPU使用权，默认这个值是0，代表不限制。

查询：
在线配置：
配置文件：`innodb_thread_concurrency  = 0`

### innodb_max_dirty_pages_pct
这个百分比是，最大脏页的百分数，当系统中 脏页 所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。

这个脏页百分比的阈值，在操作系统和其它数据库中都有用到。主要是为了提高效率。比如当你的页面中脏页过多的时候，就调用磁盘IO把内存的“脏”数据写回到磁盘。

脏页小的话，无法将几个改变一起flush到磁盘上去了。脏页越大，可以拼凑在一起flush到磁盘上去。从而减少写。

查询：`show variables like 'innodb_max_dirty_pages_pct';`
在线配置：
配置文件：`innodb_max_dirty_pages_pct = 85`