乐趣区

关于数据库:MySQL80-优化器介绍一

jiezi
  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
  • GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。
  • 作者：奥特曼爱小怪兽
  • 文章起源：GreatSQL 社区原创

前言

线上，遇到一些 sql 性能问题，须要手术刀级别的调优。optimizer_trace 是一个极好的工具，曾经有很多材料介绍 optimizer_trace 怎么应用与浏览。有必要再介绍一下咱们平时不太能留神到，然而又对 sql 性能起着相对作用的优化器。

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

以上图片有 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
**************************** 1. row ****************************
 Table: city
Create Table: CREATE TABLE `city` (`ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT ",
 `CountryCode` char(3) NOT NULL DEFAULT ",
 `District` char(20) NOT NULL DEFAULT ",
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `CountryCode` (`CountryCode`),
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` 
(`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0008 sec)

SELECT *  FROM world.city  WHERE CountryCode = 'IND'

优化器能够抉择两种形式来获取匹配的行。一种办法是应用 CountryCode 上的索引查找索引中的匹配行，而后查找申请的行值。另一种办法是进行全表扫描并查看每一行确定它是否合乎 where 条件。

这些拜访办法中哪一种老本最低（最快）不是能够间接确定。这取决于几个因素：

  • 索引的选择性：cost_单行间接获取 <cost_二级索引查问逐步后获取 <cost_全表扫描
    • 索引必须显著缩小要查看的行数。越多选择指数，应用它绝对便宜。（这里行数不太精确，应该是 IO 次数，以及 IO 的形式，程序 IO 还是随机 IO)《MySQL 是怎么运行的》有介绍一行数据是怎么读取到的。
  • 索引覆盖度：如果索引蕴含所有列查问须要，能够跳过对理论行的读取。
  • 读取记录的代价：取决于几个因素，索引和行记录是否都在 innodb_buffer_pool 中，如果不在，从磁盘读取的代价和速度是多少。应用二级索引时，在切换读取索引和读取主键索引之间，将须要更多的随机 I /O，查找记录须要消耗的索引寻找次数（个别索引高度来决定）变得十分重要。

MySQL8.0 的优化器能够询问 InnoDB 是否查问所需的记录能够在缓冲池中找到，或者是否

必须从从磁盘上读取记录。这对执行打算的改良，有微小的帮忙。

读取记录的所需 cost 是很简单的问题，MySQL 不晓得硬件的性能，MySQL8.0 默认磁盘读取的老本是 4 倍内存读取。

mysql>   select  cost_name, default_value from  mysql.server_cost;
+------------------------------+---------------+
| cost_name                    | default_value |
+------------------------------+---------------+
| disk_temptable_create_cost   |            20 |
| disk_temptable_row_cost      |           0.5 |
| key_compare_cost             |          0.05 |
| memory_temptable_create_cost |             1 |
| memory_temptable_row_cost    |           0.1 |
| row_evaluate_cost            |           0.1 |
+------------------------------+---------------+
6 rows in set (0.00 sec)

mysql>  select engine_name,cost_name,default_value  from  mysql.engine_cost;
+-------------+------------------------+---------------+
| engine_name | cost_name              | default_value |
+-------------+------------------------+---------------+
| default     | io_block_read_cost     |             1 |
| default     | memory_block_read_cost |          0.25 |
+-------------+------------------------+---------------+
2 rows in set (0.00 sec)

表关联程序（Table Join Order）

多表关联时，outer and straight joins,join 程序是固定的。inner join 时，优化器会自由选择 join 程序，为每一种组合计算代价。计算复杂度和表数量的关系：

N 张表，须要做 N! 的计算。5 张表，组合度为 5!=5*43*21=120

MySQL 反对连贯多达 61 个表，在这种状况下可能有 61! 计算成本的组合。计算组合的老本过高且可能须要更长时间而不是执行查问自身。因而，优化器默认状况下会删除基于老本的局部评估查问打算，因而只有最有心愿的打算会被齐全评估。

在给定的表之后，还能够通过参数 optimizer_prune_level 和 optimizer_search_depth 配置搜寻裁剪、搜寻深度，来进行评估。比方 10 张表关联，实践上须要评估 10！=3628800 次, 默认最多 62 次。

最佳联接程序 有两个个因素影响，表本身的大小，通过过滤器后每个表缩小的行数。

默认过滤成果（Default Filtering Effects）

多表关联时，晓得每张表有多少行数据参加 join，很有意义。

当应用索引时，当过滤器与其余表不相干时，优化器能够十分精确地预计与索引匹配的行数。如果没有索引，直方图统计可用于取得良好的滤波预计。当没有过滤列的统计信息时，就会呈现艰难。在这种状况下，优化器会后退基于内置默认预计。

那到底是怎么估算的呢？详见以下这篇赫赫有名的论文：

《Access Path Selection in a Relational Database Management System》(https://dl.acm.org/doi/pdf/10.1145/582095.582099)

须要中文版的敌人能够留言到 GreatSQL 社区。

System R 针对 join ordering 问题，开创性的应用基于动静布局的办法，联合 Interesting Order 造成等价类的形式，来对 search space 进行高效搜寻。不仅如此，其对于 selectivity 的计算，cost 的计算形式，影响十分深远，置信晚期的商业数据库大多采纳相似的代价估算形式(MySQL 直至今日依然如此）。

论文太深奥了，来点大家看得懂的

这个列表并不详尽，但它应该能让您很好地理解 MySQL 是如何实现过滤预计的。默认过滤成果显然不是十分精确，特地是对于大表，因为数据不遵循这样的严格规定。这就是为什么索引和直方图对于取得良好的查问打算十分重要。在确定查问打算的最初，会对单个局部和整个查问进行老本估算。这些信息有助于理解优化器达到查问执行打算。

（这里也能够看出 MySQL 的优化器的参考值绝对 Oracle 是比较简单的，导致的后果就是 MySQL 解析 sql 很快，快到简直不必缓存执行打算，Oracle 为了解决生成打算慢的问题，引入了软简析，软软简析，绑定执行打算等计划，当然 MySQL 的优化器短板也很显著，为 DBA 们制作了大量 sql 优化的需要）

查问老本（The Query Cost）

有 5 种形式查看 optimizer 估算进去的老本。每一种都值得独立开篇来探讨，每一种都有它应用的场景,（生产上做操作有相对的平安保障吗？）。

  • 1、explain（explain 前面的 sql，真的不会执行 or 产生 cost 吗？如果会，什么场景会触发 cost）
  • 2、explain format= tree (8.0.16) or explain format= json
  • 3、explain analyze(8.0.18) 在 format= tree 的根底上，减少了多种信息（actual cost 怎么定义 的？actual cost 又是一个量化剖析的话题，它是一个相对的概念还是一个绝对 explain 的概念），执行老本、返回行数、执行工夫、循环次数等，实质上，EXPLAIN ANALYZE 只实用于显式查问，因为它须要从头到尾监督查问。另一方面，简略的 EXPLAIN 语句也能够用于正在进行的查问。详见语法：（https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-…）
mysql> explain format=tree SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1)
1 row in set (0.01 sec)

mysql> explain analyze SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10)\G
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9) (actual time=0.153..0.200 rows=9 loops=1)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9) (actual time=0.097..0.100 rows=9 loops=1)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9) (actual time=0.001..0.002 rows=9 loops=1)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9) (actual time=0.090..0.092 rows=9 loops=1)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9) (actual time=0.037..0.042 rows=9 loops=1)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9) (actual time=0.036..0.040 rows=9 loops=1)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9) (actual time=0.035..0.038 rows=9 loops=1)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1) (actual time=0.010..0.010 rows=1 loops=9)
1 row in set (0.01 sec)

explain format= json 怎么算 参考 format= json 怎么算

explain analyze 怎么读？参考

(https://www.mmzsblog.cn/articles/2022/05/07/1651914715938.html)

4、MySQL Workbench Visual Explain diagram 大部分的 mysql 客户端都提供可视化的执行打算性能。

SELECT ci.ID, 
       ci.Name, 
       ci.District,  
       co.Name AS Country, 
       ci.Population  
  FROM world.city ci  
  INNER JOIN  (SELECT Code, 
                      Name  
                 FROM world.country  
                WHERE Continent = 'Europe'  
             ORDER BY SurfaceArea  LIMIT 10  ) co 
          ON co.Code = ci.CountryCode  
  ORDER BY ci.Population DESC  
  LIMIT 5；

可视化执行打算展现：

12 种数据表拜访形式作色

作色与表拜访形式老本大小的关系

Blue (1) is the cheapest; green (2), yellow (3), and orange (4) represent low to medium costs; and the most expensive access types and operations are red symbolizing a high (5) to very high (6) cost.

以上都只是一个平均值 or 经验值，可视化执行打算的色彩展现不是相对的真谛。

能够思考一下：索引 look up 肯定比全表扫描好吗？索引只会带来查问上的正向优化吗？

  • 5、终极武器 optimizer trace

影响以上输入的因素有：（不好意思，以下每种，又是一个开篇话题：）我真是太厌恶了。。。）

1、sql_mode

2、optimizer switch

3、index statistics

4、mysql.engine_ cost and mysql.server_cost tables

done，待续

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

退出移动版