探究MySQL中SQL查询的成本

成本

什么是成本，即SQL进行查询的花费的时间成本，包含IO成本和CPU成本。

IO成本：即将数据页从硬盘中读取到内存中的读取时间成本。通常1页就是1.0的成本。

CPU成本：即是读取和检测是否满足条件的时间成本。0.2是每行的CPU成本。

单表查询计算成本

我们分析的具体步骤如下：

1. 根据搜索条件找出可能使用到的索引。
2. 计算全表扫描的需要执行的成本。
3. 计算各个索引执行所需要执行的成本。
4. 对各个索引所需要执行的成本，找出最低的那个方案。

全表扫描的成本

计算IO成本：

• 我们首先从表的status中找出Data_Length的大小，就是整个聚簇索引的大小，然后计算它一共有多少页。

Data_Length计算页的方法：Data_Length / (页的大小 = 16 * 1024 = 16KB)

• 然后我们就可以直接计算出它的IO成本即 页数 * 1.0 + 1.1。(1.1是一个微调值)

计算CPU成本：

• 首先从表的status中找到Rows的大小，Rows是一个不准确值。
• 找到行的大小，所以CPU成本为**行数 * 0.2 + 0.01。(0.01是微调值)

因此，我们可以将这两个成本相加，作为整个表扫描的总成本。

利用索引查询的成本

区间的索引条件

如果我们选择的索引是在区间条件下执行的。

where key1 > 10 and key1 < 1000  # 在计算单个索引的成本时对于其他条件直接为true。

就会进入以下步骤

1. 我们需要对二级索引的IO成本进行计算，当然呢，在Mysql中它对于一个范围查询的二级索引直接粗暴的定义其IO成本为读取一个页面的成本，就是1 * 1.0 = 1
2. 我们就要找到需要回表的记录行，首先找出最左边的区间的记录所在的页和最右边区间所在的页。
3. 如果两个在同一页，直接计算中间隔了几个数据行。
4. 如果两个不在同一页，就找出其所在页的父页，在判断两个记录的父页是否在同一页，在同一页就计算中间隔了几个页，然后乘以相应每页的数据行的数量。如果不在就是递归处理在不在的问题了。
5. 我们找到了间隔的记录行n，这个时候让CPU从二级索引找到这n条数据行所需的成本就是 n*0.2 + 0.01。
6. 紧接着我们拿着主键值回表，在MySQL中设计者有直接粗暴的将回表操作的IO成本直接计算为一个页面的IO成本，不需要计算别的比如索引页面之类的。所以我们n条记录回表的IO成本就是n * 1 。
7. 然后我们需要计算每次回表后的CPU成本，我们需要对回表后完整的数据行对其进行其他条件的判断，所以CPU成本为 n * 0.2。

所以IO成本为1 + n * 1，CPU成本为n*0.2 + 0.01 + n * 0.2。

单点区间

where key1 in (a,b,c,...,z)

当我们选择的指数的条件是上述单点区间的情况时

我们查询n个单点区间。

• 首先需要进行n次的IO读取单点范围，就相当于最小左区间和最大右区间都是一个值。就需要n * 1 的IO成本。
• 然后就是查询记录，CPU成本就是总的记录数*0.2，后面的回表流程其实是和上面一样的。不在赘述。

最后找出成本最小的，选择对应方法执行SQL。

index dive

我们将这样从索引中找到最小左边界和最大右边界的过程计算索引的数量称为index dive。

当然我们找到一个大区间进行一次index dive，但是in(a,b,c…d)这样每一个参数都是一个单点区间，就要进行多次index dive。in里面的参数多起来，特别是in (sql) 嵌套子查询，就会使参数爆炸了，单点区间是导致超出index dive上线的主要原因。

MySQL有一个index dive的上限，默认值为200。

mysql> show variables like '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set, 1 warning (0.00 sec)

