MySQL学习[1] ——MySQL基础

一、MySQL基础

1.1 MySQL 执行流程是怎样的？

以执行一条 SQL 查询语句的流程为例，如下图所示：

MySQL可以分为两层：Server层和存储引擎层。其中：

• Server层负责管理连接、分析和执行SQL。包括连接器、解析器、预处理器、优化器、执行器、各种内置函数、各种功能等都是在Server层实现。
• 存储引擎层负责数据的存储和提取。支持InnoDB、MylSAM、Memory等多个存储引擎，默认的是InnoDB。

对于一条SQL查询语句，它的步骤包括以下流程：

第一步：建立连接

第一步肯定是要先连接 MySQL 服务，然后才能执行 SQL 语句。连接命令 ：

# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h {ip} -u {user} -p

MySQL服务器中由Server层的**「连接器」**管理这部分工作，经过 TCP 三次握手成功后，连接器会验证用户输入的用户名和密码，并获取用户的权限，后续用户在此链接中的所有操作都会基于这个权限进行判断（就算中途管理员修改了用户的权限，也不会影响本次连接）。

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，一般是使用长连接。因此可能会存在长连接占用内存的问题，MySQL中有两种解决方案：

• 定期断开长连接：MySQL有一个wait_timeout参数，由Server层定期清理长时间“空闲”的连接；
• 客户端主动重置连接：当客户端执行了一个很大的操作后，会重置连接，达到释放内存的效果。

总结一下第一步中连接器的工作：

• 与客户端进行 TCP 三次握手建立连接；
• 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
• 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

第二步：查询缓存

建立连接后，客户端就可以向MySQL服务发送SQL语句了，对于查询语句，MySQL会先去从**查询缓存（ Query Cache ）**中查找数据，看是否能够命中。若命中，则可以直接读取缓存并返回。若没有命中，则继续向下查询，并将结果加入到缓存中。

然而，对于更新频繁的表，查询缓存会被经常情况，所以导致命中率很低，因此在MySQL 8.0 版本这一步就被省略了，不会再查询缓存。

第三步：解析SQL

当没有使用缓存时则需要去具体执行SQL语句，在执行之前需要对SQL语句进行解析，由Server层的**「解析器」**完成。解析过程分为两步：

1. 词法分析

   解析器会根据输入的字符串识别关键字，如SELECT、FROM等。

   关键字	非关键字	关键字	非关键字
   select	username	from	userinfo

2. 语法分析

   语法解析器会根据语法规则，判断SQL语句是否合法，构建出SQL语法树，便于后面执行

第四步：执行SQL

解析完成后，一般会需要验证用户权限（由授权管理模块负责），若验证通过，则进入执行SQL语句的流程，可以分为下面三个阶段：

1. 预处理阶段：预处理器

此阶段主要有两个任务：

• 判断查询的表或者字段是否存在；
• 将select * 中的 * 符号扩展为表上的所有列

2. 优化阶段：优化器

「优化器」的作用是将SQL语句的执行方案确定下来，比如基于查询成本该使用那个索引，决定各个表的连接顺序等。

要想知道优化器选择了哪个索引，我们可以在查询语句**最前面加个 explain 命令**，这样就会输出这条 SQL 语句的执行计划

3. 执行阶段：执行器

确定了执行方案后，「执行器」就开始真正执行语句了，在此过程中「执行器」会和存储引擎交互来查询数据，并返回给客户端。

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

1.2.1 首先，MySQL的数据保存在哪个文件？ 是如何保存的？

MySQL的存储行为是由存储引擎实现的，不同存储引擎保存的文件不同。InnoDB 是常用的存储引擎，也是 MySQL 默认的存储引擎。因此这里就InnoDB为例，对于每个数据库都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。

在这个目录里一共会存在三个文件：

• db.opt，用来存储当前数据库的默认字符集和字符校验规则。
• t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

