mysql分区表设计（二）-白红宇

mysql分区表设计（二）

阅读量：6946 次

发布时间：2019-06-27

本文共 21223 字，大约阅读时间需要 70 分钟。

数据库中有三种类型的表，分钟粒度、小时粒度、天粒度，现在需要为三种类型的表涉及分区自动维护的存储过程，首先创建元数据信息表：

mysql> select * from op_tb_partition;+-------------------------------------+| tb_name                             |+-------------------------------------+| NL_BRS_AJAX_ERRORS_HOST_MIN         || NL_BRS_AJAX_ERRORS_URL_MIN          || NL_BRS_AJAX_HOST_MIN                || NL_BRS_AJAX_OVERVIEW_MIN            || NL_BRS_AJAX_PAGE_MIN                || NL_BRS_AJAX_URL_MIN                 || NL_BRS_APPLICATION_OVERVIEW_BIN_MIN || NL_BRS_APPLICATION_OVERVIEW_MIN     || NL_BRS_COMBO_MIN                    || NL_BRS_GEO_MIN                      || NL_BRS_JS_ERRORS_BROWSER_MIN        || NL_BRS_JS_ERRORS_MIN                || NL_BRS_JS_ERRORS_PAGE_MIN           || NL_BRS_PAGE_HOST_MIN                || NL_BRS_PAGE_URL_BIN_MIN             || NL_BRS_PAGE_URL_MIN                 || NL_BRS_WEB_BROWSER_MIN              || NL_BRS_WEB_BROWSER_VERSION_MIN      |+-------------------------------------+18 rows in set (0.00 sec)    mysql> select * from op_tb_partition_hour;+--------------------------------------+| tb_name                              |+--------------------------------------+| NL_BRS_AJAX_ERRORS_HOST_HOUR         || NL_BRS_AJAX_ERRORS_URL_HOUR          || NL_BRS_AJAX_HOST_HOUR                || NL_BRS_AJAX_OVERVIEW_HOUR            || NL_BRS_AJAX_PAGE_HOUR                || NL_BRS_AJAX_URL_HOUR                 || NL_BRS_APPLICATION_OVERVIEW_BIN_HOUR || NL_BRS_APPLICATION_OVERVIEW_HOUR     || NL_BRS_COMBO_HOUR                    || NL_BRS_GEO_HOUR                      || NL_BRS_JS_ERRORS_BROWSER_HOUR        || NL_BRS_JS_ERRORS_HOUR                || NL_BRS_JS_ERRORS_PAGE_HOUR           || NL_BRS_PAGE_HOST_HOUR                || NL_BRS_PAGE_URL_BIN_HOUR             || NL_BRS_PAGE_URL_HOUR                 || NL_BRS_WEB_BROWSER_HOUR              || NL_BRS_WEB_BROWSER_VERSION_HOUR      |+--------------------------------------+18 rows in set (0.06 sec)mysql> select * from op_tb_partition_day;+-------------------------------------+| tb_name                             |+-------------------------------------+| NL_BRS_AJAX_ERRORS_HOST_DAY         || NL_BRS_AJAX_ERRORS_URL_DAY          || NL_BRS_AJAX_HOST_DAY                || NL_BRS_AJAX_OVERVIEW_DAY            || NL_BRS_AJAX_PAGE_DAY                || NL_BRS_AJAX_URL_DAY                 || NL_BRS_APPLICATION_OVERVIEW_BIN_DAY || NL_BRS_APPLICATION_OVERVIEW_DAY     || NL_BRS_COMBO_DAY                    || NL_BRS_GEO_DAY                      || NL_BRS_JS_ERRORS_BROWSER_DAY        || NL_BRS_JS_ERRORS_DAY                || NL_BRS_JS_ERRORS_PAGE_DAY           || NL_BRS_PAGE_HOST_DAY                || NL_BRS_PAGE_URL_BIN_DAY             || NL_BRS_PAGE_URL_DAY                 || NL_BRS_WEB_BROWSER_DAY              || NL_BRS_WEB_BROWSER_VERSION_DAY      |+-------------------------------------+18 rows in set (0.00 sec)

