高性能MYSQL（三）:性能剖析-海口c网

一、性能剖析概述

（一）关于性能优化

1.什么是性能？

我们将性能定义为完成某件任务所需要的时间度量，换句话说，性能即响应时间，这是一个非常重要的原则。

我们通过任务和时间而不是资源来测量性能。数据库服务器的目的是执行 SQL 语句，所以它关注的任务是查询或者语句，如 SELECT、UPDATE、DELETE 等。

数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

2.什么是优化？

性能优化就是在一定的工作负载下，尽可能地降低响应时间。

（1）性能优化是降低 CPU 利用率，减少对资源的使用？

这是一个陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度。很多时候将使用老版本 InnoDB 引擎的 MySQL 升级到新版本后，CPU 利用率会上升得很厉害，这并不代表性能出现了问题，反而说明新版本的 InnoDB 对资源的利用率上升了。

（2）性能优化是提升每秒查询量？

这其实只是吞吐量优化，吞吐量的提升可以看作性能优化的副产品。对查询的优化可以让服务器每秒执行更多的查询，因为每条查询执行的时间更短了。

吞吐量的定义是单位时间内的查询数量，这正好是我们对性能的定义的倒数。

（3）如何降低响应时间？

既然目标是降低服务器查询的响应时间，首先需要理解为什么查询会花费这么多时间。

通过识别和消除那些对获得查询结果不必要的工作，可以有效提升性能。

（4）如何优化？

“无法测量就无法有效地优化”。这意味着优化的第一步应该是准确地测量时间花在了哪里。

很多人在优化时往往直接修改代码或配置，而忽视了精确测量，这可能导致优化方向错误。

建议将大量时间（甚至90%）用于测量响应时间的具体分布。

如果测量没有找到问题所在，可能是测量方式不正确或不够全面。

完整且正确的数据测量通常能揭示性能问题，从而让解决方案更加明确。

（二）关于测量

测量本身是一项具有挑战性的工作，不仅需要测出时间花在哪里，还需要分析原因，这两者是不同的任务。

分析测量结果同样具有挑战性，需要深入理解系统运行机制。

1.合适的测量范围

合适的测量范围是指只针对需要优化的活动进行测量，避免无关测量干扰结果。

（1）避免在错误的时间启动和停止测量：

在开始和结束测量时，要确保选择的时间点准确无误。

例如，如果目标是测量慢查询的响应时间，应该从查询开始到查询结束这段时间内进行测量，而不是在查询之前或之后。

比如说，把用户输入命令、数据库连接的时间、客户端处理返回结果的时间等等，也算到慢查询时间里了。

（2）测量目标活动本身，而非聚合信息：

测量应直接针对需要优化的具体活动（如慢查询），而不是整个服务器的综合情况。这样可以更准确地定位问题所在。

例如，如果确认存在慢查询，就应该专注于测量该慢查询的执行时间，而不是查看整个服务器的状态(CPU 使用率、内存占用、磁盘 I/O 等整体系统指标)。

比如说，你去查看整个数据库服务器的 CPU 使用率为 40%，内存使用为 60%，看起来都正常。于是你认为没有性能问题，但实际上可能有一个特定的 SQL 查询非常慢，只是它还没频繁执行，所以没引起系统资源的高峰。

2.对任务时间进行分解

完成一项任务所需的时间可以分为两部分：执行时间和等待时间。

（1）执行时间

执行时间是指实际进行计算或操作所花费的时间。优化执行时间的方法包括：

定位并优化各个子任务的执行效率。
去除不必要的子任务。
降低子任务的执行频率。

（2）等待时间

等待时间是指由于资源争用、系统间接影响等原因导致的任务停滞时间。优化等待时间相对复杂，可能涉及：

分析任务间对磁盘或CPU资源的争用情况。
考虑其他系统对当前任务的间接影响。

（三）性能剖析

性能剖析是测量和分析时间花费在哪里的主要方法。

1.性能剖析的步骤

性能剖析的两个主要步骤：

（1）步骤一：测量任务所花费的时间

这一步主要是收集数据，记录系统在执行不同任务时的具体耗时情况。例如，在数据库查询优化中，你可以记录每个查询的执行时间。

（2）步骤二：对结果进行统计和排序，将重要的任务排到前面

收集完数据后，需要对这些数据进行整理和分析。通过统计和排序，可以清楚地看到哪些任务是最耗时的，这些任务就是优化的优先级最高的目标。

