1. 全栈程序员必看首页

mysql执行计划看是否最优

全栈程序员-用户IM • • 未分类

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)

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

ref

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

ref_or_null

连接类型类似ref，只是搜索的行中包含NULL值MySQL做了额外的查找。

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

使用临时表存储结果集，在使用groupby和orderby发生

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)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • java最长递增子序列_JAVA最长递增子序列「建议收藏」

    java最长递增子序列_JAVA最长递增子序列「建议收藏」

    java最长递增子序列_JAVA最长递增子序列「建议收藏」问题描述LIS(LongestIncreasingSubsequence，最长递增子序列)：给出一个序列a1,a2,a3,a4,a5,a6,a7…an，求它的一个子序列(设为s1,s2,…sn)，使得这个子序列满足这样的性质，s1最长递增子序列实例分析17359481最长递增子序列算法设计设b[i]是在a[i]为单调递增子序列最后一个元素时，所得最长单调递增…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月11日
  • idea2021.1的激活码 3月最新注册码 idea

    idea2021.1的激活码 3月最新注册码

    idea2021.1的激活码 3月最新注册码，https://javaforall.cn/100143.html。详细ieda激活码不妨到全栈程序员必看教程网一起来了解一下吧！

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年3月15日
  • 机器学习-常用回归算法归纳(全网之最)

    机器学习-常用回归算法归纳(全网之最)

    机器学习-常用回归算法归纳(全网之最)文章目录前言一元线性回归多元线性回归局部加权线性回归多项式回归Lasso回归&Ridge回归Lasso回归Ridge回归岭回归和lasso回归的区别L1正则&L2正则弹性网络回归贝叶斯岭回归Huber回归KNNSVMSVM最大间隔支持向量&支持向量平面寻找最大间隔SVRCART树随机森林GBDTboosting思想AdaBoost思想提升树&梯度提升GBDT面试题整理XGBOOST面试题整理LightGBMXGBoost的缺点LightGBM的优化基于Hist

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年8月21日
  • clone一个react项目怎么运行[通俗易懂]

    clone一个react项目怎么运行[通俗易懂]

    首先当你从git上面clone一个项目的时候怎么让项目跑起来,首先看项目目录结构,找到README.md上面有项目运行的步骤,如果没有可以看package.json文件,找到scripts上面有dev所以跑起来项目就使用npmrundev有start就使用npmstart但是要先安装项目依赖使用npminstall依赖下载完成就可以使用npmrundev/npm…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月15日
  • 软件概要设计与详细设计

    软件概要设计与详细设计

    （一）概要设计的任务与步骤1、总体设计的必要性：可以站在全局角度上，花较少成本，从抽象的层次上分析对比多种可能性的系统实现方案和软件结构，从中选出最佳方案和最合理的软件结构，从而用较低成本开发出较高质量的软件系统。2、总体设计的两个阶段：（1）系统设计阶段：确定系统的具体实现方案（2）结构设计阶段：确定软件结构。3、总体设计的9个步骤：（1）设想供选择的方案（2）选取…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月6日
  • 关于this指针

    关于this指针

    关于this指针一个类的对象中实际只包含了该对象的数据成员信息，当我们创建了多个类的对象时，使对象1调用该类的成员函数，为什么可以改变对象1中的信息，而不去设置其他对象的信息？成员函数在类中只有一份，所有该类的对象共同使用，编译器是如何识别并处理的呢？编译器识别一个类分为三步：1.识别类的类名2.识别类的成员变量3.识别类的成员函数并对成员函数进行修改修改方式：成员函数有一个隐藏…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月16日

发表回复

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

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