mysql执行计划中性能最差的是_mysql执行计划看是否最优

最新推荐文章于 2021-11-10 16:30:12 发布

weixin_39636057

最新推荐文章于 2021-11-10 16:30:12 发布

阅读量94

点赞数

文章标签： mysql执行计划中性能最差的是

本文链接：https://blog.csdn.net/weixin_39636057/article/details/113218794

版权

介绍

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

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

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

语法：

EXPLAIN ;

输出表格字段如下：

mysql> explain select * from mysql.user where user='root';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

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

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

ColumnJSONNameMeaning

select_id

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

select_type

None

查询的类型

table

table_name

查询的表

partitions

Thematching partitions

type

access_type

连接类型

possible_keys

可能选择的索引

key

实际使用的索引

key_len

key_length

使用的索引长度

ref

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

rows

估计查询的行数

filtered

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

Extra

None

解决查询的一些额外信息

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

【select_type】

alueJSONNameMeaning

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 (

idint auto_increment not nullprimary key,

name varchar(10) null,

indate datetimenull,

tidint null,

key(tid),

key(indate)

)engine=innodb;

create table tabname2 (

idint auto_increment not nullprimary key,

name varchar(10) null,

indate datetimenull,

tidint 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 (

idint auto_increment not nullprimary key,

name varchar(10) null,

indate datetimenull,

tidint null,

key(tid),

key(indate)

)engine=innodb;

create table tabname2 (

idint auto_increment not nullprimary key,

name varchar(10) null,

indate datetimenull,

tidint 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) asa;+----+-------------+------------+------+---------------+------+---------+------+------+-------+

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

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

#PRIMARY / UNION / UNION RESULT

mysql> explain select * from tabname union select * fromtabname;

mysql> explain select * from tabname union all select * fromtabname;+----+--------------+------------+------+---------------+------+---------+------+------+-------+

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

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

mysql> explain select * from tabname where id=(select max(id) fromtabname);+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+

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

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

#PRIMARY / SUBQUERY

#PRIMARY / DEPENDENT SUBQUERY

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) fromtabname a;

mysql> explain select * from tabname where id not in(select id fromtabname);+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+

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

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

#PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT

mysql> explain select * from tabname where id in (select id from tabname union select id fromtabname);+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+

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

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

【type】

typeMeaning

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) asa;+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+

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

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

#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;+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

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

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

#eq_ref(engine=myisam)

mysql> explain select * from tabname2 as a,tabname2 as b where a.id=b.id;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+

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

| 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;+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+

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

| 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;+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+

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

| 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;+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+

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

| 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

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

#unique_subquery

mysql> explain select * from tabname where tid in(select id fromtabname);

mysql> explain select * from tabname where id in(select id fromtabname);+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+

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

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

#index_subquery

mysql> explain select * from tabname where tid in(select tid fromtabname);

mysql> explain select * from tabname where id in(select tid fromtabname);+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+

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

| 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;+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

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

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

#index

mysql> explain select id,tid fromtabname;

mysql> explain select tid fromtabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

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

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

#ALL

mysql> explain select * from tabname where tid<>2;

mysql> explain select * fromtabname;+----+-------------+---------+------+---------------+------+---------+------+------+-------+

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

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

【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;+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+

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

| 1 | SIMPLE | tabname | ref | tid | tid | 5 | const | 1 | Using where |

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

#using index

mysql> explain select tid fromtabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

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

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

#using temporary

mysql> explain select distinct name fromtabname;+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+

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

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

#select tables optimized away

mysql> explain select max(tid) fromtabname;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

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

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

#using filesort

mysql> explain select id,name fromtabname group by id,name;

mysql> explain select * fromtabname order by name;+----+-------------+---------+------+---------------+------+---------+------+------+----------------+

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

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

#not exists

mysql> explain select * from tabname a left join tabname b on a.id=b.id where b.id is null;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+

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

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | |

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

#distinct

mysql> explain select distinct a.id from tabname a left join tabname b on a.id=b.id;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+

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

| 1 | SIMPLE | a | index | NULL | tid | 5 | NULL | 3 | Using index; Using temporary |

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

#impossible where

mysql> explain select * from tabname where 1=2;+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

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

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

#impossible having

mysql> explain select id,count(*) from tabname group by id having 1=2;

mysql> explain select count(*) from tabname having 1=2;+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+

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

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

现在使用 EXTENDED 情况：

语法：

EXPLAIN EXTENDED ;

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

mysql> explain select tid fromtabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

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

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

1 row in set (0.00sec)

mysql> explain extended select tid fromtabname;+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+

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

| 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:1003Message:select `test`.`tabname`.`tid` AS `tid` from`test`.`tabname`1 row in set (0.00sec)

ERROR:

No query specified

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

更多参考：