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值是最小的。