mysql字段大小自动伸缩卷轴_MySQL 5.5 服务器变量详解(二)

innodb_adaptive_flushing={ON|OFF}

设定是否允许MySQL服务器根据工作负载动态调整刷写InnoDB buffer pool中的脏页的速率。动态调整刷写速率的目的在于避免出现IO活动尖峰。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。

innodb_adaptive_hash_index={ON|OFF}

设定是否启用InnoDB的自适应hash索引。基准测试结果显示，自适应hash索引并非对所有工作负载都有益，因此需要根据实际应用场景的测试结果选择更合适的值。此特性默认已启用，可以通过命令行选项--skip-innodb_adaptive_hash_index将其禁用。作用范围是全局，可用于选项文件，属动态变量。

innodb_additional_mem_pool_size={2097152 .. 4294967295}

设定innodb存储引擎为了存储数据字典和其它内部数据结构的内在池大小，单位是字节。表的个数越多，此参数的值就应该设定的越大；当InnoDB用完此内存池的空间，它就会向操作系统申请内存空间，并将向错误日志记录警告信息。默认大小是8MB。作用范围为全局，可用于选项文件，属非动态变量。

innodb_autoextend_increment={1 .. 1000}

当共享表空间没有多余的存储空间时，如果其允许自动增长，此变量可用于设定其单次增长的空间大小，单位是MB，默认值是8。设置了变量innodb_file_per_table的值为1时InnoDB会为每张表使用一个单独的表空间文件，而innodb_autoextend_increment变量不会对此种表空间产生影响。作用范围为全局级别，可用于选项文件，属动态变量。

innodb_autoinc_lock_mode={0|1|2}

设定用于生成“自动增长(auto_increment字段)”值的锁模型。其可接受的值有0、1和2，分别用于表示"traditional"、"consecutive"和"interleaved"锁模型。默认值为1。作用范围为全局，可用于选项文件，属非动态变量。

innodb_buffer_pool_instances=#

设定将InnoDB的buffer pool分隔为多少个区域。对于有着数GB空间的buffer pool来说，将其分隔为多个区域可以降低不同的线程对缓存页面的读写操作时资源争用系数，进行增强其并发能力。在buffer pool中，读取或存入页面时所选择的区域是基于hash算法随机进行的。每个buffer pool管理自己的空闲列表、列表刷写、LRU以及其它跟buffer pool相关的数据结构，并通过各自的互斥锁进行保护。

此变量仅在变量innodb_buffer_pool_size的值大于1G时才能发挥功用，缓冲池的整体空间将由各buffer pool实例分割使用。出于最佳效用的目的，建议配合使用innodb_buffer_pool_instances和innodb_buffer_pool_size变量以使得每个buffer pool实例的都至少有1G的空间。作用范围为全局，可用于选项文件，属非动态变量。

innodb_buffer_pool_size=#

设定InnoDB缓存表数据和索引的内存缓冲区大小，单位是字节。其默认值为128MB，最大值依赖于CPU架构。在一个较繁忙的服务器上，当缓冲池(buffer pool)大于1G时，设定innodb_buffer_pool_instances的值大于1可提其升伸缩能力。innodb_buffer_pool_size变量的值越大，MySQL服务器完成数据访问时就需要越少的IO，因此，在一个有够较大内存且为MySQL服务专用的服务器上，可以将此值设置为物理内存的80%。但如果出现如下情况，建议缩小此变量的值：(1)物理内存资源紧张导致内存页面换出；(2)InnoDB会为缓冲和控制结构(buffers and control structures)预留额外的内存，因此事实上其占用的内存空间可能会比指定的数值大10%左右，这不可能超出对内存资源分配的预估；(3)内存地址空间必须连续，这在基于DLL库使用特殊地址空间的Windows系统上可能会出现意外情况；(4)缓冲池的初始化所需要时长与为其指定的空间大小成正比，例如有10G缓冲池的x86_64的Linux系统上，初始化时间大约要6秒钟。作用范围为全局，可用于选项文件，属非动态变量。

innodb_change_buffering=#

