Mysql原理-基础

最新推荐文章于 2024-10-16 15:26:08 发布

野猪杀手X

最新推荐文章于 2024-10-16 15:26:08 发布

阅读量126

点赞数

分类专栏： Mysql基础文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/dingxiao123/article/details/130333295

版权

Mysql基础专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1. Mysql 基础

笔记总结参考自小林的mysql文档资料：https://www.xiaolincoding.com/

1.1 执行一条 select 语句，期间发生了什么？

Mysql 执行流程

先来一个上帝视角图，下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块。

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

接下来，就详细说一条 SQL 查询语句的执行流程，依次看看每一个功能模块的作用。

第一步：连接器

如果你在 Linux 操作系统里要使用 MySQL，那你第一步肯定是要先连接 MySQL 服务（连接的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的），然后才能执行 SQL 语句，普遍我们都是使用下面这条命令进行连接：

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

连接器的工作流程如下：

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

1.如何查看 MySQL 服务被多少个客户端连接了？

通过指令 show processlist 查看

2.空闲连接会一直占用着吗？

不会，因为存在空闲连接的最大空闲时长，查看指令为 show variables like 'wait_timeout'，默认是 28880 秒

3.MySQL 的连接数有限制吗？

有，最大连接数由 max_connections 参数控制，查看指令为 show variables like 'max_connections'，默认151

第二步：查询缓存

连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

如果一个表被更新，那么它的查询缓存就会被清空，所以查询缓存很鸡肋。Mysql 8.0 之后就直接将查询缓存删除了。

第三步：解析 SQL

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

解析器会做如下两件事情。

**词法分析：**MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树。这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
**语法分析：**根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。

注意，表不存在或者字段不存在，并不是在解析器里做的。解析器只负责构建语法树和检查语法，但是不会去查表或者字段存不存在。

第四步：执行 SQL

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

prepare 阶段，也就是预处理阶段；
optimize 阶段，也就是优化阶段；
execute 阶段，也就是执行阶段；

预处理器

预处理阶段做的事情：

检查 SQL 查询语句中的表或者字段是否存在，如果不存在则报错；
将 select * 中的 * 符号，扩展为表上的所有列；

优化器

经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

假如查询语句为：select * from product where id = 1，id为主键的话，这条指令就使用主键索引。在查询语句前加上 explain 命令，就会输出这条 SQL 的执行计划，执行计划中的 key 就表示执行过程中使用了哪个索引。

执行器

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

执行器与存储引擎交互，存储引擎与磁盘交互。

接下来，用三种方式执行过程，跟大家说一下执行器和存储引擎的交互过程：

主键索引查询：查询语句用了主键索引查询。存储引擎定位符合条件的第一条记录（数据），返回给执行器，执行器在判断其他条件是否符合，符合则返回给客户端一条数据，然后重复过程。
全表扫描：查询语句用了没有用索引。存储引擎读取表中的第一条记录（数据），返回给执行器判断条件是否符合，符合则返回给客户端一条数据，然后重复过程。
索引下推：

详细见原文。

总结

执行一条 SQL 查询语句，期间发生了什么？

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

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

1 MySQL 的数据存放在哪个文件？（了解即可，重点为黄字）

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。

查看 Mysql 数据库的文件存放在哪个目录的指令：show variables like 'datadir'; 。如果创建了一个数据库为 test_db，那么进入该数据库存放目录可以看到 3 个文件：

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 文件。

注意：MySQL8.0中不再单独提供 .frm 和 .opt，而是合并在 .ibd 文件中。

现在我们知道了一张数据库表的数据是保存在「表名字.ibd 」的文件里的，这个文件也称为独占表空间文件。

表空间文件的结构是怎么样的？

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

1、行（row）

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

2、页（page）

InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间（也就是一页的数据在存储空间是连续的）。页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的

3、区（extent）

连续页（物理连续）组成的空间，大小固定1MB。由连续页组成区，这样就可以使用顺序 I/O 了，那么在范围查询（扫描 B+ 树叶子节点）的时候性能就会很高。

总结：由物理连续的页组成，方便通过顺序 IO 进行范围查询

4、段（segment）

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

索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，之前讲事务隔离的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

2 InnoDB 行格式有哪些？

行格式（row_format），就是一条记录的存储结构。InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。Redundant 现在基本上没人用了；Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

3 COMPACT 行格式长什么样？

先跟 Compact 行格式混个脸熟，它长这样：

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

记录的额外信息

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

变长字段长度列表：记录该行数据中 varchar 字段的字节数。因为 varchar 是边长的，实际存储的数据的长度（大小）不固定。当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了。举例如下：

1.「变长字段长度列表」的信息要按照逆序存放，如上第一条记录，03 在 01 前面。

2.并不是每个数据库表的行格式都有「变长字段字节数列表」，当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了。
**NULL 值列表：**表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。NULL 值列表必须用整数个字节的位表示（1字节8位）。举例如下：

对于第三条数据：

最终 3 条记录的 NULL 值列表如下：

当数据表在创建时字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。
**记录头信息：**记录头信息中包含的内容很多，我就不一一列举了，这里说几个比较重要的：
- delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
- next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
- record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。见2.2的1
trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

MVCC 机制会讲 trx_id 和 roll_pointer 的作用，非常重要。

4 varchar(n) 中 n 最大取值为多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，一行记录总长度最大为 65535 字节。

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。因此要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

单字段情况：即表中只有一个 varchar 字段（允许为null）。对于 ASCII 码字符集（一个字符占一个字节），varchar(n) 中 n 最大为：n=65535 - 2（变长字段长度列表） - 1（null值列表）= 65532

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

最大长度(最大字符数) = （行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数） / 字符集单字符最大字节数。有余数时向下取整。

注意：这里一行记录没有包括记录头信息、trx_id 和 roll_ptr，没搞清楚为什么？是不是没有事务操作就不会有 trx_id 和 roll_ptr，但是没有记录头信息我不是很理解？？

varchar(20)，指的是20字符

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

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。