文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

3.4 分区存储

发布于 2024-10-03 00:33:42 字数 9008 浏览 0 评论 0 收藏 0

MySQL 分区类型

RANGE: 基于属于一个给定连续区间的列值，把多行分配给分区。datetime 类型字段要用 TO_DAYS 转换，timestamp 类型字段要用 UNIX_TIMESTAMP 转换。
RANGE COLUMNS: 类型 RANGE，但可作用到列
HASH: 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
KEY：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区：基于 RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

分区常用操作

创建表时分区： id 分区

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`) USING BTREE,
  INDEX `etc_cg_document_i4`(`state`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact PARTITION BY RANGE (`id`)
PARTITIONS 2
(
  PARTITION `p1` VALUES LESS THAN (10000) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
  PARTITION `p2` VALUES LESS THAN (20000) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0
);

增加分区

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

时间分区存储

对于 datetime 类型的字段（下面示例字段是 hiredata ），Range 和 Range Columns 能达到分区效果，HASH 则无分区效果。最好将用于分区的字段与主键联合作为复合主键。

常见错误：

1、 [Err] 1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

# 将原主键 id 改为 联合主键(id,create_time)
alter table anchor2 modify id int;      #取消自增
alter table anchor2 drop PRIMARY KEY;　 #删除主键
alter table anchor2 add PRIMARY KEY(id,room_utime);  #添加复合主键
alter table anchor2 modify  id int AUTO_INCREMENT; #id 改为自增

RANGE 示例 ：用 TO_DAYS 函数，分区对象只能是整数

CREATE TABLE range_datetime(
  id INT,
  hiredate DATETIME
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (   #分区字段
  PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
  PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
  PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);

RANGE COLUMNS 示例 ：基于列

CREATE TABLE range_datetime(
  id INT,
  hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) ) (
  PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
  PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
  PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);

验证

mysql> insert into range_columns select * from test;
Query OK, 1000000 rows affected (9.20 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230';
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_columns | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400210 | Using where |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.11 sec)

mysql 重建表分区并保留数据的方法：

创建与原始表一样结构的新表，新分区。
将原始表中数据复制到新表。 insert into log2 select * from log;
删除原始表。
将新表名称改为原始表名称。

每天自动增加分区

以下语句都可以在 SQL 编辑框里执行。

# 1.开启事件调度器（默认关闭）
SET GLOBAL event_scheduler = ON;
# 2.对已有数据先进行分区
ALTER TABLE position PARTITION BY RANGE(TO_DAYS(date))
(
  PARTITION p20181028 VALUES LESS THAN (TO_DAYS('2018-10-29')),
  PARTITION p20181029 VALUES LESS THAN (TO_DAYS('2018-10-30')),
  PARTITION p20181030 VALUES LESS THAN (TO_DAYS('2018-10-31'))
)

3、分区脚本

use test;

DELIMITER ||
-- 删除存储过程
drop procedure if exists auto_set_partitions ||
-- 注意：使用该存储过程必须保证相应数据库表中至少有一个手动分区
-- 创建存储过程[通过数据库名和对应表名]-建多少个分区，分区时间间隔为多少
-- databasename：创建分区的数据库
-- tablename：创建分区的表的名称
-- partition_number：一次创建多少个分区
-- partitiontype：分区类型[0 按天分区，1 按月分区，2 按年分区]
-- gaps：分区间隔，如果分区类型为 0 则表示每个分区的间隔为 gaps 天；
--       如果分区类型为 1 则表示每个分区的间隔为 gaps 月
--            如果分区类型为 2 则表示每个分区的间隔为 gaps 年
create procedure auto_set_partitions (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
L_END:
begin
    declare max_partition_description varchar(255) default '';
    declare p_name varchar(255) default 0;
    declare p_description varchar(255) default 0;
    declare isexist_partition varchar(255) default 0;
 declare i int default 1;

 -- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
    select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
    -- 如果不存在则打印错误并退出存储过程
    if isexist_partition <=> "" then
       select "partition table not is exist" as "ERROR";
       leave L_END;
    end if;

    -- 获取最大[降序获取]的分区描述[值]
    select partition_description into max_partition_description  from information_schema.partitions where table_schema = databasename  and table_name = tablename order by partition_description desc limit 1;

    -- 如果最大分区没有，说明没有手动分区，则无法创建自动分区
    if max_partition_description <=> "" then
       select "partition table is error" as "ERROR";
       leave L_END;
    end if;

    -- 替换前后的单引号[''两个引号表示一个单引号的转义]
    -- set max_partition_description = REPLACE(max_partition_description, '''', '');
     -- 或使用如下语句
     set max_partition_description = REPLACE(max_partition_description-1, '\'', '');

   -- 自动创建 number 个分区
    while (i <= partition_number) do
        if (partitiontype = 0) then
          -- 每个分区按天递增，递增 gaps 天
          set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
        elseif (partitiontype = 1) then
          -- 每个分区按月递增，递增 gaps 月
          set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
        else
          -- 每个分区按年递增，递增 gaps 年
          set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
        end if;
        -- 删除空格
        set p_name = REPLACE(p_description, ' ', '');
        -- 例如 10.20 的记录实际是 less than 10.21
        set p_description = DATE_ADD(p_description, interval 1 day);
        -- 如果有横杆替换为空
      set p_name = REPLACE(p_name, '-', '');
        -- 删除时间冒号
        set p_name = REPLACE(p_name, ':', '');
        -- alter table tablename add partition ( partition pname values less than ('2017-02-20 10:05:56') );
      set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
        -- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
        -- 打印 sql 变量
      -- select @sql;
        -- 准备 sql 语句
      PREPARE stmt from @sql;
        -- 执行 sql 语句
      EXECUTE stmt;
        -- 释放资源
      DEALLOCATE PREPARE stmt;
        -- 递增变量
      set i = (i + 1) ;
    end while;
end ||
-- 恢复语句中断符
DELIMITER ;

4、添加事件处理，每天执行一次（下面示例数据库名 test，表名 position）

DELIMITER ||
drop event if exists auto_set_partitions  ||
create event auto_set_partitions
on schedule every 1 day
starts '2018-10-30 23:59:59'
do
BEGIN
  call auto_set_partitions('test', 'position', 1, 0, 1);
END ||
DELIMITER ;

修改事件

ALTER EVENT
event_name

ON SCHEDULE schedule
[RENAME TO new_event_name][ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE][COMMENT 'comment']
DO sql_statement

删除事件

DROP EVENT [IF EXISTS] auto_set_partitions;

查看事件是否开启

show variables like 'event_scheduler';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

列表为空，暂无数据