mysql alter table 原理_【MySQL—原理】表

用户可以通过命令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,