MySQL的执行计划详解(Explain)

1、MySQL执行计划的定义
在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL语句,从而知道 MySQL 是如何处理 SQL 语句的。

2、MySQL整个查询的过程
• 客户端向 MySQL 服务器发送一条查询请求
• 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
• MySQL 根据执行计划,调用存储引擎的 API 来执行查询
• 将结果返回给客户端,同时缓存查询结果
注意:只有在8.0之前才有查询缓存,8.0之后查询缓存被去掉了

3、如何启动执行计划
explain select 投影列 FROM 表名 WHERE 条件 ;

4、explain中的列

4.1、id
查询执行顺序:
id 值相同时表示从上向下执行
id 值相同被视为一组
如果是子查询,id 值会递增,id 值越高,优先级越高
id为NULL最后执行。

4.2、select_type
● simple:表示查询中不包含子查询或者 union
EXPLAIN select * from actor where id=1;

● primary:当查询中包含任何复杂的子部分,最外层的查询被标记成 primary
● derived:在 from 的列表中包含的子查询被标记成 derived
● subquery:在 select 或 where 列表中包含了子查询,则子查询被标记成 subquery
用个例子来了解primary、subquery和derived
set session optimizer_switch=‘derived_merge=off’;#关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

set session optimizer_switch=‘derived_merge=on’; #还原默认配置
● union:两个 select 查询时前一个标记为 PRIMARY,后一个标记为 UNION。union 出现在 from 从句子查询中,外层 select 标记为 PIRMARY,union 中第一个查询为 DERIVED,第二个子查询标记为 UNION
explain select 1 union all select 1;

● unionresult:从 union 表获取结果的 select 被标记成 union result 。

4.3、table
显示这一行的数据是关于哪张表的。
当 from 子句中有子查询时,table列是 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。
当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的 select 行id。
1
2
3
4.4、type
这是重要的列,显示连接使用了何种类型。
SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。
说明:
1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
2) ref 指的是使用普通的索引(normal index)。
3) range 对索引进行范围检索。
反例:explain 表的结果,type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range
还低,与全表扫描是小巫见大巫。

从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL。
一般来说,得保证查询达到range级别,最好达到ref
● NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
explain select min(id) from film;

● system:表中只有一行数据。属于 const 的特例。如果物理表中就一行数据为 ALL
● const :查询结果最多有一个匹配行。因为只有一行,所以可以被视为常量。const 查询速度非常快,因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const
explain select * from (select * from film where id = 1) tmp;

● eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
explain select * from film_actor left join film on film_actor.film_id = film.id

● ref:非唯一性索引扫描,返回匹配某个单独值得所有行。索引要和某个值相比较,可能会找到多个符合条件的行。

1.简单 select 查询,name是普通索引(非唯一索引)
explain select * from film where name = ‘film1’;

2.关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分
explain select film_id from film left join film_actor on film.id = film_actor.film_id;

● range:把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、>、>=、in 等。主要应用在具有索引的列中
explain select * from actor where id > 1;

● index:full index scan全索引扫描,index与all的区别为:index类型只遍历索引树,这通常比all快,因为索引文件通常比数据文件小。也就是说虽然index和all都是读全表,但index是从索引中读的,而all是从硬盘中读的。
explain select * from film;

● ALL:即全表扫描,扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
explain select * from actor;

4.5、possible_keys (可能用到的索引)
查询条件字段涉及到的索引,可能没有使用。
explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果
4.6、key (实际使用的索引)
实际使用的索引。如果为 NULL,则没有使用索引。
如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 forceindex、ignore index。

4.7、key_len (索引的长度)
表示索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。key_len 是根据表定义计算而得的,不是通过表内检索出的。
例子:
film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成,并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执行索引查找。
explain select * from film_actor where film_id = 2;

4.8、ref
显示索引的哪一列被使用了,如果可能的话,是一个常量 const。

4.9、rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。注意这个不是结果集里的行数, 数值越低越好。

4.10、fitered
显示了通过条件过滤出的行数的百分比估计值。

4.11、Extra (额外的)
MYSQL 如何解析查询的额外信息。

Distinct: MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。

Not exists:MySQL 能够对查询进行 LEFT JOIN 优化,发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。

range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

Using filesort: 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为“文件排序”这种情况下一般也是要考虑使用索引来优化的。
4.1. actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
explain select * from actor order by name;

4.2. film.name建立了idx_name索引,此时查询时extra是using index
explain select * from film order by name;

Using index: 表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错!
如果同时出现了using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
explain select film_id from film_actor where film_id = 1;

Using temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。使用了临时表保存中间结果。常见于排序order by和分组查询group by。
Using filesort和Using temporary都是不太好的结果,会影响性能。
6.1. actor.name没有索引,此时创建了张临时表来distinct
explain select distinct name from actor;

6.2. film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
explain select distinct name from film;

Using where: 表示使用了where过滤。
explain select * from actor where name = ‘a’;

Using sort_union(…), Using union(…), Using intersect(…): 这 些 函 数 说 明 如 何 为index_merge 联接类型合并索引扫描。

Using index for group-by:类似于访问表的 Using index 方式,Using index for group-by 表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,463评论 6 497
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,868评论 3 391
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,213评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,666评论 1 290
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,759评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,725评论 1 294
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,716评论 3 415
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,484评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,928评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,233评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,393评论 1 345
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,073评论 5 340
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,718评论 3 324
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,308评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,538评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,338评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,260评论 2 352

推荐阅读更多精彩内容