mysql执行计划看是否最优

mysql执行计划看是否最优

介绍

  本篇主要通过汇总网上的大牛的知识,简单介绍一下如何使用mysql的执行计划,并根据执行计划判断如何优化和是否索引最优。

  执行计划可显示估计查询语句执行计划,从中可以分析查询的执行情况是否最优,有助于对不使用索引的语句进行优化。EXPLAIN对每个查询返回一行信息,列出了有序的表格,MySQL处理语句的时候读取他们。MySQL解决所有的连接使用嵌套连接方法。这意味读取第一张一行,然后匹配第二张表的所有行,第三张表甚至更多表。当所有的表在处理时,MySQL会输出已经查询出来的列,并且回溯到表继续查找直到所有的行被找到,从该表读取下一行,直到程序继续处理下一张表。

 
使用关键词 EXTENDED ,EXPLAIN 会处理通过 SHOW WARNINGS 看到的一些额外信息。EXPLAIN EXTENDED 会显示这些滤出的列。

语法:

EXPLAIN <select statement>;  

输出表格字段如下:

    mysql> explain select * from mysql.user where user='root';  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
    |  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  

 

 

Column

JSONName

Meaning

id

select_id

查询标识。id越大优先执行;id相同自上而下执行;

select_type

None

查询的类型

table

table_name

查询的表

partitions

partitions

Thematching partitions

type

access_type

连接类型

possible_keys

possible_keys

可能选择的索引

key

key

实际使用的索引

key_len

key_length

使用的索引长度

ref

ref

哪一列或常数在查询中与索引键列一起使用

rows

rows

估计查询的行数

filtered

filtered

被条件过滤掉的行数百分比

Extra

None

解决查询的一些额外信息

 

 

以下主要举例说明3个字段:select_type 、type、Extra 

 

select_type

alue

JSONName

Meaning

SIMPLE

None

简单查询 (不使用UNION或子查询)

PRIMARY

None

外层查询,主查询

UNION

None

UNION第二个语句或后面的语句

DEPENDENTUNION

dependent (true)

UNION中第二个语句或后面的语句,独立于外部查询

UNIONRESULT

union_result

UNION的结果

SUBQUERY

None

子查询中第一个SELECT

DEPENDENTSUBQUERY

dependent (true)

子查询中第一个SELECT,独立于外部查询

DERIVED

None

子查询在 FROM子句中

MATERIALIZED

materialized_from_subquery

物化子查询(不清楚是什么样的查询语句?)

UNCACHEABLESUBQUERY

cacheable (false)

结果集不能被缓存的子查询,必须重新评估外层查询的每一行

UNCACHEABLEUNION

cacheable (false)

UNION中第二个语句或后面的语句属于不可缓存的子查询

 

创建测试表:

create table tabname (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  
  
create table tabname2 (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=myisam;  
  
  
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);  
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4); 

 

 

#SIMPLE

create table tabname (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  
  
create table tabname2 (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=myisam;  
  
  
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);  
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4); 

 

 

#PRIMARY / DERIVED

    mysql> explain select * from (select * from tabname) as a;  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
    | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
    |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    |  2 | DERIVED     | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+  

 

 

#PRIMARY / UNION / UNION RESULT

    mysql> explain select * from tabname union select * from tabname;  
    mysql> explain select * from tabname union all select * from tabname;  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+  
    | id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+  
    |  1 | PRIMARY      | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    |  2 | UNION        | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    | NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |       |  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+  

 

    mysql> explain select * from tabname where id=(select max(id) from tabname);  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+  
    | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+  
    |  1 | PRIMARY     | tabname | const | PRIMARY       | PRIMARY | 4       | const |    1 |                              |  
    |  2 | SUBQUERY    | NULL    | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+  

 

#PRIMARY / SUBQUERY

 

 

#PRIMARY / DEPENDENT SUBQUERY

