索引组织表
innodb存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在innodb表中，每张表都有一个主键（Primary Key），如果没有在创建表时显式地定义主键，则innodb会按如下方式选择或创建主键：
- 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。
- 如果不符合上述条件，innodb自动创建一个6字节大小的指针。
当表中有多个非空唯一索引时，innodb将选择建表时第一个定义的非空唯一索引为主键。
InnoDB逻辑存储结构
innodb所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也被称为块（block），innodb逻辑存储结构大致如图：
- 表空间
表空间可以看作是innodb逻辑结构的最高层，所有的数据都存放在表空间中。 - 段
由上图可知，表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。在innodb中数据即索引，索引即数据。 - 区
区是由连续页组成的空间，在任何情况下每个区的大小都为1MB.为了保证区中页的连续性，innodb一次从磁盘申请4～5个区。默认情况下，innodb页的大小为16KB，即一个区一共有64个连续的页。 - 页
在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行记录。
InnoDB行记录格式
innodb和大多数数据库一样，记录是以行的形式存储的，这意味着页中保存着表中一行行的数据。innodb提供了Compact和Redundant两种格式来存放记录数据，Redundant是为兼容之前版本保留的。在mysql5.1版本，默认设置为Compact行格式。
在mysql5.7以后版本，默认设置为Dynamic行格式。
Compact行记录格式
以下为Compact行记录的存储方式：
由图可知，Compact行记录格式的首部是一个非NULL变长字段长度列表，且长度是按照列的顺序逆序放置的，其长度为：- 若列的长度小于255字节，用1字节表示；
- 若长度大于255字节，用2字节表示。
变长字段的长度最大不可以超过2字节，这是因为在mysql数据库中VARCHAR类型的最大长度限制为65535，变长字段之后的第二部分是NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示。该部分所占的字节应该为1字节。接下来的部分是记录头信息（record header），固定占用5字节（40位）每位含义如下表：
最后的部分就是实际存储每个列的数据。需要特别注意NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间。还有一点是每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若innodb表没有定义主键，每行还会增加一个6字节的rowid列。Redundant行记录格式
Redundant是mysql5.0版本之前innodb的行记录存储方式。存储方式如下图所示：
不同于Compact行记录格式，Redundant行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。若列的长度小于255字节，用1字节表示；若大于255字节，用2字节表示。第二个部分为记录头信息，不同于Compact，Redundant的记录头占用6字节（48位），每位的含义见下表，表中n_fields值代表一行中列的数量，占用10位。另一个要注意的是1byte_offs_flags，该值定义了偏移列表占用1字节还是2字节。最后部分就是实际存储的每个列的数据。
行溢出数据
innodb存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。Compressed和Dynamic行记录格式
这两种格式对于存放在BOLB中的数据采用了完全的行溢出的方式，如下图所示。，在数据页中只存放20个字节的指针，实际的数据都存放在Off Page中，之前的Compact和Redundant两种格式会存放768个前缀字节。
Compressed的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。CHAR的行结构存储
通常理解VARCHAR是存储变长长度的字符类型，CHAR是存储固定长度的字符类型。mysql4.1版本开始，CHAR（N）中的N指的是字符的长度，而不是之前版本的字节长度。
在多字节字符集的情况下，CHAR和VARCHAR的实际行存储基本没有区别。都被视为变长字符类型。
InnoDB数据页结构
innodb数据页由以下7个部分组成：
- File Header（文件头）
- Page Header（页头）
- Infimum & Supremum Records
- User Recods（用户记录，即行记录）
- Free Space（空闲空间）
- Page Directory（页目录）
- File Trailer（文件结尾信息）
其中File Header、Page Header、File Trailer的大小是固定的，分别为38、56、8字节，这些空间用来标记该页的一些信息，如Checksum，数据页所在B+树索引的层数等。User Recods、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的，如下图所示：
- File Header
File Header用来记录页的一些头信息，由下表8个部分组成，共占用38字节。
File Header组成部分
innodb中页的类型
 - Page Header
该部分用来记录数据页的状态信息，由14个部分组成，共占56字节，如下图所示：
 - Infimum & Supremum Records
innodb中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的值，Supremum 指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。在Compact行格式和Redundant行格式下，两者占用的字节数各不相同。下图显示了记录：
 - User Recods和Free Space
