【MySQL】 索引

MySQL与磁盘存储

MySQL就是提供数据存储服务的,而最终存储的位置就是磁盘,但是磁盘存储速度慢,所以MySQL如何与磁盘交互,提高数据存储效率,即是MySQL和磁盘交互。

磁盘基础知识回顾

 物理结构

  • 磁道:磁盘是被划分成多个同心圆,每一个同心圆都是一个磁道
  • 扇区:每个磁道又被划分成多个扇区,扇区是磁盘中最小的读写单位,一般情况下都是512或者4096字节(目前新的扇区有其他字节数,并非固定的数字)
    • 半径方向上,距离圆心越近,扇面越小,距离圆心越远,那么扇区越大
  • 柱面:在多碟片的磁盘中,不同磁片相同半径的磁道组成柱面

文件查询和磁盘关系

本质上,找到某个文件全部内容也就是找到该磁盘的扇区,只是一个反应在软件上一个体现在硬件上。

 定位扇区

CHS寻址方法,也就是借助柱面、磁头和扇区来标识磁盘上数据的位置

  • 选择柱面:硬盘控制器,通过磁盘上的电磁轨道选择某个柱面(也就是磁盘垂直方向上相同半径下所有盘片的磁道形成的圆柱体--类似于一堆光碟的堆积)
  • 选择磁头:根据需要访问的数据所在的盘面,磁头定位到相应的磁道上
  • 选择扇区:也就是磁头在磁道上找到目标扇区,开始进行读/写操作

目前主流的方式是LBA(逻辑块寻址),简单来说就是将硬盘的扇区编号为连续的逻辑块号,乜咯逻辑块对应一个扇区,操作系统则通过逻辑快号(LBA)来访问硬盘数据,而不再依赖柱面、磁头和扇区的物理结构。

分析:系统软件不按照硬件访问内存的大小进行I/O交互的原因

结论肯定是不可以,因为如果按照硬件读取磁盘的大小进行设计系统软件,那么每一次硬件读取磁盘大小的变化,系统软件都需要重新设计一次,所以不显示。而文件系统的出现就是为了解决该问题,文件系统的基本单位是数据块,不是扇区,其基本单位是4kb。 

操作系统与磁盘的交互中,操作系统不会直接处理扇区,而是通过块来读取数据,可以说扇区是硬件的最小读写单元,而块则是操作系统用来管理数据的逻辑单位,通常块的大小是多个扇区组成的。

简单理解块是什么

块就是操作系统中用于存储数据的最小单位,一般情况是4KB(也就是8个512字节的扇区,或者一个4096的扇区)。操作系统为了简化管理这些块,将数据以块为单位进行组织,同时文件系统会使用这些块来存储文件。数据存储在硬盘上,是以块的形式进行存储的。

虽然硬盘的最小物理存储单位是扇区,但是操作系统通过文件系统将多个扇区组合成块来使用。这也就导致了块的大小对性能影响较大,如果块太小,可能需要更多的寻道操作,导致性能下降;如果块要太大的话,会导致浪费空间。

磁盘随机访问与顺序访问

随机访问

随机访问具体来说就是在不同磁盘区域之间来回读写数据的过程,这也就意味着每次 I/O操作,磁盘的读写磁头必须在不同的位置移动,以此来定位到不同扇区。但是硬盘是机械设备,磁头的物理移动会产生延迟,也就是寻道时间。寻道时间就是影响效率的根源。

因为每次I/O操作之间都是没有规律的,磁头是需要频繁移动的,所以随机访问更适合去访问那些需要频繁访问但是比较小的文件。

顺序访问

顺序访问就是磁盘读写数据的时候按照连续的地址顺序进行,一般情况磁头只需要进行较小的移动就可以找到数据。该种方式可以有效降低寻道时间,所以该种方法效率高,适合大文件读取。

随机与顺序访问的实际影响

  • 数据库:处理大规模小查询的时候,如果此时数据存放的物理位置不同,系统就会产生大量的随机访问,会影响数据库的整体性能
  • 文件系统碎片化:如果文件是分散存储在不同磁盘区域中的时候,读取该文件需要多次随机访问,这样同样会降低读写性能

固态硬盘SSD对传统磁盘的性能突破分析

固态硬盘的访问方式则是对机械硬盘的访问进行了颠覆,其本身是没有磁头等,也就不需要寻道时间也没有旋转延迟,所以SSD的随机读取速度是比机械快很多。

