MySQL数据库性能优化（1）

1、MySQL概述

从本⽂开始我们将讨论建⽴在块存储⽅案之上的关系型数据库的性能优化⽅案和集群⽅案。关系型数据库的选型将以创业公司、互联⽹⾏业使⽤最⼴泛的MySQL数据为⽬标，但是MySQL的安装过程和基本使⽤⽅法等知识并不在我们讨论的范围内。后续⼏篇⽂章我们⾸先讨论影响单个MySQL节点性能的主要因素，然后介绍MySQL读写分离、数据表横纵拆分的原理和技术⽅案。

MySQL数据库⽬前已被Oracle收购，并发展处多个版本。⽬前使⽤最⼴泛且免费的MySQL版本是MySQL Community（社区版），另外还有三个付费的MySQL版本MySQL Standard（MySQL标准版）、MySQL Enterprise（MySQL企业版）、MySQL Cluster（MySQL集群版），这三个版本是按照CPU内核进⾏费⽤计算，并且价格由低到⾼。最后Oracle还提供了两个微型的MySQL版本：MySQL Classic（经典

版），这个版本的MySQL只提供了MyISAM存储引擎但是安装快速，占⽤空间较少；MySQL Embedded（嵌⼊式版本），这个版本的竞争软件是SQLite。虽然社区版本是免费的并且这个版本提供的功能也没有企业级版本丰富，同样的硬件条件下单节点性能也没有企业基本版优秀。但是我们可以借助社区版本⾃⾝提供的功能和⼀些第三⽅软件配合使⽤，搭建起相对廉价且性能不俗的MySQL数据库集群。

2、数据库引擎的选择

MySQL数据库中最重要的⼀个概念就是数据库引擎，不同的数据库引擎的⼯作原理存在很⼤差异最终造成MySQL数据库服务的性能差异。例如如果数据库引擎需要⽀持事务，就必须满⾜事务的基本特性——AICD特性（AICD：原⼦性、隔离性、⼀致性和永久性。属于基础知识所以不在这⾥赘述），那么⾃然就需要⼀定处理机制来实现这些特性。这样做的现实效果就是导致写⼊同样数据量的情况下，⽀持事务的数据库引擎⽐不⽀持事务的数据库引擎耗费更多的时间。这⾥我们⾸先为读者列举MySQL数据库社区版中⽀持的数据库引擎（部分）：

MEMORY：MEMORY存储引擎将表的数据完全存放在内存中。在MySQL数据库的历史版本中和该数据库引擎类似的其它引擎是

HEAP，后者曾是MySQL数据库中访问速度最快的数据库引擎。但由于这两种数据库引擎完全⼯作在内存中，所以如果MySQL或者服务器重新启动，数据库引擎中保存的数据将会丢失。

BLACKHOLE：中⽂名“⿊洞”，使⽤BLACKHOLE数据库引擎的数据表不存储任何数据，只根据数据库操作过程记录⼆进制⽇志。它的主要作⽤是作为MySQL主从复制的中继器，并且可以在其上⾯添加业务过滤机制。

MyISAM：MyISAM数据库引擎是MySQL数据库默认的数据库引擎。MyISAM使⽤⼀种表格锁定的机制，来优化多个并发的读写操作（实际上就是使⽤的⼀种避免数据脏读的机制）。但是这种机制对存储空间的使⽤有⼀定的浪费。MyISAM还有⼀些有⽤的扩展，例如⽤来修复数据库⽂件的MYISAMCHK⼯具和⽤来恢复浪费空间的MYISAMPACK⼯具。本⽂所介绍的MySQL数据库相关技术将不涉及到这种数据库引擎。

InnoDB：InnoDB数据库引擎是在各种版本的MySQL数据库中使⽤最⼴泛的⼀种数据库引擎，本⽂后续的介绍中如果没有特别说明都默认是在说InnoDB数据库引擎。InnoDB数据库引擎使⽤⽇志机制提供事务的⽀持。

3、基本I/O性能

要了解MySQL数据库中的性能问题，就⾸先要搞清楚在客户端向MySQL数据库提交⼀个事务操作时后者到底做了些什么事情，以及主要是怎么做的。本节所描述的⼯作过程主要围绕InnoDB数据库引擎进⾏：

上图中笔者只画出了InnoDB数据库引擎在insert/update⼀个事务的过程中所涉及的重要⼯作区域，InnoDB的实际⼯作细节要⽐上图所⽰的步骤复杂得多。上⽂已经说到InnoDB数据库引擎是⼀个⽀持事务的数据库引擎，那么如何解决异常崩溃情况下的数据⼀致性问题就是它的设计中最重要的任务之⼀。InnoDB数据库引擎采⽤⽇志来解决这个问题，请注意这⾥说的InnoDB数据库引擎⽇志，并不是MySQL数据库全局的⼆进制⽇志。InnoDB数据库引擎⽇志还有另外⼀个名字：重做⽇志（redo log），这是因为这部分⽇志主要的作⽤就是在数据库异常崩溃并重启后进⾏InnoDB引擎中数据的恢复。

