【MySQL数据库 | 第十九篇】SQL性能分析工具

目录

 

前言：

SQL执行频率：

慢查询日志：

profile：

profile各个指令：

总结：

---

前言：

        本篇我们将为大家讲解SQL性能的分析工具，而只有熟练的掌握了性能分析的工具，才可以更好的对SQL语句进行优化。虽然我们在自己练习的时候对这种优化感知并不明显，但是如果我们要处理几千几万条数据，那么这种优化带来的感知就会很强，因此我们要学好SQL语句的性能分析的工具，熟练掌握SQL的优化，才可以更加有把握解决现实生活中的实际问题

SQL执行频率：

        SQL执行频率是指在一定时间范围内某个SQL语句在数据库中被执行的次数。SQL执行频率的高低可以反映出该语句在实际业务中的重要性和影响力。

        对于一个复杂的应用系统，其数据库中可能存在大量的SQL语句，有些语句在实际使用中频率较高，有些则可能很少使用或者仅用于特定情况。对于频率较高的SQL语句，如果它们的执行效率低下，可能会导致数据库性能下降，影响整个系统的运行效率。

        因此，在进行SQL优化时，从SQL执行频率的角度出发，通常会优先考虑优化那些执行频率较高的查询语句。这些查询语句可能涉及到的数据表和字段也是比较重要的，需要认真进行索引设计和数据库结构优化。

        同时，SQL执行频率也可以作为数据库性能监控的一个指标。通过对SQL执行频率的监控、统计和分析，可以及时发现系统中执行频率较高的SQL语句，定位并解决数据库性能问题。

查询方法：
        MySQL客户端连接成功之后，通过 show [session | global] status 命令可以提供服务器状态信息，通过如下指令就可以当前数据库中 INSERT , UPDATE ,  DELETE , SELECT 的访问频次：

SHOW GLOBAL STATUS LIKE 'COM_______';

补充概念：服务器状态信息

        MySQL服务器状态信息是指数据库服务器当前的状态信息，包括各种性能指标、资源使用情况、连接信息、缓存和锁状态等。通过查看这些状态信息，可以了解数据库服务器的工作状态、瓶颈问题和性能瓶颈等，帮助我们进行系统优化和调整。

我们在 DataGrip 中进行查询：

 这些查询结果分别为：

• Com_binlog：   执行 BINLOG 操作的次数，即写二进制日志的次数；
• Com_commit： 执行 COMMIT 操作的次数，即提交事务的次数；
• Com_delete：   执行 DELETE 操作的次数，即删除数据的次数；
• Com_import：   执行 LOAD DATA 导入数据的次数；
• Com_insert：    执行 INSERT 操作的次数，即插入数据的次数；
• Com_repair：    执行 REPAIR TABLE 修复表的次数；
• Com_revoke：  执行 REVOKE 操作的次数，即取消授权的次数；
• Com_select：   执行 SELECT 操作的次数，即查询的次数；
• Com_signal：   执行 SIGNAL 操作的次数；
• Com_update： 执行 UPDATE 操作的次数，即更新数据的次数；
• Com_xa_end：执行 XA END 操作的次数，即提交分布式事务的次数。

这些命令在 MySQL 中具有不同的作用和用途，通过统计执行这些命令的次数，可以更加全面地了解 MySQL 服务器的工作状态和性能瓶颈，从而进行数据库性能优化和故障排查。

 tips:

在 MySQL 中，执行一次 SELECT 查询语句会生成多个命令请求，包括 Com_select、Handler_read_key、Handler_read_first、Handler_read_next 等。这些命令请求都会被累加到相应的命令计数器中。

如果在执行一次 SELECT 查询语句后，该命令计数器的值会增加 n，这是因为生成了 n 个命令请求。因此，如果你在 DataGrip 中执行多次 show global status 命令，每次命令执行都会使 com_select 计数器的值增加 n。

通过查询指令，我们可以快速的知道目前数据库哪些类型的语句占据数据库的绝大部分，也就确定了优先优化目标。 

那我们知道了优化的主要目标是哪类语句，我们又如何找出要对这类语句中的哪些语句进行优化呢？我们可以采用慢查询日志

慢查询日志：

        慢查询日志（slow query log）是 MySQL 中的一项功能，用于记录执行时间超过特定阈值的 SQL 语句的日志。慢查询日志包括每个 SQL 语句的执行时间、扫描的行数、使用的索引、执行的方式等信息。通过分析慢查询日志，可以定位数据库的性能问题，优化查询语句或数据库结构来提升系统性能。

        MySQL 提供了配置慢查询日志的相关参数，包括慢查询阈值、慢查询日志文件名、日志格式等。

        默认情况下，MySQL 没有开启慢查询日志。(这里是基于linux的，Linux会自动关闭)需要在 MySQL 配置文件（/ect/my.cnf）中设置 slow_query_log 参数为 ON，以开启慢查询日志的记录。另外，还需要设置 long_query_time 参数为一个时间阈值，单位为秒。当 SQL 查询的执行时间超过该阈值时，MySQL 会将该查询记录到慢查询日志中。

        通过慢查询日志，可以对具体的 SQL 查询语句进行性能分析和优化。一些常见的优化手段包括：修改查询语句的语法；创建或修改索引；优化表结构等。除此之外，还可以利用慢查询日志来监控数据库系统的性能瓶颈，分析表负载、索引使用情况、数据分布等方面的信息，以进一步完善数据库的性能和可用性。

