MySQL优化器

优化器

MySQL存储引擎中存在了一个可插拔的优化器OPTIMIZER_TRACE，可以看到内部查询计划的TRACE信息，从而可以知道MySQL内部执行过程

查询优化器状态

show variables like 'optimizer_trace';

Variable_name   Value
optimizer_trace enabled=off,one_line=off

开启优化器

set session optimizer_trace="enabled=on,one_line=on",end_markers_in_json=on;

查看优化器追踪内存大小

show variables like 'optimizer_trace_max_mem_size';

Variable_name                 Value
optimizer_trace_max_mem_size 16384

可以在information_schema数据库中的OPTIMIZER_TRACE查看sql执行情况

SELECT trace FROM information_schema.OPTIMIZER_TRACE;

示例：

 explain select id,sum(cost) from jr_form where id in (
select max(id) from jr_form group by jr_code) group by id;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

这里注意一下，我在Navicat中执行时如果两个语句分开执行会导致TRACE中内容为

错误的Trace操作
错误的Trace操作

需要选中两条sql语句一块执行才可以

trace结果为

{
    "steps": [
        {
            "join_preparation": {
                "select#": 1,
                "steps": [
                    {
                        "join_preparation": {
                            "select#": 2,
                            "steps": [
                                {
                                    "expanded_query": "/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code`"
                                },
                                {
                                    "transformation": {
                                        "select#": 2,
                                        "from": "IN (SELECT)",
                                        "to": "semijoin",
                                        "chosen": false
                                    }
                                },
                                {
                                    "transformation": {
                                        "select#": 2,
                                        "from": "IN (SELECT)",
                                        "to": "EXISTS (CORRELATED SELECT)",
                                        "chosen": true,
                                        "evaluating_constant_having_conditions": []
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "expanded_query": "/* select#1 */ select `jr_form`.`id` AS `id`,sum(`jr_form`.`cost`) AS `sum(cost)` from `jr_form` where <in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`))))) group by `jr_form`.`id`"
                    }
                ]
            }
        },
        {
            "join_optimization": {
                "select#": 1,
                "steps": [
                    {
                        "condition_processing": {
                            "condition": "WHERE",
                            "original_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))",
                            "steps": [
                                {
                                    "transformation": "equality_propagation",
                                    "subselect_evaluation": [],
                                    "resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "subselect_evaluation": [],
                                    "resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "subselect_evaluation": [],
                                    "resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns": {}
                    },
                    {
                        "table_dependencies": [
                            {
                                "table": "`jr_form`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                        "ref_optimizer_key_uses": []
                    },
                    {
                        "rows_estimation": [
                            {
                                "table": "`jr_form`",
                                "const_keys_added": {
                                    "keys": [
                                        "PRIMARY",
                                        "jr_code"
                                    ],
                                    "cause": "group_by"
                                },
                                "range_analysis": {
                                    "table_scan": {
                                        "rows": 184,
                                        "cost": 44.9
                                    },
                                    "potential_range_indexes": [
                                        {
                                            "index": "PRIMARY",
                                            "usable": true,
                                            "key_parts": [
                                                "id"
                                            ]
                                        },
                                        {
                                            "index": "jr_code",
                                            "usable": true,
                                            "key_parts": [
                                                "jr_code",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "setup_range_conditions": [],
                                    "group_index_range": {
                                        "chosen": false,
                                        "cause": "not_applicable_aggregate_function"
                                    }
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [],
                                "table": "`jr_form`",
                                "best_access_path": {
                                    "considered_access_paths": [
                                        {
                                            "rows_to_scan": 184,
                                            "access_type": "scan",
                                            "resulting_rows": 184,
                                            "cost": 42.8,
                                            "chosen": true,
                                            "use_tmp_table": true
                                        }
                                    ]
                                },
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 184,
                                "cost_for_plan": 42.8,
                                "sort_cost": 184,
                                "new_cost_for_plan": 226.8,
                                "chosen": true
                            }
                        ]
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))",
                            "attached_conditions_computation": [],
                            "attached_conditions_summary": [
                                {
                                    "table": "`jr_form`",
                                    "attached": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"
                                }
                            ]
                        }
                    },
                    {
                        "clause_processing": {
                            "clause": "GROUP BY",
                            "original_clause": "`jr_form`.`id`",
                            "items": [
                                {
                                    "item": "`jr_form`.`id`"
                                }
                            ],
                            "resulting_clause_is_simple": true,
                            "resulting_clause": "`jr_form`.`id`"
                        }
                    },
                    {
                        "reconsidering_access_paths_for_index_ordering": {
                            "clause": "GROUP BY",
                            "index_order_summary": {
                                "table": "`jr_form`",
                                "index_provides_order": true,
                                "order_direction": "asc",
                                "index": "PRIMARY",
                                "plan_changed": true,
                                "access_type": "index"
                            }
                        }
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`jr_form`"
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_optimization": {
                "select#": 2,
                "steps": [
                    {
                        "condition_processing": {
                            "condition": "HAVING",
                            "original_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))",
                            "steps": [
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns": {}
                    },
                    {
                        "table_dependencies": [
                            {
                                "table": "`jr_form`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                        "rows_estimation": [
                            {
                                "table": "`jr_form`",
                                "const_keys_added": {
                                    "keys": [
                                        "jr_code"
                                    ],
                                    "cause": "group_by"
                                },
                                "range_analysis": {
                                    "table_scan": {
                                        "rows": 184,
                                        "cost": 44.9
                                    },
                                    "potential_range_indexes": [
                                        {
                                            "index": "PRIMARY",
                                            "usable": false,
                                            "cause": "not_applicable"
                                        },
                                        {
                                            "index": "jr_code",
                                            "usable": true,
                                            "key_parts": [
                                                "jr_code",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "best_covering_index_scan": {
                                        "index": "jr_code",
                                        "cost": 41.319,
                                        "chosen": true
                                    },
                                    "group_index_range": {
                                        "potential_group_range_indexes": [
                                            {
                                                "index": "jr_code",
                                                "covering": true,
                                                "rows": 152,
                                                "cost": 64.8
                                            }
                                        ]
                                    },
                                    "best_group_range_summary": {
                                        "type": "index_group",
                                        "index": "jr_code",
                                        "group_attribute": "id",
                                        "min_aggregate": false,
                                        "max_aggregate": true,
                                        "distinct_aggregate": false,
                                        "rows": 152,
                                        "cost": 64.8,
                                        "key_parts_used_for_access": [
                                            "jr_code"
                                        ],
                                        "ranges": [],
                                        "chosen": false,
                                        "cause": "cost"
                                    }
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [],
                                "table": "`jr_form`",
                                "best_access_path": {
                                    "considered_access_paths": [
                                        {
                                            "rows_to_scan": 184,
                                            "access_type": "scan",
                                            "resulting_rows": 184,
                                            "cost": 42.8,
                                            "chosen": true,
                                            "use_tmp_table": true
                                        }
                                    ]
                                },
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 184,
                                "cost_for_plan": 42.8,
                                "sort_cost": 184,
                                "new_cost_for_plan": 226.8,
                                "chosen": true
                            }
                        ]
                    },
                    {
                        "transformation": {
                            "select#": 2,
                            "from": "IN (SELECT)",
                            "to": "materialization",
                            "has_nullable_expressions": true,
                            "treat_UNKNOWN_as_FALSE": true,
                            "possible": true
                        }
                    },
                    {
                        "execution_plan_for_potential_materialization": {
                            "surely_same_plan_as_EXISTS": true,
                            "cause": "EXISTS_did_not_change_WHERE",
                            "subq_mat_decision": {
                                "parent_fanouts": [
                                    {
                                        "select#": 1,
                                        "subq_attached_to_table": true,
                                        "table": "`jr_form`",
                                        "fanout": 184,
                                        "cacheable": true
                                    }
                                ],
                                "cost_to_create_and_fill_materialized_table": 265.6,
                                "cost_of_one_EXISTS": 226.8,
                                "number_of_subquery_evaluations": 184,
                                "cost_of_materialization": 302.4,
                                "cost_of_EXISTS": 41731,
                                "chosen": true
                            }
                        }
                    },
                    {
                        "transformation": {
                            "select#": 2,
                            "from": "IN (SELECT)",
                            "to": "materialization",
                            "chosen": true,
                            "unknown_key_1": {
                                "creating_tmp_table": {
                                    "tmp_table_info": {
                                        "row_length": 9,
                                        "key_length": 9,
                                        "unique_constraint": false,
                                        "location": "memory (heap)",
                                        "row_limit_estimate": 1864135
                                    }
                                }
                            }
                        }
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": null,
                            "attached_conditions_computation": [],
                            "attached_conditions_summary": [
                                {
                                    "table": "`jr_form`",
                                    "attached": null
                                }
                            ]
                        }
                    },
                    {
                        "clause_processing": {
                            "clause": "GROUP BY",
                            "original_clause": "`jr_form`.`jr_code`",
                            "items": [
                                {
                                    "item": "`jr_form`.`jr_code`"
                                }
                            ],
                            "resulting_clause_is_simple": true,
                            "resulting_clause": "`jr_form`.`jr_code`"
                        }
                    },
                    {
                        "reconsidering_access_paths_for_index_ordering": {
                            "clause": "GROUP BY",
                            "index_order_summary": {
                                "table": "`jr_form`",
                                "index_provides_order": true,
                                "order_direction": "asc",
                                "index": "jr_code",
                                "plan_changed": false
                            }
                        }
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`jr_form`"
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_explain": {
                "select#": 1,
                "steps": [
                    {
                        "join_explain": {
                            "select#": 2,
                            "steps": []
                        }
                    }
                ]
            }
        }
    ]
}

内容很长，之后在详细说明

https://zhhll.icu/2021/数据库/关系型数据库/MySQL/进阶/25.优化器/

本文由 mdnice 多平台发布

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

如若内容造成侵权/违法违规/事实不符，请联系瞎胡闹网进行投诉反馈，一经查实，立即删除！

相关文章

[C++] opencv + qt 创建带滚动条的图像显示窗口代替imshow

[C++] opencv + qt 创建带滚动条的图像显示窗口代替imshow

在OpenCV中&#xff0c;imshow函数默认情况下是不支持滚动条的。如果想要显示滚动条&#xff0c;可以考虑使用其他库或方法来进行实现。 一种方法是使用Qt库&#xff0c;使用该库可以创建一个带有滚动条的窗口&#xff0c;并在其中显示图像。具体步骤如下&#xff1a; 1&…
阅读更多...
【翻译】Processing安卓模式的安装使用及打包发布（内含中文版截图）

【翻译】Processing安卓模式的安装使用及打包发布（内含中文版截图）

原文链接在下面的每一章的最前面。 原文有三篇&#xff0c;译者不知道贴哪篇了&#xff0c;这篇干脆标了原创。。 译者声明&#xff1a;本文原文来自于GNU协议支持下的项目&#xff0c;具备开源二改授权&#xff0c;可翻译后公开。 文章目录 Install&#xff08;安装&#xff0…
阅读更多...
路由引入路由过滤排错

路由引入路由过滤排错

目录 排错网络拓扑图 排错需求 故障排错 故障一 故障二 故障三 排错网络拓扑图 排错需求 按照图示配置 IP 地址&#xff0c;总部和分支 A、分支 B 各自使用 loopback 口模拟业务网段公司业务流分为 A 流和 B 流&#xff0c;网段如图所示总部内部配置 OSPF 互通&#xff0…
阅读更多...
Frostmourne (霜之哀伤)日志告警系统部署安装

Frostmourne (霜之哀伤)日志告警系统部署安装

简介 Frostmourne(霜之哀伤)是汽车之家经销商技术部监控系统的开源版本&#xff0c;用于帮助监控几乎所有数据库数据(包括Elasticsearch, Prometheus, SkyWalking, MySql 等等)。如果你已经建立起了日志系统&#xff0c; 指标体系&#xff0c;却苦恼于没有一个配套监控系统&am…
阅读更多...
【芯片设计- RTL 数字逻辑设计入门 15 -- 函数实现数据大小端转换】

【芯片设计- RTL 数字逻辑设计入门 15 -- 函数实现数据大小端转换】

文章目录 函数实现数据大小端转换函数语法函数使用的规则Verilog and Testbench综合图VCS 仿真波形 函数实现数据大小端转换 在数字芯片设计中&#xff0c;经常把实现特定功能的模块编写成函数&#xff0c;在需要的时候再在主模块中调用&#xff0c;以提高代码的复用性和提高设…
阅读更多...
「递归算法」：二叉树剪枝

「递归算法」：二叉树剪枝

一、题目 给你二叉树的根结点 root &#xff0c;此外树的每个结点的值要么是 0 &#xff0c;要么是 1 。 返回移除了所有不包含 1 的子树的原二叉树。 节点 node 的子树为 node 本身加上所有 node 的后代。 示例 1&#xff1a; 输入&#xff1a;root [1,null,0,0,1] 输出&…
阅读更多...
编曲学习：旋律创作基础概念 和弦进行作曲 和弦外音使用 作曲技巧

编曲学习：旋律创作基础概念 和弦进行作曲 和弦外音使用 作曲技巧

旋律创作基础概念 和弦进行作曲 和弦外音使用 作曲技巧https://app8epdhy0u9502.pc.xiaoe-tech.com/live_pc/l_65be1ba7e4b064a83b92a3d7?course_id=course_2XLKtQnQx9GrQHac7OPmHD9tqbv文档https://app8epdhy0u9502.pc.xiaoe-tech.com/p/t_pc/course_pc_detail/camp_pro/cour…
阅读更多...
Java实现用户画像活动推荐系统 JAVA+Vue+SpringBoot+MySQL

Java实现用户画像活动推荐系统 JAVA+Vue+SpringBoot+MySQL

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 数据中心模块2.2 兴趣标签模块2.3 活动档案模块2.4 活动报名模块2.5 活动留言模块 三、系统设计3.1 用例设计3.2 业务流程设计3.3 数据流程设计3.4 E-R图设计 四、系统展示五、核心代码5.1 查询兴趣标签5.2 查询活动推荐…
阅读更多...
算法练习-二叉搜索树中的搜索（思路+流程图+代码）

算法练习-二叉搜索树中的搜索（思路+流程图+代码）

难度参考 难度&#xff1a;中等 分类&#xff1a;二叉树 难度与分类由我所参与的培训课程提供&#xff0c;但需要注意的是&#xff0c;难度与分类仅供参考。且所在课程未提供测试平台&#xff0c;故实现代码主要为自行测试的那种&#xff0c;以下内容均为个人笔记&#xff0c;旨…
阅读更多...
活字格V9 嵌入的html与活字格页面数据交互

活字格V9 嵌入的html与活字格页面数据交互

不想看分析请直接跳到解决方案 项目场景&#xff1a; 活字格V9 嵌入的html与活字格页面的数据交互&#xff08;传值&#xff09;&#xff0c;嵌入的html用了WebSocket来控制硬件&#xff0c;获取的数据无法回传到活字格页面上&#xff0c;且嵌入的html无法使用活字格内置的js及…
阅读更多...
第三百一十回

第三百一十回

我们在上一章回中介绍了"再谈ListView中的分隔线"&#xff0c;本章回中将介绍showMenu的用法.闲话休提&#xff0c;让我们一起Talk Flutter吧。 1. 概念介绍 我们在第一百六十三回中介绍了showMenu相关的内容&#xff0c;它主要用来显示移动PopupMenu在页面中的位置…
阅读更多...
Git、github与gitee码云

Git、github与gitee码云

1.git核心是两个仓库&#xff1a;本地仓库和远程仓库 主要用于团队合作和代码版本控制&#xff08;个人现有版本代码出错可回溯上个提交版本的代码&#xff09; 远程仓库国际主流githut&#xff0c;但外网速度问题&#xff0c;国内可使用码云gitee github&#xff1a;https:…
阅读更多...
【开源】JAVA+Vue.js实现在线课程教学系统

【开源】JAVA+Vue.js实现在线课程教学系统

目录 一、摘要1.1 系统介绍1.2 项目录屏 二、研究内容2.1 课程类型管理模块2.2 课程管理模块2.3 课时管理模块2.4 课程交互模块2.5 系统基础模块 三、系统设计3.1 用例设计3.2 数据库设计 四、系统展示4.1 管理后台4.2 用户网页 五、样例代码5.1 新增课程类型5.2 网站登录5.3 课…
阅读更多...
vue3+vite+ts 配置commit强制码提交规范配置 commitlint

vue3+vite+ts 配置commit强制码提交规范配置 commitlint

配置 git 提交时的 commit 信息&#xff0c;统一提交 git 提交规范 安装命令: npm install -g commitizen npm i cz-customizable npm i commitlint/config-conventional commitlint/cli -D 文件配置 根路径创建文件 commitlint.config.js module.exports {// 继承的规…
阅读更多...
Backtrader 文档学习- Plotting -Plotting on the same axis

Backtrader 文档学习- Plotting -Plotting on the same axis

Backtrader 文档学习- Plotting -Plotting on the same axis 1.概述 在同一轴上绘图&#xff0c;绘图是在同一空间上绘制原始数据和稍微(随机)修改的数据&#xff0c;但不是在同一轴上。 核心代码&#xff0c;data数据正负50点。 # The filter which changes the close pri…
阅读更多...
java面试题：MySQL中的各种JOIN的区别

java面试题：MySQL中的各种JOIN的区别

表关联是频率非常高的一种数据库操作&#xff0c;在MySQL中&#xff0c;这种JOIN操作有很多类型&#xff0c;包括内联接、左外连接、右外连接等等&#xff0c;而每种连接的含义都不一样&#xff0c;如果死记硬背&#xff0c;不仅很难记住&#xff0c;而且也容易搞混淆&#xff…
阅读更多...
JAVA Web 学习（三）Web服务架构

JAVA Web 学习（三）Web服务架构

五、软件架构模式——MVC MVC是一种 分层开发的模式 &#xff0c;其中&#xff1a;M-Model&#xff0c;业务模型&#xff0c;处理业务&#xff1b;V&#xff1a;View&#xff0c;视图&#xff0c;界面展示&#xff1b;C&#xff1a;Controller&#xff0c;控制器&#xff0c;处…
阅读更多...
基于华为云欧拉操作系统(HCE OS)容器化部署传统应用(Redis+Postgresql+Git+SpringBoot+Nginx)

基于华为云欧拉操作系统(HCE OS)容器化部署传统应用(Redis+Postgresql+Git+SpringBoot+Nginx)

写在前面 博文内容为 华为云欧拉操作系统入门级开发者认证(HCCDA – Huawei Cloud EulerOS)实验笔记整理认证地址&#xff1a;https://edu.huaweicloud.com/certificationindex/developer/9bf91efb086a448ab4331a2f53a4d3a1博文内容涉及一个传统 Springboot 应用HCE部署&#x…
阅读更多...
JavaScript 入门 完整版

JavaScript 入门 完整版

目录 第一个知识点&#xff1a;引入js文件 内部引用: 外部引用: 第二个知识点&#xff1a;javascript的基本语法 定义变量&#xff1a; 条件控制(if - else if - else) 第三个知识点&#xff1a;javascript里的数据类型、运算符&#xff1a; 数字类型 字符串类型 布尔…
阅读更多...
新型Black Matter勒索病毒，勒索300万美金

新型Black Matter勒索病毒，勒索300万美金

前言 BlackMatter勒索病毒是一款基于RAAS模式的新型勒索病毒&#xff0c;该勒索病毒组织成立于2021年7月&#xff0c;该勒索病毒黑客组织对外宣称&#xff0c;已经整合了DarkSide、REvil和LockBit等勒索病毒的最佳功能特点。 勒索病毒黑客组织曾表示不会对医疗保健、关键基础设…
阅读更多...
最新文章

Copyright @ 2022~2023