2.性能剖析工具的工作方式

启动和停止计时器：在任务开始时启动计时器，在任务结束时停止计时器，通过计算结束时间与启动时间之差得到任务的响应时间。
记录父任务：一些工具还会记录每个任务的父任务，这有助于理解任务之间的调用关系。
绘制调用关系图：这些结果数据可以用来绘制任务之间的调用关系图，帮助理解系统的整体架构。
分组和汇总相似任务：更重要的是，可以将相似的任务分组并进行汇总，这样可以对每组任务进行更复杂的统计分析，比如计算每组任务的数量和总的响应时间。
列出所有任务列表：性能剖析报告会列出系统中所有的任务，并为每个任务提供详细信息。
每行记录一个任务的信息：包括任务名、执行时间、消耗时间、平均执行时间以及该任务执行时间占全部时间的百分比。
按消耗时间降序排序：报告会按照任务的消耗时间进行降序排序，这样可以快速找到最耗时的任务，优先进行优化。

3.基于时间执行时间的分析

这种分析关注的是哪些任务的执行时间最长。

如果一个任务执行时间长是因为消耗了大量资源且大部分时间都在执行上，而等待的时间不多，那么基于等待时间的分析作用不大。

4.基于等待时间的分析

这种分析关注的是任务在什么地方被阻塞的时间最长。

如果一个任务一直在等待，没有消耗什么资源，那么去分析执行时间就不会有什么结果。

5.测量的必要性：

在进行性能剖析之前，必须能够对系统进行测量，这需要系统的支持以提供可量化的数据。

大多数系统提供的测量点有限，通常只能提供活动计数而没有活动花费的时间统计。例如，MySQL直到5.5版本才引入Performance Schema，提供了基于时间的测量点，而之前的版本如5.1及更早版本则没有任何基于时间的测量点。

即使系统本身不提供足够的测量点，也可以通过外部手段进行测量或根据对系统的了解做出合理的猜测来进行优化工作。

然而，无论是外部测量还是猜测，得到的数据都不是完全准确的，这是系统透明度不足带来的风险。

（四）理解性能剖析

1.有哪些查询值得优化？

性能剖析不会自动指出哪些查询是值得花时间去优化的，这需要根据优化的基本原则来判断。

第一点是关于一些只占总响应时间比重很小的查询是否值得优化的问题。根据阿姆达尔定律（Amdahl’s Law），如果一个查询仅占总响应时间的5%或更少，那么无论你如何努力优化它，所能获得的收益也不会超过5%。这意味着优化这样的查询可能并不会显著提升整体性能，因此不值得投入过多资源。

第二点涉及优化的成本与收益之间的关系。假设花费了1000美元去优化一个任务，但如果这个优化并没有带来任何业务收入的增加，那么实际上这笔钱就相当于被浪费了，或者说业务被“逆优化”了1000美元。

2.异常情况

某些任务即使没有出现在性能剖析输出的前面也需要优化

有时候，一些任务虽然执行次数很少，但每次执行都非常慢，严重影响用户体验。由于这些任务的执行频率低，它们在总响应时间中所占的比例并不突出，因此可能不会被优先考虑进行优化。然而，这些任务对用户体验的影响是显著的，所以仍然需要关注和优化。

3.丢失的时间：

一款好的性能剖析工具会显示可能的“丢失的时间”。丢失的时间是指任务的总时间和实际测量到的时间之间的差值。例如，如果处理器的CPU时间为10秒，而剖析到的任务总时间是9.7秒，那么就有300毫秒的丢失时间。
这种丢失的时间可能是由于以下原因造成的：

某些任务没有被测量到。
测量过程中的误差和精度问题。

如果性能剖析工具发现了这类问题，则需要引起重视，因为这可能意味着错过了某些重要的信息或潜在的问题。

即使性能剖析工具没有发现丢失时间，也需要注意考虑这类问题存在的可能性，以避免错过重要的信息。

4.被掩藏的细节

平均值的局限性：只依赖平均值进行性能分析是非常危险的，因为它会隐藏很多信息，无法全面反映实际情况。例如，如果两次查询响应时间为1秒，而另外12771次查询的响应时间只有几十微秒，那么从平均值来看，整体性能似乎很好，但实际上存在严重的性能问题。

