InnoDB的表类型,逻辑存储结构,物理存储结构

时间:2023-11-07 08:39:02

表类型

对比Oracle支持的各种表类型,InnoDB存储引擎表更像是Oracle中的索引组织表(index organized table)。在InnoDB存储引擎表中,每张表都有个主键,如果在创建表时没有显式地定义主键(Primary Key),则InnoDB存储引擎会按如下方式选择或创建主键。

  1. 首先表中是否有非空的唯一索引(Unique NOT NULL),如果有,则该列即为主键。
  2. 不符合上述条件,InnoDB存储引擎自动创建一个6个字节大小的指针。

逻辑存储结构

InnoDB存储引擎的逻辑存储结构和Oracle大致相同,所有数据都被逻辑地存放在一个空间中,我们称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block),1 extent = 64 pages,InnoDB存储引擎的逻辑存储结构大致如图所示。

InnoDB的表类型,逻辑存储结构,物理存储结构

表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都是存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都放在这个表空间内。如果我们启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。

对于启用了innodb_file_per_table的参数选项,需要注意的是,每张表的表空间内存放的只是数据、索引和插入缓冲,其他类的数据,如撤销(Undo)信息、系统事务信息、二次写缓冲(double write buffer)等还是存放在原来的共享表空间内。这也就说明了另一个问题:即使在启用了参数innodb_file_per_table之后,共享表空间还是会不断地增加其大小。

现在我们来做个实验,实验之前我已经将innodb_file_per_table设为ON了,看看初始共享表空间文件有多大:

show variables like 'innodb_file_per_table' \G

system ls -lh /usr/local/var/mysql/ibdata1

InnoDB的表类型,逻辑存储结构,物理存储结构

共享表空间ibdata1的大小为76M。

set autocommit=0;

update mytest set salary=0;

system ls -lh /usr/local/var/mysql/ibdata1

首先将自动提交设为0,即我们需要显式提交事务(注意,上面结束时我们并没有commit或者rollback该事务)。接着我们执行会产生大量Undo操作的语句update mytest set salary=0,完成后我们再观察共享表空间,会发现ibdata1已经增长到了114MB,这就说明了共享表空间中还包含有Undo信息。如果我rollback这个事务,ibdata1这个表空间会不会缩减至原来的58MB大小?我们接下去就来验证:

rollback;

system ls -lh /usr/local/var/mysql/ibdata1

很“可惜”,还是114MB,即InnoDB存储引擎不会在rollback时去收缩这个表空间。虽然InnoDB不会帮你回收这些空间,但是MySQL会自动判断这些Undo信息是否需要,如果不需要,则会将这些空间标记为可用空间,供下次Undo使用。master thread每10秒会执行一次full purge操作。因此很有可能的一种情况是,你再次执行上述的UPDATE语句后,会发现ibdata1不会再增大了,那就是这个原因了。

上图中显示了表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的(index organized),因此数据即索引,索引即数据。那么数据段即为B+树的页节点(上图的leaf node segment),索引段即为B+树的非索引节点(上图的non-leaf node segment)。

与Oracle不同的是,InnoDB存储引擎对于段的管理是由引擎本身完成,这和Oracle的自动段空间管理(ASSM)类似,没有手动段空间管理(MSSM)的方式,这从一定程度上简化了DBA的管理。

需要注意的是,并不是每个对象都有段。因此更准确地说,表空间是由分散的页和段组成。

区是由64个连续的页组成的,每个页大小为16KB,即每个区的大小为1MB。对于大的数据段,InnoDB存储引擎最多每次可以申请4个区,以此来保证数据的顺序性能。

在我们启用了参数innodb_file_per_talbe后,创建的表默认大小是96KB。区是64个连续的页,那创建的表的大小至少是1MB才对啊?其实这是因为在每个段开始时,先有32个页大小的碎片页(fragment page)来存放数据,当这些页使用完之后才是64个连续页的申请。

通过一个实验来显示InnoDB存储引擎对于区的申请:

create table t1 (

  col1 int not null auto_increment,

  col2 varchar (7000),

  primary key(col1)

)engine=InnoDB;

system ls -lh /usr/local/var/mysql/test/t1.ibd

创建了t1表,col2字段设为varchar(7000),这样能保证一个页中可以存放2条记录。可以看到,初始创建完t1后表空间默认大小为96KB.

同大多数数据库一样,InnoDB有页(page)的概念(也可以称为块),页是InnoDB磁盘管理的最小单位。与Oracle类似的是,Microsoft SQL Server数据库默认每页大小为8KB,不同于InnoDB页的大小(16KB),且不可以更改(也许通过更改源码可以)。

常见的页类型有:

  1. 数据页(B-tree Node)。
  2. Undo页(Undo Log Page)。
  3. 系统页(System Page)。
  4. 事务数据页(Transaction system Page)。
  5. 插入缓冲位图页(Insert Buffer Bitmap)。
  6. 插入缓冲空闲列表页(Insert Buffer Free List)。
  7. 未压缩的二进制大对象页(Uncompressed BLOB Page)。
  8. 压缩的二进制大对象页(Compressed BLOB Page)。

InnoDB存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页存放的行记录也是有硬性定义的,最多允许存放16KB/2~200行的记录,即7992行记录。这里提到面向行(row-oriented)的数据库,那么也就是说,还存在有面向列(column-orientied)的数据库。MySQL infobright储存引擎就是按列来存放数据的,这对于数据仓库下的分析类SQL语句的执行以及数据压缩很有好处。类似的数据库还有Sybase IQ、Google Big Table。面向列的数据库是当前数据库发展的一个方向。

物理存储结构

从物理意义上来看,InnoDB表由共享表空间、日志文件组(更准确地说,应该是Redo文件组)、表结构定义文件组成。若将innodb_file_per_table设置为on,则每个表将独立地产生一个表空间文件,以ibd结尾,数据、索引、表的内部数据字典信息都将保存在这个单独的表空间文件中。表结构定义文件以frm结尾,这个是与存储引擎无关的,任何存储引擎的表结构定义文件都一样,为.frm文件。