《mysql学习》-- explain执行计划

mysql

参考：MySQL执行计划解读

explain执行计划

使用explain查看，尽量优化sql使rows尽量小，因为查询的时候会对数据行加锁，所以rows越小代表锁的行越少

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	sp	ALL	NULL	NULL	NULL	NULL	3613155	Using where

id

MySQL选定的执行计划中查询的序列号。如果语句里没有子查询等情况，那么整个输出里就只有一个SELECT，这样一来每一行在这个列上都会显示一个1。如果语句中使用了子查询、集合操作、临时表等情况，会给ID列带来很大的复杂性。如果WHERE部分使用了子查询，其id=2的行表示一个关联子查询。

select_type

语句所使用的查询类型。是简单SELECT还是复杂SELECT(如果是后者，显示它属于哪一种复杂类型)。常用有以下几种标记类型。

1. primary

   子查询中的最外层查询，注意并不是主键查询。

2. simple

   简单查询，即没有使用联合或者子查询

3. UNCACHEABLE SUBQUERY

   结果集无法缓存的子查询。

4. union

   union语句中的第二个SELECT开始后面的所有SELECT，第一个SELECT为primary。

5. dependent union

   子查询中的union，且为union中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。

6. union result

   union 中的合并结果。从union 临时表获取结果的SELECT。

7. subquery

   子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。

8. dependent subquery

   子查询内层的第一个SELECT，依赖于外部查询的结果集。

9. derived

   衍生表查询(FROM子句中的子查询)。MySQL会递归执行这些子查询，把结果放在临时表里。在内部，服务器就把当做一个”衍生表”那样来引用，因为临时表就是源自子查询。

table

这一步所访问的数据库中表的名称或者SQL语句指定的一个别名表。这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符，如派生表、子查询或集合。

type

阿里SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。说明：1 ）

表的访问方式。以下列出了各种不同类型的表连接，依次是从最好的到最差的。

1. system

   系统表，表只有一行记录。这是const表连接类型的一个特例。

2. const

   表中最多只有一行匹配的记录。读常量，由于只有一行记录，优化程序里该行记录的字段值可以被当作是一个常量，仅在查询开始时读取一次。当PRIMARY KEY或UNIQUE索引与常量进行比较时会显示const，速度非常快

3. eq_ref

   类似const，const比较的是常量，eq_ref是同另一张表中的字段关联比较，这是最好的连接类型。eq_ref出现在PRIMARY KEY或UNIQUE类型索引进行”=”做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段。

4. ref

   查询时的索引类型不是PRIMARY KEY或UNIQUE类型索引导致匹配到的行有多个，或者仅能用到索引的左前缀而非全部时的访问类型。ref可被用于基于索引字段进行 - 或 <=> 操作

5. ref_or_null

   与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。这种连接类型类似ref，不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录，它经常用于子查询。

6. index_merge

   查询中同时使用两个(或更多)索引，然后对索引结果进行合并(merge)，再读取表数据。这种连接类型意味着使用了Index Merge优化方法。

7. unique_subquery

   子查询中的返回结果字段组合是主键或唯一约束。用于in比较操作符中的子查询锦绣谷的“键值唯一”的访问类型场景中，如value in (select primary_key from table where x = x)

8. index_subquery

   子查询中的返回结果字段组合是一个索引(或索引组合)，但不是一个主键或唯一索引。这种连接类型类似unique_subquery。它用子查询来代替IN，不过它用于在子查询中没有唯一索引的情况下。

9. range

   索引范围扫描 。只有在给定范围的记录才会被取出来，利用索引来取得一条记录。常见于between、<、>等的查询

10. index

    全索引扫描 。连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小。MySQL在查询的字段只是单独的索引的一部分的情况下使用这种连接类型。

11. fulltext

    用于full text索引中用纯文本匹配的方法来检索记录

12. all

    全表扫描。

possible_keys

该字段是指MySQL在搜索表记录时可能使用哪个索引。如果没有任何索引可以使用，就会显示为null。

key

查询时只能使用一个索引， 查询优化器从possible_keys中所选择使用的索引。key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是NULL。

key_len

被选中使用索引的索引键长度。key_len字段显示了MySQL使用索引的长度。当key字段的值为NULL时，索引的长度就是NULL。

ref

列出是通过常量，还是某个表的某个字段来过滤的。ref字段显示了哪些字段或者常量被用来和key配合从表中查询记录出来。

rows

该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。

Extra

该字段显示了查询中MySQL的附加信息。

1. Using where

   mysql服务器将在存储引擎收到数据后进行后过滤；如果where条件中使用了索引列，其读取索引时就由存储引擎检查，因此并非所有带有where子句的查询都会显示Using where

2. Using index

   表示所需要的数据从索引中就能够全部获取到，而不需要再次从表中查询数据，这意味着mysql将使用覆盖索引，但如果同时还出现了Using where，则表示索引将被用于查找特定的键值

3. Using index for group-by

   类似Using index，它表示mysql可仅通过索引中的数据完成group by或distinct操作

4. Using filesort

   表示mysql会对结构使用一个外部索引排序，而不是从表里按索引次序来读取行