为了提⾼MySQL数据库的性能，InnoDB数据库引擎的数据操作过程基本上都在内存中完成，然后通过⼀定的策略（后⽂会详细介绍）将InnoDB Log Buffer内存区域中的⽇志数据同步到磁盘上的InnoDB File Log Group区域。InnoDB File Log Group区域主要⽤于存储InnoDB数据库引擎的⽇志⽂件，它由多个⼤⼩相同的⽇志⽂件构成并且这些⽂件都采⽤顺序读写。innodb_log_file_size参数将决定每个⽂件的⼤⼩，⽽innodb_log_files_in_group参数将决定整个⽇志组中有多少个⽇志⽂件。

当MySQL数据库完成初始化过程后这些⽇志⽂件将会按照参数的设置值，在磁盘上预占⼀个连续的磁盘空间。这样做的现象就是虽然数据库中还没有任何数据，但是⽇志⽂件的总⼤⼩就已经是 innodb_log_file_size * innodb_log_files_in_group所得到的数值了：

# InnoDB数据库引擎 ⽇志⽂件⽰例....

total 1.0G

-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile0-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile1....

这样做的⽬的是保证了后续同步⽇志数据的操作都是顺序写，⽽不是随机写。当⽇志数据写到最后⼀个⽂件的末尾时，下⼀条⽇志数据⼜会重新从第⼀个⽇志⽂件的开始位置进⾏写⼊。

3-1、I/O 性能问题的产⽣

InnoDB Log Buffer内存空间中的四个标识指针是InnoDB数据库引擎⽇志处理部分最重要元素，它们分别是：Log sequence、Log flushed、Pages flushed和Last checkpoint，这四个标识涉及到InnoDB在崩溃重启时不同的数据恢复策略，以及I/O性能优化中的关键原理。这四个标识实际上是四个数值它们共享⼀个数值池（名叫LSN，⽇志序列号，其总长度是64位⽆符号整数），代表当前InnoDB对事务操作的处理状态。并且它们数值有以下特点：

Log sequence >= Log flushed >= Pages flushed >= Last checkpoint

1. 每当InnoDB接收到⼀个完整数据库insert/update请求事务后，就会创建⼀个新的LSN。新的LSN = 旧的LSN + 本次写⼊的⽇志⼤⼩。这条最新的⽇志将会使⽤Log sequence进⾏标记，并且如果出现接收到多个事务请求的情况下，InnoDB也会按照⼀个既定的顺序对这些⽇志进⾏排序，然后依次⽣成新的LSN。这⼀步骤是完全在内存中进⾏的，所以不存在I/O性能问题。2. 接下来Mysql就会开始执⾏这个事务中的各种细节操作。InnoDB数据库引擎专门有⼀个InnoDB Buffer Pool内存空间⽤来进⾏数据更改或数据新增。其⼤⼩由innodb_buffer_pool_size参数控制，其数据来源于innoDB data file并且以Page的形式存在于InnoDB BufferPool中。当⽇志中有insert操作时则⽣成新的Page；当⽇志中有update操作时，InnoDB会检查该数据是否已经存在于Page Cache中，如果存在（命中）就直接更新这个Page Cache中的内容，如果不存在（未命中）就会继续从InnoDB data file中读取原始数据到InnoDB Buffer Pool中然后再更新。这⾥要注意⼏个问题：还记得我们在讨论磁盘设备时提到的“预读”技术吗？这个技术的思路是，如果某个区域的数据被读取和使⽤那么在不久的将来与其相邻的区域也将会被读取和使⽤。所以为了提⾼读取效率，磁盘控制芯⽚会将磁盘上⽬标块和其相邻的若⼲块⼀起读取出来。InnoDB数据库引擎同样使⽤了这个思路，即读取某个Page时将会同时读取临近的Page，但是是否能起到提到I/O性能的⽬的还是要分不同的运⾏环境（后⽂进⾏说明）。

当InnoDB完成InnoDB Buffer Pool中的数据操作后，更改后数据所涉及到的Page将和此时存储在磁盘上的数据不⼀样，这样的Page称为脏页。如何控制脏页将是保持数据⼀致性的关键，InnoDB数据库引擎的做法是⾸先向InnoDB File Log Group⽇志⽂件中写⼊这个事务的⽇志信息。这⾥的写⼊策略由三种，通过innodb_flush_log_at_trx_commit参数可以进⾏控制：

