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

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

1. MySQL 的 NULL 值会占用空间吗？
        Compact行格式中用NULL值列表标记值为NULL的列，不会存储在真实数据，NULL值列表至少占用1字节的空间，当表中所有字段为NOT NULL，不会有NULL值列表，节省1字节的空间
2. MySQL 怎么知道 varchar(n) 实际占用数据的大小？
        Compact行格式用 变长字段长度列表 存储变长字段实际占用数据的大小
3. varchar(n) 中 n 最大取值为多少？
        一行记录最大存储65535字节数据，包括了变长字段长度列表所占用的字节数和NULL值列表所占用的字节数和真实数据占用的字节数
        如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。（65535-2-1）
        如果有多个字段，保证所有字段的长度+.+. <= 65535
4. 行溢出后，MySQL 是怎么处理的？
        一个数据页存不了一条记录，将溢出的数据存放到溢出页
        Compact:
            记录的真实数据处存放该列的一部分数据，剩余数据放在溢出页，真实数据处用20字节存储指向溢出页的地址
        Compressed Dynamic:
            记录的真实数据处不存放该列的数据，只存储20字节的指针指向溢出页，实际数据都存储在溢出页

MySQL 的数据存放在哪个文件？

表名字.ibd文件里，这个文件也被称为独占表空间文件

mysql的数据保存在磁盘的哪个文件？不同存储引擎保存的文件不同。

以InnoDB（默认引擎）为例讨论

每创建一个数据库，都会在/var/lib/mysql/ 目录下创建一个以数据库名为名的目录，保存表结构和表数据文件都在这个目录里

数据库名：my_test
表名：t_order

[root@xiaolin ~]#ls /var/lib/mysql/my_test
db.opt           // 存储当前数据库的默认字符集和字符校验规则
t_order.frm       // t_order的表结构
t_order.ibd        // t_order的表数据

表空间文件结构

段、区、页、行组成

1. 行
    数据库表中的记录按行存放
2. 页
    InnoDB的数据按页为单位读取，当需要读一条记录时，以页为单位，将其整体读入内存
    默认每页16KB
    表中的记录存储在[数据页]里
3. 区
    B+树每层通过双向链表连接，若以页为单位分配存储空间，链表相邻的页的物理位置不是连续的，磁盘查询时有大量的随机I/O，非常慢
        解决：链表中相邻的页物理位置上页相邻
             按照区为单位进行分配存储空间，每个区大小为1MB，连续的64页被分为一个区，可以使用顺序I/O
4. 段
    索引段：存放B+树的非叶子节点的区的集合
    数据段：存放B+树的叶子节点的区的的集合
    回滚段：回滚数据的区的集合（事务隔离）

InnoDB行格式有哪些

行格式：一条记录的存储结构

Redundant、Compact、Dynamic、Compressed

Compact行格式

• 记录的额外信息

  • 变长字段长度列表

    varchar 变长：实际存储的数据的长度不固定
    在存储的数据时，把数据占用的大小存起来，存到[变长字段长度列表]里，读取数据的时候根据[变长字段长度列表]读取对应长度的数据
    

    变长字段长度列表的信息按照列的顺序 逆序 存放
    

    

    为什么变长字段长度列表的信息按照逆序存放？

    使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

    NULL值列表的信息也需要逆序存放

    当数据表没有变长字段时，行格式不会有变长字段长度列表

  • NULL值列表

    值为NULL的列存储到NULL值列表

    二进制位为1：该列值为NULL
    二进制位为0：该列值不为NULL
    

    

    1. 第一条记录  000     = 00
    2. 第二条记录  100     = 04
    3. 第三条记录  110     = 06
    （不足八位高位补0）
    

    当数据表的字段都定义成NOT NULL时，行格式不会有NULL值列表（至少占用1字节空间）

    （若一条记录有9条字段值都是NULL，会创建2字节…）

  • 记录头信息

    1. delete_mask：
            标识此条数据是否被删除，执行delete时不会真正删除记录。
    2. next_record：
            下一条记录的位置。指向下一条记录的记录头信息和真实数据之间的位置，向左读时记录头信息，向右读是真实数据
    3. record_type：
            表示当前记录的类型。
    

• 记录的真实数据

  • row_id
    • 建表时指定了主键/唯一约束列，就没有row_id。（非必需）
  • trx_id
    • 事务id，表示这个数据是由哪个事务生成（必需）
  • roll_pointer
    • 这条记录上一个版本的指针（版本）

  MVCC

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

一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

varchar(n) n代表最多存储的字符数量（注意是字符数量）

• 单字段情况

  • 只定义一个字段，varchar(n)

    • 计算n时，需要65535减去变长字段长度列表和NULL值列表占用的字节数

    • 分为三个部分存储：

      • 真实数据
      • 真实数据占用的字节数
      • NULL标识，如果不允许为NULL，不需要这部分

    • 举例：

      CREATE TABLE test ( 
      `name` VARCHAR(65535)  NULL
      ) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
      报错
      

      NULL值列表所占用的字节数？

      1字节

      变长字段长度列表所占用的字节数？

      = 所有变长字段长度占用的字节数之和

      每个变长字段的 变长字段长度 需要用多少字节表示?

      1. 如果变长字段允许存储的最大字节数<= 255，1字节表示
      2. 如果>=255，2字节表示

      这里为65535，字符集assci，代表允许存储的最大字节数为65535，2字节表示

      65535-2-1 = 65532

      若采用utf-8，一个字符最多需要三个字节，n=65532/3

• 多字段情况

  • 所有字段的长度+变长字段字节数列表所占用的字节数+NULL值列表所占用的字节数 <= 65535

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

mysql中磁盘和内存交互的单位是页，页一般为16kb，而一个varchar(n)类型的列最多存储65532字节，一些大对象TEXT、BLOB可能存储更多的数据，一个页可能存不了一条记录，发生行溢出，多的数据就会存到另外的溢出页

Compact行格式的行溢出的处理：

Compressed 和 Dynamic 的行溢出的处理：