MySQL- EXPLAIN命令的type列

在 MySQL 中，EXPLAIN 命令用于展示查询的执行计划，展示 MySQL 如何执行某条查询语句。EXPLAIN 输出的 type 列描述了 MySQL 使用的查询访问类型，代表了 MySQL 如何访问数据表中的行。这些访问类型按效率从低到高排列分别是：ALL、index、range、ref、eq_ref、const 等。

下面分别举例说明每一种访问类型，并解释它们在 EXPLAIN 输出中的含义。

1. ALL（全表扫描）

1.1 解释

• ALL 表示全表扫描，即 MySQL 会扫描表中的所有行来查找匹配的记录。
• 这是效率最低的查询类型，通常意味着没有使用索引或索引未能优化查询。

1.2 示例

假设有一个表 employees：

CREATE TABLE employees (emp_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary DECIMAL(10, 2)
);

以下查询会触发全表扫描：

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

• 原因：salary 列没有索引，因此 MySQL 需要扫描表中的所有行来查找 salary > 50000 的记录。
• type：ALL 表示 MySQL 将进行全表扫描。

2. index（全索引扫描）

2.1 解释

• index 表示 MySQL 会扫描整个索引来满足查询条件。与全表扫描不同的是，index 只扫描索引树，而不读取表的行数据。
• 这是比全表扫描稍微好一点的访问方式，但仍然效率不高，通常用于没有合适的 WHERE 子句条件时。

2.2 示例

假设在 employees 表上有一个索引：

CREATE INDEX idx_salary ON employees(salary);

以下查询会触发全索引扫描：

EXPLAIN SELECT salary FROM employees ORDER BY salary;

• 原因：查询需要对 salary 列进行排序，MySQL 可以通过扫描 idx_salary 索引来获取排序后的数据，但它需要扫描索引的所有条目。
• type：index 表示 MySQL 将扫描整个索引。

3. range（索引范围扫描）

3.1 解释

• range 表示 MySQL 会使用索引扫描特定范围内的行，而不是扫描整个索引或表。
• 这种访问方式通常用于查询使用了索引的比较操作，如 <, <=, >, >=, BETWEEN, IN 等。

3.2 示例

假设有如下索引：

CREATE INDEX idx_salary ON employees(salary);

以下查询会触发索引范围扫描：

EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

• 原因：salary 列有索引，并且查询使用了范围条件 BETWEEN，MySQL 可以利用 idx_salary 索引扫描指定范围内的行。
• type：range 表示 MySQL 将使用索引范围扫描。

4. ref（非唯一索引扫描）

4.1 解释

• ref 表示 MySQL 将通过非唯一索引扫描找到匹配的行，或者通过索引列与非唯一列的比较来查找匹配的行。
• 这种访问方式用于使用非唯一索引或前缀索引的等值查询。

4.2 示例

假设有如下索引：

CREATE INDEX idx_department ON employees(department_id);

以下查询会触发非唯一索引扫描：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

• 原因：department_id 列有索引，但该索引不是唯一索引。因此，MySQL 会使用 ref 访问方法扫描索引中所有 department_id 为 5 的行。
• type：ref 表示 MySQL 将通过非唯一索引扫描查找匹配的行。

5. eq_ref（唯一索引扫描）

5.1 解释

• eq_ref 是使用主键或唯一索引时产生的访问方式，通常使用在多表联査中。比如，对两张表进行联查，关联条件是两张表的 user id 相等，且 user id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

5.2 示例

假设 employees 表的 emp_id 是主键：

CREATE TABLE employees (emp_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary DECIMAL(10, 2)
);

以下查询会触发唯一索引扫描：

EXPLAIN SELECT * FROM employees e, dep d WHERE e.department_id = d.depart_id;

• type：eq_ref 表示 MySQL 通过唯一索引查找匹配的行。

6. const（常量）

6.1 解释

• const 表示查询的结果集是常量值，也就是说，对于某些查询，MySQL 只需要访问一次索引就能获取结果，因为它已经确定结果集只包含一条记录。
• 通常用于查询条件是主键或唯一索引的等值匹配，这种情况下结果集至多只有一行。

6.2 示例

继续使用 employees 表：

EXPLAIN SELECT * FROM employees WHERE emp_id = 1;

• 原因：emp_id 是主键，查询条件是对主键列的等值匹配。由于主键的唯一性，MySQL 确定最多返回一条记录，因此结果集是常量。
• type：const 表示 MySQL 通过主键快速定位到唯一的一条记录。

7. 总结

• ALL：全表扫描，最不推荐，因为它扫描了表中的所有行。
• index：全索引扫描，扫描了整个索引，效率比全表扫描稍高。
• range：索引范围扫描，扫描索引中的一个范围，性能较好。
• ref：非唯一索引扫描，使用非唯一索引来查找匹配的行。
• eq_ref：唯一索引扫描，使用唯一索引或主键查找匹配的行，效率非常高。
• const：查询结果是常量，表示最多有一条记录，效率最高。

通过理解这些访问类型及其对应的查询场景，可以帮助我们优化 MySQL 查询，提高数据库性能。