深入探索MySQL:成本模型解析与查询性能优化

码到三十五 : 个人主页



在数据库管理系统中,查询优化器是一个至关重要的组件,它负责将用户提交的SQL查询转换为高效的执行计划。在MySQL中,查询优化器使用了一个称为“成本模型”的机制来评估不同执行计划的优劣,并选择其中成本最低的那个。本文将深入探讨MySQL的成本模型,以及如何利用这一知识来优化查询性能。

目录

    • 一、成本模型简介
    • 二、优化器如何工作
    • 三、如何利用成本模型优化查询
    • 四、成本值的存储和配置
      • 常用的成本条目
    • 五、全表扫码成本计算
      • 成本计算步骤
      • 优化器决策
      • 实际考虑因素
    • 结语

一、成本模型简介

成本模型是查询优化器用来估算查询执行成本的一组规则和算法。对于给定的查询,优化器会考虑多种可能的执行计划,并使用成本模型来预测每种计划的执行效率。执行成本通常是一个抽象的数值,它综合了CPU时间、I/O操作、内存使用等多个因素。

在MySQL中,成本模型主要基于以下几个方面的考量:

  1. 数据表的统计信息:包括表的行数、列的基数(不同值的数量)、索引的唯一性等。这些信息对于评估查询的过滤效果和索引的选择性至关重要。

  2. 索引的使用:索引可以显著提高查询性能,但并非所有情况下都是最优选择。成本模型会评估使用索引带来的I/O减少与索引维护成本之间的权衡。

  3. 连接操作:对于涉及多个表的查询,成本模型会考虑不同连接策略(如嵌套循环连接、哈希连接等)的成本。

  4. 排序和分组操作:这些操作通常需要额外的CPU和内存资源。成本模型会估算不同排序和分组策略的成本,并选择最优方案。

在这里插入图片描述

二、优化器如何工作

MySQL的查询优化器在执行查询之前会经历以下几个步骤:

  1. 解析查询:将SQL文本转换为抽象语法树(AST)。

  2. 预处理:检查查询的语义正确性,进行常量折叠等优化。

  3. 查询重写:根据规则和启发式方法修改原始查询,以简化结构或提高性能。

  4. 生成执行计划:考虑所有可能的执行路径,并使用成本模型评估每种路径的成本。

  5. 选择最优执行计划:根据成本模型的估算结果,选择成本最低的执行计划。

  6. 执行查询:按照选定的执行计划执行查询并返回结果。

三、如何利用成本模型优化查询

了解MySQL的成本模型对于数据库管理员和开发来说是非常有价值的。下面的一些实践建议可以帮助你利用成本模型来优化查询性能:

  1. 保持统计信息更新:定期运行ANALYZE TABLE命令来更新表的统计信息,确保优化器有准确的数据来评估查询成本。

  2. 合理设计索引:根据查询模式和数据分布来设计索引,避免过度索引导致的性能下降。使用EXPLAIN命令来检查查询是否使用了合适的索引。

  3. 优化查询语句:简化复杂的SQL查询,避免不必要的连接、子查询和计算。使用索引覆盖扫描(Covering Index)来减少数据查找的开销。

  4. 调整配置参数:某些MySQL配置参数会影响成本模型的计算方式。例如,optimizer_search_depth参数可以控制优化器搜索执行计划的深度。根据你的硬件环境和查询负载来调整这些参数。

  5. 监控和分析:使用性能监控工具(如Percona Monitoring and Management, PMM)来跟踪查询的性能指标,并找出性能瓶颈。结合EXPLAIN命令的输出和慢查询日志来分析问题查询的执行计划。

在这里插入图片描述

四、成本值的存储和配置

MySQL在server_costengine_cost这两个系统表中存储了默认的成本值。这些表位于MySQL的系统数据库中(通常是mysql数据库)。服务器在启动时会读取这些成本值到内存中,以便在运行时使用。如果需要,管理员可以通过执行特定的命令(如FLUSH OPTIMIZER_COSTS)来重新从磁盘加载成本表。

重要的是这些成本值是特定于服务器的,并且不会复制到副本或备用服务器。这意味着每台服务器的成本模型可能会根据其硬件配置、工作负载和性能调优策略而有所不同。

