问题描述
我想按日期时间列对 mysql 表进行分区.一天一个分区.创建表的脚本是这样的:
但是当我选择某天的数据时,找不到分区,select语句是这样的:
当我使用另一个语句时,它可以定位分区,但我无法选择某天的数据.
有没有人告诉我如何选择某一天的数据并使用分区.谢谢!
Partitions by HASH 对于 datetime 列是一个非常糟糕的主意,因为它不能使用 分区修剪.来自 MySQL 文档:
<块引用>修剪只能用于分区表的整数列哈希或密钥.例如,表 t4 上的这个查询不能使用修剪因为 dob 是一个 DATE 列:
<块引用>
但是,如果表将年份值存储在 INT 列中,则具有 WHERE year_col >= 2001 AND year_col <= 2005 的查询可以是修剪.
因此您可以将 TO_DAYS(DATE()) 的值存储在额外的 INTEGER 列中以使用修剪.
另一种选择是使用范围分区:
现在以下查询将只使用分区 p20110403:
I want to partition a mysql table by datetime column. One day a partition.The create table scripts is like this:
But when I select data of some day.It could not locate the partition.The select statement is like this:
when i use another statement,it could locate the partition,but I coluld not select data of some day.
Is there anyone tell me How I could select data of some day and make use of partition.Thanks!
Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs:
Pruning can be used only on integer columns of tables partitioned by HASH or KEY. For example, this query on table t4 cannot use pruning because dob is a DATE column:
However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned.
So you can store the value of TO_DAYS(DATE()) in an extra INTEGER column to use pruning.
Another option is to use RANGE partitioning:
Now the following query will only use partition p20110403:
这篇关于如何按日期时间列对表进行分区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!