1 MyISAM

1.1 特点

  • 表锁，写并发性能差。
  • 不支持事务。
  • 不支持外键
  • count()时，会直接存储总行数。
  • 清空整张表时，是删除重建。
  • 数据与索引存储分离

1.2 存储方式

  • 会在磁盘上产生三个文件： .frm(表的定义) .MYD(存储数据) .MYI(存储索引)
  • 索引存储和数据存储分离。
  • 索引文件和数据文件最小存储单位是页(也可以称为块)。mysql中页默认大小是16k（所以查看数据文件或索引文件在磁盘中占用空间大小会发现都是16k的整数倍）
  • 索引文件和数据文件在平时都是存储在磁盘上的，在查询的时候，是按照相应文件的页为单位调入内存的，不是将整个文件全部调用内存。所以查询的时候可能需要有多次IO操作。
  • 索引采用B+树，索引的节点是以页为单位，即一个节点占一个页。
  • 索引与数据存储分离，索引的叶子节点中存储了数据的磁盘地址。然后通过这个数据磁盘地址找到相应的数据页（调入内存），然后再从中取出数据。
  • MyISAM的数据文件中数据的存储是无序的，即每个数据页中的存储的顺序并没有顺序规律，所以当基于主键的范围查询，可能就需要查找多个数据页才能将范围内的数据全部查找到（意味着更多的IO操作）。

mysql 数据库索引转换高斯数据库 mysql数据和索引分开存_mysql 数据库索引转换高斯数据库

2. InnoDB

2.1 特点

  • 支持行锁，即细粒度锁，并发性能好
  • 支持事务，默认隔离级别是可重复读
  • count()时，是扫描全表。
  • 清空整张表是，一条一条删除。
  • 是基于B+树的索引组织表。索引与数据存储不分离。

2.2 存储方式

  • 会在磁盘上产生两个文件： .frm(表的定义) .idb(数据文件)
  •  InnoDB表上必须要有主键，如果用户没有指定则会mysql自己添加一个隐藏列来充当主键。然后以这个主键当做聚簇索引。
  • 聚簇索引的节点也是以页为单位的，默认大小为16k，可以通过参数调整。在聚簇索引的叶子节点中，其存放的是数据。而且这个叶子节点内的数据是逻辑有序的。所以基于主键的范围查询，要查询的叶子节点个数就会比MyISAM要少（对应IO次数页更少）。
  • 非聚簇索引的叶子节点存放的是主键。如果用非聚簇索引查询数据，还需要二次回表。找到主键后，利用主键在聚簇索引中查询出数据。

mysql 数据库索引转换高斯数据库 mysql数据和索引分开存_数据_02


2.3 为什么InnoDB推荐使用自增ID作为主键。

  • 自增ID保证了每次插入时B+树的聚簇索引时从右边扩展的，可以避免了B+树的频繁合并与分裂（对比使用UUID）。
  • 如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

2.4 InnoDB存储引擎的4大特性

  • 插入缓冲
  • 二次写
  • 自适应哈希索引
  • 预读

3. MEMORY

  • 数据内存存放，无法持久化
  • 适合偶尔作为临时表使用
  • 性能很高
  • 默认是哈希索引

4. 总结

  • mysql数据库中不同的表可以使用不同的存储引擎来存储。
  • 目前，mysql默认的存储引擎是InnoDB。而且随着mysql的更新，可能也只会支持这一种引擎。
  • 所以我们如果不知道选择什么存储引擎时，就选InnoDB吧。