mysql性能优化系列2-mysql引擎_总表可以映射的所有子表的总行数上限为 264 行-CSDN博客

本文链接：https://blog.csdn.net/huanshirenjian/article/details/90322374

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。
连接断开后，临时表定义和数据都清除，内存表还在。服务器重启后临时表定义和数据都清除，内存表定义还在，数据没了。