MySQL存储引擎（InnoDB、MyISAM）

转载：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

一个数据库中多个表，可以使用不同引擎，以满足各种性能和实际需求。

MySQL服务器使用 可插拔的存储引擎 体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

1 如何查看、设置MySQL的存储引擎

下面的命令可以用于 查看存储引擎：

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

-- 查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

-- 准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

下面的命令可以 设置存储引擎：

-- 建表时通过ENGINE指定存储引擎。默认的就是InnoDB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MYISAM;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表。

2 MySQL存储引擎对比（MyISAM、InnoDB）

常见的存储引擎有：InnoDB、MyISAM、Memory。

InnoDB 现在是 MySQL 默认的存储引擎，支持 事务、行级锁定、外键。

InnoDB 和 MyISAM 的主要区别！

主要区别：

• InnoDB 支持事务，MyISAM 不支持事务。 这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
• InnoDB 支持外键，MyISAM 不支持。 对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
• InnoDB 是聚簇索引，MyISAM 是非聚簇索引。 聚簇索引的数据存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，索引保存的是数据的指针，需要再次寻址才能拿到数据。主键索引和辅助索引是独立的。
• InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
• InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。 MyISAM 的一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

对比项	MyISAM	InnoDB
主外键	No	Yes
事务	No	Yes
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作。	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作。
缓存	只缓存索引，不缓存真实数据。	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
表空间	小	大
关注点	性能	事务
默认安装	是	是

3 InnoDB的索引和MyISAM的索引的区别

区别：

• InnoDB 使用的是聚簇索引，MyISAM使用的是非聚簇索引。
• InnoDB的主键索引的叶子结点存储着行数据，因此主键索引非常高效。
• InnoDB非主键索引的叶子结点存储的是逐渐和其他带索引的列数据，因此查询的时候能够做到覆盖索引的话，效率非常高。
• MyISAM的索引的叶子结点存储的是行数据的地址，需要再次寻址才能得到真实的数据。

4 聚簇索引

聚簇索引：将数据与索引放到了一块，找到索引也就找到了数据。

非聚簇索引：将数据和索引分开存储，索引结构的叶子节点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据。

澄清一个概念：InnoDB中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。非聚簇索引都是辅助索引。像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。