mysql optimizer_switch : 查询优化器优化策略深入解析

2024-06-07 21:52

本文主要是介绍mysql optimizer_switch : 查询优化器优化策略深入解析,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

码到三十五 : 个人主页

在 MySQL 数据库中,查询优化器是一个至关重要的组件,它负责确定执行 SQL 查询的最有效方法。为了提供DBA和开发者更多的灵活性和控制权,MySQL 引入了 optimizer_switch 系统变量。这个强大的工具允许用户开启或关闭特定的优化策略,从而可以根据具体的工作负载和数据分布调整查询的执行计划。

目录

      • optimizer_switch 的概念
        • 查看当前的优化器标志集
        • 修改optimizer_switch的值
      • 主要优化标志介绍
      • 如何使用 optimizer_switch
      • 注意事项和最佳实践
      • 结论

optimizer_switch 的概念

optimizer_switch 是一个由多个标志组成的字符串,每个标志控制一个特定的优化器行为。这些标志可以被设置为 onoff,以启用或禁用相应的优化策略。通过调整这些标志,数据库管理员可以精细地控制查询优化器的行为,以达到最佳的性能表现。

ptimizer_switch系统变量可以控制优化器行为。它的值是一组标志,每个标志都有一个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的值

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

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

每个命令值应该具有下表所示的形式之一:
在这里插入图片描述

该值中命令的顺序并不重要,但如果存在,默认命令将首先执行。将opt_name标志设置为default将其设置为on或off中的任意一个为其默认值。不允许在值中多次指定任何给定的opt_name,这会导致错误。该值中的任何错误都会导致赋值失败,并导致optimizer_switch的值保持不变。

主要优化标志介绍

  1. index_merge

    index_merge 控制是否允许索引合并优化。当查询条件可以通过多个索引来满足时,MySQL 可以合并这些索引以更有效地检索数据。在复杂查询中,这可以显著提高性能。

  2. index_condition_pushdown (ICP)

    ICP 允许将 WHERE 子句中的条件推送到存储引擎层进行处理。这减少了存储引擎需要返回给优化器的数据量,因为它可以在检索数据时就过滤掉不符合条件的行。

  3. materialization

    当查询包含子查询时,materialization 标志控制是否将子查询的结果物化(即临时存储)。物化子查询可以减少重复计算,但也可能增加内存使用。

  4. semijoinloosescan

    这两个标志与半连接优化相关。半连接是一种在处理包含 EXISTS 或 IN 子句的查询时特别有效的优化策略。semijoin 控制是否使用这种优化,而 loosescan 则允许在某些情况下进行更高效的扫描。

  5. derived_merge

    当查询中包含派生表(由子查询生成的临时表)时,derived_merge 标志控制是否尝试将这些派生表合并到外部查询中。这可以减少查询的复杂性并提高性能。

  6. exists_to_in

    在某些情况下,将 EXISTS 子句转换为 IN 子句可能会改变查询的执行计划并提高性能。exists_to_in 标志控制是否进行这种转换。

  7. mrr (Multi-Range Read)

    MRR 是一种优化技术,用于改善范围查询和JOIN操作的性能。当设置为on时,MySQL 会尝试使用 MRR 来更有效地从磁盘读取数据。这通常可以减少磁盘I/O,并提高查询速度。

  8. mrr_cost_based

    当此标志设置为on时,MySQL 将基于成本决定是否使用 MRR。如果查询优化器认为使用 MRR 会更有效,那么它就会使用这种技术。否则,它将回退到传统的读取方法。

  9. block_nested_loop

    这个标志控制是否使用块嵌套循环连接(Block Nested Loop Join, BNLJ)。BNLJ 是一种在处理连接操作时减少I/O次数的方法。当设置为on时,MySQL 将考虑使用 BNLJ 来优化连接操作。

  10. batched_key_access

当此标志启用时,MySQL 会尝试使用批处理键访问(Batched Key Access, BKA)来优化某些类型的 JOIN 操作。BKA 可以减少在 JOIN 操作中访问索引的次数,从而提高性能。

  1. use_index_extensions

