Mysql explainとprofile


explain extended + show warnings
explain extended:   explain                   。
       show warnings                ,            。
     filtered  ,        ,rows * filtered/100          explain             
(      explain   id     id    )。    
mysql> show columns from admin;
+------------+-----------+------+-----+-------------------+----------------+
| Field      | Type      | Null | Key | Default           | Extra          |
+------------+-----------+------+-----+-------------------+----------------+
| id         | int(11)   | NO   | PRI | NULL              | auto_increment |
| email      | char(30)  | NO   | UNI | NULL              |                |
| pwd        | char(60)  | NO   |     | NULL              |                |
| token      | char(60)  | NO   |     |                   |                |
| id_char    | char(35)  | NO   | UNI |                   |                |
| create_at  | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | timestamp | YES  |     | NULL              |                |
+------------+-----------+------+-----+-------------------+----------------+
7 rows in set (0.01 sec)

mysql> explain extended select id,email from admin;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | admin | index | NULL          | admin_pk | 120     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                       |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `hyperf`.`admin`.`id` AS `id`,`hyperf`.`admin`.`email` AS `email` from `hyperf`.`admin` |
+-------+------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

explain partitions
   explain     partitions   ,            ,           。

explain sql
  select        explain    ,
MySQL            ,     ,          ,       SQL
(   from       ,        ,         )


リファレンス
id列
id      select     ,    select     id,
  id      select         

select_type
  • simple:サブクエリとunion
  • を含まない単純なクエリ
  • primary:複雑なクエリーの最外層のselect
  • subquery:selectに含まれるサブクエリ(from句ではない)
  • derived:from句に含まれるサブクエリ.MySQLは結果を一時テーブルに格納します.派生テーブル(derivedの英語の意味)
  • とも呼ばれます.
  • union:unionの2番目とその後のselect
  • union result:unionテンポラリテーブルから結果を取得するselect
  • table
                   

    type
          (the join type),                 (    )
    system  const  eq_ref   ref   range  index   all
                                         
    
    ref:reference(re,fen,ce)[  ]
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  • NULL mysql ,
  • explain select min(id) from table;
              ,           ,          
  • system , , IO;
  • # mysql        time_zone ,          ,       IO
    explain select * from mysql.time_zone;
    
    #pkId   const, tmp     system
    #mysql                      
    explain select * from ( select * from user where pkId=1 ) tmp;
  • const
  • mysql                      
    1.    (primary key)    (unique)  
    2.           (const) ,pkId = 1
    tip.      
  • eq_ref (primary key) (unique not null)
  •         (row),         
    1.join  ;
    2.    (primary key)      (unique not null)  ;
    3.    ;
  • ref
  • 1.    
    2.        (row),               
    3.       ,  const    ref,                
  • ref_or_null ref, NULL 。
  • index_merge
  •   :id   ,tenant_id     。or        primary key,      primary key(id)   tenant_id   
    explain select * from role where id = 11011 or tenant_id = 8888;
  • range
  • sql   between in, in, >, <  
  • index
  •             
    explain select COUNT(*) from user;
  • ALL (full table scan)

  • possible_keys
                  。    ,       。        WHERE            

    key
           。   NULL,       。      ,MYSQL          。     ,   SELECT     USE INDEX(indexname)            IGNORE INDEX(indexname)   MYSQL    

    key_len
            。           ,      
       
        char(n):n    
        varchar(n):2         ,   utf-8,    3n + 2
        
        tinyint:1  
        smallint:2  
        int:4  
        bigint:8    
         
        date:3  
        timestamp:4  
        datetime:8  
            NULL,  1        NULL
           768  ,       ,mysql              ,
                   。

    ref
                ,      ,     

    rows
    MYSQL                  

    extra:クエリーの追加情報の解析
  • distinct
  •   mysql            ,      
  • Using where
  • SQL   where      ,     type   ,  type   all,      
  • Using index
  • SQL                   ,           ,        ,    
  • Using index condition
  •        ,               ,           ,      
  • Using filesort
  •        ,             。  SQL      ,      ,
                   order by,    filesort,        
  • Using temporary
  •        (temporary table)       。  SQL      ,         。
       ,group by order by    ,          ,       ,           
  • Using join buffer (Block Nested Loop)
  •           ,      type  ALL,rows  4,      4*4   。
      SQL         ,      。
       ,     join,          ,        。
            ,          ,          。

    profile
    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;