【MySQL数据库】索引与事务

  🔥个人主页: 中草药

🔥专栏:【MySQL】探秘:数据库世界的瑞士军刀


目录

🗳️一.索引

📮1.工作原理

📬2.类型

📭3.作用 

📪4.优缺点

📫5.使用

📦6.索引的设计与管理

📤二.事务

📧1.事务的ACID特性*

📨2.使用

📩3.事务的隔离性

3.1. 脏读(Dirty Read)

3.2. 不可重复读(Non-Repeatable Read)

3.3. 幻读(Phantom Read)

3.4.隔离级别的解决

3.5.使用事务的场景

✉️三.总结与反思


🗳️一.索引

        索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引并指定索引的类型,各类索引有各自的数据结构实现。主要目的是为了加快数据检索的速度。索引使得数据库管理系统(DBMS)能够迅速地定位到表中特定的行,从而提升查询效率。下面是关于MySQL索引的一些关键点和详细说明:

📮1.工作原理

  1. 数据结构:最常见的索引类型是B-Tree索引(B树)。在MySQL中,索引被实现为一种平衡的树形结构(通常是B+树),这种结构允许快速查找、插入和删除操作。索引中存储了表中一列或多列的值(索引键)和这些值对应的行在表中的物理位置(通常是行ID或指针)。

  2. 查询过程:当执行一个查询时,数据库首先查看索引,而不是直接遍历整张表。通过索引,数据库可以迅速定位到包含目标值的行,然后再通过行ID或指针访问实际的行数据。

📬2.类型

  • B-Tree索引:是最通用的索引类型,适用于全值匹配、范围查询、排序和分组等操作。
  • Hash索引:适用于等值比较查询,通过哈希函数将索引列的值转换成哈希码,然后直接定位到相应行。不支持范围查询。
  • 全文索引:专为大文本字段设计,用于全文搜索,可以高效地处理LIKE '%keyword%'这类模糊查询。
  • R-Tree索引:用于空间数据类型的索引,如GIS地理坐标数据。

📭3.作用 

  • 数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系。
  • 索引所起的作用类似书籍目录,可用于快速定位、检索数据。
  • 索引对于提高数据库的性能有很大的帮助。

📪4.优缺点

优点

  • 提高查询速度:通过索引直接定位数据,减少全表扫描。
  • 加速排序和分组操作:如果排序或分组的列上有索引,数据库可以直接使用索引来避免额外的排序步骤。
  • 覆盖索引:如果查询所需的全部数据都在索引中(即索引包含了查询的所有列,无需回表查询),这将进一步提升查询性能。

缺点

  • 占用存储空间:每个索引都需要额外的磁盘空间来存储索引数据。
  • 影响写操作性能插入、删除和更新操作需要维护索引结构,可能会减慢这些操作的速度。
  • 选择性问题:如果索引的选择性不高(即索引列的值重复度高),索引的效果会大打折扣。

使用场景

要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:

  • 数据量较大,且经常对这些列进行条件查询。
  • 该数据库表的插入操作,及对这些列的修改操作频率较低。
  • 索引会占用额外的磁盘空间。
满足以上条件时,考虑对表中的这些字段创建索引,以提高查询效率。
反之,如果非条件查询列,或经常做插入、修改操作,或磁盘空间不足时,不考虑创建索引。

📫5.使用

创建主键约束(PRIMARY KEY)、唯一约束(UNIQUE)、外键约束(FOREIGN KEY)时,会自动创建对应列的索引。
查看索引
show index from 表名;案例:查看学生表已有的索引
show index from student;
创建索引
create index 索引名 on 表名(字段名);案例:创建班级表中,name字段的索引
create index idx_classes_name on classes(name);

删除索引

drop index 索引名 on 表名;案例:删除班级表中name字段的索引
drop index idx_classes_name on classes;

