mysql

mysql是怎样运行的

Posted by Sutdown on February 10, 2025

前言（碎碎念，可跳）：

之前学数据库的时候用的opengauss，还有个十几章的实验，那会很多东西都搞不太清楚，基本就是跟着文档走，也没留下什么印象。期末周靠着往年题和ppt苟活，导致数据库这门课学的着实不扎实。简历上有个lsmkv的项目，浅显的数据库理解必然是不够的，基础知识不够的情况下背八股更是显得无用，这两三天经人推荐看了看这本书，看完就完也很容易忘，所以打算写个学习笔记。

整体评价，还不错，语言比较有趣，深度也可以，建议有一定数据库基础之后阅读。

正文：

1.mysql的结构（主要是InnoDB）

mysql分为server层和存储引擎层。

  • server层：连接管理，查询缓存，语法解析，查询优化
  • 存储引擎层最常用的是InnoDB和MyISAM。其中InnoDB是Mysql的默认存储引擎。
    • InnoDB：具备外键支持功能的事务存储引擎。
    • MyISAM：主要的非事务处理存储引擎。

InnoDB行格式介绍：主要有四种Compact，Redundant，Dynamic，Compressed

Compact格式：

1

变长字段长度列表 | NULL值列表 | 记录头信息 | 记录真实数据：列1 | 列2 | ...

其中，由于初始指针位于头信息，因此为了便于访问，NULL和变长字段长度列表中对应列的数值都是逆序存放。为了最小化内存的使用，变长字段长度列表中，只存放varchar的类型，NULL值列表中对应为空标识1，因此通常最好创建类型的时候标明NOT NULL。

Compact是如今最常用的格式，其它格式属于mysql之前的版本采用的，因此不过多介绍。

页是mysql中MySQL中磁盘和内存交互的基本单位，也是管理存储空间的基本单位。一般大小为16KB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

InnoDB数据页结构示意图
-----------
File Header 记录页的通用信息
-----------
Page Header 记录数据页的专有信息
-----------
Infimum + supremum 页中最小和最大伪记录
-----------
User Records 真实存入我们插入的记录
-----------
Free Space 页中尚未使用的部分
-----------
Page Directory 页面目录
-----------
File Tailer 校验页完整性
-----------

这个和之前levelDB中SSTable的结构有点像，估计是统一结构，最下面放一个目录记录上方各个数据的偏移量和一个校验整体整性的两部分，头部存放通用信息和该页面的专有信息，比如在MySQL中会存放指针指向前一个页和后一个页（B+树），中间存放数据，至于user record和free space是属于统一空间，字节不明确，从free space中去空间存放记录，然后记录从上到下存放在user record中。

2.一条mysql是如何运行的

建立连接；查询缓存（缓存效果不好，因为只会记录完全相同的语句的查找结果，但由于数据量很大，且现实中查找同一项的场景比较少，因此mysql最新版本已经取消了查询缓存）；解析sql；执行sql（预处理，优化，执行）。

在执行mysql的过程中，首先会优化语句，也就是采取什么样的查找方式最有效率，其中考虑的因素有磁盘IO成本和CPU成本。顾名思义，前者指的是从磁盘中加载数据和讲数据加载回内存，后者则是读取以及检测记录是否满足对应的搜索条件，对结果集进行排序损耗的时间。然后再从InnoDB存储引擎中寻找，池化思想在这里也有点体现，那就是我们的BufferPool，后面详细讲解。我们知到页是基本单位，由于页过于多，因此提出了区的概念。

基本就是一个表空间中，每256个区属于一组，每个区大概1MB，区中存放页。管理区的结构我们称为XDES Entry(Extent Descriptor Entry)，其中这个区属于一个结点，很多个Entry会构成链表，同时为了管理内存，设置了三个链表，分别是，完全空闲，存放但是有剩余空间，完全满。

这样查询数据便于保证效率。

3.B+树和索引

B树和B+树，B+树的优点

  • 由于冗余结点较多，增删改时不会出现太大的变动
  • 只有叶子结点存放数据，能够存放更多的索引，减少磁盘IO次数。（有个record_type用来区分目录项记录和普通的用户记录，目录项记录record_type为1，用户记录该项为0）
  • 叶子节点之间用链表连接，有利于范围查询。

常用的索引方案是聚簇索引，二级索引，以及联合索引。

聚簇索引也就是利用B+树的结构查找，时间复杂度为O(logn)，只在搜索值是主键值的时候发挥作用。

二级索引的结点中只会存储 键值+页号+主键值，以键值作为新的B+树，不会存储所有的数据以免造成冗余，后两者用于回表找到确切的条目。

联合索引其实就是二级索引的扩展，没有很大特殊性。

另外在使用联合索引和二级索引的时候，select后面最好写清楚想要查找的值，如果刚好在B+树中，就能避免回表的损耗。

4.索引失效

索引失效就是在查找的时候不能使用表中有的树进行查找，只能进行全表扫描。（全表扫描不仅损耗时间，同时会为cache产生影响，这个cache不是查询缓存，是BufferPool中的cache，为了保证数据一致性）

这里直接引用原文了：

  1. B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
  2. B+树索引适用于下边这些情况：
    • 全值匹配
    • 匹配左边的列
    • 匹配范围值
    • 精确匹配某一列并范围匹配另外一列
    • 用于排序
    • 用于分组
  3. 在使用索引时需要注意下边这些事项：
    • 只为用于搜索、排序或分组的列创建索引
    • 为列的基数大的列创建索引
    • 索引列的类型尽量小
    • 可以只对字符串值的前缀建立索引
    • 只有索引列在比较表达式中单独出现才可以适用索引
    • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
    • 定位并删除表中的重复和冗余索引
    • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。
