MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数十个物理分区对象组成,每个分区都是一个独立的对象,可以独自处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。
1、查看使用的MySQL是否支持分区表。
5.6.1版本以后使用show plugins;命令查看
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> show plugins;
+
----------------------------+----------+--------------------+---------+---------+
|
Name
| Status | Type | Library | License |
+
----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION |
NULL
| GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION |
NULL
| GPL |
| sha256_password | ACTIVE | AUTHENTICATION |
NULL
| GPL |
......
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION
SCHEMA
|
NULL
| GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION
SCHEMA
|
NULL
| GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| partition | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
+
----------------------------+----------+--------------------+---------+---------+
|
5.6.1以前的版本使用show variables like '%partition%';命令查看
1
2
3
4
5
6
|
mysql> show variables
like
'%partition%'
;
+
-------------------+-------+
| Variable_name | Value |
+
-------------------+-------+
| have_partitioning | YES |
+
-------------------+-------+
|
2、分区类型
MySQL中可用的分区类型主要有以下4种:
-
RANGE分区:基于一个给定连续区间范围,把数据分配到不同的分区。
-
LIST分区:类似RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区。
-
HASH分区:基于给定的分区个数,把数据分配到不同的分区。
-
KEY分区:类似于HASH分区。
RANGE分区、LIST分区、HASH分区都要求分区必须是INT类型,或者通过表达式返回INT类型,唯一的例外就是分区类型为KEY分区时,可以使用其他类型的列(BLOB或TEXT列类型除外)作为分区键。在5.5或以上版本中,已经支持非整数的RANGE和LIST分区了,使用RANGE COLUMNS和LIST COLUMNS分区实现(后面介绍)。无论是哪种MySQL分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须都包含分区键。
2.1、RANGE分区
按照RANGE分区的表是利用取值范围将数据分成分区,区间要连续并且不能互相重叠,使用VALUES LESS THAN 操作符进行分区定义。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql>
create
table
emp (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
range (store_id)
-> (
-> partition p0
values
less than (10),
-> partition p1
values
less than (20),
-> partition p2
values
less than (30)
-> );
Query OK, 0
rows
affected (0.08 sec)
mysql> show
create
table
emp \G;
*************************** 1. row ***************************
Table
: emp
Create
Table
:
CREATE
TABLE
`emp` (
`id`
int
(11)
NOT
NULL
,
`ename`
varchar
(30)
DEFAULT
NULL
,
`hired`
date
NOT
NULL
DEFAULT
'2016-01-01'
,
`separated`
date
NOT
NULL
DEFAULT
'9999-12-31'
,
`job`
varchar
(30)
NOT
NULL
,
`store_id`
int
(11)
NOT
NULL
) ENGINE=InnoDB
DEFAULT
CHARSET=latin1
/*!50100 PARTITION
BY
RANGE (store_id)
(PARTITION p0
VALUES
LESS THAN (10) ENGINE = InnoDB,
PARTITION p1
VALUES
LESS THAN (20) ENGINE = InnoDB,
PARTITION p2
VALUES
LESS THAN (30) ENGINE = InnoDB) */
1 row
in
set
(0.00 sec)
|
emp表可以存储store_id<30的数据,并且根据store_id值选择不同的分区,如store_id=9则存储在p0分区,如果store_id=10则存储在p1分区。如果插入store_id大于30的行会报错:
1
2
|
mysql>
insert
into
emp (id,ename,hired,job,store_id)
values
(
'1'
,
'zx'
,
'2016-01-01'
,
'zx'
,50);
ERROR 1526 (HY000):
Table
has
no
partition
for
value 50
|
可以在设置分区的时候使用使用VALUES LESS THAN MAXVALUE 子名,该子名提供给所有大于明确指定的最高值的值,MAXVALUES表示最大的可能的整数值。
1
2
3
4
5
6
|
mysql>
alter
table
emp
add
partition (partition p3
values
less than maxvalue);
Query OK, 0
rows
affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
insert
into
emp (id,ename,hired,job,store_id)
values
(
'1'
,
'zx'
,
'2016-01-01'
,
'zx'
,50);
Query OK, 1 row affected (0.02 sec)
|
MySQL支持在VALUES LESS THAN 子句中使用表达式,比如,以日期作为RANGE分区的分区列:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql>
create
table
emp_date (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
range (
YEAR
(separated))
-> (
-> partition p0
values
less than (2014),
-> partition p1
values
less than (2015),
-> partition p2
values
less than (2016)
-> );
Query OK, 0
rows
affected (0.48 sec)
|
查看语句的执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> explain partitions
select
*
from
emp
where
store_id=50\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: emp
partitions: p3
type:
ALL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref:
NULL
rows
: 2
Extra: Using
where
1 row
in
set
(0.00 sec)
ERROR:
No
query specified
|
2.2 LIST分区
LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区,LIST分区在很多方面类似于RANGE分区,区别在LIST分区是从属于一个枚举列表的值的集合,RANGE分区是从属于一个连续区间值的集合。
LIST分区通过使用PARTITION BY LIST(expr)子名来实现,expr是某列值或一个基于某列值返回一个整数值的表达式,然后通过VALUES IN(value_list)的方式来定义分区,其中value_list是一个逗号分隔的整数列表。与RANGE分区不同,LIST分区不必声明任何特定的顺序。
1
2
3
4
5
6
7
8
9
10
|
mysql>
create
table
expenses(
-> expense_date
date
not
null
,
-> category
int
,
-> amount
decimal
(10,3))
-> partition
by
list (category)
-> (
-> partition p0
values
in
(3,5),
-> partition p1
values
in
(1,10)
-> );
Query OK, 0
rows
affected (0.73 sec)
|
如果试图插入的列值(或者分区表达式的反回值)不包含分区值列表中时,那么INSERT操作会失败并报错。
1
2
|
mysql>
insert
into
expenses(expense_date,category)
values
(
'2016-11-25'
,2);
ERROR 1526 (HY000):
Table
has
no
partition
for
value 2
|
2.3 Columns分区
Columns分区是在MySQL5.5引入的分区类型,引入Columns分区解决了MySQL5.5版本之前RANGE分区和LIST分区只支持整数分区,从而导致需要额外的函数计算等到整数或通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns和LIST Columns分区,它们都支持整数、日期时间、字符串三大数据类型。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql>
drop
table
emp_date;
Query OK, 0
rows
affected (0.06 sec)
mysql>
create
table
emp_date (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
range columns (separated)
-> (
-> partition p0
values
less than (
'2014-01-01'
),
-> partition p1
values
less than (
'2015-01-01'
),
-> partition p2
values
less than (
'2016-01-01'
)
-> );
Query OK, 0
rows
affected (0.10 sec)
mysql>
drop
table
expenses;
Query OK, 0
rows
affected (0.08 sec)
mysql>
create
table
expenses(
-> expense_date
date
not
null
,
-> category
varchar
(30),
-> amount
decimal
(10,3))
-> partition
by
list columns (category)
-> (
-> partition p0
values
in
(
'food'
,
'lodging'
),
-> partition p1
values
in
(
'flights'
)
-> );
Query OK, 0
rows
affected (0.13 sec)
|
对比RANGE分区和LIST分区,Columns分区的亮点除了支持数据类型增加之外,另外一大亮点是Columns分区还支持多列分区。
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql>
create
table
rc3(
-> a
int
,
-> b
int
)
-> partition
by
range columns (a,b)
-> (
-> partition p01
values
less than (1,10),
-> partition p02
values
less than (10,10),
-> partition p03
values
less than (10,20),
-> partition p04
values
less than (10,maxvalue),
-> partition p05
values
less than (maxvalue,maxvalue)
-> );
Query OK, 0
rows
affected (0.23 sec)
|
RANGE Columns 分区键的比较是基于元组的比较,也就是基于字段组的比较,这和之前RANGE分区键的比较有些差异,比较逻辑为(a<10) or ((a=10)and(b<10))。下面插入几条测试数据验证一下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
--插入(0,11)
mysql>
insert
into
rc3(a,b)
values
(0,11);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
->
from
information_schema.partitions
->
where
table_schema=
schema
()
and
table_name=
'rc3'
;
+
------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+
------+---------+-------------------+------------+
| p01 | `a`,`b` | 1,10 | 1 |
| p02 | `a`,`b` | 10,10 | 0 |
| p03 | `a`,`b` | 10,20 | 0 |
| p04 | `a`,`b` | 10,MAXVALUE | 0 |
| p05 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+
------+---------+-------------------+------------+
5
rows
in
set
(0.01 sec)
--插入(1,10)
mysql>
insert
into
rc3(a,b)
values
(1,10);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
->
from
information_schema.partitions
->
where
table_schema=
schema
()
and
table_name=
'rc3'
;
+
------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+
------+---------+-------------------+------------+
| p01 | `a`,`b` | 1,10 | 1 |
| p02 | `a`,`b` | 10,10 | 1 |
| p03 | `a`,`b` | 10,20 | 0 |
| p04 | `a`,`b` | 10,MAXVALUE | 0 |
| p05 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+
------+---------+-------------------+------------+
--插入(12,15)
mysql>
insert
into
rc3(a,b)
values
(12,15);
Query OK, 1 row affected (0.08 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
->
from
information_schema.partitions
->
where
table_schema=
schema
()
and
table_name=
'rc3'
;
+
------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+
------+---------+-------------------+------------+
| p01 | `a`,`b` | 1,10 | 1 |
| p02 | `a`,`b` | 10,10 | 1 |
| p03 | `a`,`b` | 10,20 | 0 |
| p04 | `a`,`b` | 10,MAXVALUE | 0 |
| p05 | `a`,`b` | MAXVALUE,MAXVALUE | 1 |
+
------+---------+-------------------+------------+
5
rows
in
set
(0.01 sec)
|
2.4 HASH分区
HASH分区主要用来分散热点读,确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行HASH分区时,Mysql会对分区键应用一个英散列函数,以此确定数据应当放在N个分区中的哪个。MySQL支持两种HASH分区:常规HASH分区和线性HASH分区(LINEAR HASH分区)。常规HAS使用的是取模算法,线性HASH分区使用的是一个线性的2的幂的运算法则。
常规HASH分区使用PARTITION BY HASH(expr) PARTITIONS num子句来实现。exp是某列值或一个基于某列值返回一个整数值的表达式,num是一个非负整数,表示分割成分区的数量,默认num=1。数据保存在哪个分区N=MOD(expr,num)。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
mysql>
create
table
emp (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
hash (store_id) partitions 4;
Query OK, 0
rows
affected (0.11 sec)
mysql>
insert
into
emp (id,ename,hired,job,store_id)
values
(
'1'
,
'zx'
,
'2016-01-01'
,
'zx'
,50);
Query OK, 1 row affected (0.04 sec)
mysql>
select
mod(50,4);
+
-----------+
| mod(50,4) |
+
-----------+
| 2 |
+
-----------+
1 row
in
set
(0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'emp'
;
+
------+----------+-------+------------+
| part | expr | descr | table_rows |
+
------+----------+-------+------------+
| p0 | store_id |
NULL
| 0 |
| p1 | store_id |
NULL
| 0 |
| p2 | store_id |
NULL
| 1 |
| p3 | store_id |
NULL
| 0 |
+
------+----------+-------+------------+
|
常规HASH分区存在的问题,如果新增一个常规HASH分区,原来的取模算法是MOD(exp,4)就需要修改为MOD(exp,5),原来4个分区中的数据大部分都需要通过重新计算新分区,代价太大。为了降低分区处理上的代价,MySQL提供了线性HASH分区,分区函数是一个线性的2的幂的运算法则。
线性HASH分区和常规HASH分区在语法上的唯一区别是在"PARTITION BY"子句中添加"LINEAR"关键字
1
2
3
4
5
6
7
8
9
|
mysql>
create
table
emp (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
linear hash (store_id) partitions 4;
Query OK, 0
rows
affected (0.89 sec)
|
2.5 KEY分区
按照Key进行分区非常类似于按照HASH分区,只不过HASH分区允许使用用户自定义的表达式,而Key分区不允许,需要使用Mysql服务器提供的HASH函数。Key分区支持使用除BLOB或TEXT类型外的其他类型列作为分区键。使用PARTITION BY KEY(expr)子句来创建一个Key分区表,exp是0个或多个字段名我的列表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
mysql>
create
table
emp (
-> id
int
not
null
,
-> ename
varchar
(30),
-> hired
date
not
null
default
'2016-01-01'
,
-> separated
date
not
null
default
'9999-12-31'
,
-> job
varchar
(30)
not
null
,
-> store_id
int
not
null
)
-> partition
by
key
(job) partitions 4;
Query OK, 0
rows
affected (0.17 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'emp'
;
+
------+-------+-------+------------+
| part | expr | descr | table_rows |
+
------+-------+-------+------------+
| p0 | `job` |
NULL
| 0 |
| p1 | `job` |
NULL
| 0 |
| p2 | `job` |
NULL
| 0 |
| p3 | `job` |
NULL
| 0 |
+
------+-------+-------+------------+
4
rows
in
set
(0.01 sec)
mysql>
insert
into
emp (id,ename,hired,job,store_id)
values
(
'1'
,
'zx'
,
'2016-01-01'
,
'zx'
,50);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'emp'
;
+
------+-------+-------+------------+
| part | expr | descr | table_rows |
+
------+-------+-------+------------+
| p0 | `job` |
NULL
| 0 |
| p1 | `job` |
NULL
| 0 |
| p2 | `job` |
NULL
| 0 |
| p3 | `job` |
NULL
| 1 |
+
------+-------+-------+------------+
4
rows
in
set
(0.00 sec)
mysql>
insert
into
emp (id,ename,hired,job,store_id)
values
(
'1'
,
'zx'
,
'2016-01-01'
,
'lx'
,50);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'emp'
;
+
------+-------+-------+------------+
| part | expr | descr | table_rows |
+
------+-------+-------+------------+
| p0 | `job` |
NULL
| 0 |
| p1 | `job` |
NULL
| 1 |
| p2 | `job` |
NULL
| 0 |
| p3 | `job` |
NULL
| 1 |
+
------+-------+-------+------------+
4
rows
in
set
(0.00 sec)
|
可以不指定分区键,默认会首先选择使用主键作为分区键,没有主键的情况会选择非空唯一键作为分区键,没有主键也没有唯一键的情况就不能不指定分区键了。Key分区也可以像HASH分区一样使用LINEAR KEY分区。
2.6子分区
子分区(subpartitioning)是分区表中对每个分区的再次分割对被称为复合分区(composite partitioning)。从5.1版本开始支持通过RANGE或LIST分区了的表再进行子分区,子分区可以使用HASH分区或KEY分区。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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.22 sec)
mysql>
select
partition_name part,subpartition_name subpart,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'ts'
;
+
------+---------+------------------+----------+------------+
| part | subpart | expr | descr | table_rows |
+
------+---------+------------------+----------+------------+
| p0 | p0sp0 |
YEAR
(purchased) | 1990 | 0 |
| p0 | p0sp1 |
YEAR
(purchased) | 1990 | 0 |
| p1 | p1sp0 |
YEAR
(purchased) | 2000 | 0 |
| p1 | p1sp1 |
YEAR
(purchased) | 2000 | 0 |
| p2 | p2sp0 |
YEAR
(purchased) | MAXVALUE | 0 |
| p2 | p2sp1 |
YEAR
(purchased) | MAXVALUE | 0 |
+
------+---------+------------------+----------+------------+
6
rows
in
set
(0.00 sec)
|
2.7 MySQL分区处理NULL值的方式
MySQL不禁止在分区键值上使用NULL,分区键可能是一个字段或都一个用户定义的表达式。一般情况下,MySQL的分区把NULL当作0值或一个最小值进行处理。RANGE分区中,NULL值 会被当作最小值来处理;LIST分区中,NULL值必须出现在枚举列表中,否则不被接受;HASH/KEY分区中,NULL值被当作0值来处理。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql>
create
table
tb_range(id
int
,
name
varchar
(2))
-> partition
by
range(id)
-> (
-> partition p0
values
less than (0),
-> partition p1
values
less than (100),
-> partition p2
values
less than maxvalue
-> );
Query OK, 0
rows
affected (0.11 sec)
mysql>
insert
into
tb_range
values
(
null
,
'a'
);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'tb_range'
;
+
------+------+----------+------------+
| part | expr | descr | table_rows |
+
------+------+----------+------------+
| p0 | id | 0 | 1 |
| p1 | id | 100 | 0 |
| p2 | id | MAXVALUE | 0 |
+
------+------+----------+------------+
3
rows
in
set
(0.00 sec)
|
LIST分区处理NULL值示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql>
create
table
tb_list(id
int
,
name
varchar
(2))
-> partition
by
list(id)
-> (
-> partition p0
values
in
(0),
-> partition p1
values
in
(1)
-> );
Query OK, 0
rows
affected (0.09 sec)
mysql>
insert
into
tb_list
values
(
null
,
'a'
);
ERROR 1526 (HY000):
Table
has
no
partition
for
value
NULL
mysql>
alter
table
tb_list
add
partition(partition p2
values
in
(
null
));
Query OK, 0
rows
affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
insert
into
tb_list
values
(
null
,
'a'
);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'tb_list'
;
+
------+------+-------+------------+
| part | expr | descr | table_rows |
+
------+------+-------+------------+
| p0 | id | 0 | 0 |
| p1 | id | 1 | 0 |
| p2 | id |
NULL
| 1 |
+
------+------+-------+------------+
3
rows
in
set
(0.00 sec)
|
HASH分区处理NULL值示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql>
create
table
tb_hash(id
int
,
name
varchar
(2))
-> partition
by
hash(id) partitions 2;
Query OK, 0
rows
affected (0.12 sec)
mysql>
insert
into
tb_hash
values
(
null
,
'a'
);
Query OK, 1 row affected (0.01 sec)
mysql>
select
partition_name part,partition_expression expr,partition_description descr,table_rows
from
information_schema.partitions
where
table_schema=
schema
()
and
table_name=
'tb_hash'
;
+
------+------+-------+------------+
| part | expr | descr | table_rows |
+
------+------+-------+------------+
| p0 | id |
NULL
| 1 |
| p1 | id |
NULL
| 0 |
+
------+------+-------+------------+
2
rows
in
set
(0.00 sec)
|
参考:《深入浅出MySQL》
官方文档:http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
本文转自hbxztc 51CTO博客,原文链接:http://blog.51cto.com/hbxztc/1876610,如需转载请自行联系原作者