常用的成本条目

  • row_evaluate_cost(默认值通常为0.2):这个成本值代表处理一行数据时的CPU成本。随着查询需要处理的行数增加,这个成本也会相应增加。计算公式是:CPU成本 = 行数 * row_evaluate_cost。

  • io_block_read_costmemory_block_read_cost(默认值通常为1.0):这两个成本值分别代表从磁盘和内存中读取一个数据块(通常是一个数据页,大小约为16KB)的成本。IO成本的计算公式是:IO成本 = (总数据大小(以字节为单位)/ 1024) * io_block_read_cost 或 memory_block_read_cost。

  • disk_iotask_cost(磁盘I/O任务成本):这个值表示执行一次磁盘I/O操作的成本。由于磁盘I/O操作通常比内存操作要慢得多,因此这个成本值相对较高。优化器在考虑是否使用索引或进行全表扫描时会考虑这个成本。

  • key_compare_cost(键比较成本):当MySQL使用索引来过滤数据时,需要对索引键进行比较。这个成本条目表示进行一次键比较的成本。这个值通常较低,因为键比较操作相对较快。

  • memory_temptable_create_cost(内存临时表创建成本):在某些查询中,MySQL可能需要创建临时表来存储中间结果。这个成本条目表示在内存中创建一个临时表的成本。如果内存不足,MySQL可能会选择使用磁盘来存储临时表,这会增加I/O成本。

  • memory_temptable_batch_row_cost(内存临时表批量行成本):当向内存临时表中插入多行数据时,这个成本条目表示每插入一批数据的成本。这个值通常较低,因为批量插入比单独插入每一行要高效。

  • disk_temptable_create_cost(磁盘临时表创建成本):如果MySQL选择在磁盘上创建临时表,这个成本条目表示创建磁盘临时表的成本。这个值通常比内存临时表创建成本要高,因为磁盘操作更慢。

  • disk_temptable_batch_row_cost(磁盘临时表批量行成本):类似于内存临时表批量行成本,但这个成本条目是针对磁盘临时表的。它表示向磁盘临时表中批量插入数据的成本。

  • sort_merge_passes(排序合并传递成本):在进行排序操作时,如果数据量很大且内存不足,MySQL可能需要使用归并排序算法。这个成本条目表示进行一次归并传递的成本。归并排序涉及多次合并传递,因此这个成本在评估排序操作的总体成本时很重要。

要获取特定MySQL实例中这些成本条目的实际值,可以查询mysql系统数据库中的server_cost和engine_cost表:

SELECT * FROM mysql.server_cost;  
SELECT * FROM mysql.engine_cost;

在这里插入图片描述

要查看特定表的信息,包括其数据大小(Data_length字段),可以执行以下SQL查询:

SHOW TABLE STATUS LIKE 'your_table_name';

在这个查询结果中,Data_length字段表示表的数据部分占用的字节数。这个值可以用来计算读取整个表数据的IO成本。

在这里插入图片描述

五、全表扫码成本计算

MySQL 优化器会考虑那些因素来决定是否执行全表扫描,以及如何计算其成本的呢,下面我们来基于成本原理计算一下:

我们有一个 employees 表,其中包含员工信息,如 ID、姓名、部门和薪水等。该表具有以下特点:

  • 表大小:约 1GB(这取决于每行数据的大小和总行数)
  • 总行数:5,000,000 行
  • 每行数据大小:约 200 字节(包括所有字段)
  • 数据页大小:16KB(InnoDB 默认页大小)
  • 存储引擎:InnoDB
  • 无有效索引:对于我们要执行的特定查询,没有可以利用的索引

成本计算步骤

  1. 确定数据页数量

    • 首先,计算表占用的数据页数量。由于每行数据约 200 字节,每个数据页 16KB,每个数据页可以容纳大约 80 行数据(16,384 字节 / 200 字节 = 81.92,取整为 80)。
    • 因此,整个表占用的数据页数量为 5,000,000 行 / 80 行/页 = 62,500 页。
  2. I/O 成本计算

    • 假设每次从磁盘读取一个数据页的成本是 1.0(这个值可能因硬件性能而异)。
    • I/O 成本 = 数据页数量 × 每次读取成本 = 62,500 页 × 1.0 = 62,500。
  3. CPU 成本计算

    • CPU 成本通常与需要处理的行数成正比。假设每行数据处理的 CPU 成本是 0.2(这个值也是假设的,实际值可能不同)。
    • CPU 成本 = 总行数 × 每行处理成本 = 5,000,000 行 × 0.2 = 1,000,000。
  4. 总成本计算

    • 总成本 = I/O 成本 + CPU 成本 = 62,500 + 1,000,000 = 1,062,500。

