Innodb数据结构空间占用

了解数据存储空间占用，可以更方便我们再企业中对于数据库相关优化做评估。

一、查看当前数据表空间占用信息

首先这里准备一张数据库表约2.3w数据量：

CREATE TABLE `project` (
  `tenantsid` bigint(20) NOT NULL DEFAULT '0' COMMENT '租户ID',
  `project_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code` bigint(20) NOT NULL DEFAULT '0' COMMENT '项目编号',
  `name` varchar(72) COLLATE utf8_bin DEFAULT '' COMMENT '项目名称',
  PRIMARY KEY (`project_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=59840 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='项目档'  

查看表存储空间大小方式有很多

1、通过iinformation_schema.tables查看数据空间占用信息

select table_name,data_length,index_length from information_schema.tables where table_name = 'project';

 按照官网介绍：

DATA_LENGTH

对于MyISAM，DATA_LENGTH 是数据文件的长度，以字节为单位。

对于InnoDB，DATA_LENGTH 是为聚集索引分配的大约空间量（以字节为单位）。具体来说，它是聚集索引大小（以页为单位）乘以InnoDB页大小。

INDEX_LENGTH

对于MyISAM，INDEX_LENGTH 是索引文件的长度，以字节为单位。

对于InnoDB，INDEX_LENGTH 是为非聚集索引分配的大约空间量（以字节为单位）。具体来说，它是非聚集索引大小的总和（以页为单位）乘以 InnoDB页大小。

DATA_LENGTH  = 聚集索引大小（以页为单位）乘以InnoDB页大小

INDEX_LENGTH  = 非聚集索引大小（以页为单位）乘以InnoDB页大小

2、查看page页大小配置

show status like '%page_size%';

可以看到默认的页大小为16kb

3、查看当前project表聚簇索引使用了多少page

innodb_space -s ibdata1 -T firestation/project space-indexes

可以看到这里面PRIMARY一共分配了97个page页占用空间 = 97*16*1024 = 1,589,248可以看到正好是DATA_LENGTH大小

前面也说到了当97个表只是当前project真实数据占用的空间大小，对于表来说，还存在其他的的空间占用，比如缓存、元数据等等，所以DATA_LENTGTH表示的只是真实数据的大小。当然对于日常的空间占用评估是足够了。

那我们深入探索下，project真实存储空间占用情况。

4、查看project表使用总page数

innodb_space -s ibdata1 -T firestation/project space-page-type-summary

 可以看到project实际存储时使用到了704个page页，所以理论上应该占用空间704*16*1024=11,534,336

其实mysql在information_schema中提供了innodb系列的元数据表，它记录了innodb底层存储时真实的元数据信息，例如information_schema.innnodb_sys_tablespace（我这里是mysql5.7不同版本的名称不同，看官网说明）中就可以查看表真实存储空间大小。

5、查看project真实占用空间大小

select * from information_schema.innodb_sys_tablespaces where name = 'firestation/project';

• FILE_SIZE

  文件的表观大小，表示未压缩的文件的最大大小。

• ALLOCATED_SIZE

  文件的实际大小，即磁盘上分配的空间量。

可以看到这里的File_size就是上面通过真实数据page数计算出来的结果。

所以结论：

1、可以通过information_schema.table查看DATA_LENGTH，这个为当前主键索引也就是真实数据的空间占用大小。

2、可以通过information_schema.innodb_sys_tablespace 查看File_size,这个为当前project总占用空间大小

二、每行数据占用空间

其实通过上述的结论，就可以大致的评估出每行数据空间占用大小，总大小/行数，这里我们更深入了解下一行数据怎么计算的空间占用大小。

1、查看每一行数据占用空间大小

（1）首先先确定project存储tree一共为几层 ， 这里先介绍一种查看到方式，后续在介绍另一种。

通过innodb_space工具查看：

这里可以看到PRIMARY索引一共分2层，第1层非叶子节点只有一个page，第2层叶子节点共95个page页，需要注意的是这里叶子节点为0层，依次往上推。

（2）查看每一层tree空间占用

1层，非叶子节点（此处为root节点）：

innodb_space -s ibdata1 -T firestation/project -I PRIMARY -l 1 index-level-summary

page： page页编号，这里为3，也就是上面看到的root节点page编号

index：索引编号

level：当前page所处的层级

data：当前page占用空间大小

free：当前page空闲空间大小，因为page大小固定16kg，所以就会存在页用不满的现象

recoreds：当前page存储的数据行数，这里因为是非叶子节点，所以指的是主键行数。

min_key：当前页中最小的索引列，这里是主键。

0层，非叶子节点（此处为root节点）：

从这里就可以看出来，大约每行数据占用15000/250 = 60字节空间

行空间占用和字段数据有密切的关系，如果字段占用空间小（比如字段栏位少，无长文本字段等）那page可以存储更多的行数，整体的占用空间小，B+树层级少，查询速度快。

这里以page#5为例，看下当前page页空间分布说明：

innodb_space -s ibdata1 -T firestation/project -p 5 page-illustrate

这张图就是展示了PAGE一个详细的空间结构，前面文章也介绍过，这里不再赘述。

可以看到Record Header + Record Data 是真实数据产生的空间占用 = 1631+13471 =15102也是验证了上面看到的page空间占用。

三、每行数据空间评估

根据上面的数据结果，选择一个project_id = 33819的一行数据，从空间上来看为15102字节/233 =64字节

当前行数据：

我们知道bigint占用8字节，varchar是不定长，一个汉字3字节。按照这样的算法

空间：8+8+8+12 = 36字节，远小于60字节，这是为什么呢？

这个就是需要了解行格式知识相关了：【Mysql】 InnoDB引擎深入- 行格式_mysql 5.6 innodb引擎 不支持行格式为:dynamic_Survivor001的博客-CSDN博客

首先确定我们目前的行格式是什么，这个很好确定：

根据行格式的知识，我们知道一行数据除了真实的数据之外还会记录一些额外的信息。

• 在这里以project_id =33819数据大致计算下
• 存在变长字段，且长度<255，则需要1个字节记录其长度（这里共1~2字节，记录最大值65535，所以这就是为什么一行数据不能超过这个阈值）
• 记录头5个字节
• 除此之外，数据里面处理记录真实数据外还记录其他内比如row_id（6字节），回滚指针（7字节）、事务id（6字节）
• 总计：1+5+7+6+6= 25（字节）
• 所以：真实数据36 + 25 ~= 60字节， 这样一看就差不多了。
• 总结：
• 一行数据一般来说差不多占用空间 = 25字节 + 字段栏位真实占用空间，当然如果一行数据存在大数据内容，可能一行数据就超过了page大小，就可能跨页，这个时候就会行溢出。需要更多的页来记录其内容，次数就会占用更多的空间，因为page本身也会有额外的空间占用。所以这就是为什么大数据栏位建议独立出来不要和普通字段放在一起，而text、blog有独立BLOB PAGE原因。
• 行溢出：【Mysql】 InnoDB引擎深入- 行溢出_innodb行溢出-CSDN博客

代码007普通

打赏 收藏 海报 链接