跳至主要內容

SQL基础和InnoDB引擎

GolangCodeMySQLMySQL约 2707 字大约 9 分钟

一、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的区别。

1.count(*)、count(1)那种性能更好？

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

2.SQL执行顺序？ image-20250115204633688

二、InnoDB存储引擎

1.逻辑架构

1.1 一条SQL语句是怎么执行的呢？

image-20250118191109795

首先，MySQL的连接器会负责建立连接、校验用户身份、接收客户端的SQL语句；之后MySQL会在查询缓存中查找数据，但是在8.0版本移除了；其次MySQL的解析器会对SQL语句进行词法分析和语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；然后MySQL的优化器会基于查询成本的考虑，判断每个索引的执行成本，从中选择成本最小的执行计划；最后MySQL的执行器执行查询语句，从存储引擎读取记录，返回个客户端。

1.2 MySQL存储引擎有哪些？

我所了解的存储引擎有InnoDB、MyIsam、Memory。我对InnoDB最为了解，它是MySQL默认的存储引擎，支持事务和行级锁，具有事务提交、回滚和崩溃恢复功能。MyIasm引擎我没有用过，但是我所了解的是它不支持事务和行级锁，而且由于只支持表锁，更新性能差，我认为它支持读多写少的场景。Memory引擎我不太了解，知道它是将数据存储在内存中，所以数据读写所读还是比较快的，但是不具备持久性。

（ps：那你知道MyIsam和InnoDB有什么区别吗？）

InnoDB在数据存储方面采用的是索引组织表，表数据和索引数据都存到同一个文件中（.frm&.ibd）。MyIsam采用的是堆表，数据和索引分开存储，因此表数据和索引数据会分开放在两个不同的文件中存储。

InnoDB引擎B+树叶子节点存储的是索引和数据，但是MyIsam存储的是索引和数据地址。另外，InnoDB支持行级锁和事务，但是MyIsam都不支持，只支持表级锁。

2.MySQL一行记录是怎么存储的？

数据表的数据存放在哪？.ibd文件。

InnoDB表空间结构有哪些组成？

varchar是怎么保存长度的？

Null值是如何保存的？

表空间由段、区、页、行组成。

image-20250118191607312

2.1 NULL值是如何存储的？

image-20250118191636391

MySQL存储一行数据的时候，会使用上面这个格式进行存储，其中NULL值列表就是用来保存NULL值的。

MySQL行格式中会用NULL值列表来标记值为NULL的列，每个列对应一个二进制位，如果列的值为NULL，就标记这个位为1，否则为0，所以NULL值不会存储在行格式中的真实数据部分。

NULL列表最少会占用1字节空间，当表中所有列都定义为NOT NULL时，行格式中就不再需要NULL值列表，可以节省1字节空间。

总结，为什么说使用NULL值列表了，就可以节省空间？

就比如，一个字段varchar(200)，但是是NULL，如果你不用标记位1来表示它为空，那么会给它分配最多200字节的空间，如果使用NULL列表，只需要1bit就可以表示它是NULL了，就不需要分配这个空间。

3.char和varchar的区别？

假如说一个字段是varchar(10)，但它其实只有6个字节，那他内存中占的存储空间是多少？在文件中占的存储空间是多少？

答：内存会占用10字节，文件存储占6字节，并且额外用1-2字节存储可变长字符串长度的空间。（保存达到文件的时候，只会存储实际使用的字符串大小，但是内存会按varchar最大值固定分配大小。）

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

varchar是可变长的字符串类型，实际存储只占用实际字符串长度的空间，不会进行空格填充。

4.数据页

4.1 聚簇索引和非聚簇索引

聚簇索引和非聚簇索引的B+树有什么区别？

image-20250118191658237

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

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

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

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

4.2 Buffer Pool

InnoDB对LRU做了哪些优化？

4.2.1 什么是Buffer Pool？

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

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

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

4.2.2 空闲页？

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

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

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

4.2.3 脏页？

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

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

4.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区域。

image-20250118193658063

预读的页加载到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. 关闭前，把所有脏页刷入到磁盘。
4.2.5 Buffer Pool可以代替Redis吗？

我觉得不能代替。

MySQL设计主要是为了减少对IO的访问，而不是减少堆内存的访问，而Redis主要是用来减少对内存的访问的。MySQL在更新数据的时候，为了保证事务的隔离性，是需要加锁的，而Redis更新操作都是不需要加锁的，还有MySQL为了保证事务的持久性，还需要刷盘redolog和binlog，Redis可以选择不持久化数据。因此Buffer Pool无限大，Buffer Pool也不如Redis缓存性能好。

4.3 Change Buffer

普通索引和唯一索引有什么区别？哪个性能更好？

参考资料

1、https://xiaolincoding.com/mysql/base/how_select.html

2、https://xiaolincoding.com/mysql/base/row_format.html

3、https://learn.lianglianglee.com/%E4%B8%93%E6%A0%8F/MySQL%E5%AE%9E%E6%88%9845%E8%AE%B2/09%20%20%E6%99%AE%E9%80%9A%E7%B4%A2%E5%BC%95%E5%92%8C%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95%EF%BC%8C%E5%BA%94%E8%AF%A5%E6%80%8E%E4%B9%88%E9%80%89%E6%8B%A9%EF%BC%9F.md