mysql分区语句

news/2024/11/23 20:18:46/

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

http://www.ppmy.cn/news/547060.html

相关文章

Oracle分区

Oracle分区

ORACLE 分区 分区的原因分区的优势分区方式分区sql说明分区方式 oracle 11g 支持自动分区，不过得在创建表时就设置好分区。 分区的原因 单表的数据量如果太大，会影响到读写性能。我们可以使用分库分表来解决单表的性能问题。Oracle的分区表是将一张大表…
阅读更多...
Kafka分区策略

Kafka分区策略

生产者分区策略 1.分区的原因 （1）方便在集群中扩展，每个Partition可以通过调整以适应它所在的机器，而一个topic又可以有多个Partition组成，因此整个集群就可以适应任意大小的数据了； （2&…
阅读更多...
数据库分区、分表、分库、分片

数据库分区、分表、分库、分片

一、分区的概念 数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。 分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还…
阅读更多...
格式化U盘为FAT32

格式化U盘为FAT32

使用fdisk格式化U盘 查看硬盘挂载点 df 卸载挂载的硬盘 umount /media/xxx/8CDA-0A87擦除硬盘分区信息 mkfs.vfat /dev/sdb -I创建新的分区 #fdisk打开硬盘/dev/sdb sudo fdisk /dev/sdb #创建新分区 n #选择创建主分区 p #选择分区数目 1 #输入起始扇区，使用默认值…
阅读更多...
MySQL基础篇（day02，复习自用）

MySQL基础篇（day02，复习自用）

MySQL第二天 基本的SELECT语句大小规范注释命名规则数据导入指令select语句练习 运算符代码练习 基本的SELECT语句 SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进 每条命令以 ; 或 \g 或 \G 结束 关键字不能被缩写也不能分行 关…
阅读更多...
PyQt5中文手册

PyQt5中文手册

PyQt5中文手册 一、介绍 本教程的目的是带领你入门PyQt5。教程内所有代码都在Linux上测试通过。PyQt4 教程是PyQt4的教程，PyQt4是一个Python（同时支持2和3）版的Qt库。 关于 PyQt5 PyQt5 是Digia的一套Qt5应用框架与python的结合&#xff…
阅读更多...
20230331英语学习

20230331英语学习

thorn n.刺，荆棘；带刺小灌木 burrow v.挖掘，挖地洞；寻找，探索 differential adj.差别的，有区别的；微分的 affluent adj.丰富的；富裕的 budget n.预算 ancient adj.古代的&#…
阅读更多...
20230402英语学习

20230402英语学习

reasonable adj.合理的；通情达理的；明智的，理智的 abstract adj.抽象的，理论的 reflection n.反射; 映像, 倒影; 反映; 表达, 抒发; (长相等)酷似的人; 惟妙惟肖的事物; 深思; 考虑 instruction n.教授; 教导, 指导; 指示, 命令…
阅读更多...
最新文章

Copyright @ 2022~2023