创建日志表，用于记录存储过程执行的详细信息，如下：

mysql> desc  change_partition_log;+------------+---------------+------+-----+-------------------+-----------------------------+| Field      | Type          | Null | Key | Default           | Extra                       |+------------+---------------+------+-----+-------------------+-----------------------------+| tm_base    | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || op_type    | varchar(20)   | YES  |     | NULL              |                             || change_sql | varchar(2000) | YES  |     | NULL              |                             |+------------+---------------+------+-----+-------------------+-----------------------------+3 rows in set (0.02 sec)

编写分钟表的存储过程，包括添加分区和删除分区两个存储过程及调度event，如下：

-- 添加分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`add_partition_min`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare max_day_now varchar(255) default "";        declare max_day_will varchar(255) default "";        declare difference int default 0;        declare i int default 1;        declare tmp_day varchar(255) default "";        declare tmp_day_value varchar(255) default "";        declare par_name varchar(255) default "";        declare alter_sql varchar(255) default "";        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition;        declare continue handler for not found set done = 1;        set @tmp_sql = concat("select date_format(date_add(now(),interval 6 day), '%Y%m%d') into @max_day_will");        prepare stmt from @tmp_sql;        execute stmt;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_day_now");                prepare stmt from @tmp_sql;                execute stmt;                set @tmp_sql = concat("select to_days('",@max_day_will,"') - to_days('",@max_day_now,"') into @difference");                prepare stmt from @tmp_sql;                execute stmt;                while i <= @difference do                        set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select nl_to_timestamp(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @par_name = concat("par_",@tmp_day);                        set @alter_sql = concat("alter table ",table_name," add partition(partition ",@par_name," values less than (",@tmp_day_value,"))");                        insert into change_partition_log values( now(),"add_min", @alter_sql);                        prepare stmt from @alter_sql;                        execute stmt;                        set i = i+1;                end while;                set i = 1;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                -- 删除分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`del_partition_min`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare max_par_now varchar(255) default "";        declare par_del_num varchar(255) default "";        declare par_del varchar(255) default "";        declare alter_sql varchar(255) default "";        declare i int default 0;        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition;        declare continue handler for not found set done = 1;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("select count(*) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_par_now");                prepare stmt from @tmp_sql;                execute stmt;                set @par_del_num = @max_par_now-31-7;                while i < @par_del_num do                        set @tmp_sql = concat("select partition_name from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA ='lens_browser_data' and table_name='",table_name,"' order by partition_name limit 1 into @par_del");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @alter_sql = concat("alter table ",table_name," drop partition ",@par_del);                        prepare stmt from @alter_sql;                        execute stmt;                        insert into change_partition_log values(now(),"drop_min", concat(i,"--",@par_del,"--",@alter_sql));                        set i = i+1;                end while;                set i = 0;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                        -- 调度EventDELIMITER ;;CREATE EVENT `manager_partition_min` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-07 00:30:00' ON COMPLETION PRESERVE ENABLE DO BEGINcall add_partition_min;call del_partition_min;END ;;DELIMITER ;

编写小时表的存储过程，包括添加分区和删除分区两个存储过程及调度event，如下：

