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

一、存储引擎

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

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

语法树

1.1 MySQL的存储引擎有哪些？

• InnoDB

  • 5.5之后，MySQL默认的存储引擎，支持事务和行级锁，具有回滚和崩溃恢复功能。（为什么InnoDB替代了MyIsam？）

• MyIsam

  • 不支持事务和行级锁，无法做到崩溃恢复。
  • 数据文件结构有.frm .myd .myi

• Memory

  • 将数据存储到内存中，可以快速访问数据，并且这些数据不会被修改，重启之后丢失也没问题。

1.2 MyIsam和InnoDB有什么区别？

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

堆表

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

索引组织表

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

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.脏页更新时机

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

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.约束？

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

2.三范式？

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

比如，一个订单表：

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

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