4.BufferPool

每次从磁盘中取出页之后，也不可能立马就放回去，那磁盘IO的开销就很大了，这就是BufferPool的出现了。

BufferPool的空间应当是这样子的：

1

控制块 | 控制块 | ... | 碎片 | 缓存页 | 缓存页 | ...

  • free-list：将所有的缓存页作对应的控制块作为结点放到链表中，unordered_map<表空间号+页号，缓存页>确立该页在不在缓冲区中。（好像LRU Cache的结构，LRU缓存也是哈希表+链表的组合）

  • flush-list：存储被修改了的缓存页的控制块，保证一致性。定时会从flush链表中刷新一部分数据到磁盘。

  • LRU-list：用于管理BUffer Pool中的缓存页，缓存池的空间是有限的。

    我们所知的最简单的LRU Cache当然不适用于mysql，那肯定是要优化的，LRU是淘汰最近最不常使用的，但想像加入访问频率过高，比如全表扫描的情况，那LRU链表在不停的变动并且达不到保存使用频率较高的后淘汰的目的。因此对其增加了一个改动：

    • 一部分存储频率很高的缓存页，称为young区域；另一部分存储频率不上很高的缓存页，称为old区域。
    • 初次加载存放在old头部，当一定时间之后（这是因为如果全表扫面，一个页面中有很多条数据，那么一段时间内这个页面能够被访问很多次），这个页面再次被访问，那么会移动到young头部。
5.InnoDB的统计数据
  • 基于磁盘的永久性统计数据
  • 基于内存的非永久性统计数据

（NULL值认为是不存在，还是完全相等，还是完全不等这个在mysql中有相应的候选值确立，用户可以自行选择，当然最好不要在索引中存放数据）

这里具体的原理就不谈了，其实是自己看完也没什么很大印象）。感觉跟这个题有一定的关系。[count(*) 和 count(1) 有什么区别？哪个性能最好？ 小林coding](https://xiaolincoding.com/mysql/index/count.html#哪种-count-性能最好)，这里面的结论是count(1)==count(*)>count(主键字段)>count(普通字段)。前两个相等且大于后面的是因为InnoDB会自动优化选择最简单的方式，同时只用于统计不会真正的访问相关的字段。另外每个区每个表中应该会有相应的头信息或者指针记录相应的数目，和统计某个字段一定是有区别的。
6.mysql单表查询
[MySQL 单表不要超过 2000W 行，靠谱吗？ 小林coding](https://xiaolincoding.com/mysql/index/2000w.html#实验)
7.事务的四个特性

事务的四个特性：

  • 原子性：undo log
  • 一致性：MVCC/锁
  • 隔离性：原子性+一致性+持久性
  • 持久性：redo log

事务的隔离级别：

  • 脏写：一个事务修改了另一个未提交事务修改过的数据
  • 脏读：一个事务读取了另一个未提交事务修改过的数据
  • 不可重复读：一个事务读到另一个已提交事务修改过的数据，并且其它事务对数据修改提交后，这个事务都能查到
  • 幻读：一个事务按照某个相同条件多次读取数据时，后读取到了之前没有读取的记录。

四种隔离级别导致发生的问题：脏写，脏读，不可重复读，幻读

8.MVCC

主要原理在于版本链和read view

  • 版本链也就是undo log中的roll pointer属性连接成一个链表，链表的头结点为最新值

  • Read view：

    • 读未提交：直接读取最新版本

    • 串行：加锁

    • 读已提交和可重复读：要保证读到已经提交的事务修改的记录，也就是要判断哪个版本是事务可见的。

      • 对于读已提交隔离级别：

      一般会再读取数据之前生成Read view，比如INSERT，DELETE，UPDATE。read view中有个活跃列表，在活跃列表之内的事务id的版本是读取不到的。因此解决了未提交的问题。

      • 对于可重复读隔离级别：

      只在第一次进行普通SELECT操作前生成一个READ view，之后的查询一直使用这个read view，就解决了不可重复度的问题。

9.undo log, redo log, binlog
[MySQL 日志：undo log、redo log、binlog 有什么用？ 小林coding](https://xiaolincoding.com/mysql/log/how_update.html)

（书上讲了四章，没怎么看懂…，看看小林coding了就）

  • redo log：把事务执行时对数据库的所有修改记录下来，便于系统崩溃重启后可以恢复事务的修改

  • undo log：实现事务回滚，保障原子性；实现MVCC关键因素之一

10.锁

MySQL在可重复读的隔离级别解决了幻读的问题

  • 读版本：MVCC（一致性读）；写：加锁。
  • 读写都采用加锁。
    • 读操作：S锁
    • 写操作：
      • delete：先在B+树种定位，再delete mark
      • insert ：存在隐式锁
      • update：1.存储空间未改变，X素直接修改2.存储空间改变，先X锁删除，再insert3.键值改变，需要先delete再insert

参考：

1.图解mysql - 小林coding

2.《mysql是怎样运行的》

CATALOG
    FEATURED TAGS
    algorithm C++ levelDB 操作系统 Blog go mysql coroutine 计算机网络 recreation
    FRIENDS