MySQL 查询优化器

于 2024-10-04 18:46:54 发布
阅读量885 收藏 20
点赞数 10
分类专栏： MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40926260/article/details/142424464
版权

文章目录

控制查询计划

https://dev.mysql.com/doc/refman/8.4/en/controlling-query-plan-evaluation.html

在执行SQL前会根据优化器选择执行计划。而查询优化器的任务是找到执行SQL查询的最佳计划。MySQL的查询优化器会在所有可能的查询计划中搜索最优的计划。
在这里插入图片描述
然而生成执行计划和选择最优这一步也是需要时间的，可选的执行计划越多，意味着耗时越长。对于连接查询，MySQL优化器调查的可能计划的数量随着查询中引用的表的数量呈指数级增长。对于少量的表（通常少于7到10个），问题不大。然而，当提交更大的查询时，查询优化所花费的时间很容易成为服务器性能的主要瓶颈。

一种更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的穷举程度。总体思路是，优化器调查的计划越少，编译查询所花费的时间就越少。但是，由于优化器跳过了一些计划，它可能会错过找到最佳计划的机会。

优化器在评估查询计划数量方面的行为可以使用两个系统变量来控制：

optimizer_prune_level

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

经验表明，根据估计的行数跳过某些计划这个策略很少会错过最佳计划，并且可能会大大减少查询编译时间。这就是为什么默认情况下optimizer_prune_level处于启用状态（optimizer_prune_level=1）。但是，如果您认为优化器错过了更好的查询计划，则可以关闭此选项（optimizer_prune_level=0），但存在查询编译可能需要更长时间的风险。请注意，即使使用这种启发式方法，优化器仍然会探索大约指数数量的计划。

optimizer_search_depth

告诉优化器应该在每个不完整计划的“未来”中查看多远，以评估是否应该进一步

optimizer_search_depth的较小值可能会导致查询编译时间减少几个数量级。例如，如果optimizer_search_depth接近查询中的表数，则具有12个、13个或更多表的查询可能很容易需要数小时甚至数天才能编译。同时，如果使用optimizer_search_depth等于3或4进行编译，优化器可能会在不到一分钟的时间内对同一查询进行编译。如果您不确定optimizer_search_depth的合理值是什么，可以将此变量设置为0，以告诉优化器自动确定该值。

优化器参数

https://dev.mysql.com/doc/refman/8.4/en/switchable-optimizations.html

优化器提示

https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html

有时候SQL并没有按照我们想要的的方式执行，比如明明有索引，但是它就没走索引。这个时候可以使用 Hint 给优化器一个提示，一般情况下如果优化器认为Hint给的方案更合理，就会根据Hint提出的方案执行。

通过优化器参数的方式会控制所有后续的查询，如果指向控制单条 sql 的优化选择，就可以借助优化器提示来实现，并且优化器提示的优先级是比优化器参数高的

索引提示

索引提示与优化器提示不同，索引提示为优化器提供了如何在查询处理过程中选择索引的信息。

索引提示只能用于SELECT和UPDATE，多表DELETE语句，不适用于单表DELETE语句。

语法如下，一般跟在表名后面，[]表示可选，{}表示任选一个

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ..
  1. USE INDEX (index_list)告诉优化器只使用其中一个名称对应的索引来查找
  2. IGNORE INDEX (index_list)告诉优化器不使用某个索引
  3. FORCE INDEX：和USE INDEX (index_list)类似

举个例子：下面这条 sql 不走索引

EXPLAIN SELECT * FROM rental ORDER BY rental_date, inventory_id;

在这里插入图片描述

可以通过索引提示来告诉优化器走索引

EXPLAIN SELECT *
FROM rental FORCE INDEX(rental_date) ORDER BY rental_date, inventory_id;

在这里插入图片描述

成本模型

优化器有一组编译好的默认成本常数，可用于做出有关执行计划的决策。mysql数据库下

  • server_cost：优化器对一般服务器操作的成本估算
  • engine_cost：特定存储引擎操作的优化器成本估算

