江苏龙网

  1. 首页 > 人文 > >

最完整的Explain总结，妈妈再也不担心我的SQL优化了

在select语句之前增加explain关键字 ， MySQL会在查询上设置一个标记 ， 执行查询时 ， 会返回执行计划的信息 ， 而不是执行这条SQL（如果from中包含子查询 ， 仍会执行该子查询 ， 将结果放入临时表中）
CREATETABLE`film`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(10)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`actor`(`id`int(11)NOTNULL,`name`varchar(45)DEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`film_actor`(`id`int(11)NOTNULL,`film_id`int(11)NOTNULL,`actor_id`int(11)NOTNULL,`remark`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_film_actor_id`(`film_id`,`actor_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8两个变种explainextended
会在explain的基础上额外提供一些查询优化的信息 。 紧随其后通过showwarnings命令可以得到优化后的查询语句 ， 从而看出优化器优化了什么 。 额外还有filtered列 ， 是一个半分比的值 ， rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）
mysql>explainextendedselect*fromfilmwhereid= 相比explain多了个partitions字段 ， 如果查询是基于分区表的话 ， 会显示查询将访问的分区 。
id列id列的编号是select的序列号 ， 有几个select就有几个id ， 并且id的顺序是按select出现的顺序增长的 。 MySQL将select查询分为简单查询(SIMPLE)和复杂查询(PRIMARY) 。 复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union查询 。 id列越大执行优先级越高 ， id相同则从上往下执行 ， id为NULL最后执行**
select_type列select_type表示对应行是简单还是复杂的查询 ， 如果是复杂的查询 ， 又是上述三种复杂查询中的哪一种 。 1.simple简单查询 。 查询不包含子查询和union
mysql>explainselect*fromfilmwhereid=2; 3）subquery：包含在select中的子查询（不在from子句中）
4）derived：包含在from子句中的子查询 。 MySQL会将结果存放在一个临时表中 ， 也称为派生表（derived的英文含义）用这个例子来了解primary、subquery和derived类型
mysql>explainselect(select1fromactorwhereid=1)from(select*fromfilmwhereid=1)der; 6）unionresult：从union临时表检索结果的select用这个例子来了解union和unionresult类型：
mysql>explainselect1unionallselect1; type列这一列表示关联类型或访问类型 ， 即MySQL决定如何查找表中的行 ， 查找数据行记录的大概范围 。 依次从最优到最差分别为：system>const>eq_ref>ref>range>index>ALL一般来说 ， 得保证查询达到range级别 ， 最好达到ref
NULL
mysql能够在优化阶段分解查询语句 ， 在执行阶段用不着再访问表或索引 。 例如：在索引列中选取最小值 ， 可以单独查找索引来完成 ， 不需要在执行时访问表
mysql>explainselectmin(id)fromfilm; mysql能对查询的某部分进行优化并将其转化成一个常量（可以看showwarnings的结果） 。 用于primarykey或uniquekey的所有列与常数比较时 ， 所以表最多有一个匹配行 ， 读取1次 ， 速度比较快 。 system是const的特例 ， 表里只有一条元组匹配时为system
mysql>explainextendedselect*from(select*fromfilmwhereid=1)tmp;eq_ref
primarykey或uniquekey索引的所有部分被连接使用 ， 最多只会返回一条符合条件的记录 。 这可能是在const之外最好的联接类型了 ， 简单的select查询不会出现这种type 。
mysql>explainselect*fromfilm_actorleftjoinfilmonfilm_actor.film_id=film.id; 相比eq_ref ， 不使用唯一索引 ， 而是使用普通索引或者唯一性索引的部分前缀 ， 索引要和某个值相比较 ， 可能会找到多个符合条件的行 。
简单select查询 ， name是普通索引（非唯一索引）mysql>explainselect*fromfilmwherename="film1";2.关联表查询 ， idx_film_actor_id是film_id和actor_id的联合索引 ， 这里使用到了film_actor的左边前缀film_id部分 。
mysql>explainselectfilm_idfromfilmleftjoinfilm_actoronfilm.id=film_actor.film_id; 范围扫描通常出现在in(),between,>,=等操作中 。 使用一个索引来检索给定范围的行 。
mysql>explainselect*fromactorwhereid>1;index
扫描全表索引 ， 这通常比ALL快一些 。 （index是从索引中读取的 ， 而all是从硬盘中读取）
mysql>explainselect*fromfilm;ALL
即全表扫描 ， 意味着mysql需要从头到尾去查找所需要的行 。 通常情况下这需要增加索引来进行优化了
mysql>explainselect*fromactor;possible_keys列这一列显示查询可能使用哪些索引来查找 。 explain时可能出现possible_keys有列 ， 而key显示NULL的情况 ， 这种情况是因为表中数据不多 ， mysql认为索引对此查询帮助不大 ， 选择了全表查询 。 如果该列是NULL ， 则没有相关的索引 。 在这种情况下 ， 可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能 ， 然后用explain查看效果 。
key列这一列显示mysql实际采用哪个索引来优化对该表的访问 。 如果没有使用索引 ， 则该列是NULL 。 如果想强制mysql使用或忽视possible_keys列中的索引 ， 在查询中使用forceindex、ignoreindex 。
key_len列这一列显示了mysql在索引里使用的字节数 ， 通过这个值可以算出具体使用了索引中的哪些列 。 举例来说 ， film_actor的联合索引idx_film_actor_id由film_id和actor_id两个int列组成 ， 并且每个int是4字节 。 通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找 。
mysql>explainselect*fromfilm_actorwherefilm_id=2;“key_len计算规则如下：字符串char(n)：n字节长度varchar(n)：2字节存储字符串长度 ， 如果是utf-8 ， 则长度3n+2数值类型tinyint：1字节smallint：2字节int：4字节bigint：8字节时间类型date：3字节timestamp：4字节datetime：8字节”
如果字段允许为NULL ， 需要1字节记录是否为NULL索引最大长度是768字节 ， 当字符串过长时 ， mysql会做一个类似左前缀索引的处理 ， 将前半部分的字符提取出来做索引 。
ref列这一列显示了在key列记录的索引中 ， 表查找值所用到的列或常量 ， 常见的有：const（常量） ， 字段名（例：film.id）
rows列这一列是mysql估计要读取并检测的行数 ， 注意这个不是结果集里的行数 。
Extra列这一列展示的是额外信息 。 常见的重要值如下：
Usingindex
查询的列被索引覆盖 ， 并且where筛选条件是索引的前导列 ， 是性能高的表现 。 一般是使用了覆盖索引(索引包含了所有查询的字段) 。 对于innodb来说 ， 如果是辅助索引性能会有不少提高
mysql>explainselectfilm_idfromfilm_actorwherefilm_id=1;Usingwhere
查询的列未被索引覆盖 ， where筛选条件非索引的前导列
mysql>explainselect*fromactorwherename='a';UsingwhereUsingindex
查询的列被索引覆盖 ， 并且where筛选条件是索引列之一但是不是索引的前导列 ， 意味着无法直接通过索引查找来查询到符合条件的数据
mysql>explainselectfilm_idfromfilm_actorwhereactor_id=1;NULL
查询的列未被索引覆盖 ， 并且where筛选条件是索引的前导列 ， 意味着用到了索引 ， 但是部分字段未被索引覆盖 ， 必须通过“回表”来实现 ， 不是纯粹地用到了索引 ， 也不是完全没用到索引
mysql>explainselect*fromfilm_actorwherefilm_id=1;Usingindexcondition
与Usingwhere类似 ， 查询的列不完全被索引覆盖 ， where条件中是一个前导列的范围；
mysql>explainselect*fromfilm_actorwherefilm_id>1;Usingtemporary
mysql需要创建一张临时表来处理查询 。 出现这种情况一般是要进行优化的 ， 首先是想到用索引来优化 。
actor.name没有索引 ， 此时创建了张临时表来distinctmysql>explainselectdistinctnamefromactor;film.name建立了idx_name索引 ， 此时查询时extra是usingindex,没有用临时表mysql>explainselectdistinctnamefromfilm;Usingfilesort
mysql会对结果使用一个外部索引排序 ， 而不是按索引次序从表里读取行 。 此时mysql会根据联接类型浏览所有符合条件的记录 ， 并保存排序关键字和行指针 ， 然后排序关键字并按顺序检索行信息 。 这种情况下一般也是要考虑使用索引来优化的 。
actor.name未创建索引 ， 会浏览actor整个表 ， 保存排序关键字name和对应的id ， 然后排序name并检索行记录mysql>explainselect*fromactororderbyname;film.name建立了idx_name索引,此时查询时extra是usingindexmysql>explainselect*fromfilmorderbyname;个人网站：www.upheart.top/
作者：月伴飞鱼
链接：
【最完整的Explain总结，妈妈再也不担心我的SQL优化了】来源：掘金


    推荐阅读


    上一篇：坚果 R1迎来全新Smartisan OS 7.2.0推送，支持更多新功能

    下一篇：什么|七夕节大家都在送什么？曹操帮忙大数据告诉你