探究MySQL中SQL查询的成本

  • Z时代
  • 2024-01-10
  • 分类：综合

database

成本

什么是成本，即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. 我们就要找到需要回表的记录行，首先找出最左边的区间的记录所在的页和最右边区间所在的页。

    1. 如果两个在同一页，直接计算中间隔了几个数据行。

    2. 如果两个不在同一页，就找出其所在页的父页，在判断两个记录的父页是否在同一页，在同一页就计算中间隔了几个页，然后乘以相应每页的数据行的数量。如果不在就是递归处理在不在的问题了。

  3. 我们找到了间隔的记录行n，这个时候让CPU从二级索引找到这n条数据行所需的成本就是n*0.2 + 0.01。

  4. 紧接着我们拿着主键值回表，在MySQL中设计者有直接粗暴的将回表操作的IO成本直接计算为一个页面的IO成本，不需要计算别的比如索引页面之类的。所以我们n条记录回表的IO成本就是**n * 1 ** 。

  5. 然后我们需要计算每次回表后的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 就可以得到每个索引值重复行数的平均值。

我们根据每个值重复的数量，乘以单点区间的数量，就充当每个单点区间匹配的记录数。

连接查询计算成本

对于驱动表的查询后的得到记录条数就叫做驱动表的扇出。

对于驱动表来说计算其最后记录的条数，当能用到索引直接使用索引计算其条数，对于用不到索引的情况呢，就只能进行猜，就是对其进行评估（启发式规则），最后得到驱动表的扇出。

然后我们要计算连接的成本，就需要确定连接的方式。

  • 左，右连接。因为左右固定，所以驱动表和被驱动表是固定的。但是有时候是可以将外连接优化成内连接的。

  • 内连接。左右不固定，都可以作为驱动表，所以需要对其两种进行成本的计算。

所以流程如下：

  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有两种统计数据存储方式

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

  • 基于内存的非永久统计数据

两种模式，内存需要每次启动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个页，每个碎片区会占用一页，没有占满32个就按碎片区的数量为页面数。

  • 然后统计专属段的区，就是直接计算链表中链的区数，然后区数直接*64页。不管有没有用满，都直接算用满了。这也是不准确的原因。

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忽略掉。

以上是 探究MySQL中SQL查询的成本 的全部内容， 来源链接： utcz.com/z/536588.html

回到顶部