Mysql第三章：存储引擎（MyISAM和Innodb）_mysql存储引擎

目录

1：什么是存储引擎

2：常用存储引擎

2.1：MyISAM特点

2.2：Innodb特征

2.2.1：Innodb优点

2.2.2：innoDB内存模型

3：MVCC多版本控制器

3.1：首先了解事务ACID 的实现

3.2：首先了解undo和redo和binlog的作用。

3.3：快照读和当前读

3.4：读锁、写锁、间隙锁、表锁、行级锁

3.5:正式了解MVCC多版本控制器

4：两者区别

4.1：聚簇索引和非聚簇索引区别

---

1：什么是存储引擎

        MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

    这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

2：常用存储引擎

2.1：MyISAM特点

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

• .frm(存储表定义)
• MYD(MYData，存储数据)
• MYI(MYIndex，存储索引)

　MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。如下图所示：

　　这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

　　同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

1：在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。由于数据索引和存储数据分离，MyISAM引擎的索引结构是B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是所他的索引和实际数据是分开的。不过索引指向实际的数据，这种索引也就是非聚合索引。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2.但是它没有提供对数据库事务的支持，是表级锁（插入修改锁表），因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

2.2：Innodb特征

每个InnoDB在磁盘上存储成2个文件

• .frm(存储表定义)
• ibd(数据和索引文件)

 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　主键索引：上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

　　辅助索引：第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在name上的一个辅助索引：

　　这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引（也就是除了主键 我们创建的其他索引）搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
 

　　了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调(可能是指“非递增”的意思)的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调(可能是指“非递增”的意思)的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

与MyISAM引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

2.2.1：Innodb优点

        1:就是针对事务的支持，事务（ACID），行级锁，外键，Java中使用事务处理，首先要求数据库支持事务。如使用MySQL的事务功能，就要求MySQL的表类型为Innodb才支持事务

        2:使用MVCC多版本控制器来控制事务。

2.2.2：innoDB内存模型

1：缓冲池数据页和索引页：

         数据以叶的方式操作数据，16K大小来自于机械硬盘时代，因为我们增删改查的时候，都是把这一页的数据查询到内存中，并且事先在内存中开辟好很多个空闲叶。InnoDB将磁盘中的数据按照叶的方式缓冲到内存中的一个区域，包含了索引页和数据页，用来弥补磁盘的速度差距，并且修改数据的时候，也是先修改缓冲池的数据，然后才会把数据刷新到内存中去的。缓冲页的大小默认是16KB。缓冲池还包含锁信息，插入缓存和redo日志等等。当在缓冲区的数据页中修改数据的，页数据会变脏，这个时候需要redo文件来记录修改后的数据，先做redo日志，然后再来修改页数据。

2：插入缓存(insert buffer)

insert buffer 在聚簇索引（主键id自增的的时候）在磁盘中按照顺序插入数据，没有磁盘的随机插入数据，提升效率。但是主键是uuid的时候不行，或者插入其他的索引，比如身份证的时候不是，索引不是顺序的，插入的时候身份证的索引会慢。 

这个时候引入插入缓存：索引是辅助索引 索引不唯一

现将索引插入缓冲池的数据页和索引页然后在插入到磁盘。

3：两次写和异步IO

两次写：当内存中的数据页16KB将数据写到数据表的时候，数据库宕机，只写了4KB。这个时候发生写失效。innodb数据引擎会生成这个16KB数据页的副本，当写失效的时候回从副本写入数据，这就是两次写。

异步IO:用户查询全表的数据集，这个时候用户不用等每个数据页扫描完毕，在扫描下一个数据页。可以多个io来处理，等待处理完毕合并返回结果。

3：MVCC多版本控制器

3.1：首先了解事务ACID 的实现

（A:原子性 C:一致性 I:隔离性 D:持久性）

实现原子性：将修改的多条数据所在的数据页从磁盘加载到内存缓冲池，然后修改数据，有undolog 备份修改前的数据用于回滚  修改后的数据redolog一起提交，从而保证了原子性

实现一致性：依托于其他的三个特性

实现隔离性：MVCC版本控制器（多个事务之间修改了同一条数据，通过mvcc，不是简单的加锁来解决，从而提升效率）

实现持久性：持久性就是事务一旦提交，就会改变数据，数据库故障也不会影响。我们在一个事务中修改了多条数据，不会一次一次的向磁盘修改。会先把修改的数据记录到redolog(重做日志)。但是这个redolog是属于存储引擎INNODB的。mysql分为存储引擎层和service层。这个时候回再写一份Binlog日志。binlog属于service层。

1：首先执行器调用引擎获取数据，如果数据在内存中就直接返回；否则先从磁盘中读取数据，写入内存后再返回。 
2：修改数据后再调用引擎接口写入这行数据
3：引擎层将这行数据更新到内存中，然后将更新操作写入redo log，这时候redo log标记prepare状态。然后告诉执行器我处理完了，可以提交事务了。
4：执行器生成这个操作的binlog，并把binlog写入磁盘，然后调用引擎提交事务
引擎收到commit命令后，把刚才写入的redo log改成commit状态
5：至此我们的一条更新语句就算基本完成了，这里面涉及了两阶段提交prepare阶段和commit阶段。

什么是两段式提交？

之前我们也清楚了，binlog是MySQL中Server层的日志，redo log是InnoDB存储引擎特有的。我们为了一致性就需要把这两个日志很好的持久化下来。而上面的redo log经历prepare和commit两个阶段才算提交。

分析：时刻A发生故障redolog写完，binlog没有写。事务回滚

分析：时刻B发生故障redolog和binlog都已经写完数据。查看redolog和binlog的数据一致性。如果一直就提交，否则回滚。

