MySQL中数据以各种不同的方式存储在文件或者内存中。这些不同的方式以及配套的相关功能被称作存储引擎或者表类型。
1. MyISAM
mysql5.5之前默认的存储引擎,它不支持事务,也不支持外键,但是访问速度快。只支持表级锁,支持全文索引。
(1)存储文件
以MyISAM为引擎的表在磁盘上存储成3个文件,扩展名分别为frm、MYD、MYI。
- .frm:存储表定义
- MYD:存储表数据
- MYI:存储索引
数据文件和索引文件可以放置在不同的目录,这样平均分配IO获取更快的速度。在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定文件路径。
(2)表修复
每个MyISAM表都有一个标志,这个标志用来表明该表在上次使用后是不是正常关闭。比如服务器宕机或崩溃,这个标志可以用来判断数据表是否需要检查和修复。可以在启动服务器时使用–myisam-recover,这样服务器在每次打开一个MyISAM数据表会自动检查数据表的标志并进行必要的修复处理。也可以使用CHECK TABLE主动检查MyISAM表,并用REPAIR TABLE修复一个损的MyISAM表。
(3)存储格式
MyISAM的表支持3种不同的存储格式:
- 静态(固定长度)表:该格式是默认的存储格式。静态表中的字段长度不可变,这样每个记录都是固定长度。优点是存储非常迅速,容易缓存,出现故障容易恢复,缺点是占用的空间通常比动态表多。静态表在数据存储时会根据字段定义长度定义补足空格,但是在访问的时候会去掉空格
- 动态表:字段长度可变,优点是占用空间较少,缺点是频繁更新删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能,并且出现故障的时候恢复相对比较困难
- 压缩表:由myisamchk工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支
(4)适用场景
- 查询密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速
- 插入密集型的表。MyISAM的并发插入特性允许同时查询和插入数据
- 非事务型应用(数据仓库,报表,日志数据)
- 空间类应用(空间函数,坐标)
2. Innodb
mysql5.5以及之后默认的存储引擎,InnoDB支持事务、行级锁、外键约束。
(1)共享空间和独立空间
Innodb存储引擎可将所有表数据存放于ibdata*的共享表空间,也可将每张表存放于独立表空间。
- 共享表空间:数据库所有表数据和索引文件全部放在一个文件中,默认文件存储在data目录下,文件名为ibdata1,初始化10M。可以在配置文件my.cnf中设置参数innodb_data_file_path定义一个或者多个文件组成表空间。共享表空间中包含Undo信息,事务rollback时Undo信息不会被删除,但是此空间会被标记,后续会以覆盖的方式重新使用
- 独立表空间:每一个表都会生成独立的文件存储数据,每一个表都会存储两个文件:.frm文件(表定义)和.ibd文件(数据、索引、插入缓存的内容)。 可以在配置文件my.cnf中设置参数innodb_file_per_table = ON来开启独立表空间
共享表空间和独立表空间对比:
- 共享表空间:优点是表空间可以分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。数据和文件放在一起方便管理。缺点是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,对于统计分析,日值系统类应用不适合用共享表空间。数据都在一个或多个文件有IO瓶颈。
- 独立表空间:优点是每个表都有独立的表空间。每个表的数据和索引都会存在自已的表空间中。可以实现单表在不同的数据库中移动,而且空间可以回收(对于drop table操作,表可以自动回收表空间。如果使用innodb-plugin对于turncate table也会回收空间。对于大量数据的删除,可以通过alter table TableName engine=innodb回收不用的空间)。独立表空间的表,表空间的碎片不会太严重的影响性能,而且碎片也可处理。由于每个表一个空间,有利于并发读写。缺点是单表文件会增长过大。
(2)适用场景
- 对表更新频繁
- 需要事务
- 自动灾难恢复。InnoDB表能够自动从灾难中恢复
- 外键约束。MySQL支持外键的存储引擎只有InnoDB
- 需要列支持AUTO_INCREMENT
(3)MyISAM和Innodb对比
- MyISAM:不支持主外键、不支持事务、表级锁、只缓存索引、表空间小
- Innodb:支持主外键、支持事务、行级锁、缓存索引和数据、表空间大
3. CSV
该存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。
(1)存储文件
- CSV:存储表内容
- CSM:存储表的元数据如表状态和数据量
- frm:存储表定义
(2)特点
- 以CSV格式进行数据存储
- 所有的列必须都是不能为NULL的
- 不支持索引
- 可以对数据文件直接编辑
4. Archive
MySQL5.5版以前,Archive不支持索引,MySQL5.5以后开始支持索引,只允许在自增ID列上加索引。
(1)存储文件
- frm:存储表定义
- arz:存储数据
(2)特点
- 向archive表插入的数据会经过压缩,使用zlib进行数据压缩,支持optimize table、 check table操作
- 只支持insert和select操作,支持行级锁和缓冲区,可以实现高并发的插入
- Archive表比MyISAM表要小大约75%,比InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM好。
5. MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同。Merge表就是几个相同MyISAM表的聚合器,Merge表中并没有数据,对Merge类型的表进行查询、更新、删除操作,实际上是对内部的MyISAM表进行操作。
- 总表和子表的主键都不能使用自动增长
- 子表之间不能保证唯一键约束,只能保证单个子表内部的唯一性约束
- 子表不支持分区
- 使用总表时,不能对任何子表执行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE语句
- 总表和子表的表结构必须完全一致
- 总表可以映射的所有子表的总行数上限为 264 行
- 不支持INSERT DELAYED
6. Memory
MEMORY引擎使用存储在内存中的内容来创建表。MEMORY引擎只有一个.frm磁盘文件,保存文件定义,数据全部放在内存中。
MEMORY引擎支持HASH索引和BTree索引。默认使用哈希索引。速度比使用B型树索引快,不支持Blog和Text等大字段,使用表级锁,最大大小由max_heap_table_size参数决定。表的字段长度是定长。
(1)Memory和系统临时表区别
缺省存储引擎为MySQL服务器默认引擎,引擎类型只能是:memory(memory临时表由于表的增大可能会转变为myisam临时表)、myisam、merge、innodb。
连接断开后,临时表定义和数据都清除,内存表还在。服务器重启后临时表定义和数据都清除,内存表定义还在,数据没了。
7. Ferderated
Ferderated提供了访问远程MySQL服务器上表的方法,本地不存储数据,数据全部放到远程服务器上,本地需要保存表结构和远程服务器的连接信息。