高性能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日志（重新创建旧行版本所需的信息）​、修改缓冲区、双写缓冲区和其他内部结构。

配置表空间

可以使用innodb_data_file_path配置选项指定表空间文件。这些文件都包含在innodb_data_home_dir指定的目录中。
把文件配置到多个磁盘上并没有什么用，因为InnoDB会先填满第一个文件，然后当第一个文件填满时再填第二个，以此类推；负载并没有以更高性能所需的方式分散。RAID控制器是分散负载的一种更智能的方式。

innodb默认行为是创建一个10M B的自动扩展文件。如果让文件自动扩展，最好对表空间的大小设置一个上限，以防止其变得过大，因为一旦增长，就无法再收缩。例如，以下配置会将自动扩展文件限制为2 GB：（innodb一直都有这个毛病）

管理单个表空间可能很麻烦，特别是当它自动扩展而你又想回收空间时（出于这个原因，我们建议禁用自动扩展特性，或者至少为空间设置一个合理的上限）​。回收空间的唯一方法是将数据导出，然后关闭MySQL并删除所有文件，再修改配置，重启，让InnoDB创建新的空文件，最后再恢复数据。InnoDB对表空间是完全不宽容的：你不能简单地删除文件或者改变其大小。如果损坏了表空间，InnoDB将无法启动。同样，InnoDB对日志文件也非常严格。如果你习惯像使用MyISAM那样随意地移动文件，请务必注意！

innodb_file_per_table选项允许你将InnoDB配置为每个表使用单独的文件。它将数据存储在数据库目录下的tablename.ibd文件中。这使得删除表时更容易回收空间。然而，将数据放在多个文件中实际上会导致更多的空间浪费，因为跟InnoDB单个表空间中的内部碎片相比，每个.ibd文件中都会有一些浪费的空间。

innodb_file_per_table也有不好的一面：会使DROP TABLE性能变差。严重时可能导致服务器范围内明显的停顿，原因有二。

删除表将在文件系统级别解除（删除）文件的链接，这在某些文件系统(ext3)中可能非常慢。可以使用文件系统中的技巧来缩短这个过程的持续时间：先将.ibd文件链接到一个大小为零的文件，然后手动删除该文件，而不是等待MySQL来删除。

当启用该选项时，每个表在InnoDB中都有自己的表空间。事实证明，删除表空间需要InnoDB在查找属于该表空间的页面时锁定和扫描缓冲池，这在服务器的缓冲池很大时是非常慢的。如果使用innodb_buffer_pool_instances将缓冲池分解为多个部分，这一点会有所改善。

MySQL的各种版本对此进行了一些修复。从8.0.23版本开始，这应该不再是一个问题了。

最后的建议是什么？我们建议使用innodb_file_per_table并限制共享表空间的大小，这样会使你的生活更轻松。如前所述，如果你遇到任何使此过程痛苦的情况，请考虑我们建议的修复方法。

行的旧版本和表空间

SHOW INNODB STATUS可以帮助定位问题²。可以查看TRANSACTIONS部分中的历史列表长度(History list length)，其显示了Undo日志的大小：

如果Undo日志很大，并且表空间因此而增长，你可以强制MySQL放慢速度来让InnoDB的清理线程跟上。这听起来可能不太吸引人，但别无选择。否则，InnoDB会不断地写入数据并填充磁盘，直到磁盘空间耗尽或者表空间达到所定义的上限。

要限制写操作，请将innodb_max_purge_lag变量设置为0以外的值。这表示在InnoDB开始延迟更多修改数据的查询之前，可以等待清除的最大事务数。你必须了解你的工作负载，才能决定合适的值。例如，如果平均事务影响1KB的行，并且可以在表空间中容忍100 MB未清除的行，那么可以将该值设置为100000。

请记住，未清除的行版本会影响所有查询，因为它们会使表和索引变大。如果清除线程不能跟上进度，性能就会下降。设置innodb_max_purge_lag变量也会降低性能，但这是两害相权取其轻。

其他I/O配置选项

sync_binlog选项控制MySQL如何将二进制日志刷新到磁盘，默认值是1，意味着MySQL将执行刷新并保持二进制日志的持久性和安全性。强烈推荐将其设置为1，不建议设置为任何其他值。

如果不将sync_binlog设置为1，发生崩溃时可能会导致二进制日志与事务数据不同步。这很容易破坏复制且不可恢复，尤其是当数据库使用全局事务ID时（更多信息，请参阅第9章）​。将其设置为1所提供的安全性远远超过由此产生的I/O性能损失。

配置MySQL并发

如果遇到InnoDB并发问题，并且运行的MySQL版本低于5.7，解决方案通常是升级服务器。旧版本仍然面临许多高并发可伸缩性的挑战。所有的东西都在诸如缓冲池互斥锁之类的全局互斥锁上排队，导致服务器几乎停止运行。如果升级到较新版本的MySQL，在大多数情况下不需要限制并发性。

