MySQL实践——如何查看MySQL性能

系统变量

可以使用两种机制来管理和监控MySQL服务器的运行情况。使用服务器变量来控制其运行情况，并使用服务器状态变量读取其运行情况配置和关于功能和性能的统计信息。

服务器中有很多配置变量。有些变量只能在启动时设置(被称为启动选项，这些也可以在选项文件中设置)。其他的一些变量可以被设置成适用于全局级(对于所有连接有效)、会话级别(单个连接)或同时适用于全局和会话两个级别。

可以使用以下命令读取服务器变量：

SHOW [GLOBAL | SESSION] VARIABLES;

可以使用以下命令更改非静态(只读)变量(可以用“,”分隔符在一条命令上同时进行多个变量的设置):

SET [GLOBAL | SESSION] <variable_name>= value>;
SET [@@global. | @@session.| @@ ]<variable_name>= value>;

可以使用以下命令读取状态变量。前两条命令显示所有本地或会话范围(默认情况下是
会话)的变量值。第三条命令显示全局范围的变量：

SHOW STATUS;
SHOW SESSION STATUS;
SHOW GLOBAL STATUS;

性能监控

MySQL的性能监控是前面命令的应用。具体而言，就是设置和读取系统变量及读取状态变量。SHOW和SET命令是仅有的两个可以用于监控MySQL服务器的工具。

事实上，有很多工具可以用于监控MySQL服务器。在标准发行版中可用的监控工具仅限于控制台工具，其中包括可以在MySQL客户端执行的特殊命令(如SHOW STATUS)和可以从命令行运行的实用工具(如mysqladmin)。

还有一些GUI工具使监控变得更容易，如果你有这方面的需求，可以考虑选择这种工具。还可以下载MySQL GUI工具，其中包括高级工具，这些高级工具可以用于监控系统、管理查询和从其他数据库系统迁移数据。

下面首先介绍如何使用这些SQL命令，然后讨论MySQL管理器图形工具和查询浏览器。还将简单介绍最容易被忽视的管理工具之——服务器日志。

一些专业的管理员在管理服务器时，可能第一时间考虑最重要的工具——服务器日志。虽然它们不如性能监控工具那么重要，但是它们在诊断性能问题时也是非常重要的。

常用SQL命令

所有的SQL监控命令都是SHOW命令的变体，它们显示系统及其子系统的内部信息。虽然SHOW命令的形式很多，以下命令列表是在监控MySQL服务器时使用最多的SQL命令：

SHOW INDEX FROM <table>

显示指定表的索引基数(cardinality)统计信息，在优化程序中使用它评估连接选择性(即索引列中非重复值)。这个命令有助于诊断性能低下的查询，尤其是查询是否使用了可用的索引。

SHOW PLUGINS

显示所有已知插件的列表。它显示插件的名称和当前状态。MySQL最新发行版中的存储引擎是以插件形式实现的。使用这个命令获取当前可用插件及其状态的快照。

SHOW [FULL] PROCESSLIST

显示系统上运行的所有线程(包括连接)。这个命令与主操作系统的进程命令类似。显示的信息包括命令执行时的连接数据、执行时间和当前状态。像操作系统命令一样，它可以诊断不良的响应(太多线程)、僵尸进程(zombie process)(未响应或长时间运行)，或者甚至诊断连接问题。当在处理低性能或未响应的线程时，使用KILL命令终止它们。默认行为是显示当前用户的进程。命令中使用FULL关键字则显示所有进程。

SHOW [GLOBAL | SESSION] STATUS

显示所有系统变量的值。相对其他命令而言，你可能更频繁地使用这个命令。使用这个命令读取在服务器上可获取的所有统计信息。与GLOBAL或SESSION关键字结合使用，可以选择性地只查看全局变量的统计信息或会话变量的统计信息。

 SHOW TABLE [FROM <db>] STATUS

显示给定数据库的表的详情，其中包括存储引擎、排序规则(Collation)、创建数据、索引数据和行统计信息。可以将这个命令与SHOW INDEX命令结合使用，在诊断低性能查询时检查表信息。

 SHON [GLOBAL | SESSION] VARIABLES

显示系统变量。一般是服务器的配置选项，虽然它不显示统计信息，但是在确定当前配置是否已被更改或某些选项是否被设置时，查看变量是非常重要的。有些变量是只读的，且只可以通过配置文件或命令行在启动的时候更改，而其他的一些变量可以在全局范围内更改或在本地设置。可以将这个命令与GLOBAL或SESSION关键字结合使用，从而选择性地查看全局变量或会话变量。

与存储引擎相关的命令如下所示：

SHOW ENGINE <engine_name> LOGS

显示指定存储引擎的日志信息，这些信息是由存储引擎提供的，且在优化存储引擎时是非常有用的，有些存储引擎不提供这些信息。

SHOW ENGINE <engine_name> STATUS

显示指定存储引擎的状态信息，这些信息是存储引擎提供的。有些存储引擎比其他的存储引擎提供更多或更少的信息。例如，InnoDB存储引擎显示很多状态变量，而NDB存储引擎只显示少许的状态变量。MyISAM存储引擎则不显示任何信息。这个命令是查看一个给定存储引擎的统计信息的基本方法，且它在优化某些存储引擎时非常重要(如InnoDB)。

 SHOW ENGINES

