【MySQL】 MySQL基于成本的优化

Metadata

title: 【MySQL】 MySQL基于成本的优化
date: 2023-06-23 13:57
tags:
  - 行动阶段/完成
  - 主题场景/数据存储
  - 笔记空间/KnowladgeSpace/ProgramSpace/BasicsSpace
  - 细化主题/数据存储
categories:
  - 数据存储
keywords:
  - 数据存储
description: 【MySQL】 MySQL基于成本的优化

什么是成本

I/O 成本

从磁盘到内存这个加载的过程损耗的时间

CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间

规定读取一个页面花费的成本默认是1.0 ，读取以及检测一条记录是否符合搜索条件的成本默认是0.2 。

单表查询的成本

准备工作

CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

基于成本的优化步骤

MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询

1. 根据搜索条件，找出所有可能使用的索引

把一个查询中可能使用到的索引称之为possible keys

2. 计算全表扫描的代价

全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集

由于查询成本= I/O 成本+ CPU 成本，所以计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数
• 该表中的记录数

提供了SHOW TABLE STATUS 语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE 语句就好

SHOW TABLE STATUS LIKE 'single_table'

• Rows 本选项表示表中的记录条数。
• Data_length 本选项表示表占用的存储空间字节数。
  • Data_length = 聚簇索引的页面数量 x 每个页面的大小
  • 页数 = Data_length / 页占用空间字节数

对于single_table 的全表扫描所需的总成本 = I/O 成本 + CPU 成本 = 页数 * 1.0 (加载一个页面的成本常数) + 1.1(偏移值) + ROW * 0.2 (读取一条记录的成本常数) + 1.0(微调值)

3. 计算使用不同索引执行查询的代价

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

SELECT * FROM single_table WHERE
key1 IN ('a', 'b', 'c') AND
key2 > 10 AND key2 < 1000 AND
key3 > key2 AND
key_part1 LIKE '%hello%' AND
common_field = '123';

range

• 使用idx_key2执行查询的成本分析

• 范围数量
  查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的

• 需要回表的记录数

• 步骤1：访问一下idx_key2 对应的B+ 树索引

  • 区间最左记录
  • 区间最右记录
    • 每次最多跨10页
    • 计算页b 和页c 之间有多少页面就相当于计算它们父节点（也就是页a）中对应的目录项记录之间隔着几条记录
  • 读取二级索引记录需要付出的CPU 成本= 需要读取的二级索引记录条数 * 0.2 (读取记录的成本) + 0.01 (微调值)

• 步骤2: 根据这些记录里的主键值到聚簇索引中做回表操作

  • 粗略计算： 每次回表操作都相当于访问一个页面
    • 需要读取的二级索引记录条数 * 1 (读取一个页面所需时间成本) + 0.1 (微调值)
  • 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

In

• 使用idx_key1执行查询的成本分析

• 范围区间数量

使用idx_key1 执行查询时很显然有3个单点区间，所以访问这3个范围区间的二级索引付出的I/O成本就是：
3 x 1.0 = 3.0

• 需要回表的记录数

是否有可能使用索引合并（Index Merge）

4. 对比各种执行方案的代价，找出成本最低的那一个

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

• 全表扫描的成本： 2037.7
• 使用idx_key2 的成本： 134.01
• 使用idx_key1 的成本： 168.21
  很显然，使用idx_key2 的成本最低，所以当然选择idx_key2 来执行查询喽。

基于索引统计数据的成本计算

这种通过直接访问索引对应的B+ 树来计算某个范围区间对应的索引记录条数的方式称之为index dive 。

系统变量 eq_range_index_dive_limit

小于该值将使用index dive 的方式计算各个单点区间对应的记录条数

查看一下single_table 的各个索引的统计数据

SHOW INDEX FROM single_table;

连接查询的成本

准备工作

连接查询至少是要有两个表的，只有一个single_table 表是不够的，所以为了故事的顺利发展，我们直接构造一个和single_table 表一模一样的single_table2 表。为了简便起见，我们把single_table 表称为s1 表，把single_table2 表称为s2 表。