当在表上执行INSERT、UPDATE或DELETE操作时，索引中尤其是第二索引中的数据未必按序存储，这就可能引发随机IO以完成第二索引的更新操作。此变量用来设定InnoDB是否启用修改缓冲(change buffering)以及使用何种类型的修改缓冲。修改缓冲是一种优化方式，它能够通过延迟写入操作至第二索引将IO操作转换为顺序模式。其接受的值有inserts(缓冲insert操作)、deletes(缓冲delete-marking操作)、changes(缓冲insert和delete-marking操作)、purges(缓冲purge操作)、all(缓冲insert、delete-marking和purge操作)和none(不缓冲任何操作)。默认值是all。MySQL 5.5.4之前的版本只接受inserts和none两种值。作用范围为全局，可用于选项文件，属动态变量。

innodb_checksums={ON|OFF}

InnoDB能够使用校验和(checksum)来验正从磁盘读取的所有页面数据的完整性，从而提高对硬件或数据文件损坏的容错能力。默认为启用，然而，在少数情况下或许需要禁用这种特性，这可以通过使用--skip-innodb-checksums命令行选项实现。作用范围为全局，可用于选项文件，属非动态变量。

innodb_commit_concurrency={0 .. 1000}

设定InnoDB可同时运行的“提交”操作线程的数量。0表示无限制。此变量不能在运行时将其从“零值”修改为“非零值”，但可以从一个“非零值”修改为其它值。作用范围为全局，可用于选项文件，属非动态变量。

innodb_concurrency_tickets=#

在一个线程进入(enter)InnoDB时，其将会获取一定数量的“自由卷轴”(free tickets)并凭这些卷轴自由出入InnoDB(即免检)，直到其卷轴耗尽；而后的线程将被置于等待队列中，并可能需要再次接受并发上限限制检查。此变量则正是用于设定可同时进入InnoDB的线程并发数，即线程的“自由卷轴”数量。默认值是500。作用范围为全局，可用于选项文件，属动态变量。

innodb_data_file_path=IBDATA_FILE

指定InnoDB的各个数据文件及其大小，文件多于一个时彼此间用分号隔开。数据文件路径可以为相对路径，其相对于innodb_data_home_dir变量所指向的目录；而文件大小的表示可以以K(KB)、M(MB)、G(GB)为单位，但这些文件的大小之和至少要达到10MB。在没有显式设定innodb_data_file_path变量的情况下，MySQL服务器会在数据目录中自动创建一个可自动增长、初始大小为10MB的名为ibdata1的数据文件。单个数据文件的大小上限取决于操作系统，这意味着可以使用操作系统所支持的最大单个文件大小以为其数据文件的体积上限。InnoDB还支持使用裸设备作为数据文件。作用范围为全局，可用于选项文件，属非动态变量。

innodb_data_home_dir=/PATH/TO/DIR

InnoDB所有共享表空间数据文件的目录路径。默认值为MySQL的数据目录。可以将此变量的值设置为空，然后在innodb_data_file_path中为每个数据文件使用绝对路径。此变量不影响变量innodb_file_per_table启用状态下的每表表空间的数据文件。作用范围为全局，可用于选项文件，属非动态变量。

innodb_doublewirte={ON|OFF}

设定InnoDB是否使用双写缓冲。默认为启用。InnoDB在对页面进行部分写入的时候使用双写缓冲，以防止数据损坏。双写缓冲是表空间中一个特殊的保留区域，其大小足够在一个连续区间容纳100个页面。当InnoDB把页面从缓冲池刷写至磁盘时，它会先把这些页面刷到双写缓冲中，然后再保存至真正的目标位置。因此，双写缓冲本质上是最近写入页面的备份，其可确保每次写入的原子性和可持续性。在有些情况下双写缓冲是不必要的，例如在从服务器上就可以将之禁用；此外，一些文件系统(如ZFS)自身也会实现此功能，那么InnoDB就不用做重复的工作了。作用范围为全局，可用于选项文件，属非动态变量。

innodb_fast_shutdown={0|1|2}

设定InnoDB关闭模式。其可接受的值中，“0”表示慢速关闭，这意味着InnoDB关闭之前会完成完全清写(full purge)和修改缓冲合并(insert buffer merge)操作；“1”是默认值，它表示InnoDB在关闭时会跳过模式0中进行的这些操作，这也是其之所以称作“快速关闭”的原因；“2”表示InnoDB仅刷写日志信息并执行冷(cold)关闭，此时并没有事务丢失，只是下次启动MySQL服务时需要花费较长的时间进行故障恢复(crash recovery)。

