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

MySQL事务及索引复习笔记

本文参考小林coding,地址事务隔离级别是怎么实现的? | 小林coding

事务


一、事务是什么?

比如一个程序是转账,你要扣减a的余额,增加b的余额,但是如果程序执行扣减成功然后挂了,就会出现a的余额扣减了但是b的余额没增加的情况。因此我们要引入事务,也就是要么全部成功,要么全部失败

二、事务四大特性

原子性、一致性、隔离性、持久性
原子性 就是 一个事务中的业务要么都成功要么都失败,不能扣除了a余额却不增加b的余额
一致性 就是 事务开始前和结束后,数据库要保持一致状态,比如转账前后a和b的总余额不能少
隔离性 就是 多个事务并发执行时它们互不影响
持久性 就是 事务一旦提交就会持久化到数据库

三、事务隔离性

同时处理多个事务时会出现的问题:脏读、不可重复读、幻读
(1)脏读是什么?

      当a事务对数据做出修改但未提交,此时b事务进来查询 查到数据,然后a事务回滚撤销修改,那b事务读到的数据就是脏数据,也就是脏读
(2)不可重复读是什么?
      a事务进来读取一次数据,然后此时b事务进来修改数据并提交,然后a事务再次读取数据就会发现数据前后不一致,也就是不可重复读
(3)幻读是什么?
      a事务进来查询一次数据,查到有3条,此时b事务进来新增一条并提交,然后a事务再次查询数据就会发现有4条,记录数不一致,也就是幻读

简单说一下不可重复读和幻读的区别:走的流程差不多,主要区别在不可重复读是a前后两次读到的数据不一致,而幻读是读到的记录条数不一致

四、解决方法:事务隔离级别

sql提出四种事务隔离级别去解决这几种问题
分别是:读未提交、读已提交、可重复读、串行执行

读未提交:最捞的版本,放飞自我,三种问题都可能会出现

读已提交:只允许读已提交的数据,可以避免脏读

可重复读(默认的隔离级别):一个事务中多次读取到的数据必须相同,可以避免不可重复读

串行化:多个事务串行执行,一个事务提交之后才能执行另一个事务,最安全,性能最差

简单说一下可重复读:可重复读就是假如事务a会多次读取某条记录,就以他一开始看到的数据为准,直到事务提交

五、ReadView快照

读未提交就是不设限,数据一直保持最新,但是最容易出错;而串行化就是加锁

而【读已提交】和【可重复读】都会用到 ReadView 快照,区别在于读已提交是每次语句执行前都要更新一次快照,而可重复读是在事务开始时生成一次快照

然后对于InnoDB引擎存储的数据库表,他的一条数据会包含两个隐藏列:
第一个 trx_id,也就是最近修改这条数据的事务id
第二个 roll_pointer,指向旧版本记录
然后我们拿当前记录的trx_id去和快照中的id进行比对:

记录的 id < 快照的最小事务id 时,说明这条记录在快照创建前已提交,可以读取
快照的最小事务id < 记录的id < 下一个事务的id  时,需要进行判断:
①如果在活跃事务列表中,说明记录还在活跃状态,不可读取
②如果不在活跃事务列表中,说明已经被提交,可以读取
记录的id >下一个事务的id 时,说明这条记录是快照创建后才修改的,不可读取

索引

一、索引是什么

索引是基于数据库字段建立的一种数据结构,可以提升查询速率
在不建立索引前,我们查找一个数据需要全表扫描,遍历整个表,在建立索引后,我们根据索引即可快速找到需要的数据,而不需要遍历表,索引就类似一个目录一样

二、索引优缺点

优点:快速找到数据,提升查询速率,减少磁盘IO次数
缺点:索引是一种数据结构,建立索引需要消耗一定的空间,同时由于索引的结构,会降低增删改的速率

三、索引有哪几种

按最简单的来划分:主键索引、唯一索引、普通索引、联合索引、前缀索引、全文索引
主键索引:伟大无需多言,非空,通常在建表时就已创建
唯一索引:字段值必须唯一,可以为空
普通索引:平平无奇
联合索引:重点
前缀索引:用于 like"a%"的匹配
全文索引:将文本拆分成词条进行匹配

