optimizer trace 查看Mysql如何选择执行计划的过程

最新推荐文章于 2022-05-09 14:28:56 发布
最新推荐文章于 2022-05-09 14:28:56 发布
阅读量732 收藏 1
点赞数
分类专栏： mysql & oracle & 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37968613/article/details/115002554
版权

前面几篇讲了Mysql基于成本的优化，也看了执行计划，今天我们来看看Mysql提供的更详细的执行计划生成过程。

optimizer trace

optimizer trace由来：

1、我们平时查看SQL的执行计划，但是执行计划是相对粗略的，只告诉你使用了什么索引，采用了什么方式。但是没有告诉你它为什么选择这种查询方式。
2、所以，在MySQL 5.6之后，提供了个optimizer trace功能，可以查看Mysql详细的查询计划生成过程。

optimizer trace的使用方式：

  1. 打开optimizer trace功能 (默认情况下它是关闭的):
    SET optimizer_trace=“enabled=on”;
  2. 这里输入你自己的查询语句
    SELECT …;
  3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
    SELECT * FROM information_schema.OPTIMIZER_TRACE;
  4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步
    …
  5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
    SET optimizer_trace=“enabled=off”;

optimizer trace实践：

我们可以把optimizer_trace开关打开：

SET optimizer_trace="enabled=on";

查看修改情况：

show variables like '%optimizer_trace%';

在这里插入图片描述

查询一条sql之后，如：select * from test_index where key1 > 'key120' and key1 < 'key129';，发现SELECT * FROM information_schema.OPTIMIZER_TRACE;为
在这里插入图片描述

是IDEA的一条SQL，SET SQL_SELECT_LIMIT=501，因为optimizer_trace默认只显示最近一条SQL的执行计划。
正因为我们使用的是IDEA的数据库插件，它会在我们查询前后增添一些SQL，影响了我们查看optimizer_trace，我们观察到optimizer_trace_limit、optimizer_trace_offset两个变量，默认值分别为1和-1，表示只保留一条执行计划，同时指向最近的一条记录，我将optimizer_trace_limit和optimizer_trace_offset都修改为5，表示保留最近5条记录，显示前5条。

再次查询成功：

select * from test_index where key1 > 'key120' and key1 < 'key129';
SELECT * FROM information_schema.OPTIMIZER_TRACE;

在这里插入图片描述

