Mysql存储引擎学习篇

文章部分摘于https://www.cnblogs.com/yufeng218/p/12465694.html

存储引擎是数据库管理系统用来从数据库创建、读取和更新数据的软件模块。

MySQL中有两种类型的存储引擎：事务性和非事务性。

对于MySQL 5.5及更高版本，默认的存储引擎是InnoDB。在5.5版本之前，MySQL的默认存储引擎是MyISAM。

一、InnoDB

这是MySQL 5.5或更高版本的默认存储引擎。它提供了事务安全(ACID兼容)表，支持外键引用完整性约束。它支持提交、回滚和紧急恢复功能来保护数据。它还支持行级锁定。当在多用户环境中使用时，它的“一致非锁定读取”提高了性能。它将数据存储在集群索引中，从而减少了基于主键的查询的I/O。

二、MyISAM

该存储引擎管理非事务性表，提供高速存储和检索，支持全文搜索。

三、MEMORY

提供内存中的表，以前称为堆。它在RAM中处理所有数据，以便比在磁盘上存储数据更快地访问。用于快速查找引用和其他相同的数据。

四、MERGE

将多个类似的MyISAM表分组为一个表，可以处理非事务性表，默认情况下包括这些表。

五、EXAMPLE

你可以使用此引擎创建表，但不能存储或获取数据。这样做的目的是教开发人员如何编写新的存储引擎。

六、ARCHIVE

用于存储大量数据，不支持索引。

七、CSV

在文本文件中以逗号分隔值格式存储数据。

八、BLACKHOLE

受要存储的数据，但始终返回空。

九、FEDERATED

将数据存储在远程数据库中。

 

下面主要介绍我们经常用到的InnoDB和MyISAM：

同一个数据库中，不同的表可以设置不同的存储引擎，所以说存储引擎是相对于表而言的；

MySQL的数据存储在 data 目录下， data 目录下的 文件夹是以 数据库为单位的，数据库文件夹下面存放的表数据；  data / {数

据库名} /表文件，不同的版本有所改变。

 

1、MyISAM存储引擎索引实现

MyISAM存储引擎的索引文件和数据文件是分离的（非聚集）；

MyISAM 存储引擎的一个表有3个文件： 

*.frm 文件存储的表的结构；

*.MYD 文件存储表的数据；

*.MYI 文件存储表中的索引数据；

MYISAM 存储引擎的索引的叶子节点的data中存储的是索引所在行的磁盘指针；（非聚集索引）

MYISAM 存储引擎的主键索引 和 非主键索引的存储是差不多的，InnoDB 存储引擎的 主键索引 和 非主键索引存储是不一样的；

 2、InnoDB 存储引擎索引实现

InnoDB存储引擎索引文件和数据文件是合一的(聚集)；

InnoDB 存储引擎的1个表有2个文件：

  *.frm 文件存储表的结构；

  *.ibd 文件存储的是索引和数据；

InnoDB表的数据文件本身就是按 B+Tree 组织的一个索引结构文件；聚集索引叶子节点包含了完整的数据记录；

（1）InnoDB的主键索引

InnoDB 存储引擎的索引的叶子节点的data中存储的是索引对应的所有数据；

问题1：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

a. 因为 MySQL对于 InnoDB 表设计的就是按照 B+Tree 组织存储数据的，若没有主键就没有办法去存储数据了；但是在平常我们建表的时候没有指定主键也是可以建成功的，这是因为 MySQL 会生成一个 rowid 作为数据的唯一标识；

b. 若使用的 UUID 作为主键，在查找的时候需要去比较大小，字符串UUID比较的效率肯定低于整形的；在进行比较的时候会把数据拿到内存空间中做比较，UUID为字符串占用的内存空间肯定比整形多；

c. 若是递增的，则插入的数据直接向后排，这个节点满了，直接新增一个节点就好了；若不是递增的，有个节点存储满了（5, 9），但是新插入了一个数据（7）在这个节数据的中间，则需要将这个节点先分裂，再平衡去满足 B+Tree 的结构；

（2）InnoDB 的非主键索引

在使用非主键索引查找的时候，先从非主键索引的树中查询到对应的主键值，然后使用主键值去到主键索引的树中去查找；对于非主键单值索引，若索引字段的值为 null，则它的数据不会放到非叶子节点上，是放在叶子节点的链表的最前面的；（强烈不建议字段设置为null）

问题2：为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

因为在插入数据之前先要维护一下索引，然后再将数据插入进去；若 主键索引 和 非主键索引 的叶子节点都存储具体的数据，则一个 insert 语句插入成功的判断就是 向主键索引中插入成功 且 向非主键索引中也插入成功，这样就造成了事务的问题，事务是很耗性能的；当然，主键索引和非主键索引的叶子节点都存储具体数据，会造成数据的同样的数据存储了几份，就造成了空间的浪费；

联合索引的底层存储结构

以上的联合索引从左到右由字段 a，b，c 组成；

联合索引在存数据或比较的时候，先比较联合索引最前面的字段，若最前面的字段值一样，则再比较第二个字段的值；

联合索引的索引字段中有一个值为null，则将其放在叶子节点的最前面；可以认为null值是最小的。