执行慢速关闭时其过程可能会持续数分钟的时间，甚至在有些极端情况下，比如有着大量数据缓冲的场景，此过程时长会以小时来计。一般情况下仅在对MySQL进行主版本升级时才需要进行慢速关闭以使得数据文件能够为完全适应新版本而准备妥当。通常也只能遇到紧急状况或出于调试的目的才需要将此变量的值设定为2，以便让处于有可能损坏风险中的数据执行最快速度的关闭。作用范围为全局，可用于选项文件，属动态变量。

innodb_file_format={Antelope|Barracuda}

设定新建InnoDB表的文件格式。其可接受的参数有Antelope和Barracuda，但这仅对基于变量innodb_file_per_file的每表表空间文件有影响。某些InnoDB特性如表压缩功能仅有Barracuda文件格式支持。作用范围为全局，可用于选项文件，属动态变量。

innodb_file_format_check={ON|OFF}

用于设定InnoDB是否在MySQL服务器启动时检查共享表空间的文件格式标签。检查标签时如果其高于当前InnoDB版本所支持的能力，InnoDB就会产生错误并拒绝启动；否则，对MySQL 5.5.5 及后来的版本来说InnoDB则会设置变量innodb_file_format_max的值为共享表空间的文件格式标签，而对于MySQL 5.5.5之前的版本来说，InnoDB会将共享表空间的文件格式设置为变量innodb_file_format_check的值。作用范围为全局，可用于选项文件，属非动态变量。

innodb_file_format_max={Antelope|Barracuda}

在MySQL服务启动时，InnoDB会将变量innodb_file_format_max的值设置为共享表空间的文件格式标签(比如，Antelope或Barracuda)。如果MySQL服务器创建或打开了一个有着更高级格式的表，此变量的值则会被设置为那个更高级的格式。作用范围为全局，可用于选项文件，属动态变量。

innodb_file_per_table={ON|OFF}

设定InnoDB表是否使用每表表空间数据文件(以.ibd结尾)分别存储每个表的数据和索引。如果使用了每表表空间数据文件，其将不再使用系统表空间(即共享表空间)。InnoDB表的某些特性，如压缩表等仅对每表表空间生效。作用范围为全局，可用于选项文件，属动态变量。

innodb_flush_log_at_trx_commit={0|1|2}

设定InnoDB同步日志缓冲区(log buffer)数据至日志文件中的方式，以及刷写日志文件至磁盘的方式。其可接受的值中，“0”表示将日志缓冲区每秒一次地写入日志文件，并同时将日志文件刷写至磁盘中，但事务提交时不会采取任何动作；“1”是默认值，表示在有事务提交时将日志缓冲区写入日志文件，并同时将日志文件刷写至磁盘；“2”表示每事务提交或每秒一次将日志缓冲区写入日志文件，但不会同时执行日志文件的刷写操作。当然，由于操作系统进程调度的原因，每秒一次的日志写入或刷写操作并不能得到100%的保证。

完全兼容ACID的场景需要将此变量值设置为1，由于要执行每事务的日志刷写操作，其会阻止I/O调用，直到写操作完成，故其会显著降低InnoDB每秒钟可以提交的事务数。设置为“2”可获得比“1”更好的性能，而且仅在操作系统崩溃时才会丢失最后一秒钟的数据，因此数据安全性也有着不错的表现。设置为“0”则有可能会导致事务最后一秒钟的数据丢失，于是整个事务的数据安全性将无法保证，但其通常有着最好的性能。为了在最大程序上保证复制的InnoDB事务持久性和一致性，应该设置变量innodb_flush_log_at_trx_commit=1以及设置变量sync_binlog=1。

然而需要注意的是，有些磁盘自身也有缓存，这可能会给事务操作带来额外的潜在风险。可以使用hdparm工具或供应商的自有工具等禁用磁盘自身的缓存。当然，高性能事务的最佳配置是把此变量的值设置为1，并且将日志文件放在有备用电池的写入缓存的RAID上。作用范围为全局，可用于选项文件，属动态变量。

innodb_flush_method={O_DSYNC|O_DIRECT}

