用explain查看一个sql底层的执行情况

explain查看一个sql底层的执行情况

explain + sql语句
例如 explain select * from user
显示出：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	extra

---

mysql的索引，除了聚簇索引(一般都是主键)之外的索引(非聚簇索引)，存储的都是索引列上+主键列的数值，不存储其他列。所以查完索引如果要获取其他列的属性，还需要通过得到的主键去查一遍主键，主键索引会含有其他字段的信息。详细文章点这里：联合索引的底层结构

下面分别解释各列

1.id和table

id不代表主键，代表的一种状态，用来表示表的读取顺序。table是表名字。

1.1 id的第一种情况

如果id相同，则表的读取顺序是由上到下。

1.2 id的第二种情况

如果id不同，若是子查询，id序号递增，先执行id最大的。

1.3 id第三种情况

若相同的和不同的同时存在，先执行id最大的。对于一样的id，从上到下执行。上图执行顺序为：t3->derived->t2.

2. select_type

select_type表示的是数据库理解的查询类型，共六种。

1. simple：简单的select查询，查询中不包含子查询或者union
2. primary：查询中如果有任何复杂的子部分，最外层查询就是primary
3. subquery：select中的子查询
4. derived：from中的子查询就是derived，会把查询结果放在临时表里面
5. union：第二个查询在union之后，就会标记为union；若union包含在from的子查询中，外层的select会标记为derived
6. union result：从union表获取结果的select

3. type

type表示查询用的是哪种类型，常见的7种，由好到坏排序如下：
system>const>eq_ref>ref>range>index>all

1. system：只有一行记录，等于系统表，const类型的特例

2. 表示通过索引一次就找到了。const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。比如 where id=5,mysql就会把这个查询转换为常量。

3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。示例如下，因为t2中只有一个id和t1的id重合，所以t2是eq_ref。
   

4. ref：非唯一性索引扫描，返回所有匹配某个单独值的行。但也可能找到多个符合条件的行。示例如下，建了两个索引，应用其中一个，并且符合条件的不止有一行，下面只有一行是因为只插了一行数据。正常的索引都是这个type。
   

5. range：只检索给定范围的行，使用一个索引来选择行。key列会显示用了哪个索引。语句中含有between，>，<等字段。

6. index：只遍历索引树，而all是遍历整个表。索引在内存中，比硬盘中的数据读取快。

7. all：扫描整个表。

4. possible keys，key和key_len

possible keys显示查询字段上面的索引，若是有多个，都会列出。
key是实际用到的索引，如果是null，则没有使用索引，若用到了覆盖索引，则该索引只出现在key列表当中。

覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。如下图，col1和col2都有索引。

key_len表示索引字段的最大可能长度（单位：字节），并非实际使用长度，是根据表定义计算出来的，长度越短越好。

5. ref

哪些列或常量被用于查找索引列上的值。

t1用到了两个索引，一个是shared库的t2表的col1，还有一个是常数const(‘ac’)。

6. rows

根据表的信息，估算出需要遍历多少行才能找到符合条件的记录。
下图是建索引和没建索引的区别，建索引之后行数减少了一半多。

7. extra

不适合在其他列里面写，但又十分重要的消息。

7.1 using filesort

mysql无法按照原本的索引进行排序，只能按照默认排序的方法排序。
出现这个肯定是不好的，需要优化。

第一次order by没有用到col2索引，而第二次用到了，所以第二次没有usingfilesort。

7.2 using temporary

表示用了临时表存储中间结果，常见于order by和group by。
创建临时表，还要消除，所以出现这个字段也不好。

因为group by用到的索引并不是col1和col2的联合索引，导致索引失效。对于col1，col2，col3组成的联合索引，他们就像是楼梯一二三层，必须从左到右按顺序来，只用col2，col3或者只用col3，又或者col1加上col3，这都是不行的。

7.3 using index 和 using where

表示用到了覆盖索引，效率不错。如果同时出现了 using where ，表明索引被用来执行索引键值的查找。没有 using where ，表明索引用来读取数据而非查找。
用到了using where：

没用到using where：

7.4 using join buffer

用到了join缓存。

7.5 impossible where

where的值总是false，不能用来获取任何元素。

7.6 select tables optimized away和distinct

前者是数据库执行的优化操作，优化min，max，count等。后者在找到第一个匹配的元素之后就停止找同样值的操作。