四、索引结构是什么样的

是B+树,结构如下图所示:

首先B+树和二叉树不同,二叉树每个节点最多只有两个分支,B+树可以有n个;他和B树也不同,不像B树中间的非叶子节点也会放数据,而且底层的叶子节点是双向链表实现

B+树的非叶子节点存放索引键值和指针,指向对应的下一级,而叶子节点存放数据

对于这个图中的树来说,它是怎么样走的呢?
当我获得一个索引键值,首先我会看到有1、10和19,然后我会拿这个键和这三个数进行比较:
1 ≤ key<10时,走第一个
10 ≤ key<19时,走第二个
19 ≤ key 时,走第三个
然后继续往下走直到走到叶子节点就可以找到数据了

如图,这种叶子节点放完整数据的属于主键索引,而叶子节点只放主键值的是二级索引
一般我们建的普通索引就是二级索引
它们的区别在于:
我用主键索引去找可以直接找到完整数据
我用二级索引去找只能找到主键,然后必须回表,根据主键再去主键索引找完整数据

五、联合索引

就是用两个或者以上属性的值作为B+树的key值
但他的实际效果往往是第一个属性是全局有序的,后面的属性往往是全局无序的,只在第一个属性相同的情况下是有序的
举例:


比如我建立了一个a和b的联合索引

"where a=1 and b=2" 对a、b都有用
"where a>1 and b=2" 对a有用,对b没用,因为B+树找到a>1的数据后,它们对应的属性b往往是无序的
"where a≥1 and b=2" 对a有用,对b部分有用,按a>1查出来的对b没用,按a=1查出来的对b有用

能看懂吧?就是>、<索引会失效,但是=不会

继续

"where key between x and y" 相当于 x ≤ key ≤ y ,只在=部分有用
"where name like 'a%' "   对匹配到的a....无用,只在当 name=a的时候有用

还有一个是经典问题:我建立了一个a,b,c的联合索引,我查a,b这个索引有没有用
答:有用
但是必须按顺序,我建立一个a,b,c的联合索引,我要用这个索引,查询条件必须依次包含a,b,c
比如(a,b)有用 (a,c)没用  (b,c)也没用

六、索引优化

①减少回表次数,建立优秀的覆盖索引
比如有一个 users 表,包含 id、name、age 和 email 列。如果经常执行查询 SELECT name, age FROM users WHERE email = 'example@example.com',可以创建一个 (email, name, age) 覆盖索引

②设置自增主键,在B+树后面追加,而不是再走一次B+树
③用区分度大的属性作为索引,比如id,而不是性别这种
④索引最好是not null

七、常见索引命令

 普通索引

CREATE INDEX idx_age ON users (age)

 唯一索引

CREATE UNIQUE INDEX idx_email ON users (email)

联合索引

CREATE INDEX idx_name_age ON users (name, age)

主键索引(添加主键约束)

ALTER TABLE students ADD PRIMARY KEY (id)

原文地址:https://blog.csdn.net/m0_62519278/article/details/146141070
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.kler.cn/a/586936.html

相关文章:

  • Qt从入门到入土(十) -数据库操作--SQLITE
  • JAVA EE(10)——线程安全——synchronized JUC(java.util.concurrent) 的常见类 线程安全的集合类
  • 机器学习编译器(二)
  • Java中的访问修饰符有哪些
  • Swagger 从 .NET 9 中删除:有哪些替代方案
  • 洛谷 P4933 大师
  • LRU(最近最少使用)算法实现
  • 探索Maas平台与阿里 QWQ 技术:AI调参的魔法世界
  • 车载软件刷写工具vFlash --- 自动化接口(Automation API)应用简介
  • 德语A1学习
  • 批量ip反查域名工具
  • 删除有序数组中的重复项(26)
  • [网络] 网络基础概念--socket编程预备
  • Ubuntu 24 常用命令方法
  • 【Git】配置Git
  • 按钮权限的设计及实现
  • uniapp-x vue 特性
  • 在线 SQL 转 SQLAlchemy:一键生成 Python 数据模型
  • AcWing--870.约数个数
  • Windows环境下安装部署dzzoffice+onlyoffice的私有网盘和在线协同系统