1500字范文 > MySQL 存储引擎 | MyISAM 与 InnoDB

MySQL 存储引擎 | MyISAM 与 InnoDB

时间：2020-10-09 07:07:29

文章目录

概念innodb引擎的4大特性索引结构InnoDBMyISAM区别表级锁和行级锁

概念

MyISAM是MySQL的默认数据库引擎（5.5版之前），但因为不支持事务处理而被InnoDB替代。

然而事物都是有两面性的，InnoDB支持事务处理也会带来一些问题：

当操作完全兼容ACID时，InnoDB会自动合并多个连接，但每次有事务产生时，仍至少须写入硬盘一次，因此对于某些硬盘或磁盘阵列，会造成每秒200次的事务处理上限。

若希望达到更高的性能且保持事务的完整性，就必使用磁盘缓存与电池备援。当然InnoDB也提供数种对性能冲击较低的模式，但相对的也会降低事务的完整性。

innodb引擎的4大特性

插入缓冲(insert buffer)：

为解决非聚集索引的写性能问题（插入或更新）而生。

对非聚集索引的插入或更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中：

若在则直接插入；不在则先放入到一个Insert Buffer对象中。

此时看似数据库这个非聚集索引已经插入到了叶子节点，然而实际上只是存放在另一个位置。然后再以一定的频率进行Insert Buffer与辅助索引叶子节点的merge操作，此时通常能将多个插入合并到一个操作中，这就大大提高了非聚集索引插入的性能。

二次写(double write)：

数据库宕机可能会引起部分写失效（partial page write），解决方法有两种：

数据库宕机，物理文件完好无损，是可以通过redo log进行崩溃恢复（ACID中的持久性）。数据库宕机，物理文件由于宕机发生损坏，这时就无法通过redo log进行数据恢复了, 而只能通过double write解决问题。

二次写分为三步：

对缓冲池脏页进行刷新时，先将脏页复制到内存的double write buffer；第一次写：将double write buffer分批次写入到共享表空间中，因为共享表空间是连续存储所以很快。第二次写：将double write buffer中数据写入到各个表空间文件中，而此时的写入则是离散的，因为各个表空间可能在磁盘的不同位置，因此写入速度较慢。

PS：参数skip_innodb_doublewrite可以禁用double write功能，但不推荐这样做。对于需要提供数据高可靠性的主服务器，任何时候都应该确保开启double write功能。

自适应哈希索引(ahi)：

InnoDB存储引擎会监控对表上各个索引页的查询，如果它观察到建立哈希索引可以带来速度提升，则会自行建立哈希索引，这也就是自适应哈希索引。即会自动根据访问频率和模式来为热点数据建立哈希索引。

哈希索引是数据库自身自动创建并使用的，人工无法对其进行干预。

预读(read ahead)：

为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作，InnoDB也是如此。

同步阻塞：每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。

但是如果一条SQL语句需要扫描多个索引页，也就是需要进行多次IO操作(扫描一个页就是一次IO请求)。同步阻塞会扫描一个页并等待其完成再进行下一个页扫描，也就是等待一个IO请求完成后再发送下一个IO请求，效率很低。

异步非阻塞（AIO）：用户可以在发出一个IO请求后不必等待其完成，而是可以立即发出下一个IO请求。

索引结构

我们在MySQL 索引：哈希索引、B+树索引、全文索引中介绍过聚集索引和非聚集索引，现在结合两个存储引擎深入研究一下。

两者都使用B+树作为索引结构，但实现方式却截然不同：

主键索引：InnoDB的数据文件本身就是索引文件；MyISAM主键索引的叶节点存的是数据地址。辅助索引：InnoDB的辅助索引data域存储相应记录主键的值而不是地址；MyISAM辅助索引的叶节点存的还是数据地址。

InnoDB

InnoDB是聚集索引：

数据文件和主键索引绑在一起（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。主键索引的叶节点保存了完整的数据记录。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。主键不应该过大，因为主键太大，其他索引也都会很大。辅助索引的叶子节点是主键的值。

图源博客

MyISAM

MyISAM是非聚集索引：

索引和数据文件是分离的，主键索引和辅助索引的叶子节点都是数据文件的地址指针。主键索引要求每个叶子节点的内容是唯一的，而辅助索引每个叶子节点的内容可以重复。主键索引和辅助索引是独立的。因此不同于InnoDB需要先在辅助索引中查找到主键，再通过主键在主键索引中查找到具对应数据。MyISAM可通过辅助索引快速找到所有的数据，而不需要再遍历一边主键索引，所以适用于OLAP。