存储引擎
就是存储数据、为存储的数据建立索引和更新、查询数据等技术的实现方法。因为在关系数据库中,数据是以表的形式存储的,所以存储引擎简而言之就是指表的类型,数据库的存储引擎决定了表在计算机中的存储方式。在Oracle和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据需要编写自己的存储引擎,MySQL的核心就是存储引擎。
# 查看系统支持的存储引擎类型
SHOW ENGINES;
MySQL 5.7支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV和BLACKHOLE等。
InnoDB存储引擎
InnoDB
是事务型数据库的首选引擎,是具有提交、回滚和崩溃恢复能力的事务安全存储引擎,支持行锁定和外键约束。从MySQL 5.5.5之后,InnoDB作为默认存储引擎。相对MySQL来说,其写处理能力差些,且会占用较多磁盘空间,以保留数据和索引。InnoDB的主要特性如下:
-
支持自动增长列。存储表中的数据时,每张表的存储都按主键顺序存放。如果在定义表时没有指定主键,则InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。此ROWID由自动增长列的值填充。InnoDB存储引擎支持自动增长列AUTO-INCREMENT。自动增长列的值不能为空,且值必须唯一。若插入的值为0或为空,则实际插入的值为自动增长后的值。可通过ALTER TABLE语句强制设置自动增长列的初始值,默认从1开始。
-
支持外键约束(FOREIGN KEY)。只有InnoDB存储引擎支持外键约束。外键所在表为子表,外键依赖的表为父表。表中被子表外键关联的字段必须是主键。当删除、更新父表的某条记录时,子表也必须有相应的改变。创建索引时,可指定删除、更新父表时对子表的相应操作。
-
存储格式。InnoDB存储表和索引有下面两种方式:
-
使用共享表空间存储。表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以为多个文件。
-
使用多表空间存储。表结构仍然存储在.frm文件中,但每个表的数据和索引单独保存在.idb中。若为分区表,则每个分区对应单独的.idb文件,文件名为表名+分区名。使用多表空间存储,需设置参数innodb_file_per_table,并重启服务才可生效,只对新建表有效。
-
MyISAM存储引擎
MyISAM存储引擎
是MySQL中常见的存储引擎,曾是MySQL的默认存储引擎,不支持事务、外键约束,但访问速度快,对事务完整性不要求,适合于以SELECT/INSERT为主的表。
-
存储文件。每个MyISAM物理上存储为3个文件,文件名与表名相同,扩展名分别为.frm、.MYD、.MYI,其中数据文件和索引文件可以放置在不同目录,以达到平衡I/O的目的。
-
存储格式。MyISAM类型的表支持以下3种存储格式:
-
静态表。默认存储格式,字段长度固定,存储迅速,容易缓存。缺点是占用空间多。需要注意的是,字段存储按照宽度定义补足空格,应用访问时去掉空格;若字段本身就带有空格,也会去掉。
-
动态表。变长字段,记录不是固定长度,优点是占用空间少,但频繁地更新、删除操作会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善,出现故障时难以恢复。
-
压缩表。由myisampack工具创建,每个记录的单独压缩,访问开支小,占用空间小。
-
MEMORY存储引擎
MEMORY存储引擎
是MySQL中一类特殊的存储引擎。该存储引擎使用存在于内存中的内容来创建表,每个表实际对应一个磁盘文件,格式为.frm。这类表因为数据在内存中,且默认使用HASH索引,所以访问速度非常快;但一旦服务关闭,表中的数据就会丢失。每个MEMORY表可以放置数据量的大小受max_heap_table_size系统变量的约束,初始值为16MB,可按需求增大。此外,在定义MEMORY时,可通过MAX_ROWS子句定义表的最大行数。
该存储引擎主要用于那些内容稳定的表,或者作为统计操作的中间表。对于该类表需要注意的是,因为数据并没有实际写入磁盘,所以一旦重启,则会丢失。
存储引擎的选择
不同存储引擎都有各自的特点,以适应不同的需求。
功能 | InnoDB | MyISAM | Memory |
---|---|---|---|
存储限制 | 64TB | 256TB | RAM |
支持事务 | 支持 | 无 | 无 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
支持数据缓存 | 支持 | 无 | 无 |
插入数据速度 | 低 | 高 | 高 |
支持外键 | 支持 | 无 | 无 |
如果要提供提交、回滚的崩溃恢复能力的事务安全能力,并要求实现并发控制,则InnoDB是一个很好的选择。如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率。如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,则可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果。
(最近更新:2019年09月03日)