mysql中optimizer trace的作用

大家好。对于MySQL 5.6以及之前的版本来说,查询优化器就像是一个黑盒子一样,我们只能通过EXPLAIN语句查看到最后 优化器决定使用的执行计划,却无法知道它为什么做这个决策。于是在MySQL5.6以及之后的版本中,MySQL新增了一个optimizer trace的功 能,这个功能可以让我们方便的查看优化器生成执行计划的整个过程,今天我们就来简单了解一下这个功能。

optimizer trace功能的开启与关闭由系统变量optimizer_trace决定,我们看一下:
在这里插入图片描述
可以看到enabled值为off ,表明这个功能默认是关闭的。 one_line的值是控制输出格式的,如果为on那么所有输出都将在一行中展示,不适合人阅读。

如果想打开optimizer trace功能,必须首先把enabled的值改为on ,我们可以通过下边这个sql语句修改enabled的值:

SET optimizer_trace="enabled=on";

enabled的值改为on后我们就可以输入我们想要查看优化过程的查询语句,当该查询语句执行完成后,就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程。这个 OPTIMIZER_TRACE 表有4个列,分别是:

QUERY : 表示我们的查询语句。

TRACE : 表示优化过程的JSON格式文本。

MISSING_BYTES_BEYOND_MAX_MEM_SIZE : 由于优化过程可能会输出很多,如果超过某个限制时,多余的文本将不会被显示,这个字段展示了被忽略的文本字节数。

INSUFFICIENT_PRIVILEGES : 表示是否没有权限查看优化过程,默认值是0。

完整的使用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"

下面我们以一个复杂一点的sql为例,来聊一聊如何使用optimizer trace功能。
在这里插入图片描述

可以看到该查询可能使用到的索引有3个,那么为什么优化器最终选择了idx_key1而不选择其他的索引或者直接全表扫描呢?这时候就可以通过otpimzer trace 功能来查看优化器的具体工作过程:

SET optimizer_trace="enabled=on"; 
SELECT * FROM s1 WHERE  key1 > 'z' AND  key2 < 1000000 AND  key3 IN ('a', 'b', 'c') AND  common_field = 'abc'; 
SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

我们直接看一下通过查询OPTIMIZER_TRACE 表得到的输出:

