MySQL-常用存储引擎-深入了解

1、MyISAM引擎

1.1 什么时候使用？

当表的读操作远远大于写操作，且不需要事务的支持的时候，可以选用MyISAM引擎。
因为MyISAM不提供事务支持，也不支持行级锁和外键。当插入/更新操作需要写操作时需要把整个表锁定，效率太低。但是MyISAM保存了表的行数，SELECT COUNT(*) FROM TABLE能直接返回表的行数而不用扫描全表计算。

1.2 支持的三种存储格式

• **静态表：**默认的存储格式，表中的字段都是非变长字段，每个记录都是固定长度的，优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态多，空格的问题。
• **动态表：**表中包含变长字段，记录不是固定长度的，优点是占用的空间相对较少，但是频繁的更新和删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE `语句改善性能，并且出现故障恢复相对比较困难。
• **压缩表：**由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录都是单独压缩的，只有非常小的访问开支。

2、InnoDB引擎/MySQL默认引擎

2.1 有什么特点？

• 提供对数据库事务的支持，提供了提交、回滚、崩溃恢复能力的事务安全；
• 提供了行级锁和外键约束，由于锁的细粒度小，行级锁使得写操作不会锁定全表，在高并发较高的场景下使用InnoDB会大幅提升效率；
• 设计的出发点是处理大数据容量的数据库系统；
• MySQL运行时InnoDB会在内存中建立缓存池，用于缓存数据和索引。比较MyISAM引擎，InnoDB写操作效率慢一些，并且会占用更多的磁盘空间存储数据和索引。

2.2 特色功能

• **自动增长列：**可以设置自动增长列的初始值，对InnoDB引擎的表，自动增长列必须是索引/组合索引的第一列；对MyISAM表可以是组合索引的其他列；
• 外键约束： InnoDB特有，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建外键的时候，可以指定在删除更新父表的时候，对子表进行相应的操作。

2.3 InnoDB和MyISAM的区别

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效; MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

2.4 InnoDB关键特性

• 插入缓存：对于非聚集索引的插入或者更新操作,先判断插入的非聚集索引页是否在缓冲池中，若在则直接插入，若不在则先放入到一个Insert Buffer对象中，然后再以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的merge(合并)操作，这时通常将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。需要满足非唯一的辅助索引条件。
• 二次写： 由两部分组成：一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，即两个区，大小同样为2MB。
  在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序的写入到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题，在完成double write页的写入后，再将doublewrite buffer中的页写入到各个表空间文件中，此时的写入是离散的。
• **自适应哈希索引：**InnoDB会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称为自适应哈希索引；
• **异步IO：**用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。
• 刷新邻接页：当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，那么一起进行刷新，这样做的好处是通过AIO可以将多个IO写入操作合并为一个IO操作。

2.5 InnoDB的后台线程

InnoDB后台线程的分类如图所示

2.6 InnoDB的逻辑存储结构

从InnoDB存储引擎的逻辑存储结构来看，所有数据都被逻辑的存放在一个空间中，称之为表空间。表空间又由段、区、页 组成。

• 段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎是索引组织的，因此数据即索引，索引即数据。数据段即B+树的叶子节点，索引段即B+树的非索引节点。
• 区：区是由连续页组成的空间，在任何情况下每个区的大小都为1MB，为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区，在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页
• 页：页是InnoDB磁盘管理的最小单位，在InnoDB存储引擎中，默认每个页的大小为16KB，而从InnoDB 1.2x版本开始，可以通过参数**innodb_page_size**将页的大小设置为4k，8k，16k。
• 行：InnoDB是面向列的，即数据是按行进行存放的

3、MEMORY存储引擎

• MEMORY 存储引擎使用存在于内存中的内容来创建表，该类型的表访问速度快，因为数据是放在内存中的，一旦服务关闭，表中的数据就会丢失。
• 在 MEMORY 表中创建索引，可以指定使用 Hash 索引还是 BTREE 索引。
• MEMORY 存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效的对中间结果进行分析并得到最终的统计结果。

4、MERGE存储引擎

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

5、如何选择以上四个存储引擎？

• MyISAM：如果应用以读操作或插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么选择这个存储引擎是非常合适的。主要应用场景： WEB、数据仓库。

• InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性要求高，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包括很多的更新删除操作，那么InnoDB 是合适选择。 InnoDB 还能确保事务的完整提交和回滚，适用于计费和财务系统。

• MEMORY： 将数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。 MEMORY 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次要确保表的数据可恢复，数据库异常终止后表中的数据是可以恢复的。 通常用于更新不太频繁的小表，用于快速访问得到结果。

• MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。 MERGE 优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善 MERGE 表的访问效率。