数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据,不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。简而言之,存储引擎就是指表的类型(即数据的存储类型),业务直接决定了使用的存储引擎。一个数据库管理业务支持多种存储引擎,MySQL的核心就是插件式存储引擎
存储引擎的选择:
不同的存储引擎,支持不同的功能,如下表:
执行命令:show engines; 来查看mysql的存储引擎:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- Memory :所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
- MySIAM: 拥有较高的插入,查询速度,但不支持事务,在MySQL2.1版本前是默认的存储引擎
- CSV: 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件,这是一种普通文本文件,每个数据行占用一个文本行,CSV存储引擎不支持索引
- Federated: 将不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。非常适合分布式应用
- InnoDB :事务型数据库的首选引擎,支持ACID事务,支持行级锁定,5.5版本后Mysql的默认数据库
- BlackHole :黑洞引擎,写入的任何数据都会消失,一般用于记录binlog做复制的中继
- Archive :非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度,但其对查询的支持相对较差
查看数据库的默认存储引擎:
SHOW VARIABLES LIKE 'storage_engine';
同一个数据库可以使用多种存储引擎的表。表要求比较高的事务处理,选择InnoDB存储引擎;数据库要求查询高的表选择MyISAM存储引擎;数据库需要一个用于查询的临时表,选择MEMORY存储引擎 ,下面将介绍这三种常用的存储引擎:
常用的三种存储引擎
InnoDB存储引擎
InnoDB存储引擎是事务型数据库的首选引擎,支持ACID事务,支持行级锁定和外键,是mysql默认的存储引擎
- InnoDB 给 MySQL 提供了具有事务、回滚和崩溃修复能力、多版本并发控制的事务安全型表;提供了行级锁,提供与 Oracle 类似的不加锁读取,InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。
- SQL查询中可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定,因为 InnoDB 的行级锁定适宜非常小的空间。
- InnoDB存储引擎中支持自动增长列AUTO_ INCREMENT。自动增长列的值不能为空,且值必须唯一。MySQL中规定自增列必须为主键。在插入值时,如果自动增长列不输入值,则插入的值为自动增长后的值;如果输入的值为0或空(NULL),则插入的值也为自动增长后的值;如果插入某个确定的值,且该值在前面没有出现过,则可以直接插入。
- InnoDB支持外键(FOREIGN KEY)。外键所在的表为子表,外键所依赖的表为父表,父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时,子表也必须有相应的改变。InnoDB 是 MySQL 上第一个提供外键约束的表引擎
- InnoDB存储引擎中,创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_ data_ home_ _dir 和innodb_ data_file_ path 定义的表空间中。
- InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点,是其读写效率稍差,占用的数据空间相对比较大。
- InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
MySIAM存储引擎
MyISAM基于ISAM存储引擎,并对其进行扩展,是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事物
- 使用MyISAM引擎创建数据库,每个MyISAM在磁盘上存储成三个文件,文件的名字以表名字开始,扩展名之处文件类型:.frm文件存储表定义; .MYD (MYData)文件存储表的数据; .MYI (MYIndex)文件存储表的索引
- 大文件(达63位文件长度)在支持大文件的文件系统和操作系统上被支持
- NULL被允许在索引的列中,这个值占每个键的0~1个字节
- 所有数字键值以高字节为先被存储以允许一个更高地索引压缩
- 所有数据值先存储低字节,这使得数据机和操作系统分离。先存储数据低字节并不严重地影响速度
- 每个MyISAM类型的表都有一个AUTOINCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTOINCREMENT列将被刷新。所以说,MyISAM类型表的AUTOINCREMENT列更新比InnoDB类型的更快
- 数据文件和索引文件可以放置在不同的目录,平均分配IO,获取更快的速度。要指定数据文件和索引文件的路径,需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定,文件路径需要使用绝对路径。
- 支持true VARCHAR类型;VARCHAR列以存储在2个字节中的长度来开始。有VARCHAR的表可以有固定或动态记录长度。VARCHAR和CHAR列可以多达64KB。
- MyISAM的表支持3种不同的存储格式:静态(固定长度)表,动态表,压缩表
MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问
- MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度
- MEMORY存储引擎执行HASH和BTREE缩影
- 可以在一个MEMORY表中有非唯一键值
- MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
- MEMORY表在所有客户端之间共享(就像其他任何非TEMPORARY表)
- MEMORY表数据被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享
- MEMORY表的大小是受到限制的,表的大小取决于两个参数,max_ rows和max_ heap_ table_ size,通过修改参数从而控制表的最大大小
- 基于MEMORY存储引擎的表实际对应一个磁盘文件,文件名与表名相同:.frm只存储表的结构
注:Memory表的所有数据是存储在内存上的,重启或者关机,表的所有数据都会消失,基于Memory存储引擎的表的生命周期很短,一般都是一次性的,因此,慎重选用