MySQL存储引擎

一个库中可以有多个不同存储引擎的表。

InnoDB和MyISAM数据库的表的结构定义信息都存储在frm文件中。
InnoDB的数据和索引存储在ibd文件中（聚集），MyISAM索引存在MYI文件中，数据存储在MYD文件中（非聚集）。

Innodb

若一个主键被定义，该主键则作为密集索引；
若没有主键被定义，该表的第一个唯一非空索引则作为密集索引；
若不满足以上条件，innoDB内部会生成一个隐藏主键(rowid，密集索引，该主键是一个六字节的列，该列的值随着记录的插入而自增)。

即innoDB必须得有一个主键作为密集索引存在，innodb引擎里的主键一般都是聚集索引；
非主键索引都是非聚集索引，非主键索引存储相关键位和其对应的主键值，要进行两次查找(先通过非主键索引去查找主键索引，再通过主键索引去查找数据的物理地址)。

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “a%”，就是说在不确定的范围时，InnoDB还是会锁表的。
InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

MyISAM

MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。
B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。
主键索引和其他索引没有任何区别, 都是稀疏索引, 表数据存储在独立的地方MYD, 表数据MYD和索引MYI的分开的, 索引用地址指向表数据。

两者的对比：

  1. 是否支持行级锁；
  2. 是否支持事务和崩溃后的安全恢复
    MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。
    InnoDB提供事务支持、外部键等高级数据库功能。
  3. 是否支持外键；
  4. 是否支持MVCC
    仅InnoDB支持。
    应对高并发事务，MVCC比单纯的加锁更高效；
    MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作；
    MVCC可以使用乐观锁和悲观锁来实现；
    各数据库中MVCC实现并不统一。
1
不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度可以让MyISAM望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

MySQL InnoDB存储引擎体系结构



InnoDB后台线程

InnoDB存储引擎是多线程的模型，有多个后台线程去负责处理不同的任务。

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

  1. Master Thread
    该线程是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据的一致性。包括脏页的刷新、合并插入缓冲、UNDO页回收等。
    • 脏页
      由于InnoDB在进行表数据修改的时候，并不是直接修改磁盘上的数据文件，而是将磁盘上的页缓存到缓冲池中进行修改，之后再根据一定的机制和频率刷新回磁盘，这种还没有刷新回磁盘但是已经被修改的页称为脏页。
    • 合并插入缓冲
      当要插入非聚集索引的时候，将其放在缓冲内存中保存，根据一定的机制和频率将要插入的非聚集索引一起刷新到索引页中。
  2. IO Thread
    在InnoDB存储引擎中使用了大量的AIO（Async IO）来处理写IO请求，这样可以极大的提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调处理。
  3. Purge Thread
    Purge Thread主要的作用是回收已经使用并分配的undo页。
    事务被提交后，其所使用的undo log可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。
  4. Page Cleaner Thread
    作用就是刷新脏页，这个线程是1.2.x版本中引入的，将刷新脏页的操作放入单独的线程中完成，减轻原Master Thread的工作量，提高存储引擎的性能。

InnoDB内存结构

缓冲池

InnoDB可以有多个缓冲池实例（innodb_buffer_pool_instances默认为1，最大为64），每个页根据哈希值平均分配到不同缓冲池实例中。

  1. 数据页
  2. 索引页
  3. 插入缓冲
  4. 自适应哈希索引
  5. 锁信息
  6. 数据字典信息

重做日志缓冲

InnoDB先将重做日志信息放到这个缓冲区，然后按一定频率刷新到重做日志文件。
一般每秒都会将缓冲刷新到日志，所以重做日志缓冲不需要很大。

额外的内存池

每个缓冲池中的帧缓冲（frame buffer）以及对应的缓冲控制对象，它们的内存需要从额外内存池中获取。

InnoDB关键特性

插入缓冲 Insert Buffer

两次写 Double Write

自适应哈希索引 Adaptive Hash Index

异步IO AIO

刷新邻接页 Flush Neighbor Page

InnoDB磁盘结构