mysql alter table 原理_【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_file_per_table，则每张表内的数据可以单独放到一个表空间内。如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题：即使在启用了参数innodb_file_per_table之后，共享表空间还是会不断地增加其大小。

段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了InnoDB存储引擎表是索引组织的(indexorganized)，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点(图4-1的Leaf node segment)，索引段即为B+树的非索引节点(图4-1的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。

页

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

行

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

InnoDB行记录格式

在InnoDB 1.0.x版本之前，InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据，Redundant格式是为兼容之前版本而保留的。

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

在MySQL 5.1版本中，默认设置为Compact行格式。在MySQL 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是Dynamic。

用户可以通过命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式，其中row_format属性表示当前所使用的行记录结构类型。

注：如果要修改现有表的行模式为Compressed或Dynamic，必须先将文件格式设置成Barracuda：SET GLOBAL innodb_file_format=Barracuda;，再用ALTER TABLE tablename row_format=Dynamic;去修改才能生效。

Compact行记录格式

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

Compact行记录格式的首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：若列的长度小于255字节，用1字节表示。

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

变长字段的长度最大不可以超过2字节，这是因在MySQL数据库中VARCHAR类型的最大长度限制为65535。变长字段之后的第二个部分是NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示。该部分所占的字节应该为1字节。

注：对于多字节字符编码的CHAR数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型。这也就意味着在变长长度列表中会记录CHAR数据类型的长度。

接下来的部分是记录头信息(record header)，固定占用5字节(40位)，每位的含义见下表。

Record Header的最后两个字节，这两个字节名为next_record，代表下一个记录的偏移量，即当前记录的位置加上偏移量就是下条记录的起始位置。所以InnoDB存储引擎在页内部是通过一种链表的结构来串连各个行记录的。

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

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。

MySQL数据库的VARCHAR类型名义上可以存放65535字节，但是因为还有别的开销，通过实际测试发现能存放VARCHAR类型的最大长度为65532字节(注意单位是字节而不是字符)。

此外需要注意的是，MySQL官方手册中定义的65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建。

在一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。

InnoDB存储引擎表是索引组织的，即B+Tree的结构，这样每个页中至少应该有两条行记录(否则失去了B+Tree的意义，变成链表了)。因此，如果页中只能存放下一条记录，那么InnoDB存储引擎会自动将行数据存放到溢出页中。

如果可以在一个页中至少放入两行数据，那VARCHAR类型的行数据就不会存放到BLOB页中去。经过多次试验测试，发现这个阈值的长度为8098。

Compressed和Dynamic行记录格式

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

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

分区表

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

MySQL数据库支持的分区类型为水平分[插图]，并不支持垂直分[插图]。此外，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数据库提供的哈希函数来进行分区。

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

分区类型

RANGE分区

第一种分区类型是RANGE分区，也是最常用的一种分区类型。下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时，数据插入p0分区。当id大于等于10小于20时，数据插入p1分区。CREATE TABLE t(

id INT

) ENGINE=INNDB

PARTITION BY RANGE (id)(

PARTITION p0 VALUES LESS THAN (10),

PARTITION p1 VALUES LESS THAN (20)

);

查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成。

因为表t根据列id进行分区，所以数据是根据列id的值的范围存放在不同的物理文件中的，可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息：SELECT * FROM information_schema.PARTITIONS WHERE table_schema=database() AND table_name='t'\G;

对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。对于上述问题，我们可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值别放入p2分区。ALTER TABLE t ADD PARTITION(

partition p2 values less than MAXVALUE

);

RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录，如下面的分区表sales：CREATE TABLE sales(

money INT UNSIGNED NOT NULL,

date DATETIME

)ENGINE=INNODB

PARTITION by RANGE (YEAR(date)) (

PARTITION p2008 VALUE LESS THEN (2009),

PARTITION p2009 VALUE LESS THEN (2010),

PARTITION p2010 VALUE LESS THEN (2011)

);

如果我们要删除2008年的数据，不需要执行DELETE FROM sales WHEREdate>='2008-01-01' and date

ALTER TABLE sales DROP PARTITION p2008;

另一个好处是可以加快某些查询操作.如果我们只需要查询2008年整年的销售额，SQL优化器只需要去搜索p2008这个分区，而不会去搜索所有的分区——称为Partition Pruning(分区修剪)，故查询的速度得到了大幅度的提升。

需要注意的是，对于RANGE分区的查询，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择，对于组合函数或自定义函数如YEAR(date) * 100 + MONTH(date)等则无效。

LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。如：CREATE TABLE t (

a INT,

b INT

)ENGINE=INNODB

PARTITION BY LIST(b)(

PARTITION p0 VALUES IN (1,3,5,7,9),

PARTITION p1 VALUES IN (0,2,4,6,8)

);

不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN。因为每个分区的值是离散的，因此只能定义值。如果插入的值不在分区的定义中，MySQL同样会抛出异常。

另外，在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，因此没有任何数据插入。

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的列名。此外，用户很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

下面的例子创建了一个HASH分区的表t，分区按日期列b进行：CREATE TABLE t_hash (

a INT,

b DATETIME

)ENGINE=InnoDB

PARTITION BY HASH (YEAR(b))

PARTITIONS 4;

MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。它的语法和HASH分区的语法相似，只是将关键字HASH改为LINEAR HASH。

LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。对于NDB Cluster引擎，MySQL数据库使用MD5函数来分区；对于其他存储引擎，MySQL数据库使用其内部的哈希函数，这些函数基于与PASSWORD()一样的运算法则。如：CREATE TABLE t_key (

a INT,

b DATETIME

)ENGINE=InnoDB

PARTITION BY KEY (b)

PARTITIONS 4;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的效果。

COLUMNS分区

在前面介绍的RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须是整型(interger)，如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()，TO_DAYS()，MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE COLUMNS分区可以对多个列的值进行分区。

COLUMNS分区支持以下的数据类型：所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。

日期类型，如DATE和DATETIME。其余的日期类型不予支持。

字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。

对于日期类型的分区，我们不再需要YEAR()和TO_DAYS()函数了，而直接可以使用COLUMNS，如：CREATE TABLE t_columns_range(

a INT,

b DATETIME

)ENGINE=INNODB

PARTITION BY RANGE COLUMNS (B)(

PARTITION p0 VALUES LESS THAN ('2009-01-01'),

PARTITION p1 VALUES LESS THAN ('2010-01-01')

);

并且对于RANGE COLUMNS分区，可以使用多个列进行分区。

子分区

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

a INT,

b DATE

)engine=innodb