-- 添加分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`add_partition_hour`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare diff int default 0;        declare diffenence int default 0;        declare last_weekend varchar(255) default "";        declare max_weekend_now varchar(255) default "";        declare max_weekend_will varchar(255) default "";        declare tmp_weekend varchar(255) default "";        declare tmp_weekend_value varchar(255) default "";        declare tmp_weekend_before varchar(255) default "";        declare tmp_year varchar(255) default "";        declare tmp_week varchar(255) default "";        declare difference int default 0;        declare i int default 1;        declare alter_sql varchar(255) default "";        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition_hour;        declare continue handler for not found set done = 1;        set @tmp_sql = concat("select  dayofweek(now())-1 into @diff");        prepare stmt from @tmp_sql;        execute stmt;        set @tmp_sql = concat("select date_format(date_add(now(),interval -",@diff," day), '%Y%m%d') into @last_weekend");        prepare stmt from @tmp_sql;        execute stmt;        set @tmp_sql = concat("select date_format(date_add('",@last_weekend,"' ,interval 7*3 day), '%Y%m%d') into @max_weekend_will");        prepare stmt from @tmp_sql;        execute stmt;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("select nl_to_date(max(PARTITION_DESCRIPTION+0)) from INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_weekend_now");                prepare stmt from @tmp_sql;                execute stmt;                set @tmp_sql = concat("select (to_days('",@max_weekend_will,"') - to_days('",@max_weekend_now,"')) div 7 into @difference");                prepare stmt from @tmp_sql;                execute stmt;                while i <= @difference do                        set @tmp_sql = concat("select date_format(date_add('",@max_weekend_now,"' ,interval 7*",i," day), '%Y%m%d') into @tmp_weekend");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select nl_to_timestamp('",@tmp_weekend,"')  into @tmp_weekend_value");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select date_format(date_add('",@tmp_weekend,"' ,interval -1 day), '%Y%m%d') into @tmp_weekend_before");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select week('",@tmp_weekend_before,"',4) into @tmp_week");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select year('",@tmp_weekend_before,"') into @tmp_year");                        prepare stmt from @tmp_sql;                        execute stmt;                        if @tmp_week <10 then                                set @par_name = concat("par_",@tmp_year,"w0",@tmp_week);                         else                                set @par_name = concat("par_",@tmp_year,"w",@tmp_week);                        end if;                        set @alter_sql = concat("alter table ",table_name," add partition(partition ",@par_name," values less than (",@tmp_weekend_value,"))");                        insert into change_partition_log values( now(), "add_hour", @alter_sql);                        prepare stmt from @alter_sql;                        execute stmt;                        set i = i+1;                end while;                set i = 1;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                                   -- 删除分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`del_partition_hour`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare max_par_now varchar(255) default "";        declare par_del_num varchar(255) default "";        declare par_del varchar(255) default "";        declare alter_sql varchar(255) default "";        declare i int default 0;        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition_hour;        declare continue handler for not found set done = 1;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("select count(*) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_par_now");                prepare stmt from @tmp_sql;                execute stmt;                set @par_del_num = @max_par_now-13-3;                while i < @par_del_num do                        set @tmp_sql = concat("select partition_name from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA ='lens_browser_data' and table_name='",table_name,"' order by partition_name limit 1 into @par_del");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @alter_sql = concat("alter table ",table_name," drop partition ",@par_del);                        prepare stmt from @alter_sql;                        execute stmt;                        insert into change_partition_log  values(now(),"drop_hour", concat(i,"--",@par_del,"--",@alter_sql));                        set i = i+1;                end while;                set i = 0;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                        -- 调度EventDELIMITER ;;CREATE EVENT `manager_partition_hour` ON SCHEDULE EVERY 1 WEEK STARTS '2015-09-06 00:30:00' ON COMPLETION PRESERVE ENABLE DO BEGINcall add_partition_hour;call del_partition_hour;END ;;DELIMITER ;

编写天表的存储过程，包括添加分区和删除分区两个存储过程及调度event，如下：

