mysql学习笔记之综述

mysql存储引擎

MYISAM和InnoDb区别

1. 概述
   MYISAM是Mysql5.5版本以前默认的存储引擎，性能极佳，而且提供了大量特性，包括全文索引，压缩，空间函数等，但MyIsam不支持事务和行级锁，最大的缺陷是崩溃后无法恢复，InnoDb是Mysql5.5版本之后的默认存储引擎。
2. 两者比较
   (1). 是否支持行级锁：MyIsam只有表级锁，而InnoDb支持行级锁和表级锁，默认为行级锁。
   (2). 是否支持事务和崩溃后安全恢复：MyIsam强调的是性能，每次查询具有原子性，其执行速度比InnoDb更快，但是不支持事务，InnoDb提供事务支持，外部建等高级数据库功能
   (3). 是否支持MVCC：InnoDb支持，应对高并发事务，MVCC比单纯的加锁更高效，MVCC只在读取已提交和可重复读两个隔离级别下工作，MVCC可以使用乐观锁和悲观锁来实现，各数据库中MVCC实现并不统一。

MVCC多版本并发控制

1. 概念：指的是一种提高并发能力的技术，最早的数据库系统，只有读读之间可以并发，读写，写读，写写之间都要阻塞，引入多版本之后，只有写写之间互相阻塞，其他三种操作可以并行，这样大幅度提高了数据库的并发能力。
2. 实现：通过保存数据在某个时间点的快照，意味着一个事务无论运行多久，在同一个事务里看到的数据是一致的视图。根据事务开始时间不同，同时也意味着在不同时刻不同事务看到的相同表的数据可能是不同的（InnoDb是在undolog中实现的，通过undolog找回数据的历史版本）。

当前读和快照读

1. 当前读
   读取的记录是最新版本，读取时还要保证其他并发事务不能修改当前记录，是悲观锁的一种，会对当前记录进行加锁，如下操作都是当前读

  select    lock in share mode
  select.  for update
  update
  insert 
  delete

2. 快照读

 不加锁的select操作就是快照读
 快照读的前提是隔离级别为非串行级别，串行级别下退化成当前读
 快照读出现是由于并发考虑，即select操作的同时不会加行锁，快照读的实现就是基于多版本并发控制。
 快照读读取得并非最新纪录，有可能是历史版本。
  思考：在电商系统中高并发场景下如果想更新某商品的库存应该如何做？
   方案一：每次下单时，查询系统中该商品的库存量，扣减完成后，再更新数据库。 此方案会出现的问题：
   1. 高并发场景下，每次查询数据库性能慢
   2. 请求A和请求B获取到的库存量可能都为10
   3. 更新操作时，由于多线程并发可能导致死锁问题。
   方案二：将数据加载到内存或者分布式缓存中，在内存中进行扣减，扣减成功后，确认库存量大于0，下单成功，库存量小于0，返回错误信息扣件失败，最终订单下单以及更新库存操作可以通过mq进行异步扣减。

说白了，MVCC就是为了实现读和写冲突不加锁，而这个读就是指快照读，而非当前读，当前读实际上是一种加锁操作，是悲观锁的体现。

MVCC实现原理

实现原理主要依赖记录中的三个隐式字段，undo日志，ReadView读视图来实现。

1. 隐式字段

   数据库中的每行记录除了我们自定义字段外，还有数据库隐式定义的：

   DB_TRX_ID : 创建或者最后一次修改记录的事务ID
   DB_ROW_ID:隐藏主键，如果用户定义主键，则使用用户定义的，否则使用隐藏主键
   DB_ROW_PTR: 回滚指针（指向上一个历史记录）。与undolog配合使用。

2. undolog回滚日志

 主要分为两种：
  insert undo log: 代表事务在insert新纪录时产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
 
 update undo log：事务在进行update或delete时产生的undolog，不仅在事务回滚时需要，
 在快照读时也需要，所以不能随便删除。

