MySQL中EXPLAIN关键字详解

昨天领导突然问到，MySQL中explain获取到的type字段中index和ref的区别是什么。
这两种状态都是在使用索引后产生的，但具体区别却了解不多，只知道ref相比于index效率更高。
因此，本文较为详细地记录了MySQL性能中返回字段的含义、状态级别的产生条件与区别。

索引

假设有一个表 employees，包含以下字段：id、first_name、last_name 和 address。

1. 普通索引 (Normal Index)：这是最基本的索引类型，它没有任何限制。可以对表中的一个或多个字段创建普通索引，以加快数据查询的速度。

• 示例

  CREATE INDEX idx_last_name ON employees (last_name);
  

2. 唯一索引 (Unique Index)：该索引与普通索引类似，不同之处在于索引列中的值必须唯一，但允许有空值（NULL）。如果在列中插入重复值，MySQL 会报错。

• 示例

  CREATE UNIQUE INDEX idx_unique_first_name ON employees (first_name);
  

3. 主键索引 (Primary Key Index)：主键索引是一种特殊的唯一索引，不允许空值（NULL）。一个表只能有一个主键索引，一般用于标识表中的唯一记录。

• 示例

  ALTER TABLE employees ADD PRIMARY KEY (id);
  

4. 全文索引 (Full-text Index)：用于全文搜索的索引，主要用于 CHAR、VARCHAR 和 TEXT 类型的字段。它可以加快对大文本数据的搜索速度，适用于需要进行全文检索的场景。

• 示例

  CREATE FULLTEXT INDEX idx_fulltext_address ON employees (address);
  

5. 组合索引 (Composite Index)：组合索引是对表中的多个列创建的索引，用于提高多列条件查询的性能。MySQL 会根据组合索引中列的顺序来优化查询。

• 示例

  CREATE INDEX idx_name ON employees (first_name, last_name);
  

7. 空间索引 (Spatial Index)：这是 MySQL 特有的索引类型，用于空间数据类型（如 POINT、LINESTRING、POLYGON 等）的索引。主要用于地理空间查询。

• 示例

  CREATE SPATIAL INDEX idx_geometry ON locations (geometry);
  

关键字

EXPLAIN 是 MySQL 中的一个关键字，用于分析 SQL 查询语句的执行计划。通过 EXPLAIN 返回的信息，用户可以了解查询优化器是如何选择执行计划的，以及可能的性能瓶颈。

1. id

   • 每个查询的唯一标识符。对于多表查询，id的值会增大。

2. select_type

   • 查询的类型，主要有以下几种：
     • SIMPLE: 简单查询，不包含子查询或UNION。
     • PRIMARY: 最外层的SELECT。
     • SUBQUERY: 子查询中的第一个SELECT。
     • DERIVED: 派生表中的SELECT，比如在FROM子句中包含子查询。
     • UNION: UNION中的第二个或后续的SELECT。
     • UNION RESULT: UNION的结果集。

3. table

   • 当前查询的表。

4. partitions

   • 匹配的分区信息（如果有分区）。

5. type

   • 表连接类型，显示查询中使用的连接类型，主要有以下几种，从优到劣排列：
     • system: 表只有一行（系统表）。
     • const: 表最多有一个匹配行，用于主键或唯一索引。
     • eq_ref: 对每个来自前一个表的行组合，从该表读取一行。
     • ref: 对于每个来自前一个表的行组合，从该表读取所有匹配的行。
     • range: 检索给定范围的行，使用索引来选择行。
     • index: 扫描整个索引。
     • ALL: 扫描整个表。

6. possible_keys

   • 查询中可能使用的索引。

7. key

   • 查询中实际使用的索引。

8. key_len

   • 使用的索引的长度。

9. ref

   • 显示哪一列或常量与key一起使用。

10. rows

    • MySQL 估计要读取的行数。

11. filtered

    • 经过WHERE条件过滤后，返回的行的百分比。

12. Extra

    • 附加信息：
      • Using index: 表示使用了覆盖索引（只从索引中读取信息，不用回表）。
      • Using where: 使用了WHERE子句来过滤行。
      • Using temporary: 使用了临时表来保存中间结果。
      • Using filesort: MySQL使用外部排序而不是从表中按索引顺序读取行。