所以，一张数据库表的数据是保存在**「 表名字.ibd 」的文件里的，这个文件也称为独占表空间文件。这个表空间的结构**如下图所示：

• 行（row）

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

• 页（page）

  记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

• 区（extent）

  InnoDB 存储引擎是用 B+ 树来组织数据的，B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

  解决这个问题也很简单，就是让**链表中相邻的页的物理位置也相邻。为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配**。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

• 段（segment）

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

  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合。

1.2.2 InnoDB 行格式 —— COMPACT

行格式（row_format），就是一条记录的**存储结构**。

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。重点介绍 Compact 行格式。

一条完整的记录分为**「记录的额外信息」和「记录的真实数据」**两个部分。

😊 记录的额外信息

记录的额外信息包含 3 个部分：变长字段长度列表、NULL值列表、记录头信息。

• 变长字段长度列表

  由于对于varchar是变长的，实际存储的数据大小不固定。因此需要把大小也记录下来，也就是村早这个变长字段长度列表中。

• NULL值列表

  表中的某些列可能会存储NULL值，如果直接存放NULL值到真实数据中会比较浪费空间，而是把这些值为 NULL 的列存储到 NULL值列表中，则**每个允许NULL值列对应一个二进制位（bit，这样就实现了一个NULL值只占用1bit）**，二进制位按照列的顺序逆序排列：

  • 二进制位的值为1时，代表该列的值为NULL。
  • 二进制位的值为0时，代表该列的值不为NULL。

  注意：NULL 值列表**必须用整数个字节的位表示（1字节8位）**，如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

• 记录头信息

  是一些标记字段，用来表示当前记录的类型、数据是否被删除（不会立即删除，而是加标记）、下一条记录的位置（因为是双向链表组织的）

😊 记录的真实数据

记录真实数据部分有三个隐藏字段，分别为：row_id、trx_id、roll_pointer：

• row_id：如果没有指定主键或唯一约束，InnoDB会额外添加一个row_id字段，占用6个字节；
• trx_id：事务id，标识数据是哪个事务生成的，占用6个字节；
• roll_pinter：指向这个记录上一个版本的指针，占用7个字节。

1.3 varchar(n)中n最大取多少？

varchar(n) 字段类型的 n 代表的是**最多存储的字符数量**。

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节，其中包括「变长字段长度列表」和 「NULL 值列表」。

因此n最大取多少，取决于这个字段字符集中的字符所占用的字节大小：

字符数量 = （65535 - 「变长字段长度列表」字节数量 - 「NULL 值列表」字节数量）/ 单个字符所占用的字节数

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

1.4 MySQL是怎么知道varchar(n)实际占用大小的？

MySQL中每一行记录中会有一个变长字段长度列表，这个列表中记录了这个记录中这个变长字段的实际大小。

1.5 MySQL中的NULL值是怎么存放的？

MySQL会使用一个NULL值列表来存储NULL值，而不是把NULL值存在记录的真实数据中。每个可能含有NULL值的列都在NULL值列表中对应一个bit（逆序排列），如果这个bit值为1，则说明这个列的值是NULL，否则不是NULL，这样可以节省内存。如果所有的字段都不含NULL，则不会有这个NULL值列表。

1.6 行溢出后，MySQL 是怎么处理的？

MySQL中磁盘和内存交换的本质是页，一个页大小为16K，即16384字节。而MySQL中varchar最大可以是65535字节，一些大对象可能存储更多的数据，此时一个页可能存不了一条记录，会发生**行溢出，多余的数据会存到另外的「溢出页」中**。

Compact行格式在发生行溢出时，在记录的真实数据处只会保存一部分的数据，通过留出**20字节的空间保存溢出页的地址，从而可以找到剩余数据所在的页，即溢出页（单个溢出页内存不足时则通过链式）**，如下图所示：

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中，看起来就像下面这样：

资料参考

内容大多参考自：图解MySQL介绍 | 小林coding (xiaolincoding.com)