[plain]
view plain
copy

    mysql> explain select * from tabname a where exists(select 1 from tabname b where a.id=b.id);  
    mysql> explain select *,(select name from tabname b where a.id=b.id) from tabname a;  
    mysql> explain select * from tabname where id not in(select id from tabname);  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname | ALL             | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  

 

 

#PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT

    mysql> explain select * from tabname where id in (select id from tabname union select id from tabname);  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname    | ALL    | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname    | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    |  3 | DEPENDENT UNION    | tabname    | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    | NULL| UNION RESULT      | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL | NULL |             |  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+  

 

type

type

Meaning

system

表仅一行数据 (=system table).这是const连接类型的特例。

const

表最多只有一个匹配行,在查询开始时被读取。因为只有一个值,优化器将该列值视为常量。当在primarykey或者unique索引作为常量比较时被使用。

eq_ref(engine=myisam)

来自前面表的结果集中读取一行,这是除systemconst外最好的连接类型。当在使用PRIMARYKEY或者UNIQUENOT NULL的索引时会被使用。

ref

对于前面表的结果集匹配查询的所有行,当连接使用索引key时,或者索引不是PRIMARYKEYUNIQUE则使用该类型。如果使用索引匹配少量行时,是不错的连接类型。

ref_or_null

连接类型类似ref,只是搜索的行中包含NULLMySQL做了额外的查找。

fulltext

使用全文索引时出现。

index_merge

使用了索引合并优化。(未成功)

unique_subquery

该类型将ref替换成以下子查询的格式:

valueIN (SELECTprimary_key FROMsingle_table WHERE some_expr)

index_subquery

unique_subquery类似,但是将主键改为非唯一索引:

valueIN (SELECTkey_columnFROMsingle_table WHERE some_expr)

range

使用索引检索给定范围内的行。

index

该连接类型与ALL相同,除了扫描索引树。如果查询的字段都在索引列中,则使用index类型,否则为ALL类型。

ALL

对于前面表的结果集中,进行了全表扫描。最差的一种类型,应考虑查询优化了!

 

查询类型性能由优到差:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 

#system

    mysql> explain select id from(select id from tabname where id=1) as a;  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    | id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |             |  
    |  2 | DERIVED     | tabname    | const  | PRIMARY       | PRIMARY | 4       |      |    1 | Using index |  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+  

 

#const

    mysql> explain select * from tabname as a,tabname as b where a.id=b.id and a.id=1;  
    mysql> explain select * from tabname where id=1;  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+  
    | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+  
    |  1 | SIMPLE      | tabname | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+  

 


#eq_ref(engine=myisam)

 

    mysql> explain select * from tabname2 as a,tabname2 as b where a.id=b.id;  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+  
    | id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+  
    |  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    3 |       |  
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 |       |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+  

 

 

 

 

#ref

    mysql> explain select * from tabname as a,tabname as b where a.tid=b.tid and a.tid=2;  
    mysql> explain select * from tabname where tid=2;  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra       |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    |  1 | SIMPLE      | tabname | ref  | tid           | tid  | 5       | const |    1 | Using where |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  

 

#ref_or_null

 

    mysql> explain select id,tid from tabname where tid=2 or tid is null;  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    | id | select_type | table   | type        | possible_keys | key  | key_len | ref   | rows | Extra                    |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    |  1 | SIMPLE      | tabname | ref_or_null | tid           | tid  | 5       | const |    2 | Using where; Using index |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  

 

 

 

 

#fulltext

    mysql> explain select id,tid from tabname where tid=2 or tid is null;  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    | id | select_type | table   | type        | possible_keys | key  | key_len | ref   | rows | Extra                    |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    |  1 | SIMPLE      | tabname | ref_or_null | tid           | tid  | 5       | const |    2 | Using where; Using index |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  

 

#index_merge(未成功)

mysql> explain select * from tabname where tid>1 or indate<now();  
mysql> explain select * from tabname where (tid>1 or indate>now()) AND name<'kk'; 

 