示例

以下是一个使用 EXPLAIN 的查询及其返回结果的示例：

EXPLAIN SELECT first_name, last_name FROM employees WHERE id = 1;

假设返回结果如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	const	PRIMARY	PRIMARY	4	const	1	100.0	Using index

解释：

• id 是 1，表示这是一个简单查询。
• select_type 是 SIMPLE，表示没有子查询。
• table 是 employees，查询的表是 employees。
• type 是 const，表示使用了主键查询。
• possible_keys 是 PRIMARY，表示可能使用的索引是主键。
• key 是 PRIMARY，实际使用的索引是主键。
• key_len 是 4，表示索引的长度为 4 字节。
• ref 是 const，表示查询条件使用了常量。
• rows 是 1，表示预期扫描1行。
• filtered 是 100.0，表示返回的行没有被过滤。
• Extra 是 Using index，表示查询只使用了索引。

通过分析这些信息，用户可以优化查询，调整索引，提高查询性能。

type级别解释

在 EXPLAIN 语句的输出中，type 列表示 MySQL 在执行查询时使用的连接类型。不同的连接类型表示 MySQL 如何从表中选择数据。从性能优到劣排序：

1. system

   • 表只有一行（系统表）。这是一个特殊的 const 连接类型，是性能最优的连接类型。

2. const

   • 表最多有一个匹配行，用于主键或唯一索引。因为只有一行匹配，MySQL 可以将该值视为常量。对于 PRIMARY KEY 或 UNIQUE 索引字段进行等值查询时，会使用这种类型。

   EXPLAIN SELECT * FROM employees WHERE id = 1;
   

3. eq_ref

   • 对每个来自前一个表的行组合，从该表读取一行。这是性能次优的连接类型，用于使用唯一索引的所有部分进行等值比较的情况。通常用于带有主键或唯一索引的连接。

   EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.id;
   

4. ref

   • 对于每个来自前一个表的行组合，从该表读取所有匹配的行。这种类型用于非唯一索引或非主键的情况。

   EXPLAIN SELECT * FROM employees WHERE department_id = 1;
   

5. range

   • 检索给定范围的行，使用索引来选择行。常用于范围查询，如使用 <, <=, >, >=, BETWEEN, IN 等操作符的查询。

   EXPLAIN SELECT * FROM employees WHERE id BETWEEN 1 AND 10;
   

6. index

   • 全索引扫描（Index Scan）。这种类型与 ALL 类似，但只遍历索引树。它比 ALL 更快，因为索引文件通常比数据文件小。

   EXPLAIN SELECT * FROM employees ORDER BY last_name;
   

7. ALL

   • 全表扫描（Table Scan）。这是性能最差的连接类型。MySQL 必须扫描整个表才能找到匹配的行。通常这是由于查询没有使用索引，或者优化器认为全表扫描比使用索引更快。

   EXPLAIN SELECT * FROM employees WHERE first_name = 'John';
   

示例及详细解释

假设有一个表 employees，表结构如下：

CREATE TABLE employees (id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,INDEX (department_id)
);

使用不同查询进行 EXPLAIN：

1. const

   EXPLAIN SELECT * FROM employees WHERE id = 1;
   

   • type 是 const，因为 id 是主键，查询只会匹配一行。

2. eq_ref

   EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.id;
   

   • type 是 eq_ref，因为 department_id 是一个索引，并且是连接条件的一部分。

3. ref

   EXPLAIN SELECT * FROM employees WHERE department_id = 1;
   

   • type 是 ref，因为 department_id 是一个非唯一索引。

4. range

   EXPLAIN SELECT * FROM employees WHERE id BETWEEN 1 AND 10;
   

   • type 是 range，因为使用了范围查询。

5. index

   EXPLAIN SELECT * FROM employees ORDER BY last_name;
   

   • type 是 index，因为查询需要按照 last_name 进行排序，而没有其他过滤条件。

6. ALL

   EXPLAIN SELECT * FROM employees WHERE first_name = 'John';
   

   • type 是 ALL，因为 first_name 没有索引，需要全表扫描。