innodb_flush_log_at_trx_commit = 0时，InnoDB将按照1秒钟为单位向磁盘写⼊这个阶段所有已完成的事务⽇志信息。这⾥的写⼊成功并不是说写⼊到Linux操作系统的Page Cache中就算成功，⽽是需要等待操作系统真正写到了物理磁盘上的通知（具体请参见之前讲解⽂件系统的⽂章）。这意味着即使InnoDB Buffer Pool中的数据操作是成功的，但是⼀旦数据库系统异常崩溃，那么业务系统将会丢失前1秒内写⼊的数据：因为没有磁盘介质上的⽇志就⽆法在异常重启后恢复数据信息。

innodb_flush_log_at_trx_commit = 1时，InnoDB按照完成⼀个⽇志操作就向磁盘写⼊事务⽇志信息的⽅式来⼯作（执⾏⼀个事务就写⼊⼀个事务⽇志）。同样，这⾥的写⼊成功同样是要等待操作系统返回真正写⼊了物理磁盘的通知。

innodb_flush_log_at_trx_commit = 2时，InnoDB按照完成⼀个⽇志操作就向磁盘写⼊⽇志信息的⽅式来⼯作。但是，这种⼯作模式下InnoDB不会等待操作系统返回物理磁盘上写⼊成功的通知，就会继续⼯作。实际上这个时候，数据⼀般还存在于Linux操作系统的cache memory区块中，所以这种模式下最好使⽤带有⽇志功能的⽂件系统，并且确认开启了⽂件系统的⽇志功能。InnoDB数据库引擎在这⼀步骤的最后⼀个动作是更改Log flushed标识指针值为当前最后完成刷新动作的事务⽇志LSN值。实际上执⾏完这个步骤，⼀个事务处理操作才算真正成功。

3. 但是涉及数据变动的脏页还没有更新到磁盘上，为什么事物的处理就可以算作成功了呢？这是因为即使这个时候数据库异常崩溃了，就凭存储在磁盘上的完整⽇志我们也可以重做数据。好吧，最好还是要同步脏页是吧。在第三个步骤InnoDB数据库引擎将会把最近Log flush时所涉及到的脏页（最旧脏页）更新到磁盘上。当完成脏页向磁盘的同步操作后，InnoDB数据库引擎将会更新Pages

flushed标识点的LSN值，表⽰这个LSN值所代表的事务（以及之前的事务）都已经完成了内存和磁盘上的数据同步动作。当InnoDB数据库引擎进⾏脏页更新时，将会按照⼀定的周期策略批量提交脏页到Linux操作系统的cache memory区块中。每⼀次批量提交的脏页数量由innodb_io_capacity参数决定。不同版本InnoDB数据库引擎⽀持的pages flush策略是不⼀样的，但最基本的规则没有变化，就是周期性刷新。从Mysql version 5.6开始InnoDB数据库引擎向管理者提供了⼀个innodb_adaptive_flushing参数，当这个参数设置为“no”时InnoDB数据库引擎将检测脏页在InnoDB Buffer Pool中的⽐例，以及即时I/O状态等情况来决定pages flush的周期。如果脏页在InnoDB Buffer Pool中的⽐例达到了由innodb_max_dirty_pages_pct（默认为75）参数设置的百分⽐阀值，这时InnoDB数据库引擎将按照innodb_io_capacity_max（默认值2000）参数设置的数量将这写脏页⼀起同步到磁盘。

当磁盘I/O性能不⾜且innodb_io_capacity设置过⼤时，会导致产⽣较长的I/O队列造成I/O请求阻塞，⼀旦累积到

innodb_max_dirty_pages_pct阀值，⼜会产⽣更长的I/O阻塞队列；反之则会造成物理服务器的I/O性能没有被去完全使⽤。所以innodb_io_capacity的设置⾮常重要，特别是当读者在硬件层采⽤SSD固态硬盘和⾼速磁盘阵列时。

4. Checkpoint是InnoDB数据库引擎中最后⼀个标识点。这个标识点代表着当数据库异常崩溃重启后，⼩于或者等于这个标识点LSN值的所有⽇志信息、数据信息都⽆需进⾏重做检查。⽽LSN值⼤于Checkpoint的所有事务都需要重做，只是重做策略将视LSN值所在标识区域的不同⽽不同：

当代表事务的LSN数值在Log sequence——Log flushed范围内时（不包括Log flushed），说明在数据库崩溃时内存中的事务并没有处理完，这部分事务操作将在恢复时被丢弃。

当代表事务的LSN数值在Log flushed——Pages flushed范围内时（不包括Pages flushed），说明数据库崩溃时磁盘上已经拥有这些事务完整的⽇志记录。InnoDB数据库引擎将读取这些⽇志数据，并继续执⾏下去，直到代表这些事务的LSN值被标记为

