mysql分区表的增删改查的实现示例

分区 class partition 分区表 518 来源： 2024-10-16

一、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 查看表的所有分区

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

SELECT
    partition_name part,
    partition_expression expr,
    partition_description descr,
    table_rows
FROM
    INFORMATION_SCHEMA. PARTITIONS
WHERE
    TABLE_SCHEMA = "库名称"
AND TABLE_NAME = "表名称";

示例：

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";

结果如下：

三、分区表的查询操作

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 (null,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 (null,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_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'
    );

4.4.2 按分区插入

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

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

示例：

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（）,（）,（）,...,()

示例：

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'
    );

到此这篇关于mysql分区表的增删改查的实现示例的文章就介绍到这了,更多相关mysql分区表增删改查内容请搜索插件窝以前的文章或继续浏览下面的相关文章希望大家以后多多支持插件窝！

上一篇：mysql字符切割的四种方式汇总

下一篇：mysql创建表分区的实现示例