MySql Explain怎么执行

目录

1. 引言
2. 什么是EXPLN
3. EXPLN的基本语法
4. EXPLN的输出列
5. EXPLN的输出类型
6. EXPLN的使用场景
7. EXPLN的优化建议
8. EXPLN的局限性
9. 总结

引言

在数据库优化过程中，理解SQL查询的执行计划是至关重要的。MySQL提供了EXPLN命令，用于分析SQL查询的执行计划。通过EXPLN，我们可以了解MySQL如何执行查询，从而找出潜在的性能瓶颈并进行优化。本文将详细介绍EXPLN的使用方法、输出列的含义、使用场景以及优化建议。

什么是EXPLN

EXPLN是MySQL提供的一个命令，用于显示MySQL如何执行SQL查询。它可以帮助我们理解查询的执行计划，包括表的读取顺序、使用的索引、连接类型等信息。通过分析EXPLN的输出，我们可以找出查询中的性能瓶颈，并进行相应的优化。

EXPLN的基本语法

EXPLN的基本语法如下：

EXPLN SELECT * FROM table_name WHERE condition;

EXPLN可以用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。执行EXPLN后，MySQL会返回一个结果集，其中包含了查询的执行计划信息。

EXPLN的输出列

EXPLN的输出结果包含多个列，每列都提供了关于查询执行计划的不同信息。以下是EXPLN输出列的含义：

1. id: 查询的标识符。如果查询包含子查询或联合查询，每个子查询或联合查询都会有一个唯一的id。
2. select_type: 查询的类型。常见的类型包括：
   • SIMPLE: 简单的SELECT查询，不包含子查询或联合查询。
   • PRIMARY: 最外层的SELECT查询。
   • SUBQUERY: 子查询中的第一个SELECT。
   • DERIVED: 派生表（FROM子句中的子查询）。
   • UNION: UNION中的第二个或后续的SELECT查询。
   • UNION RESULT: UNION的结果。
3. table: 查询涉及的表名。
4. partitions: 查询涉及的分区。
5. type: 表的访问类型。常见的类型包括：
   • system: 表只有一行记录（系统表）。
   • const: 表最多有一个匹配行，通常用于主键或唯一索引的查询。
   • eq_ref: 对于每个来自前表的行组合，从该表中读取一行。通常用于主键或唯一索引的关联查询。
   • ref: 对于每个来自前表的行组合，从该表中读取所有匹配的行。通常用于非唯一索引的查询。
   • range: 只检索给定范围内的行，通常用于范围查询（如BETWEEN、IN等）。
   • index: 全索引扫描，通常用于覆盖索引查询。
   • ALL: 全表扫描，通常是最慢的访问类型。
6. possible_keys: 查询可能使用的索引。
7. key: 查询实际使用的索引。
8. key_len: 使用的索引的长度。
9. ref: 显示索引的哪一列被使用了。
10. rows: 估计需要扫描的行数。
11. filtered: 表示查询条件过滤后的行数百分比。
12. Extra: 额外的信息。常见的值包括：
    • Using where: 使用了WHERE条件过滤。
    • Using index: 使用了覆盖索引。
    • Using temporary: 使用了临时表。
    • Using filesort: 使用了文件排序。

EXPLN的输出类型

EXPLN的输出类型（type列）是理解查询执行计划的关键。以下是一些常见的输出类型及其含义：

1. system: 表只有一行记录（系统表）。这是最快的访问类型。
2. const: 表最多有一个匹配行，通常用于主键或唯一索引的查询。MySQL会将查询优化为常量查询。
3. eq_ref: 对于每个来自前表的行组合，从该表中读取一行。通常用于主键或唯一索引的关联查询。
4. ref: 对于每个来自前表的行组合，从该表中读取所有匹配的行。通常用于非唯一索引的查询。
5. range: 只检索给定范围内的行，通常用于范围查询（如BETWEEN、IN等）。
6. index: 全索引扫描，通常用于覆盖索引查询。
7. ALL: 全表扫描，通常是最慢的访问类型。

EXPLN的使用场景

EXPLN可以用于多种场景，帮助我们分析和优化SQL查询。以下是一些常见的使用场景：

1. 查询性能分析: 通过EXPLN，我们可以了解查询的执行计划，找出潜在的性能瓶颈。例如，如果type列显示为ALL，说明查询进行了全表扫描，可能需要添加索引来优化查询。
2. 索引优化: EXPLN可以帮助我们确定查询是否使用了索引，以及使用的索引是否合适。如果possible_keys列显示有多个可能的索引，但key列显示为NULL，说明查询没有使用任何索引，可能需要添加或调整索引。
3. 子查询优化: 对于包含子查询的复杂查询，EXPLN可以帮助我们理解子查询的执行顺序和访问类型，从而进行优化。
4. 连接优化: 对于多表连接查询，EXPLN可以帮助我们理解表的连接顺序和连接类型，从而优化连接查询。

EXPLN的优化建议

通过分析EXPLN的输出，我们可以得出一些优化建议。以下是一些常见的优化建议：

1. 添加索引: 如果type列显示为ALL，说明查询进行了全表扫描，可能需要添加索引来优化查询。可以通过possible_keys和key列来确定是否需要添加索引。
2. 优化查询条件: 如果rows列显示需要扫描的行数过多，可能需要优化查询条件，减少扫描的行数。
3. 避免使用临时表和文件排序: 如果Extra列显示Using temporary或Using filesort，说明查询使用了临时表或文件排序，可能需要优化查询以避免这些操作。
4. 使用覆盖索引: 如果Extra列显示Using index，说明查询使用了覆盖索引，可以避免回表操作，提高查询性能。
5. 优化连接顺序: 对于多表连接查询，可以通过调整连接顺序来优化查询性能。

EXPLN的局限性

虽然EXPLN是一个强大的工具，但它也有一些局限性。以下是一些常见的局限性：

1. 不显示实际执行时间: EXPLN只显示查询的执行计划，不显示实际的执行时间。要获取实际的执行时间，可以使用EXPLN ANALYZE（在MySQL 8.0.18及以上版本中支持）。
2. 不显示锁信息: EXPLN不显示查询的锁信息。要获取锁信息，可以使用SHOW ENGINE INNODB STATUS。
3. 不显示查询的IO和CPU消耗: EXPLN不显示查询的IO和CPU消耗。要获取这些信息，可以使用性能模式（Performance Schema）。
4. 不显示查询的缓存命中率: EXPLN不显示查询的缓存命中率。要获取缓存命中率，可以使用SHOW STATUS。

总结

EXPLN是MySQL中一个非常有用的工具，可以帮助我们理解SQL查询的执行计划，找出潜在的性能瓶颈，并进行优化。通过分析EXPLN的输出，我们可以确定查询是否使用了索引、是否需要优化查询条件、是否需要避免使用临时表和文件排序等。虽然EXPLN有一些局限性，但它仍然是数据库优化过程中不可或缺的工具。希望本文能帮助你更好地理解和使用EXPLN，从而提高数据库查询的性能。