对MVCC有帮助的实际是update undo log，undo log实际上就是保存数据的历史版本状态。
当不同的事务对同一条记录做修改时，会导致记录的undo log形成一个链表，链表的头是最新的历史记录，炼尾是最早的历史记录。

3. ReadView 读视图

   事务在进行快照读时，产生的读试图，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，此快照记录并维护当前活跃事务Id（当每个事务开启时，都会分配一个Id，这个Id是递增的，所以最新的事务，Id越大），读视图中的字段：

   trx_list: 系统活跃事务
   up_limit_id: 活跃列表中最小的事务Id
   low_limit_id: 尚未分配的下一个事务Id

所以，ReadView是用来做可见性判断的，即当我们某个事务执行快照读的时候，对该记录创建一个Read View读试图，把它当作条件用来判断当前事务能够看到哪个版本的数据。

执行规则：
1. 首先判断DB_TRX_Id < up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在记录，如果大于等于进入下一个判断
2. 接下来判断DB_TRX_ID>=low_limit_id 如果大于等于则表示DB_TRX_ID所在记录在ReadView生成后才出现，那么对于当前事务肯定不可见，如果小于，则进入下一步判断。
3. 判断DB_TRX_ID是否在活跃事务中，如果在，则代表在ReadView生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在ReadView生成之间就已经开始commit，那么修改结果可以看到。

举例说明MVCC执行流程

1. 事务0在完成数据插入后，事务2对该数据执行快照读，此时数据库为该行数据生成了一个ReadView读视图，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以ReadView记录了当前活跃事务1，3的Id，维护在一个列表中
   如下：

2. 此时Read View中维护的数据为
   trx_list: 1,2,3
   up_limit_id: 1
   low_limit_id: 5

3. 事务4在事务2执行快照读前，提交了事务，所以我们的事务2在快照读该行记录时，就会拿该行记录的DB_TRX_ID去跟up_limit_id,low_limit_id,trx_list进行比较，判断当前事务2能看到的记录版本是哪个。
   根据上述规则，事务2执行快照读，此时DB_TRX_ID为4，up_limit_id 为1，low_limit_id 为5。
   进入判断1 DB_TRX_ID<up_limit_id 不满足，进入判断2，DB_TRX_ID>=low_limit_id 不满足,进入判断3，此时DB_TRX_ID已不再活跃事务中，则说明这个事务是在Read View生成之前已经开始commit，则此时修改结果是可见的。故此时事务2可以获取到事务4的提交信息。
   如果事务2在事务4提交之前进行过一次快照读，此时根据判断3的前一种情况，事务2无法读取最新的版本。

索引

1. 概述
   Mysql索引使用的数据结构主要有Btree索引和Hash索引，对于hash索引来说，底层数据结构是hash表，因此在绝大多数需求为单条记录查询时可以选择hash索引，查询性能最快，其余大部分场景下，建议选择Btree索引。
2. 存储引擎实现对比
   Mysql中Btree索引使用的是B+Tree索引，但对于主要两种存储引擎实现方式是不同的。

MyIsam：B+Tree 叶节点的data域存放的是数据记录的地址，再索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出data域的值，然后以data域的值为地址取出相应的数据记录，被称为“非聚簇索引”。

InnoDb: 数据文件本身就是索引文件，相比MyIsam数据文件和索引文件分开存放，表数据文件本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录，这个索引key是数据表的主键，因此InnoDb表数据文件本身就是主索引，被称为“聚簇索引”，而其余的索引都是辅助索引，辅助索引的data域存储相应记录的主键值而不是地址，在根据主键索引搜索，找到key所在的节点，取出完整数据记录。

MyIsam索引文件和数据文件是分开存放的，InnoDb是同一个文件，所谓的“聚簇索引”也就是根据key找到索引数据取出来的数据满足所要取出的所有数据，不需要在回表进行二次查询。“非聚簇索引”是根据索引key找到索引记录，取出的数据是数据的行key，然后会进行二次回表取出行记录，故为“非聚簇索引”。