Mysql技术内幕之表

1. 索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在InnoDB存储引擎表中，每张表都有个主键(Primary Key)，如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键:

• 首先判断表中是否有非空的唯一索引(Unique NOT NULL)，如果有，则该列即为主键。

• 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。主键选择根据的是定义索引的顺序，而不是建表时列的顺序。

2. InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment)、区( extent)、页(page）组成。页在一些文档中有时也称为块〈block)，InnoDB存储引擎的逻辑存储结构大致如图4-1所示。

表空间

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

如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插人缓冲索引页、系统事务信息，二次写缓冲(Double write buffer）等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数innodb_file_per_table之后，共享表空间还是会不断地增加其大小。

段

空间表示有各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（图4-1的Leaf node segment),索引段即为B+树的非索引节点（图4-1的Non-leaf node segment)。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这和Oracle数据库中的自动段空间管理(ASSM）类似，从一定程度上简化了DBA对于段的管理。

区

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

InnoDB 1.0.x版本开始引入压缩页，即每个页的大小可以通过参数KEY BL OCKSIZE 设置为2K、4K、8K，因此每个区对应页的数量就应该为512、256、128。

InnoDB 1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K，但是页中的数据库不是压缩。这时区中页的数量同样也为256、128。总之，不论页的大小怎么变化，区的大小总是为1M。

在每个段开始时，先用32个页大小的碎片页( fragment page）来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表，或者是undo这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销。

页

同大多数数据库一样，InnoDB有页(Page）的概念（也可以称为块)，页是InnoDB磁盘管理的最小单位。在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 Big Table。

3. InnoDB行记录格式

InnoDB存储引擎和大多数数据库一样(如Oracle和 Microsoft sQL Server数据库)，记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。在InnoDB 1.0.x版本之前，InnoDB存储引擎提供了Compact 和Redundant两种格式来存放行记录数据，这也是目前使用最多的一种格式。在MySQL 5.1版本中，默认设置为Compact行格式。用户可以通过命令SHOW TABLE STATUS LIKE ' table_name'来查看当前表使用的行格式，其中row_format属性表示当前所使用的行记录结构类型。

Compact行记录格式

Compact行记录是在MySQL 5.0中引入的，其设计目标是高效地存储数据。简单来,一个页中存放的行说数据越多，其性能就越高。图4-2显示了Compact行记录的存储方式:

从图4-2可以观察到，Compact行记录格式的首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为:

• 若列的长度小于255字节，用1字节表示;

• 若大于255个字节，用2字节表示。

变长字段的长度最大不可以超过2字节，这是因在MySQL数据库中 VARCHAR类型的最大长度限制为65535。变长字段之后的第二个部分是 NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示。该部分所占的字节应该为1字节。接下来的部分是记录头信息（record header)，固定占用5字节(40位)，每位的含义见表4-1。

最后的部分就是实际存储每个列的数据。需要特别注意的是，NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列,分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。

例子见MySQL技术内幕 P118

Redundant行记录格式

Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式，MySQL5.0支持Redundant是为了兼容之前版本的页格式。Redundant行记录采用如图4-3所示的方式存储。

从图4-3可以看到，不同于Compact行记录格式，Redundant行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。若列的长度小于255字节，用1字节表示﹔若大于255字节，用2字节表示。第二个部分为记录头信息（recordheader)，不同于Compact行记录格式，Redundant行记录格式的记录头占用6字节(48位)，每位的含义见表4-2。从表4-2中可以发现，n_fields值代表一行中列的数量，占用10位。同时这也很好地解释了为什么MySQL数据库一行支持最多的列为1023。另一个需要注意的值为1byte_offs_flags，该值定义了偏移列表占用1字节还是2字节。而最后的部分就是实际存储的每个列的数据了。

例子见MySQL技术内幕 P123

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

首先对VARCHAR数据类型进行研究。很多DBA喜欢MySQL数据库提供的VARCHAR类型，因为相对于Oracle VARCHAR2最大存放4000字节，SQL Server最大存放8000字节，MySQL数据库的VARCHAR类型可以存放65535字节。

通过实际测试发现能存放VARCGAR类型的最大长度为65532，这是因为InooDB存储引擎在存储VARCHAR类型数据时，还有别的开销。

Compressed和Dynamic行记录格式