Checkpoint（或者⼩于Checkpoint标识的LSN值）。这⾥要注意，在数据库崩溃时处于这个范围内的某些事务可能已经完成了⼀部分的数据同步动作，但是肯定是不完整的。所以即使是这样的事务也要重新进⾏磁盘同步，才能保证数据的⼀致性。实际上在MySQL version 5.5的早期版本，InnoDB数据库引擎中只有三个标识：Log sequence、Log flushed和Checkpoint。也就是说当脏页成功同步到磁盘后，就会直接更新Checkpoint标识的LSN值。后续版本的MySQL数据库增加了Pages flushed标识点，这样做的⽬的是保证Checkpoint和Pages flush的更新可以拥有独⽴的周期，从⽽降低其带来的性能消耗。

3-2、I/O 性能问题要点

1. Log flush和Pages flush

从上⼀⼩节的描述中，我们⼤致知道了在InnoDB数据库引擎中⼀个事务的处理过程中有两个步骤存在I/O操作：Log flush和Pagesflush。

Log flush的过程是将完成的事务⽇志写⼊到⽇志⽂件中，由于InnoDB数据库引擎中⽇志⽂件的组织⽅式，所以Log flush中对磁盘的操作是顺序写。并且技术团队还可以通过innodb_flush_log_at_trx_commit参数来调整InnoDB Log Buffer到InnoDB File Log Group的同步策略，这有助于进⼀步提⾼Log flush性能。这也就是为什么实际环境中往往将MySQL数据库（⼤部分关系型数据库都适⽤）直接建⽴在块存储⽅案上，⽽不是建⽴在⽂件存储⽅案或者对象存储⽅案上的原因。

Pages flush的过程就没有那么幸运了，InnoDB数据库引擎不可能事先知道数据库会存放哪些数据，也不可能知道下次的update操作和select操作的⽬标数据存放在哪个区域。所以InnoDB数据库引擎针对Page的读取和更新都只能基于随机读写。那么Pages flush过程就需要在如何保持I/O性能这问题上想更多的解决办法。

例如在读取Page时，采⽤“预读”思路将⽬标Page所临近的Page⼀起读取出来；在写⼊Page时将⽬标Page所临近的Page⼀起写⼊（“临近写”）。“预读”策略可以通过innodb_read_ahead_threshold参数进⾏设置，并通过read thread完成，另外

innodb_flush_neighors参数可以控制是否开启“临近写”策略。总的来说“预读”/“临近写”在默认情况下都是开启的，但“预读”/“临近写”思路本⾝就需要⼀定的准确性，低命中率的“预读”反⽽会降低InnoDB的I/O性能。还有⼀种“随机预读”，它在MySQL version5.6版本中默认就是关闭的，并且在随后的版本中将会慢慢废除，所以这⾥就不再介绍了。

例如将向磁盘提交Page的动作设计为周期性且批量进⾏，并且始终保持InnoDB Buffer Pool内存区域的脏页（Dirty Page）在⼀定的⽐例，这些策略主要由innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_io_capacity_max等参数控制。例如通过调整Innodb_Buffer_Pool_size参数获得更⼤的InnoDB Buffer Pool内存区域，存储更多的Page。实际上Innodb BufferPool区域不仅包括我们已经介绍的Page Cache数据部分，还包括其它的数据区块。例如为了快速定位B+树索引的Hash Index结构。调整Innodb_Buffer_Pool_size参数将会使这些数据区域都享受到内存容量带来的优势——⾄少不会频繁地发⽣内容空间的强制清理。

2. 基础硬件条件

按照本专题之前⽂章介绍的块存储⽅案来看（《》、《》），如果存储MySQL数据的底层硬件介质就只是⼀块机械磁盘，那么⽆论怎样优化MySQL的其它各参数，MySQL实际对磁盘的顺序I/O速度理论上也只有100MB/S左右。这还是不计算硬件层校验、不计算不同⽂件系统处理耗时等等时间，所以实际I/O速度只会更慢。另外如果采⽤单块机械磁盘存储MySQL的数据，那么磁盘空间的扩容也是⼀个问题。⽬前市场上能买到的容量最⼤的单块机械磁盘，它的存储空间也只有10TB。当这部分容量使⽤完后想要进⾏扩容就基本上是就⼀个不可能完成的任务了。最后这种存储⽅式还有安全性的问题，单块机械磁盘在持续的⾼I/O环境下是很容易损坏的，只要是有⼀定资⾦⽀持的公司，机械磁盘本⾝就看做耗材。

所以即使是初创型公司的线上⽣产环境，本⽂也不推荐使⽤单块机械磁盘存储任何需要持久保存的业务数据。如果是因为资⾦问题，则推荐使⽤⼀些云服务商提供的现成PaaS环境，原因是这些PaaS

环境本⾝就⽀持数据恢复功能，且利⽤云服务商已经建设好的价格不菲的硬件/软件环境，MySQL数据库的I/O性能和计算性能暂时还不会成为业务系统的瓶颈。

3-3、突破I/O性能

为了解决上⼀节中提到的I/O性能问题，本⽂这⾥基于之前介绍的块存储⽅案的知识，列出这个问题的⼏种解决⽅案。除了根据I/O吞吐量要求对MySQL数据库特别是InnoDB引擎的配置参数进⾏更改以外，本⽂提到的硬件层解决⽅法所需要花费的资⾦和能够得到的I/O性能和扩展能⼒基本上成正⽐。

