MySQL基于成本的优化

这篇具有很好参考价值的文章主要介绍了MySQL基于成本的优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

MySQL的成本是什么?MySQL在执行一个查询的时候,其实是有多种不同的方案的,但是最终会选择一种成本比较低的方案,那么这个成本都体现在什么地方?如何计算?

MySQL的成本

I/O成本 : 把数据从磁盘加入到内存的过程损耗的时间。 读取一个页面花费成本是1

CPU成本  : 读取以及检测结果是否满足对应的搜索条件,对结果集进行排序等操作损耗的时间。读取以及检测一条记录是否符合搜索条件的成本默认是0.2 。

基于成本的优化步骤是什么?

在执行一条查询语句之前,MySQL的查询优化器会找出来该语句使用的索引方案,对比成本之后选出来一个最低的方案,最低成本方案也叫做执行计划。 总结来看,有4步:

1. 根据搜索条件查找所有可能使用的索引

2. 计算全表扫描的代价

3. 计算不同的索引执行查询的代价

4. 对比各种执行方案,找出成本最低的方案

根据搜索条件查找所有可能使用的索引

对于B+树索引来说,只要索引列和常数使用=<=>INNOT INIS NULLIS NOT NULL><>=<=BETWEEN!=(不等于也可以写成<>)或者LIKE操作符连接起来,就可以产生一个所谓的范围区间LIKE匹配字符串前缀也行),也就是说这些搜索条件都可能使用到索引

计算全表扫描的代价

全表扫描的过程是:把聚簇索引中的记录都依次和给定的搜索条件做一下比较,把符合搜索条件的记录加入到结果集中,所以需要将聚簇索引对应页面加载到内存中,然后在对其进行筛选。 查询成本 是 I/O成本 + CPU成本。 

聚簇索引占用的页面数量决定 I/O成本

表中的记录数量决定 CPU成本。 

这两个数量从何而来? MySQL的大佬为每个表维护了一个统计信息,这个数据已经被收集存放好了。 我们直接使命命令查看即可:

SHOW TABLE STATUS LIKE 'single_table'\G

MySQL基于成本的优化

 找到我们关心的参数,Rows 和Data_length ,其中Rows表送的记录数量,是模糊的,大致准确的。 我们重点来看一下Data_length,该参数表示该表占用的存储空间的字节数,对于InnoDB存储引擎来说,该值就是聚簇索引占用的存储空间大小。

Data_length = 聚簇索引的页面数量 * 每个页面的大小

于是我们很容易的就求出来了聚簇索引页面的数量。 有了聚簇索引页面的数量和rows,我们就可以计算出来全表扫描的成本了。

计算不同索引执行查询的代价

MySQL查询优化器会查找到所有可能用到的索引,然后分别计算其成本 ,然后还要计算使用联合索引的成本。 如果是唯一二级索引的话,会优先分析。 

建表语句

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

搜索条件

SELECT * FROM single_table WHERE 
    key1 IN ('a', 'b', 'c') AND 
    key2 > 10 AND key2 < 1000 AND 
    key3 > key2 AND 
    key_part1 LIKE '%hello%' AND
    common_field = '123';

先来分析idx_key2 执行查询的成本分析

MySQL基于成本的优化

 对于二级索引+ 回表的方式,成本依赖于两个方面

一:范围区间的数量。不管该范围的二级索引导致占用了几个页面,默认一个范围和读取一个页面的成本是相同的,都是1 

二:需要回表的记录数。 对于本例子的范围查询,就是要找区间最左记录和区间最右记录,然后即可统计出来符合条件的索引记录条数了,每条记录的成本是0.2 。 这种方式叫做 index dive

如何统计具体的条数,简单来说,就是最左和最右两条记录都分别对应一个数据页,只需要记录页b和页c对应的目录项记录之间隔着几条记录,就相当于是页b和页c之间隔着几个数据页。

三:根据这些记录里面的主键值到聚簇索引中做回表操作。 MySQL默认回表一次的成本和访问一个页的成本一样,都是1 

四:回表操作后得到用户的完整记录,然后再监测其他搜索条件是否成立,每次是0.2 

idx_key1同理,不再赘述

基于索引统计数据的成本计算 适用于什么场景? 是为了解决什么样的问题呢?

SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

上面的查询会产生很多的单点访问区间,我们对每个区间都要去使用index dive 去查询区间最左记录和区间最右记录的话,很耗费性能,MySQL中默认会有一个参数,如果小于这个值,使用index dive查询,如果超过了这个值,就要用到我们上面说的,基于索引统计数据的成本进行估算。 

MySQL基于成本的优化

 这里所说的统计索引数据指的是这两个值:一是使用 SHOW TABLE STATUS 展示出的Rows 值,也就是一个表中有多少记录。 二是上面表格中的Cardinality 。 两者结合,可以计算出索引列中的值一个平均出现X次。 

假设上面的查询语句有20000个参数,那成本就是20000 *  X。 很明显,没有index dive准确。

连接查询的成本

上面说的,都是单表查询的成本,接下来我们看一下连接查询的成本。

根据之前的知识,MySQL连接查询采用的是循环嵌套连接算法,驱动表被访问一次,被驱动表被访问多次,所以查询的成本就是两个部分构成:一是单次查询驱动表的成本; 二是多次查询被驱动表的成本。  驱动表进行查询之后得到的记录条数称为被驱动表的扇出。 

