MySQL(InnoDB剖析):15---table之（表空间：段(segment)、区(extent)、页(page)）

一、InnoDB逻辑存储结构

• 从InnoDB的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）
• 表空间又由段（segment）、区（extent）、页（page）组成
• 页在一些文档中有时也称为块（block）

二、表空间

• 表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中

默认表空间

• 前面文章已经介绍了在默认情况下InnoDB有一个共享表空间ibdata1，即所有数据都存放在这个表空间内

show variables like 'innodb_data_file_path'\G;

 

innodb_file_per_table参数

• 如果用户启用了innodb_file_per_table参数，则每张表内的数据可以单独放到一个表空间内（具体参阅前面文章的介绍：https://blog.csdn.net/qq_41453285/article/details/104115914）
• 如果使用此参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲等还是存放在原来的共享表空间内

共享表空间的大小问题

• 上面通过innodb_file_per_table参数我们知道即使为每个表都是用单独的表空间，但是表的有些信息还是要存放到共享表空间内，因此共享表的空间会不断增加其大小
• 下面进行一个演示案例，来观察共享表空间大小的增长过程
• 先将innodb_dile_per_table参数设置为“NO”。然后观察一下默认的共享表空间文件的大小（为58M）

• 接下来模拟产生undo的操作，set autocommit设置为0代表用户需要显式提交事务（下图中，在事务提交结束时并没有以对钙食物执行commit或rollback，因此会产生大量undo操作的update语句）。可以看到共享表空间的大小增加了

• 如果对事务进行回滚操作，那么共享表空间的大小会不会缩减至原来的大小呢？，见下图

• 答案是否，共享表空间的大小没有减小。虽然InnoDB不回回收这些空间，但是会自动判断这些undo信息是否还需要，如果不需要，则会将这些空间标记为可用空间，供下次undo使用
• 当用户再次执行上述的update语句后，会发现共享表空间文件不会再增大了，因为其会利用之前的undo信息

Python脚本：查看表空间中各页的信息

• 此脚本可以在GitHub进行下载：https://github.com/jameslcj/david-mysql-tools
• 这个脚本用来查看表空间中各页的类型和信息，使用方法如下：
  • 共有83584个页。其中插入缓冲的空间列表有204个页、5467个可用页、38675个undo页、39233个数据页等

• 用户可以通过-v选项来查看更详细的内容

三、表空间之“段”

• 从上图可以看到表空间是由多个段组成的，常见的段有数据段、索引段、回滚段等
• 因为前面介绍过InnoDB是索引组织的，因此数据即索引，索引即数据
  • 那么数据段就是B+树的叶子节点（Leaf node segment）
  • 索引段即为B+树的非索引节点（Non-leaf node segment）
  • 回滚段比较特殊，在后面介
• 在InnoDB中，对段的管理都是有存储引擎自身完成的，DBA不能也没有必要对其进行空间。这和Oracle数据库中的自动段空间管理（ASSM）类似，从一定程度上简化了DBA对于段的管理

四、表空间之“区”

• 区是由连续页组成的空间
• 在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区

区中页的数量

• 在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区一共有64个连续的页
• InnoDB 1.0.x开始引入压缩页，即每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应页的数量就应该为512、256、128
• InnoDB 1.2.x开始新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K。但是页中的数据库不是压缩。这时区中页的数量同样也为256、128
• 总之，不论页的大小怎么变化，区的大小总是为1M

区的申请方式（碎片页）

• 当启用参数innodb_file_per_table后，创建的表默认大小是96KB。但是我们知道一个区就占用了1MB，那么为什么之后96KB呢？
• 这是因为在每个段开始时，先用32个页大小的碎片页去存放数据，在使用完这些页之后才开始申请64个连续的页
• 这样做的目的是：对于一些小表，或者是undo这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销

区申请的演示案例

• 下面通过一个很小的示例来显示InnoDB对于区的申请方式
• 第一步：创建一个t1表，将col2字段设为VARCHAR(7000)，这样就能保证一个页最多可以存放2条记录（因为一个页的大小为16KB*1024=16384bit）。然后通过ls命令查看到表空间默认大小为96KB

• 第二步：接着向表中插入两条SQL语句，然后查看表空间的大小发现空间没有增加，并且这两条记录应该位于同一个页中

• 第三步：此时再去使用上面的工具查看表空间，可以看到
  • page offset为3的页：这个就是数据页
  • page level：表示所在索引层，0表示叶子节点。因为当前所有记录都在一个页中，因此没有非叶子节点

• 第四步：现在我们再插入一条记录，就会产生一个非叶节点：
  • page level：page offset为3的页的page level由之前的0变为了1，这时虽然新插入的记录导致B+树的分裂操作，但这个页的类型还是B-tree Node

• 第五步：接着上述同样的操作，再插入60条记录，也就是说当前表t1中共有63条记录，32个页。为了导入的方便，在这之前先建立一个导入的存储过程
  • 可以看到，在导入了63条数据后，表空间的大小还是小于1MB，即表示数据空间的申请还是通过碎片页，而不是通过64个连续页的区

• 第六步：再次查看表空间文件，可以观察到B-tree Node一共有33个，除去一个page level为1的非叶节点页，一共有32个page level为0的页，也就是说，对于数据库，已经有32个碎片页了。之后用户再申请空间，则表空间按连续64个页的大小开始增长了

• 第七步：现在继续插入一条记录，之后看看表空间的大小。因为已经用完了32个碎片页，新的页回采用区的方式进行空间的申请

• 第八步：现在再次分析表空间文件

五、表空间之“页”

• 页有时候也称为块
• 页是InnoDB磁盘管理的最小单位

innodb_page_size参数

• 在InnoDB存储引擎中，默认每个页的大小为16KB
• 而从InnoDB 1.2.x开始，可以通过innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有页的大小都为innodb_page_size，不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库

• 在InnoDB中，常见的页类型有：
  • 数据页（B-tree Node）
  • undo页（undo Log Page）
  • 系统页（System Page）
  • 事务数据页（Transaction system Page）
  • 插入缓冲位图页（Insert Buffer Bitmap）
  • 插入缓冲空闲列表页（Insert Buffer Free List）
  • 未压缩的二进制大对象页（Uncompressed BLOB Page）
  • 压缩的二进制大对象页（compressed BLOB Page）

六、行

• InnoDB存储引擎是面向列的（row-oriented），也就说数据是按行进行存放的。每个页存放的记录也是硬性定义的，最多允许存放16KB/2-200行记录，即7992行记录
• 这里提到的row-oriented的数据库，也就是说，存在有column-oriented的数据库
• MySQL infobright存储引擎就是按列来进行存放数据的，这对于数据仓库下的分析类SQL语句的执行及数据压缩非常有帮助。类似的数据库还有Sybase IQ、Google BigTable