3-3-1、对MySQL中的I/O相关参数进⾏调整

上⼀节我们已经对InnoDB数据库引擎（以下简称InnoDB引擎）进⾏事务操作时的I/O过程进⾏了简单说明，主要介绍了Log flush和Pagesflush两个过程。如果我们需要挖掘正式⽣产环境上MySQL数据库服务的性能潜⼒，那么对MySQL数据库服务中的默认参数进⾏更改就是必须要做的事情。在进⾏配置修改之前我们先来看看如何查看当前MySQL数据库特别是InnoDB引擎的⼯作状态：

# 通过执⾏以下命令，我们可以查看当前InnoDB引擎的⼯作状态show engine innodb status;

执⾏后可以得到类似如下的执⾏结果（已省去⼀部分与本⽂没有涉及到的知识点所相关的状态描述信息）：

......

# master thread是InnoDB引擎中优先级最⾼的线程

# 这个线程存在的主要作⽤是控制InnoDB中各种I/O操作的策略，驱动其它read/write thread

# 并根据当前的InnoDB的InnoDB Log Buffer、InnoDB Buffer Pool区域状态和配置参数决定处理逻辑-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 79085 srv_idlesrv_master_thread log flush and writes: 79085......

# 这些是负责进⾏磁盘读写的I/O线程，⽇志和数据Page的读写操作最终都是靠它们完成# 它们的数量可以通过innodb_write_io_threads参数和innodb_read_io_threads参数进⾏设置--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)......

I/O thread 8 state: waiting for completed aio requests (read thread)I/O thread 9 state: waiting for completed aio requests (read thread)I/O thread 10 state: waiting for completed aio requests (write thread)......

I/O thread 13 state: waiting for completed aio requests (write thread)

Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0524 OS file reads, 5 OS file writes, 5 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s......

# 这些信息反馈了InnoDB引擎中的⽇志标识---LOG---# 当前⽣成的最⼤⽇志LSN值Log sequence number 9902833

# 当前已完成⽇志信息向磁盘同步的最⼤⽇志LSN值Log flushed up to 9902833

# 当前已完成数据信息向磁盘同步的最⼤⽇志LSN值Pages flushed up to 9902833

# 当前已检查点同步的最⼤⽇志LSN值。⼩于和等于这是LSN值的⽇志在异常重启后都⽆需进⾏“重做”。Last checkpoint at 9902833

# 当前InnoDB中待写的⽇志操作、待写的检查点操作0 pending log writes, 0 pending chkp writes8 log i/o's done, 0.00 log i/o's/second

# 这些信息反馈了InnoDB引擎中Buffer Pool内存空间的状态----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 4395630592; in additional pool allocated 0Dictionary memory allocated 99235# 当前Buffer Pool的⼤⼩Buffer pool size 262136# 当前Buffer Pool的可⽤⼤⼩Free buffers 261619# Page数量

Database pages 506Old database pages 0Modified db pages 0Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 506, created 0, written 1

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

# 状态信息的这⾏反馈了Page的预读信息，以及预读信息的未命中剔除情况

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 506, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]......

对于上⼀⼩节提到的重要参数信息，可以采⽤如下的设定规则：

innodb_log_file_size：单个⽇志⽂件的⼤⼩不宜过⼩，例如设置为500MB。由于InnoBD引擎对⽇志⽂件采⽤顺序写的操作⽅式，所以不必担⼼⽇志⽂件的操作消耗⽐数据⽂件操作更多的性能。

innodb_log_files_in_group：该参数控制了⽂件组中⽇志⽂件的总数。设置为2-5的范围都不会有太⼤影响。更重要的是读者应该清楚innodb_log_file_size * innodb_log_files_in_group就是InnoDB引擎在磁盘上可⽤⽇志空间的总⼤⼩。

innodb_log_buffer_size：这个参数决定了InnoDB引擎可使⽤的⽇志内存空间。只要没有类似插⼊blob类型数据的操作（也不建议有这样的操作），这个内存空间都不需要设置得太⼤。5MB-10MB是⼀个推荐的设置值，不过这个参数还是要和innodb_flush_log_at_trx_commit参数配合使⽤。

innodb_flush_log_at_trx_commit：该参数可以说是InnoDB引擎⽇志操作策略部分最重要的设置参数之⼀。在上⼀篇⽂章（《》）的3-1节中，我们已经介绍过该参数的三个值和它们代表的策略意义。如果您将innodb_flush_log_at_trx_commit设置为0，代表着InnoDB引擎将会按照1秒钟的周期进⾏⽇志从内存到磁盘的同步。这时innodb_log_buffer_size的值就不能过⼩，因为在⼀个同步周期内如果待刷新的⽇志超过了innodb_log_buffer_size设置的⼤⼩，InnoDB就会强制执⾏同步操作。如果您的Linux操作系统使⽤的是带有⽇志功能的⽂件系统并且⽇志功能是开启的，那么还是建议将该参数设置为2。

