高性能 MySQL 第三版

第1章

MySQL 架构与历史

1.1 MySQL 逻辑架构

图1-1 展示了MySQL的逻辑架构图。

如果能在头脑中构建出一幅 MySQL 各组件之间如何协同工作的架构图，就会有助于深入理解MySQL服务器。图1-1 展示了MySQL的逻辑架构图。

最上层的服务并不是 MySOL 所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
该提供了与客户端进行对接的接口，类似与 JDBC 的客户端工具。所以对于绝大多数的数据库而言，都是提供了此接口，以保证客户端可以进行调用。
第二层架构是 MySQL 比较有意思的部分。大多数 MySQL的核心服务功能都在这一层包括查询解析、分析、优化、缓存以及所有的内置函数(例如，日期、时间、数学和加密函数)，所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。
该层中包含了 MySQL 不同存储引擎的基础通用功能与模块
第三层包含了存储引擎。存储引擎负责 MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析 SQL1，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。
对于数据库的处理，不同的存储引擎有其不同的特点和适用场景。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

当客户端(应用)连接到 MySQL 服务器时服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。 如果使用了安全套接字(SSL)的方式连接，还可以使用X509证书认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限(例如，是否允许客户端对 world 数据库的Country表执行 SELECT语)。

关于安全套接字可以参考下面的方案:docs.aws.amazon.com/zh_cn/Amazo…

1.1.2 优化与执行

MySQL 会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。也可以请求优化器解释 (explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和 schema、修改相关配置，使应用尽可能高效运行。第6 章我们将讨论更多优化器的细节。

优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如,某些存储引擎的某种索引,可能对一些特定的查询有优化。关于索引与schema的优化请参见第 4 章和第5章。对于 SELECT语句，在解析查询之前，*服务器会先检查查询缓存 (Query Cache)，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。 *第7章详细讨论了相关内容。

1.2 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。本章的目的是讨论 MySQL 在两个层面的并发控制:服务器层与存储引擎层。并发控制是一个内容庞大的话题，有大量的理论文献对其进行过详细的论述。本章只简要地讨论MySQL如何控制并发读写，因此读者需要有相关的知识来理解本章接下来的内容以Unix系统的email box 为例，典型的mbox文件格式是非常简单的。一个mbox邮箱中的所有邮件都串行在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可 但如果两个进程在同一时刻对同一个邮箱投递邮件，会发生什么情况?显然，邮箱的数据会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。设计良好的邮箱投递系统会通过锁(lock)来防止数据损坏。如果客户试图投递邮件,而邮箱已经被其他客户锁住那就必须等待，直到锁释放才能进行投递。

这种锁的方案在实际应用环境中虽然工作良好，但并不支持并发处理。因为在任意一个时刻，只有一个进程可以修改邮箱的数据，这在大容量的邮箱系统中是个问题。

1.2.1 读写锁

从邮箱中读取数据没有这样的麻烦，即使同一时刻多个用户并发读取也不会有什么问题。因为读取不会修改数据，所以不会出错。但如果某个客户正在读取邮箱，同时另外一个用户试图删除编号为 25 的邮件，会产生什么结果?结论是不确定，读的客户可能会报错退出，也可能读取到不一致的邮箱数据。所以，为安全起见，即使是读取邮箱也需要特别注意。如果把上述的邮箱当成数据库中的一张表，把邮箱当成表中的一行记录，就很容易看出同样的问题依然存在。从很多方面来说，邮箱就是一张简单的数据库表。修改数据库表中的记录，和删除或者修改邮箱中的邮件信息，十分类似。解决这类经典问题的方法就是并发控制，其实非常简单。在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。 这两种类型的锁通常被称为共享锁 (shared lock)和排他锁 (exclusive lock)，也叫读锁 (read lock) 和写锁 (writelock)。这里先不讨论锁的具体实现，描述一下锁的概念如下:读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写人的同资源。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时MySQL会通过锁定防止其他用户读取同一数据。大多数时候，MySOL锁的内部管理都是透明的。

1.2.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。 问题是加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。 大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁 (rowlevel lock)，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。 而MySOL则提供了多种选择每种MySOL存储引擎都可以实现自己的锁策略和锁粒度，在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持好在MySQL支持多个存储引擎的架构，所以不需要单一的通用解决方案。下面将介绍两种最重要的锁策略。

表锁（table lock）

表锁是 MySQL中最基本的锁策略，并且是开销最小的策略。表锁非常类似于前文描述的邮箱加锁机制:它会锁定整张表。一个用户在对表进行写操作(插入、删除、更新等)前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的在特定的场景中，表锁也可能有良好的性能。例如，READ LOCAL 表锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面)。尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE之类的语句使用表锁，而忽略存储引整的锁机制。

行级锁（row lock）

行级锁可以最大程度地支持并发处理 (同时也带来了最大的锁开销)。众所周知，在InnoDB 和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而 MySOL 服务器层(如有必要，请回顾前文的逻辑架构图)没有实现。服务器层完全不了解存储引擎中的锁实现。在本章的后续内容以及全书中，所有的存储引擎都以自己的方式显现了锁机制。

1.3 事务

在理解事务的概念之前，接触数据库系统的其他高级特性还言之过早。事务就是一组原子性的 SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。本节的内容并非专属于 MySOL，如果读者已经熟悉了事务的 ACID 的概念，可以直接跳转到1.3.4节。银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表:支票(checking)表和储蓄 (savings)表。现在要从用户Jane 的支票账户转移200 美元到她的储蓄账户，那么需要至少三个步骤

检查支票账户的余额高于 200 美元。
从支票账户余额中减去 200 美元。
在储蓄账户余额中增加 200 美元。上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用STARTTRANSACTION语开始一个事务，然后要么使用COMMIT提交事务将修改的数据持久保留，要么使用 ROLLBACK 撤销所有的修改。事务 SQL的样本如下:

Untitled

*单纯的事务概念并不是故事的全部。试想一下，如果执行到第四条语句时服务器崩溃了，会发生什么? *天知道，用户可能会损失 200 美元再假如，在执行到第三条语句和第四条语句之间时，另外一个进程要删除支票账户的所有余额，那么结果可能就是银行在不知道这个逻辑的情况下白白给了 Jane 200美元。

*除非系统通过严格的 ACID 测试，否则空谈事务的概念是不够的。 *ACID 表示原子性(atomicity)、一致性(consistency)、隔离性 (isolation)和持性 (durability)。一个运行良好的事务处理系统，必须具备这些标准特征。

