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 条评论
登录 后参与评论

相关文章

来自专栏吴伟祥

建表模版SQL 原

862
来自专栏沃趣科技

MySQL8.0新特性——invisible indexes

MySQL 8.0版本中新增了invisible indexes(不可见索引)特性,索引被invisible后,MySQL优化器就会忽略该索引(无此特性时需要删...

49711
来自专栏别先生

mysql根据分组和条件查询以后如何统计记录的条数

1、子查询,查询出的数据随便起一个别名,然后根据分组和条件查询出的数据,作为一个具有一列的一个表,然后外面的查询查询这个数据表的这一列的总数,即可。    

1792
来自专栏web编程技术分享

第三节 - mysql 建表

3825
来自专栏抠抠空间

MySQL之表操作

一、创建表   1、创建新表 #语法: create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名...

3477
来自专栏别先生

mysql如何添加一个表的外键

1:创建一个父表,主键作为子表的外键: 1 create table province( 2 pId int primary key auto_incr...

2057
来自专栏Python研发

MySql[二]

视图就是一个虚拟表(非真实存在),其本质是[根据SQL语句获取动态的数据集,并为其命名],用户使用时只需要使用[名称]即可获取结果集并可以将其当做代表来使用·

1724
来自专栏康怀帅的专栏

MySQL 修改数据表 ALTER

修改列定义 ALTER table 表名 MODIFY 字段名 列定义 位置[FIRST | AFTER 字段名]; 修改列名称 ALTER table 表名 ...

3636
来自专栏跟着阿笨一起玩NET

SQL Server 2008 新语法

1451
来自专栏Netkiller

数据库安全·内容版本控制,撰改留痕

以下节选择《Netkiller Architect 手札》 地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库...

3034

扫码关注云+社区

领取腾讯云代金券