数据库-mysql-小林coding-基础篇

本系列笔记为作者在跟随小林coding学习的时候做的笔记。感谢小林大大。

MySQL 执行流程

分为server层和存储引擎层

• Server 层负责建立连接、分析和执行 SQL。
• 存储引擎层负责数据的存储和提取

流程：

1. 连接器

连接数据库，MySQL 是基于 TCP 协议进行传输（TCP 三次握手）

1
2
3
4

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

验证用户名和密码，并读取权限，管理员修改的权限对已连接用户不起作用，权限只能应用于该用户下一次连接。

• 查看 MySQL 服务被多少个客户端连接:

1

show processlist

• 空闲连接

wait_timeout变量控制空闲连接最大时间

1

show variables like 'wait_timeout';

手动断开空闲连接kill connection + id

1

kill connection +6;

• 连接数限制

最大连接数由 max_connections 参数控制

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，

• 长连接占用内存问题

定期断开长连接或者客户端主动重置连接

2. 查询缓存

MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

3. 解析 SQL

由解析器完成

词法分析，语法分析

4. 执行 SQL

每条SELECT 查询语句流程主要可以分为下面这三个阶段：prepare 预处理阶段；optimize 优化阶段；execute 执行阶段；

• 预处理

检查语句中的表或者字段是否存在

将 select * 中的 * 符号，扩展为表上的所有列

• 优化

将 SQL 查询语句的执行方案确定下来

explain 命令会输出这SQL 语句的执行计划

CSDN AllenGd

执行计划中：

1. possible_key是可以选择的索引；
2. key指的是使用的索引，null值表示不使用索引；
3. Extra表示额外信息，using index表示覆盖索引，using index condition表示用到了索引下推优化，Using filesort表示文件排序效率很低，Using temporary表示使用临时表保存中间结果
4. rows是循环的次数，可以用于估计查询结果的记录数；
5. type表示数据扫描类型：All（全表扫描）；index（全索引扫描）；range（索引范围扫描）；ref（非唯一索引扫描）；eq_ref（唯一索引扫描）；const（结果只有一条的主键或唯一索引扫描），效率一次递增；
6. key_len是使用的索引的长度，可以用于判断联合索引使用的字段数

• 执行器

执行器和存储引擎都是一个记录一个记录地进行查询。

存储引擎给执行器发送的记录会在执行器中再检查是否符合条件然后发送给客户端（Server 层每从存储引擎读到一条记录就会发送给客户端，客户端会等查询语句查询完成后显示出所有的记录）。

二级索引B+树定位符合条件的主键后使用主键去主键索引查询完整记录的过程叫回表。

索引下推：减少二级索引（辅助索引）的回表操作，将 Server 层部分负责的事情，交给存储引擎层去处理。联合索引可以过滤的记录就不回表了。执行计划里的 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

MySQL 一行记录是怎么存储的

MySQL 的数据存放在哪个文件

• db.opt：存储当前数据库的默认字符集和字符校验规则
• 表名字.frm：表结构
• 表名字.ibd：表数据

表空间由段（segment）、区（extent）、页（page）、行（row）组成

1. 行（row）：记录
2. 页（page）：InnoDB 的数据是按「页」为单位来读写的。默认每个页的大小为 16KB。
3. 区（extent）：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
4. 段（segment）：表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。索引段：存放 B + 树的非叶子节点的区的集合；数据段：存放 B + 树的叶子节点的区的集合；回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了多版本并发控制（MVCC）利用了回滚段实现了多版本查询数据。

InnoDB 行格式有哪些

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

• Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
• Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
• Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

COMPACT 行格式

额外信息

1. 变长字段长度列表：

varchar， 顺序和后面的真是信息顺序相反，即逆序存放，因为指向记录的指针指的是记录头信息和真实数据直接的位置，向左就是记录头信息向右就是真实数据，使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率，同样的道理， NULL 值列表的信息也需要逆序存放。

另外NULL 是不会存放在行格式中记录的真实数据部分里的，所以这里不会存放值为null的变长字段（在NULL 值列表中标记）。

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了

2. NULL 值列表

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

• 值为1时，代表该列的值为NULL。
• 值为0时，代表该列的值不为NULL。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

「NULL 值列表」的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

3. 记录头信息

记录头信息中包含的内容很多，说几个比较重要的：

delete_mask ：标识此条数据是否被删除。

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 个字节。
• trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
• roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

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

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

单字段的情况

一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead（「变长字段长度列表」和 「NULL 值列表」）

多字段的情况

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

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

一些大对象如 TEXT、BLOB发生行溢出，多的数据就会存到另外的「溢出页」中。真实数据处用 20 字节存储指向溢出页的地址。

Compressed 和 Dynamic 这两个行格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。实际的数据都存储在溢出页中：