InnoDB和MyISAM存储引擎的区别

MySQL推荐主键自增的原因？

1. 索引本质是一个B+树，一个表有多少索引就有多少个B+树，数据按顺序保存在B+树上。

2. MySQL底层是以“数据页”为单位存储数据的，一个数据页默认大小16k，一个数据页写满了，才会去申请下一个数据页。

3. 如果自增ID的话，写满一个数据页，直接申请并写入下一个数据页就行了；如果不规则ID呢，写满之后申请数据页，还要把上个数据页的部分数据移动到新的数据页上，造成了B+树频繁的分裂合并，效率低下。

数据库的四种隔离级别

• 脏读：一个事务读到另一个事务未提交的数据
• 不可重复读：一个事务读到另一个事务已提交的更新的数据。【多次读取数据内容不一致】
• 幻读：一个事务读到另一个事务已提交的新插入的数据。【多次读取数据数量不一致】

⑴ 读未提交
在这种隔离级别下，查询不加锁，会产生“脏读”、“不可重复读”、“幻读”。

⑵ 读已提交
只能读到已经提交的内容。查询不加锁，能够避免脏读。可能会产生“不可重复读”、“幻读”。

⑶ 可重复读
重复读取，查询不加锁，但在数据读出来之后加锁。可能会产生“幻读”。

⑷ 串行化
在该级别下，事务顺序执行，不仅避免了脏读，不可重复读，而且避免了幻读。由于加锁，导致大量的请求超时，因此性能会比较低下。

MVCC（Multi-Version Concurrency Control）

MVCC，即多版本并发控制，这个词太高大上了。它是数据库用来处理读写冲突的手段，目的在于提高数据库在高并发场景下的吞吐性能。

MVCC能在大多数情况下替代行级锁，降低系统性能开销，但是只在 【读已提交】 和 【可重复读】两个隔离级别下工作。

第一，【读未提交】会读取到未提交的数据，而MVCC创建和删除版本发生在事务提交之后。第二，【串行化】是对所有操作都加锁，没有MVCC的事情了。

为什么说它能提高吞吐性能呢？

1. 最初的锁，只要锁住一个共享资源，其他人是禁止访问的。

2. 而最常见的场景则是读多写少的，进而出现了读写锁，读锁和读锁之间不互斥，写锁和读锁、写锁都互斥。

3. 后来又提出，能不能让读写并行呢？于是出现了MVCC：读的时候可以通过快照读，读多个版本避免了使用共享锁，因此可以使得读事务和写事务互不干扰。

MVCC实现

InnoDB每行数据有三个隐藏域：（注意《高性能MySQL》里说的两个隐藏域是误人子弟）

InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byteDB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

一致性读取：

一致性读取意味着InnoDB使用多版本来控制在某个时间点向查询操作展示的数据库快照。查询操作只能看到在此时间点之前提交的事务所做的更改，看不见之后或未提交的事务所做的更改。一致性读取不会在它访问的表上设置任何锁，因此其他会话可以在对该表执行一致读取的同时*地修改那些表。

使用 快照信息可基于某个时间点显示查询结果，而不管同时运行的其他事务执行的更改如何。

1. 对于隔离级别为【读已提交】，同一事务中，每个一致性读取都将设置并读取其自己的新快照。还记得这个隔离级别会出现【多次读取数据内容不一致】的问题吗？就是因为该级别下的MVCC每次读取都产生一个新的快照。

2. 对于隔离级别为【可重复读】，同一事务中的一致性读取将读取第一次读取建立的快照。这解释了可重复读，因为多次读取按照第一次读取生成的版本为准。

3. 另外，只有普通的查询【SELECT】才会产生快照并执行快照读，比如【SELECT ... FOR UPDATE】则是加锁，即当前读。

也就是说，MVCC依赖于三个隐藏域和事务查询操作产生的快照。

等等！！查询也涉及事务吗？？没错，在【可重复读】隔离级别是由MVCC+lock实现的，比如有范围的查询会加上间隙锁，更多参考：https://www.zhihu.com/question/266467051

MVCC更新原理：

1. 先用【排它锁】锁住目标数据行 A

2. 将当前数据保存到undo log 中，生成旧版本数据 B

3. 将数据行A的【回滚指针】字段指向数据B，然后更新数据

4. 提交事务

5. 如果失败则回滚undo log中的数据

MVCC读取原理：

1. 对于【读已提交】，每次读取都会取最新的已提交的那个版本。

2. 对于【可重复读】，总是取小于当前活动事务ID的那个版本。

redo log，undo log，bin log

redo log：重做日志，当事务提交之后会把所有修改信息都会存到该日志中，并且会把redo log持久化到磁盘，在崩溃恢复期间用于纠正不完整事务写入的数据。

undo log： 回滚日志，用于记录数据被修改前的信息。由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到被修改前的状态。

bin log：记录了对数据库执行更改的所有操作，不包括查询操作。用来进行数据恢复、数据库复制。

区别：

1. redo是物理日志，记录的是页的物理修改操作；undo是逻辑日志，根据每行记录进行记录。

2. redo用来保证事务的持久性；undo用来保证事务的原子性，帮助事务回滚。

3. redo是顺序写的，undo是随机读写的。

回过头来看事务特性，所有技术方案都是为了解决问题的。

1. 对于原子性，如果一个组合操作永远100%成功，那么原子性就失去了意义，所以原子性肯定主要是为了异常回滚而出现的，所以这也是undo log的意义所在

2. 对于持久性，由于事务提交之后就被写到redo log并持久化到磁盘，所以redo log 保证了持久性。

3. 对于隔离性，如何避免资源争抢呢？最好的方法就是同一时间只能有一人获得或者一人一份，加锁和MVCC多版本并发控制机制正是保证了这一点。

4. 对于一致性，也就是从一个一致的状态到另一个一致的状态，不存在中间状态，那么原子性+持久性+隔离性是不是等于一致性呢？

所以，数据库的事务是基于：undo log + redo log + 锁 + MVCC 实现的。

3种行锁算法

Record Lock：单个行记录上的锁，实际上是锁住索引记录

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身

Next-Key Lock：前面二者的组合，锁定一个范围，包括记录本身

InnoDB默认的隔离级别是【可重复读】，在此隔离级别下，用Next-Key Lock的方式来加锁。而在【读已提交】的隔离级别下，用的是Record Lock。

MySQL InnoDB存储引擎知识点