MySQL（一）：数据库架构

数据运维 2024-03-28 爱可生开源社区 手机阅读

1. Server层（SQL执行流程）

连接器：建立连接，管理连接、校验用户身份

查询缓存：查询语句命中缓存则直接返回。实则基本无用，MySQL 8.0版本已删除

解析SQL：

  • 词法分析：分析输入字符串，识别关键字和非关键字
  • 语法分析：判断语句是否满足语法规则，没问题就构建语法树

执行SQL：

  • 预处理器：检查表、字段是否存在，扩展*为表上全部列
  • 优化器：选择查询成本最小的执行计划（命中多个索引，选择成本最小的索引，执行计划中Extra为Using index代表使用覆盖索引）
  • 执行器：根据执行计划执行SQL，从存储引擎中读取记录，Server层判断查询条件，返回给客户端
    • 第一次查询，执行器索引条件交给存储引擎，存储引擎定位符合条件的第一条记录
    • 接着，执行器再判断记录是否符合其他查询条件，如果符合则发送给客户端，不符合则跳过
    • PS：全表扫描不命中索引，所以就从全表的第一条数据开始扫描
    • PS：索引下推（MySQL 8.0之后）联合索引可以在命中第一个索引条件后不执行回表操作，而是判断是否满足第二个索引条件，如果满足再进行回表，否则跳过。在MySQL 8.0之前，命中第一个索引条件后就进行回表，但之后又判断不满足条件，浪费性能

2. 存储引擎

作用：负责数据的存储和提取

InnoDB：支持B+树索引，不支持Hash索引，支持Full-text索引

MyISAM：支持B+树索引，不支持Hash索引，支持Full-text索引

Memory：支持B+树索引，支持Hash索引，不支持Full-text索引

3. 数据存储格式（InnoDB存储引擎）

3.1 磁盘存储文件

文件存储位置：/var/lib/mysql/

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

一个表分为三个文件：

  • db.opt，用来存储当前数据库的默认字符集和字符校验规则

  • table_name.frm ，存放表结构。在 MySQL 中建立一张表都会生成一个.frm文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义

  • table_name.ibd，存放表数据。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）

    这个行为是由参数innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间

    从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的.ibd 文件里

表空间文件结构：由多个段组成，一个段内分多个区，一个区内分多个页，一个页中分多行。InnoDB按页读取数据，默认每个页大小为16 KB，也就是最多保证16 KB的连续存储空间

3.2 存储行格式（COMPACT行格式）

一条行记录分为两部分：额外信息和真实数据，记录头信息在两者中间。向左移动读取额外信息，向右移动读取真实数据

COMPACT.jpg

额外信息

  • 变长字段长度列表：varchar(n)类型使用，存储变长字段的真实数据实际占用的字节数

    • 如果字段定义存储的最大字节数小于等于255字节，则这个长度为1字节
    • 如果字段定义存储的最大字节数大于255字节，则这个长度为2字节
    • 1字节 = 8 位，如果变长字段列表的实际长度不足8位，则在前面补0
    • 存放的真实数据占用字节数按照列的顺序逆序存放，因为记录头信息在中间，便于额外信息和真实数据处于同一个CPU Cache Line中，提高缓存命中率

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

    • 如果该列的值为NULL，则二进制位的值为1
    • 如果该列的值不为NULL，则二进制位的值为0
    • 不足1字节（8位）的则在前面补0
    • 如果设计时没有非空字段，则不需要NULL值列表，至少节省1字节的空间

记录头信息

  • delete_mask：标识此条数据是否被删除。执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  • record_type：表示当前记录的类型。0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

真实数据

记录的真实数据.jpg

真实数据除了定义的字段，还有三个隐藏字段

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

3.3 varchar(n)最大长度

举例：如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ASCII（一个字符占用一个字节）。varchar(n) 中 n 最大取值为 65532。

计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n)中 n 最大值时，需要减去这两个列表所占用的字节数。

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