Mysql 5.1后支持哪些子分区

前面提到了Mysql中的分区，现在我们来了解下子分区。大家都知道，分区有哪几类：range分区，List分区，hash分区，key分区四类，那么我们5.1后期能支持几种子分区呢？下面做个实验：

range_hash复合分区：

mysql> CREATE TABLE ts (id INT, purchased DATE)

-> -> PARTITION BY RANGE(YEAR(purchased)) -> -> SUBPARTITION BY HASH(TO_DAYS(purchased)) -> -> 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.11 sec)

5.1.58是支持range_hash分区，我们再看下分区是怎么存储的

mysql> insert into ts(id,purchased)values(1,'1989-12-26'); Query OK, 1 row affected (0.20 sec)

mysql> select table_name,partition_name ,table_rows

-> from information_schema.partitions -> where table_name='ts';

table_name	partition_name	table_rows
ts	p0	0
ts	p0	1
ts	p1	0
ts	p1	0
ts	p2	0
ts	p2	0

6 rows in set (0.14 sec)

mysql> insert into ts(id,purchased)values(1,'2001-12-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='ts';

table_name	partition_name	table_rows
ts	p0	0
ts	p0	1
ts	p1	0
ts	p1	0
ts	p2	1
ts	p2	0

6 rows in set (0.01 sec)

mysql> insert into ts(id,purchased)values(1,'1989-2-26'); Query OK, 1 row affected (0.01 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='ts'; +------------+----------------+------------+

table_name	partition_name	table_rows
ts	p0	1
ts	p0	1
ts	p1	0
ts	p1	0
ts	p2	1
ts	p2	0

上面介绍的是range_hash复合分区及其存储情况，下面我们来看下支不支持list_hash复合分区

mysql> CREATE TABLE tst (id INT, purchased DATE)

-> -> PARTITION BY list(id) -> -> SUBPARTITION BY HASH(year(purchased)) -> -> SUBPARTITIONS 2 -> -> ( -> -> PARTITION p0 VALUES IN (1, 3, 5), -> -> PARTITION p1 VALUES IN (2, 4, 6) -> -> );

Query OK, 0 rows affected (0.05 sec)

mysql> insert into tst(id,purchased)values(4,'2000-2-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='tst';

table_name	partition_name	table_rows
tst	p0	0
tst	p0	0
tst	p1	1
tst	p1	0

4 rows in set (0.00 sec)

mysql> insert into tst(id,purchased)values(5,'2001-2-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='tst';

table_name	partition_name	table_rows
tst	p0	0
tst	p0	1
tst	p1	1
tst	p1	0

4 rows in set (0.00 sec)

mysql> insert into tst(id,purchased)values(5,'2002-2-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='tst';

table_name	partition_name	table_rows
tst	p0	1
tst	p0	1
tst	p1	1
tst	p1	0

4 rows in set (0.00 sec)

mysql> insert into tst(id,purchased)values(1,'2002-2-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='tst';

table_name	partition_name	table_rows
tst	p0	2
tst	p0	1
tst	p1	1
tst	p1	0

4 rows in set (0.00 sec)

mysql> insert into tst(id,purchased)values(2,'2002-2-26'); Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='tst';

table_name	partition_name	table_rows
tst	p0	2
tst	p0	1
tst	p1	2
tst	p1	0

5.1暂时不支持list_range复合分区

转载 http://xuebinbin212.blog.163.com/blog/static/112167376201111294221100/