一、查看profiling状态
mysql> select @@profiling;
0：表示为关闭
1：表示开启
二、开启 profiling
mysql> SET profiling=1;
三、show profiles 命令
mysql> show profiles; +----------+------------+--------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------+ | 1 | 0.00007100 | select profiling | | 2 | 0.00010800 | select @@profiling | | 3 | 0.00004875 | show profiling | | 4 | 0.00031000 | show tables | | 5 | 0.00015800 | select count(1) from emp | | 6 | 0.00017575 | select count(1) from dept | | 7 | 0.00021650 | select * from emp | | 8 | 0.00017050 | select * from emp where location_id='1800' | +----------+------------+--------------------------------------------+ 8 rows in set, 1 warning (0.00 sec)
查看所有语句消耗的具体时间（Duration）和使用的查询（Query ）
四、单query获取详细的profile的信息
语法：
show profile for query query_id
查看具体的query_ID所经历的过程以及消耗的资源详细分析报告
1. 查询 Query_ID=5信息
mysql> show profile for query 5; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000041 | | checking permissions | 0.000004 | | Opening tables | 0.000011 | | init | 0.000009 | | System lock | 0.000005 | | optimizing | 0.000003 | | statistics | 0.000007 | | preparing | 0.000006 | | executing | 0.000001 | | Sending data | 0.000026 | | end | 0.000002 | | query end | 0.000004 | | closing tables | 0.000004 | | freeing items | 0.000006 | | logging slow query | 0.000025 | | cleaning up | 0.000007 | +----------------------+----------+ 16 rows in set, 1 warning (0.00 sec)
2. 查询 Query_ID=5的cup、io信息
cpu
mysql> show profile cpu for query 5; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000041 | 0.000023 | 0.000014 | | checking permissions | 0.000004 | 0.000003 | 0.000002 | | Opening tables | 0.000011 | 0.000006 | 0.000004 | | init | 0.000009 | 0.000006 | 0.000003 | | System lock | 0.000005 | 0.000003 | 0.000002 | | optimizing | 0.000003 | 0.000001 | 0.000001 | | statistics | 0.000007 | 0.000004 | 0.000002 | | preparing | 0.000006 | 0.000004 | 0.000002 | | executing | 0.000001 | 0.000001 | 0.000001 | | Sending data | 0.000026 | 0.000016 | 0.000009 | | end | 0.000002 | 0.000001 | 0.000001 | | query end | 0.000004 | 0.000002 | 0.000001 | | closing tables | 0.000004 | 0.000003 | 0.000002 | | freeing items | 0.000006 | 0.000003 | 0.000002 | | logging slow query | 0.000025 | 0.000016 | 0.000009 | | cleaning up | 0.000007 | 0.000004 | 0.000003 | +----------------------+----------+----------+------------+
CPU_user：当前用户占用的 CPU；
CPU_system：当前系统占用的CPU。
io：
mysql> show profile block io for query 5; ±--------------±---------±-------------±--------------+ | Status | Duration | Block_ops_in | Block_ops_out | ±--------------±---------±-------------±--------------+ | starting | 0.000036 | 0 | 0 | | freeing items | 0.000009 | 0 | 0 | | cleaning up | 0.000004 | 0 | 0 | ±--------------±---------±-------------±--------------+ 3 rows in set, 1 warning (0.00 sec)
查看所有参数，使用如下命令：
show profile all for query 5