深入剖析MySQL数据库架构：核心组件、存储引擎与优化策略（三）

存储引擎(9--5版本，11--8版本)

存储引擎Innodb

InnoDB支持事务、分布式（并发）

使用InnoDB时，会将数据表分为.frm 和 idb两个文件进行存储。

InnoDB采用MVCC(多版本并发控制)来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，并通过间隙锁策略防止幻读的出现。

它的锁粒度（表锁--力度大，只能一个人操作这张表）是行锁（一个数据）。

InnoDB是典型的事务型存储引擎，并且通过一些机制和工具，支持真正的热备份。

InnoDB表是基于聚簇索引建立的，聚簇索引对主键的查询有很高的性能，不过他的二级索引（非主键索引）必须包含主键列。

Mysql5.6版本之前默认是系统表空间，系统表空间不能收缩系统文件，浪费空间。5.6版本（包括5.6版本）后使用独立表空间，可以收缩文件，节省空间。

如果想收缩表空间的大小，可以使用语句去执行。

OPTIMIZE TABLE 表名

存储引擎MyISAM

MyISAM是5.5版本之前mysql数据库默认的存储引擎。

可以使用如下的sql语句创建数据库，采用MyISAM存储引擎。

CREATE TABLE `myisam_demo`( `id` varchar(255) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中

Frm    所有存储引擎都有的文件，存储表结构的文件

MYD    存储数据的文件

MYIs   存储索引的文件

MyISAM是基于非聚集索引进行存储的。

MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。

MyISAM不支持事务。

MyISAM提供了大量的特性，包括全文索引、压缩等。

进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。

全文索引，是一种基于分词创建的索引，可以支持复杂的查询。

存储引擎ARCHIVE

翻译为： 档案; 档案馆; 档案室

组成：以zlib对表数据进行压缩，磁盘I/O更少，数据存储在ARZ为后缀的文件中。

特点:

只支持insert和select操作，只允许在自增ID列上加索引。

Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。

较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你需要把数据从一台MySQL服务器转移到另一台的时候，Archive表可以方便地移植到新的MySQL环境，你只需将保存Archive表的底层文件复制过去就可以了

应用场景：历史数据，数据采集应用。

存储的文件

创建表的sql语句如下

CREATE TABLE `t_stu_archive` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `username` varchar(10) DEFAULT NULL,   KEY `id` (`id`) ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8;

增删改查数据测试。

-- 新增 insert into t_stu_archive values (null,"赵四"); -- 查询 select * from t_stu_archive -- 修改 update t_stu_archive set username = '张三' where id = 1 -- 删除 delete from t_stu_archive

新增测试

查询测试

修改测试

删除测试

存储引擎MEMORY

文件系统存储特点，也称HEAP存储引擎，所有的数据保存在内存中。

特点：

支持HASH索引和BTree索引

所有字段都是固定长度varchar(10) = char(10)

不支持Blog和Text等大字段

Memory存储引擎使用表级锁

最大大小由max_ heap_ _table_ size 参数决定

建表的语句

CREATE TABLE `t_stu_memory` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `username` varchar(20) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8;

使用场景：

hash索引用于查找或者是映射表(邮编和地区的对应表)

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

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

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