User Recods是实际存储行记录的内容
Free Space就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。 - Page Directory
Page Directory中存放了记录的相对位置（注意，这里存放的是页相对位置，而不是偏移量），有些时候这些记录指针称为Slots（槽）或目录槽（Directory Slots）。
innodb不是每个记录都有一个槽，innodb的槽是一个稀疏目录（sparse directory），一个槽中可能包含多个记录。 - File Trailer
File Trailer用来检测页是否已经完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器关机等）。
File Trailer只有一个FIL_PAGE_END_LSN部分，占用8字节，前4字节代表该页的checksum值，最后4字节和File Header中的FIL_PAGE_LSN相同。将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较，看是否一致（checksum的比较需要通过innoodb的checksum函数进行比较，不是简单的等值比较），以此来保证页的完整性。
Named File Formats机制
Named File Formats是innodb用来解决不同版本下页结构兼容性问题的功能
约束
数据完整性
数据完整性由以下三种形式：- 实体完整性保证表中有一个主键，在innodb表中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。
- 域完整性保证数据每列的值满足特定的条件，域完整性可通过以下途径保证：
- 选择合适的数据类型确保一个数据值满足特定条件
- 外键（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允许非法的或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，因此数据库本身没有对数据地正确性进行约束。如果想通过约束对于数据库非法数据的插入和更新，即mysql提示报错而不是警告，可以设置参数sql_mode,用来严格审核输入的参数。例如SET sql_mod = 'STRICT_TRANS_TABLES'ENUM和SET约束
mysql不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决部分这样的约束需求。触发器与约束
触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或之后自动调用sql命令或存储过程。
创建触发器的命令是CREATE TRIGGER，只有具备super权限的mysql用户才可以执行这条命令。
最多可以创建6个触发器，分别为INSERT、UPDATE、DELETE的BRFORE和AFTER各定义一个。BRFORE和AFTER代表触发器发生的时间，表示每次操作之前还是之后发生。外键约束
外键用来保证参照完整性，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就是默认的外键设置。
mysql中NO ACTION和RESTRICT的功能是相同的。
视图
mysql中，视图（view）是一个命名的虚表，它是一个sql查询定义的，可以当作表用。与持久表不同的是，视图中的数据没有实际的物理存储。
- 视图的作用
视图的主要用途之一是被用作一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需按照视图定义来取数据或更新数据，因此，视图同时在一定程度上起到一个安全层的作用。 - 物化视图
物化视图的数据存储在非易失的存储设备上。物化视图可以用于预先计算并保存多表的链接（join）或聚集（GROUP BY）等耗时较多的sql操作结果。物化视图的好处是对于一些复杂的统计类查询能直接查出结果。
分区表
分区概述
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大的对象的一部分处理。mysql支持的分区类型为水平分区，并不支持垂直分区，mysql的分区是局部分区索引，一个分区中既存放了数据又存放了索引。全局分区是指，数据存放在各个分区中，但所有数据的索引放在一个对象中。
当前mysql支持以下几种类型的分区：
- RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。mysql5.5开始支持RANGE COLUMNS的分区。
- LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值。mysql5.5开始支持LIST COLUMNS的分区。
- HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
- KEY分区：根据mysql数据库提供的哈希函数来进行分区。
分区类型
RANGE分区
RANGE分区主要用于日期了列的分区，例如销售类的表，可以根据年来分区存放销售记录。
LIST分区
LIST分区和RANGE分区非常相似，只是分区列的值是离散的，非连续的。HASH分区
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。
KEY分区
KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区。KEY分区使用mysql提供的函数进行分区。对于NDB Cluster引擎，mysql使用MD5函数来分区；对于其他存储引擎，mysql使用其内部的哈希函数，这些函数基于与PASSWORD（）一样的运算法则。COLUMNS分区
COLUMNS分区可视为RANG分区和LIST分区的一种进化，COLUMNS分区可以直接使用非整数的数据进行分区，分区根据类型直接比较而得，不需要转化为整形。此外RANGE COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型：- 所有的整形类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不支持。
- 日期类型，如DATE和DATETIME。其余的日期类型不予支持。
- 字符串类型，如CHAR、VARVHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
子分区
子分区是在分区的基础上再进行分区，有时也称这种分区为复合分区。mysql允许在RANGE和LIST分区上再进行HASH或KEY的子分区。
子分区的建立需要注意以下几个问题：- 每个子分区的数量必须相同
- 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区。
- 每个SUBPARTITION字句必须包括子分区的一个名字
- 子分区的名字必须是唯一的。
分区中的NULL值
mysql允许对NULL值做分区，但处理方式可能与其他数据库不同。mysql的分区总是视NULL值小于任何的一个非NULL值，这和mysql处理NULL值的ORDER BY操作是一样的。因此对于不同的分区类型，mysql对于null值的处理也是各不相同。- 对于RANGE分区，如果向分区插入NULL值，mysql会将该值放入最左边的分区。
- 在LIST分区下使用NULL值，必须显式的指出哪个分区中放入了NULL值，否则回报错。
- HASH和KEY分区对于NULL的处理方式和RANGE分区，LIST分区不一样，任何分区函数都会将含有NULL值的记录返回为0.
分区和性能
数据库应用分为两类：一类是OLTP（在线事务处理），如BLOG、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。在一个实际的应用环境中，可能既有OLTP应用，也有OLAP应用。
对于OLAP的应用，分区的确是可以很好的提高查询性能，因为OLAP应用大多数查询需要频繁的扫描一张很大的表。
对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。在表和分区表间交换数据
mysql5.6开始支持ALTER TABLE …EXCHANGE PARTITION语法。该语法允许分区或子分区中的数据与另一个非分区表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。
要使用ALTER TABLE …EXCHANGE PARTITION语句，必须满足以下条件：- 要交换的表需和分区表结构相同，但是表不能含有分区
- 在非分区表中的数据必须在交换的分区定以内
- 被交换的表中不能含有外键，或其他的表含有对该表的外键引用
- 用户除了需要ALTER、INSERT和CREATE权限外，还需要DROP的权限
还有两个小细节需要注意
- 使用该语句时，不会触发交换表和被交换表上的触发器
- AUTO——INCREMENT列将被重置
