当前位置: 首页 > news >正文

SQL进阶理论篇(八):SQL查询的IO成本

文章目录

  • 简介
  • 数据库缓冲池
  • 查看缓冲池的大小
  • 数据页加载的三种方式
  • 通过 last_query_cost 统计 SQL 语句的查询成本
  • 总结
  • 参考文献

简介

本节将介绍磁盘IO是如何加载数据的,重点介绍一下数据库缓冲池的概念。主要包括:

  • 什么是数据库缓冲池,它在数据库中扮演了什么角色?
  • 对数据页进行加载的几种方式
  • 如何统计一条SQL语句中,需要在缓冲池中进行加载的页的数量。

数据库缓冲池

为了能够让数据表或者索引中的数据随时为我们所用,DBMS会申请一块内存来作为数据缓冲池。

数据缓冲池里会保存经常使用的数据,这样的话,当数据库进行页面读的时候,会首先来寻找该页面是否在缓冲池里,如果存在就直接读取,如果不存在,就会通过磁盘或者内存,将页面放进缓冲池里再进行读取。

缓冲池在数据库中的结构和作用如下图:

在这里插入图片描述

如果我们执行了类似update语句,改变了缓冲池里的数据,那么这些数据会立即同步到磁盘上吗?

当然不是。

实际上,当我们修改数据库中的记录时,首先会修改缓冲池中页的记录信息,然后数据库会以一定的频率将新的数据刷新回磁盘。所以不是每次发生更新操作 ,都会立即回写的。

比如说,当缓冲池空间不够用的时候,就需要释放掉一些不常用的页,这时候就会强行将这些页的数据回写到磁盘,然后在缓冲池里将这些页释放掉。

这里面有一个脏页(dirty Page)的概念,是指在缓冲池里被修改过,尚未回写,因此与磁盘上不同的数据页。

查看缓冲池的大小

如果使用的是MySQL的MyISAM引擎,其只缓存索引,不缓存数据,对应的键缓存参数为key_buffer_size,可以通过查看这个变量来查看缓冲池大小。

如果使用的是InnoDB引擎,则可以通过以下命令查看:

mysql > show variables like 'innodb_buffer_pool_size'

在这里插入图片描述

单位是B,转换成MB就是8MB。

如果想修改缓冲池大小为128MB,则可以通过:

set global innodb_buffer_pool_size = 134217728;

在InnoDB中,我们还可以同时开启多个缓冲池。

可以通过以下命令查看当前缓冲池的数量:

mysql > show variables like 'innodb_buffer_pool_instances'

默认情况下,其实是会有8个缓冲池,但是如果你的innodb_buffer_pool_size参数小于1G,那刚才的命令只会显示出1个缓冲池。

数据页加载的三种方式

如果缓冲池中没有我们想要的数据页,那么缓冲池有三种方式,可以将指定数据页加载进缓冲池,每种方式的读取效率会有不同。

  • 内存读取

如果该数据页是在内存里,那么直接读进缓冲池,效率还是很高的。

在这里插入图片描述

  • 随机读取

如果数据没有在内存里,那就是在磁盘里,因此我们需要在磁盘上对该页进行查找,假设整体时间是10ms,这 10ms 中有 6ms 是磁盘的实际繁忙时间(包括了寻道和半圈旋转时间),有 3ms 是对可能发生的排队时间的估计值,另外还有 1ms 的传输时间,将页从磁盘服务器缓冲区传输到数据库缓冲区中。

以上过程结束之后,我们才算完成了一页的读取,多页读取的话,时间会继续拉长。

在这里插入图片描述

  • 顺序读取

顺序读取其实是一种批量读取的方式,因为我们请求的数据在磁盘上往往都是相邻存储的,顺序读取可以帮我们批量读取页面,这样的话,一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。

采用批量读取的方式,即使是从磁盘上进行读取,平均一页的读取效率也比从内存中单独读取一个页的效率要高。

通过 last_query_cost 统计 SQL 语句的查询成本

如果我们想要查看某条 SQL 语句的查询成本,可以在执行完这条 SQL 语句之后,通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页的数量。