InnoDB 1.0.x版本开始引入了新的文件格式(file format，用户可以理解为新的页格式)以前支持的Compact 和Redundant格式称为Antelope文件格式，新的文件格式称为Barracuda文件格式。Barracuda文件格式下拥有两种新的行记录格式:Compressed和 Dynamic。

新的两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式，如图4-5所示，在数据页中只存放20个字节的指针，实际的数据都存放在Off Page 中，而之前的Compact和 Redundant两种格式会存放768个前缀字节。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

CHAR的行结构存储

通常理解VARCHAR是存储变长长度的字符类型，CHAR是存储固定长度的字符类型。对于多字节字符编码的CHAR数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型。

例子见MySQL技术内幕 P133

CHAR类型被明确视为了变长字符类型，对于未能占满长度的字符还是填充0x20。InnoDB存储引擎内部对字符的存储和我们用HEX函数看到的也是一致的。因此可以认为在多字节字符集的情况下，CHAR和VARCHAR的实际行存储基本是没有区别的。

4. InnoDB数据页结构

InnoDB数据页由以下7个部分组成，如图4-6所示

• File Header(文件头)

• Page Header（页头)

• Infimun和 Supremum Records

• User Records（用户记录，即行记录)

• Free Space (空闲空间)

• Page Directory （页目录)

• File Trailer（文件结尾信息)

其中 File Header、Page Header、File Trailer的大小是固定的，分别为38、56、8字节，这些空间用来标记该页的一些信息，如 Checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的。在接下来的各小节中将具体分析各组成部分。

File Header

File Header 用来记录页的一些头信息，由表4-3中8个部分组成，共占用38字节。

Page Header

Infimum和Supremum Record

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。在Compact行格式和Redundant行格式下，两者占用的字节数各不相同。图4-7显示了Infimum和Supremum记录。

User Record和Free Space

User Record就是之前讨论过的部分，即实际存储行记录的内容。再次强调，InnoDB存储引擎表总是B+树索引组织的。

Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

Page Directory

Page Directory (页目录）中存放了记录的相对位置（注意，这里存放的是页相对位置，而不是偏移量)，有些时候这些记录指针称为Slots（槽）或目录槽（DirectorySlots)。与其他数据库系统不同的是，在InnoDB中并不是每个记录拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录(sparse directory)，即一个槽中可能包含多个记录。伪记录Infimum的n_owned值总是为1，记录Supremum的n_owned 的取值范围为[1，8],其他用户记录n_owned 的取值范围为[4，8]。当记录被插人或删除时需要对槽进行分裂或平衡的维护操作。

在Slots中记录按照索引键值顺序存放，这样可以利用二叉查找迅速找到记录的指针。

由于在InnoDB存储引擎中Page Direcotry是稀疏目录，二叉查找的结果只是一个粗略的结果，因此InnoDB存储引擎必须通过recorder header中的next_record来继续查找相关记录。同时，Page Directory很好地解释了recorder header中的n_owned值的含义，因为这些记录并不包括在Page Directory 中。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

File Trailer

为了检测页是否已经完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器关机等)，InnoDB存储引擎的页中设置了File Trailer部分。

InnoDB数据页结构实例分析 见MySQL技术内幕 P138

5. Named File Formats机制

