建设安全监督网_网站联盟是什么意思_营销型网站模板_网站设计制作公司

高性能MySQL（第四版）¹

MySQL架构

MySQL的逻辑架构

存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每种存储引擎都有其优势和劣势。服务器通过存储引擎API进行通信。这些API屏蔽了不同存储引擎之间的差异，使得它们对上面的查询层基本上是透明的。存储引擎层还包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL[插图]，不同存储引擎之间也不会相互通信，而只是简单地响应服务器的请求。

连接管理与安全性

默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行，该线程驻留在一个内核或者CPU上。服务器维护了一个缓存区，用于存放已就绪的线程，因此不需要为每个新的连接创建或者销毁线程

优化与执行

MySQL解析查询以创建内部数据结构（解析树）​，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊关键字向优化器传递提示，从而影响优化器的决策过程。也可以请求服务器解释优化过程的各个方面，使用户可以知道服务器是如何进行优化决策的，并提供一个参考点，便于用户重构查询和schema、修改相关配置，使应用尽可能高效地运行。

优化器并不关心表使用的是什么存储引擎，但存储引擎对于查询优化是有影响的。优化器会向存储引擎询问它的一些功能、某个具体操作的成本，以及表数据的统计信息。例如，一些存储引擎支持对某些查询有帮助的特定索引类型。
在旧版本中，MySQL可以使用内部查询缓存(query cache)来查看是否可以直接提供结果。但是，随着并发性的增加，查询缓存成为一个让人诟病的瓶颈。从MySQL 5.7.20版本开始，查询缓存已经被官方标注为被弃用的特性，并在8.0版本中被完全移除。尽管查询缓存不再是MySQL服务器的核心部分，但缓存被频繁请求的结果集依然是一个很好的实践

MySQL已经弃用了查询缓存了，因为消耗大于收益

并发控制

并发控制这一经典问题的解决方案相当简单。处理并发读/写访问的系统通常实现一个由两种锁类型组成的锁系统。这两种锁通常被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定包含需要修改的部分数据，而不是所有的资源。更理想的方式是，只对需要修改的数据片段进行精确的锁定。任何时候，让锁定的数据量最小化，理论上就能保证在给定资源上同时进行更改操作，只要被修改的数据彼此不冲突即可。

使用行级锁(row lock)可以最大程度地支持并发处理（也带来了最大的锁开销）​。行级锁是在存储引擎而不是服务器中实现的。服务器通常[插图]不清楚存储引擎中锁的实现方式。

事务

事务就是一组SQL语句，作为一个工作单元以原子方式进行处理。如果数据库引擎能够成功地对数据库应用整组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么整组语句都不执行。也就是说，作为事务的一组语句，要么全部执行成功，要么全部执行失败

事务的ACID概念说明：

