mysql--表详解

一、索引组织表：

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

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

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

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

二、InnoDB 逻辑存储结构

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

1.表空间

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

如果启用了innodb file per table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内

这同时也说明了另一个问题∶即使在启用了参数 innodb_file _per table 之后，共享表空间还是会不断地增加其大小。

2.段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了InnoDB 存储引擎表是索引组织的（index organized），因此数据即索引mysql数据表，索引即数据。

（1）数据段即为 B+树的叶子节点（如图中的 Leaf node segment）

（2）索引段即为 B+树的非索引节点（如图中的 Non-leaf node segment）在 InnoDB 存储引擎中，对段的管理都是由引擎自身所完成

3.区

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

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

总之，不论页的大小怎么变化，区的大小总是为 1M。

4.页

同大多数数据库一样，InnoDB 有页（Page）的概念（也可以称为块），页是 InnoDB磁盘管理的最小单位。在 InnoDB存储引擎中，默认每个页的大小为 16KB。而从 InnoDB 1.2.x版本开始，可以通过参数 innodb_page_size 将页的大小设置为 4K、8K、 16K。若设置完成，则所有表中页的大小都为 innodb_page_size，不可以对其再次进行修改。除非通过 mysqldump 导入和导出操作来产生新的库。

常见的页类型有

（1）数据页

（2）undo 页（undo Log Page）

（3）系统页（System Page）

（4）事务数据页（Transaction system Page）

（5）插入缓冲位图页（Insert Buffer Bitmap）

（6）插入缓冲空闲列表页

（7）未压缩的二进制大对象页

（8）压缩的二进制大对象页

5.行

InnoDB 存储引擎是面向行的（row-oriented），也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200行的记录，即 7992行记录。

三、InnoDB 行的记录格式

InnoDB 存储引擎和大多数数据库一样（如 Oracle 和 Microsoft SQL Server 数据库），录是以行的形式存储的。这意味着页中保存着表中—行行的数据，InnoDB存储引擎提供了Compact 和Redundant两种格式来存放行记录数据

1.Compact 行格式

在 MySOL 5.0之后的版本中，默认设置为 Compact 行格式，其设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高

2.Redundant 行记录格式

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

四、约束 1.数据完整性

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

1.1.实体完整性

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

1.2.域完整性

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

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

（2）外键（Foreign Key）约束

（2）编写触发器。

1.3.参照完整性

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

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

（1）Primary Key（主键）

（2）Unique Key（唯一键）

（3）Foreign Key（外键）

（4）Default

（5）NOT NULL

2.约束的创建和查找

创建：

1.表建立时就进行约束定义

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

3.约束和索引的区别

在前面的小节中已经看到 Primary Key 和 Unique Key 的约束，有人不禁会问∶这不就是通常创建索引的方法吗?那约束和索引有什么区别呢? 的确，当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

4.对错误数据的约束

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

5.外键约束

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

6.触发器约束

通过前面小节的介绍，用户已经知道完整性约束通常也可以使用触发器来实现，因此在了解数据完整性前先对触发器来做一个了解。 触发器的作用是在执行INSERT、DELETE 和 UPDATE 命令之前或之后自动调用

最多可以为一个表建立6个触发器，即分别为 INSERT、UPDATE、DELETE的 BEFORE 和 AFTER 各定义一个。BEFORE 和 AFTER代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。当前 MySQL 数据库只支持 FOR EACH ROW 的触发方式，即按每行记录进行触发，不支持像 DB2 的 FOR EACH STATEMENT 的触发方式。 通过触发器，用户可以实现 MySQL 数据库本身并不支持的一些特性，如对于传统 CHECK 约束的支持，物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持

五、视图

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

1.视图的作用

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

虽然视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其 本质就是通过视图的定义来更新基本表。一般称可以进行更新操作的视图为可更新视图（updatable view）。视图定义中的 WITH CHECK OPTION 就是针对于可更新的视图的，即更新的值是否需要检查，对于不满足视图定义条件的将会抛出一个异常

六、分区表

分区功能并不是在存储引擎层完成的，因此不是只有 InnoDB 存储引擎支持分区，常见的存储引擎 MyISAM、NDB 等都支持

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

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

后面章节会详讲mysql分区相关的

（编辑：武陵站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!