MySQL 优化器原来是这样工作的_1

大家好，我是只谈技术不剪发的 Tony 老师。我们在 MySQL 体系结构中介绍了 MySQL 的服务器逻辑结构，其中查询优化器（optimizer）负责生成 SQL 语句的执行计划，是决定查询性能的一个关键组件。本文将会深入分析 MySQL 优化器工作的原理以及如何控制优化器来实现 SQL 语句的优化。

优化器概述

MySQL 优化器使用基于成本的优化方式（Cost-based Optimization），以 SQL 语句作为输入，利用内置的成本模型和数据字典信息以及存储引擎的统计信息决定使用哪些步骤实现查询语句，也就是查询计划。

查询优化和地图导航的概念非常相似，我们通常只需要输入想要的结果（目的地），优化器负责找到最有效的实现方式（最佳路线）。需要注意的是，导航并不一定总是返回最快的路线，因为系统获得的交通数据并不可能是绝对准确的；与此类似，优化器也是基于特定模型、各种配置和统计信息进行选择，因此也不可能总是获得最佳执行方式。

从高层次来说，MySQL Server 可以分为两部分：服务器层以及存储引擎层。其中，优化器工作在服务器层，位于存储引擎 API 之上。优化器的工作过程从语义上可以分为四个阶段：

1. 逻辑转换，包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；
2. 优化准备，例如索引 ref 和 range 访问方法分析、查询条件扇出值（fan out，过滤后的记录数）分析、常量表检测；
3. 基于成本优化，包括访问方法和连接顺序的选择等；
4. 执行计划改进，例如表条件下推、访问方法调整、排序避免以及索引条件下推。

逻辑转换

MySQL 优化器首先可能会以不影响结果的方式对查询进行转换，转换的目标是尝试消除某些操作从而更快地执行查询。例如（数据来源）：

显然，查询条件中的 1=1 是完全多余的。没有必要为每一行数据都执行一次计算；删除这个条件也不会影响最终的结果。执行语句之后，通过命令可以查看逻辑转换之后的 SQL 语句，从上面的结果可以看出 1=1 已经不存在了。

📝关于 MySQL 执行计划和 EXPLAIN 语句的详细介绍可以参考这篇文章。

我们也可以通过优化器跟踪进一步了解优化器的执行过程，例如：

优化器跟踪输出主要包含了三个部分：

• join_preparation，准备阶段，返回了字段名扩展之后的 SQL 语句。对于 1=1 这种多余的条件，也会在这个步骤被删除；
• join_optimization，优化阶段。其中 condition_processing 中包含了各种逻辑转换，经过等值传递（equality_propagation）之后将条件 dept_id = emp_id 转换为了 dept_id = 1。另外 constant_propagation 表示常量传递，trivial_condition_removal 表示无效条件移除
• join_execution，执行阶段。

优化器跟踪还可以显示其他基于成本优化的过程，后续我们还会使用该功能。关闭优化器跟踪功能的方式如下：

下表列出了一些逻辑转换的示例：

原始语句	重写形式	备注
select * from employee where emp_id = 1;	select ‘1’ AS `emp_id`,‘刘备’ AS `emp_name`,‘男’ AS `sex`,‘1’ AS `dept_id`,NULL AS `manager`,‘2000-01-01’ AS `hire_date`,‘1’ AS `job_id`,‘30000.00’ AS `salary`,‘10000.00’ AS `bonus`,‘liubei@shuguo.com’ AS `email` from `hrdb`.`employee` where true	通过主键或唯一索引进行等值查找时，在选择执行计划之前就完成了转换，重写为查询常量。
select * from employee where emp_id = 0;	select NULL AS `emp_id`,NULL AS `emp_name`,NULL AS `sex`,NULL AS `dept_id`,NULL AS `manager`,NULL AS `hire_date`,NULL AS `job_id`,NULL AS `salary`,NULL AS `bonus`,NULL AS `email` from `hrdb`.`employee` where multiple equal(0, NULL)	通过主键或唯一索引查找不存在的值。
select emp_name from employee e, (select * from department where dept_name =‘研发部’) as d where d.dept_id = e.dept_id and e.salary > 10000;	select `hrdb`.`e`.`emp_name` AS `emp_name` from `hrdb`.`employee` `e` join `hrdb`.`department` where ((`hrdb`.`e`.`dept_id` = `hrdb`.`department`.`dept_id`) and (`hrdb`.`e`.`salary` > 10000.00) and (`hrdb`.`department`.`dept_name` = ‘研发部’))	派生表子查询转换为连接查询

基于成本的优化

MySQL 优化器采用基于成本的优化方式，简化的步骤如下：

1. 为每个操作指定一个成本；
2. 计算每个可能的执行计划各个步骤的成本总和；
3. 选择总成本最小的执行计划。