innodb_buffer_pool_size：这个参数调整分配给InnoDB引擎使⽤的可⽤数据内存区域的⼤⼩。实际上这个数据区域不⽌包括了本⽂中⼀直强调的Page Cache区域，它还有很多数据区域。例如InnoDB中⽤来进⾏查询排序的Sort Buffer区域。建议的设置⼤⼩是MySQL数据库服务所在物理服务器上总内存的60%——80%（⽂件系统的Cache Memory/Buffer Memory等其它程序还要使⽤）。8GB的物理服务器可设置6GB的InnoDB Buffer Pool可⽤内存区域。注意，当MySQL数据库启动时并不是⽴刻就会占据所有数据区域。

innodb_buffer_pool_instances：本⼩节和本⽂中多处位置都提及到innodb_buffer_pool_size参数以及它的含义。这个参数值在⽣产环境下⼀般设置得都⽐较⼤（例如4GB、8GB、12GB、24GB等等）。但是由于脏数据刷盘的周期性，在I/O性能强劲的物理机器上可能就会存在I/O间歇性低⾕。为了将I/O操作⼀直保持在⼀定的⼯作效能上，也为了发挥CPU的计算性能，InnoDB引擎允许将innodb_buffer_pool划分为多个独⽴的运⾏实例，当InnoDB需要读取新的Page时，它们会按照⼀定的算法被分配到某个独⽴运⾏的buffer pool instance中。这些bufferpool instance有各⾃独⽴的LRU算法队列、独⽴计算脏页⽐例，并且独⽴进⾏脏页刷新。innodb_buffer_pool_instances参数在具有较⾼I/O性能并且具有较⼤innodb_buffer_pool_size设定值的物理设备上能够对I/O性能产⽣⾮常明显的影响。如果您采⽤的是固态磁盘或者磁盘阵列作为MySQL服务器的硬件层存储介质，那么建议1-2GB的innodb_buffer_pool就分配⼀个独⽴的运⾏实例（这样算下来12GB的buffer pool可以设置6-12个运⾏实例，注意进⾏⽣产环境下的实测调整哦^-^）。但如果您只是使⽤的机械磁盘⼜或者innodb_buffer_pool_size的值并不⼤，那么将innodb_buffer_pool_instances参数设置为1就可以了。

innodb_io_capacity：该参数控制着InnoDB Buffer Pool数据内存区域进⾏磁盘同步时每次可以同步的脏页数量。在磁盘I/O性能不⾜时，如果innodb_io_capacity参数值过⼤就会造成I/O阻塞，并且造成InnoDB引擎性能较⼤的降低。但如果您使⽤的是固态硬盘或者RAID磁盘阵列,就可以将innodb_io_capacity参数默认的200设置⼤⼀些，例如设置成500——800）。

innodb_adaptive_flushing：该参数⼀定要打开，保证脏页的同步周期由InnoDB引擎根据实时I/O性能情况⾃⾏控制同步频率（实际上只有两种频率：1秒或者10秒）。

innodb_max_dirty_pages_pct：该参数默认为75，⼀般情况下⽆需更改。另外innodb_io_capacity_max参数表⽰当脏页数量在InnoDB

Buffer Pool内存中的⽐例超过了innodb_max_dirty_pages_pct参数设置的上限后，就按照innodb_io_capacity_max设置的脏页数量强制进⾏脏页的刷新（建议采⽤默认值即可）。但是设想⼀下这个问题：什么情况下最可能使脏页在内存中的占⽐超过上限呢？当然是InnoDB引擎的事务不断快速执⾏，并且I/O性能⼜不⾜以快速完成同步。这时InnoDB引擎将停⽌事务的执⾏，并且进⾏强制刷新。所以，当问题真正发⽣时innodb_io_capacity_max参数设置得再⼤也不可能解决I/O拥堵的问题，反⽽可能使问题更严重。

3-3-2、对MySQL中的其它参数进⾏调整

innodb_page_size：该参数决定了InnoDB引擎中每⼀页的⼤⼩。每⼀个page包含多条row数据，更⼤的page size意味着内存中存储的每页信息有更多的数据条数。由于⽂件系统和底层硬件设置的结构，所以该值都为4KB的整数倍（默认值为16KB，可选值为4KB、8KB、16KB）。注意如果您需要更改这个参数值，那么就必须在MySQL数据库初始化启动时，就加⼊到my.cnf配置⽂件中。否则⼀旦创建了⽤户数据表，再对这个参数进⾏修改，MySQL数据库就会报错。

