mysql分区表的增删改查操作

一、mysql创建表分区

详情参考链接：mysql创建表分区详细介绍及示例

二、基本分区信息查询

官方链接 : mysql分区的相关增删改查操作

2.1 查看mysql版本是否支持分区

mysql> show plugins
1

即：看名为partition的插件是否为active，active表示支持分区。

并且同一个数据库，不同表支持分区可以是不同的存储引擎，但是表分区后所有的分区都必须和表使用相同引擎。

MyISAM和InnoDB都支持分区。
MySQL 8都无需插件即可支持分区，且只有InnoDB支持，MyISAM不支持分区。
MySQL 5.7 的NDB支持分区有自己的规则。
MySQL只支持水平分区，对垂直分区的支持无计划。

2.2 查看表是否为分区表

2.2.1 查询表分区信息

1. 查看创建分区表的create语句：show create table 表名；

示例：show create table dev_fac;

2. 查看表是不是分区表：show table status;

示例：show table status;

2.2.2 查看表的所有分区

  查看对应数据库、对应表的所有分区信息

SELECT
	partition_name part,
	partition_expression expr,
	partition_description descr,
	table_rows
FROM
	INFORMATION_SCHEMA. PARTITIONS
WHERE
	TABLE_SCHEMA = "库名称"
AND TABLE_NAME = "表名称";
1
2
3
4
5
6
7
8
9
10

示例：

SELECT
	partition_name part,
	partition_expression expr,
	partition_description descr,
	table_rows
FROM
	INFORMATION_SCHEMA. PARTITIONS
WHERE
	TABLE_SCHEMA = "test"
AND TABLE_NAME = "dev_fac";
1
2
3
4
5
6
7
8
9
10

结果如下：

三、分区表的查询操作

MySQL 5.7支持显式选择分区和子分区，在执行语句时，应检查是否有与给定WHERE条件匹配的行。分区选择与分区精简相似，分区选择只检查特定分区的匹配情况，但在两个关键方面有所不同：

1. 分区选择要检查的分区由语句的发布者指定，而分区精简它是自动的。
2. 尽管分区精简仅适用于查询，但查询和许多DML语句都支持分区的显式选择。
3. 支持显式分区选择的SQL语句如下：

SELECT * FROM 表名  PARTITION (分区名称1,分区名称2,分区名称n) WHERE 查询条件;
1

示例：

4. 隐式分区要注意where条件中需要包含分区的关键字，以确保查询时是通过分区查询，而不是全表扫描，查询语句如下：

SELECT * FROM 表名  WHERE 查询条件;
1

显示扫描哪些分区，及它们是如何使用的：
  在查询语句前面加上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 分区逻辑；
1

示例：

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
);
1
2
3
4
5

4.1.2 新增分区

  新增分区需要先确认表为分区表。

alter table 表名 add partition (partition 分区名称 values less than (逻辑));
1

1. range添加新分区

mysql> alter table user add partition(partition p4 values less than MAXVALUE);
1

2. list添加新分区

mysql> alter table list_part add partition(partition p4 values in (25,26,28));
1

3. hash重新分区

mysql> alter table hash_part add partition partitions 4;
1

4.key重新分区

mysql> alter table key_part add partition partitions 4;
1

4.2 重新分区

1. range重新分区

mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
1

2. list重新分区

mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
1

3. hash和key分区不能用REORGANIZE，官方网站说的很清楚

mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;
1

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 表名称`;
1

4.3.2 删除分区

alter table 表名  drop partition 分区名称；
-- 示例
alter table staff  drop partition p0；
1
2
3

  如果你使用例子给出的分区方案，你只需执行语句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 子句
1
2
3
4

示例：DELETE FROM dev_fac PARTITION(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'
	);
1
2
3
4
5
6
7
8
9
10
11
12
13

4.4.2 按分区插入

  插入语句中指定插入的分区信息。

INSERT INTO 表名  PARTITION  (分区名称1,分区名称2,分区名称n)
列名  VALUES（）
1
2

示例：
INSERT INTO dev_fac PARTITION (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（）,（）,（）,...,()
1
2

示例：

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'
	);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23