MySQL45讲-事务隔离和索引

一、事务

1.概述

　　事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。MyISAM 引擎就不支持事务，InnoDB是支持事务。

2.隔离性与隔离级别

　　事务隔离的特性
　　ACID：原子性、一致性、隔离性、持久性

• 一致性：由原子性，隔离性和持久性保证
• 原子性： 由 Undo log 保证，Undo Log 会保存每次变更之前的记录，从而在发生错误时进行回滚。
• 隔离性：由 MVCC 和 Lock 保证
• 持久性：由 Redo log 保证。每次真正修改数据之前，都会将记录写到 Redo Log 中，只有 Redo Log 写入成功，才会真正的写入到 B+ 树中，如果提交之前断电，就可以通过 Redo Log 恢复记录。

 

　　事务的隔离级别有四种：

• 读未提交RU：可以读取其他事务修改并且未提交的数据。但是会造成“脏读”，“幻读”，“不可重复读取”。
• 读提交RC：可以读取其他事务修改并提交的数据。避免了“脏读”，“但不能避免””幻读“和”不可重复读取”。(是大多主流数据库默认的事务等级)
• 可重复读RR：锁定了已经读取的数据，当前事务未提交前其他事务不予许修改。避免了“脏读“和”不可重复读取“的情况，但不能避免“幻读”。（带来了更多的性能损失）
• 串行化Seria：读取前锁定所有要读取的数据，当前事务提交前其他事务不允许修改。“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。（最严格级别，事务串行执行，资源消耗最大）

　　Oracle 数据库的默认隔离级别其实就是“读提交”

 

3.事务隔离的实现　　

• 读未提交(RU)：读不加读锁，写加了写锁，由于没有读锁所以写锁一直不会排它
• 读已提交(RC)：利用MVCC，生成语句级别的快照
• 可重复读(RR)：利用MVCC，不过它生成的是事务级别的快照。
• 串行化(S)：读加读锁，写加写锁

4.MVCC

　　如果没有MVCC，加了写锁之后就不能进行读取了，这显然是不能接受的，所以就出现了MVCC，多版本并发控制，说白了就是生成数据快照，比如在 RC 级别下，对语句生成快照，在读取的时候生成一个版本号v1，等到其他事务commit了之后会生成版本号v2，如果有最新的版本号，读取时则会读取最新的，也就是v2，如果没有commit，那么还读取刚生成的v1。 而在 RR 级别下，快照的是当前事务的版本，即时数据别其他事务改了，也是能读到最初事务的数据，这也就解决了不可重复读的问题。

5.行记录的隐藏列

InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段

• DB_ROW_ID：隐藏的行 ID，用来生成默认聚簇索引。如果创建数据表的时候没有指定聚簇索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。
• DB_TRX_ID：事务ID，操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
• DB_ROLL_PTR：回滚指针，也就是指向这个记录的 Undo Log 信息，

　　每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

 

6. 脏读、不可重复读、幻读

　　脏读：事务A读取到了事务B还没有提交的数据（RU存在）
　　不可重复读（前后多次读取，数据内容不一致）：事务A读取了其他事务更改的数据，针对update操作（RU、RC存在）
　　　　解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
　　幻读（前后多次读取，数据总量不一致）：事务A读取到了其他事务新增的数据，针对insert和delete操作 (RU、RC存在)
　　　　解决：MVCC+间隙锁+行锁，在RR级别下只解决了大部分幻读情况，但是不能完全防止幻读。

　　间隙锁 + 行锁 合成为 next-key lock

 

7.事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

8.回滚日志

• 回滚日志什么时候删除?
  系统会判断当没有实物需要用到这些回滚日志的时候，回滚日志会被删除。
• 什么时候不需要？
  当系统中没有比这个回滚日志更早的read-view的时候

8.为什么尽量不使用长事务？

　　长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还会占用锁资源，可能会拖垮整个库。

二、索引

 

1.概述

　　索引的出现其实就是为了提高数据查询的效率，就像书的目录一样

2.索引三种常见简单的数据结构，它们分别是哈希表、有序数组和搜索树。

• 哈希表