innodb_read_io_threads：该参数设置InnoDB数据库中的负责从磁盘上读取数据的线程数量，另外这些线程还负责在预读选项开启时承担起预读的⼯作任务。innodb_read_io_threads的建议值为CPU的内核数量。

innodb_write_io_threads：该参数设置InnoDB数据库中负责将脏页同步到磁盘上的线程数量。innodb_write_io_threads的建议值为CPU的内核数量。

innodb_read_ahead_threshold：该参数表⽰InnoDB引擎中的顺序预读阀值。在buffer pool中的page也有⼀个组织结构：64个page组成⼀个extent结构。当InnoDB发现在⼀个extent结构中**已经连续读取**N个page，那么InnoDB会接着将另外64 - N个后续的page读⼊到buffer pool中。顺序预读在“连续读”性能较⾼的硬件设备上，对性能的影响⾮常⼩。所以如果读者使⽤了I/O性能⽐较强劲的固态磁盘环境或者磁盘阵列环境，则建议直接关闭该功能（设置为0即可）。

innodb_random_read_ahead：该参数表⽰是否开启随机预读，默认是关闭的。

innodb_flush_neighbors：既然InnoDB引擎提供Page的预读功能，当然就提供预写功能。该参数表⽰当Buffer Pool中的脏页被同步到磁盘时，是否⼀起刷新和这个脏页临近的页信息。该参数在I/O性能⽐较强劲的固态磁盘环境或者磁盘阵列环境下，对性能提升并不明显。所以建议在这样的情况下直接关闭这个功能（设置为0即可）。

sort_buffer_size：后⽂介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有对结果进⾏排序要求的查询性能影响⾮常⼤。

join_buffer_size：后⽂介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有各种join连接要求的查询性能影响⾮常⼤。

binlog_cache_size：在MySQL数据库中处理InnoDB层存在“重做⽇志”以外，在数据库管理层还有⼀个独⽴⼯作的⼆进制⽇志模块。这个⽇志模块的⼯作⽅式和“重做⽇志”的⼯作⽅式相似，它们采⽤的办法都是在内存中进⾏⽇志数据变更，然后再按照⼀定的策略周期性/直接同步到磁盘上。binlog_cache_size参数设置的就是可供⼆进制⽇志在内存中进⾏暂存的空间⼤⼩。需要注意的是：

binlog_cache_size和innodb_buffer_pool_size不同的是，前者的⼤⼩以MySQL数据库的客户端连接为单位。也就是说MySQL数据库会为两个独⽴的数据库客户端连接分别分配独⽴运⾏的binlog cache空间。正式环境的数据库中为每⼀个数据库连接设置的binlogcache空间不需要太⼤，当然这还要考虑实际的客户端请求频度和数据类型，还要考虑下⾯将介绍的sync_binlog参数设定。该参数建议的⼏个设置值为：32768（32KB为默认值，没有特别的要求可以保留该设置）、65536（64KB）、131072（128KB）、262144（KB）、524288（512KB）、1048576（1MB）以内。

sync_binlog：在MySQL数据库中除了InnoDB的“重做⽇志”需要同步以外，⼆进制⽇志也需要进⾏同步。这个参数是指MySQL数据库在内存中进⾏X次⼆进制⽇志操作后，就将内存中的⼆进制⽇志同步到磁盘中。

3-3-3、调整后的参数情况

以下是⼀组可以在配置有固态硬盘和磁盘阵列的正式MySQL数据库环境下使⽤的配置项参考，主要是为读者总结InnoDB引擎中和I/O性能相关的重要参数（只和I/O性能有关，因为后续的⽂章中还会介绍其他参数）。读者在进⾏参数配置是还是需要按照⾃⼰团队的⽣产环境情况，对配置项进⾏调整（这些参数信息都在MySQL数据库的my.cnf主配置⽂件中进⾏设置）：

# 设置单个⽇志⽂件的⼤⼩为500MBinnodb_log_file_size = 524288000# 设置⽇志⽂件组中有两个⽇志⽂件innodb_log_files_in_group = 2# 设置⽇志内存区域为10MB

innodb_log_buffer_size = 10485760# 设置⽇志数据同步策略为“2”

innodb_flush_log_at_trx_commit = 2# 设置buffer pool的⼤⼩为8GBinnodb_buffer_pool_size = 8G

# 设置正常情况下每⼀次脏页到磁盘的同步数量为800个

# （当然读者要确定磁盘I/O性能够⽤，否则改⼤这个值有害⽆益）innodb_io_capacity = 800

# 打开InnoDB提供的⾃监控频率innodb_adaptive_flushing = on# 已经介绍过三次了，不再赘述innodb_max_dirty_pages_pct = 75innodb_io_capacity_max = 2000

# 设置InnoDB的buffer pool区域⼀共有8个独⽴运⾏的实例innodb_buffer_pool_instances = 8

# 设置每⼀个数据页“page”的⼤⼩为16KB。为4KB的整数倍innodb_page_size = 16384

