MySQL进阶实战9，InnoDB和MyISAM的数据分布对比_小言_互联网的博客

MySQL进阶实战9，InnoDB和MyISAM的数据分布对比

2022-11-30 14:43 360人阅读评论(0)

一、InnoDB存储引擎

InnoDB的数据存储在表空间dataspace中，由很多数据文件组成。

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

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

InnoDB是基于聚簇索引建立的，聚醋索引对主键查询有很高的性能，不过其它索引中必须包含主键列，所以主键应尽可能的小。

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

二、MyISAM存储引擎

1、MyISAM简介

MyISAM支持全文索引、压缩、空间函数等，但不支持事务和行级锁，而且有一个致命缺陷是系统崩溃后，数据无法恢复。

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别是以.MYD和.MYI为扩展名的文件。MyISAM可以包含动态或固定长度的行。MySQL会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间或者操作系统中单个文件的最大尺寸。

MyISAM表如果是变长行，默认配置只能处理256TB的数据，因为指向数据记录的指针长度是6个字节。要想改变指针长度，可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引。

2、MyISAM压缩表

如果表在创建并导入数据后，不会再进行修改操作，那么这样的表可以采用MyISAM压缩表。压缩表可以极大地减少磁盘空间占用，减少磁盘IO，从而提升查询性能，压缩表业支持索引，但索引也是只读的。

大部分情况下，选择InnoDB存储引擎就对了，MySQL也在5.5版本中，将InnoDB作为默认的存储引擎了。支持事务、行级锁、崩溃恢复是选择InnoDB存储引擎主要目的。
一、先建一个表

CREATE TABLE `nezha_soft` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB;

假设该表的主键取值为1~10000，按照随机顺序插入并使用optimize table命令做了优化。

三、InnoDB的数据分布

因为InnoDB是聚簇索引，所以使用非常不同的方式存储数据。

聚簇索引的每一个叶子节点都包含主键值、事务ID、用于事务和MVCC的回滚指针、其它列。

InnoDB的二级索引和聚簇索引不同，InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的指针。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

在InnoDB中，最好按照主键顺序插入行，对于根据主键做关联操作的性能会更好。最好避免随机的聚簇索引，特别是IO密集型的应用，从性能的角度考虑，使用UUID来作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

用UUID作为主键索引的缺点：

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO。
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行为分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页面而不是一个页。
由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

四、MyISAM数据分布

MyISAM按照数据数据插入的顺序存储在磁盘上。

在行的旁边显示了行号，从0开始递增。

下图隐藏了页的物理细节，只显示索引中的节点，索引中的每个叶子节点包含行号。

在MyISAM存储引擎中主键索引和其它索引在结构上没有什么区别，主键索引就是一个名为primary的唯一非空索引。

五、顺序的主键什么时候会造成更坏的结果？

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为热点，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto_increment锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。