数据库监控与调优

SayMeEveTime
72 阅读9分钟

数据库调优的维度

Mysql 性能优化金字塔法则

成本越来越高，效果越来越差

三层： 架构调优 --> MySQL自身调优--> 硬件和系统优化

七层： 业务需求 --> 系统架构--> SQL及索引 --> 表结构 --> 数据库参数配置 --> 系统配置 --> 硬件

**存储过程只要在创建编译一次，存储过程可以减少网络开销 **

测试数据准备与数据库操作工具

msyql 官方测试数据库 GitHub 官方导入 验证

慢查询日志

发现慢SQL的工具

  • Skywalking
  • VisualVM
  • JavaMelody

配置

1.修改 my.cnf 2. 通过全局变量设置

SET GLOBAL log_output='FILE,TABLE';
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=2;

查看慢查询日志

select * from mysql.slow_log;

mysql-dump-slow 分析

mysql-dump-slow [选项] 输入文件 [输出文件]

选项：

  • -h, --help：显示帮助信息并退出。

  • -V, --version：显示版本信息并退出。

  • -v, --verbose：启用详细输出。

  • -s, --summary：仅显示汇总信息。

  • -r, --raw：显示原始慢查询日志行。

  • -c config-file, --config config-file：指定要加载的配置文件。

  • -f log-format, --log-format log-format：指定慢查询日志格式。有效值包括：

    • classic：经典格式
    • general：通用格式
    • csv：CSV 格式
    • json：JSON 格式

  • -t, --time-format：指定时间格式。有效值包括：

    • iso：ISO 8601 格式
    • epoch：自纪元以来经过的秒数

  • -z, --gzip：使用 GZIP 压缩输出。

输入文件：

输入文件是包含慢查询日志行的文本文件。可以是经典格式、通用格式、CSV 格式或 JSON 格式。

输出文件（可选）：

输出文件是将分析结果写入的文本文件。默认为标准输出。

示例：

要分析经典格式的慢查询日志文件并生成摘要信息，可以使用以下命令：

mysql-dump-slow -s my-slow-query.log

要分析通用格式的慢查询日志文件并将结果写入 CSV 文件，可以使用以下命令：

mysql-dump-slow -f general -t epoch my-slow-query.log my-slow-query.csv

pt-query-digest 分析

pt-query-digest [选项] 输入文件 [输出文件]

选项：

  • -h, --help：显示帮助信息并退出。

  • -V, --version：显示版本信息并退出。

  • -s, --summary：仅显示摘要报告。

  • -f, --flamegraph：生成火焰图。

  • -r, --rank：按执行时间、调用次数或其他指标对慢查询进行排名。

  • -t, --trend：显示慢查询随时间推移的趋势。

  • -c config-file, --config config-file：指定要加载的配置文件。

  • -f log-format, --log-format log-format：指定慢查询日志格式。有效值包括：

    • classic：经典格式
    • general：通用格式
    • csv：CSV 格式
    • json：JSON 格式

  • -t, --time-format：指定时间格式。有效值包括：

    • iso：ISO 8601 格式
    • epoch：自纪元以来经过的秒数

  • -z, --gzip：使用 GZIP 压缩输出。

输入文件：

输入文件是包含慢查询日志行的文本文件。可以是经典格式、通用格式、CSV 格式或 JSON 格式。

输出文件（可选）：

输出文件是将分析结果写入的文本文件。默认为标准输出。

示例：

要分析经典格式的慢查询日志文件并生成摘要报告，可以使用以下命令：

pt-query-digest my-slow-query.log

要分析通用格式的慢查询日志文件并生成火焰图，可以使用以下命令：

pt-query-digest -f general my-slow-query.log

Explain

  • Explain 级别是 SQL 标准中定义的查询优化的级别，它指示优化器在生成查询计划时应提供的详细信息量。Explain 级别有以下几个选项：

    • **EXPLAIN：**提供有关查询计划的基本信息，包括表、连接和操作符。
    • **EXPLAIN ANALYZE：**除了 EXPLAIN 提供的信息外，还提供有关查询执行的统计信息，例如行数估计、实际执行时间和 I/O 成本。
    • **EXPLAIN EXTENDED：**提供有关查询计划的更详细的信息，包括操作符的详细信息、索引的使用情况和查询的并行化信息。
    • **EXPLAIN FORMAT=JSON：**以 JSON 格式提供查询计划信息。

    Explain 级别的选择取决于所需的详细信息级别：

    • **EXPLAIN：**对于快速了解查询计划的结构和操作符很有用。
    • **EXPLAIN ANALYZE：**对于诊断查询性能问题和识别潜在的瓶颈很有用。
    • **EXPLAIN EXTENDED：**对于深入了解查询计划的详细信息和优化策略很有用。
    • **EXPLAIN FORMAT=JSON：**对于以编程方式解析和处理查询计划信息很有用。

    示例：

    以下是如何使用不同的 Explain 级别来分析查询：

    -- EXPLAIN
