5分钟搞懂MySQL中的优化器与成本模型


本文目录

  • 5分钟搞懂MySQL中的优化器与成本模型
  • 一、概述
  • 二、优化器
  • 三、成本模型
  • 四、成本计算示例
  • 1、全表扫描
  • 2、使用索引进行回表查询
  • 3、多表连接查询
  • 五、总结


一、概述

在之前的文章中我们学习了SQL的执行计划，通过possible_keys字段可以看得出来一个SQL在执行查询操作的时候有多个索引可供使用的，换句话说就是有很多种方式来完成这次查询。在这种情况下，我们必须要选择一个执行计划来完成查询，正是为了完成执行计划的选择，才有了查询优化器这个东西。本篇文章将会介绍查询优化器的工作原理，在学完这个后，大家对Explain输出的信息的理解会更加深刻，更加有助于大家今后的SQL优化。

二、优化器

优化器的任务是查找到执行SQL的最佳计划。因为“好”计划和”坏“计划的性能差异可能是数量级的。大多数的查询优化器都是在很多之际计划中对一个最优计划进行搜索。对于多表的连接查询，计划数量会随着表数量的增加呈指数增长。如果连接的表数量过多，很可能会成为性能的主要瓶颈。

上述问题的一个解决方法是让用户可以控制在搜索查询计划时的穷举程度。通俗的说就是用户可以控制考虑的步数，考虑的步数越少，编译所花费的时间也就越短，当然，因为执行器会跳过某些执行计划，因此可能会错过找到最佳执行计划的机会

我们可以通过两个变量来控制优化器的行为optimizer_prune_level和optimizer_search_depth，第一个变量控制是否开启启发式方法，默认值为1，意味着在搜索的时候会删除部分前景不好的计划，如果设置为0则表示禁用，那么就会进行穷举搜索。第二个变量控制优化器执的最大搜索深度，默认值为62(也是最大值)，如果为0，那么系统会自动选择一个合理的值。

#查看系统变量
SHOW VARIABLES LIKE 'optimizer_prune_level'
SHOW VARIABLES LIKE 'optimizer_search_depth'

三、成本模型

为了衡量每个计划的好坏，就需要让各种计划可比较。因为不管是哪一种执行计划，都是由很多原子操作组合而成，我们只需要为每个原子操作分别设置好成本，然后就可以计算每个执行计划的成本了。

对于每种操作的成本，存储在 mysql 系统数据库的 server _ cost 和 engine _ cost 表中，并且可以自己进行配置，服务器启动的时候会将成本模型读入内存，运行的时候使用内存中的值，当然，也可以重新读取成本表，只需要在后面加上FLUSH OPTIMIZER_COSTS就可以。成本表只用于特定的服务器，服务器不会将成本表复制到副本中去。

在这些成本条目中，有几个是比较常用的：

  • row_evaluate_cost (默认值0.2)
    处理一行数据时的成本，随着行数的增加而增加，也就是常说的CPU成本，CPU成本 = rows * 0.2
  • io_block_read_cost (默认值1.0) 和 memory_block_read_cost (默认值1.0)
    读取一个数据块的成本，换句话说就是一个数据页(16k)的IO成本，IO成本 = (总数据大小(单位是bytes) / 1024) * 1.0
    通过下面的SQL可以查看指定表的信息，并通过Data_length字段知道表的总数据大小
SHOW TABLE STATUS like 'user'

可以通过下面的语句去自定义成本：

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

四、成本计算示例

在下面计算成本的示例中，忽略了成本中微调的值（数值比较小，因此没有展示）

1、全表扫描

一下面的为例：

mysql优化器 执行器 mysql查询优化器原理_mysql优化

通过Rows可以得知，一共有14行数据，CPU成本就是 14 * 0.2 = 2.8

根据Data_length字段，16384 ÷1024 = 16 (kb)，可以知道总数据量为16k，也就是一个数据页，那么IO成本就是1 * 1 = 1

那么这个表的全表扫描成本就是2.8 + 1 = 3.8

2、使用索引进行回表查询

MySQL中扫描一个范围的成本和一个数据页的IO成本是一样的，都是1。并且回表后，MySQL认为每条数据都对应一个数据页

mysql优化器 执行器 mysql查询优化器原理_执行计划_02

二级索引的IO成本：in 中有两个值，表示两个范围，2 * 1.0 = 2.0

二级索引的CPU成本： 通过row字段可以知道有291行，291 * 0.2 = 58.2

回表IO成本：因为一条数据对于一个数据页，291 * 1.0 = 291.0

回表CPU成本：291 * 0.2 = 25.2

总成本：2.0 + 58.2 + 291 + 58.2 = 409.4

3、多表连接查询

上面两种介绍的是单表的成本计算示例，要么全表扫描，要么走索引然后回表（可能不回表），如果有多个索引，就分别计算成本，然后选取成本最低的索引。掌握了单表的成本计算之后，对于多表连接只需要知道下面的公式就可以了：

总成本 = 驱动表的成本 + 驱动表查询出的记录数量 * 被驱动表的成本

五、总结

以上就是全部的内容了，今天给大家介绍了MySQL中的优化器与成本模型以及如何计算成本。说简单其实也很简单，其实就是对于Explain输出的内容中的possible_keys中的所有可选择的索引，优化器会逐一去计算他们的成本，当然，还会计算全表扫描的成本，然后通过比较这些成本的大小，选一个成本最低的执行。对于每种操作的成本也都是可以自己根据情况去设置的。