当前位置：首页 > news >正文

关于MySQL InnoDB存储引擎的一些认识

news 来源：原创 2025/5/19 7:48:34

文章目录

一、存储引擎
- 1.MySQL中执行一条SQL语句的过程是怎样的？
- - 1.1 MySQL的存储引擎有哪些？
  - 1.2 MyIsam和InnoDB有什么区别？
- 2.MySQL表的结构是什么？
- - 2.1 行结构是什么样呢？
  - - - 2.1.1 NULL列表？
      - 2.1.2 char和varchar？
- 3.MySQL 缓存
- - 3.1 聚簇索引和非聚簇索引
  - 3.2 Buffer Pool
  - - 3.2.1 什么是Buffer Pool？
    - 3.2.2 空闲页？
    - 3.2.3 脏页？
    - 3.2.4 缓存命中率咋样？脏页更新到磁盘时机呢？
    - - 1.预读失效
      - 2.Buffer Pool污染
      - 3.脏页更新时机
    - 3.2.5 Buffer Pool可以代替Redis吗？
二、SQL基础
- 前言
- 1.约束？
- 2.三范式？
- 3.SQL刷题网站
参考资料
关于我的网站

一、存储引擎

1.MySQL中执行一条SQL语句的过程是怎样的？

在这里插入图片描述

连接器
查询缓存（那缓存什么时候失效呢？）
解析SQL
执行SQL

语法树

1.1 MySQL的存储引擎有哪些？

在这里插入图片描述

InnoDB
- 5.5之后，MySQL默认的存储引擎，支持事务和行级锁，具有回滚和崩溃恢复功能。（为什么InnoDB替代了MyIsam？）
MyIsam
- 不支持事务和行级锁，无法做到崩溃恢复。
- 数据文件结构有.frm .myd .myi
Memory
- 将数据存储到内存中，可以快速访问数据，并且这些数据不会被修改，重启之后丢失也没问题。

1.2 MyIsam和InnoDB有什么区别？

数据存储方式
- InnoDB采用索引组织表，索引即数据，数据即索引。
- MyIsam采用堆表，索引和数据完全分开。
锁
- MyIsam仅支持表锁，InnoDB支持表锁和行锁。
事务
- MyIsam不支持事务。

堆表

堆表中的索引都是非聚簇索引，没有聚簇索引这一说。
由于索引的叶子节点存放的是堆表的物理地址，如果堆表的数据发生变动，那么索引将全部被动更新，这是非常影响性能的。

索引组织表
在这里插入图片描述

索引组织表中是有聚簇索引和非聚簇索引的。
非聚簇索引的变动不影响聚簇索引。

2.MySQL表的结构是什么？

MySQL默认的是InnoDB存储引擎，所以相关内容主要以InnoDB为主。

在这里插入图片描述

段：用于存储具体对象，比如数据段、索引段、Undo段，新增数据时，会分配新的区。
区：每个区是1mb，包含64页。
页：是数据存储的基本单位，每页16kb，又根据不同的数据类型分为不同类型的页，比如数据页、索引页、undo页、系统页、事务页。
行：行数据。

2.1 行结构是什么样呢？

在这里插入图片描述

变长字符长度列表：只出现在有变长字段的表记录中，主要是根据这个变长字段去读取对应长度的数据。(varchar和char的区别？)
NULL值列表：NULL是怎么存储的？
头信息
- delete_mask：表示这条数据是否删除，执行DELETE的时候不是真的删除，而是标记delete_mask=1 。
- next_record：下一条记录的位置。
- record_type：表示记录类型，0 普通记录，1 非叶子节点，2 最小记录，3 最大记录。
row_id：不是必须的，如果没有主键或唯一约束，就使用这个隐藏列。
trx_id：事务id，表示是由那个事务生成的。6byte
roll_ptr：记录上一个版本的指针。

2.1.1 NULL列表？

NULL值列表的大小是在插入行数据时根据每行数据中允许为NULL的列数量动态生成的。

在这里插入图片描述

列1：不是NULL值，用0表示。
列2：是NULL值，用1表示。

一个字节最多可以表示8个列，也就是说可以为null字段的列，最少只需要1byte表示即可，这样就大大节省空间了。