这个标志允许优化器使用索引扩展来优化某些类型的查询。索引扩展是一种技术,其中优化器可以使用索引中的额外信息来过滤结果集,而无需回表查找数据行。

  1. condition_fanout_filter

当此标志设置为on时,优化器将尝试使用条件扇出过滤器(Condition Fanout Filter, CFF)来优化查询。CFF 是一种在处理具有多个可能值的列时减少不必要行扫描的技术。

  1. use_invisible_indexes

这个标志控制优化器是否考虑使用标记为“不可见”的索引。在某些情况下,数据库管理员可能希望将索引标记为不可见以进行测试或维护,而不影响现有查询的性能。当此标志设置为on时,即使索引被标记为不可见,优化器也会考虑使用它们。

  1. skip_scan

skip_scan 允许优化器在某些情况下使用跳跃扫描来优化范围查询。跳跃扫描是一种技术,其中优化器可以跳过某些索引条目以更快地找到满足查询条件的条目。

  1. duplicateweedout

在执行某些类型的 JOIN 操作时,可能会出现重复的行。当 duplicateweedout 设置为on时,优化器将尝试在结果集中删除这些重复的行,从而提高查询结果的准确性。

  1. subquery_materialization_cost_based

    当此标志设置为on时,优化器将基于成本决定是否物化子查询。物化子查询是将子查询的结果集存储在临时表中,以便在外部查询中重复使用。这可以提高某些类型查询的性能,但也可能增加内存使用。

如何使用 optimizer_switch

要使用 optimizer_switch,你首先需要查看其当前设置:

SHOW VARIABLES LIKE 'optimizer_switch';

这将返回一个包含所有当前设置的标志及其状态的列表。

要更改设置,你可以使用 SET 语句。例如,要启用 ICP,你可以执行:

SET optimizer_switch='index_condition_pushdown=on';

注意,上述命令只会更改当前会话的设置。如果你想全局更改设置,需要使用 GLOBAL 关键字:

SET GLOBAL optimizer_switch='index_condition_pushdown=on';

注意事项和最佳实践

  • 在更改 optimizer_switch 设置之前,最好先在测试环境中验证更改的效果。
  • 不是所有的优化标志都适用于所有版本的 MySQL。在更改设置之前,请查阅相关文档以确保你了解每个标志的具体行为和限制。
  • 避免在生产环境中盲目更改设置。应该基于实际的性能分析和测试来做出决策。
  • 监控数据库的性能指标,以便及时发现并解决潜在问题。

结论

optimizer_switch 是一个强大的工具,允许数据库管理员和开发者精细地控制 MySQL 查询优化器的行为。合理地调整这些设置,可以提高数据库的性能并优化查询效率。使用时也要谨慎并基于充分的测试和分析。


听说...关注下面公众号的人都变牛了,纯技术,纯干货 !

这篇关于mysql optimizer_switch : 查询优化器优化策略深入解析的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1040402

相关文章

网页解析 lxml 库--实战

lxml库使用流程 lxml 是 Python 的第三方解析库,完全使用 Python 语言编写,它对 XPath表达式提供了良好的支 持,因此能够了高效地解析 HTML/XML 文档。本节讲解如何通过 lxml 库解析 HTML 文档。 pip install lxml lxm| 库提供了一个 etree 模块,该模块专门用来解析 HTML/XML 文档,下面来介绍一下 lxml 库

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

【前端学习】AntV G6-08 深入图形与图形分组、自定义节点、节点动画(下)

【课程链接】 AntV G6:深入图形与图形分组、自定义节点、节点动画(下)_哔哩哔哩_bilibili 本章十吾老师讲解了一个复杂的自定义节点中,应该怎样去计算和绘制图形,如何给一个图形制作不间断的动画,以及在鼠标事件之后产生动画。(有点难,需要好好理解) <!DOCTYPE html><html><head><meta charset="UTF-8"><title>06

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

深入探索协同过滤:从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤(UserCF)2. 基于物品的协同过滤(ItemCF)3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代,推荐系统成为连接用户与内容的桥梁。本文聚焦于

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传