MYSQL(索引、事务)

文章目录

  • 一、索引
  • 二、事务

一、索引

数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系

1. 概述

  • 概念:相当于是一本书的目录,是以‘列’为维度进行建立的
  • 使用场景:如果我们要查询一个表中的某个数据,可以通过遍历来实现,但那样,速度太慢,可以通过给这个表建立索引,来提高查找的速度
  • 使用讲解
    • 按照id这列,建立索引,效果是在数据库上额外搞了一个空间,用来维护一些和id相关的信息,即id为1在哪个位置,id为2在哪个位置,参考目录。后续按照id查找的时候,就不必直接遍历了,而是从索引中进行查询,根据索引就能初步锁定数据所在的位置
    • 注意,这个索引中只能按照id来查询,按照name来查询,不能提高效率,需要给name也建立一个索引才行
  • 作用
    • 优点:提高查询效率
    • 缺点:
      (1)消耗额外的磁盘空间
      (2)可能会拖慢增删改的速度,因为新增不光要往表里写数据,还要增加索引。如果是删和改,如果删或改的条件正好是和索引匹配的,速度能快点,但是如果涉及到了索引列的删除/修改,就需要同时维护索引,就会很慢

2. 使用

创建主键约束(PRIMARY KEY)、唯一约束(UNIQUE)、外键约束(FOREIGN KEY)时,会自动创建对应列的索引。因为主键不允许重复,进行插入或修改就需要先查询看是否重复

  • 查看索引:show index from 表名;

  • 创建索引:create index 索引名 on 表名(字段名);

    • 对于非主键、非唯一约束、非外键的字段,可以创建普通索引
    • 注意这个操作,可能会很危险
      • 如果表是空的,或者表里包含的数据本身就不多,那可以创建。但是如果表非空,并且里面包含了非常多的数据,创建索引,就会引起非常大规模的硬盘IO操作,进一步就会导致数据库被卡死。这就相当于要整理全部的数据。
      • 所以需要提前考虑好,在设计表的阶段,哪些列要索引
  • 删除索引:drop index 索引名 on 表名;

    • 删除索引,只能针对手动创建的索引,自动生成的索引,是不能被删除的
    • 这个操作也十分危险,原因参考创建
  • 如果就是要给一个有着非常多数据的表删除/创建索引

    • 准备一个新的mysql服务器,把表和索引都创建好,然后把数据都导入过来,再把要替换的mysql服务器关闭,最后把新的mysql服务器替换上去就行了
    • (上述操作的原因)数据库服务器往往也不是单台服务器,为了整个系统的可靠性,通常会搞多个mysql服务器节点,这些节点的数据都是一样的,能够提供相同的服务,彼此之间相互独立,其中一个挂了,也不影响整体
  • 关于if、while等语法

    • sql中不建议使用一些其他支持的语法,如条件、循环等,因为该语法较老,且sql写得复杂,不方便调试和优化,实际开发中,去表示这样的更复杂的逻辑往往是使用其他语言+增删改查的方式

3. 底层的数据结构

其实在物理层面上,不需要‘表格’这样的数据结构,直接使用B+树来存储这个表的数据,“表格”只是让用户看起来这像个表格而已

1.概念

mysql的索引的数据结构是什么样的,取决于mysql使用哪个存储引擎

  • 位置

    • 数据库的数据结构是在硬盘上的
    • 内存上的数据结构,对于访问操作来说,是不敏感的(找数据的过程花费时间多,真正访问的操作时间则不多)
    • 硬盘上的数据结构,对于访问操作来说,是比较敏感的,读写一次硬盘,开销远远大于内存
    • 读写一次硬盘,相当于读写1w次内存(大约的约束,内存和内存,硬盘和硬盘之间彼此差异很大)
  • 存储引擎

    • mysql这个程序中,包含很多模块,有负责解析sql的,有负责网络通信的,有负责存储数据的……
    • 负责存储数据的就是存储引擎,本质上就是代码中的一个模块(包含了若干个代码文件、一群具体代码……)
  • mysql支持多种存储方案,Innodb是当下最主流的一种方式

2.分析

索引主要是为了快速查找,这方面红黑树和哈希表效率高。

  • 哈希表:不适合数据库的查询场景(哈希表是通过hash函数来得到一个确定的下标),而且只能做精确查询,没法做模糊查询和范围查询
  • 红黑树:不适合数据库的查询场景,元素有序时,可以处理范围查询,但是在元素个数比较多的时候,红黑树的高度会高,对应的比较次数会多,14个数据就有4层了,而每次比较都在内存上,都要进行硬盘IO操作

B+树