如果你发现自己遇到了并发性瓶颈，最好的选择是对数据进行分片。如果分片不可行，那么可能需要限制并发性。InnoDB有自己的“线程调度器”​，它控制线程如何进入内核访问数据，以及进入内核后可以做什么。限制并发性的最基本方法是使用innodb_thread_concurrency变量，该变量限制了内核中同时可以有多少线程。值为0表示对线程的数量没有限制。如果是在老版本的MySQL中遇到InnoDB并发问题，这个变量是最重要的配置变量。

如果内核中已经有超过允许数量的线程，则新的线程不能进入内核。InnoDB使用一个两阶段的过程来尝试让线程尽可能高效地进入内核。两阶段策略减少了操作系统调度器导致的上下文切换开销。线程首先休眠innodb_thread_sleep_delay指定的微秒数，然后再重试。如果仍然不能进入，它将进入一个等待线程队列，将控制权交给操作系统。

第一阶段的默认睡眠时间为10000微秒。在高并发性环境中，当CPU未充分利用且许多线程处于“进入队列前的睡眠”状态时，更改此值会有所帮助。如果有很多小查询，默认值也可能太大，因为这会增加查询延迟。

第一阶段的默认睡眠时间为10000微秒。在高并发性环境中，当CPU未充分利用且许多线程处于“进入队列前的睡眠”状态时，更改此值会有所帮助。如果有很多小查询，默认值也可能太大，因为这会增加查询延迟。

一旦线程进入内核，InnoDB就有一定数量的“门票”​，可以“免费”返回内核，而无须任何并发性检查。这限制了它在返回到其他等待的线程队列之前可以完成的工作量。innodb_concurrency_tickets选项控制“门票”的数量。除非有很多非常长时间运行的查询，否则很少需要更改这个选项。​“门票”是根据查询而不是事务授予的。一旦查询完成，未使用的门票将被丢弃。

安全设置

max_connections：此设置类似于紧急刹车，以防止服务器被来自应用程序的大量连接压垮。如果应用程序出现错误或服务器遇到问题（如暂停）​，可能会打开大量的新连接。但是，如果不能执行查询，那么打开连接就没有什么好处，因此被“too many connections”错误拒绝是一种快速失败且失败成本较低的方法。
可以将max_connections设置得足够高，以满足你认为将要经历的正常负载的连接需求，并且额外保留一些连接以便管理服务器时可以登录。例如，如果在正常操作中会有大约300个连接，那么可以将其设置为500。如果不知道会有多少个连接，500也并非一个不合理的起点。默认值为151，这对于很多应用程序来说都不够。

skip_name_resolve：此设置禁用另一个与网络和身份验证相关的陷阱：DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接到服务器时，默认情况下，它会尝试确定正在连接的主机名，并将其作为身份验证凭据的一部分（也就是说，你的凭据是用户名、主机名和密码，而不仅仅是用户名和密码）​。但是要验证主机名，服务器需要执行一个正向确认的反向DNS查找（或“双反向DNS查找”​）​，这在接受连接之前涉及反向和正向DNS查找。如果DNS没有问题，那这一切都很好，但在某个时间点出现DNS故障的概率几乎是确定性的。当DNS出现问题时，所有东西都会堆积起来，最终连接超时。为了防止出现这种情况，我们强烈建议设置此选项，该选项将在身份验证期间禁用DNS查找。但如果你这样配置了，则需要将所有基于主机名的授权转换为使用IP地址、通配符或特殊主机名“localhost”​，因为基于主机名的账户将被禁用。

read_only和super_read_onlyread_only选项可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改。我们强烈建议将副本设置为只读模式。还有一个更严格的只读选项super_read_only，它甚至可阻止拥有SUPER权限的用户写入数据。启用此功能后，唯一可以将更改写入数据库的就是复制。我们也强烈建议启用super_read_only。它将防止你意外地使用管理员账户将数据写入只读副本，从而引起数据不同步。、

高级InnoDB设置

innodb_autoinc_lock_mode此选项控制InnoDB如何生成自动递增的主键值，在某些情况比如高并发插入时，这可能是一个瓶颈。如果有很多事务在等待自动增量锁（可以在SHOW ENGINE INNODB STATUS中看到）​，则应该调查此设置。这里不再重复手册中对选项及其行为的解释。

innodb_buffer_pool_instances在MySQL 5.5及更新的版本中，此设置将缓冲池划分为多个段，这可能是提高多核机器上MySQL在高并发工作负载下可伸缩性最重要的方法之一。多个缓冲池对工作负载进行分区，这样一些全局互斥体就不会成为争用热点。

innodb_io_capacity，InnoDB对它过去是硬编码的，并且假设在一个每秒可以执行100次I/O操作的硬盘上运行。这是一个糟糕的假设。该选项可以告知InnoDB有多少I/O容量可供其使用。InnoDB有时需要很高的设置（在速度极快的存储设备，如PCIe闪存设备上，可能有上万个I/O）才能稳定地刷新脏页，原因比较复杂。

innodb_read_io_threads和innodb_write_io_threads这些选项控制有多少后台线程可用于I/O操作。MySQL最新的版本默认有4个读线程和4个写线程，这对很多服务器来说已经足够了，特别是在MySQL 5.5之后提供了可用的本地异步I/O。如果你有很多硬盘和高并发工作负载，并且发现线程很难跟上，那么可以增加线程的数量，或者简单地将它们设置为执行I/O操作的物理磁盘数量（即使它们位于RAID控制器后面）​。