我们来看一下执行计划生成过程：

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
			// 将select * 转换为 select 每个列
            "expanded_query": "/* select#1 */ select `test_index`.`id` AS `id`,`test_index`.`key1` AS `key1`,`test_index`.`key2` AS `key2`,`test_index`.`key3` AS `key3`,`test_index`.`key_part1` AS `key_part1`,`test_index`.`key_part2` AS `key_part2`,`test_index`.`key_part3` AS `key_part3`,`test_index`.`common_field` AS `common_field` from `test_index` where ((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
			// 对查询条件进行处理
            "condition_processing": {
				// 原始的查询条件
              "condition": "WHERE",
              "original_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))",
              "steps": [
                {
   					// 等值转换
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))"
                },
                {
   					// 常量传递转换
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))"
                },
                {
					// 琐碎的条件 （去除没用的条件）
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))"
                }
              ]
            }
          },
          {
			-- 替换虚拟生成列
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`test_index`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
				// 预估不同单表访问方法的访问成本
            "rows_estimation": [
              {
                "table": "`test_index`",
                "range_analysis": {
					// 全表扫描的行数以及成本
                  "table_scan": {
                    "rows": 99316,
                    "cost": 119181
                  },
                  "potential_range_indexes": [
                    {
						// 不能使用主键索引
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
					// 不能使用idx_key2索引
                      "index": "idx_key2",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
						// 不能使用idx_key3索引
                      "index": "idx_key3",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
						// 不能使用idx_key_part索引
                      "index": "idx_key_part",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
						// 可以使用idx_key1索引
                      "index": "idx_key1",
                      "usable": true,
						// 二级索引由 二级索引列+主键索引构成
                      "key_parts": [
                        "key1",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
               // 没有group_by或distinct，不选择通过归集索引的方式
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
					// 分析各种可能使用的索引的成本
                  "analyzing_range_alternatives": {
					// 通过索引range扫描的方案
                    "range_scan_alternatives": [
                      {
                        "index": "idx_key1",
                        "ranges": [
                          "key120 < key1 < key129"
                        ],
                        "index_dives_for_eq_ranges": true,  # 是否使用index dive
                        "rowid_ordered": false,  # 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,  # 是否使用mrr
                        "index_only": false,  # 是否是索引覆盖访问
                        "rows": 18254,  # 使用该索引获取的记录条数
                        "cost": 21906,  # 使用该索引的成本
                        "chosen": true  # 是否选择该索引
                      }
                    ],
 					# 分析使用索引合并的成本
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
 					# 对于上述单表查询最优的访问方法
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_key1",
                      "rows": 18254,
                      "ranges": [
                        "key120 < key1 < key129"
                      ]
                    },
                    "rows_for_plan": 18254,
                    "cost_for_plan": 21906,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
			# 分析各种可能的执行计划
			 #（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取就好）
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`test_index`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 18254,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_key1"
                      },
                      "resulting_rows": 18254,
                      "cost": 25557,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 18254,
                "cost_for_plan": 25557,
                "chosen": true
              }
            ]
          },
          {
				# 尝试给查询添加一些其他的查询条件
            "attaching_conditions_to_tables": {
              "original_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))",
              "attached_conditions_computation": [
                {
                  "table": "`test_index`",
					# 重新确认索引的使用，原因是low_limit，查询很少的数据
                  "rechecking_index_usage": {
                    "recheck_reason": "low_limit",
                    "limit": 501,
                    "row_estimate": 18254
                  }
                }
              ],
              "attached_conditions_summary": [
                {
                  "table": "`test_index`",
                  "attached": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))"
                }
              ]
            }
          },
          {
			# 再稍稍的改进一下执行计划
            "refine_plan": [
              {
                "table": "`test_index`",
                "pushed_index_condition": "((`test_index`.`key1` > 'key120') and (`test_index`.`key1` < 'key129'))",
                "table_condition_attached": null
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

小结

查看optimizer trace可以使我们查看更详细的执行计划生成过程。

c&0xff00
关注
专栏目录
Mysql:OPTIMIZER_TRACE详解
知识改变命运，ヾ(◍°∇°◍)ﾉﾞ【求关注】
03-27 1752
OPTIMIZER_TRACE是MySQL5.6引入的一个跟踪功能
mysql optimize trace_MySQL 调优 | OPTIMIZER_TRACE详解
weixin_42490217的博客
01-19 785
TIPS本文基于MySQL 8.0编写，理论支持MySQL 5.6及更高版本。OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。此功能默认关闭，开启后，可分析如下语句：SELECTINSERTREPLACE...
【mysql】查询优化——mysql如何选择最优的执行计划
shanxiaoshuai的博客
05-25 928
【这篇文章最开始是在公司内部写的，后面抽了点时间稍微整理了一下，主要是把涉及到公司内部数据的部分去除，可能会导致一些阅读不畅】 本文的内容主要涉及到一些工具命令比如explain\optimize trace、mysql优化器的工作原理以及涉及到InnoDB的相关内容。 mysql逻辑架构 优化分析: explain和optimize trace 一条sql语句通常都会有多种执行方案，mysql的优化器会选择其认为最优的方案进行执行，这里的最优其是指成本最低，在后面的优化器工作原理中会详细讲到。在和mys
MySQL：MySQL是如何根据成本选择执行计划的
OceanStar的博客
12-26 227
不管我们执行单表查询也好，执行多表查询也好，似乎都有多种执行计划可以选择，那到底是用哪种执行计划呢？ MySQL会根据一个查询语句的多个执行计划评估它的成本，然后根据成本评估一个成本最低的执行计划，以保证最佳的查询速度。 问题：全表扫描执行计划的成本计算方法 简单来说，跑一个SQL语句，一般成功是两块。 首先是那些数据如果在磁盘里，需要先把从磁盘里把数据读出来。这个从磁盘读取数据到内存就是IO成本，而且MySQL里都是一页一页读的，读一页的成本的约定是1.0 然后，你拿到数据之后，还需要对数据做一些运
MySQL通过trace分析优化器如何选择执行计划
Mreden的博客
04-22 350
MySQL优化(2020-04-22) 通过trace分析优化器如何选择执行计划 mysql 5.6提供了对sql跟踪trace,我们可以通过trace查看可以清楚的知道优化器执行过程，从而知道优化器的行为，可以帮助我们理解优化。 使用方式 // 首先得打开trace设置显示格式我json格式，以及设置trace最大的可以使用的大小，设置这个原因默认大小比较小，会显示不完整影响分析。 ...
浅谈mysql的成本及如何选择执行计划
butt168的博客
07-03 273
这里写目录标题前言什么是‘成本’怎么计算全表扫描索引查询总结 前言 在实际使用过程中，或许会遇到一个问题，在执行单表查询或者多表关联查询，似乎有多重执行计划可以选择，使用索引A？还是索引B？还是都不用，直接上全表扫描？到底使用哪种方式，今天我们就来探讨一下 什么是‘成本’ 跑一个SQL语句，一般成本是两块 从磁盘读数据到内存就是IO成本，而且MySQL里都是一页一页读的，读一页的成本的约定为1.0 对数据进行运算，验证是否符合条件，耗费CPU资源的，属于CPU成本，一般约定读取和检测一条数据是否符合条件的
执行计划怎么出来 MySQL获取_mysql 获取执行计划的方法
weixin_32515349的博客
02-04 200
mysql 获取执行计划方法：1.通过explain进行查看sql的执行计划；2.通线程正在执行的sql查看该sql的执行计划；explain进行查看sql的执行计划相对简单，其实通线程正在执行的sql查看执行计划也很简单，那么为什么还需要第二种方式呢？因为日常工作会发现很多sql一直在执行，执行发生异常，如果把该sql拿出来执行又很快，于是我们想获取该sql执行异常时候的执行计划，同时因为数据在...
mysql通过trace分析优化器如何选择执行计划
chenlvzhou的专栏
06-25 900
在我们调优MySQL的SQL时候，通常使用explain进行查看sql的执行计划。至于优化器为什么会这样选择，就无从得知。如果你想了解为什么，那么可以通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划.帮助我们更好的理解优化器的行为. 打开trace,并设置格式为Json SET OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_J...
MySQL再深入执行计划之trace工具
uuqaz的博客
05-09 347
MySQL 执行查询语句时需要进行的查询成本分析了，而 MySQL 提供的trace工具就可以让我们看到 MySQL 计算的查询成本，以及选择索引的大致分析过程。 #一、示例表 -- 示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT ..
mysql OPTIMIZER_TRACE
最新发布
09-29
mysql OPTIMIZER_TRACE 是一个用于帮助我们分析和优化MySQL查询执行...通过使用 OPTIMIZER_TRACE，我们可以了解MySQL为什么选择了某个特定的执行计划以及执行计划的详细信息。这对于我们分析和优化查询性能非常有帮助。
MySQL使用trace工具查看执行计划
weixin_43364551的博客
02-11 2828
文章目录一、什么是trace工具二、trace工具的使用三、结构分析3.1、SQL准备阶段3.2、SQL分析、优化阶段四、重要信息分析 一、什么是trace工具 MySQL5.6版本开始，推出了对SQL的跟踪工具trace，通过使用trace，用户可以查看MySQL优化器对SQL语句的分析结果，以及生成了怎样的执行计划。 二、trace工具的使用 1、开启/关闭trace工具 开启：set session optimizer_trace="enabled=on",end_markers_in_json
mysql执行计划和优化跟踪执行查看（optimizer_trace）
liaomingwu的专栏
03-21 1439
mysql执行计划和优化跟踪执行查看（optimizer_trace）
mysql调优：执行计划、optimizer_trace 查看执行流程、调优
SHOUGOUGOU的专栏
03-31 507
SQL执行计划 1、如何查看sql的执行计划 在需要执行的查询SQL前添加一个关键字“EXPLAIN” EXPLAIN中的列 （1）id 1、标识select所属的行，sql语句中有多少个select就有多少个id，并且id的顺序是按照select出现的顺序增长的 2、id越大，越先执行 3、id相同，从上往下执行 4、id为NUll的最后执行 (2) select_type 显示对应行是简单还是复杂select，SIMPLE值表明没有子查询或Union，如果有子查询，那么最.
MySQL实战：为什么选择这个执行计划？（optimizer trace）
小识的博客
03-30 566
CREATE TABLE `demo_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val` int(11) NOT NULL, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_val` (`val`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into demo_table (val, name.
更详细的执行计划 optimizer_trace
三易专栏
08-26 282
目录 数据准备 查看 optimizer_trace 数据准备 CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11), `b` int(11), `c` varchar(16) , `create_time` datetime, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 插入数据 for(int i=1; i&
Mysql异常'SET OPTION SQL_SELECT_LIMIT=DEFAULT'
weixin_34056162的博客
07-23 337
在用hive时遇到这样的一个异常，因为hive的元数据是存储在mysql数据库中，所以对于hive表的操作相应的会操作数据库中表的数据，该问题发生于删除表的时候，创建的时候并未产生该异常。经过查阅资料了解到mysql通过jdbc链接的时候会进行测试'SET OPTION SQL_SELECT_LIMIT=DEFAULT',但是5.6以后的版本弃用了set...
解决Kettle报错"OPTION SQL_SELECT_LIMIT=DEFAULT"的方法
weixin_43441657的博客
12-29 1505
Kettle（PDI）连接MySQL数据库，有可能会提示OPTION SQL_SELECT_LIMIT=DEFAULT。 软件提示如下错误信息： 原因是JAVA的MYSQL驱动过时，不能适应新版的MYSQL数据库。解决方法很简单，从MYSQL官方网站下载最新的MYSQL驱动，解压到Kettle对应文件夹下即可。 具体操作： 1、进入MYSQL官方网站的JAVA驱动下载页面http://dev.m...
Pycharm 添加数据库驱动
枪枪枪的博客
05-04 5053
今天在使用pycharm 通过Database窗口操作sqlite时显示 20:04 SELECT t.* FROM User t LIMIT 501 com.intellij.execution.ExecutionException: Exception in thread "main" java.lang.ClassNotFoundException: org.sqlite.JD...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值