MySql自动表分区解决方法

2022-10-19 17:44:30

MySql自动表分区解决方法

能解决问题的文章都是不错的输出，谢谢网友撰写的这篇文章，本人转载过来记录下

为提高数据库的查询效率，当数据量大了之后，对表进行分区提高查询效率是必然的，我们可以通过手动对数据库表进行分区，当然为了提高系统稳定性和减少系统维护工作量，使用自动分区是明智之选，以下为我调试了半天才ok的一个关于建立任意数据库的任意表的自动分区的一个存储过程，具体事例如下【传说中的存储过程调试工具dbForge Studio for MySQL真的很垃圾，只能进入存储过程的begin处，F10之后就没法单步调试下一步，直接卡死，最后报错为同步...超时】

1、创建测试数据库

create database test;

2、创建测试表

create table test_log
(
    created datetime,
    msg varchar(2000)
)

3、手动进行分区

如果表没有做过分区表，那可以手动进行分区；当然以下也有存储过程自动创建表分区，如果第一次没有创建表分区，存储过程会自动创建表分区，然后会面累加自动创建多个表分区；

alter table test_log partition by range columns(created)(
    partition p20151001 values less than('2015-10-01 10:10:10'),
    partition p20161001 values less than('2016-10-01 10:10:10'),
    partition p20170210 values less than('2017-02-10 10:10:10')
);

4、插入数据

insert into test_log values('2015-05-01 01:12:10', 'hi');
insert into test_log values('2016-05-01 01:12:10', 'ni');
insert into test_log values('2016-12-01 01:12:10', 'hao');

5、查看数据表的分区

SELECT partition_name, partition_description AS val
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test_log' AND TABLE_SCHEMA = 'test';

其中test_log为表名，test为数据库名

+----------------+-----------------------+
| partition_name | val                   |
+----------------+-----------------------+
| p20151001      | '2015-10-01 10:10:10' |
| p20161001      | '2016-10-01 10:10:10' |
| p20170210      | '2017-02-10 10:10:10' |
+----------------+-----------------------+
3 rows in set (0.00 sec)

4、创建存储过程