所以如果使用的是SSD,那么系统设计者应该更加关注I/O操作而不是存放位置所带来的性能消耗。

现代操作系统和磁盘的关系

目前的操作系统则是通过缓存和预读的技术来减少磁盘访问次数,具体来说就是将一部分常用的数据存储在内存中,从而减少每次访问都需要去磁盘中读取的需求。

操作系统也是通过文件系统来管理数据,通过将文件分配到连续的块来优化顺序访问的性能。但是文件系统还需要处理文件碎片问题,尽可能将文件存储在连续的物理位置,从而减少随机访问带来的性能损耗。

MySQL与磁盘交互基本单位

MySQL的基本I/O单位

MySQL的InnoDB存储引擎中,页面是基本的存储和管理单位,每个InnoDB页的默认大小都是16Kb,MySQL的数据操作和磁盘I/O都是以页面为单位进行的,这样可以提高I/O效率,减少频繁的磁盘寻道和数据传输而导致的性能下降问题

页大小为16kb原因

  • 使用更大的数据块,可以一次性读写更多的数据,从而减少硬盘读写的次数,也就提升了I/O效率
  • 可以提高顺序读取的效率

 MySQL通过将访问磁盘的基本单位扩大到16KB页面,有效提升了磁盘的I/O效率,特别是处理大规模的数据的时候,减少了频繁的小块读写操作。

MySQL与I/O操作的基本原理

MySQL可以通过内存缓存机制和page单位操作,从而优化磁盘I/O,进而提高数据库性能

  • page单位存储
    • MySQL上的数据文件都是以page进行存储的,一个page就是16KB
  • CURD操作与计算
    • MySQL的增删查改操作都是涉及到存储为止的查找或者数据的修改,这些操作也是依赖计算来确定数据在磁盘上的位置
  • 内存与磁盘交互
    • CPU使用数据的时候会先将数据放入到内存中,然后再进行访问,这样可以提高访问速度,数据刷新的基本单位也就是page
  • Buffer Pool
    • MySQL在服务器内存中分配的有对应缓冲区,也就是Buffer Pool,用于缓存从磁盘中读取的数据并减少磁盘的I/O次数

 深入理解索引

索引使用

创建员工信息表,然后再name字段上创建单列索引,同时在age和department上创建组合索引

插入信息

查询索引,分析索引的作用

 单个和多个page

MySQL和磁盘交互时,使用page本质目的就是为了减少I/O次数,从而提高性能

单个page

 InnoDB存储引擎中,page是数据存储和管理的基本单位,默认大小是16KB,页目录类似于一本书的目录,也就是在查询的时候不需要扫描整页内容,只需要通过页目录定位到数据的位置

一个page中的内容

  • 文件头:记录页的也谢基本信息,例如页类型、大小等
  • 页头:页的具体属性,例如记录数、页的空闲空间等
  • infimum 和 supremum记录:每个页都有两个虚拟的最小和最大记录
  • 用户记录:实际存储的数据行
  • 空闲空间:用于存储新插入的数据
  • 文件尾:效验页的完整性

多个page

如果存储的数据超过了单个page的容量时候,数据就会存储到其他page中,这样也就出现了多个page。数据库采用索引结构(例如B+树)管理和检索分布在不同的page的数据。

例如图书馆的书架,索引就像目录,page就像书架上的书本,通过目录可以快速找到所需要的书,而不需要一本一本的翻找。

单页与多页的情况

单页情况

  • 所有的数据都放在一个page内,查找速度快,只需要进行一次I/O就可以完成查找
  • 页内查找是可以通过页目录加速

多页情况

  • 数据分布在多个page中,需要通过索引定位到具体的page
  • 如果查找的话是需要进行多次磁盘I/O,但是如果通过B+树等数据结构,可以减少I/O的次数,从而提高效率、

在多页情况下,索引的作用体现的更加明显,因为可以帮助在多个page中快速定位到自己需要寻找的数据,从而实现自己调用数据的目的。

B+树和B树

数据结构可视化 (usfca.edu)

B树的特点

  • 所有的节点都存储键和值
  • 叶子节点和内部节点都可能包含数据
  • 不利于范围查询,因为数据都分布在各个节点上

B+树特点

  • 只有叶子节点存储真正的数据,内部节点仅仅存储键和指针
  • 叶子节点通过双向链表连接,便于顺序遍历和范围查询
  • 树的高度更低,查询效率更高

