mysql 数据库执行计划之type字段连连看, 根据数据库计划优化

拼搏现实的明天。 2023-10-10 17:03 6阅读 0赞

#### 数据库优化之执行计划 type 为何物 ⚓ ####

*   *  mysql 数据库如何查看执行计划
     *  执行计划是什么
     *  SQL 性能优化的目标以及执行计划中的 type 字段含义
     *   *  MySQL 执行计划中 type 字段
         *  解释这些索引类型
         *   *  为什么 index 类型的索引比range类型的慢?
     *  一个SQL是如何优化的

> 好脾气都是磨出来的，坏毛病都是惯出来的，治的了你脾气的是你爱的人，受的了你脾气的是爱你的人

### mysql 数据库如何查看执行计划 ###

在 MySQL 中，可以使用 `EXPLAIN` 关键字来查看查询的执行计划，以便分析查询语句的性能和优化方案。`EXPLAIN` 关键字可以在查询语句前加上，从而返回查询的执行计划。执行计划是一个查询执行的详细信息列表，包括了查询的每一步操作、操作所使用的索引、查询所涉及的行数和查询所用的时间等信息。

下面是一个示例 SQL 语句，演示如何使用 `EXPLAIN` 关键字来查看查询的执行计划：

EXPLAIN SELECT * FROM my_table WHERE age > 18;

执行结果：

在上面的示例中，我们使用 `EXPLAIN` 关键字来查看查询 `my_table` 表中所有年龄大于 18 岁的记录的执行计划。执行计划的内容包括了查询所用的索引、查询所涉及的行数、查询所用的时间等信息。

需要注意的是，执行计划中的每个操作都会有一个 `type` 字段，它表示 MySQL 执行查询时的访问类型，常见的值包括 `ALL`、`index`、`range`、`ref`、`eq_ref`、`const` 等，这些访问类型的代价从大到小排列为 `ALL`、`index`、`range`、`ref`、`eq_ref`、`const`，其中 `const` 代表在查询中使用了常量，代价最小。如果查询中存在 `type` 字段为 `ALL` 的操作，则说明查询需要执行全表扫描，性能可能会受到影响，需要考虑优化查询语句或者添加索引来提高查询效率。

除了 `EXPLAIN` 关键字之外，还可以使用 `EXPLAIN EXTENDED` 和 `EXPLAIN PARTITIONS` 关键字来获取更详细的执行计划信息。`EXPLAIN EXTENDED` 关键字会显示查询语句的执行计划和语句的执行状态，而 `EXPLAIN PARTITIONS` 关键字会显示查询语句的执行计划和表的分区信息。

### 执行计划是什么 ###

MySQL 的执行计划是指在执行查询语句时，MySQL 数据库系统通过分析查询语句和相关的数据表结构信息，生成一种查询执行方案，并将其保存在数据库内部。执行计划包含了查询执行的详细信息，包括了查询的每一步操作、操作所使用的索引、查询所涉及的行数和查询所用的时间等信息。执行计划的生成过程称为查询优化器（Query Optimizer）。

在 MySQL 中，可以使用 EXPLAIN 关键字来查看查询的执行计划。执行 EXPLAIN 查询时，MySQL 将输出一个表格，其中包含了查询的详细执行信息。表格的各个字段表示的含义如下：

1.  id：表示查询的唯一标识符。在复杂的查询语句中，可能会存在多个查询标识符，它们之间通过子查询的方式进行连接。
2.  select\_type：表示查询的类型，常见的有 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT 等。不同的查询类型对应了不同的查询策略。
3.  table：表示查询操作涉及的数据表名。
4.  partitions：表示查询操作涉及的数据表分区信息。
5.  type：表示查询所使用的索引的类型，包括了 ALL、index、range、ref、eq\_ref、const 等。不同的索引类型对应了不同的查询策略。
6.  possible\_keys：表示查询操作可能使用到的索引列表。
7.  key：表示实际使用的索引。
8.  key\_len：表示使用的索引字段长度。
9.  ref：表示使用的索引字段对应的值来源。
10. rows：表示查询所涉及的数据行数。
11. filtered：表示查询返回的数据行占总数据行数的比例，值越小表示查询效率越高。
12. Extra：表示查询操作的一些额外信息，包括了 Using filesort、Using index、Using temporary 等。

