MySQL 高级 —— 深入理解 InnoDB 与 MyISAM

柔情只为你懂 2023-07-12 05:10 122阅读 0赞

# 引言 #

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个**与表同名的.frm文件保存表的定义**。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，在Windows中，大小写不是敏感的；而在Linux系统中，则大小写敏感。不同的存储引擎保存数据和索引的方式是不同的，但**表的定义则是在MySQL服务器层统一处理的。**

InnoDB作为事务型数据的首选存储引擎，是中高级程序员必须掌握的知识，与之经常一同提起的MyISAM，也是在应用场景中频繁会接触的典型存储引擎。

在《高性能MySQL》第五章中，有关于这两种引擎的索引描述，本篇博客将结合书中内容进行总结和概括，帮助更好地理解其内部的存储方式。

# 一、查看数据库存储引擎的SQL语句 #

SHOW ENGINES;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70][]

SHOW VARIABLES LIKE '%storage_engine%';

![20200307100729420.png][]

另外，还可以通过SHOW TABLE STATUS来查看表的状态信息，里面会包含与表相关的动态信息展示：

SHOW TABLE STATUS LIKE 'teacher';

Name     Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time  Check_time  Collation        Checksum  Create_options  Comment  
    -------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -----------  ----------  ---------------  --------  --------------  ---------
    teacher  InnoDB       10  Dynamic         18             910        16384                0         81920          0              19  2020-05-24 16:13:32  (NULL)       (NULL)      utf8_general_ci    (NULL)

> Row\_format 可选三个值：Dynamic、Fixed或者Compressed。Dynamic表示行长度是可变的，一般包含可变长度的字段，如VARCHAR或 BLOB。Fixed表示行长度固定，只包含固定长度的列，如CHAR和INTEGER。Compressed只在压缩表中存在。
> 
> Rows：表中行数，MyISAM是一个精确值，InnoDB是估计值。
> 
> Avg\_row\_length：平均每行包含的字节数。
> 
> Data\_length：表数据的大小。
> 
> Max\_data\_length：表数据的最大容量，该值与存储引擎有关。
> 
> Index\_length：索引的字节数。
> 
> Data\_free：对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被INSERT 利用到的空间。
> 
> Auto\_increment：下一个AUTO\_INCREMENT的值。

# 二、InnoDB 和 MyISAM 存储引擎的比较 #

关于InnoDB和MyISAM的常规比较，下表是重点：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 1][]

## 2.1 InnoDB ##

InnoDB是MySQL默认的事务型引擎，也是**最重要、使用最广泛**的存储引擎，被设计成用来处理**大量短期事务**，短期事务大部分情况是正常提交的，很少会被回滚。

InnoDB采用**MVCC（多版本并发控制）**来支持高并发，并且实现了四个标准的隔离级别。其默认级别是可重复读，并且通过间隙锁（next-key locking）策略防止幻读的出现。

间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，如果表上的索引较多的话，主键应当尽可能的小。

InnoDB的存储格式是平台无关的，因此可以将数据和索引文件在不同的平台上复制迁移。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。

## 2.2 MyISAM ##

在MySQL5.1 及之前的版本，MyISAM是默认的存储引擎。

MyISAM提供了大量的特性：全文索引、压缩、空间函数等。但MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复。但对于只读的数据，或表比较小、可以忍受修复操作，依然可以选择MyISAM。

**MyISAM会将表存储在两个文件中**：**数据文件**和**索引文件**。分别以.MYD和.MYI为扩展名。

MyISAM表可以包含动态或静态（固定长度）行。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统单个文件的最大尺寸。

作为MySQL最早的存储引擎之一，MyISAM有一些已经开发出来很多年的特性：