EXPLAIN SELECT * FROM customers WHERE name LIKE '%John%';

-- EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM customers WHERE name LIKE '%John%';

-- EXPLAIN EXTENDED
EXPLAIN EXTENDED SELECT * FROM customers WHERE name LIKE '%John%';

-- EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT * FROM customers WHERE name LIKE '%John%';

    **注意：**Explain 级别的可用性可能因数据库系统而异。

  • EXPLAIN 查询输出的列因数据库系统而异，但通常包括以下信息：

    • **id：**操作符的唯一标识符。
    • **select_type：**操作符的类型，例如 SIMPLE、PRIMARY、UNION。
    • **table：**操作符引用的表名。
    • **partitions：**如果表被分区，则为分区号。
    • **type：**操作符的类型，例如 ALL、index、range。
    • **possible_keys：**查询可以使用的索引列表。
    • **key：**实际使用的索引（如果存在）。
    • **key_len：**使用的索引长度。
    • **ref：**用于连接表的列。
    • **rows：**估计要扫描的行数。
    • **filtered：**估计过滤掉的行的百分比。
    • **Extra：**其他信息，例如用于优化查询的提示。

    以下是对每个列的简要解释：

    • **id：**有助于识别查询计划中的操作符顺序。
    • **select_type：**指示操作符在查询中的作用，例如，PRIMARY 操作符是查询中的主表。
    • **table：**显示操作符引用的表。
    • **partitions：**如果表被分区，则显示分区号。
    • **type：**指示操作符的类型，例如，ALL 操作符扫描整个表，而 index 操作符使用索引。
    • **possible_keys：**列出查询可以使用的索引。
    • **key：**显示实际使用的索引（如果存在）。
    • **key_len：**显示使用的索引长度。这对于评估索引的使用效率非常有用。
    • **ref：**显示用于连接表的列。这对于理解连接是如何执行的非常有用。
    • **rows：**显示估计要扫描的行数。这对于了解查询的成本非常有用。
    • **filtered：**显示估计过滤掉的行的百分比。这对于评估索引的选择性非常有用。
    • **Extra：**显示其他信息，例如用于优化查询的提示。例如，它可能显示正在使用覆盖索引或查询正在并行执行。

    通过分析 EXPLAIN 输出中的这些列，可以深入了解查询计划并识别潜在的性能瓶颈。

SQL 性能分析

SHOW PROFILE

  • SHOW PROFILE 命令用于显示有关最近执行查询的性能信息，包括每个查询阶段花费的时间和资源使用情况。

    语法：

    SHOW PROFILE [ALL]
    • **ALL：**显示所有查询阶段的性能信息，包括准备、优化、执行和清理阶段。如果不指定 ALL，则只显示执行阶段的信息。

    输出：

    SHOW PROFILE 命令的输出包含以下列：

    • **Status：**查询的状态，例如正在运行、已完成或已中止。
    • **Duration：**查询的总执行时间。
    • **Rows examined：**在执行查询时检查的行数。
    • **Rows sent：**发送到客户端的行数。
    • **Avg. time：**每个阶段的平均执行时间。
    • **Calls：**每个阶段的调用次数。

    示例：

    以下是如何使用 SHOW PROFILE 分析查询的性能：

    SHOW PROFILE ALL;

    输出将类似于以下内容：

    | Status | Duration | Rows examined | Rows sent | Avg. time | Calls |
|---|---|---|---|---|---|
| Executing | 0.000023 | 100 | 100 | 0.000023 | 1 |
| Sending data | 0.000005 | 0 | 100 | 0.000005 | 1 |
| Copying to tmp table | 0.000003 | 100 | 0 | 0.000003 | 1 |
| Optimization | 0.000002 | 0 | 0 | 0.000002 | 1 |
| Preparing | 0.000001 | 0 | 0 | 0.000001 | 1 |
| Creating tmp table | 0.000001 | 0 | 0 | 0.000001 | 1 |
| Waiting for lock | 0.000000 | 0 | 0 | 0.000000 | 1 |
| Creating index | 0.000000 | 0 | 0 | 0.000000 | 1 |
| Waiting for table lock | 0.000000 | 0 | 0 | 0.000000 | 1 |
| Committing | 0.000000 | 0 | 0 | 0.000000 | 1 |

    此输出显示了查询的每个阶段的执行时间、检查的行数和发送的行数。通过分析此信息，可以识别查询中可能存在瓶颈的阶段。

