1、分区概述
分区功能并不是在存储引擎层完成的,因此不是只有 InnoDB存储引擎支持分区,常见的存储引擎 MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持,如CSV、 FEDORATED、 MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。
MySQL数据库在51版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言,从逻辑上讲,只有一个表或一个索引,但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区,并不支持垂直分区。
水平分区,指将同一表中不同行的记录分配到不同的物理文件中。
垂直分区,指将同一表中不同列的记录分配到不同的物理文件中。
此外, MySQL数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引。而全局分区是指,数据存放在各个分区中,但是所有数据的索引放在一个对象中。目前, MySQL数据库还不支持全局分区。
查询支持分区命令如下：
mysql> show plugins like '%partiotion%'; mysql> show variables like '%partition%'; Empty set (0.00 sec)
如果查询结果显示Empty，表示不支持分区。但是上面的查询方法只是针对mysql5.6以下版本。如果mysql5.6以及以上版本，需要使用下面的查询命令：
mysql> show plugins;
大多数DBA会有这样一个误区:只要启用了分区,数据库就会运行得更快。这个结论是存在很多问题的。就我的经验看来,分区可能会给某些SQL语句性能带来提高,但是分区主要用于数据库高可用性的管理。在OLTP应用中,对于分区的使用应该非常小心。总之,如果只是一味地使用分区,而不理解分区是如何工作的,也不清楚你的应用如何使用分区,那么分区极有可能会对性能产生负面的影响。
当前 MySQL数据库支持以下几种类型的分区。
- RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。 MySQL5.5开始支持 RANGE COLUMNS的分区。
- LIST分区:和 RANGE分区类型,只是LST分区面向的是离散的值。 MySQL5.5开始支持 LIST COLUMNS的分区。
- HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。
- KEY分区:根据 MySQL数据库提供的哈希函数来进行分区。
不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分,因此下面创建分区的SQL语句会产生错误。
mysql> create table t1( -> col1 int not null, -> col2 date not null, -> col3 int not null, -> col4 int not null, -> unique key (col1,col2) -> ) partition by hash(col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列,如:
mysql> create table t1( -> col1 int not null, -> col2 date not null, -> col3 int not null, -> col4 int not null, -> unique key (col1,col2,col3,col4) -> ) partition by hash(col3) partitions 4; Query OK, 0 rows affected (0.04 sec)
如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列。
2、分区类型
2.1、RANGE分区
我们介绍的第一种分区类型是 RANGE分区,也是最常用的一种分区类型。下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时,数据插入p0分区。当id大于等于10小于20时,数据插入p1分区。
mysql> create table t( -> id int -> )engine=innoDB -> partition by range (id)( -> partition p0 values less than (10), -> partition p1 values less than (20)); Query OK, 0 rows affected (0.03 sec)
查看表在磁盘上的物理文件,启用分区之后,表不再由一个id文件组成了,而是由建立分区时的各个分区ibd文件组成,如下面的t#P#p0.ibd,t#P#p1.ibd:
mysql> system sudo ls -lh /usr/local/mysql/data/test2; total 416 -rw-r----- 1 _mysql _mysql 65B 10 17 17:36 db.opt -rw-r----- 1 _mysql _mysql 96K 10 17 17:38 t#P#p0.ibd -rw-r----- 1 _mysql _mysql 96K 10 17 17:38 t#P#p1.ibd -rw-r----- 1 _mysql _mysql 8.4K 10 17 17:38 t.frm
接着插人如下数据:
mysql> insert into t select 9; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 10; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 15; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
因为表t根据列i进行分区,所以数据是根据列id的值的范围存放在不同的物理文件中的,可以通过查询 information schema架构下的 PARTITIONS表来查看每个分区的具体信息:
mysql> select * from information_schema.PARTITIONS -> where table_schema=database() and table_name='t'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test2 TABLE_NAME: t PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 10 TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: NULL INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2018-10-17 17:38:13 UPDATE_TIME: 2018-10-17 17:43:36 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test2 TABLE_NAME: t PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 20 TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: NULL INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2018-10-17 17:38:13 UPDATE_TIME: 2018-10-17 17:43:42 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 2 rows in set (0.01 sec)
TABLE ROWS列反映了每个分区中记录的数量。由于之前向表中插入了9、10、15三条记录,因此可以看到,当前分区p0中有1条记录,分区p1中有2条记录。
PARTITION_METHOD表示分区的类型,这里显示的是 RANGE。
对于表t,由于我们定义了分区,因此对于插入的值应该严格遵守分区的定义,当插入一个不在分区中定义的值时, MySQL数据库会抛出一个异常。如下所示,我们向表t中插入30这个值。
mysql> INSERT INTO t SELECT 30; ERROR 1526 (HY000): Table has no partition for value 30
对于上述问题,我们可以对分区添加一个 MAXVALUE值的分区。 MAXVALUE可以理解为正无穷,因此所有大于等于20且小于 MAXVALUE的值别放入p2分区。
mysql> alter table t add partition(partition p2 values less than maxvalue); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 30; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
RANGE分区主要用于日期列的分区,例如对于销售类的表,可以根据年来分区存放销售记录,如下面的分区表 sales:
mysql> create table sales( -> money int unsigned not null, -> date datetime -> )engine=innodb -> partition by range (year(date))( -> partition p2008 values less than (2009), -> partition p2009 values less than (2010), -> partition p2010 values less than (2011) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into sales select 100,'2008-01-01'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into sales select 100,'2008-02-01'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into sales select 200,'2008-01-02'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into sales select 100,'2009-03-01'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into sales select 200,'2010-03-01'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
这样创建的好处是,便于对 sales这张表的管理。如果我们要删除2008年的数据,不需要执行 DELETE FROM sales Where date>='2008-01-01' and date<'2009-01-01'只需删除2008年数据所在的分区即可:
alter table sales drop partition p2008;
这样创建的另一个好处是可以加快某些查询操作,如果我们只需要查询2008年整年的销售额,可以这样:
mysql> explain partitions select * from sales -> where date>='2008-01-01' and date<='2008-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales partitions: p2008 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 33.33 Extra: Using where 1 row in set, 2 warnings (0.01 sec)
通过 EXPLAIN PARTITION命令我们可以发现,在上述语句中,SQL优化器只需要去搜索p2008这个分区,而不会去搜索所有的分区—称为 Partition Pruning(分区修剪),故查询的速度得到了大幅度的提升。需要注意的是,如果执行下列语句,结果是样的,但是优化器的选择可能又会不同了:
mysql> explain partitions select * from sales -> where date>='2008-01-01' and date<='2009-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales partitions: p2008,p2009 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec)
这次条件改为date<'2009-01-01’而不是date<='2008-12-31’时,优化器会选择搜索p2008和p2009两个分区,这是我们不希望看到的。因此对于启用分区,应该根据分区的特性来编写最优的SQL语句。
对于 sales这张分区表,我曾看到过另一种分区函数,设计者的原意是按照每年每月来进行区分，如：
mysql> create table sales( -> money int unsigned not null, -> date datetime -> )engine=innodb -> partition by range (year(date)*100+month(date))( -> partition p201001 values less than (201002), -> partition p201002 values less than (201003), -> partition p201003 values less than (201004) -> ); Query OK, 0 rows affected (0.04 sec)
但是在执行SQL语句时开发人员发现,优化器不会根据分区进行选择,即使他们编写的SQL语句已经符合了分区的要求,如:
mysql> explain partitions select * from sales -> where date>='2010-01-01' and date<='2010-01-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales partitions: p201001,p201002,p201003 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec)
可以看到优化对分区p201001,p201002,p201003都进行了搜索。产生这个问题的主要原因是对于 RANGE分区的查询,优化器只能对 YEAR(), TO_DAYS(),TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化选择,因此对于上述的要求,需要将分区函数改为TO_DAYS,如:
mysql> create table sales( -> money int unsigned not null, -> date datetime -> )engine=innodb -> partition by range (to_days(date))( -> partition p201001 values less than( to_days('2010-02-01')), -> partition p201002 values less than( to_days('2010-03-01')), -> partition p201003 values less than( to_days('2010-04-01')) -> ); Query OK, 0 rows affected (0.04 sec)
这时再进行相同类型的查询,优化器就可以对特定的分区进行查询了。
mysql> explain partitions select * from sales -> where date>='2010-01-01' and date<='2010-01-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales partitions: p201001 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec)
2.2、LST分区
LIST分区和 RANGE分区非常相似,只是分区列的值是离散的,而非连续的。如:
mysql> CREATE TABLE t( -> a INT NOT NULL, -> 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) -> ); Query OK, 0 rows affected (0.03 sec)
不同于 RANGE分区中定义的 VALUES LESS THAN语句,LIST分区使用 VALUES IN。因为每个分区的值是离散的,因此只能定义值。例如向表t中插入一些数据:
mysql> INSERT INTO t select 1, 1; ERROR 1146 (42S02): Table 'test3.into t' doesn't exist mysql> INSERT INTO t select 1, 1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t select 1, 2; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t select 1, 3; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t select 1, 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select table_name,partition_name,table_rows -> from information_schema.PARTITIONS -> where table_name='t' and table_schema=database(); +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | t | p0 | 2 | | t | p1 | 2 | +------------+----------------+------------+ 2 rows in set (0.00 sec)
如果插入的值不在分区的定义中, MySQL数据库同样会抛出异常:
mysql> INSERt INTO t SElECT 1,10; ERROR 1526 (HY000): Table has no partition for value 10
另外,在用 INSERT插入多个行数据的过程中遇到分区未定义的值时, MyISAM和InnoDB存储引擎的处理完全不同。 MyISAM引擎会将之前的行数据都插入,但之后的数据不会被插入。而 InnoDB存储引擎将其视为一个事务,因此没有任何数据插入。
2.3、HASH分区
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据数量大致都是一样的。在 RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中, MySQL自动完成这些工作,用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表,要在 CREATE TABLE语句上添加一个“ PARTITION BY HASH(expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL整型的列名。此外,用户很可能需要在后面再添加一个“ PARTITIONS mum”子句,其中mm是一个非负的整数,它表示表将要被分割成分区的数量。如果没有包括个 PARTITIONS子句,那么分区的数量将默认为1。
下面的例子创建了一个HASH分区的表t,分区按日期列b进行:
mysql> CREATE TABLE t_hash( -> a INT, -> b DATETIME -> )ENGINE=InnoDB -> PARTITION BY HASH (YEAR(b)) PARTITIONS 4; Query OK, 0 rows affected (0.04 sec)
如果插入一个列b为2010-04-01的记录到表 t hash中,那么保存该条记录的分区如下:
MOD(YEAR(2010-04-01’),4)
=MOD(2010,4)
=2
因此记录会放入分区p2中,我们可以按如下方法来验证:
mysql> INSERT INTO t_hash SELECT 1,'2010-04-01'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select table_name,partition_name,table_rows -> from information_schema.PARTITIONS -> where table_schema=database() and table_name='t_hash'\G *************************** 1. row *************************** table_name: t_hash partition_name: p0 table_rows: 0 *************************** 2. row *************************** table_name: t_hash partition_name: p1 table_rows: 0 *************************** 3. row *************************** table_name: t_hash partition_name: p2 table_rows: 1 *************************** 4. row *************************** table_name: t_hash partition_name: p3 table_rows: 0 4 rows in set (0.00 sec)
可以看到p2分区有1条记录。当然这个例子中也许并不能把数据均匀地分布到各个分区中去,因为分区是按照YEAR函数进行的,而这个值本身可是离散的。如果对于连续的值进行HASH分区,如自增长的主键,则可以较好地将数据进行平均分布MySQL数据库还支持一种称为LINEAR HASH的分区,它使用一个更加复杂的算法来确定新行插人到已经分区的表中的位置。它的语法和HASH分区的语法相似,只是将关键字HASH改为LINEAR HASH。下面创建一个LINEAR HASH的分区表 t_linear_hash,它和之前的表 t_hash相似,只是分区类型不同。
mysql> CREATE TABLE t_linear_hash( -> a INT, -> b DATETIME -> )ENGINE=InnoDB -> PARTITION BY LINEAR HASH (YEAR (b)) -> PARTITIONS 4; Query OK, 0 rows affected (0.04 sec)
同样插入‘’2010-04-01’的记录,这次 MySQL数据库根据以下的方法来进行分区的判断:
- 取大于分区数量4的下一个2的幂值V,V= POWER(2, CEILING(LOG(2,num)))=4
- 所在分区N=YEAR('2010-04-01')&(V-1)=2。
虽然还是在分区P2,但是计算的方法和之前的HASH分区完全不同,接着进行插入实际数据的验证:
mysql> insert into t_linear_hash select 1,'2010-04-01'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_linear_hash'\G *************************** 1. row *************************** table_name: t_linear_hash partition_name: p0 table_rows: 0 *************************** 2. row *************************** table_name: t_linear_hash partition_name: p1 table_rows: 0 *************************** 3. row *************************** table_name: t_linear_hash partition_name: p2 table_rows: 1 *************************** 4. row *************************** table_name: t_linear_hash partition_name: p3 table_rows: 0 4 rows in set (0.00 sec)
LINEAR HASH分区的优点在于,增加、删除、合并和拆分分区将变得更加快捷,这有利于处理含有大量数据的表。它的缺点在于,与使用HASH分区得到的数据分布相比,各个分区间数据的分布可能不大均衡。
2.4、KEY分区
KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用 MySQL数据库提供的函数进行分区。对于 NDB Cluster引擎,MySQL数据库使用MD5函数来分区;对于其他存储引擎, MySQL数据库使用其内部的哈希函数,这些函数基于与 PASSWORD()一样的运算法则。如:
mysql> CREATE TABLE t_key( -> a int, -> b DATETIME)ENGINE=InnoDB -> PARTITION BY KEY (b) -> PARTITIONS 4; Query OK, 0 rows affected (0.03 sec)
在KEY分区中使用关键字 LINEAR和在HASH分区中使用具有同样的效果,分区的编号是通过2的幂( powers-of-two)算法得到的,而不是通过模数算法。
2.5、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,如:
mysql> 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') -> ); Query OK, 0 rows affected (0.03 sec)
同样可以直接使用字符串的分区:
mysql> create table customers_l( -> first_name varchar(25), -> last_name varchar(25) -> )partition by list columns(first_name)( -> partition p_first values in ('a','b'), -> partition p_first_1 values in ('c','d') -> ); Query OK, 0 rows affected (0.03 sec)
对于 RANGE COLUMNS分区,可以使用多个列进行分区,如:
mysql> CREATE TABLE rcx( -> a INT, -> b INT, -> c CHAR(3), -> d INT -> )Engine=InnoDB -> PARTITION BY RANGE COLUMNS(a, d, c)( -> PARTITION p0 VALUES LESS THAN (5,10,'ggg'), -> PARTITION p1 VALUES LESS THAN (10, 20,'mmm'), -> PARTITION p2 VALUES LESS THAN(15,30,'sss'), -> PARTITION P3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)); Query OK, 0 rows affected (0.04 sec)
MySQL5.5开始支持 COLUMNS分区,对于之前的 RANGE和LIST分区,用户可以用 RANGE COLUMNS和 LIST COLUMNS分区进行很好的代替。
3、子分区
子分区( subpartitioning)是在分区的基础上再进行分区,有时也称这种分区为复合分区( composite partitioning)。 MySQL数据库允许在 RANGE和LIST的分区上再进行HASH或KEY的子分区,如
mysql> 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); Query OK, 0 rows affected (0.04 sec) mysql> system sudo ls /usr/local/mysql/data/test3 |grep ts ts#P#P0#SP#P0sp0.ibd ts#P#P0#SP#P0sp1.ibd ts#P#p1#SP#p1sp0.ibd ts#P#p1#SP#p1sp1.ibd ts#P#p2#SP#p2sp0.ibd ts#P#p2#SP#p2sp1.ibd ts.frm
表ts先根据b列进行了 RANGE分区,然后又进行了一次HASH分区,所以分区的数量应该为(3×2=)6个,这通过查看物理磁盘上的文件也可以得到证实。我们也可以通过使用 SUBPARTITION语法来显式地指出各个子分区的名字,例如对上述的ts表同样可以这样:
mysql> 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 -> ) -> ); Query OK, 0 rows affected (0.04 sec)
子分区的建立需要注意以下几个问题:
- 每个子分区的数量必须相同。
- 要在一个分区表的任何分区上使用 SUBPARTITION来明确定义任何子分区,就必须定义所有的子分区。因此下面的创建语句是错误的:
mysql> 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), -> PARTITION p2 VALUES LESS THAN MAXVALUE( -> SUBPARTITION S4, -> SUBPARTITION S5 -> ) -> ); ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION S4, SUBPARTITION S5 ) )' at line 8 - 每个 SUBPARTITION子句必须包括子分区的一个名字。
- 子分区的名字必须是唯一的。
子分区可以用于特别大的表,在多个磁盘间分别分配数据和索引。假设有6个磁盘,分别为/disk0、/disk1、/disk2等。现在考虑下面的例子:
由于 InnoDB存储引擎使用表空间自动地进行数据和索引的管理,因此会忽略DATA DIRECTORY和 INDEX DIRECTORY语法,因此上述的分区表的数据和索引文件分开放置对其是无效的:
4、分区中的NULL值
MySQL数据库允许对NULL值做分区,但是处理的方法与其他数据库可能完全不同。 MYSQL数据库的分区总是视NULL值视小于任何的一个非NULL值,这和MySQL数据库中处理NULL值的 ORDER BY操作是一样的。因此对于不同的分区类型, MySQL数据库对于NULL值的处理也是各不相同。
对于 RANGE分区,如果向分区列插入了NUL值,则 MySQL数据库会将该值放入最左边的分区。
在LIST分区下要使用NULL值,则必须显式地指出哪个分区中放入NULL值,否则会报错。
HASH和KEY分区对于NULL的处理方式和 RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。
5、分区和性能
我常听到开发人员说“对表做个分区”,然后数据库的查询就会快了。这是真的吗?实际上可能根本感觉不到查询速度的提升,甚至会发现查询速度急剧下降。因此,在合理使用分区之前,必须了解分区的使用环境。
数据库的应用分为两类:一类是OLTP(在线事务处理),如Blog、电子商务、网络游戏等;另一类是OLAP(在线分析处理),如数据仓库、数据集市。在一个实际的应用环境中,可能既有OLTP的应用,也有OLAP的应用。如网络游戏中,玩家操作的游戏数据库应用就是OLTP的,但是游戏厂商可能需要对游戏产生的日志进行分析,通过分析得到的结果来更好地服务于游戏,预测玩家的行为等,而这却是OLAP的应用。
对于OLAP的应用,分区的确是可以很好地提高查询的性能,因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表,其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区,则只需要扫描相应的分区即可。这就是前面介绍的 Partition Pruning技术然而对于OLTP的应用,分区应该非常小心。在这种应用下,通常不可能会获取张大表中10%的数据,大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知,对于一张大表,一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作,不需要分区的帮助,并且设计不好的分区会带来严重的性能问题。
我发现很多开发团队会认为含有1000W行的表是一张非常巨大的表,所以他们往往会选择采用分区,如对主键做10个HASH的分区,这样每个分区就只有100W的数据了,因此查询应该变得更快了,如 SELECT* FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一种情况:100W和1000行的数据本身构成的B+树的层次都是一样的,可能都是2层。那么上述走主键分区的索引并不会带来性能的提高。好的,如果1000W的B+树的高度是3,100W的B+树的高度是2,那么上述按主键分区的索引可以避免1次IO,从而提高查询的效率。这没问题,但是这张表只有主键索引,没有任何其他的列需要查询的。如果还有类似如下的SQL语句: SELECT* FROM TABLE
WHERE KEY=@key,这时对于KEY的查询需要扫描所有的10个分区,即使每个分区的查询开销为2次IO,则一共需要20次IO。而对于原来单表的设计,对于KEY的查询只需要2~3次IO。
接着来看如下的表 Profile,根据主键ID进行了HASH分区,HASH分区的数量为10,表 Profile有接近1000W的数据: 
如果进行主键的查询,可以发现分区的确是有意义的:
可以发现只寻找了pl分区,但是对于表 Profile中 nickname列索引的查询, EXPLAIN PARTITIONS则会得到如下的结果。 
可以看到, MySQL数据库会搜索所有分区,因此查询速度上会慢很多。
因此对于使用 InnoDB存储引擎作为OLTP应用的表在使用分区时应该十分小心,设计时确认数据的访问模式,否则在OLTP应用下分区可能不仅不会带来查询速度的提高,反而可能会使你的应用执行得更慢。
6、在表和分区间交换数据
MySQL5.6开始支持 ALTER TABLE… EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空,那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空,则相当于将外部表中的数据导人到分区中。
要使用 ALTER TABLE… EXCHANGE PARTITION语句,必须满足下面的条件:
- 要交换的表需和分区表有着相同的表结构,但是表不能含有分区
- 在非分区表中的数据必须在交换的分区定义内
- 被交换的表中不能含有外键,或者其他的表含有对该表的外键引用
- 用户除了需要 ALTER、 INSERT和 CREATE权限外,还需要DROP的权限此外,有两个小的细节需要注意:
- 使用该语句时,不会触发交换表和被交换表上的触发器
- AUTO INCREMENT列将被重置
接着来看一个例子,首先创建含有 RANGE分区的表e,并填充相应的数据:
mysql> create table e( -> id int not null, -> fname varchar(30), -> lname varchar(30) -> )partition by range (id)( -> partition p0 values less than (50), -> partition p1 values less than (100), -> partition p2 values less than (150), -> partition p3 values less than (MAXVALUE) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into e values -> (1669,'Jim','Smith'), -> (337,'Mary','Jones'), -> (16,'Frank','White'), -> (2005,'Linda','Black'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
然后创建交换表e2。表e2的结构和表e一样,但需要注意的是表e2不能含有分区:
mysql> create table e2 like e; Query OK, 0 rows affected (0.03 sec) mysql> alter table e2 remove partitioning; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
通过下列语句观察分区表中的数据:
mysql> select partition_name,table_rows from information_schema.PARTITIONS where table_name='e'; +----------------+------------+ | partition_name | table_rows | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.02 sec)
因为表e中的没有数据,使用如下语句将表e的分区p0中的数据移动到表e2中:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.05 sec)
这时再观察表e中分区的数据,可以发现p0中的数据已经没有了。
mysql> select partition_name,table_rows from information_schema.PARTITIONS where table_name='e'; +----------------+------------+ | partition_name | table_rows | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.01 sec)
而这时可以在表e2中观察到被移动的数据:
mysql> select * from e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ 1 row in set (0.01 sec)