2.1.2 char和varchar？

char是固定长度的字符串类型，在系统中占用固定存储空间，如果实际存储空间比较小用空格填充。

varchar可变长，需要1-2字节存储可变长字符串的长度，不会进行空格填充。

3.MySQL 缓存

3.1 聚簇索引和非聚簇索引

在这里插入图片描述

聚簇索引和非聚簇索引最主要的区别就是B+树叶子节点存放的内容不同：

聚簇索引的B+树的叶子节点存放的是主键值和完整的记录；
非聚簇索引的B+树叶子节点存放的是索引值和主键值。

如果查询条件用到了二级索引，但是查询的数据不是主键值，也不是二级索引值，这时在二级索引找到主键值后，就需要回表才能查找到数据，需要扫描两次B+树。

如果查询的是主键值，因为在二级索引就能查询到，那时候就会用到覆盖索引，不需要回表，只需要扫描一次B+树。

3.2 Buffer Pool

3.2.1 什么是Buffer Pool？

Buffer Pool就是MySQL为了提高查询性能的一个缓冲池，位于存储引擎层。默认innodb_buffer_pool_size=128MB。

InnoDB会为Buffer Pool申请一片连续的内存空间，然后按照默认的16kb的大小划分出一个个页，Buffer Pool中的页叫做缓存页。

在这里插入图片描述

为了管理这些缓存页，InnoDB为每一个缓存都创建了一个控制块，这些控制块包括【缓存页的表空间、页号、缓存页地址、链表节点】等。

3.2.2 空闲页？

Buffer Pool是一片连续的内存空间，但是在MySQL运行一段时间后，肯定有空闲的也有被使用的。那么这些空闲页怎么办？

我们下次读取磁盘数据的时候，是要遍历Buffer Pool找到一个空闲页吗？那也太浪费的吧。所以就为这些空闲页创建一个Free链表。

那么每当需要加载数据的时候，直接在Free链表拿一个就行。

3.2.3 脏页？

如果说我的缓存页被修改了，那么就直接更新我的磁盘吗？那也太拉低性能了吧。所以就设计了脏页，由后台线程去更新到磁盘好了。

那么怎么找这个脏页呢？那就创建一个链表吧，就叫Flush链表。

3.2.4 缓存命中率咋样？脏页更新到磁盘时机呢？

对于这个Buffer Pool来讲，我们当然希望访问次数多的一直留下，访问次数少的就给他移除。

那么就使用LRU算法，来实现这个功能，具体LRU是什么呢，可以看这篇文章。

https://blog.csdn.net/m0_73337964/article/details/144726361?spm=1001.2014.3001.5501

如果直接使用LRU算法的话，会出现两种问题预读失效和Buffer Pool污染。

1.预读失效

预读失效就是MySQL在访问数据时，会顺带给邻居也读出来，但是这些被提前读出来的邻居，并没有被访问，相当于这个预读白做了，这就是预读失效。

要避免预读失效带来的影响，最好就是让预读的页停留在Buffer Pool里的时间尽可能短，让真正被访问的页移动到LRU链表头部，从而保证真正的热数据留在Buffer Pool里的时间尽可能长。

MySQL将LRU链表分为了两个部分young和old区域。

在这里插入图片描述

预读的页加载到old区域头部，当页真正被访问时，才将其加入到young区域头部。

2.Buffer Pool污染

当某一个SQL语句，在Buffer Pool比较有限的情况下，可能会将Buffer Pool里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的IO，这就是Buffer Pool污染。

MySQL的解决方案是，进入到young区域条件增加了一个停留在old区域的时间判断。

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从old区域移动到young区域的头部。
如果后续的访问时间与第一次访问的时间不再某个时间间隔内，那么该缓存页移动到young区域的头部。

间隔时间默认为innodb_old_blocks_time=1000ms，也就是说只有同时满足被访问与在old区域停留时间超过1秒两个条件，才会被插入到young区域头部。

3.脏页更新时机

当redo log日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，就先将脏页同步到磁盘；
空闲时，后台线程定期将适量的脏页刷入到磁盘；
关闭前，把所有脏页刷入到磁盘。

3.2.5 Buffer Pool可以代替Redis吗？

