本文大部分内容摘抄自《MySQL技术内幕（InnoDB存储引擎）第二版》，小部分来源于自己理解和网络博客，由于书比较旧，网络博客也未必准确，我尽量对文中内容进行验证和对比 MySQL 官网文档，但仍然有可能有疏漏。
索引组织表
在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表成为索引组织表（Index Organized table）。在 InnoDB 存储引擎表中，每张表都有个主键（Primary Key），如果在创建表的时候没有显式地定义主键，则 InnoDB 会按照以下方式选择或创建主键：
- 首次按判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列为主键。
- 如果没有，InnoDB 存储引擎会自动创建一个 6 字节大小的指针。
当表中有多个非空唯一索引的时候，InnoDB 存储引擎将选择建表的时候第一个定义的非空唯一索引作为主键。注意这里是定义索引的顺序，而不是建表的时候列的顺序。
InnoDB 逻辑存储结构
从 InnoDB 存储应引擎的存储结构来看，所有的数据都被逻辑的存放在一个空间中，成为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中也成为块（block），InnoDB存储引擎的逻辑存储结构大致如图：
表空间
表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，所有的数据都存放在这个表空间中。如果用户开启了参数innodb_fiule_per_table，则每张表内的数据可以单独放在一个表空间中。
即使启用了
innodb_fiule_per_table，每张表的表空间内从存放的也只是数据、索引和插入缓冲Bitmap页，其他类型的数据比如回滚（undo）、插入缓冲索引页、系统事务信息、二次写缓冲（Double write buffer）等还是存放在原有共享表空间。
段
上面的图已经显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了 InnoDB 存储引擎表是索引组织（index organized）的，因此数据即索引，索引即数据。那么数据段即 B+ 树的叶子节点（图中的 Leaf node segment），索引段即 B+ 树中的非索引节点（图中的Non-leaf node segment）。回滚段较为特殊。
区
区是由连续的页组成的空间，在任何情况下，每个区的大小都为 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 有页（Page）的概念（也可以称之为块），页是 InnoDB 磁盘管理的最小单位。
在 InnoDB 存储引擎中，常见的页类型有：
- 数据页（B-tree Node）
- undo 页（undo Log Page）
- 系统页（System Page）
- 事务数据页（Transaction system Page）
- 插入缓冲位图页（Insert Buffer Bitmap）
- 插入缓冲空闲列表页（Inser Buffer Free List）
- 未压缩的二进制大对象页（Uncompressed BLOB Page）
- 压缩的二进制大对象页（compressed BLOB Page）
行
InnoDB 存储引擎是面向列的（row-oriented），也就是说数据是按行进行存放的。每个页存放的行记录也是有硬性规定的，最多允许存放16KB/2-200行记录，即7992行。
InnoDB 行记录格式
InnoDB 引擎和大多数数据库一样，记录以行的形式存储。这意味这页中保存着表中一行行的数据。在 InnoDB 1.0.x 版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据，这也是目前使用最多的一种格式。Redundant 格式是为了兼容之前版本而保留的，MySQL 5.1 之后的版本默认为 Compact 行格式。可以使用命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式。（由于书比较旧，我在 MySQL 官网上看到其实不止这两种格式，而默认的格式为 DYNAMIC，具体如下：
Compact 行记录格式
Compact 行记录格式的设计目标是高效的存储数据，简单来说就是一个页中存放的行数据越多，性能就越高。下图是详细的存储格式：
可以看到，Compact 行记录的格式首部是一个非 NULL 变长字段长度列表，并且是按照列的顺序逆序放置的，长度为：
- 若列的长度小于 255 字节，则用 1 字节表示；
- 若大于 255 个字节，用 2 字节表示。
变长字段的长度不可以超过两个字节，因为 VARCHAR 类型的最大长度限制为 65535。变长字段之后的第二个部分是 NULL 标志位，该位指示了该位数据中是否有 NULL 值，有则用 1 表示。接下来的部分是记录头信息（record header），固定占用 5 字节（40位），每位的含义如下：
最后的部分就是实际存储每个列的数据。需要特别注意的是，NULL 不占用该部分任何空间，即 NULL 除了占用 NULL 标志位之外，实际存储不占用空间。另外，每行数据除了用户定义的之外，还有两个隐藏列，事务ID列和回归指针列，分别是6和7字节大小。如果 InnoDB 表没有定义主键，还会增加一个6字节的 rowid 列。
行溢出数据
InnoDB 存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为 BLOB 、 LOB 这类的大对象列类型的存储会把数据放在数据页面之外。但是不是这样的，BLOB 可以不放在溢出页面，而且即便 VARCHAR 这种类型也可能被存放为行溢出数据。
MySQL 数据库的 VARCHAR 类型理论上可以存放 65535 字节，实际上创建一个 65535 长度的列的时候，会得到错误信息：
1 | [42000][1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs. |
由于还有别的开销，经过实际测试，能存放 VARCHAR 类型的最大长度是65532。
这里需要注意，65535 是说的字节数，如果你是用的是多字节的字符集，比如我用utf8mb4，没个字占用4个字节，创建会提示[42000][1074] Column length too big for column 'varchars' (max = 16383); use BLOB or TEXT instead.，即varchar字段已经被限制到了16383的长度。
此外，还要注意，MySQL 官方手册中定义的65535长度是指所有 VARCHAR 列的长度综合，如果综合超过了这个长度，依然无法创建，同样是上面那个错误提示。
即便能够存储65535个字节，但是，InnoDB 引擎的页为 16KB，即16384字节，怎么能存放65535个字节呐？因此，在一般情况下，InnoDB引擎存储的数据都是放在页类型为B-tree node中。但是当发生行溢出的时候，数据存放在页类型为 Uncompress BLOB 页中。
通过工具可以看到，数据也只保存 VARCHAR 的前768的前缀（prefix）数据，之后都是偏移量，指向行溢出页，也就是 Uncompressed BLOB Page。因此，对于行溢出数据，存放采用下图的方式：
那么新的问题又来了，多长的 VARCHAR 是保存在单个数据页中的，多长开始会保存在 BLOB 中？
InnoDB存储引擎表是索引组织的，即 B+Tree 结构，这样每个页中至少应该有两条记录（不然就退化成链表了）。因此，如果页中只能存下一条记录，那么 InnoDB 引擎就会自动将行数据放到溢出页中。
经过测试，这个数字是 8098 ，如果少于这个长度，一个页中就可以放入至少两行数据，VARCHAR 类型的行数据就不会被放到 BLOB 页中去。
另一个问题，对于 TEXT 和 BLOB 数据类型，他们也不是永远存放在 Uncompressed BLOB Page 中的，跟 VARCHAR 类似，至少保证一个页中能存放两条记录。当然一般 BLOB 不会这么小，大多数情况下还是会发生行溢出，数据页只保存前 768 字节，实际数据还是存在 BLOB 页中。
InnoDB数据页结构
页是 InnoDB 存储引擎管理数据库的最小磁盘单位。也类型为 B-tree Node 的页存放的即是表中行的实际数据了。
InnoDB 数据页由以下7个部分组成，如图所示：
- 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 Dirctory 这些部分为实际的行记录存储空间，因此大小是动态的。
约束
约束完整性
关系型数据库和文件系统的一个不同点是，关系数据库本身能够保证存储数据的完整性，不需要应用程序的控制，而文件系统一般都需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束（constraint）机制，来保证数据的完整性。
对 InnoDB 存储引擎本身而言，提供了以下几种约束：
- Primary Key
- Unique Key
- Foreign Key
- Default
- NOT NULL
约束的创建和查找
约束的创建可以用以下两种方式：
- 表建立的时候进行约束定义
- 利用
ALTER TABLE命令来创建约束
对于 Unique Key （唯一索引）的约束，用户可以通过命令CREATE UNIQUE INDEX来建立。对于主键约束而言，默认约束名为 PRIMARY 。
约束和索引的区别
创建约束的方法通常就是创建索引的方法，的确，当用户创建了一个唯一索引就创建了一个唯一约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完成性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。
对错误数据的约束
在某些设置下，MySQL 允许非法的或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段中插入一个 NULL值，MySQL 数据库会将其改为0再插入，因此数据库本身没有对数据的正确性进行约束。而通过设置 sql_mode参数，MySQL 数据库又可以堆输入的合法值进行约束。详细设置可以参考MySQL官方手册中对 sql_mode 的说明。
外键约束
MyISAM 存储引擎本身不支持外键，而 InnoDB 则完整支持外键约束。现在有一定规模的项目都会不建议甚至强制不允许使用外键，具体原因可以再专门探讨，这里就不详细说明外键的使用方法和原理了，只记录几个知识要点。
可以定义的字表操作：
- CASCADE：父表变化时子表与父表进行一样的操作
- SET NULL：父表变化时子表设为 NULL
- NO ACTION：父表抛出错误，阻止操作
- RESTRICT：同上, 都是立即检查外键约束
数据库默认的设置是 RESTRICT。
视图
在MySQL数据中，视图（View）是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。
视图的作用
视图的主要用途之一是被用作一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来取数据或者更新数据，因此，视图层在一定程度上起到一个安全层的作用。
创建视图的语法如下：
1 | CREATE |
2 | [OR REPLACE] |
3 | [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] |
4 | [DEFINER = user] |
5 | [SQL SECURITY { DEFINER | INVOKER }] |
6 | VIEW view_name [(column_list)] |
7 | AS select_statement |
8 | [WITH [CASCADED | LOCAL] CHECK OPTION] |
虽然视图是基于基表的虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基表。
一般称可以进行更新操作的视图成为可更新视图（updatable view）。视图定义中的 WITH CHECK OPTION 就是针对于可更新的视图的，即更新的值是否需要检查。
分区表
分区概述
分区功能并不是在存储引擎层完成的，因此不是只有 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 数据库提供的哈希函数来进行分区。
无论创建何种类型的分区，如果分区表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
唯一索引是允许 NULL 值的，并且分区列只要是唯一索引的一个组成部分，不需要是整个唯一索引列都是分区列。
另外，如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。
分区类型
RANGE 分区
RANGE分区是最常用的一种分区。下面的CREATE TABLE语句创建了一个 id 列的区间分区表。当 id 小于10时，数据插入 p0 分区。当 id 等于10 小于 20时，数据插入 p1 分区。
1 | CREATE TABLE t{ |
2 | id INT |
3 | }ENGINE=INNODB |
4 | PARTITION BY RANGE(id){ |
5 | PARTITION p0 VALUES LESS THAN(10), |
6 | PARTITION p1 VALUES LESS THAN(20)); |
7 | } |
这时候查看磁盘上的物理文件，启用分区表之后，文件不再是由一个 ibd 文件组成了，而是由建立分区时的各个分区 ibd 文件组成。
可以通过查询information_cheme架构下的 PARATITIONS 表来查看每个分区的具体信息：
1 | SELECT * FROM information_scheme.PARTITIONS where table_scheme=database() and table_name='t'\G; |
RANGE 分区主要用于日期列的分区，例如销售类的表，按年进行分区存放销售记录。这样的好处就是，便于对表进行管理。比如要删除某一年的数据，不需要在 where 里面写大段的时间参数，只需删除对应年份所在的分区即可。另一个好处就是可以加快某些查询操作，比如查询某一年的销售额，使用预先设定好的分区可以加快查询速度。
LIST 分区
LIST 分区个 RANGE 分区非常相似，只是分区列的值是离散的，而非连续的。比如：
1 | CREATE TABLE t( |
2 | a INT, |
3 | b INT)ENGINE=INNODB |
4 | PARTITION BY LIST(b)( |
5 | PARTITION p0 VALUES IN(1,3,5,7,9), |
6 | PARTITION p1 VALUES IN(0,2,4,6,8) |
7 | ); |
不同于 RANGE 分区中定义的VALUES LESS TAHN语句，LIST 分区使用 VALUES IN。因为每个分区的值是离散的，因此只能定义值。
HASH 分区
HASH分区的目的是将数据均匀的分不到预先定义的各个分区中，保证各个分区的数据量大致都一样的。在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在 HASH 分区中，MySQL 自动完成这些工作，用户需要做的只是基于将要进行哈希分区的列值指定一个列值或者表达式，以及指定被分区的表将要被分割的分区数量。
要使用 HASH 分区来分割一个表，要在 CREATE TABLE 语句上添加一个PARTITION BY HASH（expr）子句，其中expr是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL 整型的列名。此外，用户很可能需要在后面再添加一个PARTITIONS num子句，其中 num 是一个非负的整数，它表示将要被分割成分区的数量。如果没有包含一个 PARTITIONS 字句，那么分区数量将默认为1。
下面的例子创建了一个 HASH 分区的表 t，分区按日期列 b 进行：
1 | CREATE TABLE t_hash( |
2 | a INT, |
3 | b DATETIME)ENGINE=INNODB |
4 | PARTITION BY HASH(YEAR(b)) |
5 | PARTITIONS 4; |
MySQL 数据库还支持一种成为 LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。它的语法和 HASH 分区的语法相似，只是将关键字 HASH 改为 LINEAR HASH。下面创建的是一个 LINEAR HASH 的分区表：
1 | CREATE TABLE t_linear_hash( |
2 | a INT, |
3 | b DATETIME)ENGINE=INNODB |
4 | PARTITION BY LINEAR HASH(YEAR(b)) |
5 | PARTITIONS 4; |
LINEAR HASH 分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据库的表。缺点在于，与使用 HASH 分区得到的数据分布相比，各个分区间数据的分布可能不太均衡。
KEY 分区
KEY 分区和 HASH 分区相似，不同之处在于 HASH 分区使用用户定义的函数进行分区，KEY 分区使用 MySQL 数据库提供的函数进行分区，对于 NDB Cluster 引擎，MySQL 数据库使用 MD5 函数来分区；对于其他存储引擎， MySQL 数据库使用其内部的哈希函数，这些函数基于与 PASSWORD() 一样的运算法则。
COLUMNS 分区
前面介绍的4种分区，分区得条件是：数据必须是整形（integer），如果不是整型，那应该需要通过函数化为整型。MySQL 5.5 版本开始支持 COLUMNS 分区，可以视为 RANGE 分区和 LIST 分区的一种进化。COLUMNS 分区可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转化为整形。此外 RANGE COLUMNS 分区可以对多个列的值进行分区。
COLUMNS 分区支持以下的数据类型：
- 所有整形类型，如 INT、SMALLINT、TINYINT、BIGINT。FLOAT 和 DECIMAL 则不予支持。
- 日期类型，如 DATE 和 DATETIME。其余的日期类型则不予支持。
- 字符串类型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 类型不予支持。
对于日期类型的分区，我们不再需要YEAR()和TO_DODAY()函数了，而可以直接使用 COLUMNS。
子分区
子分区（subpartitioning）是在分区的基础上在进行分区，有时也称这种分区为复合分区（composite partitioning）。MySQL 数据库允许在 RANGE 和 LIST 的分区上在进行 HASH 和 KEY 的子分区。
但是子分区的建立需要注意以下几个问题：
- 每个子分区的数量必须相同。
- 要在一个分区表的任何分区上使用 SUBPARTITION 来明确定义任何子分区，就必须定义所有的子分区。
- 每个 SUBPARTITION 子句必须包括子分区的一个名字。
- 子分区的名字必须是唯一的。
分区中的NULL值
MySQL数据库允许堆 NULL 值进行分区，但是处理方法和其他数据库可能完全不同。MySQL 数据库的分区总是视 NULL 值小于任何一个非 NULL 值，这和 MySQL 数据库中处理 NULL 值的ORDER BY操作是一样的。因此对于不同的分区类型，MySQL 数据库对于 NULL 值的处理也是各不相同。
对于 RANGE 分区，如果向分区中插入了 NULL 值，则 MySQL 数据库会将该值放入最左边的分区。
LIST 分区必须显式指出向哪个分区中放入 NULL 值，否则会报错。
HASH 分区和 KEY 分区跟上面又不相同，任何分区函数都会将含有 NULL 值的记录返回为0。
分区和性能
在合理使用分区之前，必须了解分区的使用环境。
数据库的应用分为两类：一类是 OLTP（在线事务处理），如 Blog、电子商务、网络游戏等；另一类是 OLAP（在线分析处理），如数据仓库、数据集市。
对于 OLAP 的应用，分区的确是能够很好地提高查询性能，因为 OLAP 应用大多数查询需要频繁的扫描一张很大的表。
然而对于 OLTP 的应用，分区应该非常小心。在这种应用下，通常不可能获取一张表中超过 10%，大部分通过索引返回几条记录即可。而根据 B+ 树索引的原理，一般的 B+ 树需要2~3次的磁盘 IO ，因此 B+ 树已经可以很好地完成操作，不需要分区的帮助，而且设计不好的分区会带来严重的性能问题。
在表和分区间交换数据
在MySQL 5.6 开始支持ALTER TABLE ... EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。
要使用ALTER TABLE ... EXCHANGE PARTITION语句，必须满足：
- 要交换的表和分区表有相同的结构，但是不能含有分区
- 在非分区表中的数据必须在交换的分区定义中
- 被交换的表中不能含有外键，或者其他的表含有对该表的外键引用
- 用户除了需要 ALTER、INSERT 和 CREATE 权限外，还需要 DROP 的权限
此外，还有两个小的细节需要注意：
- 使用该语句的时候，不会触发交换表和被交换表上的触发器
- AUTO_INCREMENT 列将被重置
参考文章：