两表连接的计算成本是:单次访问驱动表的成本 + 驱动表扇出数量 * 单次访问被驱动表的成本

对于外连接来说,驱动表是固定的,所以只用分别为驱动表和被驱动表选择成本最低的访问方法。

对于内连接来说,多了一步,不同的表作为驱动表最终查询的成本是不一样的,需要考虑最优的表的链接顺序。 然后分别为驱动表和被驱动表选择成本最低的访问方法。 文章来源地址https://www.toymoban.com/news/detail-475044.html

到了这里,关于MySQL基于成本的优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL】从执行计划了解MySQL优化策略

    在MySQL中,执行计划是优化器根据查询语句生成的一种重要的数据结构,它描述了如何通过组合底层操作实现查询的逻辑。当我们编写一条SQL语句时,MySQL会自动对其进行优化,并生成最优的执行计划以实现更快的查询速度。 各位精通MySQL的大佬们,像往常一样,我们经常会遇

    2024年02月16日
    浏览(33)
  • 【MySQL性能优化】- MySQL结构与SQL执行过程

    😄生命不息,写作不止 🔥 继续踏上学习之路,学之分享笔记 👊 总有一天我也能像各位大佬一样 🏆 博客首页   @怒放吧德德  To记录领地 🌝分享学习心得,欢迎指正,大家一起学习成长! 上阶段初步学习了索引与优化,以及对Explain的使用,接着来就来初识一下SQL执行

    2024年01月21日
    浏览(73)
  • Hive执行计划之什么是hiveSQL向量化模式及优化详解

    Hive开启向量化模式也是hiveSQL优化方法中的一种,可以提升hive查询速率,也叫hive矢量化。 问题1:那么什么是hive向量化模式呢? 问题2:hive向量化什么情况下可以被使用,或者说它有哪些使用场景呢? 问题3:如何查看hive向量化使用的相关信息? hive向量化模式是hive的一个特

    2024年02月08日
    浏览(44)
  • 玩转MySQL之SQL优化之EXPLAIN执行计划

    从今天开始本系列文章就带各位小伙伴学习数据库技术。 数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。 全文大约 【1965】字 ,不说废话,只讲可以让你学到技术、明

    2024年02月08日
    浏览(53)
  • MySQL优化:12种提升SQL执行效率的有效方法

    在数据库管理和优化的世界里,MySQL作为一个流行的关系型数据库管理系统,其性能优化是任何数据密集型应用成功的关键。优化MySQL数据库不仅可以显著提高SQL查询的效率,还能确保数据的稳定性和可靠性。 在本文中,我将介绍12种提升SQL执行效率的有效方法,并通过实用的

    2024年01月16日
    浏览(170)
  • MySQL执行流程_执行一条select语句,期间发生了什么

    MySQL执行流程 server层负责建立连接、分析和执行SQL 包括连接器、查询缓存、解析器、预处理器、优化器、执行器等,所有内置函数和所有跨存储引擎的功能在该层实现 存储引擎层负责数据的存储和提取 索引数据结构就是由存储引擎层实现,不同的存储引擎支持的索引类型也

    2024年02月04日
    浏览(54)
  • MYSQL04高级_逻辑架构剖析、查询缓存、解析器、优化器、执行器、存储引擎

    ①. 服务器处理客户端请求 ②. 连接层 系统(客户端)访问MySQL服务器前,做的第一件事就是建立TCP连接 经过三次握手建立连接成功后,MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取 用户名或密码不对,会收到一个Access denied for user错误,客户端程序结束执行用户名密码认

    2024年02月12日
    浏览(39)
  • MySQL SQL性能分析,快速排查SQL执行慢的原因(SQL优化 一)

    在开发和维护数据库应用程序时,优化SQL查询的性能是至关重要的。MySQL提供了一些强大的工具和技术,帮助我们进行SQL性能分析,找出潜在的瓶颈并进行相应的优化。 查看SQL的执行频率 show [ session| global ] status 命令查看服务器状态信息,可以查看当前数据库的INSERT、UPDATE、

    2024年02月07日
    浏览(102)
  • LLMs之Colossal-LLaMA-2:Colossal-LLaMA-2的简介(基于LLaMA-2架构+中文优化+扩充词表+仅千美元成本)、安装、使用方法之详细攻略

    LLMs之Colossal-LLaMA-2:Colossal-LLaMA-2的简介(基于LLaMA-2架构+中文优化+扩充词表+仅千美元成本)、安装、使用方法之详细攻略 导读 :2023年9月25日,Colossal-AI团队推出了开源模型 Colossal-LLaMA-2-7B-base = 8.5B的token 数据+ 6.9万词汇 +15 小时+ 不到1000美元 的训练成本 。Colossal-LLaMA-2项目的技

    2024年02月07日
    浏览(47)
  • 【数据库】执行计划中的两趟算法机制原理,基于排序算法来分析,算法的限制,执行代价以及优化

    ​ 专栏内容 : 手写数据库toadb 本专栏主要介绍如何从零开发,开发的步骤,以及开发过程中的涉及的原理,遇到的问题等,让大家能跟上并且可以一起开发,让每个需要的人成为参与者。 本专栏会定期更新,对应的代码也会定期更新,每个阶段的代码会打上tag,方便阶段学

    2024年02月05日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包