MySQL的存储引擎 | Erwin Feng Blog

MySQL在实际运行过程中根据不同的表类型有不同的存储引擎。在实际业务中，不同的场景需求要求表的设计和特性不同（比如高写入低读取）也会使用不同的储存引擎。本文总结MySQL的存储引擎及其对比和应用差别。

登录MySQL输入show engines。会列出如下表（只显示部分字段）：

+--------------------+---------+----------------------------------------------------------------+
| Engine             | Support | Comment                                                        |
+--------------------+---------+----------------------------------------------------------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) |
| MyISAM             | YES     | MyISAM storage engine                                          |
| CSV                | YES     | CSV storage engine                                             |
| ARCHIVE            | YES     | Archive storage engine                                         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             |
| FEDERATED          | NO      | Federated MySQL storage engine                                 |
+--------------------+---------+----------------------------------------------------------------+

常用的储存引擎有：InnoDB、MyISAM、MEMORY。

MyISAM引擎

采用表锁进行封锁。

原理

MyISAM引擎使用B+Tree作为索引，叶子结点的data区域存放数据记录的地址。这种数据和索引分离的索引方式叫做非聚集索引（非聚集索引）。在MyISAM中主索引和辅助索引都使用B+Tree叶子结点的data域保存数据记录的地址，结构上没有差别，但前者要求key是唯一的，后者可以重复key。二级索引本身也是指向记录的地址，和一级索引的差别就是它可以重复key值。

存储

MyISAM存储引擎下创建的一张表table，会在文件系统下生成三个文件：

table.frm 存储表的定义
table.MYD 存储数据
table.MYI 存储索引

可以理解为非聚集索引在文件系统上的表现（结果）。

下面我们来看看InnoDB。

InnoDB

InnoDB支持的索引包括：

B+书索引
全文索引
哈希索引

采用行锁进行封锁。

其中哈希索引是自适应的，不能人为改变，它会根据表的使用情况自动为表添加哈希索引。

本文只讨论InnoDB的B+树索引。

原理

InnoDB依然使用B+Tree作为索引结构，但有一个明显的区别：InnoDB存储的数据和索引内容位于同一个文件，称为聚集索引（聚簇索引），即找到索引就相当于找到数据。InnoDB表存储的数据都保存在B+Tree的叶子结点上，而MyISAM只保存数据的地址。聚簇索引的数据存储顺序，就是数据在硬盘上的物理顺序，因此之后找到需要的数据的第一位就可以沿着顺序查找其他连续索引的数据。

使用InnoDB的表需要定义主键，如果没有数据库会默认设置整型自增作为主键。这就是我们在使用MySQL经常遇到的。

InnoDB在索引上，辅助索引的叶子data域存储的是对应记录的主键。因此通过辅助索引查询需要两次检索：（1）通过辅助索引检索主键（2）用主键使用主索引检索需要的记录。

二级索引叶子结点保存主键值。这样，在使用二级索引进行查询时需要查找两次：（1）使用二级索引查找主键值（2）使用一级索引找到主键值所在的叶子结点，结点上保存了我们需要的数据记录。

另外，InnoDB的B+Tree结构叶子结点是有顺序指针，这样做的好处是提高区间访问性能。例如如下的SQL查询：

1	select * from exbooks where personid > 1 and personid < 10

那么只要找到personid=2的叶子结点，接下来就直接通过指针找到personid小于10的搜索叶子结点。（数据存储在叶子结点上，聚集索引）

有一个查找细节，InnoDB的B+树索引并不能根据键值定位表的具体行，只能定位要查找的行所在的页，然后InnoDB把该页读到内存中，然后顺序（二分）查找，直到找到具体的行为止。由于在内存中的线性查找时间远远小于磁盘I/O时间，因此它可以忽略不计。

事务和数据恢复

do
recover
undo

MEMORY

使用MEMORY可以使表直接建立在内存中，但由于内存在断电后数据会丢失，通常用于临时数据存储。由于表存储在内存中，对数据的操作省去了磁盘I/O，所以效率非常高。使用MEMORY引擎很方便，只需要在创建表是指定即可。下面创建的表使用MEMORY索引，是我在做网络爬虫时使用的。

CREATE TABLE `exbooks` (
    `idx` INT(11) NOT NULL AUTO_INCREMENT,
    `personid` VARCHAR(30) NULL DEFAULT NULL,
    `bookurl` VARCHAR(50) NULL DEFAULT NULL,
    `bookid` VARCHAR(20) NULL DEFAULT NULL,
    `bookname` VARCHAR(200) NULL DEFAULT NULL,
    `borrowtime` DATETIME NULL DEFAULT NULL,
    `returntime` DATETIME NULL DEFAULT NULL,
    `hash_` VARCHAR(40) NULL DEFAULT NULL,
    PRIMARY KEY (`idx`),
    UNIQUE INDEX `hash_` (`hash_`),
    INDEX `personid_index` (`personid`)
)
COLLATE='utf8_general_ci'
ENGINE=MEMORY
;

对比和使用场景

对比

InnoDB特点

（1）支持ACID特性的事务
（2）更细粒度的锁，行锁
（3）外键约束
（4）高并发下的update比MyISAM
（5）通过事务日志可以恢复故障下损失的数据

MyISAM特点

（1）支持全文索引
（2）大量写入的性能优于InnoDB
（3）查询过滤的性能优于InnoDB

MEMORY特点

（1）支持Hash索引和B树索引
（2）

使用场景

没有最好的编程语言，也没有最好的数据库，甚至没有最好的存储引擎。好不好？OK不OK看使用场景。

InnoDB的使用场景

（1）数据库需要外键
（2）业务需要事务支持
（3）大尺寸的数据集
（4）考虑到数据丢失的易恢复性

MyISAM的使用场景

（1）需要全文索引支持
（2）小型数据量但写入量大

MEMORY的使用场景

（1）小数据但需要频繁修改访问，且不怕丢失
（2）根据使用的索引方式有不同的差异：使用B树索引可以支持范围比较、不等比较，因为索引是树有序；但使用hash不能，后者这等值比较小了非常高。
（3）通常用在数据挖掘中

其他

MySQL还有其他不常用的存储引擎，包括ARCHIVE。它只支持出入和查询操作。

上述使用场景是前人的经验总结，经验虽好，但总有新现象出现打破原来的总结。保持好奇，保持开放。

转载请包括本文地址：https://allenwind.github.io/blog/3783/
更多文章请参考：https://allenwind.github.io/blog/archives/