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 |
+----------------------+----------+----------+------------+--------------+---------------+