为了解决上述问题,出现了为数据库量身定做的数据结构 ------ B+树

(1)B树
在了解B+树之前,需要先了解下B树(有时写作B-,'-'是连接符,不是减号)

B树本质上,是一个N叉搜索树,一个节点上,可以保存多个key,N个key就能延伸出 N+1 个分叉来,N个key就划分出 N+1 个区间

在这里插入图片描述

  1. 此时每个节点上,都可以保存多个元素了,对于数据库来说,每个节点都需要把数据从硬盘上读取出来,才能进行比较,一个节点有多个key,和一个节点上有一个key,硬盘IO的开销是差不多的,就像下楼倒一个垃圾和倒四个垃圾时间上没太多区别,所以B树的高度是远远小于二叉搜索树的,所以进行查询的时候,硬盘IO的次数会减少
  2. B树查询元素的流程:
    (1)从根节点出发判定要查找的元素是否在根节点上存在
    (2)如果不存在,看这个元素在哪个区间,然后根据这个区间的路线,往下一个节点找,最终找到叶子结点,如果到叶子结点还不存在,那就是真的不存在
  3. 对于B树来说,在进行插入元素和删除元素的时候,会涉及到拆分和合并的操作。
    (1)一个节点,可以存多个key,但是不能无限地存,当存储的key数量达到一定程度的时候,就需要把这个节点给拆分,把这个节点中的一部分以树的子节点的方式重新组织
    (2)这样可以保持当前这个节点中key的数量始终不会太多,能延伸出新的叶子结点
    在这里插入图片描述
    (2)B+树

在这里插入图片描述

【1】B+树也是N叉搜索树,但是N个key分出了N个区间,其中节点上最后一个key就是最大值(也可以取最小值)

【2】父节点的key会在子节点中重复值出现(以最大值的身份),虽然有很多重复元素,看起来浪费了空间,实际上能够达成 **叶子结点这一层,包含了整个数据的全集 **的效果

【3】把叶子结点按照链表这样首尾相连,就可以通过叶子结点之间的连接,快速找到“上一个”,“下一个”元素,这也方便进行范围查询

优势

  • 擅长范围查询
  • 所有的查询操作,最终都会落在叶子节点上,比较次数是均衡的,查询时间是稳定的(有时 ‘稳’ 比 ‘快’ 更重要)
  • 由于叶子节点上是完整的数据全集,因此表的每一行数据的其他列,都可以保存到叶子节点上,而非叶子节点,只存储构建索引的key即可。此时,非叶子节点的存储空间,消耗是非常小的,我们甚至可以在内存中缓存一份。这样,在进行数据查询的时候,就可以通过内存来直接进行比较,从而更快速地找到叶子节点上的记录,也进一步减少硬盘IO的次数

二、事务

  • 使用场景:为了完成某个工作,需要多组sql操作。如A转账给B,那么需要先给A的账户余额减钱,再给B账户加钱
  • 概念:事务的本质,就是为了把多个操作,打包成一个操作来完成(让这多个操作,要么全都能执行成功,要么就一个都不执行,不然就像给A扣钱了,但是没给B加钱)--------- 事务的原子性(这些操作是一个整体,不能再分割了)
    • 这个“一个都不执行”不是真的没执行,执行成不成功得执行了才知道,真正执行时是不知道哪一步操作会失败的,如果执行到中间出错了,就需要自动地把前面已经成功执行的操作还原回最初没有执行的模样
    • 所以本质上,这个“一个都不执行”不是真的没执行,而是看起来就像没执行一样
    • 还原回最初是【回滚】,回滚要把事务中执行的每个操作,都记录下来,如果需要回滚,就直接按照之前操作的“逆操作”来执行就行了
      • 操作的记录是通过特定的日志,来记录的。日志的数据是始终在硬盘上存在的,即使是数据库服务器重启,也能在重启后根据之前没有回滚完的情况继续进行处理
  • 使用
    • 开启事务:start transaction;
    • 执行多条SQL语句
    • 手动触发回滚或提交事务:rollback/commit
      • rollback即是全部失败,commit即是全部成功
      • 一个事务务必要以这两个操作结尾,如果没有这两个操作,接下来的各种sql操作都会被认为是事务的一部分
  • 事务的基本特性
    • 原子性:保证多个操作被打包成一个整体,要么能够全部执行正确,要么就一个都不执行
    • 一致性:事务执行之前,和事物执行之后,数据能对上。这依靠约束和回滚机制来实现
    • 持久性:事务这里执行的各种操作,一旦事务执行成功了,这里的所有操作产生的修改都会写到硬盘中,即持久生效的
    • 隔离性:并发执行事务的时候,隔离性会在执行效率和数据可靠之间做出权衡,“隔离”描述的是同时执行的事务之间相互的影响,隔离性越高,并发性就越低,数据越可靠,性能就越低
      • 并发意思是“同时执行”,数据库是一个客户端服务器结构的程序,既然是服务器,服务器就可以同一时刻给多个客户端提供服务(多个客户端都能给服务器提交事务),如果提交的这两个事务,是修改不同的数据库/表,相互独立那没什么影响。但是如果修改的是一个库/表,顺序的先后会影响最终的执行结果,就会出现问题(脏读、不可重复读、幻读)