我觉得不能代替。

因为MySQL的设计是进行持久化存储数据，所有的模块设计主要关系到磁盘的IO性能，在内存缓存方面并不是很迫切，而Redis是存储在内存的数据库，在内存层面操作具有每秒数十万次访问的高性能，Redis 注重极致的内存操作效率，而 MySQL 更关注数据的持久化和复杂查询能力，所以不能代替。

二、SQL基础

前言

重点掌握AND、OR、IN、NOT IN、BETWEEN、LIKE、IS、NULL、EXISTS、DISTINCT。

JOIN：内连接、左连接、外连接。UNION：组合查询。

INSERT、DELETE、UPDATE。

聚合函数：COUNT、MAX、MIN、SUM、AVG。

重点掌握GROUP BY和HAVING以及与Where的区别。

count(*)=count(1)>count(primary key field)>count(field)

1.约束？

主键约束：唯一标识一条记录，不能重复也不能为空，一般会给id设置为主键。
唯一约束：保证字段在表中的数值是唯一的。
非null约束：保证字段不能为null。
外键约束：确保表与表之间的引用完整性。
默认约束：插入数据时，给没有取值的字段设置默认值。

2.三范式？

第一范式：所有字段都是基本项。
第二范式：解决部分依赖关系。
第三范式：不允许存在传递依赖。

比如，一个订单表：

ID	姓名	单价	数量	总计
1	小明	20	5	100
2	李华	30	1	30
3	张三	10	2	20

非主键字段，完全依赖于主键订单编号，符合第二范式。但是总计字段不是完全依赖主键字段ID，可以由单价数量得到，所以不符合第三范式。

数据库三范式主要是为了解决数据冗余、数据插入更新不一致性问题。

但是，如果范式化的将数据分解为多个表，那么查询数据的时候，可能需要多表关联的操作，那么成本是很高的，所以有时业务场景采用字段冗余设计表，避免联表查询。

3.SQL刷题网站

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=199

参考资料

1、https://xiaolincoding.com/mysql/base/how_select.html#%E9%A2%84%E5%A4%84%E7%90%86%E5%99%A8

2、https://learn.lianglianglee.com/%E4%B8%93%E6%A0%8F/MySQL%E5%AE%9E%E6%88%98%E5%AE%9D%E5%85%B8/09%20%20%E7%B4%A2%E5%BC%95%E7%BB%84%E7%BB%87%E8%A1%A8%EF%BC%9A%E4%B8%87%E7%89%A9%E7%9A%86%E7%B4%A2%E5%BC%95.md

3、https://xiaolincoding.com/mysql/base/row_format.html#%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%96%87%E4%BB%B6%E7%9A%84%E7%BB%93%E6%9E%84%E6%98%AF%E6%80%8E%E4%B9%88%E6%A0%B7%E7%9A%84

关于我的网站

最后，关于我自己的网站GolangCode也是正式上线了。哈哈，也是简单的搭建了一个vuepress-theme-hope主题的静态网站，主要分享一些Go、MySQL、Redis等的后端开发编程笔记。如果有帮助的话，可以给我点点star。🍻🍻🍻

网站链接：golangcode.cn

在这里插入图片描述

最后，也是提前祝每一位还在努力提升技术的小伙伴新年快乐，身体健康。

文章目录

一、存储引擎

1.MySQL中执行一条SQL语句的过程是怎样的？

1.1 MySQL的存储引擎有哪些？

1.2 MyIsam和InnoDB有什么区别？

2.MySQL表的结构是什么？

2.1 行结构是什么样呢？

2.1.1 NULL列表？

2.1.2 char和varchar？

3.MySQL 缓存

3.1 聚簇索引和非聚簇索引

3.2 Buffer Pool

3.2.1 什么是Buffer Pool？

3.2.2 空闲页？

3.2.3 脏页？

3.2.4 缓存命中率咋样？脏页更新到磁盘时机呢？

1.预读失效

2.Buffer Pool污染

3.脏页更新时机

3.2.5 Buffer Pool可以代替Redis吗？

二、SQL基础

前言

1.约束？

2.三范式？

3.SQL刷题网站

参考资料

关于我的网站

相关文章：