MySQL执行计划各参数比较tips

来源：百家汽车网

看什么看，还不快进来学习！！！

下面我们一一解释这些参数的含义以及通过他们的值如何进行SQL的优化判定

一、id

在 MySQL 的 Explain 执行计划中，每个执行计划节点的 ID 代表着查询语句中涉及的表的执行顺序。当多个表参与查询时，MySQL 会根据查询优化器的规则来确定表的连接顺序，并为每个表分配一个唯一的 id 值。较小的id值在执行计划中先进行处理，较大的id值表示稍后处理，都是1代表从上到下执行。

二、select_type

select_type 列代表了每个表的访问类型，用于表示查询语句中涉及到的表的类型和查询方式。

其中，select_type 列的值有以下几种可能：

SIMPLE：表示查询语句中只包含了一个简单的表，没有使用 UNION 或子查询等复杂的查询方式。
PRIMARY：表示查询语句中包含多个表，但是查询优化器将该表作为主查询（即外层查询）进行处理。
SUBQUERY：表示查询语句中使用了子查询，子查询通常会在主查询之前先执行，并将结果传递给主查询。
DERIVED：表示查询语句中使用了派生表（Derived Table），也就是将查询结果保存在临时表中，并在查询中引用该临时表。
UNION：表示查询语句中使用了 UNION 操作符，将多个查询结果合并成一个结果集。
UNION RESULT：表示查询语句中使用了 UNION 操作符后得到的结果集。
DEPENDENT UNION：表示查询语句中使用了 UNION 操作符，并且依赖于外部查询的结果。
DEPENDENT SUBQUERY：表示查询语句中使用了子查询，并且依赖于外部查询的结果。
MATERIALIZED：表示查询语句中使用了 Materialized subquery，即对于子查询的结果集进行了优化，将其存储在一个临时表中以提高性能。

三、table

顾名思义啊，就是表的名称或者别名，假如你给student表设置别名为A表，那么table是A代表student表。这里我有4个表，都是别名，分别是t表、tl表、p表、w表。

四、partitions

partitions 列在 EXPLAIN 执行计划中显示了查询语句所涉及的分区信息。如果表没有使用分区，则此列将显示为 NULL。如果表使用了分区，那么 partitions 列将会显示被查询的分区数目。当查询涉及到多个分区时，这个值可能会大于 1。

分区是在 MySQL 中一种用于将大型表分割成更小、更易管理的数据块的技术。通过对表的分区，可以将表数据存储到不同的物理位置上，从而提高查询性能、减少维护和备份的时间。

MySQL 支持多种分区类型，包括 RANGE、LIST、HASH 和 KEY 等。每种分区类型都有其适用场景和实现方式。

RANGE 分区：按照给定的范围对数据进行分区，例如按照日期范围、数字范围等。
LIST 分区：按照列表对数据进行分区，例如按照省市、地区、部门等。
HASH 分区：使用哈希算法将数据均匀地分布到多个分区中。
KEY 分区：类似于 HASH 分区，但是是基于表的某个列或多个列进行分区的。

在创建分区表时，需要指定分区键（PARTITION BY 子句）和分区数目（PARTITIONS 子句）。分区键是用于决定数据如何分配到各个分区中的依据，可以是表的任意一个列或多个列的组合。分区数目决定了表被分割成多少个数据块。

使用分区能够提高大型表的查询效率，使得查询只需要扫描和处理必要的分区，而不是整个表。同时，对于大型表的备份和维护操作也更加方便，可以只备份或维护需要的分区，而不是整个表。

这样说可能有点抽象，下面举一个栗子...

假设我们有一个存储大量订单数据的表，我们可以使用日期范围作为分区键，将订单数据按照日期进行分区。

首先，创建一个具有分区的订单表，例如 orders：
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    total_amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2012),
    PARTITION p3 VALUES LESS THAN (2013),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
上述代码创建了一个名为 orders 的表，并以 order_date 列的年份作为分区键。根据不同的年份范围，我们创建了五个分区 p0、p1、p2、p3 和 p4。

然后，我们可以向这个表中插入订单数据：
INSERT INTO orders (order_id, order_date, customer_id, total_amount)
VALUES
    (1, '2010-01-10', 1001, 100.50),
    (2, '2011-05-15', 1002, 75.20),
    (3, '2012-09-20', 1003, 200.00),
    (4, '2013-03-05', 1004, 150.80),
    ...
当执行查询时，MySQL 会根据查询条件和分区键来确定只扫描相关的分区，而不是整个表。例如，如果我们只查询 2012 年的订单：

MySQL 只会扫描 p2 分区，从而提高查询性能。
SELECT * FROM orders WHERE YEAR(order_date) = 2012;

五、type

type 列表示查询使用的访问方法。在这里ref 是 type 的一种可能取值，表示使用了非唯一索引进行访问。除了 ref，常见的 type 值还包括：

ALL：全表扫描，需要遍历整个表来找到匹配的行，效率较低。
index：全索引扫描，遍历整个索引树来找到匹配的行，通常比全表扫描快。
range：范围扫描，使用索引的一部分进行范围查找。
const：常量查找，根据主键或唯一索引进行精确查找。
system：系统表的查询，例如 EXPLAIN 查询。
index_merge:是 MySQL 查询执行计划中的一种 type 值，表示使用了多个索引进行合并访问。
eq_ref : 表示使用等值连接进行索引访问。当查询通过一个唯一索引或主键与另一个表进行连接时，MySQL 可以使用 eq_ref 访问方法。

