表类型
对比Oracle支持的各种表类型,InnoDB存储引擎表更像是Oracle中的索引组织表(index organized table)。在InnoDB存储引擎表中,每张表都有个主键,如果在创建表时没有显式地定义主键(Primary Key),则InnoDB存储引擎会按如下方式选择或创建主键。
- 首先表中是否有非空的唯一索引(Unique NOT NULL),如果有,则该列即为主键。
- 不符合上述条件,InnoDB存储引擎自动创建一个6个字节大小的指针。
逻辑存储结构
InnoDB存储引擎的逻辑存储结构和Oracle大致相同,所有数据都被逻辑地存放在一个空间中,我们称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block),1 extent = 64 pages,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
共享表空间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),且不可以更改(也许通过更改源码可以)。
常见的页类型有:
- 数据页(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-orientied)的数据库。MySQL infobright储存引擎就是按列来存放数据的,这对于数据仓库下的分析类SQL语句的执行以及数据压缩很有好处。类似的数据库还有Sybase IQ、Google Big Table。面向列的数据库是当前数据库发展的一个方向。
物理存储结构
从物理意义上来看,InnoDB表由共享表空间、日志文件组(更准确地说,应该是Redo文件组)、表结构定义文件组成。若将innodb_file_per_table设置为on,则每个表将独立地产生一个表空间文件,以ibd结尾,数据、索引、表的内部数据字典信息都将保存在这个单独的表空间文件中。表结构定义文件以frm结尾,这个是与存储引擎无关的,任何存储引擎的表结构定义文件都一样,为.frm文件。