MySQL -- 存储引擎

介绍

“存储引擎”从字面理解，“存储”的意思的存储数据。“引擎”一词来源于发动机，它是发动机中的核心部分。在软件工程领域，相似的称呼有“游戏引擎”、“搜索引擎”，它们都是相应程序或系统的核心组件。所以从这里可以看出“存储引擎”似乎也是数据库的核心。

存储引擎是MySQL有别于其他数据库管理系统的最大特色，也是作为MySQL核心的存在。存储引擎的作用就是存储数据，提供读写接口(规定了数据存储时的存储结构)。

MySQL为什么要出现存储引擎?

由于不同业务产生了不同的数据，这些数据有的可能偏重于被频繁查询，有的要求增删速度快，有的则对事务、索引、外键有特殊规定。所以就需要使用不同的数据组织结构来将数据存储于表中，也就是需要使用不同的存储引擎。简单来说，就是业务直接决定了存储引擎。

MySQL存储引擎有哪些？

我们可以使用 SHOW ENGINES 命令来查看 MySQL 数据库支持使用的存储引擎，如下图所示：

 

介绍常见的三种数据库引擎:

（1）InnoDB存储引擎

　　MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。--《高性能MySQL》

补充：InnoDB的自动崩溃恢复的特性跟它内部的重做日志redo log有关，详细介绍请查看我的另一篇博文——重要的日志模块--redolog和binlog。

（2）MyISAM存储引擎

　　在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。尽管这样，它并不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM（但请不要默认使用MyISAM，而是应该默认使用InnoDB）--《高性能MySQL》

（3）MEMORY存储引擎

　　MEMORY 是内存型的数据库引擎，它会将表中的数据存储到内存中，因为它是内存级的数据引擎，因此具备最快速的查询效率，但它的缺点是，重启数据库之后，所有数据都会丢失，因为这些数据是存放在内存中的。

如何选择数据库的引擎？

选择数据库引擎要从实际的业务情况入手，不同业务产生了不同的数据，比如是否需要支持事务？是否需要支持外键？是否需要支持持久化？以及是否支持地理位置存储以及索引等方面进行综合考量。

最常用的数据库引擎是 InnoDB，它是 MySQL 5.5.5 之后的默认引擎，其优点是支持事务，且支持 4 种隔离级别(读未提交,读已提交,可重复读,串行化)。InnoDB 还支持外键、崩溃后的快速恢复、支持全文检索（需要 5.6.4+ 版本）、集群索引，以及地理位置类型的存储和索引等功能。

MyISAM 引擎是 MySQL 原生的引擎，但它并不支持事务功能，这也是后来被 InnoDB 替代为默认引擎的主要原因。MyISAM 有独立的索引文件，因此在读取数据方面的性能很高，它也支持全文索引、地理位置存储和索引等功能，但不支持外键。

InnoDB 和 MyISAM 都支持持久化，但 MEMORY 引擎是将数据直接存储在内存中了，因此在重启服务之后数据就会丢失，但它带来的优点是执行速度很快，可以作为临时表来使用。

我们可以根据实际的情况设置相关的数据库引擎，还可以针对不同的表设置不同的数据引擎，只需要在创建表的时候指定 engine=引擎名称即可，SQL 代码如下：

create table student(
   id int primary key auto_increment,
   uname varchar(60),
   age int
) engine=Memory;

补充说明

读未提交：也就是一个事务还没有提交时，它做的变更就能被其他事务看到。
读已提交：指的是一个事务只有提交了之后，其他事务才能看得到它的变更。
可重复读：此方式为默认的隔离级别，它是指一个事务在执行过程中（从开始到结束）看到的数据都是一致的，在这个过程中未提交的变更对其他事务也是不可见的。
串行化：是指对同一行记录的读、写都会添加读锁和写锁，后面访问的事务必须等前一个事务执行完成之后才能继续执行，所以这种事务的执行效率很低。

InnoDB 自增主键

在面试的过程中我们经常看到这样一道面试题：

在一个自增表里面一共有 5 条数据，id 从 1 到 5，删除了最后两条数据，也就是 id 为 4 和 5 的数据，之后重启的 MySQL 服务器，又新增了一条数据，请问新增的数据 id 为几？

我们通常的答案是如果表为 MyISAM 引擎，那么 id 就是 6，如果是 InnoDB 那么 id 就是 4。

但是这个情况在高版本的 InnoDB 中，也就是 MySQL 8.0 之后就不准确了，它的 id 就不是 4 了，而是 6 了。因为在 MySQL 8.0 之后 InnoDB 会把索引持久化到日志中，重启服务之后自增索引是不会丢失的，因此答案是 6，这个需要面试者注意一下。

常见面试题

(1)MySQL存储引擎有哪些？

(2)如何选择数据库的引擎？

(3)InnoDB 自增主键