InnoDB存储引擎——表

时间：2022-09-24 10:33:55

表是关于特定实体的数据集合，也是关系型数据库模型的核心。

1、索引组织表

InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在InnoDB存储引擎表中，每个表都有个主键，如果在创建表的时候没有显示地定义主键，则InnoDB存储引擎会按以下方式选择或创建主键：
（1）首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。
（2）如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。
当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

下面创建一个表，并没有指定主键。因此会选择非空的唯一索引。

mysql> create table test(
-> a int not null,
-> b int null,
-> c int not null,
-> d int not null,
-> unique key(b),
-> unique key(d),
-> unique key(c));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test select 1,2,3,4;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test select 5,6,7,8;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test select 9,10,11,12;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

可以通过下面的语句判断表的主键。

mysql> select a,b,c,d,_rowid from test;
+---+------+----+----+--------+
| a | b | c | d | _rowid |
+---+------+----+----+--------+
| 1 |    2 |  3 |  4 |      4 |
| 5 |    6 |  7 |  8 |      8 |
| 9 | 10 | 11 | 12 | 12 |
+---+------+----+----+--------+
3 rows in set (0.00 sec)

_rowid可以显示表的主键，因此通过上述查询可以找到表test的主键为d。
虽然b是第一个定义的唯一索引，但是b是NULL。所以d是第一个定义的非空唯一索引，InnoDB将其视为主键。_rowid只能用于查看单个列为主键的情况，对于多列组成的主键就显得无能为力了。

mysql> create table test2(
-> a int,
-> b int,
-> primary key(a,b)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test2 select 1,1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select a, _rowid from test2;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'

2、InnoDB存储引擎结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment),区(extent),页(page)组成。页也被称为块(block)。下面是InnoDB存储引擎的逻辑存储结构。

InnoDB存储引擎——表

  • 表空间
    表空间可以看作是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都存放在这个表空间中。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。
mysql> show variables like 'innodb_file_per_table'; 
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)

如果用户启用了参数innodb_file_per_table，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页。
其它类型的数据，如回滚信息，插入缓冲索引页、系统事务信息，二次写缓冲等还是放在原来的共享表空间内。
这也说明，即使在启用了参数innodb_file_per_table之后，共享表空间还是会不断地增加其大小。

  • 段
    从上图中可以看出表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎是索引组织的，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（Leaf node segment），索引段即为B+树的非索引节点（Non-leaf node segment）。

在InnoDB存储引擎中，对段的管理都是由引擎本身完成的。

  • 区
    区是由连续页组成的空间，在任何情况下每个区的大小都为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。

  • 页
    页（也被称为块）是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。而从InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K,8K,16K。若设置完成，则所有表中页的大小都为innodb_page_size,不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库。
    在InnoDB存储引擎中，常见的页类型有：
    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-oriented的数据库。
    MySql infobright存储引擎就是按列来存放数据的，这对于数据仓库下的分析类SQL语句的执行及数据压缩非常有帮助。类似的数据库还有Sybase IQ,Google Big Table。面向列的数据库是当前数据库发展的一个方向。
标签：

相关文章