注意：

  1. 成本模型相关的表是支持重新加载的，动态加载存储引擎或者执行FLUSH OPTIMIZER_COSTS这条SQL
  2. 当客户端会话开始时，当前的内存成本估计值将应用于整个会话，直到会话结束。特别是，如果服务器重新读取成本表，则任何更改的估计值仅适用于随后启动的会话。现有会话不受影响。
  3. 成本表对于每个mysql server实例是特定的，服务器不会将成本表的更改复制到副本

server_cost

在这里插入图片描述
server_cost表包含以下列：

  1. cost_name：server_cost表的主键列，成本模型中使用的成本估算的名称。名称不区分大小写。如果服务器在读取此表时无法识别成本名称，则会在错误日志中写入警告。
  2. cost_value：成本估算值。如果该值不是NULL，则服务器将其值用作成本计算。否则，它将使用默认值。DBA可以通过更新此列来更改成本估算。如果服务器在读取此表时发现成本值无效(非正数)，则会向错误日志中写入警告。要覆盖默认成本估算（对于指定为NULL的条目），请将成本设置为非NULL值。要还原为默认值，请将该值设置为NULL。然后执行FLUSH OPTIMIZER_COSTS，告诉服务器重新读取成本表。
  3. last_update：最后一行更新的时间
  4. comment：注释信息
  5. default_value：成本估算的默认值，只读且不可修改

cost_name

下面介绍以下默认的一些 cost_name 值的含义

disk_temptable_create_cost, disk_temptable_row_cost

存储在基于磁盘的存储引擎(InnoDB或MyISAM)中的内部创建的临时表的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器更喜欢使用较少的查询计划。

与相应内存参数（memory_temptable_create_cost、memory_tmptable_row_cost）的默认值相比，这些磁盘参数的默认值较大，因为处理基于磁盘的表的成本较高。

key_compare_cost

比较记录 key 的成本。增加此值会导致比较许多 key 的查询计划的成本计算值更高。例如，与避免使用索引进行排序的查询计划相比，执行文件排序的查询方案成本变得相对更高。

memory_temptable_create_cost, memory_temptable_row_cost

存储在MEMORY存储引擎中的内部创建的临时表的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器更喜欢使用较少的查询计划。

与相应磁盘参数的默认值（disk_temptable_create_cost, disk_temptable_row_cost）相比，这些内存参数的默认值较小，这反映了处理基于内存的表的成本较低。

row_evaluate_cost

扫描一行数据的成本

engine_cost

engine_cost 表示引擎层的成本估计模型，该表包含以下 7 列

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2024-09-17 11:54:41 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2024-09-17 11:54:41 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

  1. engine_name
    此成本项适用的存储引擎的名称。名称不区分大小写。如果该值为默认值，则它适用于所有没有自己的命名条目的存储引擎。如果服务器在读取此表时无法识别引擎名称，则会将警告写入错误日志。

  2. device_type
    此成本估算适用的设备类型。该列旨在为不同的存储设备类型（如硬盘驱动器与固态驱动器）指定不同的成本估算。目前，此信息未被使用，0是唯一允许的值。

  3. cost_name：与 server_cost 表中 cost_name 列相同

  4. cost_value：与 server_cost 表中 cost_value 列相同

  5. last_update：与 server_cost 表中 last_update 列相同

  6. comment：与 server_cost 表中 comment 列相同

  7. default_value：该行的成本默认值，只读且不可修改

engine_cost 表主键是一个组合索引：(cost_name, engine_name, device_type)

cost_name 列有以下值：

  1. io_block_read_cost
    从磁盘读取索引或数据块的成本。表扫描读取数据块较多，而范围扫描读取较少

  2. memory_block_read_cost
    与 io_block_read_cost 相似，但是表示的是从内存数据库缓冲区读取索引或数据块的成本