B+树的理解

  • 类似于一个有层级的目录,顶部是章节标题(内部节点),底部是具体的内容页(叶子节点),如果要找某个内容,只需要根据章节标题就可以快速定位到对应的内容页

InnoDB选择B+树的原因

  • 因为B+树的设计就像一本精心设计的百科全书,目录清晰查找方便,无论是想要寻找哪一页,都需要翻过相同数量的页数,确保了查找的稳定性
  • B+树的优点
    • 磁盘I/O性能优化:内部节点小,磁盘读取的时候可以加载更多的信息,减少I/O的次数
    • 顺序访问性能高:因为叶子节点是通过链表相连接的
    • 性能稳定:树的高度平衡,任何数据的查找路径都是相同的

聚簇索引和非聚簇索引

基本概念

  • 聚簇索引:数据行按照主键的顺序存储,叶子节点包含了完整的数据行
  • 非聚簇索引:索引的叶子节点存储的是键值和指向数据行的指针

聚簇索引就像一本按照章节编订的书籍,章节内容紧密连接(没有目录的书)。而非聚簇索引就像是书的索引页,指出关键词所在的章节,需要翻到对应的章节去阅读。

聚簇索引的特点

  • 每个表只有一个聚簇索引
  • 插入的新数据可能会导致数据的物理移动,影响插入性能
  • 数据物理上是按照主键顺序存储的,有利于范围查询和排序

非聚簇索引特点

  • 适用于非频繁查询的列
  • 叶子节点存储索引列和主键值,需要通过主键回表查询完整数据
  • 插入和更新对非聚簇索引的影响小

MyISAM(非聚簇索引)

该搜索引擎中,索引诶和数据是分开的

  • B+树的叶子节点中存储的不是实际行数据,而是指向数据所在位置的地址,这些指针指向的是数据文件中的物理位置,也就是访问实际数据需要借助其地址找到其数据
  • 该引擎中的主键索引和二级索引结构是没有本质区别,叶子节点都是存储指向数据的指针

创建表插入数据

查看表结构和索引 

InnoDB(聚簇索引)

该搜索引擎的主键索引就是聚簇索引,也就是说B+树的叶子节点中存储了实际的行数据。每个表的主键都会自动创建为聚簇索引,也就是说数据和主键在物理空间上是存储在一起的,如果查询的时候使用主键,就可以直接在主键的叶子节点获取完整的行数据

二级索引在叶子节点存储的是主键值而不是物理地址,因此通过二级索引查找到数据后,还是需要通过主键索引再次定位到实际数据的物理位置

创建表插入数据

表结构和索引

验证聚簇查询速度

索引的使用

不同索引类型

主键索引:每个表中只允许有一个主键索引,主键列的值是唯一的,用于唯一标识表中的每一行数据

CREATE TABLE Employee (emp_id INT PRIMARY KEY,   -- 创建主键索引name VARCHAR(50),age INT
);

唯一索引:保证索引列的值是唯一的,允许NULL值,其可以避免表中某列包含的重复值,表中是可以有多个唯一索引的,唯一索引允许多个NULL值 

CREATE UNIQUE INDEX idx_unique_email ON Users (email);  -- 创建唯一索引

 普通索引:不对列的值进行唯一性约束,主要用于加速查询,同时也不会对数据的唯一性进行强制检查

CREATE INDEX idx_name ON Employee (name);  -- 创建普通索引

全文索引:专门用于全文检索,比常规的Like查询高效,适用于大量文本内容的检索

CREATE FULLTEXT INDEX idx_fulltext ON Articles (content);  -- 创建全文索引

查询索引:通过show index 来查看某个表的索引,索引查询可以帮助理解当前表的索引结构

删除索引 

DROP INDEX idx_name ON Employee;  -- 删除普通索引

索引的使用与测试 

创建表与插入大量数据(可以通过.sql文件的方式进行插入)

CREATE TABLE Employees (emp_id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100),department_id INT,hire_date DATE,salary DECIMAL(10, 2)
) ENGINE=InnoDB;
DELIMITER $$CREATE PROCEDURE InsertEmployees()
BEGINDECLARE i INT DEFAULT 1;WHILE i <= 1000000 DOINSERT INTO Employees (first_name, last_name, email, department_id, hire_date, salary)VALUES (CONCAT('First', i), CONCAT('Last', i), CONCAT('email', i, '@company.com'), FLOOR(1 + (RAND() * 10)), '2020-01-01', FLOOR(30000 + (RAND() * 70000)));SET i = i + 1;END WHILE;
END $$DELIMITER ;CALL InsertEmployees();

