MySQL explain执行计划详解

数据运维 2023-12-26 泡泡 手机阅读

explain是MySQL中的一个关键字，可以用来模拟优化器执行SQL语句，分析你的SQL的性能瓶颈。

怎么使用

要使用它，只需将此关键字添加到 SQL 语句前面即可，例如：

explain select * from users where name = 'foo'

explain返回的结果有很多字段，都是什么意思?

下面是users表，有一个联合索引：name_age_school。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '',
  `age` int(11) NOT NULL DEFAULT 0,
  `school` varchar(20) NOT NULL DEFAULT '',
  `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_school` (`name`,`age`,`school`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1;

执行sql：

explain select * from users where name = 'foo';

执行计划的结果：

复杂查询

接下来，看一个稍微复杂一点的例子。

explain 
select * from users u1  
join (select id from users order by name limit 10000, 10) u2 
ON u1.id = u2.id;

该语句的含义是：查询按name字段排序的第10000到10010行的记录。

现在，解释一下结果中返回的这些列都是什么意思：

  1. id

    select 查询的序列号，它是一组数字，表示 select 子句或操作表在查询中执行的顺序。

    它有三种结果

    • id相同：表示执行顺序是从上到下。
    • id不同：如果是子查询，ID的序数会递增，ID的值越大，优先级越高，最先执行
    • id有相同也有不相同：如果id相同，则可以认为是一组，按照从上到下的顺序执行。 所有组中，id值越高，优先级越高，越先执行。例如，使用 union 的查询。
  2. select_type

    查询类型，有 6 个值。

    • SIMPLE：没有子查询或联合查询的简单查询。
    • PRIMARY：如果查询包含任何复杂的子部分，则最外面的查询将被标记为主查询。
    • SUBQUERY： select 或 where语句中包含子查询。
    • DERIVED：FROM 列表中包含的子查询标记为 DERIVED，MySQL 这些子查询以递归方式执行，将结果放置在临时表中。
    • UNION：如果第二个select出现在union之后，则被标记为UNION。
    • UNION RESULT：选择从UNION表中获取结果
  3. type
    • system：这是最基本和最简单的类型。它通常涉及单个行，并用于访问表中的单个行的查询。
    • const：当查询可以基于常量主键查找而仅能检索一行时使用此类型。
    • eq_ref：此类型用于执行使用主键或唯一索引进行唯一索引查找的查询。
    • ref：此类型用于查询使用非唯一索引来根据单个值检索行的情况。
    • range：此类型表示查询正在使用索引上的范围搜索，以检索在指定范围内的行。
    • index：此类型类似于range，但用于查询扫描整个索引。当需要检查索引的较大部分时，它可能不如range高效。
    • all：此类型用于查询执行完整表扫描，即检查表中的所有行。
    • fulltext：此类型专用于全文搜索，用于查询使用全文索引的情况。
    • unique_subquery：当查询使用具有UNIQUE索引的子查询时使用此类型。
    • index_subquery：当查询使用带有索引的子查询时使用此类型。
    • index_merge：表示使用索引合并的优化方法。
  4. table

    查询哪个表

  5. possible_keys

    显示可能应用于此表的一个或多个索引。

    如果查询涉及的字段存在索引，则会列出该索引，但可能不会使用该索引

  6. key

    实际使用的索引，如果为NULL，则没有使用索引。 如果查询中使用了覆盖索引（查询的列恰好是索引），则该索引仅出现在key列表中。

  7. key_len

    表示索引中使用的字节数，计算查询中使用的索引的长度。 在不损失准确性的情况下，长度越短越好。

    显示的 key_len 值是索引字段的最大可能长度，而不是实际使用的长度。 key_len是根据定义计算出来的，并不是从表中检索出来的。

  8. ref

    显示使用了索引的哪一列，以及使用哪些列或常量在索引列上查找值。

    9.rows

    根据表统计信息和索引选择，粗略估计查找所需记录所需读取的行数。

     10. partitions

    匹配分区

  9. filtered

    查询的表行数占表的百分比。

  10. Extra

    包含重要但不适合在其他列中显示的额外信息。

    • Using filesort：请注意，MySQL 使用外部索引对数据进行排序，而不是按照表内索引的顺序读取数据。
    • Using temporary：临时表用于保存中间结果，MySQL在对结果进行排序时使用临时表，常见于排序 order by 和分组查询 group by 。
    • Using index：表示对应的select操作使用Covering Index，避免访问表的数据行，效率较高。如果还出现using where，则表明该索引用于执行索引键值查找。如果没有同时使用where，则使用索引来读取数据而不是执行查找。
    • Using where：使用where 条件。
    •  Using join buffer：使用连接缓存。
    •  impossible where：where子句的值始终为假，不能用于获取任何元组。
    •  unique：一旦MySQL找到与该行共同匹配的行，就不再搜索。
    • Select Table Optimizedaway：选择操作已经优化到无法再优化的程度。

    怎么trace？

    有时候我们发现explain的结果和我们期望的不一样，因为MySQL优化器做了成本计算，MySQL觉得不使用索引更快。

    例如以下例子：

    explain select * from users where name > 'foo';

    可以看到执行计划中没有使用任何索引。

    关于执行成本，我们可以通过trace工具查看。

    -- open trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- execute SQL
explain select * from users where name > 'foo';
-- get result
SELECT * FROM information_schema.OPTIMIZER_TRACE;

    结果是：

    {
"steps": [
{
  "join_preparation": { -- step1: Format SQL
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `users`.`id` AS `id`,`users`.`name` AS `name`,`users`.`age` AS `age`,`users`.`school` AS `school`,`users`.`birthday` AS `birthday` from `users` where (`users`.`name` > 'foo')"
      }
    ] /* steps */
  } /* join_preparation */
},
{
  "join_optimization": { -- step2: Optimize SQL
    "select#": 1,
    "steps": [
      {
        "condition_processing": { -- step3: Use where
          "condition": "WHERE",
          "original_condition": "(`users`.`name` > 'foo')",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "(`users`.`name` > 'foo')"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "(`users`.`name` > 'foo')"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "(`users`.`name` > 'foo')"
            }
          ] /* steps */
        } /* condition_processing */
      },
      {
        "substitute_generated_columns": {
        } /* substitute_generated_columns */
      },
      {
        "table_dependencies": [
          {
            "table": "`users`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
            ] /* depends_on_map_bits */
          }
        ] /* table_dependencies */
      },
      {
        "ref_optimizer_key_uses": [
        ] /* ref_optimizer_key_uses */
      },
      {
        "rows_estimation": [
          {
            "table": "`users`",
            "range_analysis": {
              "table_scan": {
                "rows": 1,
                "cost": 3.3
              } /* table_scan */,
              "potential_range_indexes": [
                {
                  "index": "PRIMARY",
                  "usable": false,
                  "cause": "not_applicable"
                },
                {
                  "index": "idx_name_age_school",
                  "usable": true,
                  "key_parts": [
                    "name",
                    "age",
                    "school",
                    "id"
                  ] /* key_parts */
                }
              ] /* potential_range_indexes */,
              "setup_range_conditions": [
              ] /* setup_range_conditions */,
              "group_index_range": {
                "chosen": false,
                "cause": "not_group_by_or_distinct"
              } /* group_index_range */,
              "analyzing_range_alternatives": {
                "range_scan_alternatives": [
                  {
                    "index": "idx_name_age_school",
                    "ranges": [
                      "foo < name"
                    ] /* ranges */,
                    "index_dives_for_eq_ranges": true,
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": false,
                    "rows": 1,
                    "cost": 2.21,
                    "chosen": true
                  }
                ] /* range_scan_alternatives */,
                "analyzing_roworder_intersect": {
                  "usable": false,
                  "cause": "too_few_roworder_scans"
                } /* analyzing_roworder_intersect */
              } /* analyzing_range_alternatives */,
              "chosen_range_access_summary": {
                "range_access_plan": {
                  "type": "range_scan",
                  "index": "idx_name_age_school",
                  "rows": 1,
                  "ranges": [
                    "foo < name"
                  ] /* ranges */
                } /* range_access_plan */,
                "rows_for_plan": 1,
                "cost_for_plan": 2.21,
                "chosen": true
              } /* chosen_range_access_summary */
            } /* range_analysis */
          }
        ] /* rows_estimation */
      },
      {
        "considered_execution_plans": [
          {
            "plan_prefix": [
            ] /* plan_prefix */,
            "table": "`users`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "rows_to_scan": 1,
                  "access_type": "range",
                  "range_details": {
                    "used_index": "idx_name_age_school"
                  } /* range_details */,
                  "resulting_rows": 1,
                  "cost": 2.41,
                  "chosen": true
                }
              ] /* considered_access_paths */
            } /* best_access_path */,
            "condition_filtering_pct": 100,
            "rows_for_plan": 1,
            "cost_for_plan": 2.41,
            "chosen": true
          }
        ] /* considered_execution_plans */
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`users`.`name` > 'foo')",
          "attached_conditions_computation": [
          ] /* attached_conditions_computation */,
          "attached_conditions_summary": [
            {
              "table": "`users`",
              "attached": "(`users`.`name` > 'foo')"
            }
          ] /* attached_conditions_summary */
        } /* attaching_conditions_to_tables */
      },
      {
        "refine_plan": [
          {
            "table": "`users`",
            "pushed_index_condition": "(`users`.`name` > 'foo')",
            "table_condition_attached": null
          }
        ] /* refine_plan */
      }
    ] /* steps */
  } /* join_optimization */
},
{
  "join_explain": {
    "select#": 1,
    "steps": [
    ] /* steps */
  } /* join_explain */
}
] /* steps */
}