一、先说结论在 InnoDB 中一张表本质上是一棵以主键组织的BTree 聚簇索引表 └── 聚簇索引 BTree ├── 非叶子节点保存索引键和页指针 └── 叶子节点保存完整的一行数据因此一行数据不是独立地“放在某个文件里”而是作为一条索引记录存储在聚簇索引的叶子页中。二级索引则只保存索引列和对应的主键值。二、从数据页到记录InnoDB 按照“页”管理磁盘和内存中的数据。默认情况下一个 InnoDB 页是16KB也可以在初始化 MySQL 实例时配置为4KB、8KB、16KB、32KB或64KB。同一个实例中的 InnoDB 表空间使用相同的页大小。一个叶子页可以简化理解为┌──────────────────────────────┐ │ 页头等管理信息 │ ├──────────────────────────────┤ │ 记录1 │ │ 记录2 │ │ 记录3 │ │ ... │ ├──────────────────────────────┤ │ 剩余空间 │ ├──────────────────────────────┤ │ 页目录等管理信息 │ └──────────────────────────────┘一个页通常包含多行记录。行越小一个页能放下的记录越多查询时需要读取的数据页通常就越少。三、一行记录由什么组成假设有表CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT NULL, status TINYINT NOT NULL, bio TEXT ) ENGINE InnoDB;插入一行INSERT INTO users VALUES (1, 张三, 20, 1, NULL);在现代 MySQL 常用的DYNAMIC或COMPACT行格式中一条聚簇索引记录可以简化为┌────────────────────┐ │ 变长字段长度列表 │ ├────────────────────┤ │ NULL值位图 │ ├────────────────────┤ │ 5字节记录头 │ ├────────────────────┤ │ 用户定义的字段数据 │ ├────────────────────┤ │ DB_TRX_ID6字节 │ ├────────────────────┤ │ DB_ROLL_PTR7字节 │ └────────────────────┘这是便于理解的简化表示。实际字节排列还与行格式、字段定义和索引类型有关。DYNAMIC行格式沿用了COMPACT的基本记录结构同时改进了长字段的页外存储。四、变长字段长度列表对于VARCHAR、VARBINARY等变长字段InnoDB需要知道每个字段实际占用了多少字节。例如name VARCHAR(20)VARCHAR(20)表示最多保存 20 个字符但并不是每行固定占用 20 个字符的空间。如果保存张三在utf8mb4编码下这两个汉字通常占用 6 个字节。因此记录中会保存name实际数据6字节 长度信息记录实际字节长度大多数变长字段的长度信息占1或2字节取决于字段最大长度、实际长度以及是否使用页外存储。所以VARCHAR按实际数据长度存储并额外保存长度信息 CHAR更接近固定长度存储但还受到字符集和尾随空格规则影响五、NULL 值位图对于允许为NULL的字段InnoDB 不会给每个字段都保存一个字符串NULL而是用位图表示。例如age INT NULL, bio TEXT NULL这两个字段都允许为空因此需要两个二进制位age是否为NULL0或1 bio是否为NULL0或1如果bio是NULLNULL位图标记bio为NULL bio字段数据不再占用实际数据空间需要注意bio NULL和bio 不一样。NULL通过 NULL 位图表示没有字段内容空字符串不是 NULL需要保存长度为0的字段信息如果索引中有N个可空字段NULL 位图占用CEILING(N / 8) 字节例如有 916 个可空字段就需要 2 字节。六、记录头COMPACT和DYNAMIC格式的每条索引记录包含一个5字节的固定记录头。记录头中保存的是 InnoDB 管理记录所需的信息例如记录是否被删除记录在页中的组织信息下一条记录的位置记录类型当前记录属于第几层等它并不是用户定义的字段但每条索引记录都需要这些管理信息。(dev.mysql.com)七、用户字段数据接下来是用户定义的非NULL字段值id name age status bio固定长度字段通常按照对应类型占用空间例如BIGINT 8字节 INT 4字节 TINYINT 1字节因此示例记录中的部分数据可以简化为id 1 约8字节 name 张三 约6字节另有长度信息 age 20 约4字节 status 1 约1字节 bio NULL 通过NULL位图表示实际占用空间还包括记录头、长度列表、NULL 位图和 InnoDB 隐藏字段。八、InnoDB 的隐藏字段聚簇索引记录除了用户定义的字段还包含两个重要的隐藏字段。DB_TRX_ID占用6字节保存最后一次插入或更新这条记录的事务信息。它与 InnoDB 的 MVCC 多版本并发控制有关。DB_ROLL_PTR占用7字节指向与这条记录相关的 Undo Log 信息。当其他事务需要查看旧版本时InnoDB 可以沿着 Undo 信息构建之前的记录版本。简化理解当前记录 │ └── DB_ROLL_PTR ↓ 上一个版本 ↓ 更早版本因此InnoDB 所说的“一行数据”不只有业务字段还携带事务和版本管理信息。(dev.mysql.com)九、没有主键会怎样InnoDB 需要一个键来组织聚簇索引选择顺序大致是使用显式定义的主键。没有主键时选择第一个所有字段都为NOT NULL的唯一索引。两者都没有时InnoDB 创建隐藏聚簇索引并为每行生成6字节的DB_ROW_ID。因此建议 InnoDB 表显式定义主键id BIGINT PRIMARY KEY否则 InnoDB 仍然会在内部创建隐藏主键只是开发者无法直接使用它。十、长字段怎么存储假设有字段content TEXT它不一定完全存储在当前记录中。当记录过大时InnoDB可能把长字段内容放到单独的溢出页中聚簇索引记录 ┌───────────────────┐ │ id │ │ title │ │ content的20字节指针│──────┐ └───────────────────┘ │ ↓ ┌──────────────┐ │ Overflow Page│ │ content内容 │ └──────────────┘现代 MySQL 默认通常使用DYNAMIC行格式。它可以把较长的VARCHAR、VARBINARY、TEXT和BLOB值完全放到页外聚簇索引记录中保留一个20字节指针。但不是所有TEXT都必然存到页外。InnoDB会根据字段长度、整行大小和页面空间决定较短的值通常仍然直接保存在记录中。十一、二级索引记录不是完整的一行假设建立索引CREATE INDEX idx_status ON users(status);聚簇索引叶子节点保存完整行id name age status bio 事务隐藏字段而二级索引的叶子记录主要保存status 主键id结构大致是二级索引 idx_status (status1, id1) │ │ 通过主键查聚簇索引 ↓ 聚簇索引 (id1, name张三, age20, status1, ...)所以执行SELECT name FROM users WHERE status 1;可能需要查询idx_status得到主键id。使用id查询聚簇索引。从完整记录中取得name。这就是回表。如果索引改为CREATE INDEX idx_status_name ON users(status, name);查询需要的status、name和主键都在二级索引记录中就可能直接返回结果形成覆盖索引。
一、先说结论在 InnoDB 中一张表本质上是一棵以主键组织的BTree 聚簇索引表 └── 聚簇索引 BTree ├── 非叶子节点保存索引键和页指针 └── 叶子节点保存完整的一行数据因此一行数据不是独立地“放在某个文件里”而是作为一条索引记录存储在聚簇索引的叶子页中。二级索引则只保存索引列和对应的主键值。二、从数据页到记录InnoDB 按照“页”管理磁盘和内存中的数据。默认情况下一个 InnoDB 页是16KB也可以在初始化 MySQL 实例时配置为4KB、8KB、16KB、32KB或64KB。同一个实例中的 InnoDB 表空间使用相同的页大小。一个叶子页可以简化理解为┌──────────────────────────────┐ │ 页头等管理信息 │ ├──────────────────────────────┤ │ 记录1 │ │ 记录2 │ │ 记录3 │ │ ... │ ├──────────────────────────────┤ │ 剩余空间 │ ├──────────────────────────────┤ │ 页目录等管理信息 │ └──────────────────────────────┘一个页通常包含多行记录。行越小一个页能放下的记录越多查询时需要读取的数据页通常就越少。三、一行记录由什么组成假设有表CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT NULL, status TINYINT NOT NULL, bio TEXT ) ENGINE InnoDB;插入一行INSERT INTO users VALUES (1, 张三, 20, 1, NULL);在现代 MySQL 常用的DYNAMIC或COMPACT行格式中一条聚簇索引记录可以简化为┌────────────────────┐ │ 变长字段长度列表 │ ├────────────────────┤ │ NULL值位图 │ ├────────────────────┤ │ 5字节记录头 │ ├────────────────────┤ │ 用户定义的字段数据 │ ├────────────────────┤ │ DB_TRX_ID6字节 │ ├────────────────────┤ │ DB_ROLL_PTR7字节 │ └────────────────────┘这是便于理解的简化表示。实际字节排列还与行格式、字段定义和索引类型有关。DYNAMIC行格式沿用了COMPACT的基本记录结构同时改进了长字段的页外存储。四、变长字段长度列表对于VARCHAR、VARBINARY等变长字段InnoDB需要知道每个字段实际占用了多少字节。例如name VARCHAR(20)VARCHAR(20)表示最多保存 20 个字符但并不是每行固定占用 20 个字符的空间。如果保存张三在utf8mb4编码下这两个汉字通常占用 6 个字节。因此记录中会保存name实际数据6字节 长度信息记录实际字节长度大多数变长字段的长度信息占1或2字节取决于字段最大长度、实际长度以及是否使用页外存储。所以VARCHAR按实际数据长度存储并额外保存长度信息 CHAR更接近固定长度存储但还受到字符集和尾随空格规则影响五、NULL 值位图对于允许为NULL的字段InnoDB 不会给每个字段都保存一个字符串NULL而是用位图表示。例如age INT NULL, bio TEXT NULL这两个字段都允许为空因此需要两个二进制位age是否为NULL0或1 bio是否为NULL0或1如果bio是NULLNULL位图标记bio为NULL bio字段数据不再占用实际数据空间需要注意bio NULL和bio 不一样。NULL通过 NULL 位图表示没有字段内容空字符串不是 NULL需要保存长度为0的字段信息如果索引中有N个可空字段NULL 位图占用CEILING(N / 8) 字节例如有 916 个可空字段就需要 2 字节。六、记录头COMPACT和DYNAMIC格式的每条索引记录包含一个5字节的固定记录头。记录头中保存的是 InnoDB 管理记录所需的信息例如记录是否被删除记录在页中的组织信息下一条记录的位置记录类型当前记录属于第几层等它并不是用户定义的字段但每条索引记录都需要这些管理信息。(dev.mysql.com)七、用户字段数据接下来是用户定义的非NULL字段值id name age status bio固定长度字段通常按照对应类型占用空间例如BIGINT 8字节 INT 4字节 TINYINT 1字节因此示例记录中的部分数据可以简化为id 1 约8字节 name 张三 约6字节另有长度信息 age 20 约4字节 status 1 约1字节 bio NULL 通过NULL位图表示实际占用空间还包括记录头、长度列表、NULL 位图和 InnoDB 隐藏字段。八、InnoDB 的隐藏字段聚簇索引记录除了用户定义的字段还包含两个重要的隐藏字段。DB_TRX_ID占用6字节保存最后一次插入或更新这条记录的事务信息。它与 InnoDB 的 MVCC 多版本并发控制有关。DB_ROLL_PTR占用7字节指向与这条记录相关的 Undo Log 信息。当其他事务需要查看旧版本时InnoDB 可以沿着 Undo 信息构建之前的记录版本。简化理解当前记录 │ └── DB_ROLL_PTR ↓ 上一个版本 ↓ 更早版本因此InnoDB 所说的“一行数据”不只有业务字段还携带事务和版本管理信息。(dev.mysql.com)九、没有主键会怎样InnoDB 需要一个键来组织聚簇索引选择顺序大致是使用显式定义的主键。没有主键时选择第一个所有字段都为NOT NULL的唯一索引。两者都没有时InnoDB 创建隐藏聚簇索引并为每行生成6字节的DB_ROW_ID。因此建议 InnoDB 表显式定义主键id BIGINT PRIMARY KEY否则 InnoDB 仍然会在内部创建隐藏主键只是开发者无法直接使用它。十、长字段怎么存储假设有字段content TEXT它不一定完全存储在当前记录中。当记录过大时InnoDB可能把长字段内容放到单独的溢出页中聚簇索引记录 ┌───────────────────┐ │ id │ │ title │ │ content的20字节指针│──────┐ └───────────────────┘ │ ↓ ┌──────────────┐ │ Overflow Page│ │ content内容 │ └──────────────┘现代 MySQL 默认通常使用DYNAMIC行格式。它可以把较长的VARCHAR、VARBINARY、TEXT和BLOB值完全放到页外聚簇索引记录中保留一个20字节指针。但不是所有TEXT都必然存到页外。InnoDB会根据字段长度、整行大小和页面空间决定较短的值通常仍然直接保存在记录中。十一、二级索引记录不是完整的一行假设建立索引CREATE INDEX idx_status ON users(status);聚簇索引叶子节点保存完整行id name age status bio 事务隐藏字段而二级索引的叶子记录主要保存status 主键id结构大致是二级索引 idx_status (status1, id1) │ │ 通过主键查聚簇索引 ↓ 聚簇索引 (id1, name张三, age20, status1, ...)所以执行SELECT name FROM users WHERE status 1;可能需要查询idx_status得到主键id。使用id查询聚簇索引。从完整记录中取得name。这就是回表。如果索引改为CREATE INDEX idx_status_name ON users(status, name);查询需要的status、name和主键都在二级索引记录中就可能直接返回结果形成覆盖索引。
