profile日志分析
通过使用explain命令查看执行计划,并对SQL调优后,如果还想对SQL执行过程更详细的了解,查找慢更底层的原因,可以使用profile分析。
打开日志记录
先查看profile配置
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set
在当前会话中打开profile配置
mysql> set profiling=on;
Query OK, 0 rows affected
执行几个SQL
select count(1) from tb_v_user;
select count(1) from tb_v_user;
select user_id,count(1) from tb_vote group by user_id;
看profile信息
mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------+
| 1 | 0.2433095 | select count(1) from tb_v_user |
| 2 | 0.00085075 | unlock tables |
| 3 | 0.114828 | select count(1) from tb_v_user |
| 4 | 0.1181025 | select count(1) from tb_v_user |
| 5 | 0.11777725 | select count(1) from tb_v_user |
| 6 | 4.482654 | select user_id,count(1) from tb_vote group by user_id |
+----------+------------+-------------------------------------------------------+
6 rows in set
Query_ID:收集到的执行SQL的序列号,后面指定要分析那一条SQL时候需要用到这个值
Duration:执行SQL耗时
Query:SQL
分析日志记录
先使用如下命令参数。不知道为什么这个命令在navicat中不能识别,我是登录到虚拟机中打开mysql客户端执行的
mysql> ? show profile
Name: 'SHOW PROFILE'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
type列出来了很多,但我一般就看看CPU和BLOCK IO,如果通过这两项看不出什么问题,可以再挨个查看一下
如果你想看所有的信息,那么可以这样写
show profile all for query 1
查看上面日志收集到的第一条sql的所有执行过程信息(步骤、耗时),返回的列会非常多,我就不贴出来了。
查看一下执行慢的SQL(ID=6)比如:
show profile CPU,BLOCK IO for query 6
mysql> show profile CPU,BLOCK IO for query 6;
+---------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+---------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000101 | NULL | NULL | NULL | NULL |
| checking permissions | 6E-6 | NULL | NULL | NULL | NULL |
| Opening tables | 0.004756 | NULL | NULL | NULL