我们一起聊聊 MySQL8.0 优化器

作者：奥特曼爱小怪兽 2023-03-29 08:13:48数据库 MySQL 当我们将查询提交给MySQL执行时，聊聊大多数的化器查询都不像 select * from single_table;那样简单，从单个表读取所有数据就行了，聊聊不需要用到高级的化器检索方式来返回数据。大多数查询都比较复杂，聊聊有些更复杂并且完全按照编写的化器方式执行查询绝不是获得结果的最有效方式。

前言

线上，聊聊遇到一些sql性能问题，化器需要手术刀级别的聊聊调优。optimizer_trace是化器一个极好的工具，已经有很多资料介绍optimizer_trace怎么使用与阅读。聊聊有必要再介绍一下我们平时不太能注意到，化器但是聊聊又对sql性能起着绝对作用的优化器。

优化器是化器啥？在sql整个生命周期里处于什么样的位置，起到什么样的聊聊作用，cmu15445 课程(https://15445.courses.cs.cmu.edu/fall2022/notes/14-optimization.pdf)中对此有一些直观的描述。

以上图片有6大模块，每一个模块都是一个单独的领域。以优化器为例，从1979年到现在，已经发展出来9个细分的研究领域：

1. Planner framework
2. Transformation
3. Join Order Optimization
4. Functional Dependency and Physical Properties
5. Cost Model
6. Statistics
7. Query feedback loop
8. MPP optimization
9. BENCHMARK

接下来会选几个领域做一些更底层的介绍，基于篇幅的限制，某些知识点，点到为止，可以作为以后工作再深入的一个入口。

要让优化器能够得到足够好的plan，有几个必要条件：

1. 数据库中的表设置了合适的数据类型。
2. 数据库中设置了合适的索引。并且索引上有正确的统计信息。
3. 合理的数据分布。

查询优化器的作用：

当我们将查询提交给MySQL执行时，大多数的查询都不像 select  *  from  single_table;那样简单，从单个表读取所有数据就行了，不需要用到高级的检索方式来返回数据。大多数查询都比较复杂，有些更复杂并且完全按照编写的方式执行查询绝不是获得结果的最有效方式。我们可以有很多的可能性来优化查询：添加索引、联接顺序、用于执行联接的算法、各种联接优化以及更多。这就是优化器发挥作用的地方。

优化器的主要工作是准备查询以执行和确定最佳查询计划。第一阶段涉及对查询进行转换，目的是重写的查询可以以比原始查询更低的成本执行查询。第二阶段包括计算查询可以执行的各种方式的成本，确定并执行成本最低的计划。

这里有一个注意的点：优化器所做的工作并不精确科学，因为数据及其分布的变化，优化器所做的工作并不精确。转换优化器的选择和计算的成本都是基于某种程度的估计。通常这些估计值足以得到一个好的查询计划，但偶尔你需要提供提示（hint）。如何配置优化器是另外一个话题。

查询改写（Transformations）

优化器有几种更改查询的改写，在仍然返回相同结果的同时，让查询变为更适合MySQL。

当然，优化的前提是返回的结果符合期望，同时响应时间变短：减少了IO或者cpu时间。改写的前提是原始查询与重写查询逻辑一致，返回相同的查询结果是至关重要的。为什么不同的写法，可以返回相同的结果，又是一门学问：关系数据库基于数学集理论的研究。

举个查询改写简单的例子：

SELECT *
FROM world.country
INNER JOIN world.city
ON city.CountryCode = country.Code
WHERE city.CountryCode = 'AUS'

这个查询有两个条件：city.CountryCode = 'AUS'，city.CountryCode=country.Code。从这两个条件可以得出country.Code='AUS'。优化器使用这些知识来直接过滤country。由于code列是country表的主键，这意味着优化器知道最多只有一行符合条件，并且优化器可以将country表视为常数( constant)。实际上，查询最终是使用country表中的列值作为选择列表中的常量（constant)执行扫描CountryCode='AUS'的city表中的行。

改写如下：

SELECT 'AUS' AS `Code`,
'Australia' AS `Name`,
'Oceania' AS `Continent`,
'Australia and New Zealand' AS `Region`,
7741220.00 AS `SurfaceArea`,
1901 AS `IndepYear`,
18886000 AS `Population`,
79.8 AS `LifeExpectancy`,
351182.00 AS `GNP`,
392911.00 AS `GNPOld`,
'Australia' AS `LocalName`,
'Constitutional Monarchy, Federation' AS `GovernmentForm`,
'Elisabeth II' AS `HeadOfState`,
135 AS `Capital`,
'AU' AS `Code2`,
city.*
FROM world.city
WHERE CountryCode = 'AUS';

从性能的角度来看，这是一个安全的转变，且是优化器可以自动实现的，并且对外提供了一个开关。

某些转换会更加复杂，且并不总是提高性能。因此set optimizer_switch =on or off 是可选的，

optimizer_switch 的内容 以及 何时怎么使用 optimizer hints 会在下一篇文章中讨论。

有对查询改写怎么实现感兴趣的朋友，可以在GreatSQL社区留言，为大家准备了大概9篇论文。

基于成本优化（Cost-Based Optimization）

一旦优化器决定要进行哪些转换，就需要确定如何执行重写查询。业内目前有两条路径来解决，rule model 和 cost model。如果您已经熟悉对optimizer_trace输出的解读，作为dba已经对cost model 了解的足够多了。

我再试着从优化器的角度来解读一下成本优化。

单表查询

无论查询如何，计算成本的原则都是相同的，但是，查询越复杂，成本估算就越复杂。

举一个简单的例子，一个查询单个表的sql，where条件使用二级索引列。

mysql> SHOW CREATE TABLE world.city\G
(责任编辑：综合)