【MySQL精通之路】查询优化器的使用(8)

news/2024/7/20 18:36:21/文章来源:https://blog.csdn.net/qq_29519041/article/details/139161354

MySQL通过影响查询计划评估方式的系统变量可切换优化优化器索引提示以及优化器成本模型提供优化器控制。

服务器在column_statistics数据字典表中维护有关列值的直方图统计信息(请参阅第10.9.6节“Optimizer统计信息”)。与其他数据字典表一样,用户无法直接访问此表。相反,您可以通过查询information_SCHEMA来获取直方图信息。COLUMN_STATISTICS,它被实现为数据字典表上的视图。您还可以使用ANALYZE TABLE语句执行直方图管理。

1.控制查询计划评估

        查询优化器的任务是找到执行SQL查询的最佳计划。由于“好”和“坏”计划之间的性能差异可能是几个数量级(即秒与小时甚至天),因此大多数查询优化器,包括MySQL的查询优化器,都会在所有可能的查询评估计划中或多或少地搜索最优计划

        对于联接查询,MySQL优化器调查的可能计划的数量随着查询中引用的表的数量呈指数级增长。对于少量的表(通常少于7到10个),这不是问题。然而,当提交更大的查询时,用于查询优化的时间很容易成为服务器性能的主要瓶颈

        一种更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的详尽程度。一般的想法是,优化器调查的计划越少,编译查询所花费的时间就越少。另一方面,由于优化器跳过了一些计划,它可能无法找到最佳计划。

优化器相对于其评估的计划数量的行为可以使用两个系统变量进行控制:

optimizer_prune_level变量告诉优化器根据每个表访问的行数估计跳过某些计划。

我们的经验表明,这种“有根据的猜测”很少会错过最佳计划,并可能大大减少查询编译时间。这就是为什么默认情况下此选项处于启用状态(optimizer_prune_level=1)。

但是,如果您认为优化器错过了更好的查询计划,则可以关闭此选项(optimizer_prune_level=0),这样可能会导致查询编译耗时更长。

请注意,即使使用了这种启发式方法,优化器仍然会探索大致指数数量的计划

optimizer_search_depth变量告诉优化器应该在每个不完整计划的“未来”中将查看的深度,以评估是否应该进一步扩展。

optimizer_search_depth的值越小,查询编译时间就越小。

例如,如果optimizer_search_depth接近查询中的表数,则具有12个、13个或更多表的查询

可能很容易需要数小时甚至数天才能编译

同时,如果使用等于3或4的optimizer_search_depth进行编译,则优化器可以在不到一分钟的时间内对同一查询进行编译。

如果您不确定optimizer_search_depth的合理值是多少,可以将该变量设置为0,以告诉优化器自动确定该值。

2.可切换优化

优化器开关系统变量可以控制优化器的行为。

它的值是一组标志,每个标志的值为on或off,以指示相应的优化器行为是启用还是禁用

此变量具有全局值会话值,可以在运行时更改。

全局默认值可以在服务器启动时设置。

要查看当前的优化器标志集,请选择变量值:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

 要更改optimizer_switch的值,请指定一个由一个或多个命令的逗号分隔列表组成的值:

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

每个命令值都应具有下表所示的其中一种形式。

命令意义
default将每个优化重置为其默认值
opt_name=default将命名优化设置为其默认值
opt_name=off禁用命名优化
opt_name=on启用命名优化

值中命令的顺序无关紧要,尽管默认命令会首先执行(如果存在)。

将opt_name标志设置为default会将其设置为on或off中的默认值。

不允许在值中多次指定任何给定的opt_name,这会导致错误。

值中的任何错误都会导致赋值失败并出现错误,使优化器开关的值保持不变。


以下列表描述了按优化策略分组的允许的opt_name标志名称:

2.1 批处理密钥访问标志

batched_key_access(默认关闭)

控制BKA联接算法的使用。

batched_key_access在设置为on时要有任何效果,mrr标志也必须为on。

目前,mrr的成本估计过于悲观。因此,也有必要关闭mrr_cost_based以使用BKA

有关更多信息,请参阅“块嵌套循环和批处理Key访问连接”。

【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接-CSDN博客

2.2 块嵌套循环标志