innodb_old_blocks_time：InnoDB有一个由两部分组成的缓冲池LRU列表，设计目的是防止临时查询将长期多次使用的页面驱逐出去。一次性查询，如mysqldump发出的查询，通常会将一个页面带到缓冲池的LRU列表中，从其中读取行，然后移到下一个页面。理论上，两部分的LRU列表可以防止这个页面将需要很长一段时间的页面替换为“年轻”子列表，只有在被多次访问后才会移动到“年老”子列表。但是InnoDB在默认情况下不会阻止这种情况的发生，因为这个页面有多行，因此多次访问从这个页面读取的行会导致它立即被移动到“年老”的子列表中，给需要长生命周期的页面带来压力。这个变量指定一个页面从LRU列表的“年轻”部分移动到“年老”部分所需的毫秒数。默认情况下，它被设置为0。将其设置为一个较小的值如1000（1秒）​，这在我们的基准测试中被证明是非常有效的。

配置总结

如果运行的是专用数据库服务器，那么可以设置的最佳选项是innodb_dedicated_server，它可以处理90%的性能配置。如果无法使用此选项，那么最重要的两个选项是：

• innodb_buffer_pool_size
• innodb_log_file_size

恭喜你，已经解决了我们所看到的绝大多数实际配置问题！

所以说数据库等重要服务，最好还是运行在单独的服务器上，如果预算不充足，考虑云专属数据库服务器也是个很好的选择。

schema设计与管理

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于你做出更好的选择：

• 更小的通常更好：一般来说，尽量使用能够正确存储和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和CPU缓存的空间更少，并且处理时需要的CPU周期也更少。但也要确保没有低估需要存储的值的范围（经常看到很多不负责任的开发，动不动varchar几百，只是为了存储个不到两位整数的字典值，我对这种人深以为耻，优秀的程序员一定是注意每一个细节的，要有良好的编程习惯）
• 简单为好：简单数据类型的操作通常需要更少的CPU周期。例如，整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则(collation)使字符型数据的比较更复杂。
• 尽量避免存储NULL：因为产品设计上的问题，这个完全做到确实挺难的，在设计上尽可能的把列设置为not null，然后也没必要把这个作为特地的调优项

整数类型

有两种类型的数字：整数(whole number)和实数（real number，带有小数部分的数字）​。如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。它们分别使用8、16、24、32和64位存储空间。可以存储的值的范围从-2(N-1)到2(N-1)-1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如，TINYINT UNSIGNED可以存储的值的范围是0～255，而TINYINT的值的存储范围是-128～127。
MySQL可以为整数类型指定宽度，例如，INT(11)，这对大多数应用毫无意义：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如，MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不仅适用于带小数的数字，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

浮点类型通常比DECIMAL使用更少的空间来存储相同范围的值。FLOAT列使用4字节的存储空间。DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围。与整数类型一样，你只能选择存储类型；MySQL会使用DOUBLE进行浮点类型的内部计算。

由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

之前做订单和支付场景的时候都是这么做的，MySQL存储的都是bigint的整数值（相当于是表示多少分钱，把值乘以100存入），其他的加减乘除运算在Java应用层搞定，减轻数据库的负担。

字符串类型

VARCHAR和CHAR类型

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL填充BINANRY用的是\0（零字节）而不是空格，并且在检索时不会去除填充值。

VARCHAR

VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（即，更少的空间用于存储更短的值）​。
VARCHAR需要额外使用1或2字节记录字符串的长度³：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2字节存储长度信息。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据。

InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB。我们稍后再讨论。

CHAR

CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充。

CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效。（比如说手机号和身份证号等等）

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

与其他数据类型不同，MySQL把每个BLOB和TEXT值当作一个具有自己标识的对象来处理。存储引擎通常会专门存储它们。当BLOB和TEXT值太大时，InnoDB会使用独立的“外部”存储区域，此时每个值在行内需要1～4字节的存储空间，然后在外部存储区域需要足够的空间来存储实际的值。
BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。如果只需要按前面少数几个字符排序，可以减小max_sort_length服务器变量的值。

日期和时间类型

DATETIME这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，2008-01-16 22：37：08。这是ANSI表示日期和时间的标准方式。
TIMESTAMP顾名思义，TIMESTAMP类型存储自1970年1月1日格林尼治标准时间(GMT)午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。

特殊数据类型

某些类型的数据并不直接对应于可用的内置类型。IPv4地址就是一个很好的例子。人们通常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，而不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易，所以应该将I P地址存储为无符号整数。MySQL提供了INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换。使用的空间从VARCHAR(15)的约16字节缩减到无符号32位整数的4字节。

太多的列

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构的操作代价是非常高的。InnoDB的行格式总是需要转换的。这种转换的成本取决于列数。当调查一个具有非常宽的表（数百列）的客户的高CPU消耗问题时，我们发现这种转换代价可能会变得非常昂贵，尽管实际上只使用了几列。如果计划使用数百列，请注意服务器的性能特征会有所不同。

太多的连接

