在 MySQL 中，EXPLAIN 命令是一个非常有用的工具，它可以帮助我们深入了解 MySQL 执行一条 SQL 语句时的优化过程。 type 列和 extra 列是 EXPLAIN 结果中两个最重要的列，它们能提供关于查询执行方式的详细信息。

注意： EXPLAIN 提供的信息是基于 MySQL 优化器对 SQL 语句的分析，实际执行情况可能会有所不同。

type 列

type 列显示了 MySQL 处理查询时所使用的访问类型。不同的访问类型代表不同的查询执行效率，一般来说，访问类型越靠前，查询效率越高。

常见的访问类型包括：

  • system: 表中只有一行记录（系统表），直接返回。
  • const: 表中最多只有一条匹配的行，如主键或唯一索引。
  • eq_ref: PRIMARY KEY 或 UNIQUE index 的所有部分被用在连接中。
  • ref: 非唯一索引列或常数被用在连接。
  • range: 使用索引来检索一个范围的行，如使用 between、<、> 等操作符。
  • index: 全索引扫描，根据索引来获取所需的行。
  • all: 全表扫描，MySQL必须检索表中的所有行以找到匹配的行。

一般来说，我们希望查询的 type 尽可能靠前，比如 system 或 const，这表示 MySQL 能很快地找到数据。而 all 则表示 MySQL 需要扫描整个表，效率最低。

extra 列

extra 列显示了 MySQL 执行查询时所使用的访问类型的详细信息。一些常见的 extra 值包括：

  • using index: 表示 MySQL 只使用了索引来完成查询，没有读取表中的行数据。
  • using where: 表示 MySQL 在读取索引后，又使用 where 条件对结果集进行了过滤。
  • using temporary: MySQL 需要创建一个临时表来存储结果。
  • using filesort: MySQL 需要对结果集进行排序，这通常发生在排序列没有索引的情况下。

一般来说，我们希望 extra 中出现 Using index，这表示查询效率很高。而 Using temporary 和 Using filesort 则表示查询效率较低，可能需要优化。

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

MySQL root@localhost:sakila> explain select * from inventory\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | inventory
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 4581
filtered      | 100.0
Extra         | <null>
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

MySQL root@localhost:sakila> explain select store_id , film_id FROM inventory\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | inventory
partitions    | <null>
type          | index
possible_keys | <null>
key           | idx_store_id_film_id
key_len       | 3
ref           | <null>
rows          | 4581
filtered      | 100.0
Extra         | Using index

总结

type 列和 extra 列是分析 MySQL 查询性能的重要指标。通过了解它们的含义，我们可以针对性地优化 SQL 语句，提高查询效率。

优化建议：

  • 创建合适的索引： 为经常用在 where 条件、order by 和 group by 子句中的列创建索引。
  • 避免使用 select *： 只查询需要的列，减少数据的传输。
  • 优化 where 条件： 尽量使用索引列，避免使用函数和表达式。
  • 分表： 当数据量过大时，可以考虑分表。