一般的优化效果的顺序从高到低如下(主要还是这句有用)：
system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
具体还是要根据表的大小，数据的分布，以及查询的复杂度等多个因素判定。

六、possible_keys

这列显示了可能被查询优化器考虑使用的索引列表。这些索引是根据查询条件和表结构进行推断的，表示能够提高查询性能的潜在索引选项。

如下图所示，t表可能会使用到的索引字段有idx_db_tag，w表可能使用到line_id索引，tl和p表没有可能使用到的索引。这时可以根据sql语句来酌情优化，查看是否能够走索引，能尽量走索引就尽量走索引，速度会快得多得多得多得多得多得多得多............

七、key

key 列显示了实际被查询优化器选中的索引。它表示在执行查询时使用的索引名称。当查询执行计划中的 type 列为 ALL、index 或 range 时，key 列将为空，表示没有使用索引。这意味着查询将执行全表扫描或全索引扫描，可能导致性能较差。

但是，当 type 列为 ref、eq_ref、const 或 system 时，key 列将显示实际使用的索引名称。

如果 type 列为 ref: 则 key 列显示一个或多个列的名称，表示使用了非唯一索引进行访问，并且在索引中使用了某些列的等值条件或范围条件来过滤数据。
如果 type 列为 eq_ref: 则 key 列显示一个或多个列的名称，表示使用了唯一索引进行精确查找。
如果 type 列为 const: 则 key 列显示 const，表示使用了常数索引，即通过唯一索引访问单个行。
如果 type 列为 system: 则 key 列显示 NULL，表示使用了系统表。

再温习一下type列的优化效率：
system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

八、key_len

key_len 列表示索引中被使用的字节数。它表示了用于比较和匹配索引列的字节数。

较小的 key_len 值通常表示更高的选择性和更好的索引效果。因此，一般来说，较小的 key_len 值更好。

当 key_len 值为67时，意味着索引中被使用的字节数为67个字节。类似地，当 key_len 值为258时，意味着索引中被使用的字节数为258个字节。

        然而，并非所有情况下较大的 key_len 值都是不好的。在某些特定的场景下，可能会出现较大的 key_len 值。例如，对于较长的字符串列或者使用了复合索引的情况，key_len 值可能较大。

        总之，并不是越大越好，而是需要根据具体的情况来综合考虑。较小的 key_len 值通常更有利于查询性能和索引效果，但在某些特定情况下，较大的 key_len 值也可能是合理的选择。

        需要注意的是，key_len 列的值取决于索引列的数据类型和长度，以及索引的定义。因此，在设计和创建索引时，可以通过合理选择索引列和数据类型来尽量减少 key_len 值，以获得更好的性能。

九、ref

ref 列表示连接查询时使用的参考表和列。它通常出现在联接操作（JOIN）的情况下。当进行连接查询时，MySQL 会使用某些列的等值条件将多个表进行关联。ref 列会显示参考的表和列，用于指示连接操作所涉及的表和列。

ref 列所显示的参考表和列仅表示连接操作所使用的列，并不表示实际的索引使用情况。

十、rows

row 列表示每个步骤（操作）扫描或访问的行数。它显示了在执行该步骤时涉及的行数。

对于不同的操作类型，row 列的含义有所不同：

对于索引扫描（Index Scan）或范围扫描（Range Scan），row 列表示扫描的索引中满足条件的行数。
对于全表扫描（Full Table Scan），row 列表示扫描的整个表中的行数。
对于连接操作（Join），row 列表示连接过程中匹配的行数。

row 列中的具体值，如 23786和 1，表示相应操作所涉及的行数。通常情况下，较小的行数意味着更高的效率，因为处理更少的行可以减少查询的执行时间。还是那句话，仅作部分参考，具体情况还是要看表结构，表大小，查询复杂度等综合因素。

十一、filtered

filtered 列表示操作过程中通过条件过滤掉的行的比例。它表示操作后剩余的行数与初始行数之间的比率。

filtered 列的值可以是一个介于 0 到 1 之间的小数，也可以是一个表示百分比的整数。具体含义如下：

值为 0 表示没有任何行通过过滤条件，即过滤掉了所有的行。
值为 1 表示所有行都通过了过滤条件，即没有行被过滤掉。
值为介于 0 和 1 之间的小数，或表示百分比的整数，表示部分行通过了过滤条件，过滤掉了一部分行。

十二、Extra

在 MySQL 的查询执行计划中，Extra 列提供了有关查询操作的额外信息。这些信息可能包括使用的索引、排序方式、使用了哪些算法等。

以下是 Extra 列可能包含的一些信息：

Using index: 表示查询使用了覆盖索引，即查询的结果可以直接从索引中获取，而无需访问表格数据。
Using where: 表示查询使用了 WHERE 子句来过滤行。
Using temporary: 表示查询需要创建临时表格来存储中间结果，以便进行排序或分组操作。
Using filesort: 表示查询需要进行文件排序（即磁盘上的排序），因为无法使用任何索引来满足排序要求。
Using join buffer: 表示查询使用了连接缓冲区来加速连接查询。
Impossible where: 表示查询的 WHERE 子句永远无法匹配任何行。
Select tables optimized away: 表示查询优化器通过逻辑推理删除了不必要的表格。
Distinct: 表示查询需要去重操作。

溜溜球~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文