这个总成本是一个估算值,用于与优化器考虑的其他查询执行计划(如使用索引)进行比较。请注意,这里的成本是一个相对值,用于比较不同执行计划的优劣,而不是一个绝对值或货币成本。

优化器决策

基于上述成本计算,如果优化器发现使用索引的成本低于全表扫描的成本,它会选择使用索引。否则,如果没有合适的索引或全表扫描被认为更高效(例如,在需要检索表中大部分行的情况下),优化器将选择全表扫描。

实际考虑因素

在实际应用中,全表扫描的成本会受到多种因素的影响:

  • 缓存中的数据:如果表的部分或全部数据已经缓存在内存中(如 InnoDB 的缓冲池),则实际的 I/O 成本可能会降低。
  • 系统负载:高并发环境下的系统负载可能会影响 CPU 和 I/O 的性能。
  • 表的结构和存储格式:表的列数、数据类型和存储格式(如压缩)都会影响数据的存储和检索效率。
  • 硬件和配置:服务器的硬件配置(如 CPU 速度、内存大小、存储性能)和 MySQL 的配置设置(如缓冲区大小、I/O 相关参数)也会对全表扫描的成本产生显著影响。

结语

MySQL的成本模型是查询优化器的核心组件之一,它对于生成高效的执行计划至关重要。通过深入了解成本模型的工作原理,并结合实际的查询优化实践,可以显著提高数据库的性能和响应速度。



听说...关注下面公众号的人都变牛了,纯技术,纯干货 !

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/317576.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Cloudflare高级防御规则 看看我的网站如何用防御的

网站已趋于稳定,并且经过nginx调优。我想先分享一下Cloudflare的WAF规则,因为这是最有效的防御之一,可以抵御大量恶意攻击流量,我已经验证了数月。 对于海外独立站电商网站,Cloudflare的CDN服务是首选,它强…

Springboot+vue+小程序+基于微信小程序的在线学习平台

一、项目介绍    基于Spring BootVue小程序的在线学习平台从实际情况出发,结合当前年轻人的学习环境喜好来开发。基于Spring BootVue小程序的在线学习平台在语言上使用Java语言进行开发,在数据库存储方面使用的MySQL数据库,开发工具是IDEA。…

浅谈 HTTPS

文章目录 HTTPS 简介HTTPS 特点与 HTTP 的区别HTTPS 工作流程1. 服务端生成密钥对2. 服务端申请数字证书3. 服务端发送数字证书4. 客户端验证数字证书5. 客户端解析证书内容6. 客户端传送加密信息7. 服务端解密信息8. 双方协商生成会话密钥并交换9. 使用会话密钥进行通信 总结 …

第八篇:隔离即力量:Python虚拟环境的终极指南

隔离即力量:Python虚拟环境的终极指南 1 引言 在编程的多元宇宙中,Python语言犹如一颗闪耀的星辰,其魅力不仅仅在于简洁的语法,更在于其庞大而繁荣的生态系统。然而,随着应用的增长和复杂性的提升,开发者们…

『大模型笔记』Code Example: Function Calling with ChatGPT

Code Example: Function Calling with ChatGPT 文章目录 一. Code Example: Function Calling with ChatGPT二. 参考文献一. Code Example: Function Calling with ChatGPT from openai import OpenAI from dotenv import load_dotenv import json# --------------------------…

虚拟机安装与配置win7

一、安装镜像 Windows7 64位 ed2k://|file|cn_windows_7_ultimate_with_sp1_x64_dvd_u_677408.iso|3420557312|B58548681854236C7939003B583A8078|/ 建议迅雷下载 二、VMware 安装win7 1.新创自定义虚拟机 2.默认即可 3.iso文件我们自己下载,选择一个空的磁盘 4.…

基于改进遗传优化的BP神经网络金融序列预测算法matlab仿真

目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.本算法原理 4.1 遗传算法(GA)原理 4.2 BP神经网络原理 4.3 遗传优化BP神经网络结合应用 4.4 遗传算法简要改进 5.完整程序 1.程序功能描述 基于改进遗传优化的BP神经网络金融…

键盘更新计划

作为 IT 搬砖人,一直都认为键盘没有什么太大关系。 每次都是公司发什么用什么。 但随着用几年后,发现现在的键盘经常出问题,比如说调节音量的时候通常莫名其妙的卡死,要不就是最大音量要不就是最小音量。 按键 M 不知道什么原因…

python 怎么调用R

