MySQL进阶——SQL性能分析

在上篇文章我们学习了MySQL进阶——存储引擎,这篇文章学习MySQL进阶——SQL性能分析。

SQL性能分析主要是从SQL语句执行频率、耗时时间、CPU使用情况和执行时表连接情况进行分析,常用的方法工具有:SQL执行频率、慢查询日志、profile详情和explain执行计划。

SQL执行频率

通过show [session|global] status命令可以提供服务器状态信息,通过如下命令,可以查看当前数据库的增删改查的访问频率:

SHOW GLOBAL STATUS LIKE 'Com_______';

其中:一个‘_’表示一个字符。

运行结果如下

慢查询日志

通过SQL执行频率,我们可以发现增数据的SQL语句执行频率更多,但无法知道哪条SQL语句执行情况。

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。

执行如下命令查看是否开启慢查询日志,

show variables like 'slow_query_log';

配置文件开启

默认情况下,MySQL的慢查询日志是关闭的,我们可以在MySQL的配置文件(/etc/my.cnf)添加如下命令开启慢查询日志,

slow_query_log=1  # 开启慢查询日志
long_query_time=2  # 设置慢查询日志的时间为2秒,SQL语句执行超过2秒,就视为慢查询,记录慢查询日志

 

注意:如果是在docker安装的mysql,需要先执行如下命令,进入MySQL容器内部再在MySQL配置文件中添加开启慢查询日志代码。

docker exec -it 容器ID  /bin/bash

在/etc/my.cnf文件添加如下图代码:

 

配置完成后需要重启MySQL服务器。

查看开启慢查询日志,如下图所示:

 

SQL命令开启

当然我们也可以通过在mysql中执行如下命令,开启慢查询日志,

set global slow_query_log = ON;
set global slow_launch_time = 3;
show variables like 'slow%';

 

注意:这种开启方式不是永久开启慢查询日志,只要MySQL重启了,慢查询日志就会关闭。

示例

当我们执行了SQL语句而操作时长超过2秒,就会记录慢查询,这里我们执行了删除UserTable表操作,大概用了10秒,慢日志如下图所示:

 

在日志中,我们可以看到执行日期、时长、用户、IP、数据库和SQL语句等相关信息。

profile详情

在慢查询日志中,我们只能获取超过设置的时间SQL语句信息,例如设置的时长为2秒,那么只能获取超过2秒的SQL语句信息,执行了1.99秒的SQL语句无法获取,这时我们就可以使用profie详情。

profiles详情能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

首先通过have_profiling参数,查看当前MySQL是否支持profile操作:

SELECT @@have_profiling;

如下图所示:

 

接着执行如下代码查看profile是否开启,

SELECT @@profiling;   # 查看是否开启
SET profiling=1;   # 开启profile

如下图所示:

 

接下来我们就可以使用show profiles命令查看SQL语句详情的执行时间了,如下图所示:

 

这样我们就可以查看每条SQL语句的耗时情况,我们可以通过上面的Query_ID详细地查看SQL语句各阶段的耗时情况、CPU使用情况,可以执行如下代码:

# show profile for query Query_ID;   # 查看SQL语句各阶段的耗时情况
# show profile cpu for query Query_ID;  # 查看SQL语句CPU使用情况

如下图所示:

 

explain执行计划

在上面的SQL性能分析中,我们只能获取到SQL语句的频率和耗时时间,无法知道SQL语句的执行过程中的表连接情况,这时我们可以通过explain或desc命令来查看SQL语句的执行过程中的表连接情况,其使用方法如下:

explain/desc SQL语句;

如下图所示:

 

其中:

  • ID:select查询的序列号,表示查询中执行select子句或者操作表的顺序,ID相同,执行顺序从上到下,ID不同,值越大,越先执行;
  • select_type:SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBOUERY(SELECT/WHERE之后包含了子查询);
  • type:连接类型,性能由好到差的类型为:NULL、system、const、eq_ref、range、index、all;
  • possible_keys:可能用到的索引,一个或多个;
  • key:实际使用的索引,如果为NULL,则没有使用索引;
  • Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好;
  • rows:MySQL认为必要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的;
  • filtered:返回结果的行数占需读取行数的百分比,filtered的值越大越好;

好了,SQL性能分析就讲到这里了。

原创文章,作者:guozi,如若转载,请注明出处:https://www.sudun.com/ask/90236.html

(0)
guozi's avatarguozi
上一篇 2024年6月5日 下午2:55
下一篇 2024年6月5日 下午3:02

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注