数据库分区-mysql

在 MySQL 中，分区是一种将大表分割成更小、更易管理的部分的方法。按时间分区是一种常见的分区方式，特别适用于数据量大且按时间增长的数据表。通过按时间分区，可以提高查询性能和数据管理效率。

创建按时间分区的表

以下是一个按时间分区的示例代码，假设我们有一个名为 test 的表：

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',

`description` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,

`state` tinyint(4) NULL DEFAULT 0 COMMENT '0:未处理，1：处理中，2：处理完成，3：异常订单',

`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

PRIMARY KEY (`id`, `create_time`)

) ENGINE=InnoDB AUTO_INCREMENT=1 **DEFAULT** CHARSET=utf8 **COLLATE**=utf8_bin;

ALTER TABLE `test` PARTITION BY RANGE COLUMNS(`create_time`) (

PARTITION p1 VALUES LESS THAN ('2019-01-01'),

PARTITION p2 VALUES LESS THAN ('2020-01-01'),

PARTITION p3 VALUES LESS THAN ('2021-01-01'),

PARTITION p4 VALUES LESS THAN ('2022-01-01'),

PARTITION p5 VALUES LESS THAN ('2023-01-01'),

PARTITION p6 VALUES LESS THAN ('2024-01-01'),

PARTITION p7 VALUES LESS THAN ('2025-01-01'),

PARTITION p8 VALUES LESS THAN ('2026-01-01'),

PARTITION p9 VALUES LESS THAN ('2027-01-01')

);

在这个示例中，我们创建了一个名为 test 的表，并按 create_time 字段进行按年分区​^1^​。

分区类型

MySQL 支持多种分区类型，包括：

• ​RANGE 分区​：基于属于一个给定连续区间的列值，把多行分配给分区。
• ​LIST 分区​：类似于 RANGE 分区，但基于列值匹配一个离散值集合中的某个值来进行选择。
• ​HASH 分区​：基于用户定义的表达式的返回值来进行选择的分区。
• ​KEY 分区​：类似于 HASH 分区，但 MySQL 服务器提供其自身的哈希函数​^2^​。

常用命令

• ​创建表时分区​： *CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `description` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `state` tinyint(4) NULL DEFAULT 0 COMMENT '0:未处理，1：处理中，2：处理完成，3：异常订单', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin PARTITION BY RANGE (`id`) ( PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000) );*
• ​增加分区​： ALTER TABLE `test` PARTITION BY RANGE(`id`) ( PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000) );
• ​删除指定分区​： ALTER TABLE `test` DROP PARTITION p1;
• ​删除所有分区​： ALTER TABLE `test` REMOVE PARTITIONING;
• ​查看分区信息​： SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'test';

通过这些命令，可以方便地管理和操作 MySQL 表的分区。需要注意的是，删除分区时，指定分区的数据也会同步删除，因此操作时需谨慎