Mysql内核查询成本计算

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Mysql内核查询成本计算

sql语句查询成本的组成

  I/O成本:将数据从磁盘读到内存的成本。

  CPU成本 数据读到内存后过滤、排序得到满足条件数据的成本。

  总成本  =I/O成本+CPU成本

注意:对于InnoDB存储引擎来说,页是磁盘和内存之间交互的基本单位,MySQL规定读取一个页 面花费的成本默认是1.0,读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2这些数字称之为成本常数,这两个成本常数我们最常用到,当然还有其他的成本常数。

查看mysql执行所有可能途径使用的成本

  1. 开启trace工具来一查究竟,开启trace工具会影响mysql性能,所以只能临时分析sql使用,用完之后立即关闭,具体方法参考:Mysql索引优化实战一_季风泯灭的季节的博客-CSDN博客

2. 使用explain查看,在explain后面加上 format=json 即可。

explain format=json SELECT  * from test5

单表查询的成本

 计算成本的过程

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

2、计算全表扫描的代价

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

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

 全表扫描的代价

    I/O成本=总页数*1.0+1.1

    CPU成本 =总记录数*0.2+1.0

   数据库表都维护了总记录数, InnoDB是一个估算的值,MYISAM是一个准确的值。通过SHOW TABLE STATUS 命令可以查到。总页数则用表的总大小➗16➗1024得到。

 二级索引的代价

    1. 使用二级索引的成本

         如果是范围查询则算一次IO,因为它属于顺序IO。如果是精准查询则有几个就是几次IO,因为它是随机IO.顺序IO的效率大概是随机IO的40-400倍。如 in(1,3)就是两次IO。二级索引的成本如下:

      I/O成本=待查找总页数(IO次数)*1.0

      CPU成本 =待回表总记录数*0.2+0.01

    2. 回表的代价

        mysql需先计算出要回表的记录数,如果总记录数小于10页,则精准计算,如果大于10页,则以前10页的平均记录数X页数(页数从父节点的关键字数统计得出)进行估算。

      I/O成本=待回表总记录数*1.0

      CPU成本 =总记录数*0.2

  使用二级索引时不用考虑回表的CPU成本。总成本等于上述三者之和。

通过上述比较我们可以联想到,当走索引需要回表的记录数接近或大于原表的总页数时,走索引回表造成的成本可能就比不走索引的成本要高,那么mysql优化器可能会选择全表扫描。

表关联查询的成本

MySQL中连接查询采用的是嵌套循环连接算法,驱动表会被访问一次,被 驱动表可能会被访问多次,所以对于两表连接查询来说,它的查询成本由下边两个部分 构成:

1. 单次查询驱动表的成本

2. 多次查询被驱动表的成本(具体查询多少次取决于对驱动表查询的结果集中有多少条

记录)

对驱动表进行查询后得到的记录条数称之为驱动表的 扇出 (英文名:fanout)。很显然 驱动表的扇出值越小,对被驱动表的查询次数也就越少,连接查询的总成本也就越低。 当查询优化器想计算整个连接查询所使用的成本时,就需要计算出驱动表的扇出值。

如果使用的是全表扫描的方式执行的单表查询,那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条(不借助索引)。

如果使用的是索引执行的单表扫描,那么计算驱动表扇出的时候需要猜满足除使用到 对应索引的搜索条件外的其他搜索条件的记录有多少条(借助索引先过滤)。

MySQL把这个猜的过程称之为 condition filtering 。当然,这个过程可能会使用到索 引,也可能使用到统计数据,也可能就是MySQL单纯的瞎猜。

两表连接的成本分析

连接查询的成本计算公式是这样的:

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

可以知道,要想优化表关联查询有两个方向:

  1.  尽量减少驱动表的扇出,即以小表做驱动表。

  2. 减少被驱动表的单次访问成本(因为扇出数存在,使得单次访问被驱动表),即尽可能的避免磁盘IO,去走索引,且让回表的记录数尽可能的少。

多表连接的成本

   多表连接的成本与两表连接类似,只是后一个表以前面的sql查询结果为扇出去查询,其查询性能消耗为 x+ny+nz....,x、y、z为各表查询成本,n为扇出数。同时,mysql在sql分析阶段付出的成本是n!。因此,它的表越多性能越差。

where 和on 的区别

1. 执行时间不同,on先执行从各个表中过滤数据后进行关联,再使用where对结果进行进一步过滤。

2. on只对被驱动表有效。

3.

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
11
分享
相关文章
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
198 66
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
48 8
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
59 11
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
84 6
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
68 9
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
39 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
42 3
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
54 2
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
178 15
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等