比如说,我们直接在聚集索引上查找一条指定记录:

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 900001;

运行结果只有一条,运行时间为 0.042s。

然后再看下查询优化器的成本,执行以下代码:

mysql> SHOW STATUS LIKE 'last_query_cost';

在这里插入图片描述

可以看到,我们只检索了一页。

那我们把查询搞复杂点,比如说查询 comment_id 在 900001 到 9000100 之间的评论记录呢?

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id BETWEEN 900001 AND 900100;

运行结果有100条记录,运行时间为 0.046s。

执行以下代码,查看查询优化器的成本:

mysql> SHOW STATUS LIKE 'last_query_cost';

在这里插入图片描述

可以看到我们大概进行了20个页的读取。

虽然读取的页变多了,但是两条SQL的查询时间基本一致。这是因为后台通过顺序读取,将页面一次性加载到了缓冲池里,然后再进行查找。所以虽然页数量增加了不少,但其实并没有消耗太多时间。

总结

注意,缓冲池跟我们在之前章里提过的查询缓存又不一样。

查询缓存服务的是查询结果集,它是指把查询结果缓存起来,这样下次遇到相同的查询就可以直接拿到结果。注意是相同查询才行,所以这种机制的查询缓存其实命中率不高,在MySQL8.0版本中已经弃用了查询缓存的功能。

而缓冲池是服务于数据库整体的IO操作,通过建立缓冲池来弥补磁盘文件和内存之间的速度鸿沟,从而提高整体的IO效率。

参考文献

  1. 28丨从磁盘I/O的角度理解SQL查询的成本

相关文章:

SQL进阶理论篇(八):SQL查询的IO成本

文章目录 简介数据库缓冲池查看缓冲池的大小数据页加载的三种方式通过 last_query_cost 统计 SQL 语句的查询成本总结参考文献 简介 本节将介绍磁盘IO是如何加载数据的,重点介绍一下数据库缓冲池的概念。主要包括: 什么是数据库缓冲池,它在…...

宝塔PostgreSQL设置数据库远程访问

宝塔PostgreSQL设置数据库远程访问 宝塔的PostgreSQL1. 添加数据库2. 打开PostgreSQL设置界面3. 修改配置4. 重载配置/重启数据库 Docker的PostgreSQL1. postgresql.conf2. pg_hba.conf3. 重启数据库 注意其他问题 宝塔PostgreSQL设置数据库远程访问?docker容器Post…...

蓝牙协议栈学习笔记

蓝牙协议栈学习笔记 蓝牙简介 蓝牙工作在全球通用的 2.4GHz ISM(即工业、科学、医学)频段,使用 IEEE802.11 协议 蓝牙 4.0 是迄今为止第一个蓝牙综合协议规范,将三种规格集成在一起。其中最重要的变化就是 BLE(Blue…...

XXE利用的工作原理,利用方法及防御的案例讲解

XXE(XML外部实体注入)利用是一种网络安全攻击手段,其中攻击者利用XML解析器处理外部实体的方式中的漏洞。这种攻击主要针对的是那些使用XML来处理数据的应用程序,尤其是当这些应用程序没有正确限制外部实体的处理时。通过XXE利用&…...

jpa 修改信息拦截

实现目标springbootJPA 哪个人,修改了哪个表的哪个字段,从什么值修改成什么值 import jakarta.persistence.*; import jakarta.servlet.http.HttpServletRequest; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; im…...

JavaEE 09 锁策略

1.锁策略 1.1 乐观锁与悲观锁 其实前三个锁是同一种锁,只是站在不同的角度上去进行描述,此处的乐观与悲观其实是指在预测的角度上看会发生锁竞争的概率大小,概率大的则是悲观锁,概率小的则是乐观锁 乐观锁在加锁的时候就会做较少的事情,加锁的速度较快,但是消耗的cpu资源等也会…...

javacv的视频截图功能

之前做了一个资源库的小项目,因为上传资源文件包含视频等附件,所以就需要时用到这个功能。通过对视频截图,然后作为封面缩略图,达到美观效果。 首先呢,需要准备相关的jar包,之前我用的是低版本的1.4.2&…...

Fiddler中AutoResponder的简单使用

AutoResponder,自动回复器,用于将 HTTP 请求重定向为指定的返回类型。 这个功能有点像是一个代理转发器,可以将某一请求的响应结果替换成指定的资源,可以是某个页面也可以是某个本地文件 1.使用 打开“Fiddler”,点击…...

K8S(一)—安装部署

目录 安装部署前提以下的操作指导(在master)之前都是三台机器都需要执行 安装docker服务下面的操作仅在k8smaster执行 安装部署 前提 以下的操作指导(在master)之前都是三台机器都需要执行 关闭防火墙 [rootk8smaster ~]# vim /etc/selinux/config [rootk8smaster ~]# swa…...

Kubernetes Pod 网段与主机内网网段互通

开发环境的需求 开发环境部署 K8s 后,服务器会部署在 K8s 里,通常 Pod 网段被隔离,主机无法访问 实际开发需求,往往需要当前开发调试的服务主机本地部署,其他服则在 K8s 内 因此,使用 K8s ,必…...

go学习redis的学习与使用

文章目录 一、redis的学习与使用1.Redis的基本介绍2.Redis的安装下载安装包即可3.Redis的基本使用1)Redis的启动:2)Redis的操作的三种方式3)说明:Redis安装好后,默认有16个数据库,初始默认使用0…...

