单条sql分析诊断思路

最新推荐文章于 2022-08-12 14:36:59 发布

DBAdream

最新推荐文章于 2022-08-12 14:36:59 发布

阅读量563

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/DBAdream/article/details/38849547

mysql 专栏收录该内容

32 篇文章

订阅专栏

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 ;

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

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

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

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

解读：

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