*************************** 1. row ***************************
# 分析的查询语句是什么 
QUERY: SELECT * FROM single_table WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('a', 'b', 'c')AND common_field = 'abc'
# 优化的具体过程 
TRACE: {"steps": [{"join_preparation": {  # prepare阶段 "select#": 1,"steps": [{"IN_uses_bisection": true},{"expanded_query": "/* select#1 */ select `single_table`.`id` AS `id`,`single_table`.`key1` AS `key1`,`single_table`.`key2` AS `key2`,`single_table`.`key3` AS `key3`,`single_table`.`key_part1` AS `key_part1`,`single_table`.`key_part2` AS `key_part2`,`single_table`.`key_part3` AS `key_part3`,`single_table`.`common_field` AS `common_field` from `single_table` where ((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"}]}},{"join_optimization": {  # optimize阶段 "select#": 1,"steps": [{"condition_processing": {   # 处理搜索条件"condition": "WHERE",# 原始搜索条件 "original_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))","steps": [{# 等值传递转换 "transformation": "equality_propagation","resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"},{# 常量传递转换     "transformation": "constant_propagation","resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"},{# 去除没用的条件 "transformation": "trivial_condition_removal","resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"}]}},{# 替换虚拟生成列 "substitute_generated_columns": {}},{# 表的依赖信息 "table_dependencies": [{"table": "`single_table`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": []},{# 预估不同单表访问方法的访问成本 "rows_estimation": [{"table": "`single_table`","range_analysis": {"table_scan": {  # 全表扫描的行数以及成本 "rows": 9823,"cost": 1012.48},# 分析可能使用的索引 "potential_range_indexes": [{"index": "PRIMARY",   # 主键不可用"usable": false,"cause": "not_applicable"},{"index": "idx_key2",  # idx_key2可能被使用 "usable": true,"key_parts": ["key2"]},{"index": "idx_key1",  # idx_key1可能被使用 "usable": true,"key_parts": ["key1","id"]},{"index": "idx_key3",  # idx_key3可能被使用 "usable": true,"key_parts": ["key3","id"]},{"index": "idx_key_part",   # idx_keypart不可用"usable": false,"cause": "not_applicable"}],"setup_range_conditions": [],"group_index_skip_scan": {"chosen": false,"cause": "not_group_by_or_distinct"},"skip_scan_range": {"potential_skip_scan_indexes": [{"index": "idx_key2","usable": false,"cause": "query_references_nonkey_column"},{"index": "idx_key1","usable": false,"cause": "query_references_nonkey_column"},{"index": "idx_key3","usable": false,"cause": "query_references_nonkey_column"}]},# 分析各种可能使用的索引的成本 "analyzing_range_alternatives": {"range_scan_alternatives": [{# 使用idx_key2的成本分析"index": "idx_key2",# 使用idx_key2的范围区间 "ranges": ["NULL < key2 < 1000000"],"index_dives_for_eq_ranges": true,  # 是否使用index dive "rowid_ordered": false,  # 使用该索引获取的记录是否按照主键排序 "using_mrr": false,   # 是否使用mrr "index_only": false,  # 是否是索引覆盖访问"in_memory": 1,   "rows": 10000,  # 使用该索引获取的记录条数 "cost": 3895.04,  # 使用该索引的成本 "chosen": false,  # 是否选择该索引"cause": "cost"  # 因为成本太大所以不选择该索引 },{# 使用idx_key1的成本分析 "index": "idx_key1",# 使用idx_key1的范围区间 "ranges": ["'z' < key1"],"index_dives_for_eq_ranges": true,  # 是否使用index dive "rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序 "using_mrr": false,  # 是否使用mrr"index_only": false, # 是否是索引覆盖访问"in_memory": 0.0769231,"rows": 1, # 使用该索引获取的记录条数"cost": 0.688947, # 使用该索引的成本 "chosen": true # 是否选择该索引},{# 使用idx_key3的成本分析 "index": "idx_key3",# 使用idx_key3的范围区间 "ranges": ["key3 = 'a'","key3 = 'b'","key3 = 'c'"],"index_dives_for_eq_ranges": true,  # 是否使用index dive "rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序 "using_mrr": false,  # 是否使用mrr"index_only": false, # 是否是索引覆盖访问"in_memory": 0.0769231,"rows": 3, # 使用该索引获取的记录条数"cost": 2.04684, # 使用该索引的成本 "chosen": false, # 是否选择该索引"cause": "cost"  # 因为成本太大所以不选择该索引 }],# 分析使用索引合并的成本 "analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"}},# 对于上述单表查询最优的访问方法 "chosen_range_access_summary": {"range_access_plan": {"type": "range_scan","index": "idx_key1","rows": 1,"ranges": ["'z' < key1"]},"rows_for_plan": 1,"cost_for_plan": 0.688947,"chosen": true}}}]},{# 分析各种可能的执行计划 #(对多表查询这可能有很多种不同的方案,单表查询的方案上边已经分析过了,直接选取idx_key1就好)"considered_execution_plans": [{"plan_prefix": [],"table": "`single_table`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 1,"access_type": "range","range_details": {"used_index": "idx_key1"},"resulting_rows": 1,"cost": 0.788947,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 1,"cost_for_plan": 0.788947,"chosen": true}]},{# 尝试给查询添加一些其他的查询条件 "attaching_conditions_to_tables": {"original_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))","attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`single_table`","attached": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"}]}},{"finalizing_table_conditions": [{"table": "`single_table`","original_table_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))","final_table_condition   ": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"}]},{# 再稍稍的改进一下执行计划 "refine_plan": [{"table": "`single_table`","pushed_index_condition": "(`single_table`.`key1` > 'z')","table_condition_attached": "((`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"}]}]}},{"join_execution": {   # execute阶段"select#": 1,"steps": []}}]
}
# 因优化过程文本太多而丢弃的文本字节大小,值为0时表示并没有丢弃 
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段 
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

通过上述的信息我们得知,优化过程大致分为了三个阶段:prepare 阶段optimize阶段execute阶段

我们所说的基于成本的优化主要集中在optimize阶段,对于单表查询来说,我们主要关注optimize阶段 的"rows_estimation"这个过程,这个过程深入分析了对单表查询的各种执行方案的成本;对于多表连接查询来 说,我们更多需要关注"considered_execution_plans"这个过程,这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划,也就是我们使用EXPLAIN语句所展现出的那种方案。

好了,到这里我们就讲完了,大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注,谢谢大家!最后依旧是请各位老板有钱的捧个人场,没钱的也捧个人场,谢谢各位老板!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/341271.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Linux 搭建 ZeroTier 的 Moon 服务器

系统&#xff1a;centos 7.6 轻量云服务器&#xff1a;腾讯云 Moon是什么&#xff0c;为什么需要Moon&#xff1f; ZeroTier通过自己的多个根服务器帮助我们建立虚拟的局域网&#xff0c;让虚拟局域网内的各台设备可以打洞直连。这些根服务器的功能有些类似于通过域名查询找到…

自动驾驶仿真:python和carsim联合仿真案例

文章目录 前言一、Carsim官方案例二、Carsim配置1、车辆模型2、procedure配置3、Run Control配置 三、python编写四、运行carsim五、运行python总结 前言 carsim内部有许多相关联合仿真的demo&#xff0c;simulink、labview等等都有涉及&#xff0c;这里简单介绍下python和car…

RTOS笔记--任务状态与调度

任务状态 freertos中的任务分为四个状态&#xff1a;就绪状态&#xff08;ready&#xff09;、运行状态&#xff08;running&#xff09;、阻塞状态&#xff08;blocked&#xff09;、暂停状态&#xff08;suspended&#xff09; 完整的任务状态转换图&#xff1a; 在使用vTas…

NGINX之location和rewrite

一.NGINX常用的正则表达式 二.Location location作用:对访问的路径做访问控制或者代理转发 1.location 常用的匹配规则&#xff1a; 进行普通字符精确匹配&#xff0c;也就是完全匹配^~ / 表示普通字符匹配。使用前缀匹配。如果匹配成功&#xff0c;则不再匹配其它 …

视频号视频提取器有那些?免费版可以下载视频号视频吗?

在网络上&#xff0c;确实存在多种工具和公众号用于下载视频号内容。今天就说说免费的视频提取器有那些&#xff1f; 视频号提取器工具中常见的有公众号和软件。这些工具通常可以帮助用户从微信视频号平台下载视频内容。 但请注意&#xff0c;使用这些工具可能需要考虑版权和法…

使用HTML、CSS和JavaScript编写一个注册界面(一)

倘若文章或代码中有任何错误或疑惑&#xff0c;欢迎提出交流哦~ HTML和CSS 首先&#xff0c;我们需要编写一个简洁的注册界面。 简单编写下&#xff0c;如下&#xff1a; 呈现效果为&#xff1a; <!DOCTYPE html> <html lang"en"><head><me…

AndroidStudio中debug.keystore的创建和配置使用

1.如果没有debug.keystore,可以按照下面方法创建 首先在C:\Users\Admin\.android路径下打开cmd窗口 之后输入命令:keytool -genkey -v -keystore debug.keystore -alias androiddebugkey -keyalg RSA -validity 10000 输入两次密码(密码不可见,打码处随便填写没关系) 2.在build…

恒瑞医药公布创新药卡瑞利珠单抗联合治疗NK/T细胞淋巴瘤研究结果

2024年美国临床肿瘤学会&#xff08;ASCO&#xff09;年会上&#xff0c;由华中科技大学同济医学院附属协和医院张利玲教授团队开展的“卡瑞利珠单抗联合培门冬酶、依托泊苷和大剂量甲氨蝶呤治疗NK/T细胞淋巴瘤患者的II期前瞻性研究(CLAMP研究)”结果入选大会口头报告。北京时间…

Angular17(1):使用Angular CLI创建空项目

要创建一个空的 Angular 项目&#xff0c;可以使用 Angular CLI&#xff08;命令行界面&#xff09;。以下是使用 Angular CLI 创建一个新项目的步骤&#xff1a; 1、安装 Angular CLI&#xff1a; 打开你的命令行界面&#xff08;在 Windows 上是 CMD、PowerShell 或 Git Bas…

IDCF五周年专场—【研发效能·创享大会】圆满落幕!

2024 年5 月25 日&#xff0c;【研发效能创享大会】—IDCF五周年专场在北京希尔顿欢朋酒店&#xff08;大红门&#xff09;成功举办&#xff01;本次大会旨在为社区成员提供一个学习与交流的平台&#xff0c;分享技术经验&#xff0c;交流行业见解&#xff0c;促进技术合作与创…

Socket编程学习笔记之TCP与UDP

Socket&#xff1a; Socket是什么呢&#xff1f; 是一套用于不同主机间通讯的API&#xff0c;是应用层与TCP/IP协议族通信的中间软件抽象层。 是一组接口。在设计模式中&#xff0c;Socket其实就是一个门面模式&#xff0c;它把复杂的TCP/IP协议族隐藏在Socket接口后面&#…

记录第一次http转https

之前小程序用的后端是咸虾米老师的&#xff0c;昨天写小程序就想着自己又不是不会写&#xff1f;用自己的吧&#xff0c;然后发现微信小程序要域名是https协议的。看来又得学新东西了Q-Q 查了下大概要这么几个步骤 1.购买ssl证书 2.通过naginx配置ssl证书 3.将以前的http重定…

【阿里前端面试题】聊聊前端性能优化的方案,解决过什么样的性能问题?

大家好&#xff0c;我是“寻找DX3906”。每天进步一点。日积月累&#xff0c;有朝一日定会厚积薄发&#xff01; 前言&#xff1a; 前面已经和大家分享了4篇面试题&#xff1a; 《【阿里前端面试题】浏览器的加载渲染过程》 《【阿里前端面试题】客户端和服务器交互&#xff…

一键开启:盲盒小程序里的梦幻奇遇

在繁忙的都市生活中&#xff0c;每个人心中都藏着一个关于奇遇的梦想。如今&#xff0c;我们为您精心打造了一款盲盒小程序——“梦幻奇遇”&#xff0c;只需一键开启&#xff0c;就能带您走进一个充满无限惊喜和梦幻色彩的奇幻世界。 一、神秘盲盒&#xff0c;惊喜连连 “梦幻…

【成品设计】基于华大hc32F005c6ua的读取NFC卡

《基于华大hc32F005c6ua的读取NFC卡》 整体功能&#xff1a; 单片机:华大hc32F005c6ua 1、支持单片机spi接口读取nfc读卡器芯片rc522读写数据 2、读取到的数据可以通过单片机uart接口通信&#xff0c;上报给上位机&#xff08;485主机&#xff09; 3、uart接口支持modbus协议…

派派派森03

1.JSON数据 Python数据和Json数据的相互转化 # 导入json模块 import json#准备符合json格式要求的python数据 data [{"name": "老王", "age": 16}, {"name": "张三", "age": 20}]# 通过json.dump(data)方法把pyt…

【5】MySQL数据库备份-XtraBackup - 全量备份

MySQL数据库备份-XtraBackup-全量备份 前言环境版本 安装部署下载RPM 包二进制包 安装卸载 场景分析全量备份 | 恢复备份恢复综合 增量备份 | 恢复部分备份 | 恢复 前言 关于数据库备份的一些常见术语、工具等&#xff0c;可见《MySQL数据库-备份》章节&#xff0c;当前不再重…

windows操作系统提权之服务提权实战rottenpotato

RottenPotato&#xff1a; 将服务帐户本地提权至SYSTEM load incognito list_tokens –u upload /home/kali/Desktop rottenpotato.exe . execute -Hc -f rottenpotato.exe impersonate_token "NT AUTHORITY\SYSTEM" load incognito 这条命令用于加载 Metasploi…

用cocos2d-python绘制游戏开发的新篇章

用cocos2d-python绘制游戏开发的新篇章 第一部分&#xff1a;背景 在游戏开发的世界中&#xff0c;寻找一个强大而灵活的框架至关重要。cocos2d-python是一个Python游戏开发框架&#xff0c;它提供了一套丰富的功能&#xff0c;用于创建2D游戏、图形和交互式应用。基于流行的c…

计算机网络——TCP / IP 网络模型

OSI 七层模型 七层模型是国际标准化的一个网络分层模型&#xff0c;大体结构可以分成七层。每层提供不同的功能。 图片来源 JavaGuide 但是这样七层结构比较复杂&#xff0c;不太实用&#xff0c;所以有了 TCP / IP 模型。 TCP / IP 网络模型 TCP / IP 网络模型可以看作是 O…