为了找到最佳执行计划，优化器需要比较不同的查询方案。随着查询中表的数量增加，可能的执行计划会呈现指数级增长；因为每个表都可能使用全表扫描或者不同的索引访问方法，连接查询可能使用任意顺序。对于少量表的连接查询（通常少于 7 到 10 个）可能不会产生问题，但是更多的表可能会导致查询优化的时间比执行时间还要长。

所以优化器不可能遍历所有的执行方案，一种更灵活的优化方法是允许用户控制优化器在查找最佳查询计划时的遍历程度。一般来说，优化器评估的计划越少，则编译查询所花费的时间就越少；但另一方面，由于优化器忽略了一些计划，因此可能找到的不是最佳计划。

控制优化程度

MySQL 提供了两个系统变量，可以用于控制优化器的优化程度：

• optimizer_prune_level， 基于返回行数的评估忽略某些执行计划，这种启发式的方法可以极大地减少优化时间而且很少丢失最佳计划。因此，该参数的默认设置为 1；如果确认优化器错过了最佳计划，可以将该参数设置为 0，不过这样可能导致优化时间的增加。
• optimizer_search_depth，优化器查找的深度。如果该参数大于查询中表的数量，可以得到更好的执行计划，但是优化时间更长；如果小于表的数量，可以更快完成优化，但可能获得的不是最优计划。例如，对于 12、13 个或者更多表的连接查询，如果将该参数设置为表的个数，可能需要几小时或者几天时间才能完成优化；如果将该参数修改为 3 或者 4，优化时间可能少于 1 分钟。该参数的默认值为 62；如果不确定是否合适，可以将其设置为 0，让优化器自动决定搜索的深度。

设置成本常量

MySQL 优化器计算的成本主要包括 I/O 成本和 CPU 成本，每个步骤的成本由内置的“成本常量”进行估计。另外，这些成本常量可以通过 mysql 系统数据库中的 server_cost 和 engine_cost 两个表进行查询和设置。

server_cost 中存储的是常规服务器操作的成本估计值：

cost_value 为空表示使用 default_value。其中，

• disk_temptable_create_cost 和 disk_temptable_row_cost 代表了在基于磁盘的存储引擎（InnoDB 或 MyISAM）中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。
• key_compare_cost 代表了比较记录键的评估成本。增加该值将导致需要比较多个键值的查询计划变得更加昂贵。例如，执行 filesort 排序的查询计划比通过索引避免排序的查询计划相对更加昂贵。
• memory_temptable_create_cost 和 memory_temptable_row_cost 代表了在 MEMORY 存储引擎中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。
• row_evaluate_cost 代表了计算记录条件的评估成本。增加该值会导致检查许多数据行的查询计划变得更加昂贵。例如，与读取少量数据行的索引范围扫描相比，全表扫描变得相对昂贵。

engine_cost 中存储的是特定存储引擎相关操作的成本估计值：

engine_name 表示存储引擎，“default”表示所有存储引擎，也可以为不同的存储引擎插入特定的数据。cost_value 为空表示使用 default_value。其中，

• io_block_read_cost 代表了从磁盘读取索引或数据块的成本。增加该值会使读取许多磁盘块的查询计划变得更加昂贵。例如，与读取较少块的索引范围扫描相比，全表扫描变得相对昂贵。
• memory_block_read_cost 与 io_block_read_cost 类似，但它表示从数据库缓冲区读取索引或数据块的成本。

我们来看一个例子，执行以下语句：

查询计划显示使用了全表扫描（access_type = ALL），而没有选择 idx_emp_dept。通过优化器跟踪可以看到具体原因：

使用全表扫描的总成本为 2.75，使用范围扫描的总成本为 6.21。这是因为查询返回了 employee 表中大部分的数据，通过索引范围扫描，然后再回表反而会比直接扫描表更慢。

接下来我们将数据行比较的成本常量 row_evaluate_cost 从 0.1 改为 1，并且刷新内存中的值：

然后重新连接数据库，再次获取执行计划的结果如下：

此时，优化器选择的范围扫描（access_type = range）。虽然它的成本增加为 38.51，但是使用全表扫描的代价更高。

最后，记得将 row_evaluate_cost 的还原成默认设置并重新连接数据库：

不要轻易修改成本常量，因为这样可能导致许多查询计划变得更糟！在大多数生产情况下，推荐通过添加优化器提示（optimizer hint）控制查询计划的选择。

数据字典与统计信息

除了成本常量之外，MySQL 优化器在优化的过程中还会使用数据字典和存储引擎中的统计信息。例如表的数据量、索引、索引的唯一性以及字段是否可空都会影响到执行计划的选择，包括数据的访问方法和表的连接顺序等。

MySQL 会在日常操作过程中粗略统计表的大小和索引的基数（Cardinality），我们也可以使用 ANALYZE TABLE 语句手动更新表的统计信息和索引的数据分布。

这些统计信息默认会持久化到数据字典表 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，也可以通过 INFORMATION_SCHEMA 视图 TABLES、STATISTICS 以及 INNODB_INDEXES 进行查看。

