MySQL_基础篇

逻辑架构

如上图所示，MySQL的逻辑架构可分为四层，包括连接层、服务层、引擎层和存储层，各层的接口交互及作用如下所示：

• 连接层： 负责处理客户端的连接以及权限的认证。

• 服务层： 定义有许多不同的模块负责建立连接、分析和执行SQL，MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在服务层实现。

• 引擎层： 负责MySQL中数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

  我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

• 存储层： 负责将数据存储于设备的文件系统中。

---

MySQL运行机制

现在我们对服务层和存储引擎层有了一个简单认识，接下来，就详细说一条 SQL 查询语句的执行流程，依次看看每一个功能模块的作用。

1. 连接器：因为MySQL是基于TCP协议进行传输的，所以要三次握手建立连接。

2. 查询缓存：QueryCache，8.0版本已经将其删除不要了，所以在这里我们也不多说了。

3. 解析SQL：在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。

   解析器会做如下两件事情，做以下两件事的目的就是生成语法树，方便后续模块读取表名、字段、语句类型；

   • 第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

   • 第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

4. 执行SQL：经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

   • prepare 阶段，也就是预处理阶段；
     • 检查 SQL 查询语句中的表或者字段是否存在；
     • 将 select * 中的 * 符号，扩展为表上的所有列；
   • optimize 阶段，也就是优化阶段；经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。
     • 优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
   • execute 阶段，也就是执行阶段；经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

---

我们了解完MySQL 执行一条 select 查询语句期间的一个过程，再思考一下：MySQL 一行记录是怎么存储的？

大家都知道 MySQL 的数据都是保存在磁盘的，那具体是保存在哪个文件呢？

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。

InnoDB 是目前我们常用的存储引擎，也是 MySQL 目前默认的存储引擎。所以，我们就以 InnoDB 存储引擎来看一看。

首先，每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。

例如，有一个名为 my_test 的 database，该 database 里有一张名为 t_student 数据库表。进入 /var/lib/mysql/my_test 目录，可以看到，共有三个文件，这三个文件分别代表着：

• db.opt，存储当前数据库的默认字符集和字符校验规则。
• t_student.frm ，t_student 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• t_student.ibd，t_student 的表数据会保存在这个文件。这个文件也称为独占表空间文件。

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

1. 行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

2. 页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16KB，最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等]。数据表中的行记录是用「数据页」来管理的。要知道表中的记录存储在「数据页」里面就行。

3. 区（extent）

InnoDB 存储引擎是用 B+ 树来组织数据的。

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常耗时的。为此就让链表中相邻的页的物理位置也相邻，就可以使用顺序 I/O 了，在范围查询（扫描叶子节点）的时候性能就会很高。

具体来说就是：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

4. 段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

• 索引段：存放 B + 树的非叶子节点的区的集合；
• 数据段：存放 B + 树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合， MVCC就是 利用了回滚段实现了多版本查询数据。

常见面试题

• MySQL 的 NULL 值会占用空间吗？
  • MySQL 的 Compact 行格式中会用**「NULL值列表」**来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。
  • NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。
• MySQL 怎么知道 varchar(n) 实际占用数据的大小？
  • MySQL 的 Compact 行格式中会用**「变长字段长度列表」**存储变长字段实际占用的数据大小。
• varchar(n) 中 n 最大取值为多少？
  • 注意：MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。
  • 一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。
  • 计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。
  • 如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。
• 行溢出后，MySQL 是怎么处理的？
  • 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到**「溢出页」**中。
  • Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
  • Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。