mysql按照天分区月表

bear_fish

发布于 2018-09-14 10:00:32

2.6K0

发布于 2018-09-14 10:00:32

文章被收录于专栏：用户2442861的专栏

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

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

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

下面给出一个简单的示例，仅仅为了展示：

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))(

//复制到这里


);

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2017年11月28日，如有侵权请联系 cloudcommunity@tencent.com 删除

其他