MySQL优化—EXPLAIN与TRACE浅析

2021-12-31 02:57:32

准备表

CREATE TABLE `cust` (
  `cust_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cust_name` varchar(100) DEFAULT NULL,
  `age` smallint(3) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`cust_id`),
  KEY `idx_name_age` (`cust_name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EXPLAIN——执行计划

通过执行计划，可以对SQL语句的性能进行判断。

MySQL中，执行计划的语法很简单，只需要在SQL语句前加上EXPLAIN关键字即可。

示例：

--使用执行计划查看SQL性能
EXPLAIN SELECT * FROM cust WHERE cust_id >1 LIMIT 5;

MySQL优化—EXPLAIN与TRACE浅析

MySQL内部优化器

在SQL语句执行之前，MySQL会使用内部优化器对其进行一次优化，使性能尽可能的好。

在EXPLAIN语句执行之后，可通过SHOW WARNINGS;语句查看MySQL内部优化器对SQL语句的优化：

MySQL优化—EXPLAIN与TRACE浅析

--优化前
SELECT * FROM cust WHERE cust_id >1 LIMIT 5;
--优化后
/* select#1 */ select `test`.`cust`.`cust_id` AS `cust_id`,`test`.`cust`.`cust_name` AS `cust_name`,`test`.`cust`.`age` AS `age`,`test`.`cust`.`sex` AS `sex`,`test`.`cust`.`birthday` AS `birthday` from `test`.`cust` where (`test`.`cust`.`cust_id` > 1) limit 5;

结果列解析

EXPLAIN并不会实际执行要分析的SQL语句，只是分析这条SQL的性能细节。

MySQL优化—EXPLAIN与TRACE浅析

EXPLAIN结果各列的释义如下：

id

SQL的执行编号，SQL按id从大到小的顺序执行，id相同时，处在前面的先执行。

select_type

指定所使用的SELECT查询类型，取值包括

  • SIMPLE——简单的SELECT，不使用UNION或子查询
  • PRIMARY——最外部主查询
  • DERIVED——在FROM后面进行的子查询，会产生衍生表。默认情况下，MySQL开启了对衍生表的合并优化，因此是看不到这个值的。可通过执行set session optimizer_switch=‘derived_merge=off‘来关闭自动优化，就可以看到此值。
  • SUBQUERY——在FROM前进行的子查询
  • UNION——联合查询
table

指定数据库读取的数据表的名字，按被读取的先后顺序排列。

type

指定本数据库表与其他数据表之间的关联关系。可以直观的判断出当前SQL语句的性能。

type的取值和性能优劣：null > system > const > eq_ref > ref > range > index > all

对于一条SQL来说，其type列的值在range及以上级别，才能算性能良好。

  • null：性能最好，一般是在使用了聚合函数操作索引列时，这时结果直接从索引树中获取，因此性能最好。
  • system：只与一条记录进行比较，性能极好。但这种场景很少见。
  • const：使用主键索引或唯一索引与常量比较时。性能非常好。
  • eq_ref：在进行关联查询时，关联条件使用表的主键。
  • ref：查询条件使用普通索引。
  • range：在索引列上使用了范围查询。
  • index：没有进行条件过滤，但所有记录都可以直接从索引树上获取（eg：表中的字段都定义了索引）。
  • all：全表扫描。
possible_keys

给出了MySQL在搜索数据记录时可选用的各个索引。

MySQL内部优化器会进行判断，如果使用索引查询比全表扫描要慢，那么内部优化器就会使用全表扫描的方式。可通过trace工具查看。

key

MySQL实际选用的索引

key_len

给出索引按字节计算的长度，key_len数值越小，表示越快。

通过此列的值，可推断出命中了联合索引中的哪几列。

此列的计算规则如下：

索引字段类型 key_len长度（字节）
char(n) n
varchar(n) 需额外使用2字节存储字符串长度，例如utf8，则为3n+2
tinyint 1
smallint 2
int 4
bigint 8
date 3
timestamp 4
datetime 8
NULL 1，用一个字节来记录是否为NULL

索引的最大长度是768byte，当索引列的值超长时，MySQL会做一个类似最左前缀的处理，将其值从前面截取出来。

ref

给出了关联关系中另一个数据表里的数据列的名字。

rows

指MySQL在执行这个查询时预计会从数据库表里读取的数据行数。

Extra

提供了与关联操作有关的信息。

取值及释义如下：

取值 释义
Using index（覆盖索引） 查询的字段都是索引列字段，此时可直接从索引树中获取数据，不需要查表。这是SQL优化中的常用手段
Using where 在where中使用索引字段作为条件进行范围搜索
Using index condition 查询字段包含索引列之外的字段，且在where中使用索引字段作为条件进行范围搜索
Using temporary 在非索引列上进行去重等操作。此情形性能非常差
Using filesort 使用磁盘+内存的方式进行文件排序，涉及到两个概念：单路排序、双路排序
Select tables optimized away 直接在索引列上进行聚合函数的操作，没有进行任何表的操作

索引失效

当查询条件中出现如下情形时，会导致索引失效：

  • 查询的字段没有覆盖索引时，在索引列上进行算术运算、函数、类型转换
  • 使用!=、 <>、 not in、 not exists、is null、 is not null
  • 使用like查询时，使用通配符%开头
  • 隐式类型转换，如字符串不加" ‘ "
  • 使用or或者in可能会不走索引

Trace工具

MySQL内部优化器会对SQL进行判断，如果使用索引查询比全表扫描要慢，那么内部优化器就会使用全表扫描的方式。可通过trace工具查看。

Trace使用方式

--开启Trace
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=ON;
--执行SQL
SELECT * from cust where birthday !=NOW();
--查询Trace信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{
  "steps": [ 
    {
	  /* 阶段一：准备阶段*/
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `cust`.`cust_id` AS `cust_id`,`cust`.`cust_name` AS `cust_name`,`cust`.`age` AS `age`,`cust`.`sex` AS `sex`,`cust`.`birthday` AS `birthday` from `cust` where ((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
	  /*  阶段二：优化阶段 */
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
			/* 条件处理 */
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
			/* 表依赖详情 */
            "table_dependencies": [
              {
                "table": "`cust`",
                "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": "`cust`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3,
                    "cost": 3.7
                  } /* table_scan */,

				  /* 可能使用到的索引 */
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", /* 主键索引 */
                      "usable": true, /* 是否被使用：是 */
                      "key_parts": [
                        "cust_id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_name_age", /* 联合索引 */
                      "usable": true, /* 是否被使用：是 */
                      "key_parts": [
                        "cust_name",
                        "age",
                        "cust_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": "PRIMARY",
                        "ranges": [
                          "1 < cust_id"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false, /* 是否覆盖索引：否 */
                        "rows": 2, /* 需要扫描的行数 */
                        "cost": 2.41, /* 花费时间，单位：毫秒 */
                        "chosen": true /* 是否选择使用：是 */
                      },
                      {
                        "index": "idx_name_age",
                        "ranges": [
                          "a < cust_name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false, /* 是否覆盖索引：否 */
                        "rows": 3, /* 需要扫描的行数 */
                        "cost": 4.61, /* 花费时间，单位：毫秒 */
                        "chosen": false, /* 是否选择使用：否 */
                        "cause": "cost" /* 放弃的原因：开销比较大 */
                      }
                    ] /* 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": "PRIMARY",
                      "rows": 2,
                      "ranges": [
                        "1 < cust_id"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 2,
                    "cost_for_plan": 2.41,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
			/* 考虑的执行计划 */
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`cust`",
				/* 最优访问路径 */
                "best_access_path": {
				  /* 最终选择的访问路径 */
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2, /* 扫描的行数 */
                      "access_type": "range", /* 扫描的方式 */
                      "range_details": {
                        "used_index": "PRIMARY" /* 使用主键 */
                      } /* range_details */,
                      "resulting_rows": 2, /* 结果集 */
                      "cost": 2.81, /* 花费时间 */
                      "chosen": true /* 是否选择：是 */
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.81,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`cust`",
                  "attached": "((`cust`.`cust_name` > ‘a‘) and (`cust`.`cust_id` > 1))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`cust`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

MySQL优化—EXPLAIN与TRACE浅析

上一篇：mysql加锁处理分析


下一篇：08持久化操作RDB