另外，从 MySQL 8.0 开始增加了直方图统计（histogram statistics），也就是字段值的分布情况。用户同样可以通过语句生成或者删除字段的直方图：

其中，WITH N BUCKETS 用于指定直方图统计时桶的个数，取值范围从 1 到 1024，默认为 100。

直方图统计主要用于没有创建索引的字段，当查询使用这些字段与常量进行比较时，MySQL 优化器会使用直方图统计评估过滤之后的行数。例如，以下语句显示了没有直方图统计时的优化器评估：

由于 salary 字段上既没有索引也没有直方图统计，因此优化器评估返回的行数为 3，但实际返回的行数为 1。

我们为 salary 字段创建直方图统计：

然后再次查看执行计划：

此时，优化器评估的行数和实际返回的行数一致，都是 1。

MySQL 使用数据字典表 column_statistics 存储字段值分布的直方图统计，用户可以通过查询视图 INFORMATION_SCHEMA.COLUMN_STATISTICS 获得直方图信息：

删除以上直方图统计的命令如下：

索引和直方图之间的区别在于：

• 索引需要随着数据的修改而更新；
• 直方图通过命令手动更新，不会影响数据更新的性能。但是，直方图统计会随着数据修改变得过时。

相对于直方图统计，优化器会优先选择索引范围优化评估返回的数据行。因为对于索引字段而言，范围优化可以获得更加准确的评估。

控制优化行为

MySQL 提供了一个系统变量 optimizer_switch，用于控制优化器的优化行为。

它的值由一组标识组成，每个标识的值都可以为 on 或 off，表示启用或者禁用了相应的优化行为。

该变量支持全局和会话级别的设置，可以在运行时进行更改。

其中，command 可以是以下形式：

• default，将所有优化行为设置为默认值。
• opt_name=default，将指定优化行为设置为默认值。
• opt_name=off，禁用指定的优化行为。
• opt_name=on，启用指定的优化行为。

我们以索引条件下推（index_condition_pushdown）优化为例，演示修改 optimizer_switch 的效果。首先执行以下语句查看执行计划：

其中，Extra 字段中的“Using index condition”表示使用了索引条件下推。

然后禁用索引条件下推优化：

然后再次查看执行计划：

Extra 字段变成了“Using where”，意味着需要访问表中的数据然后再应用该条件过滤。如果使用优化器跟踪，可以看到更详细的差异。

优化器和索引提示

虽然通过系统变量 optimizer_switch 可以控制优化器的优化策略，但是一旦改变它的值，后续的查询都会受到影响，除非再次进行设置。

另一种控制优化器策略的方法就是优化器提示（Optimizer Hint）和索引提示（Index Hint），它们只对单个语句有效，而且优先级比 optimizer_switch 更高。

优化器提示使用 注释风格的语法，可以对连接顺序、表访问方式、索引使用方式、子查询、语句执行时间限制、系统变量以及资源组等进行语句级别的设置。

例如，在没有使用优化器提示的情况下：

优化器选择 employee 作为驱动表，并且使用全表扫描返回 salary = 10000 的数据；然后通过主键查找 department 中的记录。

然后我们通过优化器提示 join_order 修改两个表的连接顺序：

此时，优化器选择了 department 作为驱动表；同时访问 employee 时选择了全表扫描。我们可以再增加一个索引相关的优化器提示 index：

最终，优化器选择了通过索引 idx_emp_dept 查找 employee 中的数据。

需要注意的是，通过提示禁用某个优化行为可以阻止优化器使用该优化；但是启用某个优化行为不代表优化器一定会使用该优化，它可以选择使用或者不使用。

开发和测试过程可以使用优化器提示和索引提示，但是生产环境中需要小心使用。因为实际数据和环境会随着时间发生变化，而且 MySQL 优化器也会越来越智能，合理的参数配置定时的统计更新通常是更好地选择。

索引提示为优化器提供了如何选择索引的信息，直接出现在表名之后：

USE INDEX 提示优化器使用某个索引，IGNORE INDEX 提示优化器忽略某个索引，FORCE INDEX 强制使用某个索引。

例如，以下语句使用了 USE INDEX 索引提示：

虽然我们使用了索引提示，但是由于索引 idx_emp_job 和查询完全无关，优化器最终还是没有选择使用该索引。

以下示例使用了 IGNORE INDEX 索引提示：

IGNORE INDEX 使得优化器放弃了 department 的主键查找，最终选择了 hash join 连接两个表。该示例也可以通过优化器提示 no_index 实现：

从 MySQL 8.0.20 开始，提供了等价形式的索引级别优化器提示，将来可能会废弃传统形式的索引提示。

总结

MySQL 优化器使用基于成本的优化方式，利用数据字典和统计信息选择 SQL 语句的最佳执行方式。同时，MySQL 为我们提供了控制优化器的各种选项，包括控制优化程度、设置成本常量、统计信息收集、启用/禁用优化行为以及使用优化器提示等。

如果觉得文章对你有用，欢迎订阅我的专栏《MySQL性能优化》！