呼和浩特制作网站,全国建筑行业资质查询平台,深圳制作网站有用吗,来个网站好人有好报以下是我看MySQL官方文档的时候整理的笔记#xff0c;仅作参考保留。RANGE,LIST分区管理1:为未分区表创建分区 ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; 2:删除某个分区的数据 ALTER TABLE tr DROP PARTITION p2; 3:为分区表添加一个分区 ALTER TABLE members AD… 以下是我看MySQL官方文档的时候整理的笔记仅作参考保留。 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 membersREORGANIZE 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_nameREORGANIZE PARTITION partition_listINTO (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)
); 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:假设创建一个分区表,带有子分区 CREATE TABLE es (- id INT NOT NULL,- fname VARCHAR(30),- lname VARCHAR(30)- )- PARTITION BY RANGE (id)- SUBPARTITION BY KEY (lname)- SUBPARTITIONS 2 (- PARTITION p0 VALUES LESS THAN (50),- PARTITION p1 VALUES LESS THAN (100),- PARTITION p2 VALUES LESS THAN (150),- PARTITION p3 VALUES LESS THAN (MAXVALUE)- ); 然后就可以切分区了,先查看一下分区, SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE 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 EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: trb1partitions: p0,p1,p2,p3type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 10Extra: Using filesort 分区的经典案例: 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 set
mysql 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 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 membersREORGANIZE 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_nameREORGANIZE PARTITION partition_listINTO (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)
); 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:假设创建一个分区表,带有子分区 CREATE TABLE es (- id INT NOT NULL,- fname VARCHAR(30),- lname VARCHAR(30)- )- PARTITION BY RANGE (id)- SUBPARTITION BY KEY (lname)- SUBPARTITIONS 2 (- PARTITION p0 VALUES LESS THAN (50),- PARTITION p1 VALUES LESS THAN (100),- PARTITION p2 VALUES LESS THAN (150),- PARTITION p3 VALUES LESS THAN (MAXVALUE)- ); 然后就可以切分区了,先查看一下分区, SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE 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 EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: trb1partitions: p0,p1,p2,p3type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 10Extra: Using filesort 分区的经典案例: 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 set
mysql 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 最后欢迎加入技术交流群291519319 转载于:https://www.cnblogs.com/shengdimaya/p/5384884.html