娱乐新拐点:TikTok如何改变我们的日常生活?

在数字时代的浪潮中,社交媒体平台不断涌现,其中TikTok以其独特的短视频内容在全球范围内掀起了一场娱乐革命。本文将深入探讨TikTok如何改变我们的日常生活,从社交互动、文化传播到个人创意表达,逐步改写了娱乐的新篇章。 短视频潮…...

【Nginx】Nginx了解(基础)

文章目录 Nginx产生的原因Nginx简介Nginx的作用反向代理负载均衡策略动静分离 Nginx的Windows下的安装Linux下的安装Nginx常用命令 负载均衡功能演示 Nginx产生的原因 背景 一个公司的项目刚刚上线的时候,并发量小,用户使用的少,所以在低并发…...

十九)Stable Diffusion使用教程:ai室内设计案例

今天我们聊聊如何通过SD进行室内设计装修。 方式一:controlnet的seg模型 基础起手式: 选择常用算法,抽卡: 抽到喜欢的图片之后,拖到controlnet里: 选择seg的ade20k预处理器,点击爆炸按钮,得到seg语义分割图,下载下来: 根据语义分割表里的颜色值,到PS里进行修改: 语…...

虚拟机VMware安装centos以及配置网络

目录 1、CentOS7的下载2、CentOS7的配置3、CentOS7的安装4、CentOS7的网络配置 4.1、自动获取IP4.2、固定获取IP 5、XShell连接CentO 准备工作:提前下载和安装好VMware。VMware的安装可以参考这一篇文章:VMware15的下载及安装教程。 1、CentOS7的下载 …...

call 和 apply:改变对象行为的秘密武器(上)

🤍 前端开发工程师(主业)、技术博主(副业)、已过CET6 🍨 阿珊和她的猫_CSDN个人主页 🕠 牛客高级专题作者、在牛客打造高质量专栏《前端面试必备》 🍚 蓝桥云课签约作者、已在蓝桥云…...

工作中 docker 的使用积累

2 进入 openwrt 容器 docker exec -it openwrt /bin/sh3 查看 docker 信息 docker info4 启动容器 4 挂载 overlay mount -t overlay overlay -o lowerdirA:B,upperdirC,workdirworker /tmp/test -t overlay : 指定要挂载的文件系统类型为 overlayoverlay: 指定…...

初识SpringSecurity

目录 前言 特点 快速开始 导入依赖 运行项目 访问服务 权限控制 实现UserDetails接口 添加SecurityConfig配置类 测试接口DemoController 设置权限控制authorizeHttpRequests 结果分析 总结 前言 Spring Security是一个强大且高度可定制的身份验证和访问控制框架…...