#unique_subquery

    mysql> explain select * from tabname where tid in(select id from tabname);  
    mysql> explain select * from tabname where id in(select id from tabname);  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname | ALL             | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  

 

#index_subquery

    mysql> explain select * from tabname where tid in(select tid from tabname);  
    mysql> explain select * from tabname where id in(select tid from tabname);  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+  
    | id | select_type        | table   | type           | possible_keys | key  | key_len | ref  | rows | Extra                    |  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+  
    |  1 | PRIMARY            | tabname | ALL            | NULL          | NULL | NULL    | NULL |    3 | Using where              |  
    |  2 | DEPENDENT SUBQUERY | tabname | index_subquery | tid           | tid  | 5       | func |    1 | Using index; Using where |  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+  

 

 

 

 

#range

 

    mysql> explain select * from tabname where tid between 1 and 2;  
    mysql> explain select * from tabname where id>1;  
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+  
    | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+  
    |  1 | SIMPLE      | tabname | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |  
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+  

 

 

 

#index

mysql> explain select id,tid from tabname;  
mysql> explain select tid from tabname;  
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+  
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |  
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | tabname | index | NULL          | tid  | 5       | NULL |    3 | Using index |  
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

 

#ALL

    mysql> explain select * from tabname where tid<>2;  
    mysql> explain select * from tabname;  
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |  
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+  
    |  1 | SIMPLE      | tabname | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+  

 

Extra

该列输出关MySQL如何解决查询的额外信息。(下面列出部分常见的)

Extra

Meaning

usingwhere

使用过滤条件

usingindex

从索引树中查找所有列

usingtemporary

使用临时表存储结果集,在使用groupbyorderby发生

selecttables optimized away

没有groupby情况下使用min(),max(),或者count(*)

usingfilesort

有排序

notexists

leftjoin中匹配一行之后将不再继续查询查询

distinct

查找到第一个匹配的行之后,MySQL则会停止对当前行的搜索

impossiblewhere

where子句总数失败的查询

impossiblehaving

having子句总数失败的查询

usingjoin buffer

使用连接缓存

Usingindex for group-by

Usingindex类似,在使用group-by时可从索引中找到字段


#using where

    mysql> explain select * from tabname where id>2;  
    mysql> explain select * from tabname where tid=2;  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra       |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    |  1 | SIMPLE      | tabname | ref  | tid           | tid  | 5       | const |    1 | Using where |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  


#using index

 

    mysql> explain select tid from tabname;  
    +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+  
    | id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |  
    +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+  
    |  1 | SIMPLE      | tabname | index | NULL          | tid  | 5       | NULL |    3 | Using index |  
    +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+  

 

 

 

 

#using temporary

 
    mysql> explain select distinct name from tabname;  
    +----+-------------+---------+------+---------------+------+---------+------+------+-----------------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra           |  
    +----+-------------+---------+------+---------------+------+---------+------+------+-----------------+  
    |  1 | SIMPLE      | tabname | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary |  
    +----+-------------+---------+------+---------------+------+---------+------+------+-----------------+  

 


#select tables optimized away

 

    mysql> explain select max(tid) from tabname;  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+  
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+  
    |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+  

 

 

 

#using filesort

 

    mysql> explain select id,name from tabname group by id,name;  
    mysql> explain select * from tabname order by name;  
    +----+-------------+---------+------+---------------+------+---------+------+------+----------------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |  
    +----+-------------+---------+------+---------------+------+---------+------+------+----------------+  
    |  1 | SIMPLE      | tabname | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |  
    +----+-------------+---------+------+---------------+------+---------+------+------+----------------+  

 

 

 

 

#not exists

    mysql> explain select * from tabname a left join tabname b on a.id=b.id where b.id is null;  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+  
    | id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra                   |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+  
    |  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL      |    3 |                         |  
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 | Using where; Not exists |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+  

 

#distinct

