Mysql--基础知识点--91--explain

1 type

type字段表示了MySQL如何查找表中的行，即连接类型（Join Type）或访问类型（Access Type）。它描述了MySQL在执行查询时，如何遍历表中的数据以找到匹配的记录

• ALL (Full Table Scan):
  MySQL将遍历全表以找到匹配的行。在没有索引可用的情况下，通常会出现这种类型。这通常意味着需要优化查询，比如添加适当的索引。
• index:
  MySQL将遍历全索引来查找数据。这通常比全表扫描要快，因为索引通常远小于表数据。但是，如果索引不是查询中需要的列，那么这种类型可能不如预期的那么高效。
• range:
  索引范围扫描，对索引的某个范围进行扫描，比全索引扫描（index）要好，因为它只需要扫描索引中的一部分，通常是由于使用了BETWEEN、<、>、>=、<=等操作符。
• ref:
  非唯一性索引扫描，或者唯一性索引的前缀扫描，返回匹配某个单独值的所有行。与eq_ref类似，但是可能返回多行。
• eq_ref:
  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于使用主键或唯一索引作为联接条件。
• const/system:
  表中最多只有一个匹配行，它将在查询开始时被读取。因为仅有一行，所以优化器的其余部分可以将此视为常量。system是const的特例，当查询的表只有一行时（例如，系统表或使用了LIMIT 1）。
• NULL:
  MySQL能够在优化阶段分解查询语句，在执行时甚至不用访问表或索引。例如，在某些SELECT DISTINCT查询中，优化器能够直接从索引中获取信息，而无需回表查询数据。
• index_merge:
  这表示MySQL使用了索引合并优化技术。在这种情况下，MySQL会扫描多个索引，然后将结果合并。索引合并可以替代复杂的OR条件，从而提高查询效率。
• unique_subquery:
  在某些IN子查询中，子查询可能会返回唯一值，MySQL可以使用该值来优化查询。
• index_subquery:
  与unique_subquery类似，但子查询返回的值可能不唯一。
• range_or_index_merge:
  表示查询优化器发现range和index_merge都能用，但会选择更优的一种。
• index_union:
  类似于index_merge，但它只合并索引扫描的结果，而不是使用索引范围扫描。

理解和优化EXPLAIN结果中的type字段对于提高MySQL查询性能至关重要。不同的type值提供了关于查询执行方式的重要线索，从而指导我们如何调整查询语句或数据库结构以达到更好的性能。

2 select_type

select_type字段表示查询类型，即SELECT语句的类型。这个字段对于理解MySQL如何执行复杂的查询（特别是包含子查询、联合查询等）非常关键。以下是一些常见的select_type值及其解释：

• SIMPLE：
  表示这是一个简单的SELECT查询，不包含子查询或UNION等复杂操作。查询中不包含任何表连接（如INNER JOIN、LEFT JOIN等）的也被认为是SIMPLE类型，除非它们是UNION的一部分。
• PRIMARY：
  在包含UNION、子查询等复杂查询中，最外层的SELECT查询会被标记为PRIMARY。
• UNION：
  当查询中包含UNION时，UNION后的每个SELECT查询都会被标记为UNION。注意，UNION DISTINCT在EXPLAIN中也会被显示为UNION，因为MySQL优化器可能会自动去除重复行。
• UNION RESULT：
  UNION RESULT表示UNION操作的结果集。它不是指某个具体的SELECT语句，而是指从多个UNION SELECT语句中合并结果集的操作。
• SUBQUERY：
  在SELECT或WHERE子句中包含的子查询会被标记为SUBQUERY。子查询可能是一个完整的SELECT语句，它返回一个值、一行数据或一组行数据，用于外层查询的进一步处理。
• DERIVED（也称为临时表）：
  当MySQL需要从子查询中读取数据时，但它无法直接通过索引等方式高效访问这些数据时，MySQL会创建一个临时表来存储子查询的结果。这个临时表就是DERIVED表。然后，外层查询会从这个临时表中读取数据。注意，并不是所有的子查询都会被优化为DERIVED表，这取决于MySQL优化器的判断。
• MATERIALIZED：
  在某些情况下，MySQL可能会将子查询的结果集物化（即存储在磁盘上）为一个临时表，以便多次使用。这种情况下，子查询会被标记为MATERIALIZED。与DERIVED类似，但物化通常用于更复杂的情况，比如当子查询的结果集很大，且需要被多次访问时。
• UNCACHEABLE SUBQUERY：
  表示子查询的结果不能被缓存，因为它依赖于外层查询的某些值。这种子查询通常无法被优化为临时表（DERIVED）或物化表（MATERIALIZED）。
• DEPENDENT SUBQUERY：
  表示子查询依赖于外层查询的某些列的值。这意味着子查询必须在外层查询的每一行上重新计算。这种子查询通常无法被有效地优化，因为它无法利用索引或缓存来减少计算量。

理解和优化EXPLAIN结果中的select_type字段可以帮助你识别查询中的性能瓶颈，并采取相应的优化措施，比如添加索引、重写查询以避免复杂的子查询或联合查询等。

3 key

在MySQL的EXPLAIN输出中，key字段表示MySQL决定使用的索引来优化查询。这个字段对于理解查询的性能和优化至关重要。key字段的值可能表示查询中实际使用的索引，或者在某些情况下可能显示为NULL，表示没有使用索引。以下是一些key字段可能出现的几种情况：

• 索引名称：
  当MySQL能够利用索引来加速查询时，key字段将显示该索引的名称。这通常是一个好兆头，表明查询可能是高效的。索引的名称是你在创建索引时指定的名称，或者是MySQL自动生成的名称（对于自动创建的索引，如主键索引）。
• NULL：
  如果key字段的值为NULL，则表示MySQL决定不使用索引来执行这个查询。这可能是由于多种原因造成的，比如查询条件没有匹配到任何索引，或者MySQL优化器认为全表扫描（即不使用索引）比使用索引更高效。
• 可能的多个索引：
  在某些情况下，特别是当查询涉及到多个表和多个索引时，MySQL可能会考虑使用多个索引来优化查询。然而，EXPLAIN输出中的key字段通常只显示MySQL最终选择使用的索引。如果查询确实涉及到了多个索引的考虑，你可能需要查看其他输出字段（如possible_keys）来获取更多信息。但请注意，possible_keys字段只是显示可能用到的索引，而key字段则显示实际用到的索引。
• 复合索引的部分使用：
  如果查询条件只匹配了复合索引的一部分列，MySQL仍然可能会使用这个索引，但只利用与查询条件相匹配的列。在这种情况下，key字段将显示被部分使用的复合索引的名称。
• 索引合并：
  在某些情况下，MySQL可能会使用索引合并优化技术，即同时使用多个索引来执行查询。然而，在EXPLAIN输出中，key字段可能仍然只显示一个索引的名称，特别是当MySQL选择了一个“主导”索引来执行查询时。如果你怀疑MySQL使用了索引合并，可以查看Extra字段，它可能会包含类似Using index merge (union,…)的信息。
• 索引覆盖扫描：
  当查询只需要访问索引中的列时（即SELECT列表中只包含索引列），MySQL可以执行索引覆盖扫描。在这种情况下，key字段将显示被使用的索引的名称，而Extra字段可能会包含Using index的信息，表明MySQL只使用了索引而没有回表查询数据。

了解key字段的不同情况有助于你分析查询的性能，并识别出潜在的优化机会。如果你发现key字段为NULL，或者查询性能不佳，那么可能需要考虑添加或调整索引以提高查询效率。