数据库练习三： 认识Explain

介绍

EXPLAIN命令是查询性能优化不可缺少的一部分。
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

先来查看一下Explain 语句。

语句：

explain select * from employees;

image.png

其中最重要的字段为：id、type、key、rows、Extra

接下来 解析一下每个字段的含义

1. id

• id值相同，执行顺序由上往下。
• id值不同，越大优先值越高。

EXPLAIN  select concat(e.first_name,' ',last_name) `name` ,salary  from
    employees as e , salaries as sa where
    e.emp_no=sa.emp_no  and
    sa.salary= 
    (select min(salary)  from salaries);

image.png

执行顺序为 salaries->sa->e

2. select_type
   查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

• SIMPLE：简单的select查询，查询中不包含子查询或者union
• PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
• SUBQUERY：在select 或 where列表中包含了子查询
• DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
• UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
• UNION RESULT：从union表获取结果的select

3. type
   type 字段比较重要，它提供了判断查询是否高效的重要依据依据。 通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描等。

例子：
从第一点的图可以看到。

image.png

第一条和第三条，type 都是ALL，说明效率是很差，需要优化了。

效率好坏：

ALL < index < range ~ index_merge < ref < eq_ref < const < system

字段介绍：

• system: 表中只有一条数据， 这个类型是特殊的 const 类型。
• const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；
• eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_id;
• ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
• range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info where id between 2 and 8；
• index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。
• ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。

一般来说，好的sql查询至少达到range级别，最好能达到ref

4. possible_keys
   它表示 mysql 在查询时，可能使用到的索引。 注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 mysql 使用到。 mysql 在查询时具体使用了哪些索引，由 key 字段决定。

5. key
   重要字段
   此字段是 mysql 在当前查询时所真正使用到的索引。
   

   image.png

看第一条，possible_keys是主键，但key却为空，说明在实际查询中，并没有用上该索引。

6. key_len
   表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用

7. ref
   显示索引的那一列被使用了，如果可能，是一个常量const。

8. rows
   重要字段
   mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。

9. extra
   重要字段
   explain 中的很多额外的信息会在 extra 字段显示

• using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。
• using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
• using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
• using where ：表名使用了where过滤。