存储引擎介绍

• 存储引擎是数据库底层软件组织，数据库管理系统(DBMS)依托引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
• 基本功能
  • 如何存储数据
  • 如何为存储的数据建立索引
  • 如何更新和查询数据
  • 提供数据快速增删改查的功能
• 数据库存储引擎决定了数据存取方式，直接影响数据库读写性能。

常见存储引擎

• MySQL8.0 默认有9个存储引擎
  • MEMORY
  • MRG_MYISAM
  • CSV
  • FEDERATED
  • PERFORMANCE_SCHEMA
  • MyISAM
  • InnoDB
  • BLACKHOLE
  • ARCHIVE
• 单机MySQL 对Federated（联邦）不支持

MyISAM存储引擎

• MyISAM存储引擎在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：.frm(存储表定义，MySQL8.0已删除) .myd(存储数据) .myi(存储索引)
• MyISAM的表支持3种不同的存储格式，分别是静态表（默认）、动态表和压缩表
  • 静态表中的字段都是非变长字段，存储非常迅速，容易缓存，出现故障容易恢复，但占用空间比动态表大；
  • 动态表包含变长字段，占用的空间相对较少，但是频繁更新和删除记录会产生碎片，需要定期执行optimize table语句或myisamchk -r 命令来发送性能，并且在出现故障时恢复相对比较困难；
  • 压缩表由myisampack工具创建，占据非常小的空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支；
• 不支持事务、也不支持外键，其优势是访问速度快，对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表；
• 静态表在存储时会按照列的宽度定义补足空格，但是在应用访问时并不会得到这些空格，这些空格在返回给应用之前已经去掉。所以，当需要保存的内容后面本来就带有空格时，在返回结果的时候也会被去掉，开发时需要注意（内容前的空格不会被去掉）。
• 支持三种索引：B-Tree，R-Tree，Full-text。
• 表定义在mysql.ibd文件中，一般在data目录下。

InnoDB存储引擎

• InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，但是对比MyISAM，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引；
• InnoDB的存储方式采用共享表空间存储和独立表空间存储
  • 共享表空间：InnoDB的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在；
  • 共享表空间优点
    • 表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制。
    • 数据和文件放在一起方便管理。
  • 共享表空间缺点
    • 容易多表混合存储，大量删除操作后表空间中将会有大量的空隙。
    • 共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作导致表存储空间扩大后，即使删除相关的表也无法回收那部分空间。
  • 独立表空间：每一个表都将会生成独立的文件，来进行存储。
    • .frm表描述文件(MySQL8.0已删除，使用mysql.ibd代替)
    • .ibd文件：其中.ibd文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中；
    • 独立表空间优点
      • 每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。
      • 空间可以回收（除drop table操作外）。
      • 不管怎么删除，表空间的碎片不会太严重的影响性能。
    • 独立表空间缺点
      • 当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法。
  • 查看当前数据库的表空间管理类型：show variables like “innodb_file_per_table”;
    • ON代表独立表空间管理，OFF代表共享表空间管理。

MEMORY 存储引擎

• MEMORY存储引擎将将数据存储到内存中， 为查询和引用其他表数据提供快速访问。
• 默认情况下，MEMORY数据表使用散列索引，利用这种索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了。
• 在内存中存储表数据确实会提供很商的性能， 但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
• Memory数据表里的数据使用的是长度不变的格式， 无法使用BLOB和TEXT等长度可变的数据类型。
• Memory同时支持散列索引和B树索引。

MRG_MYISAM 存储引擎

• MyISAM表的组合
  • MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除操作；
  • 对于MERGE类型表的插入操作，是通过Insert_method子句实现的。可通过first/last指明插入到第一张表或是最后一张表，不定义这个子句或者定义为no，表示不能对这个merge表执行插入操作；
  • drop操作只是删除merge的定义，对内部的表没有任何影响；
• 可以突破对单个MyISAM表大小的限制，并且通过不同的表分布在多个磁盘上，可以有效地发送merge表的访问效率；
• 适用场景多为存储服务器日志数据；
• 类似于建立了一个虚拟的表结构或者表映射。
• 对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间段相关。

create table tb_merge(xx,x,x) engine=merge union(tb_log1)
insert_method=last;

alter table tb_merge engine=merge union(tb_log1) insert_method=last;

• 总表可以映射的所有子表的总行数上限为 2的64次方 行。
• 子表不支持分区(Partition)
• 总表和子表的主键都不能使用自动增长(auto increment)
• 子表之间不能保证唯一键约束，只能保证单个子表内部的唯一性约束

CSV存储引擎

• csv存储引擎是基千CSV格式文件存储数据。
• csv存储引擎的表文件组成：
  • ．frm (MySQL8.0已删除， 用mysql.ibd代替）文件：存储表结构；
  • ．csv文件：存储数据；
  • ．CSM文件：用来保存表的状态及表中保存的数据量。
• 所有列必须强制指定NOT NULL。
• csv引擎不支持索引，不支持分区。

其它存储引擎

• archive存储引擎
  • 归档存储引擎，拥有很好的压缩机制zlib，用于压缩数据；
  • 压缩后的数据表仅支持插入和查询；
• blackhole存储红皮柳
  • 任何写入此引擎的数据均会被丢弃掉，不做实际存储；select语句的内容永远是空；
  • 常用来验证语法、检测负载、检测性能等操作；
• performance_schema存储引擎
  • 用于收集数据库服务器性能参数
  • 主要关注数据库运行过程中的性能相关的数据
• ARCHIVE在MySQL5.5以后支持索引。
• BLACKHOLE和Linux中的 /dev/null 文件完成的作用完全一致。
• BLACKHOLE虽然不存储数据，但是MySQL还是会正常的记录下Binlog，而且这些Binlog还会被正常的同步到Slave上，可以在Slave上对数据进行后续的处理。
  • 验证语法：验证dump file语法的正确性。
  • 检测负载：以使用blackhole引擎来检测binlog功能所需要的额外负载。
  • 检测性能：由于blackhole性能损耗极小，可以用来检测除了存储引擎这个功能点之外的其他功能点的性能。
• information_schema主要关注server运行过程中的元数据信息。