探究MySQL中SQL查询的成本

成本

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

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

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

单表查询计算成本

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

[En]

The specific steps for our analysis are as follows:

  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是微调值)

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

[En]

So we can add up the two costs to be the total cost of the full table scan.

利用索引查询的成本

区间的索引条件

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

[En]

If the index we choose is executed under the condition of an interval.

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)

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

[En]

When the condition of the index we choose is the case of the above single point interval

我们查询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]

According to the number of repeats of each value, multiplied by the number of single point intervals, we act as the number of records matched for each single point interval.

连接查询计算成本

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

[En]

The number of records obtained after querying the driver table is called the fan out of the driver table.

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

[En]

For the driven table to calculate the number of its last records, when the index can be used to calculate the number of entries directly, for the case where the index is not used, we can only guess, that is, to evaluate it (heuristic rules). Finally get the fan out of the drive table.

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

[En]

Then we need to determine how to connect if we want to calculate the cost of the connection.

  • 左右连接。因为左右两侧是固定的,所以从动台和从动台是固定的。但有时可以将外部连接优化为内部连接。
    [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:内和外连接都是一样的,区别内连接需要确定哪个作为驱动表成本更低。

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

[En]

We will know that if two tables are joined, each result row of the driven table is passed as a constant to the driven table for query. So if you have an index on the join condition, you can speed up the join, otherwise you will have to do a full table scan.

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

[En]

Of course, it would be better if the search criteria of the driven table can have an index. It can also speed up the calculation of the final result.

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

调整成本常数

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检测一条记录的成本参数,调高会让优化器尽可能使用索引减少检测的记录条数。
&#x5982;&#x679C;&#x66F4;&#x65B0;&#x76F4;&#x63A5;&#x4F7F;&#x7528;update&#x8BED;&#x53E5;&#x5373;&#x53EF;
&#x7136;&#x540E;&#x8BA9;&#x7CFB;&#x7EDF;&#x5237;&#x65B0;&#x4EE5;&#x4E0B;&#x8FD9;&#x4E2A;&#x503C;   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进行数据统计,然后关闭统计数据就消失了。默认还是磁盘的永久存储。

基于磁盘的统计数据

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

[En]

Statistics can be divided into two categories, one is the statistics of the table and the other is the statistics of the index.

mysql> show tables from mysql like '%innodb%';
+----------------------------+
| Tables_in_mysql (%innodb%) |
+----------------------------+
| innodb_index_stats         |  // &#x7D22;&#x5F15;&#x7684;&#x7EDF;&#x8BA1;&#x6570;&#x636E;
| innodb_table_stats         |  // &#x8868;&#x7684;&#x7EDF;&#x8BA1;&#x6570;&#x636E;
+----------------------------+
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统计方式

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

[En]

First take out a few leaf pages, and then calculate the average number of rows of these leaf nodes.

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

[En]

And then multiply the pages of all the leaves, which is the total number of leaf nodes. That’s why it’s not accurate.

clustered_index_size 统计方式

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

[En]

Count the number of pages, divided into two segments, one leaf segment, one non-leaf segment, find two segments from the index root node, and then find out the number of pages occupied from the structure of the segment, the process is as follows.

  • 首先统计碎片面积。如果碎片区域已满,则有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表

探究MySQL中SQL查询的成本

统计项有如下:

  • 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查询的成本

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505350/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 08 聚集函数

    聚集函数count max min sum avgcount(*)会统计null行,count列名则不会 分组group by 过滤分组havingwhere能做的having都能…

    数据库 2023年6月16日
    0150
  • PDF转换OFD(Java实用版)

    前言: 在项目中用到了,就写一下哈 OFD简介 百度百科:https://baike.baidu.com/item/OFD/56227163?fr=aladdin OFD(Open…

    数据库 2023年6月16日
    0191
  • MySQL 数据备份与恢复

    数据备份 使用 mysqldump 命令可以将数据库中的数据备份成一个文本文件,表的结构和数据以 SQL 的形式将存储生成的文本文件 mysqldump -u username -…

    数据库 2023年5月24日
    0183
  • Docker安装和配置

    sudo yum install -y docker-ce docker-ce-cli containerd.io 配置加速(可以理解为类似于Maven的阿里仓库,用于加速镜像的下…

    数据库 2023年6月6日
    0120
  • [spring]spring注入属性值的两种方式以及作用域

    4.IOC创建对象的过程 使用无参构造创造 设定为有参后,就会报错! 对象在被注册进去的时候,就被实例化了,直接使用就好。 5.IO注入 (1)前面的构造器注入 (2)set注入 …

    数据库 2023年6月16日
    0156
  • IntelliJ IDEA 断开svn连接

    1 设置菜单 2 进入pluglns 菜单,点击 browse repositonries….. 3 搜索 svn disconnect,然后安装插件 4 安装插件后,…

    数据库 2023年6月6日
    0210
  • 浅谈事务隔离级别、MVCC及相关特性

    文采不是很好,应该有一些表达不清楚的地方,请改正。 [En] Literary talent is not very good, there should be some uncl…

    数据库 2023年5月24日
    0133
  • MySQL实战45讲 6,7,8

    06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍? Connection连接与Session会话 通俗来讲,会话(Session)是通信双⽅从开始通信到通信结束期间的⼀个上下…

    数据库 2023年6月16日
    0130
  • 分享一直在维护简单实用高效的C++Socket框架Swa-server(开源+源码)

    Swa-server 开源框架* 适用于中小型游戏,如:养成、RPG、棋牌等;应用软件,如:聊天室等* 已经封套好底层socket管理,sql请求处理、数据加密解密* 拿来即可开工…

    数据库 2023年6月14日
    0156
  • FIO磁盘性能测试工具

    FIO磁盘性能测试工具 简介 一般我们测试硬盘或者存储的性能的时候,会用Linux系统自带的dd命令,因为是自带命令,简单易使用,因此一些客户喜欢使用dd命令来测试磁盘的读写性能。…

    数据库 2023年6月9日
    0126
  • 编程相关书单

    软工概论软件工程软件工程:实践者的研究方法 职业素养软技能码农翻身技术之瞳程序员修炼之道程序员的职业素养程序员的自我修养 程序设计计算机程序的构造和解释 (SICP) UML软件建…

    数据库 2023年6月11日
    0133
  • MySQL–SELECT检索语句

    1、检索单个列 SELECT prod_name FROM products; –上述语句利用 SELECT语句从 products表中检索一个名为prod_name的列。 Vi…

    数据库 2023年6月16日
    0149
  • MySQL中实现中文转拼音

    — 插入数据 INSERT INTO t_base_pinyin ( pin_yin_, code_ ) VALUES ( "a", 20319 ),( &q…

    数据库 2023年5月24日
    0118
  • JUC学习笔记(二)

    1.1、Synchronized synchronized 是 Java 中的关键字,是一种同步锁。它修饰的对象有以下几种: 修饰一个代码块,被修饰的代码块称为同步语句块,其作用的…

    数据库 2023年6月6日
    0127
  • 设计模式六大原则(简单总结)

    一、单一职责原则 通俗来说即一个类只负责一项职责(实现类要职责单一),可以降低类的复杂度,提高系统的可读性、可维护性,降低类修改时对其他功能的影响 二、里氏替换原则 即子类继承父类…

    数据库 2023年6月6日
    0104
  • Java中如何遍历字符串呢?

    字符串是程序开发中我们见的最多的一种数据类型 对字符串的操作,也是我们日常涉及的最多的一种操作方式,那么如何遍历字符串为字符并输出呢? 下面笔者讲述三种操作方式,如下所示 1.直接…

    数据库 2023年6月11日
    0101
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球