Mysql uses profile to analyze sql overhead code

This article brings you the code about Mysql using profile to analyze SQL overhead. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Mysql uses profile to analyze sql overhead

1.使用之前先查看当前数据库的版本信息,低版本无法使用.
    show version();  或者 show variables like '%version%'
2.查看profiling
    show variables like '%profil%'    ;

    result:
        +------------------------+-------+  
        | Variable_name          | Value |  
        +------------------------+-------+  
        | have_profiling         | YES   |   --只读变量，用于控制是否由系统变量开启或禁用profiling  
        | profiling              | OFF   |   --开启SQL语句剖析功能  
        | profiling_history_size | 15    |   --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用p

    show profiles; 查看是否开启,效果同上.
3.查看使用说明 
    help profile;
4.开启profile
    set profiling=1; 赋值时候不要有多余的空格.
5.运行sql,查看对应的profile
    select * from test ;
    show profiles;            

    result:
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------+
    | Query_ID | Duration   | Query                                                                                                                    |
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------+
    |       28 | 0.00033575 | select * from test                                                                                        |
    +----------+------------+--------------------------------------------------------------------------------------------------------------------------+

    分析sql性能,分析的时候可以加上对应的开销字段
    show profile [cpu,io][all] for query 28 ;

    show profile for query 28 ;

    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 5.7E-5   |
    | checking permissions | 7E-6     |
    | Opening tables       | 1.7E-5   |
    | init                 | 2.3E-5   |
    | System lock          | 8E-6     |
    | optimizing           | 5E-6     |
    | statistics           | 1.1E-5   |
    | preparing            | 9E-6     |
    | executing            | 3E-6     |
    | Sending data         | 8.8E-5   |
    | end                  | 5E-6     |
    | query end            | 6E-6     |
    | closing tables       | 5E-6     |
    | freeing items        | 7.8E-5   |
    | cleaning up          | 1.5E-5   |
    +----------------------+----------+
6.关闭
    set profiling=off;

The above is the detailed content of Mysql uses profile to analyze sql overhead code. For more information, please follow other related articles on the PHP Chinese website!