mysql常用知识点

MYSQL常用知识点

MySQL的结构

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用；5.7默认禁用，因为当对表进行修改后，这个表的缓存无法使用，需要重新查询）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。执行器： 执行语句，然后从存储引擎返回数据

• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

MYSQL执行流程

连接器

连接器层负责的权限检验仅针对用户认证和连接访问数据库权限的检查，对于表，字段以及触发器，事件等是执行器阶段

查询语句

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，
• 如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
• 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
• 优化器根据自己的优化算法选择语句的执行步骤
  • 先查询名字为张三的学生，然后寻找18岁的张三
  • 先查询18岁的学生，然后寻找姓名为张三的学生
    -权限校验，检查是否有selec、update等权限，有权限则调用数据库引擎接口，返回引擎的执行结果

更新语句

解析语句

MYSQL日志详解

MYSQL系统变量

MySQL 服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为 MySQL 系统变量，比如允许同时连入
的客户端数量用系统变量 max_connections 表示，表的默认存储引擎用系统变量 default_storage_engine 表
示，查询缓存的大小用系统变量 query_cache_size 表示， MySQL 服务器程序的系统变量有好几百条，我们就不
一一列举了。每个系统变量都有一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量的值。大多数的系统变量的值也可以在程序运行过程中修改，而无需停止并重新启动它。

对于大多数系统变量，都可以在服务器程序运行过程中动态修改而无序重启服务器

对于mysql服务端，我们希望不同的客户端连接服务器可以用不同的值，比如一个客户端A的默认引擎是MyISAM，一个客户端B的默认引擎是InnoDB，但是对于客户端来说，每个客户端私有一份系统变量会造成许多问题

• 比如连接数max_connections，最大查询缓存query_cache_size等不适合用私有于客户端。
• 客户端初始参数应该怎么配置。

对此MYSQL设计的时候对系统变量提出了作用范围，即

• GLOBAL :全局变量，对整个服务器端作用
• SESSION：回话变量，对单个客户端连接作用

mysql默认设置系统变量的作用范围是回话变量，省略了SESSION

MYSQL数据页的结构

一个数据页的大小为16KB，超出这个大小，需要创建一个新的数据页。
对于InnoDB引擎的数据页结构如图

一个数据页被分为七个部分，有些部分的占用字节是固定的，有些则是不确定。

Infimum + supremum

一个数据页会默认创建一个最小记录Infimum 和一个最大记录
supremum

User Records 和 Free Directory

User Records 和 Free Directory是二个部分，为什么要放到一起，因为数据页中刚刚开始并不存在User Records，当我们插入数据后，Mysql会从Free Directory中申请记录大小的空间，并划分到User Records去存储记录

记录的结构

对于一条记录，对应mysql表中的某一列，而这个记录比mysql显示中的多了一些的信息。下面以Compact行格式来示意，啥？不知道行格式是什么，重新回去看mysql是怎么运行的第四章

变长字段长度列表

mysql支持一些变长字段，会自动根据存储的内容变更需要的存储内容的字节，比如VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类

对于mysql来说，这时候这些变长字段的存储就需要考虑存储这些数据占用的字节数了，方便管理这些变长字段。

在Compact格式中，各字段的真实数据占用的字段存储在这个部分的开头，从而形成一个变长字段的长度列表。存储的顺序为各变长字段顺序的逆序 ，假设有c1 c2 c3 三个可变字段，占用的字节分别为01、03、04
那么在记录开头存储的就是040301、null值列表、等等

这个变长字段长度列表不一定只存储可变长字段类型，对于ASCII编码来说，一个字符对应一个字节，当这个表的编码是utf8时，我们知道存储一个字符会占用1-3个字节。这个时候，这个字段即时是char，不可变字段，也会通过变长字段长度列表存储真实占用的字节。

另外有一点还需要注意，变长字符集的 CHAR(M) 类型的列要求至少占用 M 个字节，而 VARCHAR(M) 却没有这个要
求。比方说对于使用 utf8 字符集的 CHAR(10) 的列来说，该列存储的数据字节长度的范围是10～30个字节。即
使我们向该列中存储一个空字符串也会占用 10 个字节，这是怕将来更新该列的值的字节长度大于原有值的字节
长度而小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有
的记录空间成为所谓的碎片。（这里你感受到设计 Compact 行格式的大叔既想节省存储空间，又不想更新
CHAR(M) 类型的列产生碎片时的纠结心情了吧。）