大数据讲课笔记1.4 进程管理

文章目录 零、学习目标一、导入新课二、新课讲解(一)进程概述1、基本概念2、三维度看待进程3、引入多道编程模型(1)CPU利用率与进程数关系(2)从三个视角看多进程 4、进程的产生和消亡(1&#xf…...

技术点:实现大文件上传

大文件上传 实现思路 对于大文件上传考虑到上传时间太久、超出浏览器响应时间、提高上传效率、优化上传用户体验等问题进行了深入探讨,以下初略罗列各个知识点的实现思路: 大文件上传对文件本身进行了文件流内容 Blob 的分割,使用 Blob.pr…...

2025年能源电力系统与流体力学国际会议 (EPSFD 2025)

2025年能源电力系统与流体力学国际会议(EPSFD 2025)将于本年度在美丽的杭州盛大召开。作为全球能源、电力系统以及流体力学领域的顶级盛会,EPSFD 2025旨在为来自世界各地的科学家、工程师和研究人员提供一个展示最新研究成果、分享实践经验及…...

使用分级同态加密防御梯度泄漏

抽象 联邦学习 (FL) 支持跨分布式客户端进行协作模型训练,而无需共享原始数据,这使其成为在互联和自动驾驶汽车 (CAV) 等领域保护隐私的机器学习的一种很有前途的方法。然而,最近的研究表明&…...

【ROS】Nav2源码之nav2_behavior_tree-行为树节点列表

1、行为树节点分类 在 Nav2(Navigation2)的行为树框架中,行为树节点插件按照功能分为 Action(动作节点)、Condition(条件节点)、Control(控制节点) 和 Decorator(装饰节点) 四类。 1.1 动作节点 Action 执行具体的机器人操作或任务,直接与硬件、传感器或外部系统…...

OpenLayers 分屏对比(地图联动)

注:当前使用的是 ol 5.3.0 版本,天地图使用的key请到天地图官网申请,并替换为自己的key 地图分屏对比在WebGIS开发中是很常见的功能,和卷帘图层不一样的是,分屏对比是在各个地图中添加相同或者不同的图层进行对比查看。…...

全志A40i android7.1 调试信息打印串口由uart0改为uart3

一,概述 1. 目的 将调试信息打印串口由uart0改为uart3。 2. 版本信息 Uboot版本:2014.07; Kernel版本:Linux-3.10; 二,Uboot 1. sys_config.fex改动 使能uart3(TX:PH00 RX:PH01),并让boo…...

mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包

文章目录 现象:mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包遇到 rpm 命令找不到已经安装的 MySQL 包时,可能是因为以下几个原因:1.MySQL 不是通过 RPM 包安装的2.RPM 数据库损坏3.使用了不同的包名或路径4.使用其他包…...

docker 部署发现spring.profiles.active 问题

报错: org.springframework.boot.context.config.InvalidConfigDataPropertyException: Property spring.profiles.active imported from location class path resource [application-test.yml] is invalid in a profile specific resource [origin: class path re…...

Java线上CPU飙高问题排查全指南

一、引言 在Java应用的线上运行环境中,CPU飙高是一个常见且棘手的性能问题。当系统出现CPU飙高时,通常会导致应用响应缓慢,甚至服务不可用,严重影响用户体验和业务运行。因此,掌握一套科学有效的CPU飙高问题排查方法&…...

云原生玩法三问:构建自定义开发环境

云原生玩法三问:构建自定义开发环境 引言 临时运维一个古董项目,无文档,无环境,无交接人,俗称三无。 运行设备的环境老,本地环境版本高,ssh不过去。正好最近对 腾讯出品的云原生 cnb 感兴趣&…...

HTML前端开发:JavaScript 获取元素方法详解

作为前端开发者,高效获取 DOM 元素是必备技能。以下是 JS 中核心的获取元素方法,分为两大系列: 一、getElementBy... 系列 传统方法,直接通过 DOM 接口访问,返回动态集合(元素变化会实时更新)。…...