像上面我们利用索引计算范围的那种计算成本的方式，仅适用于区间范围数量小的情况下，当大于index dive的上限，就不能使用index dive了，就得使用索引的数据进行估算。

如何估算？

show index from 表名;

我们首先获得MySQL数据字典中统计的该表的Rows即行数，这个值是不准确的，是估计值。（后面解释）

然后通过上面语句获得的Cardinality列对应的索引的参数，即该索引列的基数，即索引列的值不重复的列的数量。

将Rows / Cardinality 就可以得到每个索引值重复行数的平均值。

根据每个值的重复次数乘以单点间隔的数量，我们就可以作为每个单点间隔匹配的记录数。

连接查询计算成本

查询驱动器表后获得的记录数称为驱动器表的扇出数。

对于被驱动表计算其最后一条记录的数量，当索引可以直接用于计算条目数量时，对于不使用索引的情况，我们只能猜测，即对其进行评估(启发式规则)。最后，将风扇从驱动器工作台中取出。

然后，如果我们想要计算连接成本，则需要确定如何连接。

• 左右连接。因为左右两侧是固定的，所以从动台和从动台是固定的。但有时可以将外部连接优化为内部连接。
  
  [En]

  connect left and right. Because the left and right sides are fixed, the driven table and the driven table are fixed. But sometimes it is possible to optimize the outer connection to the inner connection.*

• 内部连接。左右两边都不是固定的，可以作为动因表，所以有必要对两者的成本进行计算。
  
  [En]

  Internal connection. The left and right sides are not fixed and can be used as a driver table, so it is necessary to calculate the cost of both of them.*

所以流程如下：

1. 确定驱动表。
2. 计算驱动表执行的最优计划，即上文的单表查询计算成本。
3. 然后将驱动表的扇出 * 被驱动表的执行的最优成本。
4. 将2，3步骤成本相加，即连接成本。

ps：内和外连接都是一样的，区别内连接需要确定哪个作为驱动表成本更低。

我们将知道，如果两个表连接在一起，则受驱动表的每个结果行都将作为常量传递给受驱动表进行查询。因此，如果您对联接条件有索引，则可以加快联接速度，否则您将不得不执行全表扫描。

当然，如果驱动表的搜索条件可以有一个索引，那就更好了。它还可以加快最终结果的计算速度。

我在之前的总结文章中，有一个错误，就是我提出一个能不能将被驱动表在自身搜索条件筛选后应该缓存起来这个观点，其实是不对的，如果没有被驱动表自身搜索条件进行是没有意义的。而且因为驱动表的结果行也是作为一个参数的搜索条件连接的，然后一条一条的进行设置参数搜索被驱动表符合的结果行。

调整成本常数

mysql.server_cost

我们知道的从磁盘从IO到内存的成本常数是1.0

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2020-12-17 14:54:07 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2020-12-17 14:54:07 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2020-12-17 14:54:07 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2020-12-17 14:54:07 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2020-12-17 14:54:07 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2020-12-17 14:54:07 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

• disk_temptable_create_cost 磁盘中创建临时表的成本参数
• disk_temptable_row_cost 磁盘中的临时表读入页的成本参数
• key_compare_cost 键进行比较的成本参数
• …其他的就不介绍了差不多
• row_evaluate_cost 这个就是CPU检测一条记录的成本参数，调高会让优化器尽可能使用索引减少检测的记录条数。

如果更新直接使用update语句即可
然后让系统刷新以下这个值   flush optimizer_costs;

mysql.engine_cost

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2020-12-17 14:54:07 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2020-12-17 14:54:07 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

• io_block_read_cost 从磁盘IO一个块block同样就是页到内存的成本参数，提高就会让优化器尽量减少IO即从磁盘读的条数，即尽可能使用索引。就是我们上面计算的IO成本。
• memory_block_read_cost 从内存读块即页的成本参数。

MySQL统计数据

