时间戳上的MySQL表分区 [英] MySQL table partitioning on timestamp
问题描述
我已经对表进行了分区(由于内存不足错误-表太大).我已经在时间戳列上对其进行了分区,如下所示:
I have partitioned a table (because of an out of memory error - table got too big). I have partitioned it on a timestamp column as shown below:
CREATE TABLE test (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
dob timestamp NOT NULL
)
PARTITION BY RANGE( unix_timestamp(dob) ) (
PARTITION p2012 VALUES LESS THAN (unix_timestamp('2013-01-01 00:00:00')),
PARTITION p2013 VALUES LESS THAN (unix_timestamp('2014-01-01 00:00:00')),
PARTITION pNew VALUES LESS THAN MAXVALUE
);
我希望分区过程也将有助于加快我的几个查询的速度,而这需要花费几个小时才能运行;但是,这种类型的分区似乎并未启动,并且所有分区仍在使用中并一直在扫描中进行查询.我尝试了几种方法,但均以失败告终:
I was hoping that the process of partitioning would also help in speeding up a couple of my queries whihc take a few hours to run; however, this type of partitioning doesn't seem to kick in and all partitions are still being used and scanned through for the queries. I have tried, and failed, with a couple more approaches:
1)尝试使用不同的分区范围
1) Tried to use different range for the partitioning
CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob timestamp NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p2012 VALUES LESS THAN (2013),
PARTITION p2013 VALUES LESS THAN (2014),
PARTITION pNew VALUES LESS THAN MAXVALUE
);
但是,这会导致错误:Error Code: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
However, that results in an error: Error Code: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
2)放弃了更改分区以供查询优化器识别,并按照MySQL的文档中的建议-
2) Gave up on changing partitioning to be recognized by the query optimizer, and as suggested in MySQL's Doc - 18.5 Partition Selection tried specifying which partitions to use in the select statement instead:
select * from t2 partition (p2012)
但是,这将返回语法错误Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p2012) LIMIT 0, 1000' at line 1
But, that returns a syntax error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p2012) LIMIT 0, 1000' at line 1
有人对我有什么建议吗?我还能尝试利用表分区来优化查询吗?
Does anybody have any suggestions what else I could try to utilize table partitioning to optimize the queries?
推荐答案
您可以使用UNIX_TIMESTAMP()函数.来自MySQL文档的示例:
You can use UNIX_TIMESTAMP() function. Example from MySQL docs:
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
您可以在以下位置找到它: https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html .
You can find it in: https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html.
这篇关于时间戳上的MySQL表分区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!