-- 添加分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`add_partition_day`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare diff int default 0;        declare diffenence int default 0;        declare now_month varchar(255) default "";        declare now_year varchar(255) default "";        declare max_day_will varchar(255) default "";        declare max_day_now varchar(255) default "";        declare tmp_month varchar(255) default "";        declare tmp_month_before varchar(255) default "";        declare tmp_month_value varchar(255) default "";        declare tmp_year varchar(255) default "";        declare tmp_quarter varchar(255) default "";        declare difference int default 0;        declare i int default 1;        declare alter_sql varchar(255) default "";        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition_day;        declare continue handler for not found set done = 1;        set @tmp_sql = concat("select  month(now()) into @now_month");        prepare stmt from @tmp_sql;        execute stmt;        set @tmp_sql = concat("select  year(now()) into @now_year");        prepare stmt from @tmp_sql;        execute stmt;        if @now_month >= 1 and @now_month < 4 then                set @tmp_day=concat(@now_year,'0101');        elseif @now_month >=4 and @now_month < 7 then                set @tmp_day=concat(@now_year,'0401');        elseif @now_month >=7 and @now_month < 10 then                set @tmp_day=concat(@now_year,'0701');        elseif @now_month >=10 and @now_month <=12 then                set @tmp_day=concat(@now_year,'1001');        end if;        set @tmp_sql = concat("select date_format(date_add(",@tmp_day,",interval 9 month), '%Y%m%d') into @max_day_will");        prepare stmt from @tmp_sql;        execute stmt;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("select nl_to_date(max(PARTITION_DESCRIPTION+0)) from INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_day_now");                prepare stmt from @tmp_sql;                execute stmt;                set @tmp_sql = concat("select (year('",@max_day_will,"')*12 + month('",@max_day_will,"') - year('",@max_day_now,"')*12 - month('",@max_day_now,"')) div 3 into @difference");                prepare stmt from @tmp_sql;                execute stmt;                while i <= @difference do                        set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval 3*",i," month), '%Y%m%d') into @tmp_month");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select nl_to_timestamp('",@tmp_month,"')  into @tmp_month_value");                        prepare stmt from @tmp_sql;                        execute stmt;                                set @tmp_sql = concat("select date_format(date_add('",@tmp_month,"' ,interval -1 day), '%Y%m%d') into @tmp_month_before");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select quarter('",@tmp_month_before,"') into @tmp_quarter");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @tmp_sql = concat("select year('",@tmp_month_before,"') into @tmp_year");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @par_name = concat("par_",@tmp_year,"q",@tmp_quarter);                        set @alter_sql = concat("alter table ",table_name," add partition(partition ",@par_name," values less than (",@tmp_month_value,"))");                        insert into change_partition_log values( now(), "add_day", @alter_sql);                        prepare stmt from @alter_sql;                        execute stmt;                        set i = i+1;                end while;                set i = 1;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                        -- 删除分区的存储过程DELIMITER ;;CREATE PROCEDURE lens_browser_data.`del_partition_day`()BEGIN        declare table_name varchar(255) default 0;        declare tmp_sql varchar(255) default "";        declare max_par_now varchar(255) default "";        declare par_del_num varchar(255) default "";        declare par_del varchar(255) default "";        declare alter_sql varchar(255) default "";        declare i int default 0;        declare done int default -1;        declare myCursor cursor for select tb_name from op_tb_partition_day;        declare continue handler for not found set done = 1;        open myCursor;        myLoop:LOOP                fetch myCursor into table_name;                if done = 1 then                leave myLoop;                end if;                set @tmp_sql = concat("select count(*) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_browser_data'  into @max_par_now");                prepare stmt from @tmp_sql;                execute stmt;                set @par_del_num = @max_par_now-8-3;                while i < @par_del_num do                        set @tmp_sql = concat("select partition_name from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA ='lens_browser_data' and table_name='",table_name,"' order by partition_name limit 1 into @par_del");                        prepare stmt from @tmp_sql;                        execute stmt;                        set @alter_sql = concat("alter table ",table_name," drop partition ",@par_del);                        prepare stmt from @alter_sql;                        execute stmt;                        insert into change_partition_log values(now(),"drop_day",concat(i,"--",@par_del,"--",@alter_sql));                        set i = i+1;                         end while;                set i = 0;        end loop myLoop;        close myCursor;END ;;DELIMITER ;                           -- 调度EventDELIMITER ;;CREATE EVENT `manager_partition_day` ON SCHEDULE EVERY 3 MONTH STARTS '2015-10-01 00:30:00' ON COMPLETION PRESERVE ENABLE DO BEGINcall add_partition_day;call del_partition_day;END ;;DELIMITER ;

转载于:https://blog.51cto.com/quenlang/1728173

你可能感兴趣的文章

WebViewJavascriptBridge

ASPxGridview使用总结(DEVExpress)

查看>>

.NET设计模式（9）：桥接模式（Bridge Pattern）

查看>>