我们在上面所过全表扫描计算成本时我们需要拿出表的Rows即行数这个参数，这一些关于表的，索引的行数等等被叫做统计数据。

MySQL有两种统计数据存储方式

• 基于磁盘的永久性统计数据
  
  [En]

  permanent disk-based statistics*

• 基于内存的非永久性统计数据
  
  [En]

  memory-based non-permanent statistics*

两种模式，内存需要每次启动MySQL进行数据统计，然后关闭统计数据就消失了。默认还是磁盘的永久存储。

基于磁盘的统计数据

统计数据可以分为两类，一类是表的统计数据，另一类是指数的统计数据。

mysql> show tables from mysql like '%innodb%';
+----------------------------+
| Tables_in_mysql (%innodb%) |
+----------------------------+
| innodb_index_stats         |  // 索引的统计数据
| innodb_table_stats         |  // 表的统计数据
+----------------------------+
2 rows in set (0.13 sec)

innodb_table_stats表

mysql> select * from mysql.innodb_table_stats;
+---------------+-----------------------------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name                              | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-----------------------------------------+---------------------+--------+----------------------+--------------------------+
| mall          | cms_help                                | 2022-04-14 15:26:26 |      0 |                    1 |                        0 |

• database_name 数据库名
• table_name 表名
• last_update 上次更新的时间
• n_rows 即表行数
• clustered_index_size 聚簇索引占的页面数
• sum_of_other_index_sizes 其他索引占用总的页面数

n_rows统计方式

首先取出几个叶节点，然后计算这些叶节点的平均行数。

然后将所有叶子的页面相乘，这是叶子节点的总数。这就是为什么它不准确的原因。

clustered_index_size 统计方式

统计页数，分为两个段，一个叶段，一个非叶段，从索引根节点找到两个段，然后从段的结构中找出占用的页数，过程如下。

• 首先统计碎片面积。如果碎片区域已满，则有32页。每个碎片区将占据一页。如果碎片区域未满，则页数将计入页数。
  
  [En]

  first of all, count the fragment area. If the fragment area is full, there are 32 pages. Each fragment area will occupy one page. If the fragment area is not full, the number of pages will be counted as the number of pages.*

• 然后统计独占段的面积，即直接计算链表中的链数，然后直接计算64页。无论它是不是满的，它都将被算作满的。这也是不准确的原因。
  
  [En]
  then count the area of the exclusive segment, that is, directly calculate the number of chains in the linked list, and then directly * 64 pages. Whether it is full or not, it will be counted as full. This is also the reason for the inaccuracy.

sum_of_other_index_sizes 统计类似

innodb_index_stats表

统计项有如下：

• n_leaf_pages: 表示该索引的叶子节点占用多少个页面。
• size: 表示该索引一共占用的页面数
• n_diff_pfxNN: 表示对应索引列不重复的值有多少，其中的NN对于联合索引来说就是前01就是前一个列组合有几个不重复值，02就是前两个列组合有几个不重复值。

对于NULL的定义

在MySQL中，跟null的任何表达式都为null。

null值对于二级索引的不重复值来说有很大影响。对于index dive 来说就需要用到不重复值来作为评估成本的参数。

复习：当in(…)里面的参数太多，就不会执行index dive而是直接估计，查询不重复值然后除以总的记录数，就可以得到每个单点区间的大概值数。

mysql> show variables like 'innodb_stats_method';
+---------------------+-------------+
| Variable_name       | Value       |
+---------------------+-------------+
| innodb_stats_method | nulls_equal |
+---------------------+-------------+
1 row in set, 1 warning (0.08 sec)

对于null值来说，默认是认为所有的null都是相等的。

nulls_unequal : 所有null都不为相等的。

nulls_ignored : 直接把null忽略掉。

Original: https://www.cnblogs.com/duizhangz/p/16305481.html
Author: 大队长11
Title: 探究MySQL中SQL查询的成本