mysql explain 用法详解

昝辉Zac Zac的SEO博客，坚持12年，优化成为生活。

mysql explain 可以用来分析你要查询的sql语句的各项参数值,下面我们有详细的实例有需要的可以看看,代码如下:

EXPLAIN table == DESC table == SHOW COLUMNS FORM table

EXPLAIN [EXTENDED|PARTITIONS] SELECT... --显示该语句将使用哪一个索引以及何时进行多表查询与使用到的表顺序,代码如下:

  1. mysql>EXPLAINSELECT*FROMBOOKSWHEREBOOK_ID=1;
  3. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  5. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  7. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  9. |1|SIMPLE|BOOKS|const|PRIMARY|PRIMARY|4|const|1||
  11. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  13. 1rowinset(0.00sec)

POSSIBLE_KEYS字段列举出了用于查找数据的索引,而KEY字段指示我们实际上用到了XX索引,如果POSSIBLE_KEYS字段的值显示NULL,那么说明没有用到索引.

SELECT_TYPE

SIMPLE 指示简单SELECT语句，没有子查询或者UNION

PRIMARY 当使用子查询时，这是主要的SELECT语句

UNION 当使用子查询时，这是主要的SELECT语句

DEPENDENT UNION 当使用UNION时，这并不是第一个SELECT语句，取决于主查询

UNION RESULT UINON查询

SUBQUERY 子查询中的第一个SELECT语句

DEPENDENT SUBQUERY 子查询中的第一个SELECT语句,取决于主查询

DERIVED 来自于子查询的表

UNCACHEABLE SUBQUERY 指示子查询中的结果不能缓存,因此必须对主查询中的每一行重新评价.

UNCACHEABLE UNION 指示子查询的UNION中，结果不能缓存，因此必须对主查询中的每一行重新评价.

这是在官网上的说明,代码如下:

  1. EXPLAINSyntax
  2. EXPLAIN[EXTENDED]SELECTselect_options
  3. Or:
  4. EXPLAINtbl_name

The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE:

When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.

例如如下代码:

  1. mysql>explainselect*from(select*from(select*fromt3whereid=3952602)a)b;
  2. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  5. |1|PRIMARY|<derived2>|system|NULL|NULL|NULL|NULL|1||
  6. |2|DERIVED|<derived3>|system|NULL|NULL|NULL|NULL|1||
  7. |3|DERIVED|t3|const|PRIMARY,idx_t3_id|PRIMARY|4||1||
  8. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

很显然这条SQL是从里向外的执行,就是从id=3 向上执行.

2.select_type

就是select类型,可以有以下几种

(1) SIMPLE,简单SELECT(不使用UNION或子查询等),代码如下:

  1. mysql>explainselect*fromt3whereid=3952602;
  2. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
  5. |1|SIMPLE|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
  6. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(2).PRIMARY,我的理解是最外层的select.代码如下:

  1. mysql>explainselect*from(select*fromt3whereid=3952602)a;
  2. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  5. |1|PRIMARY|<derived2>|system|NULL|NULL|NULL|NULL|1||
  6. |2|DERIVED|t3|const|PRIMARY,idx_t3_id|PRIMARY|4||1||
  7. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(3).UNION,UNION中的第二个或后面的SELECT语句,代码如下:

  1. mysql>explainselect*fromt3whereid=3952602unionallselect*fromt3;
  2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
  5. |1|PRIMARY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
  6. |2|UNION|t3|ALL|NULL|NULL|NULL|NULL|1000||
  7. |NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL||
  8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4).DEPENDENT UNION,UNION中的第二个或后面的SELECT语句,取决于外面的查询,代码如下:

  1. mysql>explainselect*fromt3whereidin(selectidfromt3whereid=3952602unionallselectidfromt3);
  2. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
  5. |1|PRIMARY|t3|ALL|NULL|NULL|NULL|NULL|1000|Usingwhere|
  6. |2|DEPENDENTSUBQUERY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1|Usingindex|
  7. |3|DEPENDENTUNION|t3|eq_ref|PRIMARY,idx_t3_id|PRIMARY|4|func|1|Usingwhere;Usingindex|
  8. |NULL|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL||
  9. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+

(5).UNION RESULT,UNION的结果,代码如下:

  1. mysql>explainselect*fromt3whereid=3952602unionallselect*fromt3;
  2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
  5. |1|PRIMARY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
  6. |2|UNION|t3|ALL|NULL|NULL|NULL|NULL|1000||
  7. |NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL||
  8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(6).SUBQUERY,子查询中的第一个SELECT,代码如下:

  1. mysql>explainselect*fromt3whereid=(selectidfromt3whereid=3952602);
  2. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
  5. |1|PRIMARY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
  6. |2|SUBQUERY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4||1|Usingindex|--phpfensi.com
  7. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+

(7).DEPENDENT SUBQUERY,子查询中的第一个SELECT,取决于外面的查询,代码如下:

  1. mysql>explainselectidfromt3whereidin(selectidfromt3whereid=3952602);
  2. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
  5. |1|PRIMARY|t3|index|NULL|PRIMARY|4|NULL|1000|Usingwhere;Usingindex|
  6. |2|DEPENDENTSUBQUERY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1|Usingindex|
  7. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+

(8).DERIVED,派生表的SELECT(FROM子句的子查询),代码如下:

  1. mysql>explainselect*from(select*fromt3whereid=3952602)a;
  2. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
  4. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  5. |1|PRIMARY|<derived2>|system|NULL|NULL|NULL|NULL|1||
  6. |2|DERIVED|t3|const|PRIMARY,idx_t3_id|PRIMARY|4||1||
  7. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

上一页 1 2 下一页

相关广告
  • mysql explain 用法详解 mysql explain 用法详解 mysql explain 用法详解
相关阅读

mysql explain 用法详解

2019/10/10 17:33:19 | 谷歌SEO算法 | 百度细雨算法