Condition filtering介绍

MySQL 中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次

所以对于两表连接查询来说，它的查询成本由下边两个部分构成

• 单次查询驱动表的成本
• 多次查询被驱动表的成本 (具体查询多少次取决于对驱动表查询的结果集中有多少条记录)

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名： fanout ）

两表连接的成本分析

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2
ON s1.key1 = s2.common_field
WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
s2.key2 > 1000 AND s2.key2 < 2000;

对于内连接来说，可以选择的连接顺序有两种：

• s1 连接s2 ，也就是s1 作为驱动表， s2 作为被驱动表。
• s2 连接s1 ，也就是s2 作为驱动表， s1 作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。

定性的分析一下

• 使用single_table 作为驱动表时的总成本, 使用s1 作为驱动表的情况
  • 分析对于驱动表的成本最低的执行方案
    • 看一下涉及s1 表单表的搜索条件有哪些
  • 然后分析对于被驱动表的成本最低的执行方案
  • 使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本
• 使用s2 作为驱动表的情况
  • 分析对于驱动表的成本最低的执行方案
    • 看一下涉及s2 表单表的搜索条件有哪些
  • 然后分析对于被驱动表的成本最低的执行方案
  • 使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本
• 最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。
• 优化重点其实是下边这两个部分
  • 尽量减少驱动表的扇出
  • 对被驱动表的访问成本尽量低

尽量在被驱动表的连接列上建立索引

多表连接的成本分析

对于n 表连接的话，则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序，就是n的阶乘种连接顺序，也就是$n!$ 。

有n 个表进行连接， MySQL 查询优化器要每一种连接顺序的成本都计算一遍, 减少计算非常多种连接顺序的成本的方法

提前结束某种顺序的成本评估

在计算各种链接顺序的成本之前，会维护一个全局的变量, 这个变量表示当前最小的连接查询成本。

MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。
如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本

系统变量optimizer_search_depth

如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析

根据某些规则压根儿就不考虑某些连接顺序

启发式规则 就是根据以往经验指定的一些规则

凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划

提供了一个系统变量optimizer_prune_level 来控制到底是不是用这些启发式规则。

调节成本常数

两个成本常数：

• 读取一个页面花费的成本默认是1.0
• 检测一条记录是否符合搜索条件的成本默认是0.2

其实除了这两个成本常数， MySQL 还支持好多呢，它们被存储到了mysql 数据库

一条语句的执行其实是分为两层的：

• server 层
• 存储引擎层

在server 层进行连接管理、查询缓存、语法解析、查询优化等操作，
在存储引擎层执行具体的数据存取操作。

关于这些操作对应的成本常数就存储在了server_cost 表中
而依赖于存储引擎的一些操作对应的成本常数就存储在了 engine_cost 表中。

mysql.server_cost表

server_cost 表中在server 层进行的一些操作对应的成本常数

• cost_name
  表示成本常数的名称。
• cost_value
  表示成本常数对应的值。如果该列的值为NULL 的话，意味着对应的成本常数会采用默认值。
• last_update
  表示最后更新记录的时间。
• comment
  注释。

从server_cost 中的内容可以看出来，目前在server 层的一些操作对应的成本常数有以下几种

MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。

在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，
在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。

这些成本常数在server_cost 中的初始值都是NULL ，意味着优化器会使用它们的默认值来计算某个操作的成本

如果我们想修改某个成本常数的值的话，需要做两个步骤：

• 对我们感兴趣的成本常数做更新操作
• 让系统重新加载这个表的值。

FLUSH OPTIMIZER_COSTS;

mysql.engine_cost表

engine_cost表表中在存储引擎层进行的一些操作对应的成本常数

与server_cost 相比， engine_cost 多了两个列：

• engine_name 列 指成本常数适用的存储引擎名称。
• device_type 列 指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.21 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是0 。

我们从engine_cost 表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：

通过为engine_cost 表插入新记录的方式来添加只针对某种存储引擎的成本常数

• 插入针对某个存储引擎的成本常数
• 让系统重新加载这个表的值。

FLUSH OPTIMIZER_COSTS;