设定InnoDB实际与文件系统进行交互的方式。除了写操作之外，它还可以影响InnoDB如何读取数据。设置innodb_flush_method变量的值为O_DSYNC时，InnoDB使用O_SYNC标志来打开和刷写日志文件，而使用fsync()来刷写数据文件。O_SYNC会使得所有的写入操作都是同步的，即只有在数据被写入磁盘之后才会返回，但不会在操作系统层面禁止缓存，因此，它不会避免双缓冲，并且不会直接写入磁盘。fsync()会同时刷数据和元数据(而fdatasync()只刷写数据)，它比fdatasync()产生更多的IO操作，而且操作系统会缓存一些数据在自己的缓存中(这将导致双缓冲)。如文件系统可以智能地处理I/O需求，双缓冲可能不是坏事儿，但如果MySQL设置了innodb_file_per_table变量的值为1，则会导致第个表空间文件都单独使用fsync()函数，其写入操作就不可能会被合并了。

设置innodb_flush_method变量的值为O_DIRECT时，InnoDB使用O_DIRECT标志打开数据文件，而使用fsync()刷写数据和日志文件。O_DIRECT标志会导致操作系统既不缓存数据，也不预读数据，它完全禁止了操作系统的缓存并且使所有的读写动作直接至存储设备，避免了双缓冲。然而，其不能禁止硬件层面(如RAID卡)的缓存和预读功能，而且启用硬件层面的缓存和预读功能也是保证InnoDB使用了O_DIRECT标志时仍能保持良好性能的惟一途径。

作用范围为全局，可用于选项文件，属非动态变量。

innodb_force_load_corrupted={ON|OFF}

设定InnoDB在启动时是否装载标记为“已损坏(corrupted)”的表。仅应该在troubleshooting的场景中启用该功能以修复无法访问的表，在troubleshooting任务完成后应该禁用此功能并重启MySQL服务。作用范围为全局，可用于选项文件，属非动态变量。

innodb_force_recovery={0|1|2|3|4|5|6}

设定InnoDB的故障恢复模式。InnoDB出现了“页面损坏(page corruption)”时，通常大部分数据仍然完好，于是可以通过SELECT...INTO OUTFILE命令备份出数据以降低损失程度。然而，某些“损坏”类的故障可能会导致SELECT * FROM tbl_name命令无法执行或InnoDB后台操作崩溃，甚至会导致InnoDB的前滚操作。这种情况下，就可以使用innodb_force_recovery变量强制InnoDB存储引擎在启动时不执行后台操作，以便能将数据备份出来。

innodb_force_recovery可接受的值中，“0”为默认值，表示执行正常启动，即不启用“强制修复”模式。而非零值中，某数值会包含比其小的所有数值的预防措施，然而其也较可能给B-tree索引及其它的数据结构带来更多的损坏。故此，在此变量值为非零值时，其会阻止用户使用INSERT、UPDATE或DELETE操作，但是会允许执行SELECT、CREATE TABLE或DROP TABLE类的操作。以下是关于其它非零值功能的说明：

1(SRV_FORCE_IGNORE_CORRUPT)：即使出现了页面损坏也照常运行MySQL服务，其会在SELECT * FROM tbl_name语句执行时尝试跳过损坏的索引记录和页面。

2(SRV_FORCE_NO_BACKGROUND)：禁止启动主线程(master thread)，其会在执行清写(purge)操作时防止出现崩溃(crash)。

3(SRV_FORCE_NO_TRX_UNDO)：在故障恢复(crash recovery)后不执行事务的回滚操作。

4(SRV_FORCE_NO_IBUF_MERGE)：禁止执行修改缓冲(insert buffer)合并操作。

5(SRV_FORCE_NO_UNDO_LOG_SCAN)：在启动数据库服务时不检查撤消日志(undo logs)，这会导致InnoDB将未完成的事务视为已提交。

6(SRV_FORCE_NO_LOG_REDO)：不执行重做日志(redo log)的前滚操作。此时，仅能执行不带WHERE、ORDER BY或其它子句的SELECT * FROM tbl_name操作，因为复杂查询在遇到损坏的数据结构时会中止并退出。

innodb_io_capacity=#

