锐客网

  1. 首页 > 生活百科 > it技术 > >

mysql怎么按时间分区 mysql创建时间分区

IT技术分区

Mysql按时间进行表分区以下是创建一张测试表TEST并且按照时间CREATE_TIME创建RANGE分区mysql怎么按时间分区 ， 并使用ID创建hash分区mysql怎么按时间分区 ， 组成复合分区 。
CREATE TABLE TEST (
CREATE_TIME DATETIME DEFAULT NULL, IDBIGINT(15) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(CREATE_TIME)) PARTITIONS 7 SUBPARTITION BY HASH(ID) SUBPARTITIONS 16
(PARTITIONP1710VALUES LESS THAN (TO_DAYS ('2017-10-01'))
(SUBPARTITIONP1710sp0,SUBPARTITIONP1710sp1,
SUBPARTITIONP1710sp2,SUBPARTITIONP1710sp3,
SUBPARTITIONP1710sp4,SUBPARTITIONP1710sp5,
SUBPARTITIONP1710sp6,SUBPARTITIONP1710sp7,
SUBPARTITIONP1710sp8,SUBPARTITIONP1710sp9,
SUBPARTITIONP1710sp10,SUBPARTITIONP1710sp11,
SUBPARTITIONP1710sp12,SUBPARTITIONP1710sp13,
SUBPARTITIONP1710sp14,SUBPARTITIONP1710sp15),
PARTITIONP1711VALUES LESS THAN (TO_DAYS ('2017-11-01'))
(SUBPARTITIONP1711sp0,SUBPARTITIONP1711sp1,
SUBPARTITIONP1711sp2, SUBPARTITIONP1711sp3,
SUBPARTITIONP1711sp4, SUBPARTITIONP1711sp5,
SUBPARTITIONP1711sp6, SUBPARTITIONP1711sp7,
SUBPARTITIONP1711sp8, SUBPARTITIONP1711sp9,
SUBPARTITIONP1711sp10, SUBPARTITIONP1711sp11,
SUBPARTITIONP1711sp12, SUBPARTITIONP1711sp13,
SUBPARTITIONP1711sp14, SUBPARTITIONP1711sp15 ),
mysql 怎么按照时间做表分区用mysql的表分区功能（逻辑上还是一个表，对程序来说是透明的） ， 通过分区函数可实现自动分表 。如果想实现根据数据每月或每周动态的再分区，可以写一个存储过程实现分区调整逻辑，最后写一个mysql event（自动化作业）按周期调用这个存储过程就行了 。
mysql 以时间分区怎么打开一 ， 什么是mysql分表，分区
什么是分表，从表面意思上看呢，就是把一张表分成N多个小表，具体请看mysql分表mysql怎么按时间分区的3种方法
什么是分区，分区呢就是把一张表的数据分成N多个区块 ， 这些区块可以在同一个磁盘上，也可以在不同的磁盘上
一，先说一下为什么要分表
当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了 。分表的目的就在于此，减小数据库的负担，缩短查询时间 。
根据个人经验，mysql执行一个sql的过程如下mysql怎么按时间分区：
1,接收到sql;2,把sql放到排队队列中 ;3,执行sql;4,返回执行结果 。在这个执行过程中最花时间在什么地方呢mysql怎么按时间分区？第一，是排队等待的时间 ， 第二，sql的执行时间 。其实这二个是一回事，等待的同时，肯定有sql在执行 。所以我们要缩短sql的执行时间 。
求助：MySQL如何按天分区所谓按天mysql怎么按时间分区，不过是日期精确到天而已 。
错误的按日期分区例子
最直观的方法 ， 就是直接用年月日这种日期格式来进行常规的分区mysql怎么按时间分区：
mysqlcreate table rms (d date)
-partition by range (d)
- (partition p0 values less than ('1995-01-01'),
-partition p1 VALUES LESS THAN ('2010-01-01'));
上面的例子中，就是直接用"Y-m-d"的格式来对一个table进行分区，可惜想当然往往不能奏效，会得到一个错误信息:
ERROR 1064 (42000): VALUES value must be of same type as partition function near '),
partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3
上述分区方式没有成功，而且明显的不经济，老练的DBA会用整型数值来进行分区：
mysql CREATE TABLE part_date1
-(c1 int default NULL,
-c2 varchar(30) default NULL,
-c3 date default NULL) engine=myisam
-partition by range (cast(date_format(c3,'%Y%m%d') as signed))
- (PARTITION p0 VALUES LESS THAN (19950101),
- PARTITION p1 VALUES LESS THAN (19960101) ,
- PARTITION p2 VALUES LESS THAN (19970101) ,
- PARTITION p3 VALUES LESS THAN (19980101) ,
- PARTITION p4 VALUES LESS THAN (19990101) ,
- PARTITION p5 VALUES LESS THAN (20000101) ,
- PARTITION p6 VALUES LESS THAN (20010101) ,
- PARTITION p7 VALUES LESS THAN (20020101) ,
- PARTITION p8 VALUES LESS THAN (20030101) ,
- PARTITION p9 VALUES LESS THAN (20040101) ,
- PARTITION p10 VALUES LESS THAN (20100101),
- PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.01 sec)
搞定？接着往下分析
mysql explain partitions
- select count(*) from part_date1 where
-c3 '1995-01-01' and c3 '1995-12-31'\G
*************************** 1. row ***************************
id: 1
【mysql怎么按时间分区 mysql创建时间分区】select_type: SIMPLE
table: part_date1
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8100000
Extra: Using where
1 row in set (0.00 sec)
万恶的mysql居然对上面的sql使用全表扫描 ， 而不是按照我们的日期分区分块查询 。原文中解释到MYSQL的优化器并不认这种日期形式的分区，花了大量的篇幅来引诱俺走上歧路，过分 。
正确的日期分区例子
mysql优化器支持以下两种内置的日期函数进行分区：
TO_DAYS()
YEAR()
看个例子：
mysql CREATE TABLE part_date3
-(c1 int default NULL,
-c2 varchar(30) default NULL,
-c3 date default NULL) engine=myisam
-partition by range (to_days(c3))
- (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
- PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
- PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
- PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
- PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
- PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
- PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
- PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
- PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
- PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
- PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
- PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)
以to_days()函数分区成功，我们分析一下看看：
mysql explain partitions
- select count(*) from part_date3 where
-c3 date '1995-01-01' and c3 date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_date3
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 808431
Extra: Using where
1 row in set (0.00 sec)
可以看到 ， mysql优化器这次不负众望 ， 仅仅在p1分区进行查询 。在这种情况下查询，真的能够带来提升查询效率么？下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比：
mysql select count(*) from part_date3 where
-c3 date '1995-01-01' and c3 date '1995-12-31';
----------
| count(*) |
----------
|805114 |
----------
1 row in set (4.11 sec)
mysql select count(*) from part_date1 where
-c3 date '1995-01-01' and c3 date '1995-12-31';
----------
| count(*) |
----------
|805114 |
----------
1 row in set (40.33 sec)
可以看到，分区正确的话query花费时间为4秒，而分区错误则花费时间40秒（相当于没有分区），效率有90％的提升！所以我们千万要正确的使用分区功能，分区后务必用explain验证，这样才能获得真正的性能提升 。
注意：
在mysql5.1中建立分区表的语句中 ， 只能包含下列函数：
ABS()
CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型），例如
mysql CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )( - PARTITION p0 VALUES IN (1,3,5), - PARTITION p1 VALUES IN (2,4,6) - );; ERROR 1491 (HY000): The PARTITION function returns the wrong typemysql CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )( - PARTITION p0 VALUES IN (1,3,5), - PARTITION p1 VALUES IN (2,4,6) - ); Query OK, 0 rows affected (0.01 sec)
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK()
mysql怎么按时间分区的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql创建时间分区、mysql怎么按时间分区的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：java查询oracle内存溢出，oracle查询内存占用情况

    下一篇：创业网站制作头像，大学生创业网头像必须是自己吗