MySQL优化—EXPLAIN与TRACE浅析

时间:2021-07-22 23:39:02   收藏:0   阅读:76

准备表

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内部优化器

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

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

技术分享图片

--优化前
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的性能细节。

技术分享图片

EXPLAIN结果各列的释义如下:

id

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

select_type

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

table

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

type

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

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

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

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 直接在索引列上进行聚合函数的操作,没有进行任何表的操作

索引失效

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

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 */
}

原文:https://www.cnblogs.com/wind-ranger/p/15046434.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!