04-MySql 存储引擎-CSDN博客

本文链接：https://blog.csdn.net/my_momo_csdn/article/details/92795150

文章目录

Mysql存储引擎

Mysql存储引擎

MySQL的存储引擎的插拔式的，用户可以自己选择使用哪一种MySql提供的存储引擎，甚至开发一个自己的存储引擎(MySql是开源的)，
或者一些第三方提供的存储引擎(非MySql内置的)
存储引擎是和表绑定的，也就是说对于不同的表我们可以选择不同的存储引擎

一、存储引擎相关操作

show engines：查看数据库支持的引擎
show variables like %storage_engine% :查看默认的存储引擎
alter table mytbl engine = InnoDB; 修改数据库的存储引擎

//如果修改比较慢，可以创建一个新表，再将数据移过去
create table newtbl like oldtbl;
alter table newtbl engine=innodb;
insert into newtbl select * from oldtbl;

二、MyISAM和InnoDB

MyISAM和InnoDB是mysql最主流的2种存储引擎

2.1 MyISAM

Mysql 5.5之前默认的存储引擎。MyISAM设计简单，数据以紧密格式存储，在某些场景下性能很好，它最典型的问题还是表锁。特点如下：

    不支持行级锁，只支持表级锁，并发特性差
    不支持事务，无法回滚，崩溃后无法安全恢复。
    支持全文检索、数据压缩（myisampack -b -f testmyisam.MYI）、空间函数等
    数据和索引分开存储

适应场景：

    非事物型应用(数据仓库，报表，日志数据)
    只读类应用
    空间类应用（空间函数，坐标）

2.2 InnoDB

Mysql 5.5之后默认的存储引擎，也是使用频率最高的MySql存储引擎。特点如下：

    支持事物，事物型存储引擎，完全支持事物的ACID特性。采用MVCC（多版本并发控制）来支持高并发，实现了四个ANSI标准的隔离级别，
    默认为Repeatable Read，并且通过间隙锁(next-key lock)防止幻读。包括RedoLog和Undo Log(和事物相关)
    支持行级锁，并发特性更高
    基于聚簇索引，对主键查询有很高的性能，但它的二级索引必须包含主键列。
    ON：独立表空间：tablename.ibd(这个文件存储了数据和索引)
    OFF：系统表空间：ibdataX 
    数据和索引保存在一个文件
    支持独立表空间，通过innodb_file_per_table配置开启，并且在5.6以前默认是系统表空间，

适用场景

适用于大多数OLTP应用(联机事物处理)

2.3 表空间

MySQL的Innodb包含两种表空间文件模式，共享表空间和独立表空间，共享表空间也叫系统表空间。一般当数据量很小的时候建议使用共享表空间的管理方式。数据量很大的时
候建议使用独立表空间的管理方式。
查看InnoDB下独立表空间是否开启(ON表示开启)：

show variables like '%innodb_file_per_table%';(5.6以前默认是系统表空间)

2.3.1 共享表空间

Innodb的所有数据保存在一个单独的表空间里面，这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，因此表的大小限制不再是文件大小的限制，而是表空间的限制。
Innodb的官方文档中表示，表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，这个大小是包括这个表的所有索引等其他相关数据。
优点：表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制
缺点：所有的数据和索引存放到一个文件导致多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分
析，对于经常删除操作的这类应用最不适合用共享表空间。
共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，即使删除相关的表也没办法回缩那部分空间了，进行数据库的冷备很慢；

2.3.2 独立表空间

独立表空间是每个表都有独立的多个数据文件，而且做到了索引和数据的分离。Innodb建议使用独立表空间
优点：每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。
缺点：文件的利用率不高

2.4 MyISAM和InnoDB对比

引擎	MyISAM	Innodb
主外键	不支持	支持
事物	不支持	支持
锁	表级锁，不适合高并发	行级锁，适合高并发
缓存	只缓存索引	缓存索引和真实数据
表空间	小	大
关注点	性能	事物

三、其他存储引擎

3.1 Archive

是一个针对高速插入和压缩做了优化的简单引擎，只支持insert和select操作，每次select都是全表扫描，适合日志和数据采集应用。
我在测试的时候，首先创建了一个简单的表，使用存储过程插入10W条记录，使用Archive发现插入过程在1S内完成，检查数据文件发现只有600K左右。
而使用InnoDB插入过程需要十多分钟，数据文件也在14MB左右，修改为MyIsam引擎之后数据文件在4MB左右，索引文件在1MB多一点。由此可以看到Archive
的优点，比较适合数据采集，比如日志存储，插入速度很快，存储空间很小，适合存储数据量大，写入密度大的数据，而且不支持修改和删除。

而且发现，在创建表的时候指定使用Archive存储引擎，插入数据之后可以建存储引擎修改为MyISAM或者InnoDB，改了之后再改回Archive发现就会
提示：Can’t write; duplicate key in table ‘#sql-1814_18’。

alter table mytbl engine = InnoDB;

3.2 CSV

3.2.1 特点

以CSV格式存储数据
不支持索引
所有列不能为NULL
数据文件可以直接编辑

3.2.2 使用场景

适合做数据交换中间表。比如将excel的数据存储为csv格式文件，然后复制到MySQL的数据目录下，就能在MySQL中以表的方式打开，
因此CSV引擎作为一种数据交换机制，非常有用。

3.3 Memory

所有的数据都保持在内存中，重启后数据会丢失。Memory表是表级锁，因此写性能较低，但它的查询性能非常高，支持Hash索引，可以指定一个文件在mysql启动时从文件加载数据。
使用不多可以参阅mysql之memory存储引擎

3.4 第三方存储引擎

前面的都是mysql内建的存储引擎(还有部分未介绍完全)，可以参阅：MySql存储引擎特性对比
第三方存储引擎不是mysql内建的存储引擎
XtraDB、PBXT、TokuDB、RethinkDB和Infobright

四、存储引擎和文件结构

4.1 表结构

不管使用什么存储引擎，创建一个表都会有一个表名.frm文件，是表定义文件。

存储引擎	文件
MyIsam	表定义文件： xx.frm ，数据文件：xx.MYD，索引文件：xx.MYI
InnoDB	表定义文件： xx.frm ，xx.ibd文件(含数据和索引)
CSV	表定义文件： xx.frm ，数据文件：xx.CSV，表元数据：xx.CSM