1. 脏读

问题描述:读到的数据是一个临时的数据,不是最终的数据,“脏”表示临时的,并非是最终准确结果

解决方法:给写操作加锁,一个事务A写的时候,其他事务B不能读了,直到A事务写完数据,提交事务,其他的事务B才能来读取数据。

效果:引入了写加锁,降低了两个事务之间的并发性,提高了隔离性,降低了效率,使数据更加准确了

2. 不可重复读
问题描述:一个事务中,两次读到的数据不一致(在读的过程中,另一个事务修改了数据)

解决方法:给读操作加锁,别人读的时候,我不能写(除非是写完提交后,别人才能读),此时,别人读的时候,可以再开启一个事务来写,第二个事务提交之前,其他读事务读到的都是旧版本数据,第二个事务提交之后,别人再读读到的就是新版数据了

效果:并发程度又进一步降低了,隔离性提高,执行效率降低,数据更加可靠

3. 幻读
问题描述:一个事务在多次读的过程中,虽然读到的数据的值是一样的,但是结果集是不同的(多出或少出一些记录),可以视为是不可重复读的特殊情况

解决方法:串行化,彻底放弃并发执行事务,所有的事务都是一个挨着一个地串行执行(执行完一个事务,再执行下一个事务)

效果:并发性最低,隔离性最高,效率最低,数据最可靠

4. mysql的四种事务的隔离级别
在这里插入图片描述

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

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

相关文章

汽车自适应巡航系统车距控制策略研究

