MYSQL explain 执行计划 Type 结合表和sql语句 详细解释

看了很多关于explain执行计划的讲解，结合sql语句和表来讲的比较少，大部分是直接给出定义或者结论，理解的不深刻。下面结合表来讲解下explain的type列各值的区别

表结构

假设我们有一张 products 表，用于存储商品信息：

SQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category_id INT,
  price DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_product_name (product_name),
  INDEX idx_category_id (category_id),
  INDEX idx_price (price)
);

这张表包含以下索引：

• 主键索引： id
• 普通索引： product_name, category_id, price

explain

不同 type 对应的 SQL 示例

1. system

• SQL: EXPLAIN SELECT @@version;
• 解释： 查询 MySQL 版本号，直接从系统表获取，所以 type 为 system。

2. const

• SQL: EXPLAIN SELECT * FROM products WHERE id = 1;
• 解释： 通过主键索引 id 直接定位到唯一的一行记录，因此 type 为 const。

3. eq_ref

• SQL: EXPLAIN SELECT * FROM products INNER JOIN categories ON products.category_id = categories.id;
• 解释： 通过 category_id 索引进行连接，对于 categories 表中的每一行，在 products 表中最多找到一行匹配的记录，所以 type 为 eq_ref。(categories表跟products表的category_id字段关联，categories没有在表结构中展示出来，特此说明)

4. ref

• SQL: EXPLAIN SELECT * FROM products WHERE product_name = 'iPhone 14';
• 解释： 通过 product_name 索引查找，但 product_name 不是唯一索引，可能有多条记录的 product_name 为 'iPhone 14'，所以 type 为 ref。

5. range

• SQL: EXPLAIN SELECT * FROM products WHERE price BETWEEN 1000 AND 2000;
• 解释： 通过 price 索引进行范围查询，找到 price 在 1000 到 2000 之间的记录，所以 type 为 range。

6. index

• SQL: EXPLAIN SELECT product_name, price FROM products ORDER BY price;
• 解释： 虽然没有在 WHERE 子句中使用索引，但 ORDER BY 子句使用了 price 索引，所以 MySQL 会根据 price 索引进行全索引扫描，因此 type 为 index。

7. ALL

• SQL: EXPLAIN SELECT * FROM products WHERE created_at > '2023-11-01' ORDER BY product_name;
• 解释： 如果 created_at 列没有索引，或者 product_name 索引无法满足 ORDER BY 的需求，MySQL 会进行全表扫描，所以 type 为 ALL。

总结

通过上面的例子，我们可以清楚地看到：

• system 类型用于系统表查询或常量访问。
• const 类型用于通过主键或唯一索引直接找到唯一的一行记录。
• eq_ref 类型用于通过索引做等值连接。
• ref 类型用于通过非唯一索引查找。
• range 类型用于索引范围查询。
• index 类型用于全索引扫描。
• ALL 类型用于全表扫描。

优化查询的关键在于尽量让 type 靠前，即使用索引来加速查询。