需要注意的是，查询的执行计划是查询优化器根据查询语句和数据表结构等信息生成的，它可能受到许多因素的影响，包括了查询语句的复杂度、数据表的大小和索引的使用情况等。因此，在优化查询性能时，需要结合具体的业务场景和数据特征来进行分析和优化。

其实我们最关心的就是执行计划的type类型, 也就是索引的类型了

### SQL 性能优化的目标以及执行计划中的 type 字段含义 ###

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts  
最好。  
说明：  
1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。  
2）ref 指的是使用普通的索引（normal index）。  
3）range 对索引进行范围检索。  
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级  
别比较 range 还低，与全表扫描是小巫见大巫。

--------------------

SQL 性能优化的目标是尽可能达到较高的执行计划级别，比如达到 range 级别，进一步优化则可达到 ref 级别，最好的情况是达到 consts 级别。这里的级别指的是执行计划中的 type 字段，type 字段的值越小，执行查询所需的资源越少，查询效率越高。

执行计划中的 type 字段表示查询所使用的索引类型，包括 consts、eq\_ref、ref、range、index、all 等。其中，consts 表示单表中最多只有一个匹配行（主键或唯一索引），在优化阶段即可读取到数据，查询效率最高；eq\_ref 表示多表关联时使用主键或唯一索引查询，查询效率次之；ref 表示普通索引查询，查询效率再次之；range 表示对索引进行范围检索；index 表示全索引扫描；all 表示全表扫描。其中，range 级别已经是比较理想的优化目标，如果能够进一步优化到 ref 级别甚至 consts 级别，查询效率会更高。

最后，还提到了一个反例，即当执行计划中 type 字段的值为 index 时，表示索引物理文件全扫描，速度非常慢，这个索引级别比 range 级别还低，实际上不如直接进行全表扫描。

#### MySQL 执行计划中 type 字段 ####

在MySQL中，执行计划的类型（type）表示了查询操作的性能。类型从好到差的顺序如下：

system > const > eq_ref > ref > fulltext > 
    ref_or_null > index_merge > 
    unique_subquery > index_subquery > 
    range > index > all

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>system</code></td> 
   <td>查询系统表或者 const 连接的查询，仅返回一行，效率最高</td> 
  </tr> 
  <tr> 
   <td><code>consts</code></td> 
   <td>单表中最多只有一个匹配行（主键或唯一索引），效率最高</td> 
  </tr> 
  <tr> 
   <td><code>eq_ref</code></td> 
   <td>多表关联查询，使用主键或唯一索引查询，效率较高</td> 
  </tr> 
  <tr> 
   <td><code>ref</code></td> 
   <td>使用普通索引进行查询，效率一般</td> 
  </tr> 
  <tr> 
   <td><code>fulltext</code></td> 
   <td>全文索引中进行查询</td> 
  </tr> 
  <tr> 
   <td><code>range</code></td> 
   <td>对索引进行范围查询，效率较高</td> 
  </tr> 
  <tr> 
   <td><code>index</code></td> 
   <td>全索引扫描，效率低，不如全表扫描</td> 
  </tr> 
  <tr> 
   <td><code>all</code></td> 
   <td>全表扫描，效率最低</td> 
  </tr> 
  <tr> 
   <td><code>NULL</code></td> 
   <td>没有使用索引或无法使用索引</td> 
  </tr> 
 </tbody> 
</table>

需要注意的是，以上类型及其含义是一份比较常见的类型总结，实际上在执行计划中可能会出现其他的类型，具体要根据实际情况来分析和判断。

#### 解释这些索引类型 ####

我将创建一个简单的表结构，并逐一解释这些索引类型。以下是示例表结构：

CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        department_id INT NOT NULL,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        salary DECIMAL(10, 2) NOT NULL,
        hire_date DATE NOT NULL,
        INDEX idx_department_id (department_id),
        INDEX idx_last_name (last_name)
    );

接下来，我将逐一解释这些索引类型以及对应的SQL查询：

1.  system: 当查询仅涉及到一行时，会出现system类型，例如只有一行数据的表。

SELECT * FROM employees LIMIT 1;

1.  const: 表示常量，查询仅涉及到主键或唯一索引的一行时，会出现const类型。

SELECT * FROM employees WHERE id = 1;

1.  eq\_ref: 当使用唯一索引或主键作为连接条件时，会出现eq\_ref类型。

CREATE TABLE departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30) NOT NULL
    );
    
    SELECT e.*, d.name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;

1.  ref: 当通过非唯一索引进行连接或查询时，会出现ref类型。

