网站怎么做分享链接,2018年静安区品牌网站建设,临安区规划建设局网站,dede关闭手机网站什么是Explain Explain被称为执行计划#xff0c;在语句之前增加 explain 关键字#xff0c;MySQL 会在查询上设置一个标记#xff0c;模拟MySQL优化器来执行SQL语句#xff0c;执行查询时#xff0c;会返回执行计划的信息#xff0c;并不执行这条SQL。#xff08;注意在语句之前增加 explain 关键字MySQL 会在查询上设置一个标记模拟MySQL优化器来执行SQL语句执行查询时会返回执行计划的信息并不执行这条SQL。注意如果 from 中包含子查询仍会执行该子查询将结果放入临时表中。 Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。 Explain命令扩展
explain extended 在explain的基础上提供一些额外的查询信息在explian extended执行以后通过show warnings命令可以得到优化后的查询语句可以看出优化器做了哪些工作还可以通过某些数据估算表连接的行数。
explain partitions 用于分析使用了分区的表会显示出可能用到的分区。 两点重要提示
1. Explain结果是基于数据表中现有数据的。
2. Explain结果与MySQL版本有很大的关系不同版本的优化器的优化策略不同。 本文示例使用的数据库表
Explain命令(关键字) explain简单示例
mysqlexplain select * from t_user; 在查询中的每个”表”会输出一行这里的“表”的意义非常广泛不仅仅是数据库表还可以是子查询、一个union 结果等。
explain结果列说明
【id列】 id列是一个有顺序的编号是查询的顺序号有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行id列的值相同则从上往下执行id列的值为NULL最后执行。
【select_type列】 select_type列的值标明查询的类型 1simple表明当前行对应的select是简单查询不包含子查询和union 2primary表明当前行对应的select是复杂查询中最外层的 select 3subquery表明当前行对应的select是包含在 select 中的子查询不在 from 子句中 4derived表明当前行对应的select是包含在 from 子句中的子查询。 MySQL会创建一个临时表来存放子查询的查询结果。用如下的语句示例说明 explain select (select 1 fromt_user where user_id1) from (select * from t_group where group_id1) tmp; *注意在资料收集过程中发现不同版本的MySQL表现不一致经反复对比5.7及以后版本的输出如下 很显然MySQL在这方面进行了优化. *注意MySQL不同版本Explain表现差异很大有些场景从语句层面看是要使用到索引但经过优化器分析结合表中现有数据如果MySQL认为全表扫描性能更优则会使用全表扫描。 5union表明当前行对应的select是在 union 中的第二个和随后的 select 6union result表明当前行对应的select是从 union 临时表检索结果的 select explain select 1 union all select 2 fromdual; MySQL5.7及以后同样做了优化
【table列】 table列的结果表明当前行对应的select正在访问哪个表。当查询的from子句中有子查询时table列是 derivedN 格式表示当前的select依赖 idN结果行对应的查询要先执行 id序号N 的查询。当存在 union 时UNION RESULT 的 table 列的值为unionN1,N2N1和N2表示参与 union 的select 行的id序号。
【type列】 type列的结果表明当前行对应的select的关联类型或访问类型也就是优化器决定怎么查找数据表中的行以及查找数据行记录的大概范围。该列的取值优化程度的优劣从最优到最差依次为nullsystem const eq_ref ref range index ALL。一般来说要保证查询达到range级别最好达到ref。 1nullMySQL优化器在优化阶段分解查询语句在优化过程中就已经可以得到结果那么在执行阶段就不用再访问表或索引。
explain select min(user_id) from t_user; 这时的函数min在索引列user_id中选取最小值可以直接查找索引来完成不需要执行时再访问数据表。 2const和systemconst出现在用 primary key主键 或 unique key唯一键 的所有列与常数比较时优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行读取1次速度非常快。而system是const的特例表中数据只有一条匹配时为system。此时可以用explain extendedshow warnings查看执行结果。 explain extended select * from (select * from t_user where user_id 1) tmp show warnings;
MySQL5.7及以后版本优化后 3eq_refprimary key主键或 unique key唯一键 索引的所有构成部分被join使用 只会返回一条符合条件的数据行。这是仅次于const的连接类型。 explain select * from t_group_user gu left join t_group g ong.group_id gu.group_id; 4) ref与eq_ref相比ref类型不是使用primary key主键 或 unique key唯一键等唯一索引而是使用普通索引或者联合唯一性索引的部分前缀索引和某个值相比较可能会找到符合条件的多个数据行。 1. 如下示例使用的group_name是普通索引 explain select * from t_group where group_name group1; 2.关联表查询 explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id g.group_id; 5range出现在 in(),between , ,, 等操作符中。使用一个索引来查询给定范围的行。 6index扫描全表索引index是从索引中读取的,所有字段都有索引而all是从硬盘中读取比ALL要快。 explain select * from t_group; 7all即全表扫描需要从头到尾去查找所需要的行。一般这种情况下这需要增加索引来进行查询优化了 explain select * from t_user; 【possible_keys列】 这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现possible_keys 列有结果而 后面的key列显示 null 的情况这是因为此时表中数据不多优化器认为查询索引对查询帮助不大所以没有走索引查询而是进行了全表扫描。 如果possible_keys列的结果是null则表明没有相关的索引。这时可以通过优化where子句增加恰当的索引来提升查询性能。
【key列】 这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引则该列是 null。
【key_len列】 这一列表明了在索引里使用的字节数通过这个值可以大致估算出具体使用了联合索引中的前几个列。
key_len计算规则这里不再赘述不同的数据类型所占的字节数是不一致的。
【ref列】 这一列表明了在key列记录的索引中表查找值所用到的列或常量常见的有const常量字段名如user.user_id
【rows列】 这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。
【Extra列】 顾名思义这一列表明的是额外信息,这一列的取值对优化SQL非常有参考意义。常见的重要取值如下 1using index所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列出现这样的结果是性能高的表现。
explainselect group_id,group_name from t_group; 2using where被查询的列未被索引覆盖where条件也并非索引的前导列表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”Post-filter。所谓“后过滤”就是先读取整行数据再检查此行是否符合 where 句的条件符合就留下不符合便丢弃。
explain select * from t_user whereuser_nameuser1; 3using where Using index被查询的列被索引覆盖并且where条件是索引列之一但是不是索引的前导列也就是没有办法直接通过索引来查询到符合条件的数据
explain select * from t_group where group_name group1; 4null被查询的列没有被索引覆盖但where条件是索引的前导列此时用到了索引但是部分列未被索引覆盖必须通过“回表查询”来实现不是纯粹地用到了索引也不是完全没用到索引
explain select * from t_user where user_id1;
5using index condition与using where类似查询的列不完全被索引覆盖where条件中是一个前导列的范围这种情况未能通过示例显现可能跟MySQL版本有关系。 6 using temporary这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的用索引来优化。创建临时表的情况distinctgroup byorderby子查询等 explain select distinct user_name from t_user;
explain select distinct group_name fromt_group; --group_name是索引列 7) usingfilesort在使用order by的情况下出现mysql会对结果使用一个外部索引排序而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录并保存排序关键字和行指针然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。 explain select * from t_user orderby user_name; explain select * from t_group order bygroup_name; --group_name是索引列 查询优化建议 结合前面的描述首先看 type列的结果如果有类型是 all 时表示预计会进行全表扫描fulltable scan。通常全表扫描的代价是比较大的建议创建适当的索引通过索引检索避免全表扫描。 再来看下 Extra 列的结果如果有出现 Using temporary 或者 Using filesort 则要多加关注 Using temporary表示需要创建临时表以满足需求通常是因为GROUP BY的列没有索引或者GROUP BY和ORDER BY的列不一样也需要创建临时表建议添加适当的索引。 Using filesort表示无法利用索引完成排序也有可能是因为多表连接时排序字段不是驱动表中的字段因此也没办法利用索引完成排序建议添加适当的索引。 Using where通常是因为全表扫描或全索引扫描时type 列显示为 ALL 或index又加上了WHERE条件建议添加适当的索引。
索引使用情况分析
数据库表 主键索引demo_id
联合索引c1,c2,c3 实例说明
实例一
explain select * from t_demo where c1d1and c2d2 and c3d3;
explain select * from t_demo where c2d2and c1d1 and c3d3;
explain select * from t_demo where c3d3and c1d1 and c2d3;
几个Sql表现一致
typeref,refconst,const,const
执行常量等值查询时改变索引列的顺序并不会更改explain的执行结果优化器会进行优化推荐按照索引顺序列编写sql语句。 实例二
explain select * from t_demo where c1d1and c2d2 and c3d3; explain select * from t_demo where c1d1and c3d3 and c2d2; 第一个例子范围右侧索引失效使用到了两个索引。
第二个例子由于优化器优化的原因使用到了全部的三个索引。
实例三
explain select * from t_demo wherec1c and c2d2 and c3d3; explain select * from t_demo wherec1e and c2d2 and c3d3; 从上面两个实例可以发现同样使用最左的索引列范围查询有些情况未用到索引做了全表扫描第一个例子有些情况使用到了索引第二个例子。 经反复验证发现如下规律不一定可靠也可能与数据的第一行或最小值相关。
1. 跟存储的数据有关
2. 在大于条件下如果条件数据小于列数据则索引无效如果条件数据大于列数据则索引有效 在设计查询条件时请注意规避。
针对第一个例子可以采用覆盖索引的方式优化。
实例四
explain select * from t_demo where c1d1and c2d2 order by c3; explain select * from t_demo where c1d1order by c3; explain select * from t_demo where c1d1and c3d3 order by c2; order by排序使用到索引和没使用到索引的情况
实例五
explain select * from t_demo where c1d1and c4d4 order by c1,c2; 条件列包含没有索引的列出现了Using filesort
实例六
explain select * from t_demo where c1d1and c4d4 group by c1,c2; 性能非常差的场景同时出现了Using temporary和Using filesort
总结 1. 两种方式的排序filesort和indexUsingindex是指MySQL扫描索引本身完成排序。index效率高filesort效率低。 2. order by满足两种情况会使用Using index。
1order by语句使用索引最左前列。
2使用where子句与order by子句条件列组合满足索引最左前列。 3. 尽量在索引列上完成排序遵循索引建立索引创建的顺序时的最佳左前缀法则。 4. group by与order by很类似都是先排序后分组遵照索引创建顺序的最佳左前缀法则。
