mysql分区表的增删改查操作
mysql分区表的增删改查操作
- 一、mysql创建表分区
- 二、基本分区信息查询
- 2.1 查看mysql版本是否支持分区
- 2.2 查看表是否为分区表
- 2.2.1 查询表分区信息
- 2.2.2 查看表的所有分区
- 三、分区表的查询操作
- 四、分区表的增删改操作
- 4.1 新增分区
- 4.1.1 给已有的表加上分区
- 4.1.2 新增分区
- 4.2 重新分区
- 4.3 删除
- 4.3.1 删除表
- 4.3.2 删除分区
- 4.3.4 删除指定分区中的数据
- 4.4 数据插入
- 4.4.1 按表插入
- 4.4.2 按分区插入
- 4.4.2 按分区批量插入
一、mysql创建表分区
详情参考链接：mysql创建表分区详细介绍及示例
二、基本分区信息查询
官方链接 : mysql分区的相关增删改查操作
2.1 查看mysql版本是否支持分区
mysql> show plugins
即：看名为partition的插件是否为active，active表示支持分区。
并且同一个数据库，不同表支持分区可以是不同的存储引擎，但是表分区后所有的分区都必须和表使用相同引擎。
MyISAM和InnoDB都支持分区。
MySQL 8都无需插件即可支持分区，且只有InnoDB支持，MyISAM不支持分区。
MySQL 5.7 的NDB支持分区有自己的规则。
MySQL只支持水平分区，对垂直分区的支持无计划。
2.2 查看表是否为分区表
2.2.1 查询表分区信息
- 查看创建分区表的create语句：
show create table 表名；
示例：show create table dev_fac;
- 查看表是不是分区表：show table status;
示例：show table status;
2.2.2 查看表的所有分区
查看对应数据库、对应表的所有分区信息
SELECTpartition_name part,partition_expression expr,partition_description descr,table_rows
FROMINFORMATION_SCHEMA. PARTITIONS
WHERETABLE_SCHEMA = "库名称"
AND TABLE_NAME = "表名称";
示例：
SELECTpartition_name part,partition_expression expr,partition_description descr,table_rows
FROMINFORMATION_SCHEMA. PARTITIONS
WHERETABLE_SCHEMA = "test"
AND TABLE_NAME = "dev_fac";
结果如下：
三、分区表的查询操作
MySQL 5.7支持显式选择分区和子分区，在执行语句时，应检查是否有与给定WHERE条件匹配的行。分区选择与分区精简相似，分区选择只检查特定分区的匹配情况，但在两个关键方面有所不同：
- 分区选择要检查的分区由语句的发布者指定，而分区精简它是自动的。
- 尽管分区精简仅适用于查询，但查询和许多DML语句都支持分区的显式选择。
- 支持显式分区选择的SQL语句如下：
SELECT * FROM 表名 PARTITION (分区名称1,分区名称2,分区名称n) WHERE 查询条件;
示例：
- 隐式分区要注意where条件中需要包含分区的关键字，以确保查询时是通过分区查询，而不是全表扫描，查询语句如下：
SELECT * FROM 表名 WHERE 查询条件;
显示扫描哪些分区，及它们是如何使用的：
在查询语句前面加上EXPLAIN PARTITIONS 关键字.
示例： EXPLAIN PARTITIONS SELECT * FROM dev_date WHERE Partition_Date = ‘2022-11-25 16:07:00’;
四、分区表的增删改操作
4.1 新增分区
4.1.1 给已有的表加上分区
alter table 表名 partition by 分区逻辑；
示例：
alter table results partition by RANGE (month(ttime)) (
PARTITION p5 VALUES LESS THAN (6) ,
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN MAXVALUE
);
4.1.2 新增分区
新增分区需要先确认表为分区表。
alter table 表名 add partition (partition 分区名称 values less than (逻辑));
1. range添加新分区
mysql> alter table user add partition(partition p4 values less than MAXVALUE);
2. list添加新分区
mysql> alter table list_part add partition(partition p4 values in (25,26,28));
3. hash重新分区
mysql> alter table hash_part add partition partitions 4;
4.key重新分区
mysql> alter table key_part add partition partitions 4;
4.2 重新分区
1. range重新分区
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
2. list重新分区
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
3. hash和key分区不能用REORGANIZE，官方网站说的很清楚
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PARTITION 9’ at line 1
4.3 删除
4.3.1 删除表
表删除，对应的分区及数据也会删除。
DROP TABLE 表名称`;
4.3.2 删除分区
alter table 表名 drop partition 分区名称；
-- 示例
alter table staff drop partition p0；
如果你使用例子给出的分区方案，你只需执行语句alter table staff drop partition p0来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如”delete from staff WHERE year(separated) <= 1990;”这样的一个DELETE查询要有效得多。
4.3.4 删除指定分区中的数据
DELETE
FROM表名 PARTITION (分区名称1,分区名称2,分区名称n)
WHERE 子句
示例：DELETE FROM
dev_facPARTITION(p1000000000000001) WHERE devName = ‘D10000000000000011名称’
4.4 数据插入
4.4.1 按表插入
直接按表插入，数据库自动根据数据查找分区插入。
INSERT INTO `dev_fac`
VALUES('D10000000000000010','D10000000000000010名称','F1000000000000001','2022-11-25 16:07:00','1','1669363620000','1','1000000000000001','2022-11-25 16:07:00');
4.4.2 按分区插入
插入语句中指定插入的分区信息。
INSERT INTO 表名 PARTITION (分区名称1,分区名称2,分区名称n)
列名 VALUES（）
示例：
INSERT INTOdev_facPARTITION (1000000000000001)
VALUES(
‘D10000000000000012’,
‘D10000000000000012名称’,
‘F1000000000000001’,
‘2022-11-25 16:07:00’,
‘1’,
‘1669363620000’,
‘1’,
‘1000000000000001’,
‘2022-11-25 16:07:00’
);
4.4.2 按分区批量插入
插入语句中指定插入的分区信息且一次插入多条数据。
INSERT INTO 表名 PARTITION (分区名称1,分区名称2,分区名称n)
列名 VALUES（）,（）,（）,...,()
示例：
INSERT INTO `dev_fac` PARTITION (p1000000000000001)
VALUES('D10000000000000012','D10000000000000012名称','F1000000000000001','2022-11-25 16:07:00','1','1669363620000','1','1000000000000001','2022-11-25 17:07:00'),('D10000000000000013','D10000000000000013名称','F1000000000000001','2022-11-25 16:07:00','1','1669363620000','1','1000000000000001','2022-11-25 17:07:00');