无索引下搜索某个数据

此时需要扫描整个数据表去查找这个数据,逐行查出目标数据

创建普通索引并测试查询性能 

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

如若内容造成侵权/违法违规/事实不符,请联系一条长河网进行投诉反馈,一经查实,立即删除!

相关文章

ARM 栈和函数调用

阅读本文前&#xff0c;可以先阅读下述文档&#xff0c;对函数栈、栈帧等的概念会有所了解&#xff0c;会对本文章的理解大有益处 X86_64 栈和函数调用 1、调试环境 Ubuntu&#xff1a; liangjieliangjie-virtual-machine:~/Desktop$ cat /proc/version Linux version 6.5.0…

c++9月20日

1.思维导图 2.顺序表 头文件 #ifndef RECTANGLE_H #define RECTANGLE_H#include <iostream>using namespace std;using datatype int ;//类型重定义class Seqlist { private://私有权限datatype *ptr; //指向堆区申请空间的起始地址int size;//堆区空间的长度int len …

leetcode第十二题:整数转罗马数字

七个不同的符号代表罗马数字&#xff0c;其值如下&#xff1a; 符号值I1V5X10L50C100D500M1000 罗马数字是通过添加从最高到最低的小数位值的转换而形成的。将小数位值转换为罗马数字有以下规则&#xff1a; 如果该值不是以 4 或 9 开头&#xff0c;请选择可以从输入中减去的…

利用LRZ压缩与Base64编码实现高效文件上传

引言 在当今互联网时代&#xff0c;文件上传已成为众多在线服务不可或缺的一部分&#xff0c;尤其是在社交媒体平台上的照片分享和云存储服务中的文档管理等场景&#xff0c;高效且安全的文件上传机制对于保障用户体验至关重要。 为此&#xff0c;本文将介绍一种结合了LRZ压缩…

使用vite+react+ts+Ant Design开发后台管理项目(三)

前言 本文将引导开发者从零基础开始&#xff0c;运用、react、react-router、react-redux、Ant Design、less、tailwindcss、axios等前沿技术栈&#xff0c;构建一个高效、响应式的后台管理系统。通过详细的步骤和实践指导&#xff0c;文章旨在为开发者揭示如何利用这些技术工具…

VSCode环境下连接 MySQL 8.0 数据库 (C++)

前言 时隔了不知道多久&#xff0c;笔者需要在Windows环境下通过VSCode重新搭建一个简单的数据库连接的Cpp工程。由于VSCode和MySQL的版本和之前连通时发生了一些变化&#xff0c;无需用MySQL Connector&#xff0c;环境配置的细节和之前也不尽相同&#xff0c;因此笔者找了一…

简单有效关于msvcp140.dll丢失的解决方法,msvcp140.dll修复的方法原理及步骤

这篇文章将和大家分享几种msvcp140.dll丢失的解决方法&#xff0c;分析解决方法为什么能够通过这种方法进行修复成功&#xff0c;有效的将丢失的msvcp140.dll文件进行修复完成。 msvcp140.dll丢失&#xff1f;简单有效的解决途径 一、重新安装相关软件 原理 许多应用程序在安…

掌握Android开发新趋势:Jetpack与现代架构技术全解析

随着Android开发技术的不断进步&#xff0c;Jetpack和现代架构技术已成为构建高效、可维护应用的关键。本文将为您介绍一套全面的学习资料&#xff0c;包括大纲、PDF文档、源代码以及学习视频&#xff0c;帮助您深入理解Jetpack核心库、架构组件以及现代开发工具。 内容&#…

【C++】—— string模拟实现

前言&#xff1a; 学习了string的使用&#xff0c;总感觉了解不是很深厚&#xff1b;自己模拟实现string类来帮助自己理解。 这里只是实现了一部分内容&#xff08;并没有实现完整的string类&#xff09;。 先来实现string类里面的成员变量&#xff1a; #include<iostream…

草莓团队创造了o1 - Building OpenAI o1 (Extended Cut) 观后笔记

美妙的事物往往需要世界去创造&#xff0c;商业希望大模型越来越快给出回答。或许花费几个月几年的时间持续思考&#xff0c;大模型能够解决更复杂的问题&#xff0c;而不只是回答42 刚发现凌晨OpenAI发布了一个22多分钟的采访&#xff0c;将构建出O1的整个团队拉到一个小屋子&…