📦6.索引的设计与管理

  • 选择索引列:通常选择出现在WHERE子句、JOIN条件、ORDER BY和GROUP BY中的列作为索引。
  • 复合索引:当一个查询涉及多个列时,可以创建包含多个列的复合索引,但需注意索引列的排列顺序。
  • 监控和优化:定期分析索引的使用情况,移除无效或很少使用的索引,调整或新增索引来适应查询模式的变化。

综上所述,索引是MySQL性能优化的重要手段,但需要根据实际情况合理设计和管理,以达到最佳的性能与存储空间的平衡。

📤二.事务

        MySQL中的事务(Transaction)是数据库操作的基本单位,它能确保一系列操作要么全部成功,要么全部失败,以此来维护数据的一致性和完整性。事务是关系型数据库中的一个核心特性,特别是在处理金融、银行、电子商务等领域中对数据准确性要求极高的应用场景时尤为重要。以下是MySQL中事务的几个关键概念和操作:

📧1.事务的ACID特性*

  1. 原子性(Atomicity):事务被视为一个不可分割的最小工作单元,事务中的所有操作要么全部执行,要么都不执行。
  2. 一致性(Consistency):事务执行前后,数据库的状态都必须保持一致,即符合所有的预定义规则。
  3. 隔离性(Isolation):多个事务并发执行时,彼此之间互不影响,仿佛是在串行执行一样。MySQL提供了多种事务隔离级别来控制这一特性。下文做详细概述:
  4. 持久性(Durability):一旦事务提交,其结果就会永久保存在数据库中,即使系统发生故障也不会丢失。

📨2.使用

在MySQL中,可以通过以下命令来管理事务:

  • 开始事务:在默认的自动提交模式下,每条SQL语句都会作为一个单独的事务执行。要手动控制事务,首先需要关闭自动提交模式,使用SET autocommit = OFF;命令。
  • 执行事务中的操作:在此之后执行的SQL语句属于同一事务。
  • 提交事务:使用COMMIT;命令来确认并保存事务中的更改。
  • 回滚事务:如果事务中的某些操作失败,或者决定放弃已经做出的更改,可以使用ROLLBACK;命令撤销整个事务中的所有操作。
start transaction;-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

📩3.事务的隔离性

        事务的隔离性问题主要体现在三种现象上:脏读(Dirty Read)、不可重复读(Non-Repeatable Read)和幻读(Phantom Read)。这些现象发生在并发事务处理时,不同的事务隔离级别会不同程度地解决这些问题。以下是这三种现象的具体解释:

3.1. 脏读(Dirty Read)

  • 定义:当一个事务读取了另一个事务尚未提交的数据时,如果后者进行了回滚,那么前者读取到的就是“脏数据”,即从未真正存在于数据库中的数据。
  • 示例:事务T1更新了一行记录,但还未提交。此时,事务T2读取了T1更新后的数据。如果T1随后因某种原因被回滚,那么T2之前读取的数据就是无效的,即发生了脏读。

3.2. 不可重复读(Non-Repeatable Read)

  • 定义:在一个事务内,两次读取同一行数据,由于另一个并发事务在这两次读取之间对该行数据进行了修改并提交,导致两次读取的结果不一致。
  • 示例:事务T1读取某一行数据,事务T2随后更新了该行数据并提交。当T1再次读取该行数据时,发现数据内容与第一次读取时不同,尽管T1本身没有对数据进行任何修改,这就是不可重复读。

3.3. 幻读(Phantom Read)

  • 定义:在一个事务内,两次执行相同的查询,第二次查询结果中出现了第一次查询结果中没有的新行,这是因为另一个事务在这两次查询之间插入了新数据。
  • 示例:事务T1执行一个查询,返回所有满足某个条件的行。在同一时间,事务T2插入了一行新数据,恰好也满足T1的查询条件,并提交了事务。当T1再次执行同样的查询时,发现多出了一行记录,就像幻影一样出现,这就是幻读。

