18 Show Profile

作者: 笑Skr人啊 | 来源:发表于2018-01-02 17:20 被阅读17次

    查看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参数

    相关文章

      网友评论

        本文标题:18 Show Profile

        本文链接:https://www.haomeiwen.com/subject/wllqnxtx.html