explAIn关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下:
explain各个字段代表的意思
select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序。三种情况:
id 相同: 执行顺序由上而下:
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';
id 不同: 如果是子查询,id 序号会递增,id 越大优先级越高,越先被执行。
explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));
id 相同不同同时存在: id 如果相同,可以认为是一组,由上往下执行;在所有组里 id 越大,优先级越高,越先执行。
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;
主要用于区别普通查询、联合查询、子查询等的复杂程度。
SIMPLE: 简单的 select 查询,查询中不包含子查询或者 UNION。
PRIMARY: 查询中若包含任何复杂的自查询,最外层查询为 PRIMARY。
SUBQUERY: 在 SELECT 或 WHERE 中包含子查询。
UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行。
DERIVED: 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)MySQL 会递归执行这些子查询,把结果放进临时表。
UNION: 若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION,若 UNION 包含在 FROM 子句的子查询,则外层SELECT 将被标记为 DERIVED。
UNION RESULT: 从 UNION表中获取结果的 SELECT。
显示这行数据是关于那张表
首先说一下这个字段,要记住以下10个状态,(从左往右,越靠左边的越优秀)
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。存在这样一种情况,大家都知道索引是将数据在B+Tree中进行排序了,所以你的查询速率才这么高,那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊?既然你都知道了,那MySQL比你更知道啊!当你要查询最大值或者最小值时,MySQL会直接到你的索引得分叶子节点上直接拿,所以不用访问表或者索引。
NULL的前提是你已经建立了索引。
表只有一行记录(等于系统表),这是const类型的特列,平时不大会出现,可以忽略。
表示通过索引一次就能找到,const用于比较 primary和 unique索引。因为只匹配一行数据,所以很快。
简单来说,const是直接按主键或唯一键读取。
用于联表查询的情况,按联表的主键或唯一键联合查询。多表join时,对于来自前面表的每一行,在当前表中只能找到一行。这可能是除了system和const之外最好的类型。
ref 可以用于单表扫描或者连接。如果是连接的话,驱动表的一条记录能够在被驱动表中通过非唯一(主键)属性所在索引中匹配多行数据,或者是在单表查询的时候通过非唯一(主键)属性所在索引中查到一行数据。
ref_or_null 类似ref,但是可以搜索值为NULL的行。
表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range。
只检索给定范围的行,使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好,因为只需开始索引的某一点,而结束另一点,不用扫描全部索引;
Full Index Scan,index与 ALL区别为 index类型只遍历索引树,索引文件通常比数据文件小。index从索引中读取,而All是从硬盘读取;
从磁盘中读取;如果一个查询的type是All,并且表的数据量很大,那么请解决它!!!
这个表里面存在且可能会被使用的索引,可能会在这个字段下面出现,但是一般都以key为准。
实际使用的索引,如果为null,则没有使用索引,否则会显示你使用了哪些索引,查询中若使用了覆盖索引(查询的列刚好是索引),则该索引仅出现在key列表。
列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。
rows是根据表的统计信息和索引的选用情况,优化器大概帮你估算出你执行这行函数所需要查询的行数。
Filter是查询的行数与总行数的比值。其实作用与rows差不多,都是数值越小,效率越高。
表示相应的 select操作中使用了覆盖索引(convering index),避免访问了表的数据行,效率不错!
覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。
表示MySQL需要使用临时表来存储结果集,常见于排序 order by和分组查询 group by。
表示当SQL中有一个地方需要对一些数据进行排序的时候,优化器找不到能够使用的索引,所以只能使用外部的索引排序。
MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上,一般可以通过合适的索引来减少或者避免。(出现表示不好)
上面提到的常见情况,SQL语句通常写成这样select * from a where type = 5 order by id,这类语句一般会产生Using filesort这个选项,即使你在type和id上分别添加了索引。我们想一下它的工作过程,先根据type的索引从所有数据信息中挑选出满足type = 5条件的,然后根据id列的索引信息对挑选的数据进行排序,所以产生了Using filesort选项。可以通过联合索引解决这个问题,即在type, id两列上建立一个联合索引。
查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra 中为 Using where。
所谓前导列,就是在创建复合索引语句的第一列或者连续的多列。比如通过:CREATE INDEX comp_ind ON table1(x, y, z)创建索引,那么x,xy,xyz都是前导列,而yz,y,z这样的就不是。
查询的列被索引覆盖,并且 where筛选条件是索引列之一但是不是索引的前导列,Extra中为Using where; Using index,意味着无法直接通过索引查找来查询到符合条件的数据;
查询的列被索引覆盖,并且where筛选条件是索引列前导列的一个范围,同样意味着无法直接通过索引查询到符合条件的数据
查询的列不全在索引中,where条件中是一个前导列的范围
查询列不完全被索引覆盖,查询条件完全可以使用到索引(进行索引查找)
NULL(既没有Using index,也没有Using where Using index,也没有using where)。
查询的列未被索引覆盖,并且where筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现,不是纯粹地用到了索引,也不是完全没用到索引,Extra中为NULL(没有信息)。
使用了连接缓存。
小表驱动大表
在表连接过程中。一般选择小表作为驱动表,大表作为被驱动表。
驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引,则可以走索引。如果没有建立索引则也需要全表扫描。
两张表连接的情况
被驱动表的连接字段有索引:主键索引
对于驱动表中的每一条数据,到被驱动表的聚簇索引上寻找其对应的数据。
被驱动表的连接字段有索引:二级索引
对于驱动表上的每一条数据,到被驱动表的二次索引上寻找其对应的数据id,然后再根据数据id到聚簇索引上寻找对应的数据。
被驱动表的连接字段没有索引
对于驱动表上的每一条数据,都要到被驱动表上进行一次全表遍历,找到对应的数据。
join buffer的作用
就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描,所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。
where子句总是false,不能用来获取任何元素。即筛选条件没能筛选出任何数据。
在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX操作。
优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。