显示MySQL发行版的所有可用存储引擎的列表及其状态(如存储引擎是否可用)。这个命令有助于决定在数据库上使用何种存储引擎，并有助于决定复制过程中是否在Master和Slave上存在相同的存储引擎。

与MySQL复制相关的具体命令如下所示：

SHOW BINLOG EVENTS [IN '<log_file>'][FROM <pos>][LIMIT [<offset>,]<row count>]

显示被记录到二进制日志的事件。可以指定一个二进制文件用于审查(系统默认使用当前二进制文件)，并限定输出从日志中某个特定位置到日志结尾的所有事件，或者限定输出从日志第一行到第N行的事件。这个命令是用于诊断复制问题的主要命令，当事件中断复制或在复制过程中导致错误时，这个命令非常有用。

SHOW BINARY LOGS

显示服务器上的二进制列表。使用这个命令获取过去和当前binlog文件名的相关信息。每个文件的大小也被显示出来了。它是诊断复制问题的另一个有用的命令，因为它将允许你在指定的文件上使用SHOW BINLOG EVENTS命令，从而减少在诊断问题时必须查看的数据量。SHOW MASTER LOGS是它的同义词。

SHOW RELAYLOG EVENTS [IN'<log_file>'][FROM <pos>][LIMIT[<offset>,]<row count>]

在MySQL 5.5.0中可用，这个命令和SHOW BINLOG EVENTS命令的功能类似，仅限制查询Slave上的中继日志。如果不提供日志文件名，该命令将显示第一个中继日志中的事件。这个命令在Master上运行无效。

SHON MASTER STATUS

显示Master的当前配置。它显示当前二进制日志文件、文件的当前位置和所有的排他性或包容性复制的设置。当连接或重新连接Slave时使用这个命令。

SHOW SLAVE HOSTS

使用–report-host选项显示连接到Master的Slave列表，根据这个列表可以确定哪些Slave连接到Master上。

SHOW SLAVE STATUS

显示复制中Slave的系统状态信息。这个命令是追踪Slave性能和状态的主要命令，显示了大量维护Slave健康状态的重要信息。第2章介绍了更多有关这个命令的信息。

这个列表中的两个最重要的命令是“SHOW VARIABLES”和“SHOW STATUS”。这里有许多变量(大约有290个状态变量)，因此一旦你掌握LIKE从句，就可以将结果精准到系统特定方面的信息。

变量列表通常是按字母表顺序排列的，并常常按功能分组。然而，有时变量没有整齐地排序。在这种情况下，可以使用关键字查找它们。例如，SHOW STATUS LIKE ‘%threads’ 显示所有与线程执行相关的状态变量。

这个例子不仅显示了那些用于线程管理的状态变量，还显示了用于控制InnoDB存储引擎的线程。虽然有时候将获得比你想象的更多的信息，但是可以使用LIKE语句查找指定的变量。

知道更改哪些变量和监控哪些变量是监控MySQL服务器最具挑战性的部分。MySQL在线参考手册上包含了有关这方面的大量有价值的信息。

为了阐明MySQL服务器上可以被监控的功能，我们讨论控制Query Cache的变量。如果你在应用数据中使用MyISAM存储引擎，Query Cache是MySQL的一个最重要的性能特征之一。它允许服务器在内存中缓存频繁使用的查询语句和查询结果。因此，一个查询运行得越频繁，这个查询的结果就越可能从缓存中读取，而不是重新审查索引结构和表去检索数据。显然，从内存中读取数据比从硬盘上读取数据要快很多。如果你的数据读取的频率比写入(更新)频率高，这可以提高性能。

每次运行查询，这个查询将被放入缓存并且有生命周期，该生命周期由它最近被使用的情况(旧查询首先被回收)和Query Cache可用的内存量来决定，另外，有大量事件可以将查询从缓存中删除。这里列举了部分事件：

• 频繁更改(数据或索引)。
• 不同形式的查询，这将会导致缓存命中率丢失。因此，使用标准查询去正常访问数据是非常重要的。你将在本章后面看到视图在这个方面的作用。
• 当查询从临时表中获取数据时。
• 事务事件可以使内存中的查询无效(如COMMIT)。

可以通过检查have_query_cache变量来确定MySQL安装程序中的Query Cache是否被配置和是否可用。这是个系统全局变量，但是它是只读的。可以使用多个变量中的一个来控制Query Cache。

如上所说，可以更改很多东西去影响Query Cache。最值得注意的是通过设置query_cache_size变量去临时关闭Query Cache——这个变量用于设置Query Cache的可用内存大小。如果将这个变量设置为0，它将迅速关闭Query Cache。这与have_query_cache变量无关，它仅仅表明这个特性可用。

在这里我们将看到MySQL服务器中比较不可思议的一种不一致性。可以使用以query_cache开头的变量来控制Query Cache，但是状态变量是以Qcache开头的。虽然这个不一致性是故意设计成的(有助于区分服务器变量和状态变量)，但像这样奇怪的设计将会使搜索正确的项复杂化。

允许你管理和配置并监控其性能的Query Cache间有很多细微的区别。这使得Query Cache成为说明监控MySQL服务器的复杂性的好例子。具体详情可能需要额外的研究和深入阅读MySQL在线参考手册。

另外两个用于监控复制的非常有用的命令是SHOW MASTER STATUS和SHOW SLAVESTATUS命令。我们将在下面的文章中介绍这些命令的详情。