MySQL存储引擎

• 看你的mysql现在已提供什么存储引擎:

show engines;

• 看你的mysql当前默认的存储引擎:

show variables like ‘%storage_engine%’;

MyISAM

特点

• 并发性与锁级别-表级锁
• 支持全文检索
• 支持数据压缩

组成

.frm 文件是任何存储引擎都会有的文件，用来存储表结构
.MYD 是数据文件，存储表数据
.MYI 是索引文件，存储表索引
因为数据和索引是分开储存的，因此MyISAM的索引是非聚集索引（如果是存放在一个文件中，则为聚集索引）。

MySql 5.5之前默认的存储引擎
MyISAM 存储引擎由MYD和MYI组成

create table testmyisam (
id int PRIMARY key
) ENGINE=myisam ;
insert into testmyisam VALUES(1),(2),(3);

表压缩

myisampack -b -f /usr/local/mysql/data/mall/testmysam.MYI

压缩后再往表里面新增数据就新增不了（官方文档写的是新增不了，但实际上有的时候可以新增）

insert into testmysam VALUES(1),(2),(3)

压缩后，需要

myisamchk -r -f /usr/local/mysql/data/mall/testmysam.MYI

适用场景

• 非事务型应用（数据仓库，报表，日志数据）
• 只读类应用
• 空间类应用（空间函数，坐标）

由于现在innodb越来越强大，myisam已经停止维护
(绝大多数场景都不适合)

Innodb ☆

特点

• Innodb是一种事务性存储引擎
• 完全支持事务的ACID特性
• Redo Log 和 Undo Log
• Innodb支持行级锁（并发程度更高）

组成

.frm
.ibd

系统表空间和独立表空间

• 系统表空间无法简单的收缩文件大小
• 独立表空间可以通过optimize table 收缩系统文件
• 系统表空间会产生IO瓶颈
• 独立表空间可以同时向多个文件刷新数据

mysql5.6以前默认为系统表空间
建议：Innodb使用独立表空间（5.7默认）

show variables like ‘%innodb_file_per_table%’;
ON:独立的表空间：tablename.ibd
OFF:系统表空间：ibdataX

适用场景

Innodb适合于大多数OLTP应用。

OLTP，On-Line Transaction Processing，联机事务处理过程，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

对比MyISAM

CSV

特点

• 以csv格式进行数据存储
• 所有列都不能为null的（创建时要加not null）
• 不支持索引（不适合大表，不适合在线处理）
• 可以对数据文件直接编辑（保存文本文件内容）

组成

.csv 文件存储内容
.csm 文件存储表的元数据，如表状态和数据量
.frm 表结构

创建

create table mycsv(id int not null,c1 VARCHAR(10) not null,c2 char(10) not null) engine=csv;
insert into mycsv values(1,‘aaa’,‘bbb’),(2,‘cccc’,‘dddd’);

修改文本数据

vi /usr/local/mysql/data/mall/mycsv.CSV

刷新表

flush TABLES;
select * from mycsv;

create index idx_id on mycsv(id)#失败，不支持索引

Archive

特点

• 只支持insert和select操作
• 只允许在自增ID列上加索引
• 以zlib对表数据进行压缩，磁盘I/O更少

组成

.frm
.ARZ 存储数据

create table myarchive(id int auto_increment not null,c1 VARCHAR(10),c2 char(10), key(id)) engine = archive;
INSERT into myarchive(c1,c2) value(‘aa’,‘bb’),(‘cc’,‘dd’);
create index idx_c1 on myarchive(c1);#失败，只允许在自增ID列上加索引
delete from myarchive where id = 1;#失败，不支持delete
update myarchive set c1=‘aaa’ where id = 1;#失败，不支持update

使用场景

日志和数据采集应用

Memory

特点

• 文件系统存储特点
  也称HEAP存储引擎，所以数据保存在内存中
• 支持HASH索引和BTree索引
• 所有字段都是固定长度 varchar(10) = char(10)
• 不支持Blog和Text等大字段
• Memory存储引擎使用表级锁
• 最大大小由max_heap_table_size参数决定

查看最大大小

show VARIABLES like ‘max_heap_table_size’;#1M

create table mymemory(id int,c1 varchar(10),c2 char(10),c3 text) engine = memory;#失败，因为不支持大字段
create table mymemory(id int,c1 varchar(10),c2 char(10)) engine = memory;
create index idx_c1 on mymemory(c1);#HASH索引
create index idx_c2 using btree on mymemory(c2);#BTree索引
show index from mymemory;
show TABLE status LIKE ‘mymemory’;

对比临时表

临时表可以由自己或者系统创建。
系统在max_heap_table_size范围内会创建Memory表，超过了会创建MyISAM表。

使用场景

• hash索引用于查找或者是映射表（邮编和地区的对应表）
• 用于保存数据分析中产生的中间表
• 用于缓存周期性聚合数据的结果表

Ferderated

特点

• 提供了访问远程MySQL服务器上表的方法
• 本地不存储数据，数据全部放到远程服务器上
• 本地需要保存表结构和远程服务器的连接信息

使用场景

偶尔的统计分析及手工查询（某些游戏行业）

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

参考：Deer——mysql优化