vonlinee
关注
专栏目录
mysql optimizer_switch : 查询优化器优化策略深入解析
码到三十五
06-07 9546
当查询条件可以通过多个索引来满足时，MySQL 可以合并这些索引以更有效地检索数据。在复杂查询中，这可以显著提高性能。ICP 允许将 WHERE 子句中的条件推送到存储引擎层进行处理。这减少了存储引擎需要返回给优化器的数据量，因为它可以在检索数据时就过滤掉不符合条件的行。当查询包含子查询时，标志控制是否将子查询的结果物化（即临时存储）。物化子查询可以减少重复计算，但也可能增加内存使用。semijoin和loosescan这两个标志与半连接优化相关。
Mysql——》查询优化器
小仙~
03-28 2030
Mysql使用 基于开销（cost）的优化器 ，哪个开销最小就用哪个执行计划。
Mysql查询优化器
caomiao2006的专栏
08-14 1574
Mysql查询优化器 本文的目的主要是通过告诉大家，查询优化器为我们做了那些工作，我们怎么做，才能使查询优化器对我们的sql进行优化，以及启示我们sql语句怎么写，才能更有效率。那么到底mysql到底能进行哪些优化那，下面通过以下几个方面来探讨一下： 1          常量转化 　它能够对sql语句中的常量进行转化，比如下面的表达式：　WHERE col1 = col2
MySQL 查询优化器（转自Popgo的博客）
07-27 127
这篇描述MySQL查询优化器的工作原理。MySQL查询优化器主要为执行的查询决断最有效的路线（routine，走向）。 一。源代码和概念 这部分讨论优化器关键概念，术语，及在MySQL源代码怎么对应的。 1.定义 狭义定义：优化器，就是DBMS为查询时决断要往哪种执行路径的一系列路线。 MySQL是经常调整查询的路线，所以你得把这篇描述的逻辑和在源代码里的做比较。为了使比较...
查询优化器
weixin_34082695的博客
06-30 164
查询优化器查询优化器是关系型数据库管理系统的核心之一，决定对特定的查询使用哪些索引、 哪些关联算法，从而使其高效运行。它是优化器中最重要的组件之一。查询优化器（简称优化器）是 SQL Server针对用户的请求进行内部优化，生 成 （或重 用）执行计划并传输给存储引擎来操作数据，最终返回结果给用户的组件。SQL Server的 优化器是基于开销（cost-based)的优化器。通过针对特定DML语...
mysql的查询优化器_MySQL查询优化之查询优化器 | 学步园
weixin_33514163的博客
01-19 405
MySQL查询优化之查询优化器phpma.com当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。phpma.com当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL执行它的速...
Mysql查询优化器.rar
02-17
MySQL查询优化器是数据库管理系统中的核心组件，负责解析SQL语句并制定出执行查询的最佳计划。这个过程涉及多个阶段，包括解析、预处理、优化和执行。本资料“Mysql查询优化器.rar”包含了对这一关键主题的深入探讨...
mysql查询优化器的错误执行和优化处理类型
lighter613的博客
12-09 891
查询优化器一.导致优化器选择错误的执行计划的原因1.统计信息不准确2.执行计划中的成功估算不等同于实际执行的成本3.MySQL是基于成本模型选择最优的计划4.MySQL不考虑其他的并发执行的查询5.MySQL 不是任何时候都是基于成本的优化6.MySQL 不会考虑其不受控制的操作的成本7.无法估算所有可能的执行计划二.优化策略的分类​ 1.静态优化(编译时优化)​ 2.动态优化(运行时优化)三.M...
mysql查询优化器提示( hint )
xinyuan的专栏
05-28 5007
查询优化器提示( hint ) : 一般指改变 mysql 优化器的执行计划， 除非业务需要， 不建议这样做。 查询优化器提示( hint ) 1. HIGH_PRIORITY 、 LOW_PRIORITY HIGH_PRIORITY: 提示mysql该语句优先执行 LOW_PRIORITY： 提示mysql该语句处于等待执行， 有可能出现一...
【MySQL】MySQL 查询优化器的提示(hint)
九师兄
07-26 1272
1.概述 如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint) 来控制最终的执行计划。下面将列举–些常见的提示，并简单地给出什么时候使用该提示。通过在查询中加入相应的提示,就可以控制该查询的执行计划。关于每个提示的具体用法， 建议直接阅读MySQL官方手册。有些提示和版本有直接关系。可以使用的- 些提示如下: 1.1 HIGH PRIORITY和LOW_ PRIORITY 这个提示告诉MySQL，当多个语句同时访问某- 个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。.
MySQL查询优化器的工作原理
01-20
当处理一个SQL查询时，MySQL优化器会经历以下步骤： 1. 解析查询语句并创建一个内部表示，这个表示是树状结构，每个节点代表查询中的一个操作或表。 2. 对内部表示进行规范化处理，主要是移除查询中的冗余部分，...
MYSQL查询优化器
辛勤的搬运工_
11-12 4279
MYSQL 逻辑结构 MySQL 使用典型的客户端/服务器（Client/Server）结构， 体系结构大体可以分为三层：客户端、服务器层以及存储引擎层。其中，服务器层又包括了连接管理、查询缓存 、SQL 接口、解析器、优化器、缓冲与缓存以及各种管理工具与服务等。逻辑结构图如下所示： 具体来说，每个组件的作用如下： 客户端，连接 MySQL 服务器的各种工具和应用程序。例如 mysql 命令行工具、mysqladmin 以及各种驱动程序等。 连接管理，负责监听和管理客户端的连接以及线
【Mysql】查询优化器
flora
01-08 241
Mysql查询优化基于成本I/O成本CPU成本join连接原理 基于成本 Mysql执行一个查询可以有不同的执行方案,它会选择其中成本最低的方案. 主要计算的就是: 全表扫描成本 针对每个索引计算不同的访问方式的成本 成本其中包括 I/O成本和CPU成本 InnoDB存储引擎规定 读取一个页面花费的成本默认是1.0(I/O成本),读取以及检测一条记录是否符合搜素条件的成本为0.2(CPU成本). 如果是计算辅助索引成本 还会加上 回表成本(因为辅助索引的叶子结点存的是主键而不是真实数据). I/O成本
java实现语法分析器_[玩转MySQL之六]MySQL查询优化器
weixin_39659837的博客
11-20 538
注:由于查询优化器涉及面很广也比较复杂，作者也没有完全领会，本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>，如果涉及到版权，请告知作者，删除本文。一、查询语句的执行过程简介MySQL查询语句在数据库中的执行分为5个阶段，具体如下:1.1 SQL输入数据库接收用户输入的SQL语句，准备执行。1.2 语法分析对输入的SQL语句进行词法分析、语法分析，得...
MySQL查询优化器
05-02 756
当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。 当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL执行它的速度都会非常快： SELECT * FROM
mysql的查询优化器
xuqiaobo的博客
08-26 481
mysql的查询优化器的主要功能就是通过计算分析系统中收集的各种统计 信息，为客户端请求的query给出它认为最有的执行计划，也就是它认为的最优的数据检索方式。 mysql的查询优化器不是单纯的（基于代价）代价优化器，而是加上了一个启发式（基于规则）优化器的复合优化器。也就是说mysql查询优化器在优化一个查询的时候，并不一定完全根据数据库的元信息和统计信息，而是在此基础上增加了某些特定的规则。
mysql查询优化器
zhoushimiao1990的博客
09-22 733
查询优化器是mysql中非常重要且复杂的部件，mysql优化器优化策略分为静态优化和动态优化：静态优化可以直接对解析树进行分析并完成优化，不依赖于特别的数值，依次完成后就一直有效，如通过简单的代数变换将where条件转换成另一种等价形式； 动态优化和查询的上下文有关，也可能和多种其他因素有关，如where条件的取值、索引中条目对应数据行数等，它需要每次在查询的时候进行评估，可以理解为 “运行时优化”。 mysql能处理到的优化类型： 重新定义关联表顺序：数据表的关联不会总是按照在查询中指定顺序进行，关联
MySQL查询优化之查询优化器
ethanzhao的专栏
08-20 2928
MySQL查询优化之查询优化器phpma.com　　当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。phpma.com　　当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值