> **①加锁和并发**：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但在表有读取查询的同时，也可以往表里插入新的记录，这被称为并发插入——CONCURRENT INSERT。
> 
> **②修复**：MyISAM可以手工或自动执行检查和修复操作，但这里的修复并不是事务恢复或崩溃恢复。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误，可以通过执行REPAIR TABLE mytable进行修复。
> 
> **③索引特性**：对于MyISAM，即使BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
> 
> **④延迟更新索引键（Delayed Key Write）**：创建MyISAM表的时候，如果指定了DELAY\_KEY\_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的缓冲区。只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式极大的提升了写入性能，但在数据库或主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引的特性可以在全局设置，也可以在单个表设置。

如果表在创建并导入数据之后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。

可以使用myisampack对MyISAM表进行压缩（也叫打包pack）。压缩表中的数据是不可以直接修改的，但可以先解压缩、修改数据、再压缩。

压缩表可以极大的减少磁盘空间占用，因此也可以减少磁盘IO，从而提升查询性能。压缩表也支持索引，但索引也是只读的。

以目前的硬件能力，大多数场景下，读取压缩表数据时的解压开销影响并不大，而减少IO带来的好处是非常明显的。压缩表中的记录是独立记录的，所以读取单行的时候不需要解压整张表，甚至不需要解压行所在的页面。

MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。但MyISAM最典型的性能问题是表锁的问题，如果你发现所有的查询都长期处于“Locked”状态，那么毫无疑问表锁是罪魁祸首。

# 三、InnoDB 和 MyISAM 的数据分布 #

在《高性能MySQL》第五章，作者围绕着数据在两种截然不同的存储引擎中是如何存储的**，**进行了细致的分析。

先来说说MyISAM存储引擎。它对表中数据有单独的存储文件，所谓“单独的” 指的是数据和主键是分开存储的。这一点与InnoDB有着本质的区别。这在数据库领域，叫做——**非聚簇索引**。

我思考了一下，如果让我去设计一个存储引擎，根据我的知识水平，多半就是会设计成MyISAM这样的数据存储结构。我们先来看一下它是如何来存储数据和主键的：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 2][]

首先，不论在InnoDB还是在MyISAM中，索引都是以B树的形式来存储的，这没什么好说的（参考《[MySQL 高级 —— 索引实现的思考][MySQL _ _]》），然后我们看到，主键索引树中的叶子节点都会指向具体的数据行。

也就是说，**MyISAM分开存储了主键列和数据行，然后通过在主键索引的叶子节点中同时保存列值（主键值）和指向数据行的指针，从而实现关联**。这在计算机领域是一种非常典型的键值关联的方式。这也是为什么我说，如果要我来设计存储引擎，可能多半也是这样做的原因，可以说MyISAM的数据存储方式是非常简单的。

MyISAM的二级索引的叶子节点同样保存了指向数据行的指针。因此**本质上，MyISAM的主键索引和普通的二级索引（或者叫辅助索引）没有太大的区别**。从上图中也可以看出。

> **什么是二级索引？**
> 
> 二级索引也叫辅助索引，是除主键索引以外的其他类型的索引。

InnoDB存储引擎，相对于MyISAM就要复杂许多。

首先，它以聚簇索引的形式来组织数据，其次作为聚簇索引的主键索引与二级索引也是有许多不同点：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 3][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 4][]

**InnoDB的聚簇索引就是主键索引**，其叶子节点包含：**主键的列值、事务ID、回滚指针、以及所有数据列**。

可以说，InnoDB整个表的逻辑结构就是通过主键的聚簇索引方式来存储的，**在InnoDB中，聚簇索引就是表**。

所谓“聚簇”，意思就是数据与主键存储在一起。

> 另外，如果InnoDB的主键是一个列前缀索引，InnoDB还是会包含完整的主键列和剩下的其他列。这里的列前缀，我的理解是主键列并不是完整的作为索引列，而是“前缀”作为索引列。比如，主键列值是123456，那么这里的列前缀可以是123，即仅取主键列的前缀作为索引。

InnoDB的二级索引与MyISAM的二级索引有所不同，它不是类似于MyISAM那样在叶子节点中保存“行指针”，而是**保存主键值，以此来作为“指针”。这是因为当出现行移动或数据页分裂时，可以避免对二级索引的维护操作**。但这样的代价可能是会让二级索引占用更多的空间。

