[大数据]MySQL中存储引擎之间的对比

目录

1. 什么是存储引擎

2. 常用的存储引擎

2.1? MyISAM 引擎：主要的非事务处理存储引擎

优点

缺点

MyISAM 数据文件结构

2.2 InnoDB 引擎：具备外键支持功能的事务存储引擎

优点

缺点

InnoDB的数据文件结构

3. MyISAM和InnoDB对比

4. 存储引擎的使用与查看

4.1?查看存储引擎

4.2? 设置系统默认的存储引擎

4.3?设置表的存储引擎?

1. 什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。我们将这些存储技术称之为存储引擎。

存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法

2. 常用的存储引擎

2.1? MyISAM 引擎：主要的非事务处理存储引擎

MySQL版本在?5.5之前默认的存储引擎。

优点

优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用

针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等

缺点

MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

MyISAM 数据文件结构

表名.frm 存储表结构

表名.MYD 存储数据 (MYData)

表名.MYI 存储索引 (MYIndex)

应用场景：只读应用或者以读为主的业务

2.2 InnoDB 引擎：具备外键支持功能的事务存储引擎

MySQL版本在5.5之后，默认采用InnoDB作为默认的存储引擎。

优点

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

innodb存储引擎的mysql表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

innodb支持自增长列（auto_increment）,自增长列的值不能为空

innodb存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。

innodb存储引擎最重要的是支持事务，以及事务相关联功能。

innodb存储引擎支持mvcc的行级锁。

innodb存储引擎索引使用的是B+Tree

InnoDB是为处理巨大数据量的最大性能设计。?

除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

缺点

对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
MyISAM只缓存索引，不缓存真实数据；

InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

InnoDB的数据文件结构

表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

表名.ibd 存储数据和索引

3. MyISAM和InnoDB对比

3.1 MyISAM 与 InnoDB两种引擎中索引的区别?

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。

① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

4. 存储引擎的使用与查看

4.1?查看存储引擎

show engines;
# 或
show engines\G

4.2? 设置系统默认的存储引擎

查看默认的存储引擎：

show variables like '%storage_engine%'; 
#或
SELECT @@default_storage_engine;

修改默认的存储引擎?

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf 文件：

default-storage-engine=MyISAM

重启服务

systemctl restart mysqld.service

4.3?设置表的存储引擎?

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

CREATE TABLE 表名(
    建表语句; 
) ENGINE = 存储引擎名称;

?修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

?如果本篇博客对您有一定的帮助，大家记得留言+点赞+收藏哦