MySQL存储引擎

介绍

MySQL的核心架构大致如下。官方的图：

以JDBC客户端为例画个草图：

MySQL的存储引擎有很多，我们可以通过SQL查询，查看自己当前版本的MySQL支持的所有存储引擎。

SHOW ENGINES;

我之前安装在服务器上的MySQL版本为：5.7.x

以MySQL5.7版本简单介绍一下常用的几种存储引擎。

MyISAM

在MySQL 5.5版本之前，MySQL的默认存储引擎。是ISAM的改良版。

特点：

1. 存储

   使用MyISAM存储的表，有三个文件：
   

   • .frm

     表信息文件

   • .MYD

     表中的数据文件

   • .MYI

     表中的索引文件

2. 读取速度快，性能极佳

   紧密存储，顺序读性能很好

3. 不支持事务、外键

   数据是无法进行回滚

4. 支持全文检索

   即使是text，blob等长字段，也可以基于前500字创建索引

5. 支持数据压缩

   减少磁盘的占用

6. 表级锁

   直接锁定整张表，混合读写性能较差

7. 使用B+Tree索引

   非聚簇索引。保存的是真实数据的地址(缓存只能缓存索引)。引用百度图库中的图(偷懒就不画了)：

   

适用：

1. 无需支持事务的业务
2. 以读为主的业务
3. 空间计算
4. 全文检索

InnoDB

在MySQL 5.5.8以后，MySQL默认存储引擎。

5.7版本以后，支持全文检索，空间函数

特点：

1. 采用表空间保存文件：

   • 使用系统表空间

     较早版本时，InnoDB默认将所有表的数据保存至data目录下的ibdata1文件中。当ibdata1空间占满以后，则存储于文件ibdata2中，自动增长。

     

   • 独立表空间

     后续版本中，每个表的数据则独立保存在data/dbname/tablename.ibd文件中。

     以test_db数据库中的表t_test_innodb1为例：

     

     .frm文件则为表信息文件

   两种模式可以切换，推荐使用独立表空间。

   如果使用系统表空间，执行如下SQL：

   SET GLOBAL innodb_file_per_table=OFF;

   现版本查看当前值为ON，是默认使用的独立表空间：

   SHOW VARIABLES LIKE 'innodb_file_per_table';

   当然，上面的设置并不是永久生效，需要永久生效，需在my.cnf配置文件中添加。

   ---

   为什么推荐使用独立表空间？

   使用系统表空间，所有使用InnoDB引擎的表数据都存储于一个文件中，管理困难，回收存储空间困难，并会有IO瓶颈。

   ---

2. 支持事务特性

   事务的ACID特性就不说了，对事务可以进行提交、回滚操作。

3. 支持外键约束

   外键约束：当某个表的主键被其他表创建了外键参照，则该表对应的主键索引或主键禁止被删除。

   实际工作中，只是做逻辑意义上的外键，外键约束增加了表与表之间的耦合度，降低了查询速度。

4. 自增长列

   MySQL中设置自动增长只需在定义的字段后面添加auto_increment。在Oracle中，需要使用序列才行。

5. 默认使用行级锁

   只针对表中的某一行数据进行加锁，对高并发有很好的适应能力，但需要确保查询是通过索引完成。即：只有利用索引的更新，删除，才可以使用行级锁。而未使用索引的写操作，使用的是表锁。

6. 存在缓存池，缓存特性

   可以将数据，索引进行缓存。

7. 使用B+Tree索引

   聚簇索引。创建的索引就是数据，顺序存储。引用百度图库中的图：

   

   这也是主键推荐使用自增长的原因。使用了自增长，新增的数据永远是最大的，这样一来，树的平衡，数据插入改动相对较小。

适用：

1. 需要事务支持的业务
2. 数据更新较高的业务
3. 数据一致性要去高

MEMORY

将数据存储于内存中。访问速度极快(比MyISAM高一个量级)。数据库出现故障，数据会丢失。磁盘中仅仅保留的表信息文件。

特点：

1. 默认使用hash索引

2. 表级锁

3. 支持的字段类型有限

   不支持text、blob。仅支持固定长度的行，varchar会自动存储为char类型

4. 不支持事务

适用：

1. 读多写少的业务
2. 充当缓存
3. 系统临时表

---

设置内存表大小：max_heap_table_size，单位字节。

设置内存临时表最大值：tmp_table_size，单位字节。

---

BLACKHOLE

写入的任何数据都会消失，用于记录binlog做复制的中继

ARCHIVE

Archive引擎会缓存写操作，利用zlib对写入进行压缩。适合存储大量的，独立的历史记录数据。插入速度快，查询相对较差。

CSV

数据存储于CSV文件中(固定的文件格式，字段用,分割)。同样适用Excel创建数据另存于CSV也可以在MySQL中打开使用。常用于数据迁移，数据交换。

MRG_MYISAM

该引擎的表由多个MyISAM引擎的表合并起来的虚拟表，用于超大规模数据存储。

FEDERATED

将不同的MySQL服务器联合起来，逻辑组成一个完整数据库，用于分布式应用。

附

MySQL支持同一个库的不同的表使用不同的存储引擎，这样一来，我们可以根据实际业务需要，对表创建不同的存储引擎。

参考：
MySQL之存储引擎
MySQL各种存储引擎对比总结