你想知道的 MySQL 性能调优方式，都在这里！

前言：对于性能测试来说，数据库的监控是尤为的重要，以及对数据库进行调优，用以提升性能，是能在短期内有显著的效果的，本文是针对MySQL数据库进行分析如何定位MySQL数据库的性能问题。

关键MySQL 统计指标

如果你的数据库运行缓慢，或者出于某种原因无法响应查询，技术栈中每个依赖数据库的组件都会遭受性能问题。为了保证数据库的平稳运行，你可以主动监控以下四个与性能及资源利用率相关的指标：

查询吞吐量
查询执行性能
连接情况
缓冲池使用情况

MySQL 用户可以接触到数百个数据库指标，因此，在本文中我们将专注于能帮助我们实时了解数据库健康与性能的关键指标，本系列文章讨论的一些监控策略只适用于MySQL 5.6与5.7版本。

一、查询吞吐量(QPS)

在监控任何系统时，你最关心的应该是确保系统能够高效地完成工作。数据库的工作是运行查询，因此在本例中，你的首要任务是确保MySQL 能够如期执行查询。

通过以下指令，查询诸如Questions 或 Com_select 服务器状态变量的值：

show global status like "Questions";               

show status like 'Com_select';                          //读取查询指令

show global status like 'Com_insert';               //写入查询可能增加以下三个状态变量中的某一个值

show status like 'Com_update';

show status like 'Com_delete';

备注：（show global,，表示本次MySQL启动后到现在的总请求数；show status  当前会话连接执行的参数状态）：

show global status like 'com_select';                //当前MySQL启动后所有连接执行的select语句总数

show status like 'com_select'；                       //仅仅表示当前会话连接执行的SELECT语句数量

MySQL查询的数量,可以用来统计qps,qps计算公式：

基于Questions计算：questions / uptime

或者基于com_%计算：（Com_select + Com_insert + Com_update + Com_delete）/ uptime

应该设置告警的指标：Questions

当前的查询速率通常会有起伏，因此，如果基于固定的临界值，查询速率常常不是一个可操作的指标。但是，对于查询数量的突变设置告警非常重要——尤其是查询量的骤降，可能暗示着某个严重的问题。

二、查询性能

性能模式语句摘要

性能模式的 events_statements_summary_by_digest 表格中保存着许多关键指标，抓取了与每条标准化语句有关的延迟、错误和查询量信息。

想要按模式抽取出以微秒为单位的平均运行时间，你可以这样查询性能模式：

SELECT schema_name,SUM(count_star)count, ROUND((SUM(sum_timer_wait)/SUM(count_star))/ 1000000) AS avg_microsec  
     FROM performance_schema.events_statements_summary_by_digest 
     WHERE schema_name IS NOT NULL 
     GROUP BY schema_name;

相似地，按模式计算出现错误的语句总数，可以这么做：

SELECT schema_name, SUM(sum_errors) err_count
  FROM performance_schema.events_statements_summary_by_digest 
  WHERE schema_name IS NOT NULL 
  GROUP BY schema_name;

sys 模式

用上面的方式查询性能模式能以编程方式有效地从数据库中检索出指标。然而，对于特别查询或调查，使用MySQL 的 sys 模式通常更为简单。sys 模式以人们更易读的格式提供了一个有条理的指标集合，使得对应的查询更加简单。例如，想要找出最慢的语句（运行时间在 95 名开外）：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者查看哪些标准化语句出现了错误：

SELECT * FROM sys.statements_with_errors_or_warnings;

慢查询

除了性能模式与 sys 模式中丰富的性能数据，MySQL 还提供了一个 Slow_queries 计数器，每当查询的执行时间超过 long_query_time 参数指定的值之后，该计数器就会增加。默认情况下，该临界值设置为 10 秒。

SHOW VARIABLES LIKE 'long_query_time';

long_query_time 参数的值可通过一条指令进行调整。例如，将慢查询临界值设置为 5 秒：

SET GLOBAL long_query_time = 5;

（请注意，你可能要关闭会话，再重新连接至数据库，这些更改才能在会话层生效。）

show status like 'Slow_queries';

超过该值（--long-query-time）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。

调查查询性能问题

如果你的查询运行得比预期要慢，很可能是某条最近修改的查询在捣鬼。如果没有发现特别缓慢的查询，接下来就该评估系统级指标，寻找核心资源（CPU，磁盘 I/O，内存以及网络）的限制。CPU 饱和与 I/O 瓶颈是常见的问题根源。你可能还想检查 Innodb_row_lock_waits 指标，该指标记录着 InnoDB 存储引擎不得不停下来获得某行的锁定的次数。从 MySQL 5.5 版本起，InnoDB 就是默认的存储引擎，MySQL 对 InnoDB 表使用行级锁定。

应该设置告警的指标：

查询运行时间：管理关键数据库的延迟至关重要。如果生产环境中数据库的平均查询运行时间开始下降，应该寻找数据库实例的资源限制，行锁或表锁间可能的争夺，以及客户端查询模式的变化情况。

查询错误：查询错误的猛增可能暗示着客户端应用或数据库本身的问题。你可以使用sys 模式快速查找可能导致问题的查询。例如，列举出返回错误数最多的 10 条标准化语句：

SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;

Slow_queries：如何定义慢查询（并由此设置 long_query_time 参数）取决于你的用户案例。但是，无论你如何定义 “慢”，你都会想知道慢查询的数量是否超出了基准水平。为了找出真正执行缓慢的查询，你可以询问 sys 模式，或深入了解 MySQL 提供的慢查询日志（该功能默认是禁用的）。有关启用并读取慢查询日志的更多信心，请参考 MySQL 文档。

三、连接

mysql5.5之前默认数据库引擎是MyISAM，不过，5.5版本后，MySQL引入了InnoDB数据引擎。本文中使用的是mysql5.7，引擎为InnoDB，查询数据库引擎的方法如下：

SHOW VARIABLES LIKE 'default_storage_engine%';

QPS（每秒查询数），根据数据引擎不同计算方式不同；

针对MyISAM引擎为主的DB：QPS=questions/uptime=5172，mysql自启动以来的平均QPS，如果要计算某一时间段内的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=(q2-q1)/(t2-t1)

针对InnnoDB引擎为主的DB：QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一时间段内的QPS查询方法同上。

原文转载链接：https://blog.csdn.net/u011391839/article/details/108235161

测试派 Give testers a home.