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性能分析就讲到这里了。

公众号：白巧克力LIN

该公众号发布Python、数据库、Linux、Flask、Django、自动化测试、Git、算法、前端、服务器等相关文章！

- END -