MySQL事务及索引复习笔记

本文参考小林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 = ’ ’，可以创建一个 (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)