18 Show Profile

查看Profile是否开启

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set

# 如关闭,则开启
mysql> set profiling = on;
Query OK, 0 rows affected


mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set

分析Profile

# 首先执行几条SQL

# 查看Profiles
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 |    9.75E-5 | like                                |
|        2 | 0.00042825 | show variables like 'profiling'     |
|        3 | 0.08572125 | select * from a                     |
|        4 |  1.8947365 | select * from do                    |
|        5 |  0.0015365 | select * from t_wechat_customer_msg |
|        6 | 8.02308925 | select * from do group by rand()    |
+----------+------------+-------------------------------------+
6 rows in set

# 分析耗时较少的select查询
mysql> show profile cpu,block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 4.2E-5   | 0        | 0          | NULL         | NULL          |
| checking permissions | 5E-6     | 0        | 0          | NULL         | NULL          |
| Opening tables       | 0.001203 | 0        | 0          | NULL         | NULL          |
| System lock          | 9E-6     | 0        | 0          | NULL         | NULL          |
| init                 | 1.2E-5   | 0        | 0          | NULL         | NULL          |
| optimizing           | 5E-6     | 0        | 0          | NULL         | NULL          |
| statistics           | 6E-6     | 0        | 0          | NULL         | NULL          |
| preparing            | 6E-6     | 0        | 0          | NULL         | NULL          |
| executing            | 1E-6     | 0        | 0          | NULL         | NULL          |
| Sending data         | 4.4E-5   | 0        | 0          | NULL         | NULL          |
| end                  | 3E-6     | 0        | 0          | NULL         | NULL          |
| query end            | 3E-6     | 0        | 0          | NULL         | NULL          |
| closing tables       | 5E-6     | 0        | 0          | NULL         | NULL          |
| freeing items        | 0.00019  | 0        | 0          | NULL         | NULL          |
| logging slow query   | 2E-6     | 0        | 0          | NULL         | NULL          |
| cleaning up          | 1E-6     | 0        | 0          | NULL         | NULL          |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set


# 分析耗时较多的select查询
mysql> show profile cpu,block io for query 6;
+------------------------------+----------+----------+------------+--------------+---------------+
| Status                       | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+------------------------------+----------+----------+------------+--------------+---------------+
| starting                     | 6.6E-5   | 0        | 0          | NULL         | NULL          |
| checking permissions         | 8E-6     | 0        | 0          | NULL         | NULL          |
| Opening tables               | 0.001002 | 0        | 0          | NULL         | NULL          |
| System lock                  | 1.2E-5   | 0        | 0          | NULL         | NULL          |
| init                         | 1.4E-5   | 0        | 0          | NULL         | NULL          |
| optimizing                   | 4E-6     | 0        | 0          | NULL         | NULL          |
| statistics                   | 7E-6     | 0        | 0          | NULL         | NULL          |
| preparing                    | 8E-6     | 0        | 0          | NULL         | NULL          |
| Creating tmp table           | 5.8E-5   | 0        | 0          | NULL         | NULL          |
| executing                    | 3E-6     | 0        | 0          | NULL         | NULL          |
| Copying to tmp table         | 0.324864 | 0.327602 | 0          | NULL         | NULL          |
| converting HEAP to MyISAM    | 0.860471 | 0.858006 | 0          | NULL         | NULL          |
| Copying to tmp table on disk | 4.042798 | 4.009226 | 0.0156     | NULL         | NULL          |
| Sorting result               | 0.698543 | 0.452403 | 0.156001   | NULL         | NULL          |
| Sending data                 | 1.887769 | 0.436803 | 1.450809   | NULL         | NULL          |
| end                          | 8E-6     | 0        | 0          | NULL         | NULL          |
| removing tmp table           | 0.207213 | 0        | 0.0156     | NULL         | NULL          |
| end                          | 1.6E-5   | 0        | 0          | NULL         | NULL          |
| query end                    | 3E-6     | 0        | 0          | NULL         | NULL          |
| closing tables               | 7E-6     | 0        | 0          | NULL         | NULL          |
| freeing items                | 0.000215 | 0        | 0          | NULL         | NULL          |
| logging slow query           | 2E-6     | 0        | 0          | NULL         | NULL          |
| cleaning up                  | 2E-6     | 0        | 0          | NULL         | NULL          |
+------------------------------+----------+----------+------------+--------------+---------------+
23 rows in set

几种拖慢速度的Status

# converting HEAP to MyISAM
    查询结果太大,内存不够使用,要使用磁盘

# Copying to tmp table
    创建临时表
        1:创建临时表--Creating tmp table  
        2:拷贝数据到临时表--Copying to tmp table
        3:用完后删除临时表--removing tmp table   

# Copying to tmp table on disk
    把内存中的临时表复制到磁盘,会严重拖慢SQL执行速度

# locked
    锁表

profile几种参数类型

profile参数