> 对于**非叶子节点**，它包含了**索引列**和一个**指向下级节点的指针**，这对所有的 B树索引都适用。

# 四、InnoDB为什么更推荐顺序递增id？ #

InnoDB更推荐使用自增id作为聚簇索引的主键。

我们知道，B树索引是按照索引列递增的顺序进行存储的，InnoDB的主键索引也不例外。

在向InnoDB插入数据时，自增的 id 可以更快速地直接在数据末尾追加。**MySQL数据的存储以页为单位，当页被插满（达到页的最大填充因子，默认15/16），下一条记录就会写入新的页**。

而如果使用随机值，如UUID作为主键，因为新行的UUID不一定比之前插入的记录大，所以InnoDB**无法简单的把新行插入到索引的最后，而是需要为新行寻找合适的位置**。**通常是已有数据的中间位置**。那么之前已经写满的，并且已经刷到磁盘上的页可能会被重新读取。这会增加很多额外工作，并会导致数据分布不够优化。

> **随机主键的缺点如下**：  
> 1、写入的目标页可能已经刷到磁盘上，并且从缓存中移除，或者还没有被加载到缓存中，就必须要先从磁盘中读取目标页，**导致大量的随机IO**。
> 
> 2、因为写入是随机的，InnoDB**不得不频繁的做页分裂操作**，以便为新的行分配空间。**页分裂会移动大量的数据**，一次插入最少需要修改三个页面而不是一个。
> 
> 3、由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以**最终数据会有碎片**。因此可能还需要做一次**OPTIMIZE TABLE **来重建表并优化页的填充。

> **什么是 OPTIMIZE TABLE？** 
> 
> 语法：OPTIMIZE \[LOCAL | NO\_WRITE\_TO\_BINLOG\] TABLE tbl\_name
> 
> 简单的说，由于大量修改数据，如删除、移动等，造成的存储空间利用不均，导致的数据碎片。那么就可以使用OPTIMIZE TABLE 来优化数据表，**从而更好的利用未使用的空间，整理数据文件的碎片**。
> 
> 一般情况下，根本不需要运行 OPTIMIZE TABLE，即使对可变长度的行进行了大量的更新，也不需要频繁运行，每周一次或每月一次即可。只对MyISAM、BDB、InnoDB表有效。OPTIMIZE TABLE时，MySQL会锁表。

另外，顺序主键也不一定是完全无害的，在高并发场景，顺序插入可能会造成明显的争用，主键的上界会成为"热点"，这可能会使并发插入导致间隙锁竞争。

还有另一个热点可能是AUTO\_INCREMENT锁机制。这些问题可能需要重新设计表或应用，或者更改 innodb\_autoinc\_lock\_mode设置。

# 五、选择合适的存储引擎 #

MySQL其实还有很多其他存储引擎，这里建议如何选择存储引擎的原则是“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB”。

例如，如果要用到全文索引，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文索引的MyISAM。

如果应用需要不同的存储引擎，请先考虑一下几个因素：

①事务：如果需要事务，则使用InnoDB是最好的选择，如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型应用比较符合这一特性。

②备份：如果可以定期关闭服务器来执行备份，那么备份的因素可以忽略。如果需要在线热备份，那么最好选择InnoDB。

③崩溃恢复：很多人即使不需要事务支持，也会选择InnoDB的原因，就是它可以在系统崩溃后快速地恢复数据。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200307100600214.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk=,size_16,color_FFFFFF,t_70
[20200307100729420.png]: https://img-blog.csdnimg.cn/20200307100729420.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200307095056794.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200521170728482.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk=,size_16,color_FFFFFF,t_70
[MySQL _ _]: https://blog.csdn.net/u014745069/article/details/104091956
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200521172107964.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200521172121974.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ3NDUwNjk=,size_16,color_FFFFFF,t_70