　　将值放到数组里，然后用哈希函数将key换算为一个确定的位置 ，将value放到数组的这个位置，当哈希冲突时，会拉出一个链表进行保存。适用场景：只适合等值查询情况，不适合用于范围查询

• 有序数组

　　将值按顺序放入到数组中，可采用二分法查询，时间复杂度为O(lg(N))，但是插入比较麻烦，需要移动很多值。适用场景：不再变化的值。

• 二叉树搜索树

　　每个结点的左儿子小于父节点，右儿子大于父节点，平衡二叉树是搜索速度最快的数据结构，但是索引不仅存在于内存，也要存储到硬盘中，如果用平衡二叉树，那么100万的数据就是一个树高20的二叉树，对应磁盘就是20个数据块，要查询一个数据要访问20个数据块，这就很慢了。

• N叉树

　　N叉树顾名思义就是每个节点有N儿子，儿子之间从左到右递增。它是为了解决二叉树占用数据块太多而产生的。

 

3.B树与B+树对比　　B树中每一个节点都存储数据；⽽B+树只在叶子节点存储数据，非叶子节点存储索引。所以，B+树⽐B树会多占⽤⼀些空间，这部分空间就是B+树内节点的所有空间，但B+树通过这种⽅式提⾼了整体性能，更适合于性能要求很⾼的⽂件检索。

　　由于B树的每⼀个节点都存储了真实的数据，会导致每⼀个节点存储的数据量变⼩，所以整个B树的层数就会相对变⾼，当数据量变⼤之后，维护代价是⽐较⼤的，⽽且层数越⾼，搜索或修改的性能就会越低；⽽在B+树的内节点中，只存储键值，相对⽽⾔，⼀个内节点存储的记录个数⽐B树多很多，数据存放的更加紧密，具有更好的空间局部性，因此访问叶⼦⼏点上关联的数据也具有更好的缓存命中率。由于B+树是横向扩展的，所以随着其中数据量的增长，最终会成长为⼀个矮胖⼦，不像B树⼀样是纵向扩展，最终只会变成⼀个瘦⾼个⼦。这样整体⽽⾔，B+树在搜索时，从上到下直到叶⼦节点只需要遍历层数个节点⽽已，因此性能会⽐较⾼。
　　B+树的叶⼦节点之间使⽤双向链表相连，根据页⾯编号，因此对整棵树的遍历只需要⼀次线性遍历叶⼦结点即可。⽽且由于数据顺序排列并且相连，所以便于区间查找和搜索。⽽B树则需要进⾏每⼀层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
　　B树的查询效率与键在B树中的位置有关，（在叶⼦节点的时候）最⼤时间复杂度与B+树相同，最⼩时间复杂度为1（在根节点的时候）；⽽B+树的复杂度对某个建成的树是固定的。
B树中，键的位置不固定，且在整个树结构中只出现⼀次，虽然可以节省存储空间，但却使得插⼊、删除等操作复杂度明显增加。⽽且性能不平衡，有可能会很快找到合适的位置，也有可能需要做⽐较多的IO操作才能找到。⽽B+树相对来说是⼀种较好的折中，因为内节点相对叶⼦节点⽽⾔，相当于是⼀个索引，在插⼊的过程中，只需要通过在每⼀层搜索⼀个节点，依次找到叶⼦节点之后，在叶⼦节点处做插⼊操作即可，只是在遇到⼀个节点存储满了的情况下会进⾏B+树分裂，但总体⽽⾔性能还是⽐较稳定的。

4.基于主键索引和普通索引的查询有什么区别？

　　基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

　　主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

　　从性能和存储空间方面考量，自增主键往往是更合理的选择。

 

5.有没有什么场景适合用业务字段直接做主键的呢？

　　还是有的。比如，有些业务的场景需求是这样的：只有一个索引；该索引必须是唯一索引。这就是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

　　B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

 

6.索引类型与回表

　　索引分为主键索引（聚簇索引）和非主键索引，主键索引的叶子结点存放的是这一行的数据，而非主键索引的叶子结点存放的是主键索引的值。当使用主键索引去查询时可以直接获取到该行数据，而使用非主键索引去查询时，会先拿到主键的值，再根据主键获取到该行数据，这个过程被称为回表