需要更多响应时间的信息：为了做出最佳决策，需要为性能剖析输出提供更多关于响应时间的详细信息，如直方图、百分比、标准差、偏差指数等。这些数据可以帮助识别异常情况和潜在问题。

5. 更高层次的堆栈分析

交互式分析的缺失：当前的性能剖析方法通常只能针对单个查询进行分析，无法将相关查询联系起来，也无法理解这些查询是否属于同一个用户交互的一部分。
扩展至事务或页面查看级别：性能剖析应不仅仅局限于任务层面，还应扩展到事务或页面查看级别，以便更全面地理解系统行为。
解决办法：可以通过给查询添加特殊注释作为标签，标明其来源并进行聚合分析；也可以在应用层面增加更多的测量点，以获得更丰富的数据。

二、剖析mysql查询

对查询进行性能剖析有两种方式，每种方式都有各自的问题。

可以剖析整个数据库服务器，这样可以分析出哪些查询是主要的压力来源(如果已经在最上面的应用层做过剖析，则可能已经知道哪些查询需要特别留意)。

定位到具体需要优化的查询后，也可以钻取下去对这些查询进行单独的剖析，分析哪些子任务是响应时间的主要消耗者。

（一）剖析服务器负载

服务器端的剖析很有价值，因为在服务器端可以有效地审计效率低下的查询。定位和优化“坏”查询能够显著地提升应用的性能，也能解决某些特定的难题。

还可以降低服务器的整体压力，这样所有的查询都将因为减少了对共享资源的争用而受益(“间接的好处”)。

降低服务器的负载也可以推迟或者避免升级更昂贵硬件的需求，还可以发现和定位糟糕的用户体验，比如某些极端情况。

1.捕获mysql的查询到日志文件

早期版本的查询日志只能记录执行时间超过一定阈值（单位为秒）的查询，这种粒度较粗，无法捕捉到所有可能影响性能的查询。此外，日志仅能记录“慢”查询，而不能用于全面的性能剖析。

现在，慢查询日志的功能得到了显著增强。通过将long_query_time参数设置为0，可以捕获所有的查询，而不仅仅是那些执行时间较长的查询。查询响应时间的单位也从秒提升到了微秒级，这使得慢查询日志能够更精确地记录查询的执行时间，从而更好地支持性能分析和优化工作。

（1）启用慢查询日志

首先，你需要确保MySQL服务器配置允许记录慢查询。这通常涉及到修改MySQL的配置文件（如my.cnf或my.ini），添加或修改以下参数：

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 2

更改配置后，需要重启MySQL服务以使新的配置生效。

重启MySQL之后，可以通过以下SQL命令检查慢查询日志是否已正确启用：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

（2）分析慢查询日志

一旦慢查询日志开始记录信息，你可以使用多种方法来分析它：

手动查看: 直接打开慢查询日志文件，查看记录的信息。不过，这种方法对于较大的日志文件不太实用。

使用pt-query-digest工具: 这是Percona Toolkit中的一个工具，非常适合用来分析慢查询日志。它可以提供关于最耗时查询的详细报告，帮助你快速定位性能问题。
使用示例：

pt-query-digest /path/to/your/slow-query.log

MySQL自带的工具: MySQL Workbench提供了导入和分析慢查询日志的功能；另外，从MySQL 5.6开始，可以使用sys模式下的视图来简化慢查询日志的分析工作。

（二）剖析单条查询

在确定需要优化的具体查询后，需要“钻取”更多详细信息，以了解为什么该查询会花费这么长的时间执行，并确定如何进行优化。

在实际应用中，除了以下三种方法外，没有更好的办法来剖析单条查询：

SHOW STATUS：显示服务器状态变量的信息。
SHOW PROFILE：提供查询执行过程中各个阶段的时间消耗情况（需要注意的是，这要求必须是Percona Server，官方MySQL版本的慢查询日志缺失了很多附加信息）。
检查慢查询日志的条目：记录执行时间超过设定阈值的查询。

下面将逐一演示如何使用这三种方法来剖析单条查询，看看每一种方法是如何显示查询的执行情况的。

1.SHOW PROFILE

SHOW PROFILE命令是在MySQL 5.1及以后的版本中引入的，由开源社区中的Jeremy Cole贡献。

（1）启用方法

默认情况下，SHOW PROFILE是禁用的。

可以通过设置服务器变量profiling来在会话（连接）级别动态地启用它。例如：

mysql> SET profiling = 1;

（2）功能和作用

