探究MySQL中SQL查询的成本

   2023-02-10 学习力0
核心提示:成本什么是成本,即SQL进行查询的花费的时间成本,包含IO成本和CPU成本。IO成本:即将数据页从硬盘中读取到内存中的读取时间成本。通常1页就是1.0的成本。CPU成本:即是读取和检测是否满足条件的时间成本。0.2是每行的CPU成本。单表查询计算成本我们对其进行

成本

什么是成本,即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表

image

统计项有如下:

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

 
反对 0举报 0 评论 0
 

免责声明:本文仅代表作者个人观点,与乐学笔记(本网)无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责,若因作品内容、知识产权、版权和其他问题,请及时提供相关证明等材料并与我们留言联系,本网站将在规定时间内给予删除等相关处理.

  • sql:mysql:函数:TIMESTAMPDIFF函数实现TimeStamp字段相减,求得时间差
    sql:mysql:函数:TIMESTAMPDIFF函数实现TimeS
     函数内指定是minute,则最终结果value值的单位是分钟,如果函数内指定为hours,则最终结果value值单位为小时。//UPLOAD_TIME 减去 CREATE_DTTM 求得时间差,以分钟数计时select avg(TIMESTAMPDIFF(MINUTE,CREATE_DTTM,UPLOAD_TIME)) value,LEFT(CREATE_DTTM
    03-08
  • 去重复的sql(Oracle) 去重复的英文
    1.利用group by 去重复2.可以利用下面的sql去重复,如下  1) select id,name,sex from (select a.*,row_number() over(partition by a.id,a.set order by name) su from test a ) where su=1  2)select id,name,sex from (select a.*,row_number() over(p
    02-10
  • Oracle SQL七次提速技巧
    以下SQL执行时间按序号递减。1,动态SQL,没有绑定变量,每次执行都做硬解析操作,占用较大的共享池空间,若共享池空间不足,会导致其他SQL语句的解析信息被挤出共享池。create or replace procedure proc1as beginfor i in 1..100000 loop    execute imme
    02-10
  • Oracle\SQL  Server等及其他基本语句写法
    Oracle\SQL Server等及其他基本语句写法
    Oracle\SQL  Server等及其他基本语句写法目录一.Excel相关 11.Excel中写脚本范例: 12.提取字节 23. 提取单元格内字符 24.VLOOKUP函数: 2二.SQL语句汇总 21.建表: 22.增 33.删 44.查 65.改 236.Alter的应用 24三.数据库备份与恢复脚本 261. Oracle: 2
    02-10
  • SQL ORACLE case when函数用法
    case when 用法(1)简单case函数:格式:  case 列名   when 条件值1 then 选项1  when 条件值1 then 选项2......  else 默认值 end例如:  select   case job_level  when '1' then '1111'  when '2' then '2222'   when '3' then '3333
    02-10
  • mysql下如何执行sql脚本 执行SQL脚本
    1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 
    02-10
  • MySQL 5.7版本sql_mode=only_full_group_by问题
    用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景:最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%,或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题,记忆最为深刻的子查询,当初的
    02-10
  • ORACLE中通过SQL语句(alter table)来增加、删除
    1.添加字段:alter table  表名  add (字段  字段类型)  [ default  '输入默认值']  [null/not null]  ;2.添加备注:comment on column  库名.表名.字段名 is  '输入的备注';  如: 我要在ers_data库中  test表 document_type字段添加备注  comm
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
点击排行