数据库存储引擎

MySQL整体架构

MySQL主要分为四层架构，分别是网络连接层，服务层，存储引擎层，物理层。

网络连接层

主要负责连接管理，授权认证，安全等。每个客户端连接都对应着服务器上的一个线程。服务器上维护一个线程池，避免为每个连接创建和销毁线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

服务层

第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构：

存储引擎层

负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。

物理层

将数据放在硬盘上

数据库存储引擎

查看存储引擎

show engines;

MyISAM存储引擎

MyISAM存储引擎是MySQL5.5之前的版本默认存储引擎
MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成

MyISAM存储引擎特性

1.并发性与锁级别
2.表损坏修复
3.MyISAM表支持数据压缩

myisampack -b -f myIsam.MYI

MyISAM存储引擎限制

版本 < MySQL5.0时默认表大小为4G,如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
版本 > MySQL5.0时默认支持256TB

MyISAM存储引擎适合场景

1.非事务型应用
2.只读类应用

Innodb存储引擎

MySQL5.5 及之后版本默认存储引擎
Innodb使用表空间进行数据存储

innodb_file_per_table

ON:独立表空间,tablename.ibd
OFF:系统表空间:ibdataX X是一个数字

系统表空间和独立表空间如何选择?

系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
独立表空间可以同时向多个文件刷新数据

Innodb存储引擎的特性

1、支持事务的ACID特性
2、Innodb支持行级锁,可以最大程度的支持并发

MyISAM和InnoDB对比

存储引擎	MyISAM	InnoDB
存储结构	每张表被存放在三个文件： 1、frm-表格定义 2、MYD(MYData)-数据文件 3、MYI(MYIndex)-索引文件	所有的表都保存在同一个数 据文件中（也可能是多个文 件，或者是独立的表空间文 件），InnoDB表的大小只 受限于操作系统文件的大 小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内 存和存储，它会在主内存中 建立其专用的缓冲池用于高 速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文 件的形式存储，所以在跨平 台的数据转移中会很方便。 在备份和恢复时可单独针对 某个表进行操作	免费的方案可以是拷贝数据 文件、备份 binlog，或者 用 mysqldump，在数据量 达到几十G的时候就相对痛 苦了
事务安全	不支持,每次查询具有原子性	支持,具有事务(commit)、 回滚(rollback)和崩溃修复 能力(crash recovery capabilities)的事务安全 (transaction-safe (ACID compliant))型表
AUTO_INCREMENT	MyISAM表可以和其他字段 一起建立联合索引	InnoDB中必须包含只有该 字段的索引
SELECT	MyISAM更优
INSERT		InnoDB更优
UPDATE		InnoDB更优
DELETE		InnoDB更优 它不会重新建 立表，而是一行一行的删除
COUNT without WHERE	MyISAM更优。因为 MyISAM保存了表的具体行 数	InnoDB没有保存表的具体 行数，需要逐行扫描统计， 就很慢了
COUNT with WHERE	一样	一样，InnoDB也会锁表
锁	只支持表锁	支持表锁、行锁 行锁大幅度 提高了多用户并发操作的新 能。但是InnoDB的行锁， 只是在WHERE的主键是有 效的，非主键的WHERE都 会锁全表的
外键	不支持	支持
FULLTEXT全文索引	支持	不支持 可以通过使用 Sphinx从InnoDB中获得全 文索引，会慢一点

CSV存储引擎

文件系统存储特点

数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息

特点

以CSV格式进行数据存储

使用场景

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

Memory存储引擎

也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来

功能特点

1、支持HASH索引和BTree索引
2、所有字段都为固定长度 varchar(10)=char(10)
3、不支持BLOB和TEXT等大字段
4、Memory存储引擎使用表级锁

适用类型

1、日志型应用
2、只读或者大部分情况下只读的表
3、订单处理

如何选择存储引擎

大部分情况下，InnoDB都是正确的选择，可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。

选择时参考条件

事务

如果应用需要事务支持，那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择

备份

如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择
InnoDB就是基本的要求

崩溃恢复

MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