• 原子性(atomicity)：一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
• 一致性(consistency)：数据库总是从一个一致性状态转换到下一个一致性状态。在前面的例子中，一致性确保了，即使在执行第3、4条语句之间时系统崩溃，支票账户中也不会损失200美元。如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。
• 隔离性(isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。
• 持久性(durability)：一旦提交，事务所做的修改就会被永久保存到数据库中。此时即使系统崩溃，数据也不会丢失。没有什么系统能真正保障百分之百的持久性的。

隔离级别通用标准说明：

• READ UNCOMMITTED（读未提交）：就是人们常说的脏读，在事务中可以查看其他事务中还没有提交的修改。非常之离谱，现实中没人使用，也没有使用场景
• READ COMMITTED（读已提交）：大多数数据库系统的默认隔离级别都是他(比如Oracle和SqlServer）。一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。这个级别仍然允许不可重复读(nonrepeatable read)，这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果。
• REPEATABLE READ（可重复读）:这一级别解决了不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读(phantom read) 的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(phantom row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了幻读的问题（实际上是还要有间隙锁的配合才真正意义上解决了幻读的问题）。
• SERIALIZABLE（可串行化）：这个级别实际上意义不大，因为他意味着性能的急剧下降，基本不可用。不过可以把MySQL的实现看作一种伪级别的串行化，通过MVCC等一系列机制，保证并发性能和落盘存储的前提下解决掉了所有的“读”问题。

死锁

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。

为了解决这个问题，数据库系统实现了各种死锁检测和锁超时机制。更复杂的系统，比如InnoDB存储引擎，检测到循环依赖后会立即返回一个错误信息。这可能是一件好事——否则，死锁将表现为非常缓慢的查询。还有一种方式，当超过锁等待超时的时间限制后直接终止查询，这样做通常来说不太好。InnoDB目前处理死锁的方式是将持有最少行级排他锁的事务回滚（这是一种最容易回滚的近似算法）​。

事务日志

事务日志有助于提高事务的效率。存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这会非常快。然后再把更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。因为事务日志采用的是追加写操作，是在硬盘中一小块区域内的顺序I/O，而不是需要写多个地方的随机I/O，所以写入事务日志是一种相对较快的操作。最后会有一个后台进程在某个时间去更新硬盘中的表。因此，大多数使用这种技术（write-ahead logging，预写式日志）的存储引擎修改数据最终需要写入磁盘两次。

如果修改操作已经写入事务日志，那么即使系统在数据本身写入硬盘之前发生崩溃，存储引擎仍可在重新启动时恢复更改。具体的恢复方法则因存储引擎而异。

隐式锁定和显式锁定

InnoDB使用两阶段锁定协议(two-phase locking protocol)。在事务执行期间，随时都可以获取锁，但锁只有在提交或回滚后才会释放，并且所有的锁会同时释放。前面描述的锁定机制都是隐式的。InnoDB会根据隔离级别自动处理锁。另外，InnoDB还支持通过特定的语句进行显式锁定，这些语句不属于SQL规范：

select ...  for share
select ... for update

多版本并发控制

MVCC的工作原理是使用数据在某个时间点的快照来实现的。这意味着，无论事务运行多长时间，都可以看到数据的一致视图，也意味着不同的事务可以在同一时间看到同一张表中的不同数据！

InnoDB通过为每个事务在启动时分配一个事务ID来实现MVCC。该ID在事务首次读取任何数据时分配。在该事务中修改记录时，将向Undo日志写入一条说明如何恢复该更改的Undo记录，并且事务的回滚指针指向该Undo日志记录。这就是事务如何在需要时执行回滚的方法。

当不同的会话读取聚簇主键索引记录时，InnoDB会将该记录的事务ID与该会话的读取视图进行比较。如果当前状态下的记录不应可见（更改它的事务尚未提交）​，那么Undo日志记录将被跟踪并应用，直到会话达到一个符合可见条件的事务ID。这个过程可以一直循环到完全删除这一行的Undo记录，然后向读取视图发出这一行不存在的信号。

事务中的记录可以通过在记录的“info flags”中设置“deleted”位来删除。这在Undo日志中也被作为“删除标记”进行跟踪。

值得注意的是，所有Undo日志写入也都会写入Redo日志，因为Undo日志写入是服务器崩溃恢复过程的一部分，并且是事务性的。[插图]这些Redo日志和Undo日志的大小也是高并发事务工作机制中的重要影响因素。

在记录中保留这些额外信息带来的结果是，大多数读取查询都不再需要获取锁。它们只是尽可能快地读取数据，确保仅查询符合条件的行即可。缺点是存储引擎必须在每一行中存储更多的数据，在检查行时需要做更多的工作，并处理一些额外的内部操作（说白了感觉设计更为精妙和复杂的读写分离操作，只不过让读和写根据事务id是能够有多个版本的）。

数据文件结构

在8.0版本中，MySQL将表的元数据重新设计为一种数据字典，包含在表的.ibd文件中。这使得表结构上的信息支持事务和原子级数据定义更改。在操作期间，我们不再仅仅依赖information_schema来检索表定义和元数据，而是引入了字典对象缓存，这是一种基于最近最少使用(LRU)的内存缓存，包括分区定义、表定义、存储程序定义、字符集和排序信息。服务器访问表的元数据的方式的这一重大变化减少了I/O，非常高效。特别是当前访问最活跃的那些表，在缓存中最常出现。每个表的.ibd和.frm文件被替换为已经被序列化的字典信息(.sdi)。

InnoDB引擎

InnoDB是MySQL默认的通用存储引擎。默认情况下，InnoDB将数据存储在一系列的数据文件中，这些文件统被称为表空间(tablespace)。表空间本质上是一个由InnoDB自己管理的黑盒。

InnoDB使用MVCC来实现高并发性，并实现了所有4个SQL标准隔离级别。InnoDB默认为REPEATABLE READ隔离级别，并且通过间隙锁(next-key locking)策略来防止在这个隔离级别上的幻读：InnoDB不只锁定在查询中涉及的行，还会对索引结构中的间隙进行锁定，以防止幻行被插入。

InnoDB内部做了很多优化。其中包括从磁盘预取数据的可预测性预读、能够自动在内存中构建哈希索引以进行快速查找的自适应哈希索引(adaptive hash index)，以及用于加速插入操作的插入缓冲区(insert buffer)。

从MySQL 5.6开始，InnoDB引入了在线DDL，它最初只支持有限的使用场景，但在5.7和8.0版本中进行了扩充。就地(in-place)更改schema的机制允许在不使用完整表锁和外部工具的情况下进行特定的表更改操作，

JSON文档支持

JSON类型在5.7版本被首次引入InnoDB，它实现了JSON文档的自动验证，并优化了存储以允许快速读取，这是对旧版本只能使用BLOB类型来处理JSON文档作为折中的重大改进。除了支持新的数据类型，InnoDB还引入了SQL函数来支持在JSON文档上的丰富操作。MySQL 8.0.7的进一步改进增加了在JSON数组上定义多值索引的能力。

数据字典的变化

MySQL 8.0的另一个主要变化是删除了基于文件的表元数据存储，并将其转移到使用InnoDB表存储的数据字典中。这给所有类似修改表结构这样的操作带来了InnoDB的崩溃恢复事务的好处。

原子DDL

MySQL 8.0引入了原子数据定义更改。这意味着数据定义语句现在要么全部成功完成，要么全部失败回滚。这是通过创建DDL特定的Undo日志和Redo日志来实现的，InnoDB便依赖这两种日志来跟踪变更——这是InnoDB经过验证的设计，已经扩展到MySQL服务器的操作中。

Performance Schema

Performance Schema提供了有关MySQL服务器内部运行的操作上的底层指标。
第一个概念是程序插桩(instrument)。程序插桩在MySQL代码中插入探测代码，以获取我们想了解的信息。
第二个概念是消费者表(consumer)，指的是存储关于程序插桩代码信息的表。
当应用程序用户连接到MySQL并执行被测量的插桩指令时，performance_schema将每个检查的调用封装到两个宏中，然后将结果记录在相应的消费者表中。这里的要点是，启用插桩会调用额外的代码，这意味着插桩会消耗CPU资源。

从5.7版开始，Performance Schema在默认情况下是启用的。大多数插桩默认是禁用的，只启用了全局、线程、语句和事务插桩。从8.0版本开始，默认情况下还启用了元数据锁和内存插桩。mysql、information_schema和performance_schema数据库没有启用插桩，但所有其他对象、线程和actor都启用了插桩。大多数实例、句柄和设置表都是自动调整大小的。_history表会存储每个线程的最后10个事件，_history_long表则存储每个线程的最后10000个事件。存储的SQL文本的最大长度为1024字节。SQL摘要的最大长度也是1024字节。超出部分会被截断(right-trimmed)。

元数据锁

元数据锁用于保护数据库对象定义不被修改。执行任何SQL语句都需要获取共享元数据锁：SELECT、UPDATE等，这不会影响其他需要获取共享元数据锁的语句。但是，共享元数据锁会阻止那些更改数据库对象定义的语句，比如ALTER TABLE或CREATE INDEX，直到锁被释放为止。

performance_schema中的metadata_locks表包含关于当前由不同线程设置的锁的信息，以及处于等待状态的锁请求信息。通过这种方式，可以轻松确定哪个线程阻塞了DDL请求，你可以决定是终止该语句还是等待它完成执行。要启用元数据锁监测，需要启用wait/lock/meta-data/sql/mdl插桩。

操作系统和硬件优化

平衡内存和磁盘资源

配置大内存的主要原因并不是为了在内存中保存大量数据，而是为了避免磁盘I/O，因为磁盘I/O比访问内存中的数据要慢几个数量级。重要的是平衡内存和磁盘空间大小、速度、成本和其他因素，以便让工作负载获得良好的性能。

缓存、读取和写入

事实上，除了允许写操作延迟之外，缓存还允许它们以两种重要的方式组合在一起。多次写操作，一次刷新：
一个数据片段可以在内存中被多次更改，而无须每一次都将新值写入磁盘。当数据被最终刷新到磁盘时，自上次物理写入以来发生的所有修改都将被持久化。例如，许多语句可以更新内存中的计数器。如果计数器被更新了100次，然后写入磁盘，则100次内存修改被合并为一次磁盘写入。

I/O合并：
许多不同的数据片段可以在内存中被修改，这些修改可以被收集在一起，因此物理写可以作为单个磁盘操作执行。

这就是为什么许多事务系统使用提前写日志(write-ahead logging)策略的原因。提前写日志允许在内存中更改页面，而不用将更改刷新到磁盘，这通常涉及随机I/O，速度非常慢。相反，它们将更改的记录写入顺序日志文件，这样要快得多。后台线程可以稍后将修改过的页面刷新到磁盘，这样做可以优化写操作的性能。写操作从缓冲中获益，因为可以将随机I/O转换为顺序I/O。异步（缓冲）写操作通常由操作系统处理，并且是被成批处理的，因此可以更优地被刷新到磁盘。同步（无缓冲）写入必须等待数据落盘。

RAID性能优化

RAID 5比较受欢迎，RAID控制器经常对RAID 5进行高度优化，尽管存在理论上的限制，但在某些工作负载下，RIAD 5智能控制器能够很好地使用缓存，其性能有时几乎可以达到RAID 10控制器的水平。这实际上可能反映了RAID 10控制器的优化程度较低，但不管什么原因，这就是我们所看到的。

RAID 10是一个非常好的数据存储选择。它由条带化的镜像对组成，因此在读写方面都能很好地被扩展。与RAID 5相比，它的重建速度快且容易。RAID 10也可以很好地在软件中被实现

RAID 50由条带化的RAID 5阵列组成，如果有很多磁盘，它可以很好地兼顾RAID 5的经济性和RAID 10的性能。这主要用于非常大的数据集，如数据仓库或非常大的OLTP系统。

RAID缓存

最后要考虑的是，很多硬盘驱动器都有自己的写缓存，通过欺骗控制器数据已经写入物理媒介，实际执行了“假”fsync()操作。直接连接（而不是连接到RAID控制器）的硬盘驱动器有时可以让操作系统管理它们的缓存，但这也并不总是有效。对于fsync()，这些缓存通常被刷新，对于同步I/O，这些缓存会被绕过，但同样地，硬盘也可能会撒谎。应该确保在fsync()时刷新缓存，或者干脆在没有备用电池时禁用它们。没有被操作系统或RAID固件正确管理的硬盘驱动器导致了许多数据丢失的案例。

选择文件系统

总的来说，最好使用日志型文件系统，如ext4、XFS或ZFS。否则，系统崩溃后，检查文件系统可能需要很长时间。
如果使用ext3或其后续版本ext4，日志级别可设置为3个，你可以在/etc/fstab挂载选项中设置。

不管使用什么文件系统，都有一些特定的选项是最好禁用的，因为它们没有提供任何好处，并且可能会增加相当多的开销。最著名的是记录访问时间，其在读取文件或目录时也需要写入。要禁用此选项，请将noatime、nodiratime挂载选项添加到/etc/fstab；这有时可以将性能提高5%～10%，具体取决于工作负载和文件系统（尽管在其他情况下可能没有多大区别）​。

还可以调整文件系统的预读行为，因为它可能是多余的。例如，InnoDB有自己的预读机制。禁用或限制预读对Solaris的UFS尤其有利。使用innodb_flush_method=O_DIRECT会自动禁用文件系统的预读。

我们通常建议使用XFS文件系统。ext3文件系统有太多太严格的限制，比如每个inode只有一个互斥锁，还有一些不好的行为，比如在fsync()时刷新整个文件系统中的所有脏块，而不是某个文件的脏块。尽管ext4文件系统在特定的内核版本中存在性能瓶颈，但它是一个可以接受的选择，你应该在确认选择之前进行调查。当考虑数据库所使用的文件系统时，最好考虑它已经使用了多长时间、有多成熟，以及是否在生产环境中被实际验证过。文件系统是在数据库中保证数据完整性的最底层。

内存和交换

给MySQL分配大量内存后，它的表现最好。正如我们在第1章中了解到的，InnoDB使用内存作为缓存来避免磁盘访问。这意味着内存系统的性能会直接影响查询的速度。即使在今天，确保更快的内存访问的最佳方法之一仍然是用外部内存分配器（如tcmalloc或jemalloc）来替换内置的内存分配器(glibc)。大量基准测试[插图]表明，与glibc相比，这两种方法都能提高性能并减少内存碎片。

在极端情况下，过多的内存分配可能会导致操作系统耗尽交换空间。如果发生这种情况，虚拟内存不足可能会导致MySQL崩溃。但即使没有耗尽交换空间，非常活跃的交换也会导致整个操作系统没有响应，甚至无法登录并终止MySQL进程。有时候，当交换空间用完时，Linux内核甚至会完全挂起。我们建议在运行数据库时完全不使用交换空间。磁盘会比RAM慢一个数量级，但这可避免这里提到的所有让人头痛的问题。

在极端的虚拟内存压力下经常发生的另一件事是，OOM Killer进程将启动并终止某些进程，通常会是MySQL，也可能是其他进程，比如被终止的是SSH进程，则会使系统无法通过网络访问。可以通过设置SSH进程的oom_adj或oom_score_adj值来防止这种情况发生。在使用专用数据库服务器时，我们强烈建议识别所有关键进程，如MySQL和SSH，并主动调整OOM Killer分值，以防止这些进程被首先终止。

操作系统通常允许对虚拟内存和I/O进行控制。我们提到了在GNU/Linux上控制它们的几种方法。最基本的方法是将/proc/sys/vm/swappiness的值更改为较低的值，例如0或1。这告诉内核，除非对虚拟内存的需求非常大，否则不要进行交换。

另一种选项是更改存储引擎读写数据的方式。例如，设置innodb_flush_method=O_DIRECT可以减轻I/O压力。直接I/O没有被缓存，因此操作系统不会将其视为增加文件缓存大小的原因。该参数仅对InnoDB有效。

优化服务器设置

应该怎么优化

MySQL有许多可以更改但不应该更改的设置。通常更好的做法是正确地配置基本设置（在大多数情况下，只有少数设置是重要的）​，并将更多的时间花在schema优化、索引和查询设计上。在正确设置MySQL的基本配置选项之后，从进一步的更改中获得的潜在收益通常很小。

另外，修改配置的潜在缺点可能是巨大的。MySQL的默认设置是有充分理由的。在不了解其影响的情况下进行更改可能会导致崩溃、卡顿或性能下降。因此，你永远不应该盲目地相信来自热门帮助网站（如MySQL论坛或Stack overflow）的某些人所报告的最佳配置。[插图]应该始终通过阅读相关的官方手册来检查任何更改并仔细测试。

简单来说就是千万不要自以为是，大部分配置保持默认就好，然后根据实际的业务情况和场景微调，并且做好充分的验证

MySQL配置工作原理

配置文件地址与查找

MySQL在类UNIX系统上，配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf。
需要永久使用的任何设置都应该写入全局配置文件，而不是在命令行中指定。否则会有风险，可能会在没有指定命令行选项的情况下意外启动服务器。将所有配置文件保存在一个地方也是一个好主意，这样可以方便地检查它们。如果不知道服务器会读取哪些文件，可以通过如下命令查询：

语法、作用域和动态性

配置设置全部用小写字母书写，单词之间以下画线或短横线分隔。这两种写法是等效的。

配置设置可以有多个作用域。有些设置是服务器范围的（全局作用域）​，有些设置对于每个连接都不同（会话作用域）​，有些设置是基于每个对象的。许多会话作用域的变量都有相应的全局变量，可以将相应的全局变量的值视为会话变量的默认值。如果更改会话作用域的变量，它只会影响更改该变量的连接，在连接关闭时更改将丢失。以下是一些例子，需要注意各个例子中的不同行为。

除了在配置文件中进行设置外，很多变量（但不是全部）还可以在服务器运行时进行更改。MySQL将这些称为动态配置变量。

持久化系统变量

MySQL 8.0引入了一个名为持久化系统变量的新功能（参见链接18）​，这有助于简化这个问题。新的语法SET PERSIST允许在运行时设置一次值，MySQL将把这个设置写入磁盘，以便在下次重启后继续使用该值。

最小化配置

在MySQL 8.0中引入了一个新的配置选项，innodb_dedicated_server。这个选项检查服务器上可用的内存，并为专用的数据库服务器配置了4个额外的变量(innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method)，从而简化这些值的计算和更改。这在云环境中特别有用，在云环境中，你可以运行一个128G B内存的虚拟机(VM)，然后重新启动以扩展到256G B内存。这种情况下MySQL是自配置的，不需要管理配置文件中的值的修改。这通常是管理这4个设置的最佳方式。

还要解释的一个设置是open_files_limit选项。在典型的Linux系统中，我们将其设置得尽可能大。在现代操作系统中，打开文件句柄的成本很低。如果这个设置不够大，就会看到经典的24号错误，​“too many open files”​。（这个配置很多的中间件都需要修改，比如nginx，kafka，es等等因为他们需要操作大量的文件句柄）

InnoDB缓冲池

InnoDB缓冲池需要的内存比其他任何组件都多，就性能而言，InnoDB缓冲池大小通常是最重要的变量。InnoDB缓冲池不仅缓存索引，还缓存行数据、自适应哈希索引、更改缓冲区、锁和其他内部结构等。InnoDB还使用缓冲池来实现延迟写操作，从而可以将多个写操作合并在一起并按顺序执行。简而言之，InnoDB严重依赖缓冲池，应该确保为其分配足够的内存。你可以使用SHOW命令或innotop等工具中的变量来监控InnoDB缓冲池的内存使用情况。

大型缓冲池会带来一些挑战，比如更长的关闭时间和预热时间。如果缓冲池中有很多脏（修改过的）页，InnoDB可能需要很长时间才能关闭，因为它会在关闭时将脏页写到数据文件中。当然也可以强制快速关闭，但在重新启动时，InnoDB需要做更多的恢复工作，因此实际上不能加快关闭和重新启动周期时间。如果提前知道什么时候需要关闭，可以在运行时将innodb_max_dirty_pages_pct变量更改为较低的值，等待刷新线程清理缓冲池，然后在脏页数量变少时关闭。可以通过监控innodb_buffer_pool_pages_dirty服务器状态变量或使用innotop监控SHOW INNODB STATUS来查看脏页数量。还可以使用变量innodb_fast_shutdown来调整关闭InnoDB的方式。

降低innodb_max_dirty_pages_pct变量的值并不能保证InnoDB在缓冲池中保留更少的脏页。相反，它控制的是InnoDB停止“lazy”行为的阈值。InnoDB默认使用同一个后台线程来刷新脏页，以及合并写操作并按顺序执行以提高效率。这种行为被称为“lazy”​，因为它允许InnoDB延迟刷新缓冲池中的脏页，除非需要为其他数据提供空间。当脏页的百分比超过阈值时，InnoDB会尽可能快地刷新页面，以尽量降低脏页的数量。与之前的行为相比，这些页面清理操作已经得到了极大的优化（参见链接19）​，包括能够配置多个线程来执行刷新。

当MySQL再次启动时，缓冲池缓存是空的，也称为冷缓存。在内存中保存行和页的所有好处现在都没有了。值得庆幸的是，默认情况下，innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个配置可以配合使用，以在启动时预热缓存池。启动时的加载需要时间，但它可以比等待服务器自然填充缓冲池更快地提高性能。

线程缓存

线程缓存保存了当前没有与连接关联但已准备好为新连接提供服务的线程。创建新连接时，如果缓存中有一个线程，MySQL会从缓存中取出该线程并将其提供给新连接。当连接关闭时，如果缓存中还有空间，MySQL会将线程放回缓存中。如果缓存中已经没有空间，MySQL会销毁线程。只要MySQL在缓存中有空闲线程，它就可以快速响应连接请求，因为不必为每个新连接创建新线程。

变量thread_cache_size指定了MySQL可以保存在缓存中的线程数。其默认值为-1或auto-sized，通常不需要更改这个变量，除非服务器会收到很多连接请求。要检查线程缓存是否足够大，请查看Threads_created状态变量。应该尽量保持线程缓存足够大，以使每秒创建的新线程数少于10个，但通常很容易使这个数字低于每秒1个。

配置MySQL的I/O行为

InnoDB事务日志

InnoDB使用日志来降低提交事务的成本。它不会在每个事务提交时将缓冲池刷新到磁盘，而是将事务记录到日志中。事务对数据和索引所做的更改通常映射到表空间中的随机位置，因此将这些更改刷新到磁盘将需要随机I/O。

使用日志，InnoDB可以将随机磁盘I/O转换为顺序I/O。一旦日志被安全地保存在磁盘中，即使更改的数据尚未写入数据文件，事务仍将是持久的。如果发生故障（例如停电）​，InnoDB可以重放日志并恢复已提交的事务。

当然，InnoDB最终必须将更改的数据写入数据文件，因为日志的大小固定，采取的是循环写入的方式：当到达日志的末尾时，它会环绕到日志的开头。如果日志记录中包含的更改尚未应用于数据文件，则无法覆盖日志记录，因为这将删除已提交事务的唯一永久记录。（环形文件记录日志，首尾相接的时候就必须要处理了）

InnoDB使用后台线程智能地刷新对数据文件的更改。该线程可以将写入分组，并使数据写入顺序化，以提高效率。实际上，事务日志可以将随机数据文件I/O转换为顺序日志文件I/O和顺序数据文件I/O。将刷新移到后台可以更快地完成查询，并有助于缓冲I/O系统的查询负载峰值。

日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制，这对写入性能非常重要。如果你采纳了我们之前的建议，使用innodb_dedicated_server，日志文件的大小将根据系统内存量来自动管理。

日志缓冲区

InnoDB修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当缓冲区满了、事务提交时，或者每秒1次（这三个条件以先满足者为准）​，InnoDB会将缓冲区刷新到磁盘上的日志文件中。如果有大型事务，增加缓冲区大小（默认为1MB）有助于减少I/O。控制缓冲区大小的变量是innodb_log_buffer_size。

通常不需要将缓冲区设置得太大。建议的范围是1～8M B，一般来说足够了，除非写入很多大的BLOB记录。与InnoDB的普通数据相比，日志条目非常紧凑。它们不是基于页面的，所以不会浪费空间一次存储整个页面。InnoDB也会让日志条目尽量短，有时甚至只用几个整数来表示记录的操作类型和该操作所需的任何参数！

InnoDB如何刷新日志缓冲区

当InnoDB将日志缓冲区刷新到磁盘上的日志文件时，会使用互斥锁锁定缓冲区，将其刷新到所需的位置，然后将剩余的条目移动到缓冲区的前面。当释放互斥锁时，可能会有多个事务准备刷新其日志条目。InnoDB使用了一个组提交特性，可以在单次I/O操作中将一组日志全部提交。

日志缓冲区必须被刷新到持久存储中，以确保提交的事务完全持久。如果你更关心性能而不是持久性，可以更改innodb_flush_log_at_trx_commit来控制日志缓冲区的刷新位置和刷新频率。可能的设置如下：

• 0：每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作。
• 1：每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中。这是默认的（也是最安全的）设置；它保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）​。
• 2: 每次事务提交时都将日志缓冲区写入日志文件，但不执行刷新。InnoDB按计划每秒刷新1次。与0设置最重要的区别是，如果只是MySQL进程崩溃，设置为2不会丢失任何事务。但是，如果整个服务器崩溃或断电，仍然可能丢失事务。

了解将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别很重要。在大多数操作系统中，将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中，依然还是在内存中。它实际上不会将数据写入持久存储。因此，如果发生崩溃或断电，设置为0和2通常会导致最多1秒的数据丢失，因为数据可能只存在于操作系统的缓存中。我们之所以说“通常”​，是因为InnoDB会以每秒1次的速度将日志文件刷新到磁盘上，但在某些情况下，例如刷新暂停时，可能会丢失超过1秒的事务。

InnoDB如何打开和刷新日志文件和数据文件

如果你使用的是类UNIX操作系统，并且RAID控制器有备用电池的写缓存，我们建议使用O_DIRECT。如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序。如果你选择使用innodb_dedicated_server，正如我们前面提到的，这个选项会自动设置。

InnoDB表空间

InnoDB将数据保存在表空间中，表空间本质上是一个虚拟文件系统，由磁盘上的一个或多个文件组成。InnoDB将表空间用于多种用途，而不仅仅是存储表和索引。表空间中还包含了Undo日志（重新创建旧行版本所需的信息）​、修改缓冲区、双写缓冲区和其他内部结构。

---

1. 作者：​[美]西尔维亚·博特罗斯(Silvia Botros)，​[美]杰里米·廷利(Jeremy Tinley)。译者：宁海元，周振兴，张新铭 ↩︎