mysql按照天分区月表

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/haluoluo211/article/details/78658603

背景,每天需要向mysql中插入大概5KW数据,数据主要是插入,与查询基本不会update,由于量大需要查询因此想创建天表,但是缺点在于:

  1. 每天一张表也比较麻烦
  2. 假设现在要查询几天的数据,还得查询多张表,最终合并查询结果,这一点十分麻烦

这时可以考虑用一张月表,每一天的数据一个分区表,而分区表对于用户是透明的。

下面给出一个简单的示例,仅仅为了展示:

CREATE TABLE `audio_rec_ret_2017_11` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ftime` datetime NOT NULL,
  PRIMARY KEY (`id`,`ftime`),
  KEY `ftime_idx` (`ftime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(ftime))
(    PARTITION p20171101 VALUES LESS THAN (TO_DAYS('2017-11-02')) ENGINE = InnoDB,
    PARTITION p20171102 VALUES LESS THAN (TO_DAYS('2017-11-03')) ENGINE = InnoDB,
    PARTITION p20171103 VALUES LESS THAN (TO_DAYS('2017-11-04')) ENGINE = InnoDB,
    PARTITION p20171104 VALUES LESS THAN (TO_DAYS('2017-11-05')) ENGINE = InnoDB,
    PARTITION p20171105 VALUES LESS THAN (TO_DAYS('2017-11-06')) ENGINE = InnoDB,
    PARTITION p20171106 VALUES LESS THAN (TO_DAYS('2017-11-07')) ENGINE = InnoDB,
    PARTITION p20171107 VALUES LESS THAN (TO_DAYS('2017-11-08')) ENGINE = InnoDB,
    PARTITION p20171108 VALUES LESS THAN (TO_DAYS('2017-11-09')) ENGINE = InnoDB,
    PARTITION p20171109 VALUES LESS THAN (TO_DAYS('2017-11-10')) ENGINE = InnoDB
);

插入如下数据

insert into springdemo.audio_rec_ret_2017_11(ftime)
values('2017-11-1 10:10:10'),
('2017-11-2 10:10:10'),
('2017-11-3 10:10:10'),
('2017-11-4 10:10:10'),
('2017-11-5 10:10:10'),
('2017-11-6 10:10:10');

看下我们的查询结果:

explain partitions 
select * from springdemo.audio_rec_ret_2017_11
where ftime between '2017-11-4 0:0:0' and '2017-11-5 23:59:59';

可以看到只遍历了,两个分区表,只扫描了2行,而不是扫描所有的行。

上面的按照月每天一张表的,如下数据,每次写的很麻烦,于是考虑使用python生成

 PARTITION p20171106 VALUES LESS THAN (TO_DAYS('2017-11-07')) ENGINE = InnoDB,

生成代码如下:

def tb_partition_sql():
    """
    mysql partition table by day in month
    :return:
    """
    sql = """PARTITION p%s VALUES LESS THAN (TO_DAYS('%s')) ENGINE = InnoDB,"""
    d1 = datetime.date(2017, 12, 1)
    d2 = datetime.date(2017, 12, 31)
    days = [d1 + datetime.timedelta(days=x) for x in range((d2-d1).days + 2)]
    # print len(days)
    for i in xrange(len(days) - 1):
        s1 = days[i].strftime('%Y%m%d')
        s2 = days[i + 1].strftime('%Y-%m-%d')
        print sql % (s1, s2)

代码见本人的github

生成完成之后,复制到mysql的语句中即可。

CREATE TABLE `audio_rec_ret_2017_12` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ftime` datetime NOT NULL,
  PRIMARY KEY (`id`,`ftime`),
  KEY `ftime_idx` (`ftime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(ftime))(

//复制到这里


);

使用的过程中,查询的时候一定要加上时间范围这样可以大大减少查询时间。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区