随着InnoDB存储引擎的发展，新的页数据结构有时用来支持新的功能特性。比如前面提到的InnoDB 1.0.x版本提供了新的页数据结构来支持表压缩功能，完全的溢出(off page）大变长字符类型字段的存储。这些新的页数据结构和之前版本的页并不兼容，因此从InnoDB 1.0.x版本开始，InnoDB存储引擎通过Named File Formats机制来解决不同版本下页结构兼容性的问题。

6. 约束

数据完整性

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束(constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说，数据完整性有以下三种形式:

实体完整性保证表中有一个主键。在InnoDB存储引擎表中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。

域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证:

• 选择合适的数据类型确保一个数据值满足特定条件。

• 外键(Foreign Key）约束。

• 编写触发器。

• 还可以考虑用DEFAULT约束作为强制域完整性的一个方面。

参照完整性保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于InnoDB存储引擎本身而言，提供了以下几种约束:

• Primary Key

• Unique Key

• Foreign Key

• Default

• NOT NULL

约束的创建和查找

约束的创建可以采用两种方式：

• 表建立时就进行约束定义

• 利用ALTER TABLE命令来进行创建约束

对Unique Key(唯一索引）的约束，用户还可以通过命令CREATE UNIQUE INDEX来建立。对于主键约束而言，其默认约束名为PRIMARY。而对于Unique Key约束而言，默认约束名和列名一样，当然也可以人为指定Unique Key约束的名字。Foreign Key约束似乎会有一个比较神秘的默认名称。

约束和索引的区别

约束是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

对错误数据的约束

在某些默认设置下，MySQL 数据库允许非法的或不正确的数据的插人或更新，又或者可以在数据库内部将其转化为一个合法的值，如向NOT NULL的字段插入一个NULL值，MySQL数据库会将其更改为0再进行插人，因此数据库本身没有对数据的正确性进行约束。

ENUM和SET约束

MySQL数据库不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个性别类型，规定域的范围只能是male或female,在这种情况下用户可以通过ENUM类型来进行约束。

触发器与约束

触发器的作用是在执行INSERT、DELETE 和UPDATE命令之前或之后自动调用SQL命令或存储过程。MySQL 5.0对触发器的实现还不是非常完善，限制比较多，而从MySQL5.1开始触发器已经相对稳定，功能也较之前有了大幅的提高。

外键约束

外键用来保证参照完整性，MySQL数据库的MyISAM存储引擎本身并不支持外键,对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。

一般来说，称被引用的表为父表，引用的表称为子表。外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE 和 UPDATE操作时，对子表所做的操作，可定义的子表操作有:

• CASCADE

• SET NULL

• NO ACTION

• RESTRICT

CASCADE表示当父表发生DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE 或UPDATE操作。SET NULL表示当父表发生DELETE 或UPDATE操作时，相应的子表中的数据被更新为NULL值，但是子表中相对应的列必须允许为NULL值。NO ACTION表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。RESTRICT表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE 或ON UPDATE,RESTRICT就是默认的外键设置。

在其他数据库中，如Oracle数据库，有一种称为延时检查(deferred check）的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查（immediate check)，因此从上面的定义可以看出，在MySQL数据库中NOACTION和 RESTRICT的功能是相同的。

7. 视图

在MySQL数据库中，视图（ View）是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。与持久表( permanent table）不同的是，视图中的数据没有实际的物理存储。

视图的作用

视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置,特别是对于一些应用程序，程序本身不需要关心基表(base table）的结构，只需要按照视图定义来取数据或更新数据，因此，视图同时在一定程度上起到一个安全层的作用。

虽然视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基本表。一般称可以进行更新操作的视图为可更新视图(updatable view)。视图定义中的WITH CHECK OPTION就是针对于可更新的视图的，即更新的值是否需要检查。

物化视图见P160

8. 分区表

分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持。

MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

当前MySQL数据库支持以下几种类型的分区：

• RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。MySQL 5.5开始支持RANGE COLUMNS的分区。

• LIST分区:和RANGE分区类型，只是LIST分区面向的是离散的值。MySQL 5.5开始支持LIST COLUMNS的分区。

• HASH分区:根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。

• KEY 分区:根据MySQL数据库提供的哈希函数来进行分区。

分区类型P170

RANGE分区、LIST分区、HASH分区、KEY分区、COLUMNS分区

子分区

子分区( subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区(composite partitioning)。MySQL数据库允许在RANGE 和LIST的分区上再进行HASH 或KEY的子分区。

子分区的建立需要注意以下几个问题：

• 每个子分区的数量必须相同。

• 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区。

• 每个SUBPARTITION子句必须包括子分区的一个名字。

• 子分区的名字必须是唯一的。

分区中的NULL值

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。MYSQL数据库的分区总是视NULL值视小于任何的一个非NULL值，这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。因此对于不同的分区类型，MySQL数据库对于NULL值的处理也是各不相同。

分区和性能

数据库的应用分为两类:一类是OLTP（在线事务处理)，如 Blog、电子商务、网络游戏等;另一类是OLAP（(在线分析处理)，如数据仓库、数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。

对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一-张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。这就是前面介绍的 Partition Pruning技术。

然而对于OLTP 的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2～3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

在表和分区间交换数据

MySQL 5.6开始支持ALTER TABLE…EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。

要使用ALTER TABLE…EXCHANGE PARTITION语句，必须满足下面的条件:

• 要交换的表需和分区表有着相同的表结构，但是表不能含有分区在非分区表中的数据必须在交换的分区定义内

• 被交换的表中不能含有外键，或者其他的表含有对该表的外键引用

• 用户除了需要ALTER、INSERT和CREATE权限外，还需要DROP的权限