启用后，在服务器上执行的所有SQL语句都会被测量其耗费的时间和其他一些查询执行状态变更相关的数据。

这个功能有一定的作用，最初的设计功能更强大，但在未来版本中可能会被Performance Schema所取代。

尽管如此，SHOW PROFILE工具最有用的作用还是在于能够在语句执行期间剖析服务器的具体工作。

（3）记录与查询标识

mysql> select * from sys_menu;

当一条查询提交给服务器时，SHOW PROFILE工具会将剖析信息记录到一张临时表中，并且会给查询赋予一个唯一的标识符。

mysql> show profiles;

在这里插入图片描述

首先可以看到的是以很高的精度显示了查询的响应时间，这很好。MySQL客户端显示的时间只有两位小数，对于一些执行得很快的查询这样的精度是不够的。下面继续看接下来的输出：

mysql> show profile for query 1;

在这里插入图片描述

剖析报告给出了查询执行的每个步骤及其花费的时间，看结果很难快速地确定哪个步骤花费的时间最多。

因为输出是按照执行顺序排序，而不是按花费的时间排序的——而实际上我们更关心的是花费了多少时间，这样才能知道哪些开销比较大。

但不幸的是，无法通过诸如 ORDER BY 之类的命令重新排序。

(4)使用INFORMATION_SCHEMA重新排序

假如不使用 SHOW PROFILE 命令而是直接查询 INFORMATION_SCHEMA 中对应的表，则可以按照需要格式化输出：

-- 设置查询ID
mysql> SET @query_id = 1;
Query OK, 0 rows affected (0.00 sec)-- 查询并计算每个状态的总时间、百分比、调用次数及平均时间
mysql> SELECT STATE, SUM(DURATION) AS Total_R,
ROUND(
100 * SUM(DURATION) /
(SELECT SUM(DURATION)
FROM INFORMATION_SCHEMA.PROFILINGWHERE QUERY_ID = @query_id
), 2) AS Pct_R,COUNT(*) AS calls,SUM(DURATION) / COUNT(*) AS "R/Call"FROM INFORMATION_SCHEMA.PROFILINGWHERE QUERY_ID = @query_idGROUP BY STATEORDER BY Total_R DESC;

在这里插入图片描述

字段解释

STATE：表示查询执行的不同阶段或状态。
Total_R：表示每个状态下花费的总时间（单位通常是秒）。
Pct_R：表示每个状态下花费的时间占整个查询总时间的百分比。
calls：表示每个状态被调用的次数。
R/Call：表示每次调用该状态所花费的平均时间。

这样这个效果就好多了。

2.使用 SHOW STATUS

SHOW STATUS命令返回了一些计数器，这些计数器可以分为两类：服务器级别的全局计数器和基于某个连接的会话级别的计数器。

例如，其中的Queries计数器在会话开始时为0，每提交一条查询就增加1。如果执行SHOW GLOBAL STATUS（注意新增的GLOBAL关键字），则可以查看服务器级别的从服务器启动时开始计算的查询次数统计。

（1）计数器的可见范围：

不同计数器的可见范围不一样，但全局的计数器也会出现在SHOW STATUS的结果中，容易被误认为是会话级别的，千万不能搞混了。

在使用这个命令的时候要注意几点，就像前面所讨论的，收集合适级别的测量值是很关键的。如果打算优化从某些特定连接观察到的东西，测量的却是全局级别的数据，就会导致混乱。

MySQL官方手册中对所有的变量是会话级还是全局级做了详细的说明。

（2）SHOW STATUS 的局限性：

SHOW STATUS是一个有用的工具，但并不是一款剖析工具。SHOW STATUS的大部分结果都只是一个计数器，可以显示某些活动如读索引的频繁程度，但无法给出消耗了多少时间。

SHOW STATUS的结果中只有一条指的是操作的时间（Innodb_row_lock_time），而且只能是全局级的，所以还是无法测量会话级别的工作。

（3）计数器的应用场景

尽管SHOW STATUS无法提供基于时间的统计，但对于在执行完查询后观察某些计数器的值还是有帮助的。有时候可以猜测哪些操作代价较高或者消耗的时间较多。

-- 重置所有会话级别的状态变量
mysql> FLUSH STATUS;-- 从sys_menu表中选择所有记录
mysql> SELECT * FROM sys_menu;
[query results omitted]-- 显示与Handler%和Created%相关的状态变量
mysql> SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%';