MySQL 数据库自动分区

MySQL 数据库自动分区

MySQL自动分区（Automatic Partitioning）是指数据库管理体系自动将数据分布到预先定义好的分区表布局中，而无需显式地对每个插入操纵举行分区。它通常基于特定的计谋，如范围、列表、哈希或螺旋分区，可以提高查询性能、管理大规模数据以及优化存储资源。
几种常见的自动分区范例包罗：
RANGE PARTITIONING：按照某个字段值的范围划分，例如按日期范围创建每月的数据分区。
LIST PARTITIONING：根据指定的一系列值来分割，比如按月份创建12个月份的分区。
HASH PARTITIONING：通过哈希函数确定命据应放入哪个分区，常用于匀称分散数据。
KEY PARTITIONING：利用表的主键或其他唯一索引举行分区，包管每个分区都是唯一的。
设置自动分区时，必要预先规划好分区计谋，并在创建表的时候启用分区功能。如许，在后续的数据插入过程中，体系会根据设定的规则自动分配数据到相应的分区中。
自动分区脚本：

1. DELIMITER ||
2. -- 删除存储过程
3. drop procedure if exists auto_set_partitions ||
4. -- 必须保证相应数据库表中至少有一个手动分区
5. -- 参数databasename：    创建分区的数据库
6. -- 参数tablename：       创建分区的表的名称
7. -- 参数partition_number：一次创建多少个分区
8. -- 参数partitiontype：   分区类型[0按天分区，1按月分区，2按年分区]
9. -- 参数gaps：            分区间隔，如果分区类型为0则表示每个分区的间隔为 gaps天；
11. --  如果分区类型为1则表示每个分区的间隔为 gaps月
13. -- 如果分区类型为2则表示每个分区的间隔为 gaps年
14. 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)
15. L_END:
16. begin   
17.     declare max_partition_description varchar(255) default '';
18.     declare p_name varchar(255) default 0;      
19.     declare p_description varchar(255) default 0;  
20.     declare isexist_partition varchar(255) default 0;
21.     declare i int default 1;
22. -- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
23.     select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
24.     -- 如果不存在则打印错误并退出存储过程
25.     if isexist_partition <=> "" then
26.        select "partition table not is exist" as "ERROR";
27.        leave L_END;
28.     end if;
30.     -- 获取最大[降序获取]的分区描述[值]
31.     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;
32.     -- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
33.     if max_partition_description <=> "" then
34.        select "partition table is error" as "ERROR";
35.        leave L_END;
36.     end if;
37.    
38.     -- 替换前后的单引号[''两个引号表示一个单引号的转义]
39.     -- set max_partition_description = REPLACE(max_partition_description, '''', '');
40.      -- 或使用如下语句
41.      set max_partition_description = REPLACE(max_partition_description-1, '\'', '');
42.    -- 自动创建number个分区
43.     while (i <= partition_number) do
44.                  if (partitiontype = 0) then
45.                      -- 每个分区按天递增,递增gaps天
46.                      set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
47.                  elseif (partitiontype = 1) then
48.                      -- 每个分区按月递增,递增gaps月
49.                      set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
50.                  else
51.                      -- 每个分区按年递增,递增gaps年
52.                      set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
53.                  end if;
54.                  -- 删除空格
55.                  set p_name = REPLACE(p_description, ' ', '');
56.                  set p_description = DATE_ADD(p_description, interval 1 day)
57.                  -- 如果有横杆替换为空
58.           set p_name = REPLACE(p_name, '-', '');
59.                  -- 删除时间冒号
60.                  set p_name = REPLACE(p_name, ':', '');
61.                  -- alter table tablename add partition ( partition pname values less than ('2017-02-20 10:05:56') );
62.           set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
63.           -- select @sql;
64.           PREPARE stmt from @sql;
65.           EXECUTE stmt;
66.           DEALLOCATE PREPARE stmt;
67.                  -- 递增变量
68.           set i = (i + 1) ;
69.     end while;         
70. end ||
71. -- 恢复语句中断符
72. DELIMITER ;

复制代码

在现实利用中，按需修改此脚本。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。