检查慢查询日志是否开启：

SHOW VARIABLES LIKE 'SLOW_QUARY_LOG';

运行结果：

 没有开启的需要在Datagrip中配置如下信息：

SET GLOBAL slow_query_log = 1;  # 开启慢查询日志
 
SET GLOBAL long_query_time = 1;  # 设置慢查询阈值为 1 秒钟

设置慢查询阈值的意义是：只要待查询的语句超过了设定阈值，我们就会把他记入慢查询日志之中，供后续我们进行针对优化。
开启之后我们可以看到

 第一个是确定慢查询表已经开启记录，第二个则是告诉我们慢查询的表名。

那么我们可以直观的在文件资源管理器中查看慢查询表：

 点击进入Data：

 这个就是我们的慢查询表。

需要注意的是Data基本上都会被隐藏，需要我们先对这个文件以管理员模式进行访问，如果还是不显示的话，我们就在MySQL客户端执行这条语句：

 SELECT @@datadir;

点击执行后，运行结果就是Data的地址，我们直接访问就可以了

 

慢查询表：

 我们尝试执行这样一句：

select sleep (10);

执行结束后，我们查看慢查询表：

 我们可以看到有关于这条语句的执行用户，使用的哪一个数据库，以及执行的语句都会被记录下来。

profile：

MySQL的`PROFILE`是一个可以用来分析执行查询语句的工具。它可以让我们更好地了解查询执行过程中的各种时间和资源开销，包括：

• 1. 执行查询语句所需的时间
• 2. 查询语句返回的行数和大小
• 3. 系统在执行查询语句时所使用的资源，如CPU时间、磁盘和内存使用情况等

使用`PROFILE`可以帮助我们优化查询语句，发现慢查询问题，以及更好地了解MySQL数据库引擎执行查询的方式。

简单的来讲，使用profile可以告诉我们具体的每一条语句耗时多少，具体耗时在哪个环节，让我们可以做出更加具体的优化。

但是在使用之前，我们还要查询当前的MySQL是否支持profile

select @@have_profiling;

 通过查询我们可以看到目前是支持profile的，而默认情况下profile是关闭的，因此我们再次执行语句，查询当前profiling是否开启：

select @@profiling;

于是我们调用下面的语句开启profiling

set profiling =1;

运行结果：

profile各个指令：

1.查看每一条SQL的耗时基本情况

show profiles;

2.查看指定query_id的SQL语句的各阶段耗时：

show profile for query query_id;

3.查看指定query_if的SQL语句CPU使用情况：

show profile cpu for query query_id;

我们用语句1来举一个例子：

我们执行语句：

select name from emp where id =1;
 
show profiles ;

 这段SQL语句的作用大致如下：

1. 第一行查询上一个查询语句的执行结果中是否包含警告信息。
2. 第二行查询当前所在的数据库名称。
3. 第三行和第四行都是执行`SHOW WARNINGS`，查询语句是否产生了警告信息。
4. 第五行和第六行分别设置`net_write_timeout`和`SQL_SELECT_LIMIT`的值。
5. 第七行查询`emp`表中`id`为1的员工的姓名。
6. 第八行和第九行查询当前会话的事务隔离级别。
7. 第十一行将当前会话的事务隔离级别设置为读写模式。
8. 第十三行查询当前会话的事务隔离级别是否被设置为只读模式。
9. 第十四行和第十五行执行`SHOW WARNINGS`语句查询语句是否产生了警告信息。
10. 第十六行和第十七行都是重新设置`net_write_timeout`和`SQL_SELECT_LIMIT`的默认值。

总之，`SHOW WARNINGS`语句可以用来查看最后一次执行的查询语句是否产生了警告信息，警告信息可以包括查询语句执行过程中产生的一些异常和错误信息。

总结：

        本篇我们讲解如何利用 SQL执行频率，查询慢日志，profile来确定需要对哪些句子进行优化他们分别起到了：哪一部分需要被优化，哪些语句需要被优化，如何对语句进行更具体的优化的作用，但这三个也只是从时间角度粗略的评判哪些语句需要被优化，而下一篇我们将会讲解explain，他提供了查看执行计划的功能，在实际生活中我们也经常通过它来评判语句性能，因此大家要做好准备，熟练的掌握这一篇的内容，充满信心的学习下一篇章。

如果我的内容对你有帮助，请点赞，评论，收藏。创作不易，大家的支持就是我坚持下去的动力！