sql优化——在mysql中使用explain查看sql执行计划

1. 为什么要查看执行计划

       数据表在程序开发中是很重要的一环。在数据量较小时，比如要查询的是一个单表，而这个表至多不过是几百条数据，那我们也许无需关注它的查询效率，因为它总是会很快的查出结果；但如果要查询的是一个大表，达到了几十万、几百万、甚至更大时，又或者一个查询的结构比较复杂，涉及到连表与子查询，这个时候去查看这个sql的执行计划，并根据执行计划所显示的信息去优化数据表与sql语句就很重要了。

 

2. 在mysql中查看执行计划

       在mysql中只要在任意查询语句前面加上explain就可以查看该sql的执行计划，如下图所示。

 

3. 详解执行计划各个字段的含义

3.1 id

        在涉及到多表连接查询或者子查询时，使用explain会有显示多列数据。此时id值大的先执行；id的值相同时，表示由上至下执行。如下两图所示，一为内连接、二为包含子查询的sql。

3.2 select_type

表示查询的类型，包含子查询、连表查询等。select_type取值为一下几种，查询的效率由上之下是越来越慢的。

SIMPLE：查询中不包含子查询或者UNION，为查询速度最快的查询；

PRIMARY：查询中包含子查询的sql，在最外层查询的select_type会为此，且只有一个；

SUBQUERY：子查询语句；

DERIVED: from子句中出现的子查询，该结果存在于临时表中；

UNION: union连接两个select查询，第二个SELECT出现在UNION之后，标记为UNION；

UNION RESULT: 从UNION结果中进行SELECT，因其不参与对数据表的操作，只是临时表，所以id为null。

3.3 table

        显而易见，table列表示的是查询的是哪张表。

3.4 type

       type是执行计划中的一个重要指标，表示访问类型，跟索引有很大的关系，简单说就是一个查询是扫描了全表还是索引起到了它该有的作用。大致有以下几个值，从上至下表示效率由快至慢。一般来说在进行优化时，需保证至少达到range级别，最好达到ref级别。

NULL: 不访问表，直接返回结果，比如SELECT 2 FROM DUAL;

system: 访问的表只有一行数据或是空表；

const: 通过索引一次就查到结果，只返回一条记录，通常是指通过主键查找或者唯一索引；

eq_ref: 多表关联查询，且查出的记录只有一条，常出现在主键查询或者唯一索引；

ref: 扫描非唯一性索引，返回的结果可能会有多行。

range: 走索引，在WHERE之后使用BETWEEN, <,>,in等操作；

index: 遍历了整个索引树，性能不高；

all: 遍历全表。

3.5 possible_keys

        显示可能应用在该表的索引。

3.6 key

        显示实际走的索引，若为null则表示没有走索引。

3.7 key_len

        显示索引使用的字节数，在不损失精度的条件下，该字段值越小越好。

3.8 ref

        显示索引走的字段。

3.9 rows

        显示查询扫描行的数量。

4.0 Extra

        显示以上字段没有展示出来的信息，需关注的值有以下几种。

        using filesort：按“文件排序”；

        using temporary： 用到了临时表存储结果；

        using index：用到了索引，效率较好。