Mysql学习笔记-存储引擎 试读

参考

• mysql中文文档：https://www.mysqlzh.com/doc/213.html
• 《高性能Mysql》第三版
• 《深入浅出Mysql》唐汉明第二版

概要

查看Mysql所有存储引擎

show engines;

下图是Mysql8.0的存储引擎

通过表中可以发现，只有InnoDB支持事务,除此之外还有BDB支持事务(5.1之后不支持，被收购)
最为常见有三种，InnoDB,MyISAM,MEMORY。5.5之前默认是MyISAM,5.5之后默认是InnoDB
简单来说，InnoDB支持事务，支持外键约束，支持行锁，适合高并发；MEMORY支持哈希索引，内存数据库，适合用于创建临时表。MyISAM不支持事务和外键，效率更高，支持全文索引。

MyISAM

存储

MyISAM的存储文件：

• .frm 表结构 保存了每张表的行数
• .myd 数据文件(mydata)，
• .myi 索引文件(myindex)
  MyISAM的存储格式：
• 静态表（固定长度）：默认方式
• 动态表：动态长度，存储空间小，容易产生碎片，OPTIMIZE TABLE或myisamchk -r来改善性能
• 压缩表：myisampack压缩创建，只读格式

特性

• 锁：只能加表锁(锁粒度大) 读-共享锁，写-排他锁；读的时候可以写。
• 修复：MyISAM表可能会损坏，通过myisamchk工具修复或者 check和repair修复。
• 索引特性： 即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引，支持全文索引。不支持外键，非聚集索引：索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• 延迟更新索引键：执行完成之后不会立刻将修改的索引数据写入磁盘，而是会写到内存中键缓冲区；提高写入特性，但容易造成索引被破坏。

InnoDB

存储

InnoDB的存储

• .frm 表结构
• .idb 数据和索引文件
  InnoDB的存储数据结构 (https://blog.csdn.net/marco__/article/details/81666512)
  数据页结构，页是innodb存储引擎管理数据的最小磁盘单位，而B-TREE节点就是实际存放表数据的节点，一个innodb页有七个部分组成：
  
  这是一个链表结构，Fil Header和Fil Trailer是头尾指针，记录页的头信息，内部的 Page Header/Page Directory 关心的是页的状态信息，UserRecords是存储行记录，FreeSprace是页的剩余空间。每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录，Infimum 记录是比该页中任何主键值都要小的值，Supremum 是该页中的最大值：
  B+ 树在查找对应的记录时，并不会直接从树中找出对应的行记录，它只能获取记录所在的页，将整个页加载到内存中，再通过 Page Directory 中存储的稀疏索引和 n_owned、next_record 属性取出对应的记录，不过因为这一操作是在内存中进行的，所以通常会忽略这部分查找的耗时。

特性

• 锁：行锁(锁粒度小)，除了读写锁(S,X)之外还支持意向锁，Innodb支持的两种意向锁：

  • 意向共享锁(IS)：事务想要获得一张表中某几行的共享锁；
  • 意向排他锁(IX)：事务想要获得一张表中某几行的排他锁。

• 支持事务ACID

• MVCC并发控制

• 插入缓冲
  InnoDB中，主键是行唯一的标志，通常应用程序中记录的插入顺序是按照主键的递增顺序进行插入。因此插入聚集索引都是顺序的，不用在磁盘上随机读。
  但更多时候你需要去建一些非聚集索引去辅助查询，因为B+树叶子的数据根据主键顺序存放，所以可能用你建的索引去插入的时候就是离散的了。
  所以对于非聚集索引中数据的的插入和更新，如果索引页在缓冲池就直接插入，如果缓冲池没有，就放到一个insert buffer对象中，再以一定的频率把insert buffer和辅助索引的叶子节点merge，这时通常可以把多个merge合并成一个操作。要想使用insert buffer需要满足两个条件：索引是辅助索引，索引不要求value唯一（如果要求唯一，那还要去索引页上找是不是唯一的，没法用）。
  Change buffer是insert buffer的升级，可以分成：insert buffer，delete buffer，purge buffer，可以对insert、delete和update都进行缓冲。和insert一样change buffer适用的对象还是不唯一的辅助索引。
  比如update操作，Delete buffer对应update的第一个操作，purge buffer对应update的第二个操作，即将真正的记录删除。

• 两次写（double write）
  部分写失效情况：如果在从缓冲中的数据页写到磁盘时，写到一半挂了。这样页就被损坏了，也许会想到用重做日志去恢复，但redo log存的是对页的物理操作，页已经坏了没办法恢复？？所以需要一个页副本，当写失效发生，先还原受损的页，再进行重做。
  在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是先把脏页复制到内存中的double write buffer。

• 自适应哈希
  就是Innodb会监控B+索引，如果它觉得给你建和自适应哈希会让查询速度更快就会给你建。自适应hash是根据缓冲池的B+树来构造，而且不要求对整张表建立hash索引，它会自动给某些热点页建立hash索引。
  自适应hash有一个要求：对这个页的连续访问模式必须是一样的，例如对于（a，b）这样的联合索引页，访问方式可以是：
  where a = xxx 或者 where a = xxx and b = xxx
  必须是其中一种，不能两种交替，而且要以该模式访问100次，页通过该模式访问了N次，N=页中记录*1/16。

• 异步IO（AIO）
  为了提高性能，当前数据库都采用异步IO，用户发一个IO请求之后可以再发送一个IO请求，而且它可以把多个IO请求优化成一个；

• 刷新邻接页
  就是把脏页刷到磁盘时，会检查页所在的区还有没有其他脏页，如果有就一起刷回去。好处就是AIO可以把多个IO合成一个。

MEMORY

存储

每个MEMORY表和一个磁盘文件关联起来。文件名由表的名字开始，并且由一个.frm的扩展名来指明它存储的表定义。

特性

• 不支持事务，不支持外键，表级锁。
• MEMORY存储引擎用存在内存中的内容来创建表，MEMORY表被存储在内存中，且默认使用哈希索引。关闭服务器时，表结构仍然存在，但是空表。
• MEMORY表使用一个固定的记录长度格式，MEMORY不支持BLOB或TEXT列，MEMORY表在所有客户端之间共享。

MERGE

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。