mysql> explain select distinct a.id from tabname a left join tabname b on a.id=b.id;  
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+  
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra                        |  
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+  
|  1 | SIMPLE      | a     | index  | NULL          | tid     | 5       | NULL      |    3 | Using index; Using temporary |  
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 | Using index; Distinct        |  
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+ 

 

  1.  

#impossible where

    mysql> explain select * from tabname where 1=2;  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------+  
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------+  
    |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |  
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------+  

 


#impossible having

mysql> explain select id,count(*) from tabname group by id having 1=2;  
mysql> explain select count(*) from tabname having 1=2;  
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+  
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |  
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+  
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible HAVING |  
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+

 

 

现在使用 EXTENDED 情况:

 

语法:

  1. EXPLAIN EXTENDED <select statement>;  


不使用 extended 和使用extended 的分析情况:

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

 


可以看到,使用 extended 时,输出的最下面多了 1 条警告。 此时可以用 show warnings 来查看:

    mysql> show warnings \G;  
    *************************** 1. row ***************************  
      Level: Note  
       Code: 1003  
    Message: select `test`.`tabname`.`tid` AS `tid` from `test`.`tabname`  
    1 row in set (0.00 sec)  
      
    ERROR:  
    No query specified  

 


show warnings 显示了优化器中是怎么规范表和字段名的,在通过重写和优化规则之后的 select 语句是什么样子。

 

 

更多参考:

EXPLAIN Output Format

EXPLAIN EXTENDED Output Format

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119681.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • NOIP2008_2012年12月16号农历是

    NOIP2008_2012年12月16号农历是NOIP2012DAY1T1Vigenère密码纯模拟#include<bits/stdc++.h>chara[105],b[1005],ans[1005];intlen1,len2,h=1,p;intc[105];usingnamespacestd;intmain(){// freopen(“vigenere.in”,”r”,stdin); …

  • Dataset之CIFAR-10:CIFAR-10数据集的简介、下载、使用方法之详细攻略

    Dataset之CIFAR-10:CIFAR-10数据集的简介、下载、使用方法之详细攻略Dataset之CIFAR-10:CIFAR-10数据集的简介、下载、使用方法之详细攻略目录CIFAR-10的简介1、与MNIST数据集中目比,CIFAR-10真高以下不同点2、TensorFlow官方示例的CIFAR-10代码文件3、CIFAR-10数据集的数据文件名及用途4、基于CIFAR-10数据集最新算法预测准确率对比CIFAR-10的下载1、下载CIFAR-10数据集的全部数据CIFAR-10使用方法1、使用TF读取CIFAR-10数据官网链接:TheCIFAR-10datas

    2022年10月17日
  • Linux 路由表详解及 route 命令详解

    Linux 路由表详解及 route 命令详解Linux内核的路由表通过route命令查看Linux内核的路由表:[root@VM_139_74_centos~]#routeKernelIProutingtableDestinationGatewayGenmaskFlagsMetricRefUseIfacedefaultgatewa…

  • CSS的Display属性可能的值

    none 此元素不会被显示。 block 此元素将显示为块级元素,此元素前后会带有换行符。 inline 默认。此元素会被显示为内联元素,元素前后没有

    2021年12月26日
  • Windows日志分析工具_Windows7激活工具

    Windows日志分析工具_Windows7激活工具Windows主要有以下三类日志记录系统事件:应用程序日志、系统日志和安全日志系统日志:%SystemRoot%\System32\Winevt\Logs\System.evtx记录操作系统组件产生的事件,主要包括驱动程序、系统组件和应用软件的崩溃以及数据丢失错误等。应用程序日志:%SystemRoot%\System32\Winevt\Logs\Application.evtx包含由应用程序或系统程序记录的事件,主要记录程序运行程序方面的事件。安全日志:%SystemRoot%\Syste

  • EasyMock日记1[通俗易懂]

    privatePayControllerpayController;privateHttpServletRequestrequest;privateUseruser;privateHttpSessionsession;@Beforepublicvoidbefore(){payController=newPayController();user=ne

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号