DELIMITER ||
-- 注意：使用该存储过程必须保证相应数据库表中至少有一个手动分区
-- 创建存储过程[通过数据库名和对应表名]-建多少个分区，分区时间间隔为多少
-- databasename：创建分区的数据库
-- tablename：创建分区的表的名称
-- partition_number：一次创建多少个分区
-- partitiontype：分区类型[0按天分区，1按月分区，2按年分区]
-- gaps：分区间隔，如果分区类型为0则表示每个分区的间隔为 gaps天；
-- 如果分区类型为1则表示每个分区的间隔为 gaps月
-- 如果分区类型为2则表示每个分区的间隔为 gaps年
create procedure create_table_partition (in databasename varchar(50),in tablename varchar(50), 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 ISNULL(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 ISNULL(max_partition_description) then

set @sqlCreatePartition=CONCAT('alter table ',tablename,' partition by range columns(Time)(PARTITION ',databasename,tablename,DATE_FORMAT(NOW(),'%Y%m%d'),' values less than(\'',DATE_FORMAT(NOW(),'%Y-%m-%d'),'\') );');
PREPARE stmt_initPartition FROM @sqlCreatePartition;
EXECUTE stmt_initPartition;
DEALLOCATE PREPARE stmt_initPartition;
-- 首次创建表分区后重新在查询一遍
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;
set partition_number=partition_number-1;
end if;
-- 替换前后的单引号[''两个引号表示一个单引号的转义]
-- set max_partition_description = REPLACE(max_partition_description, '''', '');
-- 或使用如下语句
set max_partition_description = REPLACE(max_partition_description, '\'', '');

-- 自动创建number个分区
while (i <= partition_number) do

if (partitiontype = 0) then
-- 每个分区按天递增,递增gaps天
set p_description = DATE_ADD(max_partition_description, interval i*gaps day);
elseif (partitiontype = 1) then
-- 每个分区按月递增,递增gaps月
set p_description = DATE_ADD(max_partition_description, interval i*gaps month);
else
-- 每个分区按年递增,递增gaps年
set p_description = DATE_ADD(max_partition_description, interval i*gaps year);
end if;
-- 删除空格
set p_name = REPLACE(p_description, ' ', '');
-- 如果有横杆替换为空
set p_name = REPLACE(p_name, '-', '');
set p_name = LEFT(p_name,8);
-- alter table tablename add partition ( partition pname values less than ('2021-06-07 00:00:00') );
set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION ',databasename,tablename,p_name ,' VALUES LESS THAN (\'', p_description ,'\'))');
-- 准备sql语句
PREPARE stmt from @sql;
-- 执行sql语句
EXECUTE stmt;
-- 释放资源
DEALLOCATE PREPARE stmt;
-- 递增变量
set i = (i + 1) ;

end while;
end ||
-- 恢复语句中断符
DELIMITER ;

上述两天需要特别注意的地方：
（1）需要替换掉其中的单引号【两个单引号表示一个单引号的转义】

set max_partition_description = REPLACE(max_partition_description, '''', '');

或使用：

 set max_partition_description = REPLACE(max_partition_description, '\'', '');

（2）自动添加新分区必须的前提是该表有对应手动分区

5、手动调用存储过程

call create_partition_by_number('test', 'test_log', 5, 1, 1);

表示为test数据库的test_log表创建5个分区，分区类型为年，每个分区间隔为1年.
6、使用mysql的事件实现定时分区

-- 开启mysql的事件例程
set global event_scheduler=1;
-- 查看事件是否开启
show variables like '%event_scheduler';
-- 新建一个事件

mysql默认是不会开启事件例程的，需要手动打开，查看是否打开：

mysql> show variables like '%event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

7、启用定时器调用创建过程

DELIMITER $$
drop event if exists auto_create_partition_time  $$
create event auto_create_partition_time on schedule every 1 minute
starts sysdate()
do
BEGIN
    call create_partition_by_number('test', 'test_log', 5, 1, 1);
END$$
delimiter ;

每分钟查看分区情况：

8、合并分区

ALTER TABLE test_log reorganize partition 
REORGANIZE PARTITION 
p20151001,p20161001,p20170210 INTO
(PARTITION p20150101 VALUES LESS THAN ('2015-01-01 00:00:00'),
PARTITION p20160101 VALUES LESS THAN ('2016-01-01 00:00:00'),
PARTITION p20170101 VALUES LESS THAN ('2017-01-01 00:00:00'),
);

9、删除表的所有分区

alter table test_log remove partitioning;

10、删除分区存储过程创建

/* ------------分区删除存储过程的创建 ----------------*/
-- 替换语句中断符
DELIMITER ||
-- 删除存储过程
drop procedure if exists remove_partition ||
 
-- 删除分区
-- databasename：创建分区的数据库
-- tablename：创建分区的表的名称
-- dateline: 时间点，早于当前点的分区将会被删除[分区删除同时会删除数据]
-- recordnumber：删除时间点之前的几个分区
create procedure remove_partition(in databasename varchar(50),in tablename varchar(50), in dateline varchar(50), in recordnumber int)
outer_label:
begin     
    declare max_partition_description varchar(255) default 0;
    declare p_description varchar(255) default 0;
    declare p_name varchar(255) default 0; 
    declare i int default 1;
     
    while i <= recordnumber do
        -- 获取最大[降序获取]的分区描述[值]
        select partition_description into max_partition_description from information_schema.partitions where table_schema = databasename and table_name = tablename
                                    and STRCMP(REPLACE(partition_description, '''', ''), dateline) < 0  order by partition_description asc limit 1;
        
        if max_partition_description <=> "" then
            leave outer_label; 
        end if;
        
        set max_partition_description = REPLACE(max_partition_description, '''', '');
        set p_description = max_partition_description;
        -- 删除空格
        set p_name = REPLACE(p_description, ' ', '');
        -- 如果有横杆替换为空
        set p_name = REPLACE(p_name, '-', '');
        -- 删除时间冒号
        set p_name = REPLACE(p_name, ':', '');
        -- ALTER TABLE sale_data DROP PARTITION p201010;
      set @sql=CONCAT('ALTER TABLE ', tablename ,' DROP PARTITION p', p_name);
        -- 打印sql变量
        -- select @sql;
        -- 准备sql语句
        PREPARE stmt from @sql;
        -- 执行sql语句
        EXECUTE stmt;
        -- 释放资源
        DEALLOCATE PREPARE stmt;
        -- 递增变量
        set i = (i + 1) ;
    end while;            
end ||
-- 恢复语句中断符
DELIMITER ;

其他语法和实用功能：

alter table test_log add partition (partition p20170220 values less than('2017-02-20'));
alter table test_log drop partition p20170220;

SELECT partition_name,cast(replace(partition_description, ' ', '') AS date) AS val FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test' ;
-- 开启mysql的事件例程
set global event_scheduler=1;
-- 查看事件是否开启
show variables like '%event_scheduler';

DELIMITER $$
drop event if exists auto_create_partition_time  $$
-- EVERY 1 day STARTS '2016-05-27 23:59:59'
create event auto_create_partition_time  on schedule every 1 minute
-- 定时器完成后是否继续定时 second秒 minute分 hour day month year
on completion preserve enable
starts sysdate()
do
BEGIN
    call proc_test_log_pt();
END$$
delimiter ;

-- 删除定时器

drop event if exists test_event;

-- 开启事件

alter event test_event on completion preserve enable;

-- 关闭事件

alter event test_event on completion preserve disable;

-- 查看我的event

select * from mysql.event;

C# 执行Mysql 脚本 要引用：using MySql.Data.MySqlClient;

public static void ExecuteMysqlScript(string connectionstring, string sqlScript)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionstring))
            {
                connection.Open();                
                MySqlScript script = new MySqlScript(connection);

                script.Query = sqlScript;
                script.Delimiter = "||";
                int count = script.Execute();
                script.Delimiter = ";";
                connection.Close();
            }
        }

码农公寓

MySql自动表分区解决方法

相关文章