MySQL常用存储引擎

MySQL常用存储引擎之MyISAM

MySQL 5.5 之前版本默认存储引擎，在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表。

MyISAM 存储引擎表由 MYD (表数据) 、MYI (表索引) 与 frm (表结构) 组成。

MyISAM 特性

• 并发性与锁级别

表级锁

• 表损坏修复

check table tableName
repair table tabelName

• MyISAM 表支持的索引类型

fulltext

• MyISAM表支持数据压缩

myisampack

压缩后的表无法进行写操作，只能进行读操作。

MyISAM限制

在 MySQL 版本小于 5.0 时，默认表大小为 4G，如存储大表则要修改表的 MAX_Rows 和 AVG_ROW_LENGTH 参数。

在MySQL版本大于5.0时，默认支持为 256TB。

MyISAM 适用场景

非事务型应用

只读类应用

空间类的应用

MySQL常用存储引擎之InnoDB

MySQL5.5 及之后版本默认存储引擎。

InnoDB 使用表空间进行数据存储

• 如果数据库的配置参数 innodb_file_pre_table => on，那么创建后的 InnoDB 引擎的表将产生 tableName.frm 和 tableName.ibd 文件。

• 如果数据库的配置参数 innodb_file_pre_table => off，那么创建后的  InnoDB 引擎的表将产生 ibdata*（共享表空间） 和 tableName.frm

那么，系统表空间和独立表空间要如何选择呢？

• innodb_file_pre_table => off 建立的表情况下，系统表空间无法简单的收缩系统文件大小，造成大量的空间浪费，并且会产生大量的磁盘碎片，从而降低系统的性能。

• 独立表空间可以通过 optimize table 命令收缩系统文件。

• 系统表空间会产生IO瓶颈

• 独立表空间可以同时向多个文件刷新数据

建议对 InnoDB 使用独立表空间，即 innodb_file_pre_table => on

InnoDB 特性

• InnoDB 是一种事务性存储引擎

• 完全支持事务的 ACID 特性

• InnoDB 支持行级锁

行级锁可以最大程度的支持并发

行级锁是有存储引擎层实现

• 锁的类型：

• 共享锁（也称读锁）

• 独占锁（也称写锁）

• InnoDB 状态检查

show engine innodb status

• 适用场景

InnoDB适用于大多数 OLTP 应用

MySQL常用存储引擎之CSV

文件系统存储特点

数据以文本方式存储在文件中，他们分别包含有如下文件：

.csv 文件存储表内容

.csm 文件存储标的元数据如表状态和数据量

.frm 文件存储表结构信息

CSV文件存储的特点

• 以 csv 格式进行数据存储

• 所有列必须都是不能为 null 的数据

• 不支持索引

• 可以对数据文件直接编辑

适用场景

适合作为数据交换的中间表

MySQL常用存储引擎之Archive

以 zlib 对表数据进行压缩，磁盘 I/O更少

数据存储在 tableName.ARZ 为后缀的文件中

Archive 文件存储特点

只支持 insert 和 select 操作

只允许在自增 ID 列上加索引

使用场景

日志和数据采集类应用

MySQL常用存储引擎之Memory

文件系统存储特点

也称 heap 存储引擎，所以数据保存在内存中。

功能特点

• 支持 hash 索引（等值查找快）和 Btree 索引（范围查找快）

• 所有字段都为固定长度，例如：varchar(10) = char(10)

• 不支持 BLOB/TEXT 等大字段

• Memory 存储引擎使用表级锁

• 最大大小由 max_heap_table_size 参数决定

使用场景

• 用于查找或者是映射表，例如：邮编和地区的对应表

• 用于保存数据分析中产生的中间表

• 用于缓存周期性聚合数据的结果表

Memory 数据易丢失，所以要求数据可再生。

MySQL常用存储引擎之Federated

默认禁止，启用需要在启动时增加 federated 参数。

mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

功能特点

• 提供了访问远程MySQL服务器上表的方法

• 本地不存储数据，数据全部放到远程服务器上

• 本地需要保存表结构和远程服务器的连接信息