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)