解决MYSQL大表问题-实战篇（二）

作者: 西普_ship | 来源:发表于2018-06-14 01:42 被阅读0次

首先上表结构

CREATE TABLE `sys_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `did` bigint(20) NOT NULL ,
  `ndata` int(11) NOT NULL DEFAULT '0' ,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `is_deleted` int(11) DEFAULT '0',
  PRIMARY KEY (`id`,`did`,`create_time`),
  UNIQUE KEY `hindex` (`id`,`did`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p2018061300 VALUES LESS THAN ('2018-06-13 00:00:00') ENGINE = InnoDB,
 PARTITION p2018061302 VALUES LESS THAN ('2018-06-13 02:00:00') ENGINE = InnoDB,
 PARTITION p2018061304 VALUES LESS THAN ('2018-06-13 04:00:00') ENGINE = InnoDB,
 PARTITION p2018061306 VALUES LESS THAN ('2018-06-13 06:00:00') ENGINE = InnoDB,
 PARTITION p2018061308 VALUES LESS THAN ('2018-06-13 08:00:00') ENGINE = InnoDB) */

在创建表的时候，首先建立了id，did，create_time三个联合索引，由于我的数据中，did和create_time都会重复，但是三个合起来就肯定不会重复，所以我就选择了创建联合索引。至于为何这样见索引就自己google或者度娘啦，这不是本文的重点。

这里我使用了Range分区，因为我们这里设想的是按小时创建分区，这是一个时间范围，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数，添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势。值得注意的是我建表的时候已经预先创建了几个分区，举例来说：PARTITION p2018061300 VALUES LESS THAN ('2018-06-13 00:00:00') ENGINE = InnoDB，这里我预先创建了一个名为p2018061300的分区，其 LESS值为2018-06-13 00:00:00，目的是想把create_time<=2018-06-13 00:00:00的数据放入改分区，p2018061300，p2018061302，p2018061304，p2018061306从分区名也可以看出每两个小时一个分区，p2018061300的意思就是2018年6月13日0点之前的数据的分区。

自动分区

总不能每次都手动每两小时创建分区吧...必须是自动的！
这时候存储过程就要登场了！

自动每两个小时创建一个分区的存储过程贴上来

-- 自动创建表分区的存储过程
DROP PROCEDURE IF EXISTS AUTO_PARTITION_HOUR;

DELIMITER //
CREATE PROCEDURE AUTO_PARTITION_HOUR(IN $table_name VARCHAR(64), IN $range_hours INT,  IN $min_time VARCHAR(20))
BEGIN

    -- $table_name 待分片的表名
    -- $range_hours 每隔分区的跨度时长，例如2小时，尽量取24能整除的数
    -- $min_time 已有历史数据的最小时间，决定起始分片的时间，如果为 null 则自动取当天的零点

    DECLARE $base_dir VARCHAR(64);
    DECLARE $monthly_dir  VARCHAR(64);
    DECLARE $now VARCHAR(30); -- 当前时间戳
    DECLARE $zero_am VARCHAR(30); -- 明天零点
    DECLARE $stop_hour VARCHAR(30); -- 预创建终止时间戳
    DECLARE $sql_partition_template VARCHAR(500); -- 建分片的 SQL 模板

    DECLARE $partition_name VARCHAR(20); -- 新分片名字
    DECLARE $last_less_than_hour VARCHAR(30); -- 上一个分片的 less 值
    DECLARE $less_than_hour VARCHAR(30); -- 上一个分片的 less 值
    DECLARE $sql_tmp VARCHAR(500); -- 临时拼接的 SQL

    -- 数据文件和索引文件的存放目录
    SET $base_dir = CONCAT('/data/mysql/hisdata/', DATABASE(), '/', $table_name, '/');

    -- 当前系统时间
    SET $now = DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
    -- 今天零点
    SET $zero_am = DATE_FORMAT(now(), '%Y-%m-%d %00:%00:%00');
    -- 预创建分区的终止小时值(后天零点)
    SET $stop_hour =  DATE_FORMAT(now()+interval 172800 second, '%Y-%m-%d %00:%00:%00');

    -- 创建新分片的SQL模板
    SET $sql_partition_template = CHAR(10);
    SET $sql_partition_template = CONCAT($sql_partition_template, 'ALTER TABLE ', $table_name, ' ADD PARTITION (');
    SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), 'PARTITION $partition_name VALUES LESS THAN (');
    SET $sql_partition_template = CONCAT($sql_partition_template,'\'$less_than_hour\'',')');
    SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), ');');

    -- 查找上一个分片的终止时间
    SET $last_less_than_hour = NULL;
    SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $last_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
    ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1;

    IF $last_less_than_hour IS NULL OR $last_less_than_hour='' THEN
        IF $min_time IS NULL THEN
            -- 没有记录，设置为当天零点
            SET $last_less_than_hour = $zero_am;
        ELSE
            -- 设置为已有最早记录的当天零点
            SET $last_less_than_hour = DATE_FORMAT($min_time, '%Y-%m-%d %00:%00:%00');
        END IF;

    END IF;

    -- 循环预创建分区
    _PARTITION_LOOP_ : LOOP
        SET $less_than_hour = DATE_ADD($last_less_than_hour ,interval $range_hours HOUR);
        IF $less_than_hour > $stop_hour THEN
            LEAVE _PARTITION_LOOP_;
        END IF;

        SET $partition_name = CONCAT('p', DATE_FORMAT($less_than_hour, '%Y%m%d%H'));

        SET $sql_tmp = $sql_partition_template;
        SET $sql_tmp = REPLACE($sql_tmp, '$partition_name', $partition_name);
        SET $sql_tmp = REPLACE($sql_tmp, '$less_than_hour', $less_than_hour);
        SET @stmt_sql = $sql_tmp;
        PREPARE stmt FROM @stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET $last_less_than_hour = $less_than_hour;
    END LOOP _PARTITION_LOOP_;

END
//
DELIMITER ;

存储过程的解读

我的存储过程是参考网上一位大牛的，网址忘了...Sorry
大致意思就是输入表名，分片时间步进，最早数据时间，然后查找上一个分片的终止时间，如果没有记录就设置为当天的0点，或者最早记录当天的0点，
如果已经有分片记录了，就取分片的终止时间，然后循环创建分区，每循环一次小时加上$range_hours个小时，循环里面有个条件跳出

IF $less_than_hour > $stop_hour THEN
            LEAVE _PARTITION_LOOP_;
        END IF;

这里的$stop_hour就是之前设置的后天的0点。
这个存储过程每天运行一次就可以了。

自动执行存储过程

这个简单啦，创建一个自动运行的Event就可以了，上代码

-- 创建事件
create event auto_partition_event
    on schedule every 1 DAY starts '2018-06-12 18:30:00'
    ON COMPLETION PRESERVE ENABLE
    do
        call AUTO_PARTITION_HOUR('sys_history',2);

限制保留历史数据的天数

上一篇说到了要求保留一年的历史数据，那么就意味着需要定时删除一年前的数据和分区（注意：删除分区就是删除数据），那么想到的又是存储过程，上代码

-- 循环删除分区
    _CLEAR_PARTITION_LOOP_:LOOP
        -- 查找最早的一个分区的时间
        SET $most_less_than_hour = NULL;
        SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $most_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
        ORDER BY PARTITION_ORDINAL_POSITION LIMIT 1;

        SET $most_partition_name = CONCAT('p', DATE_FORMAT($most_less_than_hour, '%Y%m%d%H'));
        SET $most_less_than_hour = DATE_FORMAT($most_less_than_hour, '%Y-%m-%d %00:%00:%00');
        IF timestampdiff(day,$most_less_than_hour,$zero_am) < $delay_day THEN
            LEAVE _CLEAR_PARTITION_LOOP_;
        END IF;

        SET $del_sql = CHAR(10);
        SET $del_sql = CONCAT($del_sql,'ALTER TABLE ',$table_name,' DROP PARTITION ', $most_partition_name);

        SET @sd_sql = $del_sql;
        PREPARE sd FROM @sd_sql;
        EXECUTE sd;
        DEALLOCATE PREPARE sd;

    END LOOP _CLEAR_PARTITION_LOOP_;

创建一个循环自动循环删除时间大于输入保留天数的数据，这里多了一个输入参数$delay_day，例如我只想保留365天的数据，那么$delay_day输入值就为365。

贴完整的存储过程

-- 自动创建表分区的存储过程
DROP PROCEDURE IF EXISTS AUTO_PARTITION_HOUR;

DELIMITER //
CREATE PROCEDURE AUTO_PARTITION_HOUR(IN $table_name VARCHAR(64), IN $range_hours INT,  IN $min_time VARCHAR(20),IN $delay_day INT)
BEGIN

    -- $table_name 待分片的表名
    -- $range_hours 每隔分区的跨度时长，例如2小时，尽量取24能整除的数
    -- $min_time 已有历史数据的最小时间，决定起始分片的时间，如果为 null 则自动取当天的零点

    DECLARE $base_dir VARCHAR(64);
    DECLARE $monthly_dir  VARCHAR(64);
    DECLARE $now VARCHAR(30); -- 当前时间戳
    DECLARE $zero_am VARCHAR(30); -- 明天零点
    DECLARE $stop_hour VARCHAR(30); -- 预创建终止时间戳
    DECLARE $sql_partition_template VARCHAR(500); -- 建分片的 SQL 模板

    DECLARE $partition_name VARCHAR(20); -- 新分片名字
    DECLARE $last_less_than_hour VARCHAR(30); -- 上一个分片的 less 值
    DECLARE $less_than_hour VARCHAR(30); -- 上一个分片的 less 值
    DECLARE $sql_tmp VARCHAR(500); -- 临时拼接的 SQL

    DECLARE $most_less_than_hour VARCHAR(30); -- 最早一个分区的less 值
    DECLARE $most_partition_name VARCHAR(30); -- 最早一个分区的名称
    DECLARE $del_sql VARCHAR(100);

    -- 数据文件和索引文件的存放目录
    SET $base_dir = CONCAT('/data/mysql/hisdata/', DATABASE(), '/', $table_name, '/');

    -- 当前系统时间
    SET $now = DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
    -- 今天零点
    SET $zero_am = DATE_FORMAT(now(), '%Y-%m-%d %00:%00:%00');
    -- 预创建分区的终止小时值(后天零点)
    SET $stop_hour =  DATE_FORMAT(now()+interval 172800 second, '%Y-%m-%d %00:%00:%00');

    -- 创建新分片的SQL模板
    SET $sql_partition_template = CHAR(10);
    SET $sql_partition_template = CONCAT($sql_partition_template, 'ALTER TABLE ', $table_name, ' ADD PARTITION (');
    SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), 'PARTITION $partition_name VALUES LESS THAN (');
    SET $sql_partition_template = CONCAT($sql_partition_template,'\'$less_than_hour\'',')');
    SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), ');');

    -- 查找上一个分片的终止小时值
    SET $last_less_than_hour = NULL;
    SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $last_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
    ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1;

    IF $last_less_than_hour IS NULL OR $last_less_than_hour='' THEN
        IF $min_time IS NULL THEN
            -- 没有记录，设置为当天零点
            SET $last_less_than_hour = $zero_am;
        ELSE
            -- 设置为已有最早记录的当天零点
            SET $last_less_than_hour = DATE_FORMAT($min_time, '%Y-%m-%d %00:%00:%00');
        END IF;

    END IF;

    -- 循环预创建分区
    _PARTITION_LOOP_ : LOOP
        SET $less_than_hour = DATE_ADD($last_less_than_hour ,interval $range_hours HOUR);
        IF $less_than_hour > $stop_hour THEN
            LEAVE _PARTITION_LOOP_;
        END IF;

        SET $partition_name = CONCAT('p', DATE_FORMAT($less_than_hour, '%Y%m%d%H'));

        SET $sql_tmp = $sql_partition_template;
        SET $sql_tmp = REPLACE($sql_tmp, '$partition_name', $partition_name);
        SET $sql_tmp = REPLACE($sql_tmp, '$less_than_hour', $less_than_hour);
        SET @stmt_sql = $sql_tmp;
        PREPARE stmt FROM @stmt_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET $last_less_than_hour = $less_than_hour;
    END LOOP _PARTITION_LOOP_;

    -- 循环删除分区
    _CLEAR_PARTITION_LOOP_:LOOP
        -- 查找最早的一个分区的时间
        SET $most_less_than_hour = NULL;
        SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $most_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
        ORDER BY PARTITION_ORDINAL_POSITION LIMIT 1;

        SET $most_partition_name = CONCAT('p', DATE_FORMAT($most_less_than_hour, '%Y%m%d%H'));
        SET $most_less_than_hour = DATE_FORMAT($most_less_than_hour, '%Y-%m-%d %00:%00:%00');
        IF timestampdiff(day,$most_less_than_hour,$zero_am) < $delay_day THEN
            LEAVE _CLEAR_PARTITION_LOOP_;
        END IF;

        SET $del_sql = CHAR(10);
        SET $del_sql = CONCAT($del_sql,'ALTER TABLE ',$table_name,' DROP PARTITION ', $most_partition_name);

        SET @sd_sql = $del_sql;
        PREPARE sd FROM @sd_sql;
        EXECUTE sd;
        DEALLOCATE PREPARE sd;

    END LOOP _CLEAR_PARTITION_LOOP_;

END
//
DELIMITER ;

这样每隔一天运行一次，就可以自动创建直到后天0点的分区，并且自动删除过期的分区。

解决MYSQL大表问题-实战篇（二）

首先上表结构

自动分区

自动每两个小时创建一个分区的存储过程贴上来

存储过程的解读

自动执行存储过程

限制保留历史数据的天数

贴完整的存储过程

上一篇解决MYSQL大表问题-小白篇-高手忽略（一）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

解决MYSQL大表问题-实战篇（二）

首先上表结构

自动分区

自动每两个小时创建一个分区的存储过程贴上来

存储过程的解读

自动执行存储过程

限制保留历史数据的天数

贴完整的存储过程

上一篇 解决MYSQL大表问题-小白篇-高手忽略 （一）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

上一篇解决MYSQL大表问题-小白篇-高手忽略（一）