block_nested_roop(默认启用)

控制BNL联接算法的使用。

在MySQL 8.0.18及更高版本中,这也控制了散列联接的使用,BNLNO_BNL优化器提示也是如此。

在MySQL 8.0.20及更高版本中,从MySQL服务器中删除了块嵌套循环支持,该标志仅控制散列联接的使用,引用的优化器提示也是如此。

有关更多信息,请参阅“块嵌套循环和批处理Key访问连接”。

【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接-CSDN博客

2.3 条件筛选标志

condition-fanout-filter(默认打开)

控制条件筛选的使用。

【MySQL精通之路】SQL优化(1)-查询优化(13)-条件过滤-CSDN博客

未完待续。。。

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

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

相关文章

Matlab-熵权法

文章目录 熵权法一、模型简介二、例题1. 数据标准化2.指标的熵值和变异程度3.权重与评分4.代码实现 熵权法 提示:这里可以添加本文要记录的大概内容: 例如:随着人工智能的不断发展,机器学习这门技术也越来越重要,很多…

修改了vue3 <script setup>留言板

Лунная ночь <template><button class"edit_view_checkbox"><input type"checkbox" v-model"editshowInput" value"编辑" /></button><div class"editshowInput" v-if"editshowI…

线性回归计算举例

使用正规方程计算&#xff08;一元线性回归&#xff09; import numpy as np import matplotlib.pyplot as plt # 转化成矩阵 X np.linspace(0, 10, num 30).reshape(-1, 1) # 斜率和截距&#xff0c;随机生成 w np.random.randint(1, 5, size 1) b np.random.randint(1,…

骆驼大赛

目录 一&#xff0c;主版图 二&#xff0c;骰子 三&#xff0c;初始设置 四&#xff0c;核心规则 五&#xff0c;结算 这是适合5-8人玩的一个概率推理类的回合制桌游。 一&#xff0c;主版图 赛道由16个格子组成&#xff0c;编号为1-16。 一共7个骆驼&#xff0c;其中正…

基于 Java 的浏览器——JxBrowser使用分享

软件介绍 JxBrowser 是一个基于 Java 的浏览器&#xff0c;它使用 Chromium 引擎来提供高性能的网页渲染和丰富的功能。它支持多种 GUI 框架&#xff0c;如 Swing、JavaFX 和 SWT&#xff0c;使得在 Java 应用程序中嵌入浏览器组件变得简单。 JxBrowser 是一个适用于多种用途…

MySQL索引和视图

MySQL索引和视图是关系型数据库MySQL中的两个重要概念。索引用于优化数据库的查询性能&#xff0c;而视图用于提供一个逻辑上的表结构&#xff0c;方便用户查询和操作数据。 索引是一种数据结构&#xff0c;可以加速对数据库表中的数据进行查询的速度。通过创建索引&#xff0…

基于QEMU-aarch64学习UEFI(EDK2)-7Print打印函数

1 基于QEMU-aarch64学习UEFI(EDK2)-7Print打印函数 文章目录 1 基于QEMU-aarch64学习UEFI(EDK2)-7Print打印函数1.1 Print打印函数输出字符串1.2 Print打印函数其他用法程序开发我们以 edk2-stable202302版本为准。 1.1 Print打印函数输出字符串 我们把edk2/MdeModulePkg/App…

Py列表(list)

目录 正向索引&#xff1a; 反向索引&#xff1a; 嵌套列表&#xff1a; 修改列表中的值 列表常用的方法 实例 练习&#xff1a; 正向索引&#xff1a; 从0开始&#xff0c;依次递增。第一个元素的索引为0&#xff0c;第二个元素的索引为1&#xff0c;依此类推。 列表的下标…

Windows 使用技巧

Windows 使用技巧 ①局域网内共享文件 ②CTRL Y 和 CTRL Z ①局域网内共享文件 第一步&#xff1a; 选择要共享的文件&#xff08;分享方操作&#xff09; 第二步&#xff1a; 右键打开属性&#xff0c;选择共享&#xff08;分享方操作&#xff09; 第三步&#xff1a; …

redis数据类型set,zset

