MYSQL-性能分析工具

于 2024-07-30 16:52:57 发布
阅读量775 收藏 9
点赞数 13
分类专栏： MYSQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51329147/article/details/140800384
版权

目录

准备测试数据

创建存储过程，生成 10w 条数据。

-- 创建employees表
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10, 2)
);

-- 创建存储过程
CREATE PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE name_value VARCHAR(50);
  DECLARE salary_value DECIMAL(10, 2);
  WHILE i < 600000 DO
    SET name_value = CONCAT('姓名', i); -- 假设每个名字都是"姓名"加上一个数字
    SET salary_value = FLOOR(RAND() * 10000); -- 假设薪水在0到10000之间
    INSERT INTO employees (name, salary) VALUES (name_value, salary_value);
    SET i = i + 1;
  END WHILE;
END;

-- 调用存储过程，生成60w条数据
CALL generate_test_data();

SQL 执行频率

MySQL客户端连接成功后，通过show [sessionlglobal status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOW GLOBAL STATUS LIKE 'Com_';

// output 表示当前数据库的操作频次
|Variable_name|Value|
|-------------+-----+
|Com_binlog   |0    |
|Com_commit   |25   |
|Com_delete   |17   |
|Com_import   |0    |
|Com_insert   |22   |
|Com_repair   |0    |
|Com_revoke   |0    |
|Com_select   |581  |
|Com_signal   |0    |
|Com_update   |21   |
|Com_xa_end   |0    |

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位:秒，默认10秒）的所有SQL语句的日志。通过分析慢查询日志，可以找到数据库性能的瓶颈，并进行优化。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

启用慢查询日志
  1. 查看是否开启慢查询
SHOW VARIABLES LIKE 'slow_query_log';

|Variable_name |Value|
|--------------+-----+
|slow_query_log|ON   |
  1. 修改配置文件 my.cnf（Linux） 或 my.ini（Window）：
slow_query_log = 1
slow_query_log_file = D:/MYSQL/logger/slow-query.log
long_query_time = 2  # 设置慢查询的时间阈值为2秒
  1. 重启 MYSQL。
查看慢查询日志

直接查看日志文件：

cat /path/to/your/slow-query.log


// 日志格式
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.38 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
停用慢查询日志

在配置文件中设置：

slow_query_log = 0

show profiles

慢查询记录的是执行耗时时间超过预设值 2 秒的查询记录，假设现在需要优化 1 秒以内的 SQL 查询，如何定位这条记录呢？
show profiles 能够在做 SOL 优化时帮助我们了解时间都耗费到哪一条具体的 SQL 语句，从而快速定位。

查看是否开启 profiles

select @@profiling;

-- Output
|@@profiling|
|-----------+
|          0|
// 或者

show variables like 'profiling';

-- Output
Variable_name|Value|
-------------+-----+
profiling    |OFF  |

profiling = 0 代表关闭，我们需要把 profiling 打开，即设置为 1：

set profiling=1;

-- Output
|Variable_name|Value|
|-------------+-----+
|profiling    |ON   |

多次执行的 SQL 查询

SELECT  e.* FROM employees e;

SELECT  COUNT(*) FROM employees e;

查看 profiles

查看当前会话所产生的所有 profiles：

show profiles; # 显示最近的几次查询

-- Output
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.01894200 | SELECT  COUNT(*) FROM employees e |
|        2 | 0.02037825 | SELECT  COUNT(*) FROM employees e |
|        3 | 0.35443900 | SELECT  e.* FROM employees e      |
+----------+------------+-----------------------------------+

查看具体 SQL 的执行时间

用户可以根据 QUERY ID 查询执行 SQL 语句各个阶段的耗时。

show profile for query 3;

-- outpout
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000070 |
| Executing hook on transaction  | 0.000002 |
| starting                       | 0.000023 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000031 |
| init                           | 0.000003 |
| System lock                    | 0.000007 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000012 |
| preparing                      | 0.000014 |
| executing                      | 0.354195 |    -- 执行操作耗时最久
| end                            | 0.000016 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000011 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000018 |
| cleaning up                    | 0.000009 |
+--------------------------------+----------+

此外，还可以查询更丰富的内容：

show profile cpu,block io for query 3;

-- Output
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000070 | 0.000000 |   0.000000 |         NULL |          NULL |
| Executing hook on transaction  | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| starting                       | 0.000023 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables                 | 0.000031 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock                    | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing                     | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics                     | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing                      | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing                      | 0.354195 | 0.328125 |   0.031250 |         NULL |          NULL |
| end                            | 0.000016 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end                      | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables                 | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items                  | 0.000018 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up                    | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+

explain

上述四种方法均以执行时间作为衡量SQL性能的标准，但这种方法仅能提供大致的评估，并非精确衡量SQL语句性能的手段。为了深入理解SQL语句的执行效率，我们应利用 EXPLAIN 命令来分析其执行计划。

EXPLAIN是 MySQL 中的一项关键功能，它用于剖析 SQL 查询的执行过程。通过 EXPLAIN，我们可以获得 MySQL 执行SELECT语句的详细步骤，这有助于我们洞察数据库引擎对查询的优化策略和处理细节。

基本查询

单表查询
mysql> EXPLAIN SELECT  * FROM employees ;

-- Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 550116 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
多表查询
EXPLAIN 
SELECT  e.* FROM employees e
LEFT JOIN test_table tt 
ON e.id = tt.id;

-- Output
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      | 550116 |   100.00 | NULL        |
|  1 | SIMPLE      | tt    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.e.id |      1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

参数说明

以下是一些主要的EXPLAIN输出参数及其含义：

  1. id：查询中每个表的顺序编号，标识查询执行的顺序。

    • 如果 id 相同，从上往下，顺序执行。
    • id 值越大，执行优先级越高，越先被执行。

  2. select_type：表示查询类型，例如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表，从FROM子句中临时创建的表）等。

  3. table：当前正在访问的表名。

  4. type：访问类型，反映了MySQL在表中找到所需行的方式，

    • 如ALL（全表扫描）、INDEX（对整个索引进行扫描）、RANGE（范围扫描）、EQ_REF（唯一性索引扫描）、REF（非唯一性索引扫描）、CONST（对于主键或唯一索引）等。
    • 性能由好到差：Null > system > const > eq_ref > ref > range > index > all。

  5. possible_keys：该查询可能用到的索引列表。

  6. key：实际使用的索引，如果没有选择任何索引，则显示NULL。

  7. key_len：使用的索引长度，可以帮助推算出 MySQL 实际检索了多少个索引项。

  8. ref：显示与索引相对应的列或者常量，比如关联操作中的列引用。

  9. rows：MySQL根据统计信息估算的需要读取的数据行数，影响性能的一个重要因素。

  10. filtered：表示存储引擎返回的数据在服务器层过滤后，预计能剩下多少行的比例。

  11. Extra：提供额外信息。

    • Using index表示使用覆盖索引
    • Using where表示在存储引擎层应用WHERE条件过滤
    • Using temporary表示使用了临时表
    • Using filesort表示MySQL需要进行外部排序等。
RainbowJier
关注
  • 13
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL高级篇——性能分析工具
vincewm的博客
05-01 5063
MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long-query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。它的主要作用是，帮助我们发现那些执行时间特别长的 SOL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
MySQL 高级篇 -- 性能分析
L小Ray想有腮
03-26 5617
profiling、mysqldumpslow、trace、sys.schema 相关知识。
MySQL之性能分析工具
宋文轩
06-30 755
MySQL之性能分析工具
MySQL-自带工具介绍
大虾好吃吗
03-30 1921
MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql，mysqladmin，mysqldump等等
MySQL四种SQL性能分析工具
qq_53267860的博客
05-31 4095
目录MySQL四种SQL性能分析工具1.SQL性能分析2.查看SQL执行频率3.慢查询日志3.1概念3.2查看慢查询日志变量、文件、时间3.3.开启慢查询日志3.4设置慢查询时间3.5查看慢查询日志文件3.6使用慢查询日志3.7检查慢查询日志4.profile详情4.1profile4.2查看是否支持profile4.3查看profile开关4.4开启profiling4.5使用profile4.5.1查看每条SQL耗时4.5.2查看指定SQL各阶段耗时4.5.3查看指定SQL各阶段CPU使用情况5.exp
MysqlWorkBench性能分析工具--性能仪表盘
xiangjai的专栏
05-26 1391
MySQLworkbench可以在图形化仪表盘中显示服务器性能统计信息。如果想要显示仪表盘，请打开一个数据库服务器，然后从左侧导航中选择管理（"management"）选项卡。然后单击仪表盘("dashboard")。 仪表盘位置 下图显示了仪表盘中的信息布局 注意 此功能需要MySQL server5.6或者更高版本 仪表盘布局 网络状态(Network Status) 这里突出显示了Mysql服务器通过客户端连接发送和接受的网络流量的统计数据，包括传入网络流量，传出网络流..
mysql数据库性能测试工具推荐
02-21 5101
一.sysbench方法如何使用 以下列出了sysbench对测试数据库mysql时常用的选项。 [root@xuexi~]#sysbench--help Usage: sysbench[options]...[test_lua][lua_options][command] Commandsimplementedbymosttests:prepareruncleanuphelp 通用选项：以下中括号中的值表示默认值 --threads=N...
MySQL--SQL执行分析工具
BtWangZhi的博客
03-04 3227
explain 使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQl是如何处理你的SQl语句的，分析你的查询语句或是表结构的性能瓶颈。 表的读取顺序， 数据读取操作的操作类型 哪些索引可以使用， 哪些索引被实际使用。 表之间的引用， 每张表有多少行被优化器查询。 Explain各个列名说明 id id相同，执行顺序由上到下。 ID不同，如果是子查询，id的序号会递增，id值...
MySQL之四种SQL性能分析工具
weixin_52690231的博客
03-15 6876
MySQL知识总结之四种SQL性能分析工具
MySQL性能分析命令
疾风先生的博客
06-04 1684
服务性能简介 三个性能服务请求 如何确认服务器是否达到了性能最佳状态 找出某条sql语句执行慢的原因 诊断被用户描述成“停顿”,“堆积”或者是“卡死”的间歇性疑难故障. 性能定义 为完成某件任务所需要的时间度量,换言之,性能即响应时间,这是一个非常重要的原则. SQL性能分析 执行分析 - 基于执行时间的分析 等待分析 - 基于等待时间的分析 SQL性能优化 在一定的工作负载下尽可能地降低响应时间,在SQL服务器中定义的吞吐量指标为单位时间内的查询量. 性能优化工具 慢查询日志是
MySQL性能分析工具profile使用教程
09-10
MySQL性能分析工具profile是用于分析SQL执行效率的重要工具，它可以帮助数据库管理员理解SQL语句的执行过程，识别潜在的性能瓶颈，并对SQL进行优化。在MySQL中，profile提供了关于SQL执行时间分布的详细信息，包括...
性能测试 mysql 耗时分析工具
03-21
MySQL提供了内置的性能分析工具，如`EXPLAIN`和`SHOW VARIABLES`，它们可以帮助我们了解查询执行计划和系统配置。 `EXPLAIN`命令是分析SQL查询性能的基础工具，它可以展示MySQL如何执行一个SELECT语句，包括表的...
MySQL-tpch测试工具简要手册
09-16
MySQL TPCH测试工具是用于评估决策支持系统(DSS)在商业分析中OLAP(在线分析处理)性能的一个标准工具包。这个工具由TPC（Transaction Processing Performance Council）组织提供，它包含一系列针对商业的ad-hoc查询和...
MySQL-tpch 测试工具简要手册
09-10
MySQL-tpch测试工具是用来评估决策支持系统（DSS）在商业分析中的性能，特别是针对在线分析处理（OLAP）场景。TPC（Transaction Processing Performance Council）是一个制定基准测试标准的组织，其推出的TPC-H工具...
详解MySQL监控工具 mysql-monitor
09-08
总结来说，`mysql-monitor`是一个实用的MySQL监控和优化工具，它提供了一站式的解决方案，便于监控数据库性能，优化SQL，分析表结构，并且有强大的可视化界面。对于那些需要管理MySQL数据库的团队来说，这是一个极具...
数据库安全：MySQL安全配置，MySQL安全基线检查加固
wangyuxiang946的博客
07-23 2614
MySQL基线检查，基线配置，安全加固
mysql的主从复制和读写分离
Hai990218的博客
07-24 1545
介于异步和全同步之间，主库更新完事务之后，也是同步到从库，同步完成之后有一个等待时间，等待时间是一个tcp/ip的往返时间，一般都是5毫秒左右。mysql的默认模式：主库在更新完事务之后会立即吧结果返回给从服务器，并不关心从库是否接收到，以及从库是否处理成功。控制innodb缓冲池的大小，增大可以提高数据库的性能，但是占用的是系统内存，配置的时候要注意合理化使用。从库配置更好的硬件，提升随机写的性能。实现是基于mysql的二进制日志，根据主库的二进制文件的标志位，实现主和从的同步。
mysql高阶语句
最新发布
qq_59169480的博客
07-24 637
高级语法的查询语句。
MySQL常见指令
weixin_47755728的博客
07-23 471
如数值类型包括整数类型和浮点数类型 整数类型根据占用的存储空间的不同 又包括TINYINT(1字节)，SMALLINT(2字节)，INT(4字节)，BIGINT(8字节)，可以存储不同范围的整数。浮点数类型包括FLOAT(4字节)和DOUBLE(8字节)，可以存储不同范围的浮点数。日期和时间类型包括DATE日期, TIME时间, DATETIME日期时间, TIMESTAMP时间戳。格式 : grant select on 数据库 .* to 用户名@登录主机 identified by ”密码"
MySQL的性能分析工具
06-22
MySQL提供了一些内置的性能分析工具来帮助优化数据库性能。以下是几个常用的工具： 1. **EXPLAIN**：这是最基础的性能分析工具，用于查看SQL语句的执行计划。当你运行一个查询时，可以添加`EXPLAIN`关键字，MySQL会返回一个执行计划，显示了如何解析、选择数据和排序结果[^4]。 ```sql EXPLAIN SELECT * FROM table_name WHERE condition; ``` 2. **SHOW STATUS**：这个命令显示了服务器的实时状态信息，包括连接数、查询缓存使用情况、内存使用等，可以帮助识别潜在的性能瓶颈[^5]。 3. **SHOW VARIABLES**：用于查看和修改配置参数，有些参数（如`innodb_buffer_pool_size`）对性能有直接影响[^5]。 4. **Percona Toolkit**：这是一个第三方工具集，包括pt-query-digest（用于分析慢查询）、pt-blackhole（模拟大量写入），以及pt-table-checksum（检查表的完整性）[^6]。 5. **MySQL Workbench**：虽然主要是GUI工具，但其性能分析功能也很强大，可以创建和执行查询，查看执行计划，并提供可视化报告[^7]。 6. **Performance Schema**：这是一个专为性能监控设计的内建功能，提供了一种低开销的方式来收集和分析数据库活动[^8]。 7. **InnoDB Monitor**：对于InnoDB存储引擎，提供了实时监控和诊断工具，帮助检测锁争用和其他内部活动[^9]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值