如何在python中调用R?这其中包括了如何调用R的对象(函数和包),R和python的对象如何互相转换,以及如何调用R的脚本(外界参数的输入)。python提供了一个模块rpy2,可以较好地完成这项工…

Leetcode 145:二叉树的后序遍历(迭代法)

给你一棵二叉树的根节点 root ,返回其节点值的 后序遍历 。 思路: 迭代法的思路是,使用栈,一层一层的将树节点遍历入栈。 比如下面这个树,使用迭代法,1)第一层,让根节点入栈。2&a…

kubectl_入门_Pod控制器

Pod控制器 在k8s中,按照pod的创建方式可以将其分为两类 自主式pod:k8s直接创建出来的pod,这种pod删除后就没有了,也不会重建控制器创建的pod:通过控制器创建的pod,这种pod删除了之后还会自动重建 1. 什么…

图像处理的一些操作(2)

图像处理 9. 转换类型9.1转换成浮点数类型9.2转换成无符号字节类型 10.颜色空间转换10.1RGB转GRAY10.2RGB转HSV10.3RGB转LAB10.4HSV转RGB10.5LAB转RGB10.6 convert_colorspace函数进行颜色转换 11.标签化处理图像11.1导入模块11.2加载图片11.3RGB图像转灰度图像11.4遍历图像11.…

WSL2-Ubuntu使用Conda配置百度飞浆paddlepaddle虚拟环境

0x00 缘起 本文将介绍在WSL2-Ubuntu系统中,使用Conda配置百度飞浆paddlepaddle虚拟环境中所出现的各种问题以及解决方法,最终运行"run_check()"通过测试。 在WSL2中配置paddlepaddle不像配置Pytorch那样顺滑,会出现各种问题(如:库的文件缺失、不知道如何匹配C…

nginx下载安装配置(含ssl)

下载安装环节 wget https://nginx.org/download/nginx-1.24.0.tar.gz tar -zxvf xxx.tar.gz yum -y install pcre-devel openssl openssl-devel ./configure --prefix/usr/local/nginx --with-http_stub_status_module --with-http_ssl_module --with-stream make & make i…

APScheduler定时器使用:django中使用apscheduler,使用mysql做存储后端

一、基本环境 python版本:3.8.5 APScheduler3.10.4 Django3.2.7 djangorestframework3.15.1 SQLAlchemy2.0.29 PyMySQL1.1.0二、django基本设置 2.1、新增一个app 该app用来写apscheduler相关的代码 python manage.py startapp gs_scheduler 2.2、修改配置文件s…

8. Django 表单与模型

8. 表单与模型 表单是搜集用户数据信息的各种表单元素的集合, 其作用是实现网页上的数据交互, 比如用户在网站输入数据信息, 然后提交到网站服务器端进行处理(如数据录入和用户登录注册等).网页表单是Web开发的一项基本功能, Django的表单功能由Form类实现, 主要分为两种: dj…

翻译: 什么是ChatGPT 通过图形化的方式来理解 Transformer 架构 深度学习三

合集 ChatGPT 通过图形化的方式来理解 Transformer 架构 翻译: 什么是ChatGPT 通过图形化的方式来理解 Transformer 架构 深度学习一翻译: 什么是ChatGPT 通过图形化的方式来理解 Transformer 架构 深度学习二翻译: 什么是ChatGPT 通过图形化的方式来理解 Transformer 架构 深…

GoLang Gin实际使用

所有代码同步到Admin/gitDemo - Gitee.comhttps://gitee.com/mec-deployment-team_0/git-demo/tree/dev/ 1.创建Gin框架 一般设计一个常规的web项目,都需要以下几个模块 runApp 主函数,运行整个项目routes 路由控制,管理跳转以及路由分组co…

微软如何打造数字零售力航母系列科普06 - 如何使用微软的Copilot人工智能

如何使用微软的Copilot人工智能? Copilot和ChatGPT有很多相似之处,但微软的聊天机器人本身就有一定的优势。以下是如何对其进行旋转,并查看其最引人注目的功能。 ​​​​​​​ (资料来源:Lance Whitney/微软&…

ZooKeeper 搭建详细步骤之一(单机模式)

ZooKeeper 搭建详细步骤之三(真集群) ZooKeeper 搭建详细步骤之二(伪集群模式) ZooKeeper 搭建详细步骤之一(单机模式) ZooKeeper 及相关概念简介 搭建模式简述 ZooKeeper 的搭建模式包括单机模式、集群模…