所谓的实体属性值(entity attribute value，EAV)设计模式是一种被普遍认为糟糕的设计模式的典型案例，尤其是在MySQL中效果不佳。MySQL限制每个联接有61个表，而E AV模式设计的数据库需要许多自联接。我们已经看到不少E AV模式设计的数据库最终超过了这个限制。然而，即使联接数远小于61，规划和优化查询的成本对MySQL来说也会成为问题。一个粗略的经验法则是，如果需要以高并发性快速执行查询，那么每个查询最好少于十几个的表。

创建高性能的索引

B-tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，如图所示：

叶子节点比较特殊，它们的指针指向的是被索引的数据，而不是其他的节点页（不同存储引擎的“指针”类型不同）​。图中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
请注意，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，这时，则根据他们的出生日期来排列。
自适应哈希索引。InnoDB存储引擎有一个被称为自适应哈希索引的特性。当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建一个哈希索引。这就让B-tree索引也具备了一些哈希索引的优势，例如，可以实现非常快速的哈希查找。这个过程是完全自动化的，用户无法进行控制或者配置。不过，可以通过参数彻底关闭自适应哈希索引这个特性。
B-tree索引通常可以支持“只访问索引的查询”​，即查询只需要访问索引，而无须访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。（就是二级索引上覆盖字段查询，防止回表在查询一次数据，减少一次io）

前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

多列索引

MySQL引入了一种叫“索引合并”(index merge)的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行。在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合(union)，AND条件的相交(intersection)，组合前两种情况的联合及相交。下面的查询就使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引​。

如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”​，这可能会导致非常高的锁竞争，从而出现性能问题。

聚簇索引优缺点

优点如下：

• 你可以把相互关联的数据保存在一起。例如，在实现电子邮箱应用时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
  缺点如下：
• 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂(page split)的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。（这也是人们常说的回表查询）

InnoDB的数据分布

InnoDB以下图所示的方式存储数据。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针，以及所有的剩余列（在这个例子中是col2）​。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB的二级索引的叶子节点中存储的是主键值，并以此作为指向行的“指针”​。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值作为指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”​。（否则一致性的维护工作会更加影响性能，与之相比不如再去回表查询一遍）

InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针（下一级节点可以是非叶子节点，也可以是叶子节点）​。这对聚簇索引和二级索引都适用。

在InnoDB表中按主键顺序插入行

如果你正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的值和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入的，对于根据主键做联接操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这就是最糟糕的情况，数据本身没有任何聚集特性。（总见到一些人用uuid作为主键，感觉是没脑子么，最懒就用个自增id也可以啊，其实最好推荐用雪花id）

覆盖索引

当执行一个被索引覆盖的查询（也叫作索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。所以说自己写sql的话尽量别select *，有时候用得到覆盖索引的，而且查太多无用的列对数据库资源也是一种浪费。

使用索引扫描来做排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录都回表查询一次对应的记录。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的应用负载上。

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。[插图]如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

有一种特殊情况，如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。

冗余和重复索引

不幸的是，MySQL允许在相同列上创建多个相同的索引。虽然MySQL会抛出一个警告，但是并不会阻止你这么做。MySQL需要单独维护重复的索引，优化器在优化查询的时候也需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间。

找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者出现莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误——可以尝试运行CHECK TABLE来检查是否发生了表损坏。
可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。

更新索引统计信息

如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息，以解决这个问题。

InnoDB的统计信息值得深入研究。InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在旧InnoDB版本中，样本页面数是8，新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。

InnoDB会在表首次打开，或者执行ANALYZE TABLE，或者表的大小发生非常大的变化时计算索引的统计信息。

InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，或者在MySQL客户端开启自动补全功能的时候，会触发索引统计信息的更新。如果服务器上有大量的数据表，这可能会带来严重的问题，尤其是当I/O比较慢的时候。客户端程序或者监控工具触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多额外的压力，这会让用户因为启动时间漫长而感到沮丧。只要使用SHOW INDEX查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。

查询性能优化

EXPLAIN

EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列出的这些，速度从慢到快，扫描的行数从多到少。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

一般地，MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）​。这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。

切分查询

一次删除一万行数据一般来说是一个比较高效而且对服务器[插图]影响最小的做法（如果是事务型引擎，很多时候小事务能够更高效）​。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时间段中。

MySQL的客户端/服务器通信协议

MySQL的客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块来独立发送。

这种协议让MySQL通信变得简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只有一个人能控制球，而且只有控制球的人才能将球抛回去（发送消息）​。

查询状态

对于一个MySQL连接，或者一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列，其就表示当前的状态）​。在一个查询的生命周期中，状态会变化很多次。

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”​。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用了错误的关键字，使用关键字的顺序是否正确，或者它还会验证引号是否能前后正确匹配。
然后，预处理器检查生成的解析树，以查找解析器无法解析的其他语义，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

查询优化器

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4KB数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本：

这个结果表示，MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）​、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存带来的影响，它假设读取任何数据都需要一次磁盘I/O。

有很多种原因会导致MySQL优化器选择错误的执行计划，如下所示：