3.2：首先了解undo和redo和binlog的作用。

undo（撤销日志）：数据修改前的内容（用于回滚），当把数据从磁盘读取内存的时候，每次数据操作这些数据都会变脏，就是脏数据，需要都会记录undo日志。当发生回滚的时候，就需要这些日志用来回滚数据

redo（重置日志）：数据修改后的内容，在缓冲池，然后数据修改后生成redo日志，需要把这些内存中的数据插入到磁盘。这个时候当数据库宕机的时候。这些redo就是重要的记录，重启之后会把redo日志也就是修改的数据重新写入数据库。

binlog：二进制日志，保存在磁盘中，记录了数据库的所有改变。它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：

  A.事务开始.
  B.记录A=1到undo log.
  C.修改A=3.
  D.记录A=3到redo log.

  E.记录B=2到undo log.
  F.修改B=4.
  G.记录B=4到redo log.
  H.将redo log写入磁盘。

  I.事务提交

3.3：快照读和当前读

在开启mvcc之前我们需要首先了解当前读和快照度 以及mysql的各种锁 比如读锁、写锁、间隙、行锁、表锁等概念

快照度：简单的select

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读：

select lock in share mode(共享锁-读锁),

select for update ;(排它锁-写锁   读的时候锁定数据，其他事务不能使用排它锁，只能select，只能等待事务提交)

update, insert ,delete(排他锁 修改的时候，锁定数据，其他线程不能修改)

这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

1：开启两个事务 验证事务1开启写锁（排它锁）之后，事务2的写锁操作会等待事务1提交

 2：开启两个事务 验证事务1开启读锁（共享锁）之后，事务2的锁操作会等待事务1提交

 3.4：读锁、写锁、间隙锁、表锁、行级锁

首先我们建表

CREATE TABLE `z` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
 
 
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('1', '1', '0');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('3', '6', '1');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('5', '4', '2');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('7', '8', '3');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('8', '10', '4');

b的索引结构如下，间隙锁分析

 我们验证b的值[4-8]之间不能才插入数据，并且主键在不能大于5 小于7，防止出现幻读。

当b=6的时候的任意主键id的值。

事务1

事务2 

 间隙锁在数据库事务RR的情况，在使用间隙锁的情况下能防止幻读产生，但是会有间隙，造成不必要的锁。锁住命中索引项的前一个索引到命中索引项的后一个索引之间的一个左开右闭区间

在RR的情况下，不使用间隙锁，修改数据，查询（当前读 for update）,会出现幻读,快照度不会产生幻读。

1)、唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
2)、普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；

 

3.5:正式了解MVCC多版本控制器

1：什么是MVCC多版本控制器？

在上边我们知道了事务的原子性依靠undo和redo日志对于多条数据的统一需改和统一提交。持久性也依靠了redo和binlog的两段式提交。但是这些操作不能实现多个事务修改一条数据的隔离性。我们要实现隔离性可能凭经验对数据加锁。锁定这条被多个事务修改的数据来实现（行锁）。但是单纯的行锁会影响效率。我们可以想到行级读写锁。写的时候加锁。可以读，不能写。读的时候不加锁来实现。但是随着mysql的事务隔离级别的复杂化，RR（重复读）和RC（读已提交）带来的影响个不相同。所以单纯的读写锁已经不能满足需求了。 

所以有了MVCC多版本控制器，实现了更复杂的机制。做到了有读写冲突的时候也能不加锁。非阻塞的读取数据。

MVCC是行锁的变种,主要是相比较以前的行锁，解决的读写之间的冲突，不需要加锁，但是写写之间需要加行锁。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖数据行中记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

表数据演示：

 undo日志演示：

Read View演示：说白了Read View就是事务进行快照读操作的时候生产的读视图

现在来看看MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：（锁表）

　　② Repeatable read (可重复读)：可避免脏读、不可重复读、幻读的发生。

   （mvcc每次select查询之后使用第一个查询的read view 避免读取到别人事务提交的数据，同时配合next key lock锁解决幻读）

　　③ Read committed (读已提交)：可避免脏读的发生，会读取到别人提交的事务数据，两次查询不一致。

（mvcc每次select同一条数据都会有一个新的read view，也就是会有多个，形成一个链表，那么就会出现读取到其他事务提交的数据

重复读取数据不一致）

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

在事务123中分别修改表数据。开启事务，事务隔离级别是RC

 当事务4查询数据的时候，会在Read View（是事务开启时，当前所有没有事务的一个集合）中结合undo日志，在日志中查询事务的号，然后选择版本，读取数据。未提交的事务是1和3。然后根据连读可以读物到2.

trx_list:[1,3] 存活事务集合

up_limit_id：1
记录列表中事务ID最小的ID

low_limit_id：4+1=5
ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

4：两者区别

4.1：聚簇索引和非聚簇索引区别

InnoDB：

1、通过id=7查询，直接可以取到数据；

2、如果通过name查询，需先找到id=14，然后再通过id查询，需要回表查询；

id name age  id是表user的主键 name创建索引

回表：select * from user where name='张三'（需要回表 再根据主键查询所有信息）

索引覆盖：select  id,name  from user where name='张三'(索引覆盖  不需要回表 直接得到id,name)  出现Using index  索引覆盖

索引下推(只能使用联合索引)：select  id,name  from user where name like '三%'  and company='百度'  (索引下推  不需要回表 直接得到name名字开头是三的人 在联合索引中匹配company='百度'   然后得到数据) 

最左匹配：联合索引，必须要有最左边的列出现 才能使用索引

select name,age FROM table where name='张三'