MYSQL-性能分析工具

目录

准备测试数据

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

-- 创建employees表CREATETABLE employees (
  id INTAUTO_INCREMENTPRIMARYKEY,
  name VARCHAR(50),
  salary DECIMAL(10,2));-- 创建存储过程CREATEPROCEDURE generate_test_data()BEGINDECLARE i INTDEFAULT0;DECLARE name_value VARCHAR(50);DECLARE salary_value DECIMAL(10,2);WHILE i <600000DOSET name_value = CONCAT('姓名', i);-- 假设每个名字都是"姓名"加上一个数字SET salary_value = FLOOR(RAND()*10000);-- 假设薪水在0到10000之间INSERTINTO employees (name, salary)VALUES(name_value, salary_value);SET i = i +1;ENDWHILE;END;-- 调用存储过程，生成60w条数据CALL generate_test_data();

SQL 执行频率

MySQL客户端连接成功后，通过

show [sessionlglobal status

命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOWGLOBALSTATUSLIKE'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;SELECTCOUNT(*)FROM employees e;

查看 profiles

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

profiles

：

show profiles;# 显示最近的几次查询-- Output+----------+------------+-----------------------------------+| Query_ID | Duration   | Query                             |+----------+------------+-----------------------------------+|1|0.01894200|SELECTCOUNT(*)FROM employees e ||2|0.02037825|SELECTCOUNT(*)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 ontransaction|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 forhandlercommit|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 ontransaction|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 forhandlercommit|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>EXPLAINSELECT*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|+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+1rowinset,1 warning (0.00 sec)

多表查询

EXPLAINSELECT  e.*FROM employees e
LEFTJOIN 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|Usingindex|+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+2rowsinset,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需要进行外部排序等。