单条sql分析诊断思路

1.1.1 查看执行计划

  

mysql> expain  select DISTINCT a.offer_id      AS OFFERID,           

    ->                  a.OFFER_SPEC_ID ASOFFERSPECID,           

    ->                  c.NAME,           

    ->                  c.offer_type_cd ASOFFERTYPECD,           

    ->                  a.party_id      AS PARTYID           

    ->   from offer a, offer_spec c           

    ->  where c.OFFER_TYPE_CD = 1           

    ->    AND c.AGREEMENT_TYPE_CD = 1           

    ->    and a.offer_spec_id = c.offer_spec_id           

    ->    AND a.STATUS_CD IN ('10', '11', '12', '13')           

    ->    and a.party_id =250007239424  ;

ERROR 1064 (42000): Youhave an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near 'expain  select DISTINCT a.offer_id      AS OFFERID,           

                ' at line 1

mysql> explain  select DISTINCT a.offer_id      AS OFFERID,           

    ->                  a.OFFER_SPEC_ID ASOFFERSPECID,           

    ->                  c.NAME,           

    ->                  c.offer_type_cd ASOFFERTYPECD,           

    ->                  a.party_id      AS PARTYID           

    ->   from crmdb.offer a, crmdb.offer_spec c           

    ->  where c.OFFER_TYPE_CD = 1           

    ->    AND c.AGREEMENT_TYPE_CD = 1           

    ->    and a.offer_spec_id = c.offer_spec_id           

    ->    AND a.STATUS_CD IN ('10', '11', '12', '13')           

    ->    and a.party_id =250007239424  ;

+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+------------------------------+

| id | select_type | table | type   | possible_keys      | key                | key_len | ref                   | rows | Extra                        |

+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+------------------------------+

|  1 | SIMPLE      | a    | ref    | IDX_OFFER_PARTY_ID |IDX_OFFER_PARTY_ID | 8       | const                 |   17 | Using where; Using temporary |

|  1 | SIMPLE      | c    | eq_ref | PRIMARY            |PRIMARY            | 8       | crmdb.a.offer_spec_id |    1 | Using where                  |

+----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+------------------------------+

解读：

id：执行顺序由上至下，如果是子查询语句，id会按照查询执行顺序递增。

select_type:

SIMPLE	查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
SUBQUERY	在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
DERIVED	在FROM列表中包含的子查询被标记为：DERIVED
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在  FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT被标记为：UNION RESULT

 

TYPE:

ALL	Full table scan
INDEX	Full Index Scan
RANGE	Index rang Scan
REF	非唯一性索引扫描，返回匹配某个单独值的所有行
EQ_REF	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
CONST，SYSTEM	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
NULL	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

 

KEY:

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

KEY_LEN:

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

REF:

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值.。

EXTRA:

       重要的额外信息 

注：EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。 

1.1.2 Query Profiler等位性能瓶颈 

mysql> SELECT@@profiling;

+-------------+

| @@profiling |

+-------------+

|           0 |

+-------------+

 

 

mysql>SET profiling =1;

 

mysql> show profiles                                    --查询当前session中执行过的sql

    -> ;

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID |Duration   | Query                                                                                                                                                                                                                                                                                                       |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|        1 | 0.00230975 | expain  select DISTINCT a.offer_id      AS OFFERID,           

                 a.OFFER_SPEC_ID AS OFFERSPECID,           

                 c.NAME,           

                 c.offer_type_cd ASOFFERTYPECD,           

                 a.party_id      AS PARTYID           

   from offer a, offe |

|        2 | 0.02249075 | explain  select DISTINCT a.offer_id      AS OFFERID,           

                 a.OFFER_SPEC_ID ASOFFERSPECID,           

                 c.NAME,           

                 c.offer_type_cd ASOFFERTYPECD,           

                 a.party_id      AS PARTYID           

   from crmdb.offer  |

|        3 | 0.00044075 | show variables likewarning_count                                                                                                                                                                                                                                                                           |

|        4 | 0.00018025 | show variable like'warning_count'                                                                                                                                                                                                                                                                          |

|        5 | 0.00111325 | show variables like'warning_count'                                                                                                                                                                                                                                                                          |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5 rows in set, 1 warning(0.00 sec)

 

mysql> show profilefor query 2;        --查询第2个sql的profile

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.001909 |

| checking permissions |0.000009 |

| checking permissions |0.000008 |

| Opening tables       | 0.000098 |

| init                 | 0.000078 |

| System lock          | 0.000021 |

| optimizing           | 0.000031 |

| statistics           | 0.019901 |

| preparing            | 0.000168 |

| Creating tmptable   | 0.000071 |

| explaining           | 0.000044 |

| query end            | 0.000013 |

| removing tmptable   | 0.000005 |

| query end            | 0.000028 |

| closing tables       | 0.000020 |

| freeing items        | 0.000050 |

| cleaning up          | 0.000040 |

+----------------------+----------+

 

17 rows in set, 1warning (0.00 sec)

 

 

 

mysql>  show profile block io,cpu for query 2;     --查询详细的cpu、io信息

+----------------------+----------+----------+------------+--------------+---------------+

| Status               | Duration | CPU_user |CPU_system | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+--------------+---------------+

| starting             | 0.001909 |     NULL |       NULL |         NULL |          NULL |

| checking permissions |0.000009 |     NULL |       NULL |         NULL |          NULL |

| checking permissions |0.000008 |     NULL |       NULL |         NULL |          NULL |

| Opening tables       | 0.000098 |     NULL |       NULL |         NULL |          NULL |

| init                 | 0.000078 |     NULL |       NULL |         NULL |          NULL |

| System lock          | 0.000021 |     NULL |       NULL |         NULL |          NULL |

| optimizing           | 0.000031 |     NULL |       NULL |         NULL |          NULL |

| statistics           | 0.019901 |     NULL |       NULL |         NULL |          NULL |

| preparing            | 0.000168 |     NULL |       NULL |         NULL |          NULL |

| Creating tmptable   | 0.000071 |     NULL |       NULL |         NULL |          NULL |

| explaining           | 0.000044 |     NULL |       NULL |         NULL |          NULL |

| query end            | 0.000013 |     NULL |       NULL |         NULL |          NULL |

| removing tmptable   | 0.000005 |     NULL |       NULL |         NULL |          NULL |

| query end            | 0.000028 |     NULL |       NULL |         NULL |          NULL |

| closing tables       | 0.000020 |     NULL |       NULL |         NULL |          NULL |

| freeing items        | 0.000050 |     NULL |       NULL |         NULL |          NULL |

| cleaning up          | 0.000040 |     NULL |       NULL |         NULL |          NULL |

+----------------------+----------+----------+------------+--------------+---------------+