设定InnoDB后台任务(如从缓冲池刷写页面或合并修改缓冲中的数据等)可执行的I/O操作上限。其最小值为100，默认值为200，最大值取决于CPU架构。对于有着较大I/O负载的服务器来讲，应该为其指定更大的值以便能够更好更快的执行后台维护任务。然而，在实践中，此变量的值应该尽可能接近MySQL服务器每秒钟执行的I/O操作数量(即IOPS)，甚至于让其低至以不影响后台任务执行为目标的最低限度。因为，如果此值过高的话，数据会被频繁地从缓冲中移入移出，这会降低缓存池的在系统性能提升方面的效用。单个5400RPM或7200RPM磁盘仅能完成大约100个IOPS，因此，此种情况下应该将此变量值降低至100；而对于有着多块磁盘或更强性能的存储设备(如固态磁盘)的应用场景，可以按需提高此变量的值。作用范围为全局，可用于选项文件，属动态变量。

innodb_large_prefix={ON|OFF}

设定对于使用了DYNAMIC或COMPRESSED行格式的InnoDB表来说，是否能够使用大于767字节长度的索引前缀。然而，创建此种类型的表还需要设定innodb_file_format的值为barracuda，以及innodb_file_per_table的值为ture。同时，此设定对使用了REDUNDANT和COMPACT行格式的表的索引长度限定来说是不起作用的。作用范围为全局，可用于选项文件，属动态变量。

innodb_lock_wait_timeout={1 .. 1073741824}

设定InnoDB中某事务试图访问一个由其它InnoDB事务加锁的行时其最长的等待时间，单位为秒钟，默认值为50。在超时情况发生时，InnoDB会返回一个1205类型的错误信息，并对当前语句(非整个事务)执行回滚操作；如果需要在此种情况下对整个事务进行回滚，则需要在MySQL服务启动时使用--innodb_rollback_on_timeout选项。

对于OLTP系统或有着较多交互式应用的程序来说，应该降低此变量值以使得用户较快地获取到反馈信息，或使得系统较块地将此更新操作提交到队列中以便延后处理。对于批处理应用较多的场景来说，如数据仓库，应该增加此变量的值以等待其它较大的插入或更新操作完成。

此变量仅对InnoDB的行锁产生作用，MySQL的表锁并非在InnoDB中实现，所以此超时时长对表锁没有影响。而且，由于InnoDB会能立即探测到死锁的发生并会对其中的一修整务执行回滚操作，因此此超时时长也不应用于死锁。作用范围为全局或会话级别，可用于选项文件，属动态变量。

innodb_locks_unsafe_for_binlog={ON|OFF}

设定InnnoDB是否在搜索和索引扫描中使用间隙锁(gap locking)。InnoDB使用行级锁(row-level locking)，通常情况下，InnoDB在搜索或扫描索引的行锁机制中使用“下一键锁定(next-key locking)”算法来锁定某索引记录及其前部的间隙(gap)，以阻塞其它用户紧跟在该索引记录之前插入其它索引记录。站在这个角度来说，行级锁也叫索引记录锁(index-record lock)。

默认情况下，此变量的值为OFF，意为禁止使用非安全锁，也即启用间隙锁功能。将其设定为ON表示禁止锁定索引记录前的间隙，也即禁用间隙锁，InnoDB仅使用索引记录锁(index-record lock)进行索引搜索或扫描，不过，这并不禁止InnoDB在执行外键约束检查或重复键检查时使用间隙锁。

启用innodb_locks_unsafe_for_binlog的效果类似于将MySQL的事务隔离级别设定为READ-COMMITTED，但二者并不完全等同：innodb_locks_unsafe_for_binlog是全局级别的设定且只能在服务启动时设定，而事务隔离级别可全局设定并由会话级别继承，然而会话级别也以按需在运行时对其进行调整。类似READ-COMMITTED事务隔离级别，启用innodb_locks_unsafe_for_binlog也会带来“幻影问题(phantom problem)”，但除此之外，它还能带来如下特性：

(1)对UPDATE或DELETE语句来说，InnoDB仅锁定需要更新或删除的行，对不能够被WHERE条件匹配的行施加的锁会在条件检查后予以释放。这可以有效地降低死锁出现的概率；

(2)执行UPDATE语句时，如果某行已经被其它语句锁定，InnoDB会启动一个“半一致性(semi-consistent)”读操作从MySQL最近一次提交版本中获得此行，并以之判定其是否能够并当前UPDATE的WHERE条件所匹配。如果能够匹配，MySQL会再次对其进行锁定，而如果仍有其它锁存在，则需要先等待它们退出。

innodb_log_buffer_size={262144 .. 4294967295}

设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_file_size={108576 .. 4294967295}

设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_files_in_group={2 .. 100}

设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。