Performance Schema

Performance Schema 是 MySQL 5.5 及更高版本中引入的一个功能，它提供了一个内部工具，用于监控和分析 MySQL 服务器的性能。它提供有关数据库活动、资源使用和等待事件的详细数据。

Performance Schema 的主要特性：

  • **会话状态：**跟踪每个会话的活动，包括正在执行的查询、使用的资源和等待事件。
  • **事件等待：**收集有关等待事件的信息，例如锁争用、I/O 操作和网络延迟。
  • **文件 I/O：**监控文件 I/O 操作，例如读取和写入延迟、缓冲区命中率和磁盘使用情况。
  • **表锁：**跟踪表锁信息，例如锁的类型、持有者和等待时间。
  • **语句摘要：**收集有关已执行语句的信息，例如执行时间、调用次数和资源使用情况。

使用 Performance Schema

要使用 Performance Schema，需要启用以下选项：

performance_schema=ON
performance_schema_instrument='%,innodb%,socket%,...'

启用后，可以通过以下方式访问 Performance Schema 数据：

  • **Performance Schema 表：**Performance Schema 提供了数百个表，其中包含有关数据库活动和性能的详细数据。这些表可以像普通表一样使用 SQL 查询。

  • **Performance Schema 函数：**Performance Schema 还提供了一组函数，用于访问和处理性能数据。

  • **第三方工具：**有许多第三方工具可以帮助可视化和分析 Performance Schema 数据，例如：

示例：

以下是如何使用 Performance Schema 表来查找等待时间最长的查询：

SELECT query, SUM(waits) AS total_waits
FROM performance_schema.events_waits_summary_by_user_by_event_name
GROUP BY query
ORDER BY total_waits DESC
LIMIT 10;

以下是如何使用 Performance Schema 函数来查找正在使用最多的文件：

SELECT file, SUM(count_read) + SUM(count_write) AS total_ops
FROM performance_schema.file_summary_by_file
GROUP BY file
ORDER BY total_ops DESC
LIMIT 10;

Performance Schema 是一个强大的工具，可用于深入了解 MySQL 服务器的性能并识别潜在的瓶颈

OPTIMIZER_TRACE

  • OPTIMIZER_TRACE 是 MySQL 中的一个系统变量，它允许查看查询优化的详细信息。它提供有关查询重写、索引选择和执行计划生成的信息。

    启用 OPTIMIZER_TRACE

    要启用 OPTIMIZER_TRACE，请使用以下命令：

    SET OPTIMIZER_TRACE="enabled=on";

    查看 OPTIMIZER_TRACE 输出

    启用 OPTIMIZER_TRACE 后，查询的优化详细信息将显示在查询结果中。输出将类似于以下内容：

    mysql> EXPLAIN SELECT * FROM t1 WHERE a > 10;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | t1         | ALL  | NULL           | NULL  | NULL    | NULL | 1000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Note  | 1003 | Optimizer trace: distinct_count(t1.a) = (1000 / 1000) |
| Note  | 1003 | Optimizer trace: pushed_condition_prefix: index(t1.a) |
| Note  | 1003 | Optimizer trace: index_condition(t1.a, 10)                  |
+-------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

    输出解释：

    • **Optimizer trace：distinct_count()：**估计使用 DISTINCT COUNT() 函数返回的不同值的个数。
    • **Optimizer trace：pushed_condition_prefix：**将条件下推到索引扫描操作中。
    • **Optimizer trace：index_condition()：**使用索引上的条件来过滤数据。

    禁用 OPTIMIZER_TRACE

    要禁用 OPTIMIZER_TRACE，请使用以下命令：

    SET OPTIMIZER_TRACE="enabled=off";

    注意：

    • OPTIMIZER_TRACE 输出仅在启用了慢查询日志记录时才可用。
    • OPTIMIZER_TRACE 可能会影响查询的性能，因此在生产环境中不建议长期启用它。
avatar
文章
阅读
粉丝