3.4.隔离级别的解决

  • 读未提交(READ UNCOMMITTED):允许所有三种问题发生。
  • 读已提交(READ COMMITTED):解决了脏读问题,但不可重复读和幻读仍可能发生。
  • 可重复读(REPEATABLE READ):解决了脏读和不可重复读的问题,但在标准的SQL隔离级别定义中,理论上幻读仍可能发生。然而,在MySQL的InnoDB引擎中,通过多版本并发控制(MVCC)机制,实际上很大程度上避免了幻读的发生,他同时也是MySQL的默认级别。
  • 串行化(SERIALIZABLE):通过强制事务串行执行,解决了所有三种问题,但这是以牺牲并发性为代价的。

选择合适的事务隔离级别需要权衡数据一致性和系统性能之间的关系。在大多数应用中,可重复读(REPEATABLE READ)是一个平衡的选择。

3.5.使用事务的场景

  • 当你需要确保一系列数据库操作要么全部成功,要么全部失败时,应该使用事务。
  • 在转账、库存管理、订单处理等需要精确数据一致性的场景中,事务是必不可少的。

理解并正确使用事务,对于开发高性能、高可靠性的数据库应用程序至关重要。

✉️三.总结与反思

任凭怎样脆弱的人,只要把全部的精力倾注在唯一的目的上,必能使之有所成就。——西塞罗

        在深入学习MySQL的索引与事务后,我深刻体会到这两个概念不仅是数据库技术的核心组成部分,也是构建高性能、高可用数据库应用的关键。以下是我对这两个主题的学习总结与反思。

索引

重要性:索引是数据库优化的利器,它通过减少数据检索的时间复杂度,极大地提升了查询效率。理解索引的工作原理,特别是B-Tree结构,对于合理设计索引至关重要。

索引设计的艺术:选择合适的列建立索引,考虑列的选择性、查询频率以及是否涉及范围查询等因素。复合索引的顺序安排也是优化查询的关键。此外,认识到索引并非越多越好,过多的索引会占用额外的存储空间,并可能降低写操作的性能。

监控与调优:通过EXPLAIN分析查询计划,了解索引的实际使用情况,定期评估并调整索引策略。学会利用MySQL的性能监控工具,如SHOW INDEX、INFORMATION_SCHEMA库等,来辅助决策。

反思:在实践中,我意识到初期对索引的过度依赖有时会导致忽视了对查询逻辑本身的优化。未来,我应更加注重平衡索引的利弊,结合查询优化和合理的数据库设计来提升整体性能。

事务

事务的基石:ACID:理解事务的ACID属性(原子性、一致性、隔离性、持久性)是掌握事务管理的基础。每个属性都有其在保证数据完整性和一致性方面的关键作用。

隔离级别与并发控制:深入学习了四种事务隔离级别(读未提交、读已提交、可重复读、串行化)及其权衡,认识到不同的业务场景需要不同的隔离级别来平衡并发性和数据一致性。特别是InnoDB引擎的MVCC机制如何在可重复读级别上减少了幻读现象。

事务的实战应用:实践操作中,明确事务边界,适时使用BEGIN、COMMIT、ROLLBACK命令来控制事务流程,是防止数据不一致和错误累积的有效手段。同时,对死锁的理解和预防也是在并发环境下不可或缺的知识点。

反思:事务管理在实际应用中往往比理论学习更为复杂,尤其是在高并发场景下,正确选择隔离级别和处理事务冲突成为一大挑战。我需要更多地通过实战案例来深化理解,尤其是如何在特定场景下灵活运用SAVEPOINT、锁机制等高级特性,以提高系统的稳定性和响应速度。

        总之,学习MySQL的索引与事务是一个不断实践、反思和优化的过程。通过理论学习结合实际应用,我不仅加深了对这些核心概念的理解,也意识到了在实际工作中持续优化和学习的重要性。未来,我将继续探索更深层次的数据库知识,不断提升数据库设计和优化的能力


🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀

