首页 > > 网络编程 > Mysql >

mysql执行计划看是否最优

2018-06-18 01:04:08来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

介绍

　　本篇主要通过汇总网上的大牛的知识，简单介绍一下如何使用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

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：MySQL存储过程（批量生成论坛中发帖、回帖、主题等数据）

下一篇：mysql用户授权、数据库权限管理、sql语法详解

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签