MySQL 查询优化器

文章目录

• 控制查询计划
• • optimizer_prune_level
  • optimizer_search_depth
• 优化器参数
• 优化器提示
• 索引提示
• 成本模型
• • server_cost
  • • cost_name
  • engine_cost

控制查询计划

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 相似，但是表示的是从内存数据库缓冲区读取索引或数据块的成本