以上,就是本期的全部内容啦,若有错误疏忽希望各位大佬及时指出💐

  制作不易,希望能对各位提供微小的帮助,可否留下你免费的赞呢🌸

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

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

相关文章

Git远程操作

下面演示在gitee上创建远程仓库 创建远程仓库 上面的.gitignore是为了在上传时过滤掉不重要的文件,分支模型选单分支模型就够用了,或者选生产/开发模型。 当然,如果创建仓库的时候没有添加.gitignore文件,可以在本地仓库创建.gi…

Nvidia Jetson/Orin +FPGA+AI大算力边缘计算盒子:美团小袋自动配送车

大型电商公司美团已选用NVIDIA Jetson AGX Xavier 平台,作为无人配送机器人核心AI算力。 美团点评是全球大型的按需食品配送公司,结合了Uber Eats、Yelp和Groupon的商业模式,与超过40万家本地企业开展合作。他们推出了小袋自动配送车&#…

【OpenHarmony】TypeScript 语法 ③ ( 条件语句 | if else 语句 | switch case 语句 )

文章目录 一、条件语句1、if else 语句2、switch case 语句 参考文档 : <HarmonyOS第一课>ArkTS开发语言介绍 一、条件语句 1、if else 语句 TypeScript 中的 if 语句 / if else 语句 用法 , 与 JavaScript 语言中的 if 语句 / if else 语句 语法 基本相同 ; if else 语…

QT 信号和槽教程,窗体和控件对象之间的沟通一般都使用信号和槽

Qt的信号和槽&#xff08;Signals and Slots&#xff09;机制是一种强大的对象间通信方式&#xff0c;它允许对象在完全解耦的情况下相互通信。以下是关于Qt信号和槽的简明教程&#xff1a; 基本概念 信号&#xff08;Signal&#xff09;&#xff1a;信号是由Qt对象发出的通知…

Docker 快速更改容器的重启策略(Restart Policies)以及重启策略详解

目录 1. 使用 docker update 命令2. 在启动容器时指定重启策略3. 在 Docker Compose 文件中指定重启策略4. 总结 官方文档&#xff1a;Start containers automatically 1. 使用 docker update 命令 Docker 提供了 docker update 命令&#xff0c;可以在容器运行时更改其重启策…

Elasticsearch:基于多个 kNN 字段对文档进行评分

作者&#xff1a;来自 Elastic Madhusudhan Konda 通过具有多个 kNN 字段的最接近的文档对文档进行评分 Elasticsearch 不仅仅是一个词法&#xff08;文本&#xff09;搜索引擎。 Elasticsearch 是多功能搜索引擎&#xff0c;除了传统的文本匹配之外&#xff0c;还支持 k 最近…

华为设备配置静态路由和默认路由

华为设备配置静态路由和默认路由 理论部分知识&#xff1a; 路由分为两个大类&#xff1a;静态路由-----动态路由 静态路由&#xff1a;手工指定&#xff0c;适用于小规模的网络应用场景&#xff0c;如果网络规模变大&#xff0c;这样的方式非常不适合而且容易出错。 语法&…

【JavaEE进阶】——Mybatis操作数据库(使用注解和XML方式)

目录 &#x1f6a9;三层架构 &#x1f388;JDBC操作回顾 &#x1f6a9;什么是MyBatis &#x1f6a9;MyBatis⼊⻔ &#x1f388;准备工作 &#x1f4dd;创建⼯程 &#x1f4dd;数据准备 &#x1f388;配置数据库连接字符串 &#x1f388;写持久层代码 &#x1f388;单…

STM32作业设计

目录 STM32作业设计 STM32作业实现(一)串口通信 STM32作业实现(二)串口控制led STM32作业实现(三)串口控制有源蜂鸣器 STM32作业实现(四)光敏传感器 STM32作业实现(五)温湿度传感器dht11 STM32作业实现(六)闪存保存数据 STM32作业实现(七)OLED显示数据 STM32作业实现(八)触摸按…