原子性(atomicity) 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性(consistency) 数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失 200 美元,因为事务最没有提交,所以事务中所做的修改也不会保存到数据库中。
隔离性(isolation）通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户总程序开始运行，则其看到的支票账户的余额并没有被减去 200 美元。后面我们讨论隔离级别(Isolation level)的时候,会发现为什么我们要说“通常来说”是不可见的。
持久性(durability）一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有能做到 100%的持久性保证的策略 (*如果数据库本身就能做到真正的持久性，那么备份又怎么能增加持久性呢? *)。在后面的一些章节中，我们会继续讨论MySOL 中持久性的真正含义。

事务的ACID特性可以确保银行不会弄丢你的钱。而在应用逻辑中,要实现这一点非常难甚至可以说是不可能完成的任务。 一个兼容 ACID 的数据库系统，需要做很多复杂但可能用户并没有觉察到的工作，才能确保 ACID的实现。就像锁粒度的升级会增加系统开销一样，这种事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。一**个实现了 ACID 的数据库，相比没有实现 ACID 的数据库，通常会需要更强的 CPU 处理能力、更大的内存和更多的磁盘空间。正如本章不断重复的，这也正是 MySOL 的存储引擎架构可以发挥优势的地方**。用户可以根据业务是否需要事务处理，来选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。即使存储引擎不支持事务，也可以通过 LOCK TABLES语句为应用提供一定程度的保护，这些选择用户都可以自主决定。

1.3.1 隔离级别

隔离性其实比想象的要复杂。在 SQL 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITTED (未提交读)

在READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读 (Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用
READ COMMITTED (提交读) 大多数数据库系统的默认隔离级别都是READ COMMITTED (MySQL不是)。READCOMMITTED 满足前面提到的隔离性的简单定义:一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读 (nonrepeatableread)，因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ (可重复读) REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row) 。InnoDB和XtraDB 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了幻读的问题。本章稍后会做进一步的讨论。
SERIALIZABLE(可事行化) SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

表1-1 ANSI SQL 隔离级别

1.3.2 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源 时，就可能会产生死锁多个事务同时锁定同一个资源时，也会产生死锁。例如，设想下面两个事务同时处理StockPrice表:

StockPrice 表

如果凑巧，两个事务都执行了第一条 UPDATE语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条 UPDATE语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统比如InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方式，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚 (这是相对比较简单的死锁回滚算法)。

锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。

死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

1.3.4 MySQL 中的事务

MySQL提供了两种事务型的存储引擎;InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括 XtraDB 和PBXT。后面将详细讨论它们各自的一些特点。

自动提交(AUTOCOMMIT) MySOL默认采用自动提交 (AUTOCOMMIT)模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:

AUTOCAMIIT变来启动或者禁用自动提交模式

1或者0N表示启用，0或者OFF表示禁用。当AUTOCOMMIT= 0 时，所有的查询都是在一个事务中，直到显式地执行 COMMIT提交或者 ROLLBACK 回滚，该事务结束，同时又开始了另一个新事务。修改AUTOCOMMIT对非事务型的表，比如 MyISAM 或者内存表，不会有任何影响。对这类表来说，没有 COMMIT 或者 ROLLBACK的概念，也可以说是相当于一直处于AUTOCOMMIT启用的模式。另外还有一些命令，在执行之前会强制执行 COMMIT提交当前的活动事务。典型的例子在数据定义语言 (DDL)中，如果是会导致大量数据改变的操作，比如 ALTER TABLE就是如此。另外还有 LOCK TABLES 等其他语句也会导致同样的结果。如果有需要，请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。 MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL 命来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别 :

Mysql > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

MySQL能够识别所有的4个 ANSI隔离级别InnoDB引也支持所有的隔离级别。

在事务中混合使用存储引擎 MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中使用多种存储引擎是不可靠的。如果在事务中混合使用了事务型和非事务型的表(如InnoDB和MISAM表)在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不-致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要在非事务型的表上执行事务相关操作的时候，MySOL 通常不会发出提醒，也不会报错有时候只有回滚的时候才会发出一个警告 :“某些非事务型的表上的变更不能被回滚”但大多数情况下，对非事务型表的操作都不会有提示。

隐式和显式锁定 InnoDB 采用的是两阶段锁定协议 (two-phase locking protocol)。在事务执行过中，随时都可以执行锁定，锁只有在执行 COMMIT或者 ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。

另外，InnoDB 也支持通过特定的语句进行显式锁定，这些语句不属于 SOL规范:

SELECT … LOCK IN SHARE MODE
SELECT .. FOR UPDATE

MySOL也支持 LOCK TABLES 和 UNLOCK TABLES语，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。经常可以发现，应用已经将表从MVISAM 转换到InnoDB，但还是显式地使用LOCKTABLES语句。这不但没有必要，还会严重影响性能，实际上InnoDB 的行级锁工作得更好。

1.4 多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制 (MVCC) 。不仅是 MySQL，包括 OraclePostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC 没有一个统一的实现标准。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。 MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。如果之前没有这方面的概念，这句话听起来就有点迷惑。熟悉了以后会发现，这句话其实还是很容易理解的。前面说到不同存储引擎的 MVCC 实现是不同的，典型的有乐观 (optimistic)并发控制和悲观 (pessimistic)并发控制下面我们通过InnoDB 的简化版行为来说明MVCC是如何工作的。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间 (或删除时间)。当然存储的并不是实际的时间值，而是系统版本号 (system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ 隔离级别下，MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录:

InnoDB 只查找版本早于当前事务版本的数据行 (也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号DELETE

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。 MVCC 只在REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为 READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

第2章

MySQL 基准测试

基准测试 (benchmark)是MySQL新手和专家都需要掌握的一项基本技能。简单地说，基准测试是针对系统设计的一种压力测试。通常的目标是为了掌握系统的行为。但也有其他原因，如重现某个系统状态，或者是做新硬件的可靠性测试。本章将讨论MySQL 和基于 MySQL 的应用的基准测试的重要性、策略和工具。我们将特别讨论一下sysbench，这是一款非常优秀的 MySQL基准测试工具。

2.1 为什么需要基准测试

为什么基准测试很重要?因为基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据。基准测试可以在系统实际负载之外创造一些虚构场景进行测试。基准测试可以完成以下工作，或者更多:

验证基于系统的一些假设，确认这些假设是否符合实际情况。
重现系统中的某些异常行为，以解决这些异常。
测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题。
模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展性瓶颈。
规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件需要多大容量的网络，以及其他相关资源。这有助于降低系统升级和重大变更的风险。
测试应用适应可变环境的能力。例如，通过基准测试，可以发现系统在随机的并发峰值下的性能表现，或者是不同配置的服务器之间的性能表现。基准测试也可以测试系统对不同数据分布的处理能力。
测试不同的硬件、软件和操作系统配置。比如 RAID 5 还是 RAID 10 更适合当前的系统?如果系统从ATA硬盘升级到SAN，对于随机写性能有什么助? Linux2.4系列的内核会比26系列的可扩展性更好吗?升级MySOL的版本能改善性能吗?为当前的数据采用不同的存储引擎会有什么效果?所有这类问题都可以通过专门的基准测试来获得答案。
证明新采购的设备是否配置正确。笔者曾经无数次地通过基准测试来对新系统进行压测，发现了很多错误的配置，以及硬件组件的失效等问题。因此在新系统正式上线到生产环境之前进行基准测试是一个好习惯，永远不要相信主机提供商或者硬件供应商的所谓系统已经安装好，并且能运行多快的说法。如果可能，执行实际的基准测试永远是一个好主意。

基准测试还可以用于其他目的，比如为应用创建单元测试套件。但本章我们只关注与性能有关的基准测试。

基准测试的一个主要问题在于其不是真实压力的测试。基准测试施加给系统的压力相对真实压力来说，通常比较简单。真实压力是不可预期而且变化多端的，有时候情况会过于复杂而难以解释。所以使用真实压力测试，可能难以从结果中分析出确切的结论。

基准测试的压力和真实压力在哪些方面不同?有很多因素会影响基准测试，比如数据量数据和查询的分布，但最重要的一点还是基准测试通常要求尽可能快地执行完成，所以经常给系统造成过大的压力。在很多案例中，我们都会调整给测试工具的最大压力，以在系统可以容忍的压力闹值内尽可能快地执行测试，这对于确定系统的最大容量非常有帮助。然而大部分压力测试工具不支持对压力进行复杂的控制。务必要记住，测试工具自身的局限也会影响到结果的有效性。

使用基准测试进行容量规划也要掌握技巧，不能只根据测试结果做简单的推断。例如假设想知道使用新数据库服务器后，系统能够支撑多大的业务增长。首先对原系统进行基准测试，然后对新系统做测试，结果发现新系统可以支持原系统 40 倍的 TPS(每秒事务数)，这时候就不能简单地推断说新系统一定可以支持 40 倍的业务增长。这是因为在业务增长的同时，系统的流量、用户、数据以及不同数据之间的交互都在增长，它们不可能都有 40 倍的支撑能力，尤其是相互之间的关系。而且当业务增长到 40 倍时，应用本身的设计也可能已经随之改变。可能有更多的新特性会上线，其中某些特性可能对数据库造成的压力远大于原有功能。而这些压力、数据、关系和特性的变化都很难模拟所以它们对系统的影响也很难评估结论就是，我们只能进行大概的测试，来确定系统大致的余量有多少。当然可以做一些真实压力测试(和基准测试有区别)，但在构造数据集和压力的时候要特别小心，而且这样就不再是基准测试了。基准测试要尽量简单直接，结果之间容易相互比较，成本低且易于执行。尽管有诸多限制，基准测试还是非常有用的(只要搞清楚测试的原理并且了解如何分析结果所代表的意义)。

2.2 基准测试的策略

基准测试有两种主要的策略:一是针对整个系统的整体测试，另外是单独测试 MySQL这两种策略也被称为集成式(full-stack)以及单组件式 (single-component)基准测试。针对整个系统做集成式测试，而不是单独测试 MySL的原因主要有以下几点 :

测试整个应用系统，包括 Web 服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不仅仅是 MySQL 本身的性能，而是应用整体的性能。
MySQL 并非总是应用的瓶颈，通过整体的测试可以揭示这一点。
只有对应用做整体测试，才能发现各部分之间的缓存带来的影响。
整体应用的集成式测试更能揭示应用的真实表现，而单独组件的测试很难做到这一点。

另外一方面，应用的整体基准测试很难建立，甚至很难正确设置。如果基准测试的设计有问题，那么结果就无法反映真实的情况，从而基于此做的决策也就可能是错误的。

不过，有时候不需要了解整个应用的情况，而只需要关注 MySOL的性能，至少在项目初期可以这样做。基于以下情况，可以选择只测试MySQL:

需要比较不同的schema或查询的性能
针对应用中某个具体问题的测试。
为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的“周期循环”来检测出某些调整后的效果。

另外，如果能够在真实的数据集上执行重复的查询，那么针对 MySQL 的基准测试也是有用的，但是数据本身和数据集的大小都应该是真实的。如果可能，可以采用生产环境的数据快照。

不幸的是，设置一个基于真实数据的基准测试复杂而且耗时。如果能得到一份生产数据集的拷贝，当然很幸运，但这通常不太可能。比如要测试的是一个刚开发的新应用，它只有很少的用户和数据。如果想测试该应用在规模扩张到很大以后的性能表现，就只能通过模拟大量的数据和压力来进行。

2.2.1 测试何种指标

在开始执行甚至是在设计基准测试之前，需要先明确测试的目标。测试目标决定了选择什么样的测试工具和技术，以获得精确而有意义的测试结果。可以将测试目标细化为一系列的问题，比如，“这种 CPU 是否比另外一种要快?”，或“新索引是否比当前索引性能更好?” 有时候需要用不同的方法测试不同的指标。比如，针对延迟 (latency) 和吞吐量(throughput)就需要采用不同的测试方法请考虑以下指标，看看如何满足测试的需求。

吞吐量

吞吐量指的是单位时间内的事务处理数。这一直是经典的数据库应用测试指标。些标准的基准测试被广泛地引用，如TPC-C (参考 http://wwwtporg)，而且很多数据库厂商都努力争取在这些测试中取得好成绩。这类基准测试主要针对在线事务处理(OLTP)的吞吐量，非常适用于多用户的交互式应用。常用的测试单位是每秒事务数 (TPS)，有些也采用每分钟事务数 (TPM)响应时间或者延迟这个指标用于测试任务所需的整体时间。

根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。根据不同的时间单位可以计算出平均响应时间、最小响应时间、最大响应时间和所占百分比。最大响应时间通常意义不大，因为测试时间越长，最大响应时间也可能越大。而且其结果通常不可重复，每次测试都可能得到不同的最大响应时间。因此，通常可以使用百分比响应时间 (percentile responsetime)来替代最大响应时间。例如，如果95%的响应时间都是5 毫秒，则表示任务在95%的时间段内都可以在 5毫秒之内完成使用图表有助于理解测试结果。可以将测试结果绘制成折线图 (比如平均值折线或者 95% 百分比折线)或者散点图，直观地表现数据结果集的分布情况。通过这些图可以发现长时间测试的趋势。本章后面将更详细地讨论这一点。

并发性

并发性是一个非常重要又经常被误解和误用的指标。例如，它经常被表示成多少用户在同一时间浏览一个 Web 站点，经常使用的指标是有多少个会话注1。然而，HTTP协议是无状态的，大多数用户只是简单地读取浏览器上显示的信息，这并不等同于Web 服务器的并发性。而且，Web 服务器的并发性也不等同于数据库的并发性，而仅仅只表示会话存储机制可以处理多少数据的能力。Web 服务器的并发性更准确的度量指标，应该是在任意时间有多少同时发生的并发请求。

注1: 特别是一些论坛软件，已经让很多管理员错误地相信同时有成千上万的用户正在同时访问网站。

在应用的不同环节都可以测量相应的并发性。Web 服务器的高并发，一般也会导致数据库的高并发，但服务器采用的语言和工具集对此都会有影响。注意不要将创建数据库连接和并发性搞混淆。一个设计良好的应用，同时可以打开成百上千个MySQL 数据库服务器连接，但可能同时只有少数连接在执行查询。所以说，一个Web站点“同时有 50 000个用户”访问，却可能只有 10~15个并发请求到MySOL数据库。换句话说，并发性基准测试需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数。当并发性增加时，需要测量吞吐量是否下降，响应时间是否变长，如果是这样，应用可能就无法处理峰值压力。并发性的测量完全不同于响应时间和吞吐量。它不像是一个结果，而更像是设置基准测试的一种属性。并发性测试通常不是为了测试应用能达到的并发度，而是为了测试应用在不同并发下的性能。当然，数据库的并发性还是需要测量的。可以通过sysbench 指定32、6或者 128 个线程的测试，然后在测试期间记录MSOL 数据库的Threads running状态值。在第11 章将讨论这个指标对容量规划的影响。

可扩展性

在系统的业务压力可能发生变化的情况下，测试可扩展性就非常必要了。第 11 章将更进一步讨论可扩展性的话题。简单地说，可扩展性指的是，给系统增加一倍的工作，在理想情况下就能获得两倍的结果 (即吞量增加一倍) 。或者说，给系统增加一倍的资源 (比如两倍的 CPU 数)就可以获得两倍的吞量。当然，同时性能 (响应时间)也必须在可以接受的范围内。大多数系统是无法做到如此理想的线性扩展的。随着压力的变化，吞吐量和性能都可能越来越差。可扩展性指标对于容量规范非常有用，它可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈。比如，如果系统是基于单个用户的响应时间测试(这是一个很糟糕的测试策略)设计的，虽然测试的结果很好，但当并发度增加时，系统的性能有可能变得非常糟糕。而一个基于不断增加用户连接的情况下的响应时间测试则可以发现这个问题。一些任务，比如从细粒度数据创建汇总表的批量工作，需要的是周期性的快速响应时间。当然也可以测试这些任务纯粹的响应时间，但要注意考虑这些任务之间的相互影响。批量工作可能导致相互之间有影响的查询性能变差，反之亦然。归根结底，应该测试那些对用户来说最重要的指标。因此应该尽可能地去收集一些需求比如，什么样的响应时间是可以接受的，期待多少的并发性，等等。然后基于这些需求来设计基准测试，避免目光短浅地只关注部分指标，而忽略其他指标。

2.3 基准测试方法

在了解基本概念之后，现在可以来具体讨论一下如何设计和执行基准测试。但在讨论如何设计好的基准测试之前，先来看一下如何避免一些常见的错误，这些错误可能导致测试结果无用或者不精确:

使用真实数据的子集而不是全集。例如应用需要处理几百 GB 的数据，但测试只有1GB 数据，或者只使用当前数据进行测试，却模来大增后的情况。
使用错误的数据分布。例如使用均匀分布的数据测试，而系统的真实数据有很多热点区域(随机生成的测试数据通常无法模拟真实的数据分布)。使用不真实的分布参数，例如假定所有用户的个人信息 (profile) 都会被平均地读2
在多用户场景中，只做单用户的测试。
在单服务器上测试分布式应用。
与真实用户行为不匹配。例如 Web 页面中的“思考时间”。真实用户在请求到一个页面后会阅读一段时间，而不是不停顿地一个接一个点击相关链接。
反复执行同一个查询。真实的查询是不尽相同的，这可能会导致缓存命中率降低。而反复执行同一个查询在某种程度上，会全部或者部分存结果。
没有检查错误。如果测试的结果无法得到合理的解释，比如一个本应该很慢的查询突然变快了，就应该检查是否有错误产生。否则可能只是测试了 MySOL 检测语法错误的速度了。基准测试完成后，一定要检查一下错误日志，这应当是基本的要求。
忽略了系统预热 (warm up)的过程。例如系统重启后马上进行测试。有时候需要了解系统重启后需要多长时间才能达到正常的性能容量，要特别留意预热的时长反过来说，如果要想分析正常的性能，需要注意，若基准测试在重启以后马上启动则缓存是冷的、还没有数据，这时即使测试的压力相同，得到的结果也和缓存已经装满数据时是不同的。
使用默认的服务器配置。第3 章将详细地讨论服务器的优化配置测试时间太短。基准测试需要持续一定的时间。后面会继续讨论这个话题。
只有避免了上述错误，才能走上改进测试质量的漫漫长路。

如果其他条件相同，就应努力使测试过程尽可能地接近真实应用的情况。当然，有时候和真实情况稍有些出入问题也不大。例如，实际应用服务器和数据库服务器分别部署在不同的机器。如果采用和实际部署完全相同的配置当然更真实，但也会引入更多的变化因素，比如加入了网络的负载和速度等。而在单一节点上运行测试相对要容易，在某些情况下结果也可以接受，那么就可以在单一节点上进行测试。当然，这样的选择需要根据实际情况来分析是否合适。

2.3.1 设计和规划基准测试

规划基准测试的第一步是提出问题并明确目标。然后决定是采用标准的基准测试，还是设计专用的测试。

如果采用标准的基准测试，应该确认选择了合适的测试方案。例如，不要使用 TPC-H 测试电子商务系统。在 TPC的定义中，“TPC-H 是即席查询和决策支持型应用的基准测试因此不适合用来测试OLTP 系统。

设计专用的基准测试是很复杂的，往往需要一个迭代的过程。首先需要获得生产数据集的快照，并且该快照很容易还原，以便进行后续的测试。

然后，针对数据运行查询。可以建立一个单元测试集作为初步的测试，并运行多遍。但是这和真实的数据库环境还是有差别的。更好的办法是选择一个有代表性的时间段，比如高峰期的一个小时，或者一整天，记录生产系统上的所有查询。如果时间段选得比较小，则可以选择多个时间段。这样有助于覆盖整个系统的活动状态，例如每周报表的查询、或者非峰值时间运行的批处理作业。

可以在不同级别记录查询。例如，如果是集成式 (full-stack)基准测试，可以记录 Web服务器上的HTTP 请求，也可以打开 MySQL 的查询日志(Query Log)。倘若要重演这些查询，就要确保创建多线程来并行执行，而不是单个线程线性地执行。对日志中的每个连接都应该创建独立的线程，而不是将所有的查询随机地分配到一些线程中。查询日志中记录了每个查询是在哪个连接中执行的即使不需要创建专用的基准测试，详细地写下测试规划也是必需的。测试可能要多次反复运行，因此需要精确地重现测试过程。而且也应该考虑到未来，执行下一轮测试时可能已经不是同一个人了。即使还是同一个人，也有可能不会确切地记得初次运行时的情况。测试规划应该测试数据、系统配置的步骤、如何测量和分析结果，以及预热的方案等。

应该建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录。文档规范可以很简单，比如采用电子表格 (spreadsheet)或者记事本形式，也可以是复杂的自定义的数据库。需要记住的是，经常要写一些脚本来分析测试结果，因此如果能够不用打开电子表格或者文本文件等额外操作，当然是更好的。

2.3.2 基准测试应该运行多长时间

基准测试应该运行足够长的时间，这一点很重要。如果需要测试系统在稳定状态时的性能，那么当然需要在稳定状态下测试并观察。而如果系统有大量的数据和内存，要达到稳定状态可能需要非常长的时间。大部分系统都会有一些应对突发情况的余量，能够吸收性能尖峰，将一些工作延迟到高峰期之后执行。但当对机器加压足够长时间之后，这些余量会被消耗尽，系统的短期尖峰也就无法维持原来的高性能。

有时候无法确认测试需要运行多长的时间才足够。如果是这样，可以让测试一直运行持续观察直到确认系统已经稳定。下面是一个在已知系统上执行测试的例子，图 2-1显示了系统磁盘读和写吞吐量的时序图。

图2-1：扩展基准测试的I/O性能图

系统预热完成后，读I/0 活动在三四个小时后曲线趋向稳定，但写I/0 至少在八小时内变化还是很大，之后有一些点的波动较大，但读和写总体来说基本稳定了生。一个简单的测试规则，就是等系统看起来稳定的时间至少等于系统预热的时间。本例中的测试持续了72个小时才结束，以确保能够体现系统长期的行为。

一个常见的错误的测试方式是，只执行一系列短期的测试，比如每次 60 秒，并在此测试的基础上去总结系统的性能。我们经常可以听到类似这样的话:“我尝试对新版本做了测试，但还不如旧版本快”，然而我们分析实际的测试结果后发现，测试的方式根本不足以得出这样的结论。有时候人们也会强调说不可能有时间去测试 8 或者 12个小时以验证 10 个不同并发性在两到三个不同版本下的性能。如果没有时去完成准确完整的基准测试，那么已经花费的所有时间都是一种浪费。有时候要相信别人的测试结果这总比做一次半拉子的测试来得到一个错误的结论要好。

2.3.3 获取系统性能和状态

在执行基准测试时，需要尽可能多地收集被测试系统的信息。最好为基准测试建立一个目录，并且每执行一轮测试都创建单独的子目录，将测试结果、配置文件、测试指标、脚本和其他相关说明都保存在其中。即使有些结果不是目前需要的，也应该先保存下来。多余一些数据总比缺乏重要的数据要好，而且多余的数据以后也许会用得着。需要记录的数据包括系统状态和性能指标，诸如 CPU 使用率、磁盘 I/0、网络流量统计、SHOWGLOBAL STATUS 计数器等。

下面是一个收集 MySQL 测试数据的 shell 脚本 :

Untitled

这个 shell 脚本很简单，但提供了一个有效的收集状态和性能数据的框架。看起来好像作用不大，但当需要在多个服务器上执行比较复杂的测试的时候，要回答以下关于系统行为的问题，没有这种脚本的话就会很困难了。下面是这个脚本的一些要点: 迭代是基于固定时间间隔的，每隔 5 秒运行一次收集的动作，注意这里 sleep 的时间有一个特殊的技巧。如果只是简单地在每次循环时插入一条“sleep 5”的指令，循环的执行间隔时间一般都会稍大于 5 秒，那么这个脚本就没有办法通过其他脚本和图形简单地捕获时间相关的准确数据。即使有时候循环能够恰好在 5 秒内完成，但如果某些系统的时间戳是 15:32:18.218192，另外一个则是 15:32:23.819437，这时候就比较讨厌了。当然这里的 5 秒也可以改成其他的时间间隔，比如 1、1030 或者60秒。不过还是推荐使用5或者 10秒的间隔来集数据。
每个文件名都包含了该轮测试开始的日期和小时。如果测试要持续好几天，那么这个文件可能会非常大，有必要的话需要手工将文件移到其他地方，但要分析全部结果的时候要注意从最早的文件开始。如果只需要分析某个时间点的数据，则可以根据文件名中的日期和小时迅速定位，这比在一个 GB 以上的大文件中去搜索要快捷得多。
每次抓取数据都会先记录当前的时间戳，所以可以在文件中搜索某个时间点的数据也可以写一些awk 或者 sed 脚本来简化操作。
这个脚本不会处理或者过滤收集到的数据。先收集所有的原始数据，然后再基于此做分析和过滤是一个好习惯。
如果在收集的时候对数据做了预处理，而后续分析发现一些异常的地方需要用到更多的原始数据，这时候就要“抓瞎”了。
如果需要在测试完成后脚本自动退出，只需要删除 /home/benchmarks/running 文件即可。

这只是一段简单的代码，或许不能满足全部的需求，但却很好地演示了该如何捕获测试的性能和状态数据。从代码可以看出，只捕获了 MySQL 的部分数据，如果需要，则很容易通过修改脚本添加新的数据捕获。例如，可以通过 pt-diskstats 工具捕获/proc/diskstats 的数据为后续分析磁盘IO使用。

2.3.4 获得准确的测试结果

获得准确测试结果的最好办法，是回答一些关于基准测试的基本问题:是否选择了正确的基准测试?是否为问题收集了相关的数据?是否采用了错误的测试标准?例如，是否对一个I/0密集型(I/0-bound)的应用采用了CPU密集型(CPU-bound)的测试标准来评估性能? 接着，确认测试结果是否可重复。每次重新测试之前要确保系统的状态是一致的。如果是非常重要的测试，甚至有必要每次测试都重启系统。一般情况下，需要测试的是经过预热的系统，还需要确保预热的时间足够长(请参考前面关于基准测试需要运行多长时间的内容)是否可重复。如果预热用的是随机查询那么测试结果可能就是不可重复的。

如果测试的过程会修改数据或者 schema，那么每次测试前，需要利用快照还原数据。在

表中插人 1000 条记录和插入 100 万条记录，测试结果肯定不会相同。数据的碎片度和在磁盘上的分布，都可能导致测试是不可重复的。一个确保物理磁盘数据的分布尽可能一致的办法是，每次都进行快速格式化并进行磁盘分区复制。要注意很多因素，包括外部的压力、性能分析和监控系统、详细的日志记录、周期性作业，以及其他一些因素，都会影响到测试结果。一个典型的案例，就是测试过程中突然有cron定时作业启动，或者正处于一个巡查读取周期 (Patrol Read cycle)，抑或RAID卡启动了定时的一致性检查等。要确保基准测试运行过程中所需要的资源是专用于测试的。如果有其他额外的操作，则会消耗网络带宽，或者测试基于的是和其他服务器共享的 SAN存储，那么得到的结果很可能是不准确的。每次测试中，修改的参数应该尽量少。如果必须要一次修改多个参数，那么可能会丢失一些信息。有些参数依赖其他参数，这些参数可能无法单独修改。有时候其至都没有意识到这些依赖，这给测试带来了复杂性。一般情况下，都是通过迭代逐步地修改基准测试的参数，而不是每次运行时都做大量的修改。举个例子，如果要通过调整参数来创造一个特定行为，可以通过使用分治法(divide-and-conquer，每次运行时将参数对分减半)来找到正确的值。

另外，基于MySQL的默认配置的测试没有什么意义，因为默认配置是基于消耗很少内存的极小应用的。有时候可以看到一些 MySQL 和其他商业数据库产品的对比测试，结果很让人尴尬，可能就是 MySQL 采用了默认配置的缘故。让人无语的是，这样明显有误的测试结果还容易变成头条新闻固态存储 (SSD 或者 PCL-E 卡)给基准测试带来了很大的挑战，第9章将进一步讨论最后，如果测试中出现异常结果，不要轻易当作坏数据点而丢弃。应该认真研究并找到产生这种结果的原因。测试可能会得到有价值的结果，或者一个严重的错误，抑或基准测试的设计缺陷。如果对测试结果不了解，就不要轻易公布。有一些案例表明，异常的测试结果往往都是由于很小的错误导致的，最后搞得测试无功而返。

2.3.5 运行基准测试并分析结果

一旦准备就绪，就可以着手基准测试，收集和分析数据了。

通常来说，自动化基准测试是个好主意。这样做可以获得更精确的测试结果。因为自动化的过程可以防止测试人员偶尔遗某些步骤，或者误操作。另外也有助于归档整个测试过程。

自动化的方式有很多，可以是一个 Makefile 文件或者一组脚本。脚本语言可以根据需要选择;shel、PHP、Perl 等都可以。要尽可能地使所有测试过都自动化，包括装载数据系统预热、执行测试、记录结果等。

基准测试通常需要运行多次。具体需要运行多少次要看对结果的记分方式，以及测试的重要程度。要提高测试的准确度，就需要多运行几次。一般在测试的实践中，可以取最好的结果值，或者所有结果的平均值，抑或从五个测试结果里取最好三个值的平均值可以根据需要更进一步精确化测试结果。还可以对结果使用统计方法，确定置信区间(confidence interval)等。不过通常来说，不会用到这种程度的确定性结果。只要测试的结果能满足目前的需求，简单地运行几轮测试，看看结果的变化就可以了。如果结果变化很大，可以再多运行几次，或者运行更长的时间，这样都可以获得更确定的结果。

获得测试结果后，还需要对结果进行分析，也就是说，要把“数字”变成“知识”。最终的目的是回答在设计测试时的问题。理想况下，可以获得诸如“升级到4核 CPU可以在保持响应时间不变的情况下获得超过 50%的增”或者“增加索引可以使询更快”的结论。如果需要更加科学化，建议在测试前读读 null hypothesis 一书，但大部分情况下不会要求做这么严格的基准测试。

如何从数据中抽象出有意义的结果，依赖于如何收集数据。通常需要写一些脚本来分析数据，这不仅能减轻分析的工作量，而且和自动化基准测试一样可以重复运行，并易于文档化。下面是一个非常简单的 shell 脚本，演示了如何从前面的数据采集脚本采集到的数据中抽取时间维度信息。脚本的输入参数是采集到的数据文件的名字。

Untitled

假设该脚本名为 analyze，当前面的脚本生成状态文件以后，就可以运行该脚本，可能会得到如下的结果:

Untitled

第一行是列的名字，第二行的数据应该忽略，因为这是测试实际启动前的数据。接下来的行包含 Unix 时间戳、日期、时间 (注意时间数据是每秒更新一次，前面脚本说明时曾提过)、系统负载、数据库的 QPS(每秒查询次数)五列，这应该是用于分析系统性能的最少数据需求了。接下来将演示如何根据这些数据快速地绘成图形，并分析基准测试过程中发生了什么。

2.3.6 绘图的重要性

如果你想要统治世界，就必须不断地利用“阴谋””。而最简单有效的图形，就是将性能指标按照时间顺序绘制。通过图形可以立刻发现一些问题，而这些问题在原始数据中却很难被注意到。或许你会坚持看测试工具打印出来的平均值或其他汇总过的信息，但平均值有时候是没有用的，它会掩盖掉一些真实情况。幸运的是，前面写的脚本的输出都可以定制作为gnuplot 或者 R 绘图的数据来源。假设使用gnuplot，假设输出的数据文件名是OPS-per-5-seconds :

gnuplot> plot"QPS-per-5-seconds” using 5 w lines title "QPS

该gnuplot命令文件的第五列qps 数据绘成图形，图的标题是 QPS。图2-2是绘制出来的结果图

第3章

服务器性能剖析

在我们的技术咨询生涯中，最常碰到的三个性能相关的服务请求是:如何确认服务器是否达到了性能最佳的状态、找出某条语为什么执行不够快，以及诊断被用户描述成“停顿”“堆积”或者“卡死”的某些间歇性疑难故障。本章将要针对这三个问题做出解答我们将提供一些工具和技巧来优化整机的性能、优化单条语句的执行速度，以及诊断或者解决那些很难观察到的问题 (这些问题用户往往很难知道其根源，有时候甚至都很难察觉到它的存在)

这看起来是个艰巨的任务，但是事实证明，有一个简单的方法能够从噪声中发现苗头。这个方法就是*专注于测量服务器的时间花费在哪里，使用的技术则是性能剖析(profiling) *。在本章，我们将展示如何测量系统并生成剖析报告，以及如何分析系统的整个堆栈 (stack)，包括从应用程序到数据库服务器到单个查询。

首先我们要保持空杯精神，抛弃掉一些关于性能的常见的误解。这有一定的难度，下面我们一起通过一些例子来说明问题在哪里。

3.1 性能优化简介

问 10个人关于性能的问题，可能会得到 10个不同的回答，比如“每秒查询次数”“CPU利用率”“可扩展性”之类。这其实也没有问题，每个人在不同场景下对性能有不同的理解，但本章将给性能一个正式的定义。我们将性能定义为完成某件任务所需要的时间度量，换句话说，性能即响应时间，这是一个非常重要的原则。我们通过任务和时间而不是资源来测量性能。数据库服务器的目的是执行 SQL 语，所以它关注的任务是查询或者语句，如SELECT、UPDATE、DELETE等数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

还有另外一个问题:什么是优化?我们暂时不讨论这个问题，而是假设性能优化就是在一定的工作负载下尽可能地降低响应时间。

很多人对此很迷茫。假如你认为性能优化是降低 CPU 利用率，那么可以减少对资源的使用。但这是一个陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度。很多时候将使用老版本InnoDB引擎的MySQL升级到新版本后，CPU利用率会上升得很厉害，这并不代表性能出现了问题，反而说明新版本的InnoDB 对资源的利用率上升了。查询的响应时间则更能体现升级后的性能是不是变得更好。版本升级有时候会带来一些 bug，比如不能利用某些索引从而导致 CPU 利用率上升。CPU 利用率只是一种现象，而不是很好的可度量的目标。

同样，如果把性能优化仅仅看成是提升每秒查询量，这其实只是吞吐量优化。吞吐量的提升可以看作性能优化的副产品。对查询的优化可以让服务器每秒执行更多的查询,因为每条查询执行的时间更短了 (吞的定义是单位时间内的询数量，这正好是我们对性能的定义的倒数)。所以如果目标是降低响应时间，那么就需要理解为什么服务器执行查询需要这么多时间然后去减少或者消除那些对获得查询结果来说不必要的工作。也就是说，先要搞清楚时间花在哪里。这就引申出优化的第二个原则:无法测量就无法有效地优化。所以第一步应该测量时间花在什么地方。

我们观察到，很多人在优化时，都将精力放在修改一些东西上，却很少去进行精确的测量。我们的做法完全相反，将花费非常多，甚至 90% 的时间来测量响应时间花在哪里。如果通过测量没有找到答案，那要么是测量的方式错了，要么是测量得不够完整。如果测量了系统中完整而且正确的数据，性能问题一般都能暴露出来，对症下药的解决方案也就比较明了。测量是一项很有挑战性的工作，并且分析结果也同样有挑战性，测出时间花在哪里，和知道为什么花在那里，是两码事。

前面提到要合适的测量范围，这是什么意思呢?合适的测量范围是说只测量需要优化的活动。有两种比较常见的情况会导致不合适的测量 :

在错误的时间启动和停止测量。
测量的是聚合后的信息，而不是目标活动本身。

例如，一个常见的错误是先查看慢查询，然后又去排查整个服务器的情况来判断问题在哪里。如果确认有慢查询，那么就应该测量慢查询，而不是测量整个服务器。测量的应该是从慢查询的开始到结束的时间，而不是查询之前或查询之后的时间。

完成一项任务所需要的时间可以分成两部分:执行时间和等待时间。如果要优化任务的执行时间，最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或者提升子任务的效率。而优化任务的等待时间则相对要复杂一些，因为等待有可能是由其他系统间接影响导致，任务之间也可能由于争用磁盘或者 CPU 资源而相互影响。根据时间是花在执行还是等待上的不同，诊断也需要不同的工具和技术。

刚才说到需要定位和优化子任务，但只是一笔带过。一些运行不频繁或者很短的子任务对整体响应时间的影响很小，通常可以忽略不计。那么如何确认哪些子任务是优化的目标呢?这个时候性能剖析就可以派上用场了。

Untitled

3.1.1 通过性能剖析进行优化

一旦掌握并实践面向响应时间的优化方法，就会发现需要不断地对系统进行性能剖析(profiling)。

性能剖析是测量和分析时间花费在哪里的主要方法。性能剖析一般有两个步骤:测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务排到前面。

性能剖析工具的工作方式基本相同。在任务开始时启动计时器，在任务结束时停止计时器，然后用结束时间减去启动时间得到响应时间。也有些工具会记录任务的父任务。这些结果数据可以用来绘制调用关系图，但对于我们的目标来说更重要的是，可以将相似的任务分组并进行汇总。对相似的任务分组并进行汇总可以帮助对那些分到一组的任务做更复杂的统计分析，但至少需要知道每一组有多少任务，并计算出总的响应时间。通过性能剖析报告 (profile report) 可以获得需要的结果。性能剖析报告会列出所有任务列表。每行记录一个任务，包括任务名、任务的执行时间、任务的消耗时间、任务的平均执行时间，以及该任务执行时间占全部时间的百分比。性能剖析报告会按照任务的消耗时间进行降序排序。为了更好地说明，这里举一个对整个数据库服务器工作负载的性能剖析的例子，主要输出的是各种类型的查询和执行查询的时间。这是从整体的角度来分析响应时间，后面会演示其他角度的分析结果。下面的输出是用 Percona Toolkit 中的 pt-query-digest (实际上就是著名的 Maatkit 工具中的 mk-query-digest)分析得到的结果。为了显示方便，对结果做了一些微调，并且只截取了前面几行结果 :

Untitled

上面只是性能剖析结果的前几行，根据总响应时间进行排名，只包括剖析所需要的最小列组合。每一行都包括了查询的响应时间和占总时间的百分比、查询的执行次数、单次执行的平均响应时间，以及该查询的摘要。通过这个性能剖析可以很清楚地看到每个查询相互之间的成本比较，以及每个查询占总成本的比较。在这个例子中，任务指的就是查询，实际上在分析MySOL的时候经常都指的是查询。我们将实际地讨论两种类型的性能剖析:基于执行时间的分析和基于等待的分析。基于执行时间的分析研究的是什么任务的执行时间最长，而基于等待的分析则是判断任务在什么地方被阻塞的时间最长。

如果任务执行时间长是因为消耗了太多的资源且大部分时间花费在执行上，等待的时间不多，这种情况下基于等待的分析作用就不大。反之亦然，如果任务一直在等待，没有消耗什么资源，去分析执行时间就不会有什么结果。如果不能确认问题是出在执行还是等待上，那么两种方式都需要试试。后面会给出详细的例子。

事实上，当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析一下，可能会发现某些“执行时间”实际上是在等待。例如，上面简单的性能剖析的输出显示表 InvitesNew上的SELECT 查询花费了大量时间，如果深入研究，则可能发现时间都花费在等待I/O完成上。

在对系统进行性能剖析前，必须先要能够进行测量，这需要系统可测量化的支持。可测量的系统一般会有多个测量点可以捕获并收集数据，但实际系统很少可以做到可测量化大部分系统都没有多少可测量点，即使有也只提供一些活动的计数，而没有活动花费的时间统计。MySQL就是一个典型的例子，直到版本5.5才第一次提供了PerformanceSchema，其中有一些基于时间的测量点，而版本5.1及之前的版本没有任何基于时的测量点。能够从MySQL收集到的服务器操作的数据大多是 show status计数器的形式这些计数器统计的是某种活动发生的次数。这也是我们最终决定创建 Percona Server的主要原因，Percona Server 从版本 5.0开始提供很多更详细的查询级别的测量点。

虽然理想的性能优化技术依赖于更多的测量点，但幸运的是，即使系统没有提供测量点也还有其他办法可以展开优化工作。因为还可以从外部去测量系统，如果测量失败，也可以根据对系统的了解做出一些靠谱的猜测。但这么做的时候一定要记住，不管是外部测量还是猜测，数据都不是百分之百准确的，这是系统不透明所带来的风险。

举个例子，在 Percona Server 5.0 中，慢查询日志揭露了一些性能低下的原因，如磁盘I0等待或者行级锁等待。如果日志中显示一条查询花费 10 秒，其中9.6 秒在等待 I0那么追究其他 4% 的时间花费在哪里就没有意义，磁盘 I/0 才是重要的原因。

3.1.2 理解性能剖析

MySQL 的性能剖析 (profile) 将最重要的任务展示在前面，但有时候没显示出来的信息也很重要。可以参考一下前面提到过的性能剖析的例子。不幸的是，尽管性能剖析输出了排名、总计和平均值，但还是有很多需要的信息是缺失的，如下所示。

值得优化的查询 (worthwhile query)

性能剖析不会自动给出哪些查询值得花时间去优化。这把我们带回到优化的本意如果你读过 Cary Millsap 的书，对此就有更多的理解。这里我们要再次强调两点第一，一些只占总响应时间比重很小的查询是不值得优化的。根据*阿姆达尔定律(Amdahl’s Law)对一个占总响应时间不超过5%的查询进行优化，无论如何努力收益也不会超过 5% *。第二，如果花费了 1 000 美元去优化一个任务，但业务的收入没有任何增加，那么可以说反而导致业务被逆优化了 1 000 美元。如果优化的成本大于收益，就应当停止优化。

异常情况

某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验。因为其执行频率低，所以总的响应时间占比并不突出。

未知的未知

一款好的性能剖析工具会显示可能的“丢失的时间”。丢失的时间指的是任务的总时间和实际测量到的时间之间的差。例如，如果处理器的 CPU 时间是 10 秒，而剖析到的任务总时间是 9.7 秒，那么就有 300 毫秒的丢时间。这可能是有些任务没有测量到，也可能是由于测的误差和精度问题的缘故。如果工具发现了这类问题则要引起重视，因为有可能错过了某些重要的事情。使性能剖没有发现丢失时间也需要注意考虑这类问题存在的可能性，这样才不会错过重要的信息。我们的例子中没有显示丢失的时间，这是我们所使用工具的一个局限性。

被掩藏的细节

性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的，它会隐藏很多信息，而且无法表达全部情况。Peter 经常举例说医院所有病人的平均体温没有任何价值。假如在前面的性能剖的例子的第一项中如有两次查的响应时间是1 秒，而另外 1271 次的应时间是几十微秒，结果只从平值里是无法发现两次 1秒的询的。要做出最好的决策，需要为性能剖析里输出的这一行中包含的 12 773 次查询提供更多的信息，尤其是更多响应时间的信息，比如直方图百分比、标准差、偏差指数等。

好的工具可以自动地获得这些信息。实际上，pt-query-digest 就在剖的结果里包含了很多这类细节信息，并且输出在剖析报告中。对此我们做了简化，可以将精力集中在重要而基础的例子上:通过排序将最昂贵的任务排在前面。本章后面会展示更多丰富而有用的性能剖析的例子。

在前面的性能剖析的例子中，还有一个重要的缺失，就是无法在更高层次的堆栈中进行交互式的分析。当我们仅仅着眼于服务器中的单个查询时，无法将相关查询联系起来也无法理解这些查询是否是同一个用户交互的一部分。性能剖析只能管中窥豹，而无法将剖析从任务扩展至事务或者页面查看 (page view)的别有一些办法可以解决这个问题，比如给查询加上特殊的注释作为标签，可以标明其来源并据此做聚合，也可以在应用层面增加更多的测量点，这是下一节的主题。

3.2 对应用程序进行性能剖析

对任何需要消耗时间的任务都可以做性能剖析，当然也包括应用程序。实际上，剖析应用程序一般比剖析数据库服务器容易，而且回报更多。虽然前面的演示例子都是针对。

MySQL服务器的剖析，但对系统进行性能剖析还是建议自上而下地进行，这样可以追踪自用户发起到服务器响应的整个流程。虽然性能问题大多数情况下都和数据库有关但应用导致的性能问题也不少。性能瓶颈可能有很多影响因素 :

外部资源，比如调用了外部的 Web 服务或者搜索引擎。
应用需要处理大量的数据，比如分析一个超大的XML 文件。
在循环中执行昂贵的操作，比如滥用正则表达式。
使用了低效的算法，比如使用暴力搜索算法 (naive search algorithm)来查找列表中的项。

幸运的是，确定 MySQL 的问题没有这么复杂，只需要一款应用程序的剖析工具即可 (作为回报，一旦拥有这样的工具，就可以从一开始就写出高效的代码) 建议在所有的新项目中都考虑包含性能剖析的代码。往已有的项目中加入性能剖析代码也许很困难，新项目就简单一些。

性能剖析本身会导致服务器变慢吗?

说“是的”，是因为性能剖析确实会导致应用慢一点 ;说“不是”，是因为性能剖析可以帮助应用运行得更快。先别急，下面就解释一下为什么这么说。

性能剖析和定期检测都会带来额外开销。问题在于这部分的开销有多少，并且由此获得的收益是否能够抵消这些开销。

大多数设计和构建过高性能应用程序的人相信，应该尽可能地测量一切可以测量的地方，并且接受这些测量带来的额外开销，这些开销应该被当成应用程序的一部分。Oracle 的性能优化大师 Tom Kyte 曾被问到 Oracle 中的测量点的开销,他的回答是测量点至少为性能优化贡献了 10% 。对此我们深表赞同，而且大多数应用并不需要每天都运行详细的性能测量，所以实际贡献甚至要超过 10% 。即使不同意这个观点，为应用构建一些可以永久使用的轻量级的性能剖析也是有意义的。如果系统没有每天变化的性能统计，则碰到无法提前预知的性能瓶颈就是一件头痛的事情。发现问题的时候，如果有历史数据，则这些历史数据价值是无限的。而且性能数据还可以帮助规划好硬件采购、资源分配，以及预测周期性的性能尖峰。

那么何谓“轻量级”的性能剖析?比如可以为所有 SQL 语句计时，加上脚本总时间统计，这样做的代价不高，而且不需要在每次页面查看 (page view) 时都执行。如果流量趋势比较稳定，随机采样也可以，随机采样可以通过在应用程序中设置实现:

<?php
$profiling_enabled = rand(0，100) > 99;
?>

这样只有 1% 的会话会执行性能采样，来帮助定位一些严重的问题。这种策略在生产环境中尤其有用，可以发现一些其他方法无法发现的问题。

几年前在写作本书的第二版的时候，流行的 Web 编语言和架中还没有太多现成的性能剖析工具可以用于生产环境，所以在书中展示了一段示例代码，可以简单而有效地复制使用。而到了今天，已经有了很多好用的工具，要做的只是打开工具箱，就可以开始优化性能。

首先，这里要“兜售”的一个好工具是一款叫做 New Relic 的软件即服务 (oftware-asa-service)产品。声明一下我们不是“托”，我们一般不会推荐某个特定公司或产品，但这个工具真的非常棒，建议大家都用它。我们的客户借助这个工具，在没有我们帮助的情况下，解决了很多问题，即使有时候找不到解决办法，但依然能够帮助定位到问题 New Relic 会插人到应用程序中进行性能剖析，将收集到的数据发送到一个基于 Web 的仪表盘，使用仪表盘可以更容易利用面向响应时间的方法分析应用性能。这样用户只需要考虑做那些正确的事情，而不用考虑如何去做。而且 ****** New Relic 测量了很多用户体验相关的点，涵盖从 We 浏览器到应用代码，再到数据库及其他外部调用。

像 New Relic 这类工具的好处是可以全天候地测量生产环境的代码,一既不限于测试环境,也不限于某个时间段。这一点非常重要,因为有很多剖析工具或者测量点的代价很高所以不能在生产环境全天候运行。在生产环境运行，可以发现一些在测试环境和预发环境无法发现的性能问题。如果工具在生产环境全天候运行的成本太高，那么至少也要在集群中找一台服务器运行，或者只针对部分代码运行，原因请参考前面的“性能剖析本身会导致服务器变慢吗?”

3.2.1 测量 PHP 应用程序

如果不使用 New Relic，也有其他的选择。尤其是对 PHP，有好几款工具都可以帮助进行性能剖析。其中一款叫做 xhprof(pecl.phpnet/package/xhp…)，这是 Facebook 开发给内部使用的，在 2009 年开源了。xhprof有很多高级特性，并且易于安装和使用它很轻量级，可扩展性也很好，可以在生产环境大量部署并全天候使用，它还能针对函数调用进行剖析，并根据耗费的时间进行排序。相比 xhprof，还有一些更底层的工具比如xdebug、Valgrind和cachegrind，可以从多个角度对代码进行检测。有些工具会产生大量输出，并且开销很大，并不适合在生产环境运行，但在开发环境却可以发挥很大的作用。下面要讨论的另外一个 PHP 性能剖析工具是我们自己写的，基于本书第二版的代码和原则扩展而来，名叫IfP (instrumentation-for-php)，代码托管在Goole Code 上 (http:/code.googlecom/p/instrumentation-for-php/)。Ip并不像xhprof一样对PHP做深入的测量而是更关注数据库调用。所以当无法在数据库层面进行测量的时候，Ifp 可以很好地帮助应用剖析数据库的利用率。Ifp 是一个提供了计数器和计时器的单例类，很容易部署到生产环境中，因为不需要访问 PHP 配置的权限(对很多开发人员来说，都没有访问 PHP配置的权限，所以这一点很重要)。 Ifp 不会自动解析所有的 PHP 函数，而只是针对重要的函数。例如，对于某些需要剖析的地方要用到自定义的计数器，就需要手工启动和停止。但 Ifp 可以自动对整个页面的执行进行计时，这样对自动测量数据库和 memcached 的调用就比较简单，对于这种情况就无须手工启动或者停止。这也意味着，Ifp 可以剖析三种情况:应用程序的请求(如page view)、数据库的查询和缓存的查询。Ifp 还可以将计数器和计时器输出到Apache通过 Apache 可以将结果写入到日志中。这是一种方便且轻量的记录结果的方式。Ifp 不会保存其他数据，所以也不需要有系统管理员的权限。

使用Ifp，只需要简单地在页面的开始处调用 start request()。理想情况下，在程序的一开始就应当调用。

Untitled

这段代码注册了一个 shutdown 函数，所以在执行结束的地方不需要再做更多的处理。

Ifp 会自动对 SQL添加注释，便于从数据的查询志中更灵活地分析应用的情况，通过SHOW PROCESSLIST 也可以更清楚地知道性能低的查询出自何处。大多数情况下，定位性能低下查询的来源都不容易，尤其是那些通过字符串拼接出来的查询语句，都没有办法在源代码中去搜索。那么Ifp 的这个功能就可以帮助解决这个问题，它可以很快定位到查询是从何处而来的，即使应用和数据库中间加了代理或者负载均衡层，也可以确认是哪个应用的用户，是哪个页面请求，是源代码中的哪个函数、代码行号，甚至是所创建的计数器的键值对。

下面是一个例子 :

Untitled

如何测量MySOL的调用取决于连接 MySQL的接口。如果使用的是面向对象的 mysgl接口，则只需要修改一行代码:将构造函数从 mysgli改为可以自动测量的 mysgli_x 即可。mysqli_x 造是 Ifp 供的子类，可以在后测量并改写查询。如使用的不是面向对象的接口，或者是其他的数据库访问层，则需要修改更多的代码。如果数据库调用不是分散在代码各处还好，否则建议使用集成开发环境(IDE)如 Eclipse，这样修改起来要容易些。但不管从哪个方面来看，将访问数据库的代码集中到一起都可以说是最佳实践。

Ifp 的结果很容易分析。Percona Toolkit 中的pt-guery-digest 能够很方便地从查询注释中抽取出键值对，所以只需要简单地将查询记录到 MySQL 的日志文件中，再对日志文件进行处理即可。Apache 的 mod log_config 块可以利用I 输出的环境变量来定制日志输出，其中的宏 D 还可以以微秒级记录请求时间。

也可以通过 LOAD DATA INFILE将Apache的日志载到 MySQL数据库中，然后通过SQL 进行查询。在 Ifp 的网站上有一个 PDF 的灯片，详细给出了使用示例，包括查询和命令行参数都有。

或许你会说不想或者没时间在代码中加入测量的功能，其实这事比想象的要容易得多而且花在优化上的时间将会由于性能的优化而加倍地回报给你。对应用的测量是不可替代的。当然最好是直接使用 New Relic、xhprof、Ifp 或者其他已有的优化工具，而不必重新去发明“轮子”。

Untitled

3.3 剖析 MySQL查询

对查询进行性能剖析有两种方式，每种方式都有各自的问题，本章会详细介绍。可以剖析整个数据库服务器，这样可以分析出哪些查询是主要的压力来源(如果已经在最上面的应用层做过剖析，则可能已经知道哪些查询需要特别留意)。定位到具体需要优化的查询后，也可以钻取下去对这些查询进行单独的剖析，分析哪些子任务是响应时间的主要消耗者。

3.3.1 剖析服务器负载

服务器端的剖析很有价值，因为在服务器端可以有效地审计效率低下的查询。定位和优化“坏”查询能够显著地提升应用的性能，也能解决某些特定的难题。还可以降低服务器的整体压力，这样所有的查询都将因为减少了对共享资源的争用而受益 (“间接的好处”)。降低服务器的负载也可以推迟或者避免升级更昂贵硬件的需求，还可以发现和定位糟糕的用户体验，比如某些极端情况。

MySQL的每一个新版本中都增加了更多的可测量点。如果当前的趋势可靠的话，那么在性能方面比较重要的测量需求很快能够在全球范围内得到支持。但如果只是需要剖析并找出代价高的查询，就不需要如此复杂。有一个工具很早之前就能帮到我们了，这就是慢查询日志。

捕获MySQL的查询到日志文件中

在MySQL中慢查询日志最初只是捕获比较“慢”的查询，而性能剖析却需要针对所有的查询。而且在 MySQL 5.0 及之前的版本中，询志的响应时间的单位是秒，粒度太粗了。幸运的是，这些限制都已经成为历史了。在 MySQL 5.1 及更新的版本中，慢日志的功能已经被加强，可以通过设置 long_query_time为0来捕获所有的查询，而且查询的响应时间单位已经可以做到微秒级。如果使用的是 Percona Server，那么5.0版本就具备了这些特性，而且 Percona Server 提供了对日志内容和查询捕获的更多控制能力。

在 MySOL的当前版本中，查询日志是开销最低、精度最高的测量查询时间的工具如果还在担心开启慢查日志会带来额外的I/0 开销，那大可以放心。我们在 I/0 密集型场景做过基准测试，慢查询日志带来的开销可以忽略不计 (实际上在 CPU 密集型场景的影响还稍微大一些)。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意要部署日志轮转 (log rotation)工具。或者不要长期启用查询日志只在需要收集负载样本的期间开启即可。

MySQL还有另外一种查询日志，被称之为“通用日志”，但很少用于分析和剖析服务器性能。通用日志在查询请求到服务器时进行记录，所以不包含响应时间和执行计划等重要信息。MySQL 5.1之后支持将日志记录到数据库的表中，但多数情况下这样做没什么必要。这不但对性能有较大影响，而且 MySQL 5.1在将询记录到文中时已经支持微秒级别的信息，然而将慢查询记录到表中会导致时间粒度退化为只能到秒级。而秒级别的慢查询日志没有太大的意义。

Percona Server 的慢查询日志比 MySQL 官方版本记录了更多细节且有价值的信息，如查询执行计划、锁、I/0 活动等。这些特性都是随着处理各种不同的优化场景的需求而慢慢加进来的。另外在可管理性上也进行了增强。比如全局修改针对每个连接的 longquery_time 的阙值，这样当应用使用连接池或者持连接的时候，可以不用重置会话级别的变量而启动或者停止连接的查询日志。总的来说，慢查询日志是一种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器有时因为某些原因如权限不足等，无法在服务器上记录查询。这样的限制我们也常常碰到所以我们开发了两种替代的技术都集成到了 Percona Toolkit 中的pt-query-digest 中

第一种是通过--processlist 选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现的时间和消失的时间。某些情况下这样的精度也足够发现问题，但却无法捕获所有的查询。一些执行较快的查询可能在两次执行的间隙就执行完成了，从而无法捕到。

第二种技术是通过抓取TCP 网络包，然后根据MySQL的客户端/服务端通信协议进行解析。可以先通过 tcpdump 将网络包数据保存到磁盘，然后使用pt-query-digest的--type=tcpdump 选项来解析并分析查询。此方法的精度比较高，并且可以捕获所有查询。还可以解析更高级的协议特性，比如可以解析二进制协议，从而创建并执行服务端预解析的语句(prepared statement)及压缩协议。另外还有一种方法，就是通过MySQLProxy 代理层的脚本来记录所有查询，但在实践中我们很少这样做。

分析查询日志

强烈建议大家从现在起就利用慢查询日志捕获服务器上的所有查询，并且进行分析。可以在一些典型的时间窗口如业务高峰期的一个小时内记录查询。如果业务趋势比较均衡那么一分钟甚至更短的时间内捕获需要优化的低效查询也是可行的。 不要直接打开整个慢查询日志进行分析，这样做只会浪费时间和金钱。首先应该生成一个剖析报告，如果需要，则可以再查看日志中需要特别关注的部分。自顶向下是比较好的方式，否则有可能像前面提到的，反而导致业务的逆优化。从慢查询日志中生成剖析报告需要有一款好工具，这里我们建议使用pt-query-digest这毫无疑问是分析 MySOL查询日志最有力的工具。该工具功能强大，包括可以将查询报告保存到数据库中，以及追踪工作负载随时间的变化。

一般情况下，只需要将慢查询日志文件作为参数传递给 pt-query-digest，就可以正确地工作了。它会将查询的剖析报告打印出来，并且能够选择将“重要”的查询逐条打印出更详细的信息。输出的报告细节详尽,绝对可以让生活更美好。该工具还在持续的开发中,因此要了解最新的功能请阅读最新版本的文档。

这里给出一份 pt-query-digest 输出的报告的例子，作为进行性能剖析的开始。这是前面提到过的一个未修改过的剖析报告 :

Untitled

可以看到这个比之前的版本多了一些细节。首先，每个查询都有一个ID，这是对查询语句计算出的哈希值指纹，计算时去掉了查询条件中的文本值和所有空格，并且全部转化为小写字母(请注意第三条和第四条语的摘要看起来一样，但哈希指纹是不一样的)该工具对表名也有类似的规范做法。表名 InvitesNew后面的问号意味着这是一个分片(shard)的表，表名后面的分片标识被问号替代，这样就可以将同一组分片表作为一个整体做汇总统计。这个例子实际上是来自一个压力很大的分片过的 Facebook 应用。

报告中的V/M列提供了方差均值比 (variance-to-mean ratio)的详细数据，方差均值比也就是常说的离差指数 (index of dispersion)。离差指数高的查询对应的执行时间的变化较大，而这类查询通常都值得去优化。如果 pt-query-digest 指定了 --explain 选项，输出结果中会增加一列简要描述查询的执行计划，执行计划是查询背后的“极客代码”。通过联合观察执行计划列和 V/M 列，可以更容易识别出性能低下需要优化的查询。

最后，在尾部也增加了一行输出，显示了其他 17 个占比较低而不值得单独显示的查询的统计数据。可以通过 --limit 和 --outliers 选项指定工具显示更多查询的详细信息，而不是将一些不重要的查询汇总在最后一行。默认只会打印时间消耗前 10 位的查询，或者执行时间超过 1秒阔值很多倍的查询，这两个限制都是可配置的。剖析报告的后面包含了每种查询的详细报告。可以通过查询的ID 或者排名来匹配前面的剖析统计和查询的详细报告。下面是排名第一也就是“最差”的查询的详细报告 :

Untitled

查询报告的顶部包含了一些元数据，包括查询执行的频率、平均并发度，以及该查询性能最差的一次执行在日志文件中的字节偏移值，接下来还有一个表格格式的元数据，包括诸如标准差一类的统计信息。

接下来的部分是响应时间的直方图。有趣的是，可以看到上面这个查询在 Query_timedistribution部分的直方图上有两个明显的高峰，大部分情况下执行都需要几百毫秒，但在快三个数量级的部分也有一个明显的尖峰，几百微秒就能执行完成。如果这是Percona Server 的记录，那么在查询日志中还会有更多丰富的属性，可以对查询进行切片分析到底发生了什么。比如可能是因为查询条件传递了不同的值，而这些值的分布很不均衡，导致服务器选择了不同的索引，或者是由于查询缓存命中等。在实际系统中这种有两个尖峰的直方图的情况很少见，尤其是对于简单的查询，查询越简单执行计划也越稳定。

在细节报告的最后部分是方便复制、粘贴到终端去检查表的模式和状态的语句，以及完整的可用于EXPLAIN分析执行计划的语句。EXPLAIN分析的语要求所有的条件是文本值而不是“指纹”替代符，所以是真正可直接执行的语句。在本例中是执行时间最长的一条实际的查询。

确定需要优化的查询后，可以利用这个报告迅速地检查查询的执行情况。这个工具我们经常使用，并且会根据使用的情况不断进行修正以帮助提升工具的可用性和效率，强烈建议大家都能熟练使用它。MySQL本身在未来或许也会有更多复杂的测量点和剖析工具，但在本书写作时，通过慢查询日志记录查询或者使用pt-query-diges t分析 tcpdump 的结果，是可以找到的最好的两种方式。

****** ****** ****** ****** ****使用 SHOW PROFILE ****** ****** ****** **********