1 引言 自适应巡航控制( Adaptive Cruise Control,ACC) 是汽车驾驶辅助系统的重要组成部分,其作用是根据车距传感器探测到本车( ACC 车辆) 与主目标车辆( 前车) 之间的相对位置和相对速度信息,自动调节ACC 车辆的节气门开度或部分制动力矩( 即…

RT_Thread内核机制学习(一)

ARM架构及汇编 ARM芯片属于精简指令集计算机(RISC:Reduced Instruction Set Computer),它所使用的指令比较简单,有如下特点: 对内存只有读、写指令。对于数据的运算实在CPU内部实现。使用RISC指令的CPU复杂…

CSC2121A

半桥架构的栅极驱动电路CSC2121A CSC2121系列是一款高性价比的半桥架构的栅极驱动专用电路,用于大功率MOS管、IGBT管栅极驱动。IC内部集成了逻辑信号处理电路、死区时间控制电路、欠压保护电路、电平位移电路、脉冲滤波电路及输出驱动电路,专用于无刷电…

MybatisPlus(2)

前言🍭 ❤️❤️❤️SSM专栏更新中,各位大佬觉得写得不错,支持一下,感谢了!❤️❤️❤️ Spring Spring MVC MyBatis_冷兮雪的博客-CSDN博客 上篇我们简单介绍了MybatisPlus的方便之处,这篇来深入了解Myb…

LNMT架构

所谓的LNMT架构 指的就是Linux操作系统上部署Nginx web服务器、MySQL数据库服务器、Tomcat中间件服务器 L linux N nginx M mysql T tomcat 单机部署 1,安装 apache-tomcat 2,移动目录 3,复制第二个tomcat 4,…

大型商城系统功能逻辑架构_各大系统关系设计_OctShop

一个商城系统应该具备什么样的功能才算一个合格的网上商城呢,才能满意用户的下单支付,退款退货,售后等需求呢! 商城一般分为三种角色:买家,商家,平台,这三种角色都有各自的功能特点。…

springMVC之拦截器

文章目录 前言一、拦截器的配置二、拦截器的三个抽象方法三、多个拦截器的执行顺序总结 前言 拦截器 一、拦截器的配置 SpringMVC中的拦截器用于拦截控制器方法的执行 SpringMVC中的拦截器需要实现HandlerInterceptor SpringMVC的拦截器必须在SpringMVC的配置文件中进行配置&…

Android之自定义时间选择弹框

文章目录 前言一、效果图二、实现步骤1.自定义Dialog2.xml布局3.背景白色转角drawable4.取消按钮背景drawable5.确定按钮背景drawable6.NumberPicker样式和弹框样式7.弹框动画8.Activity使用 总结 前言 随着产品人员不断变态下,总是会要求我们的界面高大上&#xf…

Java 多线程系列Ⅱ(线程安全)

线程安全 一、线程不安全线程不安全的原因: 二、线程不安全案例与解决方案1、修改共享资源synchronized 使用synchronized 特性 2、内存可见性Java内存模型(JMM)内存可见性问题 3、指令重排列4、synchronized 和 volatile5、拓展知识&#xf…

计算机毕设之基于Python+django+MySQL可视化的学习系统的设计与实现

系统阐述的是使用可视化的学习系统的设计与实现,对于Python、B/S结构、MySql进行了较为深入的学习与应用。主要针对系统的设计,描述,实现和分析与测试方面来表明开发的过程。开发中使用了 django框架和MySql数据库技术搭建系统的整体架构。利…

Git结合Gitee的企业开发模拟

本系列有两篇文章: 一是另外一篇《快速使用Git完整开发》,主要说明了关于Git工具的基础使用,包含三板斧(git add、git commit、git push)、Git基本配置、版本回退、分支管理、公钥与私钥、远端仓库和远端分支、忽略文…

迈向无限可能, ATEN宏正领跑设备切换行业革命!

随着互联网在各个领域的广泛应用,线上办公这一不受时间和地点制约、不受发展空间限制的办公模式开始广受追捧,预示着经济的发展正朝着新潮与活跃的方向不断跃进。当然,在互联网时代的背景下,多线程、多设备的线上办公模式也催生了许多问题:多设备间无法进行高速传输、切换;为保…

直播预约|哪吒汽车岳文强:OEM和Tier1如何有效对接网络安全需求

信息安全是一个防护市场。如果数字化程度低,数据量不够,对外接口少,攻击成本高,所获利益少,自然就没有什么攻击,车厂因此也不需要在防护上花费太多成本。所以此前尽管说得热闹,但并没有太多真实…

软技能的重要性:在面试中展示团队合作与沟通能力

🌷🍁 博主猫头虎 带您 Go to New World.✨🍁 🦄 博客首页——猫头虎的博客🎐 🐳《面试题大全专栏》 文章图文并茂🦕生动形象🦖简单易学!欢迎大家来踩踩~🌺 &a…

故事营销要搞,但不要迷恋于故事

故事营销要搞,但不要迷恋于故事【安志强趣讲274期】 趣讲大白话:迷恋故事就输了 **************************** 看了策划界有名的李金斗的《故事营销》 真心讲写的非常一般 东拼西凑的 把三个东西要梳理一下: 1、品牌调性:就是以某…

ZooKeeper集群环境搭建

🥇🥇【大数据学习记录篇】-持续更新中~🥇🥇 个人主页:beixi 本文章收录于专栏(点击传送):【大数据学习】 💓💓持续更新中,感谢各位前辈朋友们支持…

Jetpack Compose 自定义 好看的TabRow Indicator

背景 Jetpack Compose 提供了强大的 Material Design 组件,其中 TabRow 组件可以用于实现 Material Design 规范的选项卡界面。但是默认的 TabRow 样式可能无法满足所有场景,所以我们有时需要自定义 TabRow 的样式。 Jetpack Compose 中使用 TabRow 简单使用 TabRow 一般可以…

新手练习python+selenium自动化测试实战项目【还有其他项目】

说明:本项目采用流程控制思想,未引用unittest&pytest等单元测试框架 【项目都放在最下面小卡片了,有需要可以自取】 一.项目介绍 目的 测试某官方网站登录功能模块可以正常使用 用例 1.输入格式正确的用户名和正确的密码,验…

Stable Diffusion 提示词技巧

文章目录 背景介绍如何写好提示词提示词的语法正向提示词负向提示词 随着AI技术的不断发展,越来越多的新算法涌现出来,例如Stable Diffusion、Midjourney、Dall-E等。相较于传统算法如GAN和VAE,这些新算法在生成高分辨率、高质量的图片方面表…

Gazebo仿真环境下的强化学习实现

Gazebo仿真环境下的强化学习实现 主体源码参照《Goal-Driven Autonomous Exploration Through Deep Reinforcement Learning》 文章目录 Gazebo仿真环境下的强化学习实现1. 源码拉取2. 强化学习实现2.1 环境2.2 动作空间2.3 状态空间2.4 奖励空间2.5 TD3训练 3. 总结 1. 源码…