mysql查询日志 6.MySQL之SQL性能分析工具(查看SQL执行频率,慢查询日志,profile指令查询耗时情况,explain查看执行计划)

6.1 查看SQL执行频率

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

show global status like 'com_______';# 共7个下划线 _

示例：

6.2 慢查询日志

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

1. 查看当前慢查询日志是否开启

show variables like 'slow_query_log';

示例：

2. 开启慢查询日志

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

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

示例：

编辑MySQL配置文件

配置相关参数

重启MySQL

进入MySQL查看

可以看到慢查询日志开关已经打开了

可以在/var/lib/mysql查看到该慢日志文件：

测试

可以先执行 tail 命令用于实时追踪日志尾部新增文件

查询一条800w的数据量，耗时超过2s

可以看到已被记录

6.3 profile指令查询耗时情况

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling 参数，能够看到当前MySQL是否支持 profile 操作：

select @@have_profiling;

默认profiling是关闭的，可以通过set语句在 session/global 级别开启 profiling：

set profiling = 1;

查询指令的相关耗时情况：

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

show profiles;

查看指定 query_id 的SQL语句各个阶段的耗时情况：

show profile for query Query_ID;

查看指定query_id的SQL语句CPU的使用情况

show profile cpu for query Query_ID;

6.4 explain查看执行计划

explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。

#直接在 select 语句之前加上关键字 explain / descexplain select 字段列表 from 表名 where 条件;

执行计划中各个字段的含义 id：

select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）

示例：

连接查询：

根据产品ID查询指定产品名称和对应备注

id相同，执行顺序从上到下

子查询：

根据产品名称查看对应产品的备注

id不同，值越大，越先执行

select_type：

表示 select 的类型，常见的取值有 simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union联合查询中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等

partitions：

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表中的分区情况。

type：

表示连接类型，性能 由好到差 的连接类型为

NULL 一般查询时不访问任何表才会出现

system 访问一张系统表

const 主键或者唯一索引

eq_ref 知道查询结果只有一个

ref 使用了索引但是索引列值不唯一

range 有范围的索引扫描

index 按照索引顺序进行全表扫描，没比全表扫描快多少

all 全表扫描

possible_key：

显示可能应用在这张表上的索引，一个或多个

key:

实际使用的索引，如果为NULL，则没有使用索引

Key_len：

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

ref：

当使用常量等值查询，显示const，

当关联查询时，会显示相应关联表的关联字段

如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func

其他情况null

rows：

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是以个估计值，可能并不总是准确的

filtered：

表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

Extra：

不适合在其他列中显示的信息，explain 中的很多额外的信息会在 Extra 字段显示