MySQL分区实战以及分区的管理
冯帅，精通Oracle、 MySQL，擅长异构数据库数据同步及迁移、数据库的设计和调优，,深入了解RAC、DG、OGG等高可用方案，熟悉MySQL数据库开发、备份与恢复 。
CREATE TABLE part_tab
( c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
create table no_part_tab
(c1 int(11) default NULL,
c2 varchar(30) default NULL,
c3 date default NULL) engine=myisam;
delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end
//
delimiter ;
call load_part_tab();
insert into no_part_tab select * from part_tab;
select count(*) from no_part_tab where
c3 > date '1995-01-01' and c3 < date '1995-12-31';
select count(*) from part_tab where
c3 > date '1995-01-01' and c3 < date '1995-12-31';
explain select count(*) from no_part_tab where
c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
explain partitions select count(*) from part_tab where
c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
垂直呢？
alter table part_tab drop column c2;
INNODB呢？
CREATE TABLE part_tab
( c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
insert into part_tab select * from no_part_tab;
alter table part_tab engine=innodb;
1.RANGE分区
基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。以下是实例。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (6),
partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21)
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch ... case”语句。
对于包含数据 insert into employees values(72, 'Michael', 'Widenius', '1998-06-25', '9999-12-31', 13,7);的一个新行，可以很容易地确定它将插入到p2分区中，但是如果增加了一个编号为第21的商店，将会发生什么呢？在这种方案下，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。 要避免这种错误，
insert into employees values(72, 'Michael', 'Widenius', '1998-06-25', '9999-12-31', 13,22);
ERROR 1526 (HY000): Table has no partition for value 22
可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句，该子句提供给所有大于明确指定的最高值的值：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
)；
MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
在几乎一样的结构中，你还可以基于雇员的工作代码来分割表，也就是说，基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通（店内的）工人，三个数字代码表示办公室和支持人员，四个数字代码表示管理层，你可以使用下面的语句创建该分区表：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
在这个例子中, 店内工人相关的所有行将保存在分区p0中，办公室和支持人员相关的所有行保存在分区p1中，管理层相关的所有行保存在分区p2中。
在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分；因此，表达式的值不能为NULL 。由于这个原因，雇员表的hired, separated, job_code,和store_id列已经被定义为非空（NOT NULL）。
除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
)；
在这个方案中，在1991年前雇佣的所有雇员的记录保存在分区p0中，1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中， 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中，2000年后雇佣的所有工人的信息保存在p3中。
RANGE分区在如下场合特别有用：
1）、 当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0；”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990；”这样的一个DELETE查询要有效得多。
2）、想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
3）、经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
注释：这种优化还没有在MySQL 5.1源程序中启用，但是，有关工作正在进行中。
2.LIST分区
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
注释：在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)；
假定有20个音像店，分布在4个有经销权的地区，如下表所示：
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；
这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。
【要点】：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：
INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。
3.HASH分区
基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4；
如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 例外： 对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。
1.)LINER HASH
MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4；
假设一个表达式expr, 当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到：
1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下面的公式得到：
2. V = POWER(2, CEILING(LOG(2, num)))
（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。
CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。
3. 设置 N = F(column_list) & (V - 1).
4. 当 N >= num:
• 设置 V = CEIL(V / 2)
• 设置 N = N & (V - 1)
例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插入两行记录到表t1中，其中一条记录col3列值为'2003-04-14'，另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下：
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下：
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(6 >= 4 为真（TRUE）: 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
= 6 & 3
= 2
(2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000GA）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。
4.KSY分区
类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。
分区表管理
对指定表添加分区
alter table user add partition(partition p4 values less than MAXVALUE); //只能添加大于分区键的分区
删除指定表指定分区
alter table student drop partition p1;
创建子分区
create table role_subp(id int(20) not null,name int(20) not null)
partition by list(id)
subpartition by hash(name)
subpartitions 3
(
partition p1 values in(10),
partition p2 values in(20)
)
复合分区
alter table user
reorganize partition p1,p3 into
(partition p1 values less than (1000));
rebuild重建分区
alter table xxxxxx rebuild partition pm1/all; //相当于drop所有记录，然后再reinsert；可以解决磁盘碎片
优化表
alter table tt2 optimize partition pm1; //在大量delete表数据后，可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代，如果用rebuild+analyze速度慢
analzye表
alter table xxxxxx analyze partition pm1/all;
show create table employees2; //查看分区表的定义
show table status like 'employees2'\G; //查看表时候是分区表 如“Create_options: partitioned”
select * from information_schema.KEY_COLUMN_USAGE where table_name='employees2'; //查看索引
SELECT * FROM information_schema.partitions WHERE table_name='employees2' //查看分区表
explain partitions select * from employees2 where separated < '1990-01-01' or separated > '2016-01-01'; //查看分区是否被select使用
分区管理和操作：
RANGE,LIST分区管理
1:为未分区表创建分区
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
2:删除某个分区的数据
ALTER TABLE tr DROP PARTITION p2;
3:为分区表添加一个分区
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
4:将分区表的第一个分区分为两个新的分区
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1960),
PARTITION n1 VALUES LESS THAN (1970)
);
5:也可以将两个分区合并为一个分区,也可以理解为重新组织分区
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
ALTER TABLE tbl_name
REORGANIZE PARTITION partition_list
INTO (partition_definitions);
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
删除分区表：alter table tb_user remove partitioning;
HASH,KEY 分区管理
1:创建一个hash分区表
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
将分区表从12个分区变为8个分区
ALTER TABLE clients COALESCE PARTITION 4;
同样的有以下的语句关于KEY分区的表:
mysql> CREATE TABLE clients_lk (
-> id INT,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> signed DATE
-> )
-> PARTITION BY LINEAR KEY(signed)
-> PARTITIONS 12;Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
当然还有有限制的
mysql> ALTER TABLE clients COALESCE PARTITION 18;ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead
要是分区数比现有的分区数多的话,只能使用 ADD来添加分区数.下面就表示增加了6个分区数
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
交换分区,子分区的管理
交换分区,ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt
where pt is the partitioned table and p is the partition or subpartition of pt to be exchanged with unpartitioned table nt, provided that the following statements are true:
要满足以下的条件:
1:PT是已经分区表,nt不是临时表
2:两张表的表结构必须是一模一样的
3:nt不能有外键约束,也不能有关于其他表的外键约束.
4:nt表中的数据没有分区P以外的数据.WITHOUT VALIDATION指定的时候这条就可以忽视调
另外很重要的一点就是想要拥有EXCHANGE的权限的话必须对全表有DROP的权限才可以执行.
alter table ......EXCHANGE partition 将不会调用任何的触发器,执行完以后被EXCHANGE 的表的自增列就会重新赋初始值.
例如:
ALTER TABLE pt
EXCHANGE PARTITION p
WITH TABLE nt with VALIDATION ;
1:将分区和一个没有分区的表EXCHANGE
创建表插入语句:
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)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
查看分区和分区的行数
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'e';
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'e';
创建新表:
CREATE TABLE e2 LIKE e;
然后交换分区开始了:
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
这个语句是很奇怪的,如果e2里面没有数据的话就是切出分区,如果e2里面有数据的话就是相互交换
但是如果e2里面的数据不能够满足分区p0的要求的话,切分区就会失败.
1737 - Found a row that does not match the partition
只有再指定不验证的时候才不会报错
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
WITHOUT VALIDATION 指定的时候效率会更高,因为不再做逐行验证了。
子分区和没分区的表进行切换
1:假设创建一个分区表,带有子分区
然后就可以切分区了,先查看一下分区,
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'es';
然后切出分区:
ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
当前执行切出分区前,必须要对新表做以下的处理:
ALTER TABLE es2 REMOVE PARTITIONING;
修改表的默认引擎:
ALTER TABLE es3 ENGINE = MyISAM;
维护表分区
1:重建分区
ALTER TABLE t1 REBUILD PARTITION p0, p1;
2:重新组织分区
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
3:分析某个分区,主要看行数和名称以及状态
ALTER TABLE t1 ANALYZE PARTITION p3;
4:修复分区,有重复值的时候就会报错.
ALTER TABLE t1 REPAIR PARTITION p0,p1;
5:检查分区的状态
ALTER TABLE trb3 CHECK PARTITION p1;
6:truncate分区
ALTER TABLE ... TRUNCATE PARTITION.
ALTER TABLE ... TRUNCATE PARTITION ALL
7:获取表的信息
Using the SHOW CREATE TABLE statement to view the partitioning clauses used in creating a partitioned table.
Using the SHOW TABLE STATUS statement to determine whether a table is partitioned.
Querying the INFORMATION_SCHEMA.PARTITIONS table.
Using the statement EXPLAIN PARTITIONS SELECT to see which partitions are used by a given SELECT.
看以下信息:
分区的经典案例:
mysql> CREATE TABLE employees_sub (
-> id INT NOT NULL AUTO_INCREMENT,
-> fname VARCHAR(25) NOT NULL,
-> lname VARCHAR(25) NOT NULL,
-> store_id INT NOT NULL,
-> department_id INT NOT NULL,
-> PRIMARY KEY pk (id, lname)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN (15),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
分区的一些添删查修语句:
mysql> DELETE FROM employees PARTITION (p0, p1)
-> WHERE fname LIKE 'j%';Query OK, 2 rows affected (0.09 sec)
mysql> UPDATE employees PARTITION (p2)
-> SET store_id = 2 WHERE fname = 'Jill';
SELECT * FROM employees PARTITION (p2);
mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);ERROR 1729 (HY000): Found a row not matching the given partition setmysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);Query OK, 1 row affected (0.07 sec)
分区不够多,要添加分区:
ysql> ALTER TABLE employees
-> REORGANIZE PARTITION p3 INTO (
-> PARTITION p3 VALUES LESS THAN (20),
-> PARTITION p4 VALUES LESS THAN (25),
-> PARTITION p5 VALUES LESS THAN MAXVALUE
-> );Query OK, 6 rows affected (2.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
长按二维码关注公众号AIX专家俱乐部