架构设计：系统存储（5）——MySQL数据库性能优化（1）

转自http://blog.csdn.net/yinwenjie/article/details/52461398

接上文：《架构设计：系统存储（4）——块存储方案（4）》
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

每当InnoDB接收到一个完整数据库insert/update请求事务后，就会创建一个新的LSN。新的LSN = 旧的LSN + 本次写入的日志大小。这条最新的日志将会使用Log sequence进行标记，并且如果出现接收到多个事务请求的情况下，InnoDB也会按照一个既定的顺序对这些日志进行排序，然后依次生成新的LSN。这一步骤是完全在内存中进行的，所以不存在I/O性能问题。

接下来Mysql就会开始执行这个事务中的各种细节操作。InnoDB数据库引擎专门有一个InnoDB Buffer Pool内存空间用来进行数据更改或数据新增。其大小由innodb_buffer_pool_size参数控制，其数据来源于innoDB data file并且以Page的形式存在于InnoDB Buffer Pool中。当日志中有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值。实际上执行完这个步骤，一个事务处理操作才算真正成功。

但是涉及数据变动的脏页还没有更新到磁盘上，为什么事物的处理就可以算作成功了呢？这是因为即使这个时候数据库异常崩溃了，就凭存储在磁盘上的完整日志我们也可以重做数据。好吧，最好还是要同步脏页是吧。在第三个步骤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固态硬盘和高速磁盘阵列时。

Checkpoint是InnoDB数据库引擎中最后一个标识点。这个标识点代表着当数据库异常崩溃重启后，小于或者等于这个标识点LSN值的所有日志信息、数据信息都无需进行重做检查。而LSN值大于Checkpoint的所有事务都需要重做，只是重做策略将视LSN值所在标识区域的不同而不同：

![这里写图片描述](https://img-blog.csdn.net/20161210094349704?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ2l0aHViXzMzNzM2OTcx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

    当代表事务的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 性能问题要点

Log flush和Pages flush

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

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 version 5.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 Buffer Pool区域不仅包括我们已经介绍的Page Cache数据部分，还包括其它的数据区块。例如为了快速定位B+树索引的Hash Index结构。调整Innodb_Buffer_Pool_size参数将会使这些数据区域都享受到内存容量带来的优势——至少不会频繁地发生内容空间的强制清理。

基础硬件条件

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

所以即使是初创型公司的线上生产环境，本文也不推荐使用单块机械磁盘存储任何需要持久保存的业务数据。如果是因为资金问题，则推荐使用一些云服务商提供的现成PaaS环境，原因是这些PaaS
环境本身就支持数据恢复功能，且利用云服务商已经建设好的价格不菲的硬件/软件环境，MySQL数据库的I/O性能和计算性能暂时还不会成为业务系统的瓶颈。

3-3、突破I/O性能

=================================================
（接下文）