• 统计信息不准确。MySQL服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
• MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到的根据执行成本来选择执行计划并不是完美的模型。
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
• MySQL也并不是任何时候都是基于成本的优化。它有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引。即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。
• MySQL不会考虑不受其控制的操作的成本，例如，执行存储函数或者用户自定义函数的成本。
• 后面我们还会看到，优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。（表连接太多了就不行，所以建议不要太多表连一起查询）

MySQL的主动优化

将外联接转化成内联接

并不是所有的OUTER JOIN语句都必须以外联接的方式执行。诸多因素，例如WHERE条件、库表结构都可能会让外联接等价于一个内联接。MySQL能够识别这一点并重写查询，让其可以调整联接顺序。

使用代数等价变换规则

MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，(5=5 AND a>5)将被改写为a>5。类似地，如果有(a5 AND b=c AND a=5

优化COUNT()、MIN()和MAX()

索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会将这个表达式作为一个常数对待。类似地，如果要查找一个最大值，也只需读取B-tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”​。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数代替。

预估并转化为常数表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。例如，一个用户自定义变量在查询中没有发生变化时就可以将其转换为一个常数。数学表达式则是另一种典型的例子。
让人惊讶的是，在优化阶段，有时候一个查询也能够转化为一个常数。一个例子是在索引列上执行MIN()函数。甚至是主键或者唯一键查找语句也可以被转换为常数表达式。如果WHERE子句中使用了该类索引的常数条件，MySQL可以在查询开始阶段就先查找到这些值，这样优化器就能够知道并将其转换为常数表达式。

子查询优化

MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。
除此之外，MySQL在执行过程中，如果发现某些特殊的条件，则会提前终止查询。当查询执行引擎需要检索“不同取值”或者判断存在性的时候，MySQL都可以使用这类优化。

等值传播

如果两列的值可通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上。

列表IN()的比较

在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

表和索引的统计信息

因为服务器没有存储任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则给优化器提供对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度是多少、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

MySQL如何执行联接查询

MySQL对任何联接都执行嵌套循环联接操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要的各列。MySQL会尝试在最后一个联接表中找到所有匹配的行，如果最后一个联接表无法找到更多的行，MySQL返回到上一层次的联接表，看是否能够找到更多的匹配记录，依此类推，迭代执行。

在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接（参见链接33）​。这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时。

执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树[插图]，然后通过查询执行引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果你对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。

联接查询优化器

重新定义联接的顺序是优化器非常重要的一项功能，通过这种操作可以过滤出更少的数据查询。不过有的时候，优化器给出的并不是最优的联接顺序。这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的最优的联接顺序执行——不过老实说，人的判断很难那么精准，优化器给出来的大概率都是最优解。
不过，糟糕的是，n个表的联接可能有n的阶乘种联接顺序，我们称之为所有可能的查询计划的“搜索空间”​。搜索空间的增长速度非常块，例如，若是10个表的联接，那么共有3628800种不同的联接顺序！当搜索空间非常大的时候，优化器不可能逐一评估每一种联接顺序的成本。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的联接顺序。实际上，当需要联接的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了（optimizer_search_depth参数可以根据需要指定大小）​。

有时查询不能重新排序，联接优化器可以利用这一点通过消除选择来减小搜索空间。左联接(LEFT JOIN)和相关子查询都是很好的例子（稍后将详细介绍子查询）​。这是因为，一个表的结果依赖于另外一个表中检索的数据，这种依赖关系通常可以帮助联接优化器通过消除选择来减少搜索空间。

排序优化

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序(filesort)，即使完全是在内存中排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”​，MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并(merge)，最后返回排序结果。

在联接查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句中的所有列都来自联接的第一个表，那么MySQL在联接处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”字样。除此之外的所有情况，MySQL都会先将联接的结果存放到一个临时表中，然后在所有的联接都结束后，再进行文件排序。在这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary；Using filesort”字样。如果查询中有LIMIT的话，LIMIT也会在文件排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。（所以这里面会涉及到了一个数据查询的深分页的问题）

将结果返回给客户端

MySQL将结果集返回客户端是一个增量且逐步返回的过程。例如，我们回头看看前面的联接操作，一旦服务器处理完最后一个联接表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也可让MySQL客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存，然后批量传输。

并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系数据库能够提供这个特性，但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。

优化COUNT()查询

COUNT()的作用

COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某列的值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）​。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。因为很多人对NULL理解有问题，所以这里很容易产生误解。

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

我们发现最常见的错误之一是，当需要统计行数时，在COUNT()函数的括号内指定了列名。如果想要知道结果中的行数，应该始终使用COUNT(*)，这样可以更清晰地传达意图，避免糟糕的性能表现。

所以网上很多所谓的博客count(*)性能差什么的都是纯属放屁，国内的博客里面充斥着太多的伪科学了，好多东西最好还是找官网和权威性著作查实一下

简单优化

通常来说，COUNT()查询需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面提到的方法，在MySQL层面还能做的就只有索引覆盖扫描了。

优化联接查询

确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到联接的顺序。当表A和表B用列c联接的时候，如果优化器的联接顺序是B、A，那么就不需要在B表的对应列上建索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需在联接顺序中的第二个表的相应列上创建索引。

确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

优化LIMIT和OFFSET子句

一个非常常见又令人头疼的问题是，在偏移量非常大的时候，例如，可能是LIMIT 1000，20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这就是常说的深分页问题，很多数据库都有类似的问题

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。在偏移量很大的时候，这样做的效率会有非常大的提升。sql类似这样写：

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地被使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）​。

除非你确实需要服务器消除重复的行，否则一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端，虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）​。

复制

MySQL内置的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库的方式来进行数据同步。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份、数据分析以及数据仓库等工作的基础。

复制如何工作

总的来说，复制有三个步骤：

• 源端把数据更改记录到二进制日志中，称之为“二进制日志事件”(binary log events)。
• 副本将源上的日志复制到自己的中继日志中。
• 副本读取中继日志中的事件，将其重放到副本数据之上。
  
  在复制架构中，读取和重放日志事件是解耦的，这就允许读取日志和重放日志异步进行，也就是说，这里的I/O线程和SQL线程都是可以独立运行的。

复制原理

MySQL提供了三种不同的二进制日志格式用于复制：基于语句的、基于行的和混合模式。可以通过系统参数binlog_format控制日志写入时使用哪种日志格式。

1. 基于语句的复制是通过记录所有在源端执行的数据变更语句来实现的。当副本从中继日志读取到事件并执行时，实际上是重新执行在源端执行过的SQL语句。这种格式的主要优点是简单且紧凑。一条更新了大量数据的SQL语句，在二进制日志中可能仅仅需要几十字节存储。其最大的弊端则在于会遇到某些具有“不确定性”的SQL语句问题。假设有一条语句删除了一张有1000行记录的表中的100行，但没有用ORDER BY子句。如果在源和副本上，记录的排序不同，这条SQL语句在源和副本上删除的100条记录就会不同，这将导致数据不一致。
2. 基于行的复制将事件写入二进制日志，该事件包含了该行记录发生了什么改变。这听起来很简单，但是，因为这种模式的确定性，相比基于语句的方式来说，其实发生了很大的变化。使用基于行的复制，通过查看二进制日志中的事件，可以看到究竟是哪一行记录发生了什么样的改变。在基于语句的复制模式下，SQL语句在执行时被解析，服务器在执行时找到所有需要变更的记录进行操作。而在基于行的复制模式下，每条被改变的记录都会作为事件被写入二进制日志，这可能会让二进制日志的大小发生巨大的增长。
3. “混合模式”(the mixed method)试图结合以上两种格式的优点。在这种模式下，事件的写入，默认使用基于语句的格式，仅在需要时才切换到基于行的格式。我们说“试图”是因为这种模式真的非常“努力”​，在写入每个事件时会有很多的判断条件[插图]，以确定使用哪种格式，而这也会导致二进制日志中出现不可预测的事件。我们认为二进制日志数据应该是两种模式选其一，而不应该是两者的混合。

我们建议坚持使用基于行的复制，除非某些场景下明确需要临时使用基于语句的复制。基于行的复制提供了最安全的数据复制方法。(所以说混合模式压根不靠谱呗 可能判断写的太复杂了容易有bug)

全局事务标识

为了解决断连重连确认副本复制进度的问题，MySQL新增了另一种跟踪复制位点的方法：全局事务标识符(GTID)。使用GTID，源服务器提交的每个事务都被分配一个唯一标识符。此标识符是由server_uuid[插图]和一个递增的事务编号组成的。当事务被写入二进制日志时，GTID也随之被写入。回顾本章前面的内容可以了解到，副本将从源库读取的二进制日志事件先写入本地中继日志，再使用SQL线程执行该事务，将变更应用到本地副本上。当SQL线程提交事务时，它也会将GTID标记为执行完成。
GTID解决了运行MySQL复制的一个令人痛苦的问题：处理日志文件和位置。强烈建议始终按照MySQL官方文档中的说明，在数据库中启用GTID。

崩溃后的复制安全

为了尽量降低复制中断的可能性，建议MySQL的部分参数按照如下讲解内容进行配置。

• innodb_flush_log_at_trx_commit=1：严格来说，这并不是一个复制相关的配置。不过，这个参数可以保障每个事务日志都被同步地写到磁盘。这是一个符合ACID要求的配置，将最大限度地保护你的数据——即使是在复制中也是这样。这是因为二进制日志事件首先被提交，然后事务将被提交并写入磁盘。将此参数设置为1将增加磁盘写入操作的频次，同时确保数据的持久性。
• sync_binlog=1：该变量控制MySQL将二进制日志数据同步到磁盘的频率。将此值设置为1意味着在每次事务执行的时候都会把二进制日志同步写入磁盘。这可以防止在服务器崩溃时丢失事务。就像之前的配置参数一样，它也会增加磁盘写入量。
• relay_log_info_repository=TABLE：以前，MySQL的复制通常依赖磁盘上的文件来跟踪复制位置。这意味着，复制完成事务操作之后，还需要完成同步写入磁盘操作。如果在事务提交和同步之间发生了服务器崩溃，此时，磁盘上的文件将可能包含错误的文件和位置信息。在该配置下，该信息将被转移到MySQL本身的InnoDB表中，允许复制更新同一事务中的事务和中继日志信息。这会在一个原子操作中完成，并有助于崩溃恢复。
• relay_log_recovery=ON：简单地说，该参数使得副本服务器在检测到崩溃时会丢弃所有本地中继日志，并从源服务器中获取丢失的数据。这确保了在崩溃中发生的磁盘上的任何损坏或不完整的中继日志都是可恢复的。配置该参数后，不再需要配置sync_relay_log，因为在发生崩溃时，中继日志将被删除，也就无须花费额外的操作将它们同步到磁盘。

延迟复制

在某些场景下，在一个拓扑结构中，某些副本有一些延迟反而是有好处的。在这个策略下，可以让副本中的数据保持在线并且持续运行，但同时落后于源数据库数小时或者数天。延迟复制的配置语句是CHANGE REPLICATION SOURCE TO，配置选项为SOURCE_DELAY。

想象一下这样的场景，你正在处理大量数据，突然意外地做了一些变更：删除了一个表。从备份中恢复可能需要几个小时。如果使用了延迟复制的副本，则可以找到DROP TABLE语句对应的GTID，使副本服务器的复制运行到表被删除之前的时间点，这会大大减少修复时间。

多线程复制

在复制技术中，历史非常悠久的挑战之一就是，虽然在源上数据可以并行写入，但在副本上只能是单线程的。最新的MySQL版本则提供了多线程复制能力​，可以在副本端运行多个SQL线程，从而加快本地中继日志的应用。
多线程复制有两种模式：DATABASE和LOGICAL_CLOCK。在DATABASE模式下，可以使用多线程更新不同的数据库；但不会有两个线程同时更新同一个数据库。如果将数据分布在MySQL的多个数据库中，则可以同时并且一致地更新它们，这种模式非常有效。另一个模式LOGICAL_CLOCK允许对同一个数据库进行并行更新，只要它们都是同一个二进制日志组提交的一部分。

什么是二进制日志的组提交

相当于多等待一些延迟时间，获取到多个事务的提交信息，一组发过来，增加一次网络传输的数据效率，节省网络次数开销。（kafka也有类似的设计，这样对于网络资源的开销来说会更加的节省）。
人工延迟的配置参数是binlog_group_commit_sync_delay（以微秒为单位的延迟）和binlog_group_commit_sync_no_delay_count（决定中止等待之前要等待的事务数）​。

多线程复制设置

在大多数情况下，可以简单地通过将replica_parallel_workers设置为非零值来开启该配置，并立即看到效果。如果在单个数据库上操作，还需要将replica_parallel_type更改为LOGICAL_CLOCK。由于多线程复制还需要使用协调线程，因此管理这些线程的状态，也会带来一些额外的开销。此外，确保你的副本配置了参数replica_preserve_commit_order，这样就不会出现无序提交的问题。请参阅官方文档中Gaps（​“差距”​）小节，那里详细解释了这个配置的重要性。

半同步复制

在启用半同步复制后，源在完成每个事务提交时，都需要确保事务至少被一个副本所接收。[插图]需要确认副本已收到并成功将其写入自己的中继日志（但不一定应用到本地数据）​。
一个值得注意的重要事情是，如果在一定时间范围内没有副本确认事务，MySQL将恢复到标准的异步复制模式。这时事务并不会失败。这也说明，半同步复制不是一种防止数据丢失的方法，而是可以让你拥有更具弹性的故障切换的更大工具集的一部分。

所以说这就是个鸡肋的功能，实用价值不大，靠他也确保不了数据不丢失。

计划内切换

1. 确定将哪个副本切换为新的源。这通常是一个包含所有数据的副本。这就是要操作的目标。
2. 检查延时，确保延时在秒级别。
3. 通过设置super_read_only停止数据写入源服务器。
4. 等待副本与目标完全同步。可以通过比较GTID来确定这一点。
5. 在目标（需要切换为源的副本）上取消read_only设置。
6. 将应用流量切换到目标上。
7. 将所有副本重新指向新的源，包括刚刚被降级为副本的服务器。如果配置了GTID和AUTO_POSITION=1，这很简单。

计划外切换

1. 确定需要切换的副本。通常会选择数据最完整的副本。这就是要切换的目标。
2. 在目标上关闭read_only设置。
3. 将应用流量切换到目标上。
4. 将所有副本重新指向新源（目标服务器）​，包括恢复后的原来提供服务的源服务器。在使用了GTID之后，这些操作都很简单。

还需要注意，切换前的源服务器再次启动时，需要默认启用super_read_only。这将有助于防止任何意外的写入。

使用分片技术

这看起来是一种逃避问题的方式，但使用分片技术将写入分散到多个源也是一种非常有效的策略。MySQL长期存在的经验法则是：使用副本扩展读取操作，使用分片技术扩展写入操作。

备份与恢复

备份方式

建议混合使用裸文件备份和逻辑备份两种方式：先使用裸文件备份，用得到的数据启动MySQL服务器实例并运行mysqlcheck。然后，周期性地使用mysqldump执行逻辑备份。这样做可以获得两种方法的优点，不会使生产服务器在导出时有过度负担。如果能够方便地利用文件系统的快照，也可以生成一个快照，将该快照复制到另外一台服务器上并释放，然后测试原始文件，再执行逻辑备份。

备份配置文件

对于服务器配置来说，备份中对生产服务器至关重要的任何外部配置，都十分重要。在UNIX服务器上，这可能包括cron任务、用户和组的配置、管理脚本，以及sudo规则。

这些建议在许多场景下会被当作“备份一切”​。然而，如果有大量的数据，这样做的开销将非常高，如何做备份，需要更加明智地去考虑。特别是，可能需要在不同备份中备份不同的数据。例如，可以单独地备份数据、二进制日志和操作系统及系统配置。

管理和备份二进制日志

服务器的二进制日志是需要备份的最重要元素之一。它们对于基于时间点的恢复是必需的，并且通常比数据要小，所以更容易被进行频繁的备份。如果有某个时间点的数据备份和所有从那时以后的二进制日志，就可以重放从上次全备份以来的二进制日志并“向前回滚”所有的变更。

MySQL也使用二进制日志进行复制，因此备份和恢复的策略经常和复制配置相互影响。经常备份二进制日志是一个好主意。如果你不能承受丢失超过30分钟数据的代价，至少要每30分钟就备份一次。

文件系统快照

文件系统快照是一种非常好的在线备份方法。支持快照的文件系统能够瞬间创建与用来备份的内容一致的镜像。支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理(LVM)，以及许多的SAN系统和文件存储解决方案，例如，NetApp存储。也有部分云厂商提供远程可挂载磁盘，其可以提供快照的功能。

不要把快照和备份相混淆。创建快照是减少必须持有锁的时间的一个简单方法；释放锁后，必须将文件复制到备份中。事实上，使用文件系统快照，有些时候甚至无须任何锁定，就可以创建InnoDB的备份快照。我们将要展示两种使用LVM来为全InnoDB文件系统做备份的方法，你可以选择最小化锁或零锁。

快照，无论是LVM、ZFS还是SAN快照，都不是真正的备份，因为它们不包含数据的完整副本。因为快照是写时复制，所以它们只包含数据的实时副本与快照发生时的数据之间的差异。如果未修改的块在数据的实时副本中损坏，则没有可用于恢复的该块的完整副本，这时，每个快照都会看到与实时卷相同的损坏块。在进行备份时，可以使用快照“冻结”数据，但不要将快照本身当作备份来依赖。

数据库扩展

使用分片扩展写

分片意味着将数据切分成不同的、更小的数据库集群，这样就可以同时在更多的源主机上执行更多的写入操作。可以进行两种不同类型的分片或分割：功能分割或数据分片。
功能分割(Functional partitioning)，或称为职责划分，意味着将不同的节点用于不同的任务。其中的一个例子可能是将用户记录放在一个集群上，并将其计费放在另一个集群上，这种方法允许每个集群单独扩展。用户注册量的激增可能会给用户集群带来压力。基于使用单独的系统，计费集群负载较少，从而允许进行客户计费操作。相反，如果计费周期日是月初的第一天，你可以清楚地知道运行计费操作不会在其他时间影响用户注册。
数据分片(Data sharding)是当今扩展超大型MySQL应用程序最常见和最成功的方法。通过将数据切分成更小的部分或分片，并将它们存储在不同的节点上，可以达到拆分数据的目的。（好多数据库都是直击提供分片机制的，redis的集群，es自带的索引分片，都是为了应对高并发大数据量的设计）

多个分片键

复杂的数据模型使数据分片更加困难。许多应用程序有多个分片键，特别是当数据中有两个或更多重要的维度时。换句话说，应用程序可能需要从不同的角度看到一个有效的、连贯的数据视图。这意味着可能需要在系统中至少将一些数据存储两次。

跨分片查询

大多数分片应用程序都会有一些查询需要聚合或联接多个分片的数据。例如，如果读书俱乐部网站显示了最受欢迎或最活跃的用户，那么它必须根据定义访问每个分片。实现数据切分最困难的部分是使这类查询正常工作，因为应用程序所认为的单个查询需要被分裂成多个查询且并行执行，每个分片一个。一个好的数据库抽象层可以帮助减轻查询的负担，但即便如此，这样的查询也比分片内查询慢得多，且代价更高，因此主动缓存通常也是有必要的。

如果跨分片查询是例外情况而不是常态，你所选择的分片方案就是一个好方案。你应该努力使查询尽可能简单，并被包含在一个分片内。对于那些需要跨分片聚合的情况，我们建议从应用程序逻辑的整体加以考虑。

跨分片查询也可以从汇总表中获益。可以通过遍历所有分片并在完成后将结果冗余地存储在每个分片上来构建汇总表。如果觉得在每个分片上复制数据太浪费，可以将汇总表合并到另一个数据存储中，这样其就只被存储一次。

---

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

2. 他算是innodb万能的问题排查方法了，他会详细的输出各种日志和行为，包括线程死锁等等，算是排查的问题的一个万金油了，出现问题可以第一时间执行这个命令看下输出 ↩︎

3. 这也是为什么能看到很多人的字符串设置为varchar(255)的原因，因为字符串长度差不多255能够的情况下这么设置能节省一字节的空间，好多人不知道跟着这么设置其实是有深层次原因的 ↩︎