通过mysql的表分区解决数据库挂死-博客-最代码

kaka 2014-05-23 14:50:34

通过mysql的表分区解决数据库挂死

来说下我们这个应用场景吧：我们这个监控系统目前每天存储的历史监控数据大概有几百万条(具体跟监控频率有关系)，用户可以自定义历史数据保存时间，假如用户设置的时间为12天，那么12天后也就是第13天，我们就要删掉最早一天的数据，以此类推。最近项目部署后，刚好到第12天，项目就挂掉了，排查原因发现是mysql数据库被一句sql语句给搞死了，看了下程序里面对历史数据删除的代码如下：

public void cleanDataByDayTime( Date currentTime, Integer day ) {
		String dateTime = DateUtil.getDateByFormat( DateUtil.DEFAULT_DATETIME_FORMAT, DateUtil.addDaysForDate( currentTime, -day ) );
		// for (int i = 0; i < TableRouter.FACTOR; i++) {
		String delHql = "delete from MonitorDatum where dEndTime<='" + dateTime + "'";
		this.batchHql( delHql );
		// }
	}

这么大的数据量这样删除，导致IO操作很慢，数据库直接挂掉了。下面说下我们的解决方案，目前采用的是表分区的方式：

1. mysql配置修改
在mysql配置文件my.ini中[mysqld]中添加以下两项：

event_scheduler=1
innodb_file_per_table=1

2.初始化分区（建表时建立初始化分区）

-- 示例表结构

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (
    alarm_id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dbs_id              BIGINT UNSIGNED NOT NULL DEFAULT 0,  
    sql_id              BIGINT UNSIGNED NOT NULL DEFAULT 0,  
    rule_id             INT UNSIGNED NOT NULL DEFAULT 0,     
    alarm_time          DATETIME NOT NULL,
    PRIMARY KEY (alarm_id, alarm_time),
    INDEX (alarm_time)
) PARTITION BY RANGE (TO_DAYS(alarm_time))
(PARTITION pmax VALUES LESS THAN MAXVALUE);

注：分区参照字段必须与主键字段共同建立联合主键，分区参照字段需要建立索引，本例中的分区参照字段为alarm_time.

如果删除分区采用下面方式：pd20140526为分区名称


ALTER TABLE t_test DROP PARTITION pd20140526;

如果查询指定表的所有分区采用下面方式：dbName为数据库名称，tableName为被分区的表的名称

SELECT PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'dbName' AND TABLE_NAME = 'tableName'

3.定时创建分区

-- 创建分区的事件

EVERY 1 DAY:每天创建一个分区

01:00:00：每天凌晨1点创建分区，这个可以根据自己的需求修改。

pd:创建分区的前缀，这个也可以根据需求修改。

DELIMITER $$
DROP EVENT IF EXISTS event_create_partition$$
CREATE EVENT event_create_partition ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE(), '01:00:00') ON COMPLETION PRESERVE ENABLE DO BEGIN
		DECLARE partition_name VARCHAR(32);
		DECLARE partition_desc INT UNSIGNED;
		
		SET partition_name = CONCAT('pd', DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'%Y%m%d'));
		SET partition_desc = TO_DAYS(CURRENT_DATE());
	    SET @partition_alter = CONCAT('ALTER TABLE t_test REORGANIZE PARTITION pmax INTO (
			PARTITION ', partition_name, ' VALUES LESS THAN (', partition_desc, '),
			PARTITION pmax VALUES LESS THAN MAXVALUE)');
		
		PREPARE stmt FROM @partition_alter;
		EXECUTE stmt;
	    DEALLOCATE PREPARE stmt;
	END$$

DELIMITER ;

最后在mysql安装目录的data目录中（我本地的路径是：C:\ProgramData\MySQL\MySQL Server 5.5\data\zq，zq是我的数据库名），具体查看分区后的结构，如下图：

采用表分区的方式后我们删除具体某一天的数据就不是直接操作数据库表数据了，我们直接通过ALTER TABLE t_test DROP PARTITION pd20140526;语句删除分区文件了，将表数据的操作上升到对文件的操作了，速度非常快。表分区对查询操作来说也是非常快的。有兴趣的话，大家可以参照上面的在自己本地测试下。

由最代码官方编辑于2016-8-30 9:35:42

打赏

5个评论