innodb行格式

一、基础知识记录

innodb以页作为内存和磁盘交互的基本单位，innodb的页大小一般为16kb，也就是说一般情况下一次最少从磁盘读取16kb的内容到内存，一次最少把16kb的内容刷新到磁盘中。

一个页里有包含了多条数据，那么每条数据的格式是什么样的呢？

innodb一共有四种行格式，分别是：compact、redundant、dynamic、compressed，本文主要记录compact行格式，其他的行格式大同小异。

二、Compact行格式

1、问题引入

为了方便的说明行格式各个部分的作用，以一张表test为例

create table test（
 c1 varchar(10),
 c2 varchar(10) not null,
 c3 char(10),
 c4 varchar(10),
）

• c1和c2和c4为变长字段，最多存储10个字符
• c2字段不能为空
• c3字段为定长字段

思考：我们向数据库插入一条数据，那么这条数据在磁盘上的存储格式是什么样的呢？话不多说，直接上图。

可以看到大致分为两个大块：[记录的额外信息]和[记录的真实数据]

2、记录的额外信息

①、变长字段宽度列表

• c1和c2和c4为变长字段，字段的长度是不固定的，所以说我们在每条记录的前面把这些变长字段所占用的字节数给记录下来，这就是变长字段宽度列表的作用
  • 比如我们插入一条记录insert into test(c1,c2,c3,c4) value(a,bb,ccc,dddd)，那么在[变长字段宽度列表]中就会存储c1、c2和c4字段的长度，分别是1，2，4（注意：是逆序存放）
  • 注意：如果某个变长字段是空的，那么在[变长字段宽度列表]中是不会存储他的长度的，比如insert into test(c2,c3,c4) value(bb,ccc,dddd)，c1字段为空，那么[变长字段宽度列表]就不会存储c1字段的长度。如果不存储变长字段值为空的列，那么MySQL如何知道该如何读取数据呢？这就涉及到了[空值列表]
• 如果某个表中没有变长字段，那么[变长字段宽度列表]也不存在了

②、空值列表

• 上面说到在[变长字段宽度列表]中不会存储为空值的变长字段的长度（为了节省空间），那么如何区分一行数据中哪些字段是空的呢？这就是[空值列表]的作用。在空值列表中记录了这行记录中所有为空的列是否为空（即记录了可以为空的列是否为空的标识，像主键列、not null 修饰的列便不会被记录）
• 插入一行数据前，MySQL先检查表的结构，查看哪些列可以为空，然后对这行数据中这些可以为空的列，检查他们的值是否为空，如果为空则用 1 标识，如果不为空则用 0 标识
  • 比如：insert into test(c1,c2) value(a,bb)，这条数据只有c1，c2列不为空，那么在[空值列表]中便会记录011
• 如果某个表中没有可为空的字段，那么[空值列表]也不存在了

③、记录头信息

• 记录头信息主要记录着这条数据的一些信息，比如最常见的几个delete_flag、record_type、next_record、n_owned

  

• delete_flag 标识该条记录是否已经被删除

  • 我们知道，当我们使用delete语句删除数据时，数据并不会立即从磁盘上删除，而是这条记录的delete_flag先被标记为1，表示该记录已经被删除了（当然 truncate和drop语句就是直接删除了）。

• record_type 表示该条记录的类型（在innodb数据页中包含了很多条记录，这些记录的类型可能会不一样）

  • 0 表示普通记录
  • 1 表示b+树非叶子节点的目录项记录
  • 2 表示infimum记录（即一页中最小的记录）
  • 3 表示supremum记录（即一页中最大的记录）

• next_record 表示指向下一条记录的地址指针（innodb中可以通过一条记录找到下一条记录就是通过这个指针实现的）

• n_owned 在innodb中一个页面的记录会被分为若干个组，每个组都有一个”带头大哥“，其余的都是”小弟“。”带头大哥“记录的n_owned 代表着该组中所有记录的条数，”小弟“的 n_owned 都为0

  • 这个和innodb的页结构有关，需要从page角度考虑。

3、记录的真实数据

①、row_id

• 如果建表时没有指定主键，并且表中又找不出非空且不重复的列的话，那么MySQL会自动为该表生成一个隐藏的有序的列作为主键列，这个列就是row_id，用6个字节表示（所以该ID能表示的范围就是1 ~ 2 ^48 ）。
• 如果有指定主键，那么MySQL的行记录中不会有这一列。

②、trx_id

• 记录着最近修改这条数据的事务ID

③、roll_pointer

• 主要是为innodb的mvcc生成版本连使用

④、其他列数据

后面的就是存放我们具体插入的列的数据了，没有什么特殊的

三、其他

1、tyint(1) 能表示的范围是多少

这里的1表示的是一个字节的范围，意思就是：-128 ~ 127 范围

2、varchar(m) 能存放多少个字符或汉字

m表示的是能存放m个字符和字符集有关，假如字符集是utf8那么varchar(m)占用的最大空间就是 3 * m个字节（因为utf8表示一个字符使用 1 ~ 3个字节）

3、count(*) 和 count(1) 和 count(列名)的区别

• count( * ) 和 count(1) 其实没有区别，在MySQL实现代码中，count(*)最后会被转化为count(0)，同样也是count(常数)
  • count( * ) 和 count(1) 统计数量时，MySQL会选择最优的方案来统计，比如：不用全表扫描聚簇索引，而是通过扫描占用空间更小的唯一二级索引来统计数量
• count(1) 和 count(列名) 的区别是 count(列名) 统计的是该列所有不为空的行数

4、count(列名)真的比count(*) 效率高吗

网上有很多资料说count(列名)真的比count(*) 效率高，其实这是一个错误的结论。

• count(列名)

  • 如果该列是索引列，那么就会扫描该列对应的索引B+树，然后统计该索引树上该列的值有多少不为空（扫描二级索引树）

  • 如果该列不是索引列，那么就会扫描聚簇索引对应的B+树，然后统计该索引树上该列的值有多少不为空（全表扫描）

• count(*)

  • 当表上没有任何二级索引列时，那么count(*) 使用全表扫描，扫描聚簇索引B+树上的所有记录，然后得出结果
  • 当表上有二级索引列且该二级索引为[唯一索引]时，那么count(*) 扫描[唯一索引B+树]上的所有记录，然后得出结果

• 可以看到当表上有唯一二级索引列时，count(*)的效率会大于count(列名)的全部扫描

4、Int(20)、bigInt(20)、decimal(20, 2)真的能表示20个字节的范围吗

在项目上我看到很多建表语句都是写的Int(20)、bigInt(20)、decimal(20, 2)这种格式，目的是什么呢？想让该字段存储的数字范围是（0，2^20*8）吗 ? 这样是做不到的。

其实Int(20)、bigInt(20)、decimal(20, 2)这种即使你填的20，但是实际上能保存的数据范围也就是int类型所占的字节数、bigint所占用的字节数。

• 比如int类型占4个字节，那么int类型的列最大能表示的范围是（0，2^31 - 1）
• 比如bigint类型占8个字节，那么bigint类型的列最大能表示的范围是（0，2^63 - 1）