MySQL 查询性能优化 - EXPLAIN 命令

it2022-05-09 40

查询优化的官方文档在这里。 EXPLAIN 的输出格式译文

1. MySQL 架构

1.1 MySQL 的简化架构

MySQL 可以简单的分为三层：连接层、服务层、存储引擎层。其中服务层包含了 MySQL 的核心组件：

用户连接层 ↓ ↓ ↓ ↓ ↓ ↓ --------------------------------------------------------------------- +------------------+ | MySQL 连接池 | +------------------+ +------------------+-------------+----------+------------------+ | Cache && Buffer | Optimizer | Parser | SQL Interface | +------------------+-------------+----------+------------------+ --------------------------------------------------------------------- ↓ ↓ ↓ ↓ ↓ InnoDB MyISAM Memory Archive Cluster 存储引擎层

各个组件概述如下：

连接池：维护用于用户连接的线程池，避免频繁创建销毁线程导致的开销。存储引擎层：提供标准接口，满足这个接口的存储引擎都可以用于 MySQL。SQL Interface：SQL 接口层，接受 SQL 语句并返回结果Parser：解析器，解析和验证 SQL 语句。Cache && Buffer：缓存，由一系列小缓存组成，比如表缓存、记录缓存、key 缓存、权限缓存等。查询命中缓存时，会直接返回缓存中的数据。Optimizer：优化器，执行 SQL 语句之前会进行优化

1.2 工作流程

到 MySQL 的连接建立后，执行的每一条 SQL 语句的流程如下：

调用 SQL Interface，发生 SQL 语句通过 Parser 解析 SQL 语句，如果发生错误则报错并退出查询缓存。如果在缓存中发现相同的指令，则命中缓存，数据从缓存返回，不再执行后续步骤通过 Optimizer 优化 SQL 语句，最终执行的 SQL 语句可能与你发送的 SQL 语句不一样执行 SQL 语句，结果写入缓存并返回

2. EXPLAIN 概述

EXPLAIN 可以提供 MySQL 语句的执行信息

可以用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 五条语句。显示的信息来自 Optimizer 优化器。

EXPLAIN 命令输出如下：

id

SELECT 标识符，该值为 SELECT 在查询中的顺序号，如果当前行引用的是其他行的联合查询结果，该值将被设置为 NULL。在这种情况下，表中的列将显示一个类似于

select_type

SELECT 的类型，可用类型参考下表。如果该值为 SIMPLE 或者 PRIMARY，一个 JSON 格式的 EXPLAIN 将以一个 query_block 的属性显示 SELECT 的类型。

如果查询有任何复杂的子查询，则最外层标记为 PRIMARY，内层可以是 DERIVED、UNION、UNION RESUlT。

select_type ValueJSON Name说明SIMPLENone简单的 SELECT (没有使用 UNION 和子查询)PRIMARYNone使用 UNION 联合查询和子查询时，最外层的 SELECTUNIONNone在 UNION 查询中的第二个或者之后的 SELECT 语句，如果是 UNION 位于 FROM 中则标记为 DERIVEDDEPENDENT UNIONdependent (true)在 UNION 查询中的第二个或者之后的 SELECT 语句，依赖外层的查询UNION RESULTunion_result联合查询的结果SUBQUERYNone在子查询中的第一个 SELECTDEPENDENT SUBQUERYdependent (true)在 subquery 子查询中的第一个 SELECT，依赖于外层查询DERIVEDNone派生表 SELECT(由 FROM 中的 subquery 子查询引起的临时表)MATERIALIZEDmaterialized_from_subquery物化子查询UNCACHEABLE SUBQUERYcacheable (false)子查询的结果不能被缓存，必须重新评估外部查询的每一行UNCACHEABLE UNIONcacheable (false)在 UNION 联合查询中的第二个或者之后的多个 SELECT 都不可缓存的子查询

依赖通常意味着使用了相关子查询。

子查询的分类及评估：

相关子查询：只重新评估一次外部上下文环境的每一组变量的不同值不可缓存的子查询：重新评估外部上下文环境的每一行数据。

子查询的缓存不同与 query cache 中查询结果的缓存：

子查询缓存：发生在查询执行期间query cache：用来存储查询执行完成后的结果。

table

输出的行引用了哪个表，除了真实表名外，还有临时表的表名。临时表没有任何索引。临时表的表名可以是下列值之一：

<union M,N>：有 UNION RESULT 的时候，表名是这个形式。该行指的是 ID 值为 M 和 N 的行的联合。<derivedN>：FROM 中有 SUBQUERY 子查询的时候，表名是这个形式。N 指向子查询对应的行。<subqueryN>：该行指的是一个物化子查询的结果，该结果为 ID 值为 N 的行。

type

数据访问/读取操作的类型，可用值有：ALL、index、range、ref、eq_ref、const/system、NULL。

这是重要指标，type 的结果值从好到坏依次是：

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

查询至少要达到 range 级别，最好能达到 ref。

All

全表扫描（速度慢，磁盘 I/O 高）。可以增加索引来避免全表扫描。

对于非索引字段，查询时触发全表扫描（尽量避免）：

index

覆盖索引，所需数据可以直接从索引中获取。与 All 全表扫描类似，但是只扫描索引，所以速度较快。当查询的几个字段刚好都在同一个索引中时，会使用使用覆盖索引，Extra 列对应 Using index。

