常用MySQL存储引擎

常用MySQL存储引擎

1）概述：MySQL数据库支持多种存储引擎，用户可以通过选择使用不同的存储引擎提高应用的效率，提供存储的灵活性。当前使用的是MySQL 8.0，支持以下数据库引擎，其中InnoDB提供事务完全表，也是MySQL 8.0 的默认数据库存储引擎。

Engine	Support
MEMORY	YES
MRG_MYISAM	YES
CSV	YES
FEDERATED	NO
PERFORMANCE_SCHEMA	YES
MyISAM	YES
InnoDB	DEFAULT
BLACKHOLE	YES
ARCHIVE	YES

2）创建新表时，可以指定该表的数据库存储引擎。

​
create table newTable(
    i int(20) not null auto_increment,
    primary key(i)
)engine=MyISAm default charset = utf8;//创建新表直接指定表的存储引擎

​

当然 ，如果你创建后发现不是你想要的数据库存储引擎也可以更改。

alter table newTable engine = InnoDB;//将上面创建的表格的引擎个更换为InnoDB

3）常用存储引擎对比

特点	MyISAM	InnoDB	Memory	MerGE
存储限制	有	64TB	有	没有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁
B树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低
内存使用	低	高	中等	低
批量插入的速度	高	低	高	高
支持外键		支持

下面详细介绍上面四种数据库存储引擎：

1、MyISAM

MyISAM不支持事务，也不支持外键，它的优势在于访问速度快，对事务的完整性没有要求，因此，以select、insert命令为主的应用基本上都可以使用这个引擎来创建表。

每个MyISAM在磁盘上行存储成3个文件，每个文件的文件名都和表名相同，只是扩展名不同：

例如：user表

1）user.frm 存储表定义

2）user.MYD 存储数据

3）user.MYI 存储索引

数据文件和索引文件可以放在在不同的目录，平均分布IO，可以获得更快的速度。

MyISAM还可以支持3中不同的存储格式，分别是：

1）静态表（固定长度）

2）动态表

3）压缩表

其中，静态表是默认的存储格式。因为存储的字段都是定长的，所以这种存储方式的优点是：存储非常的迅速，容易缓存，出现故障容易恢复；缺点是：占用的空间通常比动态表多。

特别需要注意一点：由于静态表的数据存储时会按照列的宽度定义补足空格，但是在应用访问时并不会得到这些空格，因为这些空格在返回给应用之前就已经去掉了，所以，在保存数据时，如果本身数据尾部有空格，并且在默认的存储格式（静态表）下，就会造成尾部数据的丢失。

动态表中包含长字段，记录时不是固定长度的，这样存储的优点是：占用空间比较少；缺点是：如果频繁的更新和删除记录会产生碎片，需要定期的维护，使用OPTIMIZE TABLE语句 或者 myisamchk-r命令来改善性能，并且在出现故障时相对比较困难。

压缩表有myisampack工具创建，占据非常晓得磁盘空间。因为每个记录时被单独压缩的，所以开支非常小。

2、InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyIASM存储引擎，InnoDB的写的处理效率要差一点，并且会占用给更多的磁盘来存储数据和索引。

1）自动增长

InnoDB表的自动增长列可以手工插入，但是插入后的值如果是0或者null，那么真实插入的值将会是自动增长过后的值。

举个简单的栗子：

建一张表，存储引擎是InnoDB，设置i为主键且自增，还有一列是name。

create table increTable(
i int(20) not null auto_increment,
name varchar(20),
primary key(i)
)engine = InnoDB;

现在插入数据：

insert increTable values(1,"hhx"),(4,"kkj"),(0,"llo"),(null,"wq");

查询结果：

i	name
1	hhx
4	kkj
5	llo
6	wq

可以看到我们手动插入自增列后带来的影响，i 从1跳到4 ，这是手动添加的结果，后面的添加的i为0或者为空，就存入自动增长后的值。

这里还可以强制设置自增的初始值（默认从1开始），使用如下语句：

alter table increTable auto_increment = 5; //强制设置从5开始自增

插入值：

insert into increTable(name) values("hhx"),("gt"),("zhj"),("dqx");

查询结果：

i	name
5	hhx
6	gt
7	zhj
8	dqx

可以看出已经设置成功了。这里需要注意的是：这个强制设置的值是存储在内存里面的，如果数据库重启，那么这个值会丢失，需要重新设置。

另外，对于InnoDB表，自动增长列必须使索引。如果是组合索引，那么也必须是组合索引的第一列。但是对于MyISAM表来说，就没有这么严格了，自增列可以是组合索引的其他列。

2）外键约束

MySQL支持外键的存储引擎只用InnoDB，在创建外键时，要求父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。

当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。

关闭外键和恢复外键的命令：

set foreign_key_checks   =  0 ; //关闭外键

set foreign_key_checks   =  1 ; //开启外键

查看外键信息的命令：

show create  table;//第一种

show table status;//第二种

3）存储方式

InnoDB存储表和索引的方式有两种。

第一种：使用共享表空间存储，这种方式创建的表结构保存在.frm文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

第二种：使用多表空间存储，表结构仍然保存在.rem中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，则每个分区表对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区时指定每个分区的数据文件的位置，以此来讲表的IO均匀分布在多个磁盘上。

这里需要注意一点：即使是在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和在线重做日志放在这个文件中。

3、MEMORY

MEMORY存储引擎使用存在于内存中的内容来创建表。每个表只对应一个磁盘文件，格式为.frm。MEMORY类型的表将数据放在内存中，因此访问速度非常快，并且默认使用HASH索引，但是一旦关闭服务，表中的数据就会丢失掉。

MEMORY存储引擎下的表创建索引是，可以指定使用HASH索引还是BTREE索引。

可以使用下面的命令查看当前MEMROY存储引擎下的表是使用的什么索引。

show index from table;

另外，服务器需要足够的内存来位置所有在同一时间使用的MEMORY表，不在需要使用时，要释放表的内存，应该执行 delete from 或者truncate table，或者将整个表直接删除（drop table）。

每个MEMORY表中的存放的数据大小，收到max_heap_table_size系统变量的约束，初始值是16MB，但是可以加大。此外，定义该类型的表时，可以使用max_rows语句来指定表的最大行数。

需要注意的是：对MEMORY类型的表进行更新操作时，需要谨慎一点，因为数据没有写到磁盘里，所以要对下次重启服务后活的到这些修改后的数据有所考虑。

4、MERGE

MERGE存储引擎是一组MyISAM表的组合。这些MyISAM表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的查询、更新等操作是对内部的MyISAM表进行的。另外，当对MERGE表进行删除操作时，只是删除了MERGE表的额定义，对内部的表没有任何的影响。

MERGE表在磁盘上保留两个文件，一个是.frm文件存储表的定义；另外一个.MRG文件包含组合表的信息，其中包括了MERGE表由哪些表组成、插入新的数据时的依据，可以通过修改.MEG文件来修改MERGE表，修改后记得使用flush tables刷新表。