null值列表

当某个字段为空时，mysql不可能单纯的把null 这个值真的存储起来，这样很占空间。

所以方法和前面类似，用空字段的顺序的逆序，通过0和1进行表示

• 当二进制位为1时，表示这个列（字段）的值为null
• 当二进制位为0时，表示该列的值不为null

记录头信息

记录头信息由五个字节组成，共40个二进制位。这个可以说是最重要的一个地方了。mysql通过头信息中包含的几个部分，巧妙的把多个记录连接起来，并通过主键来排列。什么没有主键，mysql会生成一个虚拟主键的。
这部分拆解如图

名称	描述
预算位1	没有使用
预算位2	没有使用
delete_mask	标记记录是否删除
min_rec_mask	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	表示当前记录拥有的记录数
heap_no	表示当前记录在记录堆的位置信息
record_type	表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶子节点记录， 2 表示最小记录， 3表示最大记录
next_record	表示下一条记录的相对位置

• next_record 记录的是地址偏移量，表示的是从当前记录的真实数据到下一条记录的真实数据的地址偏移量 。即第一条记录next_record为12，那么从第一条记录的真实地址往后12位就是 第二条记录。本质上相当于一条链表

隐藏列

mysql会为每个记录添加一些列，具体如下：

列名	是否必须	占用空间	描述
row_id	否	6字节	行id，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

默认以用户设置的主键作为主键 ，如果没有主键则以一个 Unique为主键，如果没有 Unique则创建 row_id为主键。

Unique 唯一索引约束

Page Directory（页目录）

前面提到，每一条记录都有一个next_record，那么对于数据页中的记录，如何快速的搜索呢，暴力遍历当然可以，但是这样太慢了。想象一下你翻书的过程中，如何快速找到需要的内容。没错就是目录！

• 现在开始把记录分为几个组，首先规定，最小记录Infimum为一组，其他的记录以 4-8条记为一个分组，最大记录的分组包含1-8分组。
• 每个分组最后一条记录的头信息n_owned记录当前分组共有几条记录。还记得n_owned的描述吗，当前记录在当前记录堆的位置。
• 在页目录中插入每一个分组中最后一条记录的地址偏移量 相当于我们平时的书页，但注意这个并不是实际地址。每一个地址偏移量称为槽（英文名： Slot）

好了现在对页目录的逻辑和框架有了一个大概了解了

现在开始了解一下为什么是这样分组，n_owned的值有什么特别

• 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。

• 之后每插入一条记录，都会从 页目录 中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对
  应的记录的 n_owned 值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。

• 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一
  个5条记录。这个过程会在 页目录 中新增一个 槽 来记录这个新增分组中最大的那条记录的偏移量。

总结

1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做 数据页 。
2. 一个数据页可以被大致划分为7个部分，分别是
   • File Header ，表示页的一些通用信息，占固定的38字节。
   • Page Header ，表示数据页专有的一些信息，占固定的56个字节。
   • Infimum + Supremum ，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的 26 个字节。
   • User Records ：真实存储我们插入的记录的部分，大小不固定。
   • Free Space ：页中尚未使用的部分，大小不确定。
   • Page Directory ：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插
   • 入的记录越多，这个部分占用的空间越多。
   • File Trailer ：用于检验页是否完整的部分，占用固定的8个字节。
3. 每个记录的头信息中都有一个 next_record 属性，从而使页中的所有记录串联成一个 单链表 。
4. InnoDB 会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个 槽 ，存放在
   Page Directory 中，所以在一个页中根据主键查找记录是非常快的，分为两步：
   • 通过二分法确定该记录所在的槽。
   • 通过记录的next_record属性遍历该槽所在的组中的各个记录。
5. 每个数据页的 File Header 部分都有上一个和下一个页的编号，所以所有的数据页会组成一个 双链表 。
6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时
   对应的 LSN 值，如果首部和尾部的校验和和 LSN 值校验不成功的话，就说明同步过程出现了问题。

MYSQL索引

MYSQL引擎