PyQt5创建与MySQL数据库集成的应用程序

最近&#xff0c;对之前的mysql管理系统进行了更新升级&#xff0c;制作了一版关于车牌的管理系统&#xff01; &#xff08;1&#xff09;实现了对车牌和用户基本信息的增删改查的功能 &#xff01; &#xff08;2&#xff09;加入了对数据库的刷新和状态显示功能 &#xff…

没有知网资源如何快速下载知网论文

今天有位同学求助一篇知网论文&#xff0c;“球磨-点击化学反应&#xff1a;无溶剂绿色反应方式”&#xff0c;其实下载知网论文是一件非常简单的事情&#xff0c;下面小编就把如何在家轻松查找下载知网论文的方法给大家演示一遍。 一、首先你需要获取知网使用权限&#xff0c…

css特殊效果和页面布局

特殊效果 圆角边框&#xff1a;div{border-radius: 20px 10px 50px 30px;} 四个属性值按顺时针排列&#xff0c;左上的1/4圆半径为20px&#xff0c;右上10&#xff0c;右下50&#xff0c;左下30。 div{border-radius: 20px;} 四角都为20px。 div{border-radius: 20px 10…

Maven配置

Maven – Download Apache Maven https://maven.apache.org/install.html 得下载有 bin的

MongoDB~俩大特点管道聚合和数据压缩(snappy)

场景 在MySQL中&#xff0c;通常会涉及多个表的一些操作&#xff0c;MongoDB也类似&#xff0c;有时需要将多个文档甚至是多个集合汇总到一起计算分析&#xff08;比如求和、取最大值&#xff09;并返回计算后的结果&#xff0c;这个过程被称为 聚合操作 。 根据官方文档介绍&…

Facebook开户 | Facebook二不限户

Facebook二不限户的正确使用方法 Facebook 二不限是指 Facebook 国内二不限户&#xff0c;是通过代理开出来的一种特殊账户&#xff0c;️需要广告主准备主页。 其特点是&#xff1a;限主页、不限域名、额度没解限&#xff0c;解限后则不限额度。 相比于三不限户&#xff0c;…

Notepad++不显示CRLF的方法

View -> Show Symbol -> 去掉勾选 Show All Characters

threejs 场景构建技巧与优化策略探讨

threejs 场景构建技巧与优化策略探讨 一、引言 在使用 Three.js 进行三维场景开发时&#xff0c;艾斯视觉作为行业ui设计与前端开发服务商很高兴能在这里与你共同探讨&#xff1a;如何高效地构建场景并实现良好的性能表现是至关重要的。本文将深入探讨一些场景构建的技巧以及…

LabVIEW在高校电力电子实验中的应用

概述&#xff1a;本文介绍了如何利用LabVIEW优化高校电力电子实验&#xff0c;通过图形化编程实现参数调节、实时数据监控与存储&#xff0c;并与Simulink联动&#xff0c;提高实验效率和数据处理能力。 需求背景高校实验室在进行电机拖动和电力电子实验时&#xff0c;通常使用…

Nodejs-- 网络编程

网络编程 构建tcp服务 TCP tcp全名为传输控制协议。再osi模型中属于传输层协议。 tcp是面向连接的协议&#xff0c;在传输之前需要形成三次握手形成会话 只有会话形成了&#xff0c;服务端和客户端才能想发送数据&#xff0c;在创建会话的过程中&#xff0c;服务端和客户…

关于如何通过APlayer+MetingJS为自己的wordpress博客网页添加网易音乐播放器(无需插件)

本文转自博主的个人博客&#xff1a;https://blog.zhumengmeng.work,欢迎大家前往查看。 原文链接&#xff1a;点我访问 序言&#xff1a;最近在网上冲浪&#xff0c;发现大家的博客大部分都有一个音乐播放器能够播放音乐&#xff0c;随机我也开始寻找解决方法。可是找来找去我…