目录
1. 索引组织表
在InnoDB存储引擎中，表中数据都是根据主键顺序来组织存放的，这种存储方式的表称为：索引组织表index organized table。因此，在InnoDB存储引擎表中，每张表都会有主键primary key，如果开发人员在创建表时没有显式地定义主键，则InnoDB存储引擎会按照如下规则创建主键：
①判断表中是否有非空的唯一索引，如果有，则该列即为主键；
②如果没有符合上述的列，则InnoDB自动创建一个6字节大小的指针；
注意：当表中有多个非空唯一索引时，InnoDB选择建表时第一个定义的非空唯一索引为主键，这里的指的是第一个定义索引的顺序，而不是建表时列的顺序。
2. InnoDB逻辑存储结构
从逻辑存储结构来看，所有数据都被放在一个空间中，这个空间叫做表空间tablespace，而表空间则是由段segment组成，段则是由区extent组成，而区又是由页page组成，而页又是由行Row组成，下面挨个介绍各个组成部分：
2.1 表空间
从逻辑结构层面来看，表空间tablespace是最高层，所有数据都存放在表空间中，默认情况下InnoDB有一个共享表空间ibdata1，所有数据都存放在这里，除非用户启用innodb_file_per_table来为每张表内的数据单独存放一个表空间；
只不过即便开启了innodb_file_per_table参数，每张表的表空间也仅仅是存放数据、索引、插入缓冲Bitmap页，至于其他数据，例如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是继续存放到ibdata1中，这就意味着，不管你是否开启innodb_file_per_table，随着时间流逝，ibdata1占用空间还是会不断增长；
2.2 段
表空间是由各种段组成，常见的段有：数据段、索引段、回滚段等等。
考虑到采用的索引情况，数据即索引，索引即数据，数据段就是B+树的叶子节点，索引段即为B+树的非索引节点，在InnoDB存储引擎中，对段的管理都是由引擎自身完成；
2.3 区
区是由连续页组成的空间，在任何情况下每个区的大小都是1MB，为了保证区中页的连续性，InnoDB一次性从磁盘申请4-5个区，默认情况下，InnoDB存储页大小为16KB，即一个区中一共有64个连续的页；（1024/16=64）
在InnoDB1.0.x引入压缩页，通过key_block_size来设置页的大小，可以设置为2K/4K/8K，对应的每个区的页数量就是512/256/128；
从1.2.x又新增了innodb_page_size，该参数可以将默认页大小设置为4K/8K，但页中数据库不是压缩的，对应区中页的数量就是253/128，无论页大小怎么变化， 区就是1MB。
注意：当用户启用innodb_file_per_table后，创建的表默认是96KB，但是一个区是1MB，这是为什么呢？
在每个段开始时，先用32个页大小的碎片页(16KB*32=512KB)来存放数据，当使用完之后才去申请64个连续页，因为作为InnoDB来讲，其并不知道这个表到底占用多少存储空间，因此，先给你几个空间用用，如果确实放的数据量大了，再去给你分配更大的空间；
2.4 页
页page在其他资料里也叫做块block；页是InnoDB磁盘管理的最小单位，默认页大小为16KB，从InnoDB1.2.x开始，通过innodb_page_size可以将页设置为4K/8K/16K，设置完成后则所有表中页的大小为innodb_page_size，且不可以对其再次进行修改，除非通过mysqldump导入和导出来产生新的数据库，常见的页类型有：
数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页；
2.5 行
InnoDB存储引擎是面向列存储的，也就是说数据是按照行来进行存放的；每个页最多允许存放16KB/2 - 200行记录，即7992行记录；
3. InnoDB行记录格式
既然以行的形式存储记录，那我们看一下是怎么存储的：
在InnoDB 1.0.x之前，有Compact和Redundant这2种格式来存放行记录，在MySQL5.1中，默认采用Compact行格式，用户可以通过show table status like ‘table_name’来查看，其中的row_format就是当前所使用的行记录格式类型；
3.1 Compact行记录格式
Compact行记录格式是在MySQL 5.0中引入的，目标就是高效的存储数据，即一个页中存放的行数据越多，其性能就越高，其存储格式如下：
| 变长字段长度列表 | Null标志位 | 记录头信息 | 列1数据 | 列N数据..... |
变长字段长度列表：非NULL变长字段长度列表，并且是按照列的顺序逆序放置的，其长度如下：
①若列的长度小于255个字节，则用1字节表示；
②若列的长度大于255个字节，则用2字节表示；
变长字段的长度最大不可以超过2字节，这是因为MySQL的varchar最大长度限制为65535；
NULL标志位：指示该行数据中是否有NULL值，有则用1表示，占1字节；
记录头信息：固定占用5字节，一共40位，里面存储了不同的信息，例如：标记该行是否已被删除（占1位）、该记录拥有的记录数（占4位）、索引堆中该条记录的排序记录（占13位）、记录类型用来标记自己是B+树节点还是普通（占3位）、页中下一条记录的相对位置（占16位）等等；
列N数据：实际存储的，每个列的数据；这里特别注意的是NULL不占用该部分任何空间，即NULL除了占用NULL标志位，实际存储不占用任何空间，另外就是每行数据除去用户定义的列之外，还有2个隐藏列：事务ID列（6个字节）、回滚指针列（7个字节），最后就是如果该表没有自定义主键，那么InnoDB还会自己给加上一个6字节的rowid列；
3.2 Redundant行记录格式
该格式是MySQL5.0之前InnoDB的存储格式，所以，这个Redundant单纯是为了兼容之前版本而用的，其组成如下：
| 字段长度偏移列表 | 记录头信息 | 列1数据 | 列N数据..... |
字段长度偏移列表：按照列的顺序逆序放置的，其长度如下：
①若列的长度小于255个字节，则用1字节表示；
②若列的长度大于255个字节，则用2字节表示；
记录头信息：占用6字节，48位，同样的，每一位都有固定的含义，例如：该行是否已被删除（1位）、该记录拥有的记录数（4位）、索引堆中该条记录的索引号（13位）、记录中列的数量（10）、偏移列表为1字节还是2字节（1位）、页中下一条记录的相对位置（16位）等等；
3.3 行溢出数据
InnoDB存储引擎可以把一条记录中某些数据存储在真正的数据页面之外，InnoDB存储引擎表是索引组织的，即B+ Tree的结构，这样每个页中至少应该有2条行记录，否则失去了B+ Tree的意义变成链表了，因此，如果某一条记录占据空间特别大，大到页中只能放下这一条记录，那么InnoDB存储引擎就自动把这行数据存放到溢出页中；
3.4 Compressed和Dynamic行记录格式
InnoDB 1.0.x版本引入了新的文件格式，之前的Compact和Redundant称为Antelope文件格式，而新引入的文件格式叫做Barracuda文件格式，该文件格式拥有2种新的行记录格式：Compressed、Dynamic；
这2种记录格式对于存放在BLOB类型中的数据，直接采用了完全的行溢出方式，且Compressed行记录格式另外的功能是存储在其中的行数据会以zlib算法进行压缩，因此，对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储
3.5 CHAR的行结构存储
在多字节字符接的情况下，char和varchar的实际存储基本没有什么区别，因为从MySQL 4.1开始，char(N)中 N指的是字符的长度，而不是之前的字节长度了，因此不同的字符集情况下，char类型列内部存储的可能是不定长的数据；
4. Named File Formats机制
随着InnoDB引擎发展，各种新引入的页数据结构来支持新的功能特性，为了使得这些新的页数据结构与之前的页数据结构兼容，从InnoDB 1.0.x开始，InnoDB存储引擎通过Named File Formats机制来解决页结构兼容性问题；
InnoDB将1.0.x之前的文件格式叫做Antelope，这个版本中的文件格式叫做Barracuda，新的文件格式总是包含之前版本的页格式，未来还将引入新的文件格式，而我们可以通过innodb_file_format来指定文件格式，可以通过show variables like ‘innodb_file_format’\G来查看；
Innodb_file_format_check用来检查当前InnoDB对不同文件格式的支持度，默认值为on，如果出现不支持的文件格式，则打印到错误日志文件中；（目前InnoDB存储引擎里至少有26种不同的页数据格式，至于能否与不同版本的InnoDB兼容，就靠这个属性了）_
5. 约束
5.1 数据完整性
一般来说，数据完整性有以下3种形式：
①实体完整性保证表中有一个主键：在InnoDB存储引擎表中，可以通过定义primary key或者unique key约束来保证实体的完整性，甚至还可以通过编写一个触发器来保证数据完整性；
②域完整性保证数据每列的值满足特定的条件：在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：
途径1：选择合适的数据类型来确保一个数据值满足特定条件；
途径2：外键foreign key约束；
途径3：编写触发器；
途径4：还可以用default约束作为强制域完整性的一个方面；
③参照完整性保证2张表之间的关系：InnoDB支持外键，因此允许用户定义外键来强制参照完整性，也可以通过编写触发器来强制执行；
具体结合InnoDB存储引擎而言，其提供了如下几种约束：
主键约束primary key、唯一性约束unique key、外键约束foreign key、默认值约束default、非空约束not null；
5.2 约束的创建与查找
约束创建可以采用以下2种方式：
①表建立时就进行约束定义；
②利用alter table命令来创建约束；
具体语法，可以上网搜一下；
5.3 约束和索引的区别
约束是一个逻辑概念，来保证数据的完整性；
索引是一个数据结构，既有逻辑上的概念，在数据库中也代表着物理存储的方式；
5.4 对错误数据的约束
在某些默认设置下，MySQL允许非法/不正确的数据插入/更新，又或者在数据库内部将其转化为一个合法的值，例如向not null的字段插入一个null值之后，MySQL数据库会将其更改为0之后再进行插入，因此，MySQL并没有对数据的正确性进行约束；
如果用户想通过约束来对数据库的非法数据的插入/更新操作进行严格限制，则必须修改sql_mode参数；
当sql_mode=strict_trans_tables时，MySQL数据库会对输入值的合法性进行约束，不同错误会有提示不同的错误内容，至于sql_mode其他取值，可以网上搜一下；
5.5 ENUM和SET约束
MySQL不支持传统的check约束，但通过enum和set可以解决部分此类约束需求；
这里即便你违反了enum和set，MySQL也只是“警告warning”级别的信息，你需要修改sql_mode，从而让“警告warning”级别的信息变成“错误error”级别的信息；
5.6 触发器与约束
触发器的作用是在执行insert、delete、update命令前/后，自动调用SQL命令或存储过程，从MySQL 5.1开始触发器逐渐稳定、成熟；
通过create trigger命令来创建触发器，只有具备Super权限的MySQL数据库用户才可以执行该create trigger命令；
一个表最多创建6个触发器，分别对应insert-before/afler、update-before/afler、delete-before/afler，而其中的before/after分别代表触发器发生的时间，即在每行操作的之前发生还是之后发生；只不过目前MySQL只支持for each row的触发方式，即按每行记录进行触发；
5.7 外键约束
用户可以执行create table时就添加外键，也可以在表创建之后通过alter table来添加；
外键定义时的on delete和on update表示在对父表进行delete和update操作时，对子表所做的操作，而可定义的子表操作有：
①cascade：表示当父表发生delete或update操作时，对相应的子表中的数据也进行delete或update操作；
②set null：表示当父表发生delete或update操作时，相应的子表中数据被更新为null值，前提是子表中对应列允许为null值；
③no action：表示当父表发生delete或update操作时，抛出错误，不允许这类操作发生；
④restrict：表示当父表发生delete或update操作时，抛出错误，不允许这类操作发生，如果定义外键时没有指定on delete或on update时，restrict就是默认的外键设置；
注意：
①MySQL对外键约束都是立刻检查，而不是像其他数据库等SQL语句运行完毕之后再去检查；
②InnoDB在外键建立时会自动的对该列添加一个索引；
最后，外键约束在数据导入时，会非常麻烦，耗费大量时间，因为MySQL外键是即时检查，所以，对导入的每一行都会进行外键检查，为此，可以这样处理导入时的外键约束：
set foreign_key_checks = 0;
导入数据完毕
set foreign_key_checks = 1;
6. 视图
在MySQL数据库中，视图view是一个命名的虚表，由一个SQL查询来定义，可以当做表来用，与持久表不同的是，视图中的数据没有实际的物理存储；（通俗理解为视图是存在于内存中的，持久表是存在于磁盘和内存中的）
6.1 视图作用
视图主要用途之一就是被用作一个抽象装置，特别是对于一些应用程序，这部分程序不需要基表的全部字段，只需要部分字段，因此只需要根据所需字段定义视图来获取数据或者更新数据，因此，从某种程序来讲视图也算是起到一个安全层的作用，毕竟隔离了应用程序与基表；
MySQL 5.0开始支持视图，虽然视图是基于基表的一个内存虚拟表，但用户还是可以对某些视图进行update操作，通过视图来更新基表，像这种可以更新的视图叫做可更新视图updatable view，视图定义中的with check option就是针对可更新视图的，即更新值是否需要检查，对于不满足视图定义条件的，则抛出一个异常，不允许视图中数据更新；
通过show tables来查看所有的基表和视图，如果只想看基表则可以在information_schema架构下的table表来查询，搜索table_type = ‘base table’的表，而information_schema架构下的views表则存放了视图的详细信息；
6.2 物化视图
物化视图指的是把位于内存中的视图，放到磁盘上，物化视图可以用于预先计算并保存多表的链接join或者聚集group by等耗时较多的SQL操作结果，这样在执行复杂查询的时候，就可以避免进行耗时的操作，从而快速得到结果，而MySQL本身不支持物化视图；
7. 分区表
7.1 分区概念
分区功能并不是在存储引擎层面完成的，但也并不是所有的存储引擎都支持，因此，有些引擎支持分区，而有些引擎则不支持分区；
MySQL 5.1版本新增了对分区的支持，分区的过程就是把一个表或者索引分解为多个更小、更容易管理的部分，从逻辑上讲，用户看到的只有一个表或一个索引，但物理层面这个表或索引则是由数十个物理分区组成，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理；
MySQL支持水平分区(同一个表中不同行记录分配到不同的物理文件中)，并不支持垂直分区(同一个表中不同列记录分配到不同的物理文件中)，此外，MySQL分区是局部分区索引，即一个分区中既存放了数据又存放了索引(全局分区指数据存放到各个分区中，但所有数据的索引存放到一个对象中)；
可以通过show variables like ‘%partition%%’\G来查看当前数据库是否启用了分区功能；
MySQL数据库支持以下几种类型的分区：
①range分区：行数据基于属于一个给定连续区间的列值来被放入分区，MySQL 5.5开始支持range columns分区；
②list分区：和range分区类似，只不过是list分区面向的是离散的值，MySQL 5.5开始支持list columns分区；
③hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数；
④key分区：根据MySQL提供的哈希函数来进行分区；
注意事项：
①不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分；唯一索引可以允许null值的，而分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列，例如unique key(col1,col2,col3)的话，则分区列只能从col1和col2以及col3来选择
②如果建表时没有指定主键/唯一索引，那么可以指定任何一个列为分区列；
7.2 分区类型：range分区
| create table t1( id INT ) ENGINE=INNODB PARTITION BY RANGE(id)( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20) ) |
这里当id小于10的时候插入p0分区，当id大于等于10小于20时插入p1分区；
再查看表在磁盘上的物理文件，启用分区之后，表文件是由建立分区时的各个分区的ibd文件组成，文件名格式为：表名#P#分区id.ibd，例如：t1#P#p0.ibd、t1#P#p1.ibd；
分区表t1是根据列id的值范围来存放到不同的物理文件中，可以通过information_schema架构下的partitions表来查看每个分区的详细信息；
当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常，因此可以在分区中添加一个maxvalue值的分区来解决这类情况：
| alter table t1 add partition(partition p2 values less than maxvalue); |
结合实际项目开发，range分区主要用于日期列的分区，根据月份、年份来存放对应的记录，当我们要删除对应年份的记录时，不再需要delete语句，而是直接alter table xxx drop partition 分区名即可，直接drop掉对应的分区；
注意事项：
| create table t1( date datetime ) ENGINE=INNODB PARTITION BY RANGE(year(date))( PARTITION p2008 VALUES LESS THAN (2009), PARTITION p2009 VALUES LESS THAN (2010) ) 查询2008年数据-sql1： select * from t1 where date >= ‘2008-01-01’ and date <= ‘2008-12-31’ 查询2008年数据-sql2： select * from t1 where date >= ‘2008-01-01’ and date < ‘2009-01-01’ |
①对于启用分区后，应该根据分区的特定来编写性能最优的SQL语句，在编写select查询的时候，必须把分区列作为查询条件；
②date >= ‘2008-01-01’ and date <= ‘2008-12-31’则是告诉MySQL仅仅查询P2008这一个分区；而date >= ‘2008-01-01’ and date < ‘2009-01-01’则是告诉MySQL要分别查询P2008和P2009这2个分区，因此编写SQL的时候，尽量不要夸分区查询；
③PARTITION BY RANGE(year(date))，这里对range分区查询优化器，其只对year()/to_days()/to_seconds()/unix_timestamp()这类函数进行优化选择，其他函数在range(XXXXX)没有效果；
7.3 分区类型：list分区
list分区和range分区非常相似，只是分区列的值是离散的，而非连续的：
| create table t1(id int) partition by list(id)( partition p0 values in (1,2,3,4,5), partition p1 values in (6,7,8,9,10) ); |
这里与range区别：
range用的是values less than，而list用的是values in；
7.4 分区类型：hash分区
Hash分区目的是把数据均匀分散到预先定义好的各个分区中，从而保证各个分区中数据量大致是一样的；
相比较range和list在定义时需要人工明确指定，hash分区保存则是MySQL自动完成这些工作，而需要开发者做的只是把要进行hash分区的列编写一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量；
parttition by hash(expr)中expr是一个返回一个整数的表达式，然后后面添加partitions num，其中的num是一个非负的整数来表示将要被分割成分区的数量，如果没有partitions，则默认分数数量为1；
| create table t1(id int) partition by hash(id) partiton 10; |
（个人猜测hash返回一个整数，然后用整数除以10取余，余数就是要放的分区id）
这里是hash分区，MySQL还提供了linear hash的分区；
7.5 分区类型：key分区
Key分区与hash分区类似，不同点在于hash分区使用用户自定义的函数来进行分区，key分区使用MySQL数据库提供的函数来进行分区；
| create table t1(id int) partition by key(id) partiton 10; |
7.6 分区类型：columns分区
range/list/hash/key分区的条件是：数据必须是整数，如果不是整数，那么需要通过函数将其转化为整数，例如：year()/to_days()/month()等函数；
从MySQL 5.5开始支持columns分区，该分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得到，不需要转化为整数，另外 range columns分区还支持对多个列的值进行分区；
Columns分区支持以下数据类型：
①所有整型类型，例如int，smallint，tinyint，bigint；
②日期类型，如date和datetime；
③字符串类型，如char，varchar，binary，varbinary；
| 按照日期类型来分区： create table t1(birthday datetime)engine=innodb partition by range columns(birthday)( partition p0 values less than (‘2009-01-01’), partition p1 values less than (‘2010-01-01’) ) 按照字符串类型来分区： create table t1(name varchar(1000)) partition by list columns(name)( partition p0 values in (‘zhagnsan’,‘lisi), partition p1 values in (‘wangwu’,‘maliu) ) 针对range columns，还可以使用多个列进行分区： create table t1( age int, name varchar(10) )engine=innodb partition by range columns(age,name)( partition p0 values less than(20,’lisi’), partition p1 values less than(80,‘wangwu’) ); |
这里可以看出，columns分区的2个分支range column和list column分别可以替代range和list分区；
7.7 子分区
子分区指的是在分区的基础上再进行分区，有时候这种分区又叫做复合分区，MySQL只允许在range和list分区上再进行hash或key的子分区；
| create table t1(id int,birth date)engine=innodb partition by range(year(birth)) subpartition by hash(to_days(birth)) subpartitions 2( partition p0 values less than (1990), partition p1 values less than (2000), partition p2 values less than (maxvalue) ); |
这里先根据partition by range(year(birth))进行range分区，然后再通过subpartition by hash(to_days(b))进行hash分区，subpartitions 2这里的意思是：按照range分成p0、p1、p2一共3个分区，然后每个分区继续按照hash继续分成2个子分区；
我们还可以通过subpartition语法来为每个子分区指定名字，对上面t1表改造如下：
| create table t1(id int,birth date)engine=innodb partition by range(year(birth)) subpartition by hash(to_days(b))( partition p0 values less than (1990)(subpartition s0,subpartition s1), partition p1 values less than (1990)(subpartition s2,subpartition s3), partition p2 values less than (1990)(subpartition s4,subpartition s5) ); |
子分区的建立需要注意下面几个问题：
①每个子分区的数量必须相同；
②要在一个分区表的任何分区上使用subpartiton来明确定义任何子分区，那么就必须定义所有的子分区；（通俗来讲，就是要么不定义，要么都定义）
③每个subpartition子句必须包括子分区的一个名字；
④子分区的名字必须是唯一的；
7.8 分区中的null值
MySQL允许对null值做分区，MySQL分区的时候，总是把null值看做小于任何一个非null值，跟处理null值的order by操作是一样的，因此对不同的分区类型，MySQL对null值的处理也各不相同，例如：
①对于range分区，向分区列插入null值，则MySQL会把该值放入最左边的分区，可以理解为最先定义的partition；
②对于list分区，向分区列插入null值，则必须显式地指出哪个分区中将放入null值，否则会报错，即在values in(null)中显式指明null存放的分区；
③对于hash和key分区，任何分区函数都会将含有null值的记录返回为0；
7.9 在表和分区间交换数据
MySQL 5.6开始支持alter table ... exchange partition语法，该语法允许分区/子分区中的数据与另一个非分区表中的数据进行交换；如果非分区表中的数据为空，则将分区中的数据移动到非分区表中，如果分区表中的数据为空，则将非分区表中的数据移动到分区中；（这里是移动，相当于是剪切操作，而不是复制操作）
要想使用alter table exchange partition语法，需要满足下面几个条件：
①要交换的表需要和分区表有着相同的表结构，且表不能是分区表；
②在非分区表中的数据必须在交换的分区定义内；
③被交换的表中不能含有外键，或者其他表含有对该表的外键引用；
④用户除了alter、insert、create权限外，还需要drop权限；
这里还有2个细节：
细节1：使用该语法时，不会触发交换表和被交换表上的触发器；
细节2：auto_increment列会被重置；
139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