# 设置每次⼆进制⽇志操作都提交到⽂件系统的Cache中sync_binlog = 0

# 也可设置⼆进制⽇志在内存区域每操作1000次后，就进⾏磁盘同步#sync_binlog = 1000# 关闭顺序预读

innodb_read_ahead_threshold = 0# 关闭随机读

innodb_random_read_ahead = off# 关闭临近写

innodb_flush_neighbors = 0

配置完成后可以通过以下命令查看当前MySQL数据库和InnoDB引擎中相关的配置参数（为节约篇幅，已省去⼀部分查询结果）：

# 查询配置参数

show variables like 'innodb%';# 或者查询全局⼯作状态也⾏

# show global status like 'innodb%';

......

innodb_adaptive_flushing ONinnodb_adaptive_flushing_lwm 10innodb_adaptive_hash_index ON

innodb_adaptive_max_sleep_delay 150000innodb_additional_mem_pool_size 8388608innodb_api_disable_rowlock OFFinnodb_api_enable_binlog OFFinnodb_api_enable_mdl OFFinnodb_buffer_pool_instances 8

innodb_buffer_pool_size 8589934592innodb_disable_sort_file_cache OFFinnodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_per_table ONinnodb_flush_log_at_timeout 1innodb_flush_log_at_trx_commit 2 innodb_flush_neighbors 0innodb_flushing_avg_loops 30innodb_io_capacity 800innodb_io_capacity_max 2000innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFF

innodb_log_buffer_size 10485760innodb_log_compressed_pages ONinnodb_log_file_size 524288000innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_lru_scan_depth 1024innodb_max_dirty_pages_pct 75innodb_max_dirty_pages_pct_lwm 0innodb_mirrored_log_groups 1

innodb_online_alter_log_max_size 134217728innodb_open_files 2000innodb_page_size 16384innodb_random_read_ahead OFFinnodb_read_ahead_threshold 0innodb_read_io_threads 8innodb_write_io_threads 4

innodb_sort_buffer_size 1048576innodb_table_locks ONinnodb_use_native_aio ONinnodb_use_sys_malloc ONinnodb_version 5.6.22......

3-3-4、提供更优异的硬件⽅案

使⽤磁盘阵列替代单块磁盘

这是最基本的硬件层改造⽅式，⽬前⼤多数⼚商提供的PC Server基本上都集成了RAID控制器。所以这样做⼀般不需要额外增加购买硬件设备的费⽤。在MySQL官⽹上并没有明确推荐使⽤哪⼀种磁盘阵列模式，但是从搭建磁盘阵列⽀持MySQL的实际引⽤情况来看，更多是使⽤RAID 10阵列模式（另外磁盘阵列的整体性能和阵列控制芯⽚有很⼤关系）。RAID 10阵列模式可以在提升了整个系统I/O性能的基础上兼顾了存储的安全性。为了使⽤RAID 10磁盘阵列模式，读者⾄少需要为准备4块磁盘。其中2/4的磁盘容量⽤来存储数据冗余，另外2/4的磁盘容量⽤来分散存储数据。对于RAID 10磁盘阵列模式的详细⼯作⽅式介绍，读者可以参看另⼀篇⽂章（《》）

使⽤固态硬盘进⼀步替代机械磁盘

以上解决⽅案中，每⼀个机械磁盘的I/O性能将会成为整个RAID 10磁盘阵列的性能瓶颈（不考虑阵列控制芯⽚的处理性能）。所以如果技术团队还有多余的资⾦⽀持那么下⼀步要做的就是将构成RAID 10磁盘阵列多个机械磁盘全部替换成固态磁盘。如下图所⽰：

进⼀步使⽤外置磁盘阵列柜 + 光纤结构

USB3.0接⼝的理论带宽只有600MB/S，⽽且PC Server内置的磁盘阵列控制器由于服务器内部空间的限制，也存在磁盘数量扩展困难的问题。如果读者确认⽣产环境的某个物理服务器将以I/O读写操作为主，且I/O性能将成为其上⼯作的应⽤软件的瓶颈。那么这时最好的硬件⽅案就是直接采⽤外置企业级磁盘阵列柜 + 光纤接⼝的⽅式搭建硬件层⽀持。

⽬前主流的光纤线路带宽为16Gb/s，这远远⾼于USB3.0 6Gb/s的理论带宽、⾼于SAS 12Gb/s的理论带宽。另外单个企业级磁盘阵列柜可容纳的磁盘数量就已经很⾼了（例如IBM Storwize V5000 单柜提供24个磁盘位，单柜⽀持最⼤72TB存储容量），并且这些企业级盘柜⼀般⽀持扩展成多柜。这两种特性有效解决了硬件层⾯磁盘I/O速度和容量的问题，但代价就是这些IT基础折本的价格⼀般⽐较昂贵，技术团队所在企业需要有⽐较宽裕的项⽬/产品建设预算。