华子目录 Set结构图相关命令sdiff key1 [key2]sdiffstore destination key1 [key2...]sinter key1 [key2...]sinterstore destination key1 [key2...]sunion key1 [key2...]sunionstore destination key1 [key2...]smove source destination memberspop key [count]sscan key c…

使用Word表格数据快速创建图表

实例需求&#xff1a;Word的表格如下所示&#xff0c;标题行有合并单元格。 现在需要根据上述表格数据&#xff0c;在Word中创建如下柱图。如果数据在Excel之中&#xff0c;那么创建这个图并不复杂&#xff0c;但是Word中就没用那么简单了&#xff0c;虽然Word中可以插入图表&a…

[IMX6ULL驱动开发]-Linux对中断的处理(二)

上一篇文章中&#xff0c;引入了Linux对于中断的一些简略流程以及中断抽象为具体实际形象。此文章主要是继续加深对Linux对中断的处理流程以及一些相应的数据结构。 目录 Linux对中断的扩展&#xff1a;硬件中断、软件中断 多中断处理 中断上下部处理流程 发生中断A&#…

Golang | Leetcode Golang题解之第104题二叉树的最大深度

题目&#xff1a; 题解&#xff1a; func maxDepth(root *TreeNode) int {if root nil {return 0}queue : []*TreeNode{}queue append(queue, root)ans : 0for len(queue) > 0 {sz : len(queue)for sz > 0 {node : queue[0]queue queue[1:]if node.Left ! nil {queue…

第十四届蓝桥杯c++研究生组

A 关键思路是求每个十进制数的数字以及怎么在一个数组中让判断所有的数字次数相等。 求每个十进制的数字 while(n!0){int x n%10;//x获取了n的每一个位数字n/10;}扩展&#xff1a;求二进制的每位数字 &#xff08;注意&#xff1a;进制转换、1的个数、位运算&#xff09; x…

骨折分类数据集1129张10类别

数据集类型&#xff1a;图像分类用&#xff0c;不可用于目标检测无标注文件 数据集格式&#xff1a;仅仅包含jpg图片&#xff0c;每个类别文件夹下面存放着对应图片 图片数量(jpg文件个数)&#xff1a;1129 分类类别数&#xff1a;10 类别名称:["avulsion_fracture",…

景源畅信:抖音小店新手小白如何做好运营?

在数字时代的浪潮中&#xff0c;抖音小店成为了众多创业者和商家的新宠。但面对激烈的市场竞争和不断变化的平台规则&#xff0c;新手小白如何才能在抖音小店的海洋里稳健航行&#xff0c;捕捉到属于自己的商机呢?接下来的内容将为你揭晓答案。 一、精准定位&#xff0c;明确目…

常见开源蜜罐系统

蜜罐系统&#xff08;Honeypot&#xff09;在信息安全领域中是一种被广泛使用的技术&#xff0c;旨在吸引和诱导黑客入侵&#xff0c;从而获取和分析攻击者的行为和手段。以下是一些常见的蜜罐系统的介绍&#xff1a; HFish开源蜜罐系统 特点&#xff1a; 多功能&#xff1a;支…

Windows hook介绍与代码演示

Windows Hook 是一种机制&#xff0c;允许应用程序监视系统或处理特定事件。它可以拦截和更改消息&#xff0c;甚至可以插入到其他应用程序的消息处理机制中。Windows 提供了多种挂钩类型&#xff0c;例如键盘挂钩、鼠标挂钩、消息挂钩等。 hook代码实现 下面是一个使用 Wind…

就说说Java初学者求职准备项目的正确方式

当下不少Java初学者也知道求职时项目的重要程度&#xff0c;但在简历上写项目和准备面试项目时&#xff0c;真有可能走弯路&#xff0c;这样的话&#xff0c;加重学习负担还是小事&#xff0c;还真有可能导致无法入职。 1 对于在校生和应届生来说&#xff0c;你去跑通个学习项…

pillow学习3

Pillow库中&#xff0c;图像的模式代表了图像的颜色空间。以下是一些常见的图像模式及其含义&#xff1a; L&#xff08;灰度图&#xff09;&#xff1a;L模式表示图像是灰度图像&#xff0c;每个像素用8位表示&#xff08;范围为0-255&#xff09;&#xff0c;0表示黑色&#…