PARTITION BY RANGE( YEAR(b) )

SUBPARTITION BY HASH( TO_DAYS(b) )

SUBPARTITIONS 2 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

我们也可以通过使用SUBPARTITION语法来显式地指出各个子分区的名字，例如对上述的ts表同样可以这样：CREATE TABLE ts (a INT, b DATE)

PARTITION BY RANGE( YEAR(b))

SUBPARTITION BY HASH( TO_DAYS(b) ) (

PARTITION p0 VALUES LESS THAN (1990) (

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2000) (

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4,

SUBPARTITION s5

)

);

子分区的建立需要注意以下几个问题：每个子分区的数量必须相同。

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

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

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

分区中的NULL值

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

对于RANGE分区，如果向分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区。

在LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值。

HASH和KEY分区对于NULL的处理方式和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。

分区和性能

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

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

很多开发团队会认为含有1000W行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有100W的数据了，因此查询应该变得更快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一种情况：100W和1000W行的数据本身构成的B+树的层次都是一样的，可能都是2层。那么上述走主键分区的索引并不会带来性能的提高。好的，如果1000W的B+树的高度是3，100W的B+树的高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询的效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的。如果还有类似如下的SQL语句：SELECT * FROM TABLE WHEREKEY=@key，这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2～3次IO。

因此对于使用InnoDB存储引擎作为OLTP应用的表在使用分区时应该十分小心，设计时确认数据的访问模式，否则在OLTP应用下分区可能不仅不会带来查询速度的提高，反而可能会使你的应用执行得更慢。

在表和分区间交换数据

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

要使用ALTER TABLE … EXCHANGE PARTITION语句，必须满足下面的条件：要交换的表需和分区表有着相同的表结构，但是表不能含有分区

在非分区表中的数据必须在交换的分区定义内

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

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

此外，有两个小的细节需要注意：使用该语句时，不会触发交换表和被交换表上的触发器

AUTO_INCREMENT列将被重置