SELECT * FROM employees WHERE department_id = 1;

1.  range: 当使用范围条件进行查询时，会出现range类型。

首先，为 hire\_date 添加一个索引：

ALTER TABLE employees ADD INDEX idx_hire_date (hire_date);
    SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

1.  index: 当查询中使用到了全索引扫描时，会出现index类型。

SELECT last_name FROM employees ORDER BY last_name;

全索引扫描（index）是一种比全表扫描（all）更高效的查询方式，因为它仅扫描索引，而不是整个表。

在这个例子中，由于 last\_name 列有索引，MySQL 可以直接从索引中获取数据并按照索引的顺序进行排序。这种情况下，索引扫描（index）比全表扫描（all）更高效，因为它只需要扫描索引而不是整个表。

在实际应用中，全索引扫描比全表扫描要快，因为索引通常比表小，占用的磁盘空间更少，因此扫描所需的I/O操作更少。

请注意，虽然在这种情况下使用了索引，但在某些场景下（例如，大量数据需要过滤或排序），全索引扫描可能仍然会导致性能下降。在这种情况下，可以考虑其他优化策略，如使用覆盖索引、限制结果集大小等。

1.  all: 当查询需要全表扫描时，会出现all类型。

SELECT * FROM employees WHERE salary > 5000;

1.  NULL: 当执行计划中没有使用索引时，会出现NULL类型。

SELECT COUNT(*) FROM employees;

这些例子展示了如何通过不同的SQL查询触发不同的索引类型。在实际情况下，理解这些类型的含义并根据需求选择合适的索引策略，可以帮助提高查询性能。

##### 为什么 index 类型的索引比range类型的慢? #####

`index` 类型要比 `range` 类型慢。原因是 `index` 类型表示全索引扫描，而 `range` 类型表示基于索引的范围扫描。让我们分析一下这两者的区别和性能影响。

**全索引扫描（index）：**

全索引扫描会扫描整个索引（或索引的一部分），而不是对整个表进行扫描。虽然这比全表扫描更有效，但它仍然需要检查索引中的所有记录。在某些情况下，这可能导致较慢的查询性能，尤其是在需要处理大量数据的情况下。

**基于索引的范围扫描（range）：**

基于索引的范围扫描则是在索引上进行有限范围的查找。在这种情况下，数据库仅需要检查索引中满足特定条件的记录，而不是整个索引。这使得范围扫描比全索引扫描更高效，因为它只需要检查符合条件的记录，而不是全部记录。

总之，`range` 类型比 `index` 类型快，因为它只需要检查索引中满足条件的记录，而不是整个索引。然而，在实际应用中，查询性能还取决于其他因素，如索引选择、表的大小和复杂性等。因此，根据具体情况选择合适的索引策略是关键。

### 一个SQL是如何优化的 ###

通过一个SQL一步一步帮我优化, 从性能差的type 类型 all开始, 一步一步优化到system

假设我们有以下表结构：

CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        department_id INT NOT NULL,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        salary DECIMAL(10, 2) NOT NULL,
        hire_date DATE NOT NULL
    );
    
    CREATE TABLE departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30) NOT NULL
    );

我们要查询特定部门的员工信息。下面是一个未优化的SQL查询，该查询将导致全表扫描（all）：

SELECT e.*
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE d.name = 'Engineering';

**优化1：创建索引（从all到index或range）**

为了优化查询，我们首先需要为 `departments` 表的 `name` 列创建索引，这样可以避免全表扫描：

ALTER TABLE departments ADD INDEX idx_name (name);

现在，查询将使用基于索引的范围扫描（range）或全索引扫描（index），取决于实际数据分布和优化器的决策。

**优化2：使用主键或唯一索引（从index或range到ref或eq\_ref）**

如果 `departments` 表中的 `name` 列具有唯一性约束，我们可以将其更改为唯一索引以提高性能：

ALTER TABLE departments DROP INDEX idx_name;
    ALTER TABLE departments ADD UNIQUE INDEX uniq_name (name);

现在，查询将使用非唯一索引扫描（ref）或唯一索引扫描（eq\_ref），这取决于优化器的决策。这是因为优化器知道 `name` 列是唯一的，所以它只需要查找一个匹配的行。

**优化3：减少返回的数据量（从ref或eq\_ref到const或system）**

最后一步是减少查询返回的数据量。例如，如果我们只关心员工的 `id` 和 `last_name`，我们可以修改查询以仅返回这些列：