查询字段被索引包含及超出索引的区别：

range

部分索引扫描。key 列显示使用的索引。当指定了索引且对索引使用 =、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN AND 或 IN (a, b)范围限定操作符时，会使用 range。

对于主键 id 使用范围限定操作符，只读取 id 字段时，触发 range 部分索引扫描：

对于主键 id 使用范围限定操作符，读取索引之外的字段时，触发 range 部分索引扫描。如果只读取索引之内的字段，则触发 index 覆盖索引：

ref

通过索引访问数据。只有当使用非唯一性索引（PRIMARY 和 UNIQUE 之外的索引）或唯一性索引的非唯一性前缀时才会发生。

这个类型跟 eq_ref 不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是 UNIQUE 和 PRIMARY KEY。ref 可以用于使用 = 或 <=> 操作符的带索引的列。对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。

将 WHERE 用的字段添加到索引后，触发 ref 索引访问：

eq_ref

最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生。对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

const

确定最多只有一行匹配时，MySQL 优化器会在查询开始时将这一行数据读入内存（只读一次），这一行的所有字段值被优化器剩余部分当做常量处理。const 用于用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分时。

对于 PRIMARY KEY 主键或 UNIQUE 索引字段，因为其唯一性，查询效率特别高：

注意，匹配到 const 表后，不能在这个表上再次进行匹配了：

system

要匹配的表仅有一行数据。这是 const 连接类型的一种特例，

例如下面的子查询，对于内层查询，通过 PRIMARY KEY 主键返回唯一一条数据，对于外层查询，type 就是 system 类型：

NULL

mysql 在优化 SQL 语句后，分解的某个查询语句命中缓存或使用临时表，不需要访问数据或索引。

possible_keys

possible_keys 中列出本次查询覆盖的所有索引，这些都可能被 MySQL 选择使用。如果该列的值是 NULL，说明查询语句没有覆盖到索引列，通常会发生全表扫描。可以检查 WHERE 子句是否引用了包含索引的列。

可能有多个可用索引，但可能 MySQL 一个都不会用：

使用 SHOW INDEX FROM tbl_name 查看表中已有的索引：

key

MySQL 实际使用的索引。如果 key 值为 NULL，表明 MySQL 没有找到执行高效查询的索引。MySQL 一般会通过 possible_keys 中的某个索引查找行，并将那个索引列在 key 列中；但是也可以使用不包含在 possible_keys 列中的索引。

可以在查询语句中使用 FORCE INDEX、USE INDEX、IGNORE INDEX，强制 MySQL 使用或者忽略 possible_keys 列中的索引。

key_len

MySQL 决定使用的 key 的长度。key_len 值能够使你判定 MySQL 实际使用了 multiple-part key 中的多少个 part。如果 key 列的值为 NULL，则 key_len 列值也为 NULL。在不损失精确性的情况下，长度越短越好。

ref

ref 列显示列或常量，这些列或者常量与 key 列中的索引进行比较，以便从表中选择行。

rows

rows 是 MySQL 预估的在执行查询时必须扫描的行数。把所有 rows 列值相乘，可粗略估算整个查询会检查的行数。

对于 InnoDB 类型的表，这个数字只是一个估计，并不总是精确的。

Extra

额外信息，是查询过程的重要补充信息。有几十种可能的输出，完整资料参考官网的 EXPLAIN Extra Information 部分。

Using filesort

MySQL 有两种方式生成有序结果：

通过排序操作（ORDER BY 或 GROUP BY）。此时 Extra 中出现“Using filesort”，文件排序。按索引顺序扫描，通常速度更快。如果 EXPLAIN 出来的 type 列的值为“index”，则说明 MySQL 使用了索引扫描来排序。不要和 Extra 列的“Using index”（覆盖索引，只访问索引即可返回所查询的数据）搞混。

扫描索引本身是很快的，只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，就不得不每扫描一条索引记录就回表查询一次对应的行，这基本上都是随机 I/O。按索引顺序读数据通常比顺序地全表扫描速度慢，尤其是 I/O 密集型工作负载。

例如，对于 MySQL 的示例数据库 Sakila，rental 表中的 rental_date 字段是有索引的，return_date 字段是没有索引的，这两个字段区别如下：

Using temporary

用临时表保存中间结果，常用于 GROUP BY 和 ORDER BY 操作中。尽量避免硬盘临时表的使用。

Not exists

MySQL 能够对查询执行 LEFT JOIN 优化，并且在找到与 LEFT JOIN 条件相匹配的一行后，不会在该表中检查更多行。以下是可以用这种方式进行优化的查询类型的示例：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

Using index

发生覆盖索引，查询的字段完全落入索引，从索引文件中即可获得所需信息，不需要读数据文件。

Using where

Extra 列出现“Using where”表示 MySQL 服务器在存储引擎返回行后再应用 WHERE 过滤条件。对于带 WHERE 的 SQL 语句，可能会在存储引擎返回行之前或之后应用 WHERE 过滤条件。

Impossible where

表示 WHERE 条件总是 FALSE，不能用来获取任何数据。

转载于:https://www.cnblogs.com/kika/p/10851622.html

相关资源：数据结构—成绩单生成器

专利

最新回复(0)