让Tkinter更美观:教你同步Tkinter窗口与弹窗图标(Tkinter同步主窗口与Messagebox的图标)

文章目录 📖 介绍 📖🏡 演示环境 🏡📒 文章内容 📒📝 步骤1:主窗口图标📝 步骤2:messagebox 图标📝 示例代码📝 实现原理与代码解释⚓️ 相关链接 ⚓️📖 介绍 📖 你有没有注意到,在开发软件图形界面时,会需要弹出一些提示框,而这些提示框的图标总…

695. 岛屿的最大面积

思路&#xff1a; 只有当前是陆地&#xff0c;才会构成岛屿 当前是陆地&#xff0c;进入回溯 往当前的上、下、左、右位置分别找陆地位置&#xff0c;为陆地 1>标记为2:代表已经遍历过的陆地 2>记录当前方向的陆地总数 以当前陆地组成的岛屿面积当前陆地面积向上的…

11----mtk芯片专用解锁工具 解除FRP 很小的工具 去除屏幕锁 免授权等等 工具预览与步骤解析

机型的FRP锁是谷歌账号锁。工具是mtk芯片使用 。可以去除当前机型的FRP和米账号重置。操作非常简单。但前提是联机驱动要装好。任何的工具联机驱动是关键。 工具功能选项 ★★★★★工具开发者说明功能与选项操作与资源下载 ★★★★★具体工具操作使用指南 工具联机界面与…

Type-C协议(CC检测原理)-CC1和CC2接电阻-数字和模拟耳机兼容

Type-C协议&#xff08;CC检测原理&#xff09;-CC1和CC2接电阻-数字和模拟耳机兼容 - Sean_hn - 博客园 (cnblogs.com)

4.变量与函数

作业系统链接 变量是Python中用于存储数据的命名标签&#xff0c;通过赋值&#xff0c;新值可以覆盖旧值&#xff0c;且数据类型不必相同。变量命名规则包括必须以字母或下划线开头&#xff0c;由字母、数字和下划线组成&#xff0c;大小写敏感&#xff0c;并避免使用保留字。函…

精选写作技巧!分享4款ai写毕业论文可以写出公式表格的软件

在撰写毕业论文时&#xff0c;AI写作工具可以极大地提高效率和质量。以下是四款值得推荐的AI软件&#xff0c;它们不仅能够帮助生成高质量的论文初稿&#xff0c;还能处理公式和表格等复杂内容。 传送门&#xff1a;https://www.aipaperpass.com?piclLGw 千笔-AIPassPaper是一…

Rx Strategist:智能体实现处方验证的方方面面,如适应症、剂量、药物相互作用

Rx Strategist&#xff1a;智能体实现处方验证的方方面面&#xff0c;如适应症、剂量、药物相互作用 秒懂大纲提出背景&#xff1a;拆解解法分析全流程分析创意 秒懂大纲 ├── 处方验证系统【主题】 │ ├── 背景和问题【问题描述】 │ │ ├── 现代药物复杂性对严…

2024年华为杯研究生数学建模竞赛研赛C题超详细解题思路+数据预处理代码分享

研赛题目由当年负责赛区【23年为东南大学&#xff0c;24年为山东大学】的相关老师每年独立命题&#xff0c;因此每年的题目在涉及的模型和知识点上都有很大差异。数模竞赛是百分比获奖&#xff0c;选择人数最多的也就意味着题目简单&#xff0c;会有很多新手、小白选择。我们只…

python有main函数吗

python和C/Java不一样&#xff0c;没有主函数一说&#xff0c;也就是说python语句执行不是从所谓的主函数main开始的。 当运行单个python文件时&#xff0c;如运行a.py&#xff0c;这个时候a的一个属性__name__是__main__。 当调用某个python文件时&#xff0c;如b.py调用a.p…

华为HarmonyOS地图服务 13 - 如何实现地图数据聚合效果?

场景介绍 本章节将向您介绍如何根据地图数据实现聚合效果。 您可以通过比例尺缩放自适应聚合效果,聚合图标可点击。聚合支持功能: 支持按距离聚合ClusterItem。支持绘制聚合Overlay的默认图标。支持绘制聚合Overlay的自定义图标。支持监听聚合Overlay的点击事件。支持添加单…